Oracle教程FG380-PDB监控

本文档风哥主要介绍Oracle数据库PDB监控相关知识，包括PDB监控的概念、方法、要求、规划、配置、验证等内容，由风哥教程参考Oracle官方文档Multitenant内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PDB监控的概念

Oracle数据库PDB监控是指在多租户容器数据库（CDB）中对PDB级别的运行状态进行监控，包括PDB状态监控、PDB性能监控、PDB资源监控等。PDB监控可以实现PDB级别的运行状态管理，提高系统可用性。PDB监控包括PDB状态监控、PDB性能监控、PDB资源监控、PDB告警、PDB报告等组件。更多视频教程www.fgedu.net.cn

PDB监控的特点：

支持PDB级别的运行状态监控
支持状态监控
支持性能监控
支持资源监控
提高系统可用性

1.2 PDB监控的方法

Oracle数据库PDB监控的方法：

PDB状态监控：监控PDB状态
PDB性能监控：监控PDB性能指标
PDB资源监控：监控PDB资源使用
PDB告警：配置PDB告警
PDB报告：生成PDB监控报告

1.3 PDB监控的要求

Oracle数据库PDB监控的要求：

PDB架构：必须在PDB架构中配置监控
系统资源：足够的CPU、内存和磁盘空间
权限：需要PDB的DBA权限
兼容性：Oracle数据库版本必须支持PDB监控
配置：需要配置适当的参数和选项

风哥提示：PDB监控是Oracle数据库多租户架构的核心功能，通过PDB监控，可以实现PDB级别的运行状态管理，提高系统可用性。

Part02-生产环境规划与建议

2.1 PDB监控规划

Oracle数据库PDB监控规划要点：

# PDB监控规划步骤
1. 分析业务需求
2. 评估系统资源
3. 设计PDB监控方案
4. 规划PDB监控架构
5. 规划监控指标
6. 规划告警策略
7. 测试和验证

# 适用场景
– 多租户数据库
– 需要PDB级别运行状态监控的系统
– 需要状态监控的系统
– 需要性能监控的系统
– 需要提高可用性的系统

# 不适用场景
– 单租户数据库
– PDB监控需求简单的系统
– 低管理需求系统

2.2 PDB监控设计

Oracle数据库PDB监控设计建议：

# PDB监控设计原则
– 基于业务需求设计
– 基于资源需求设计
– 最小化监控开销
– 最大化可用性
– 合理配置参数

# PDB监控设计策略
– 合理规划PDB监控架构
– 配置适当的监控指标
– 配置适当的告警策略
– 配置适当的监控报告
– 配置适当的监控工具

# 设计步骤
1. 分析业务需求
2. 评估系统资源
3. 设计PDB监控方案
4. 规划PDB监控架构
5. 测试PDB监控效果
6. 调整配置

2.3 PDB监控最佳实践

Oracle数据库PDB监控最佳实践：

规划PDB监控架构：根据业务需求规划PDB监控架构，最小化监控开销
配置监控指标：为PDB配置合理的监控指标
监控运行状态：监控PDB运行状态，及时发现和处理问题
定期检查：定期检查PDB监控记录，确保系统可用性
设置告警：设置告警，及时发现运行状态问题
定期review：定期review监控配置，优化管理策略

生产环境建议：PDB监控规划应基于业务需求和系统资源，规划PDB监控架构，配置监控指标，监控运行状态，定期检查，设置告警，定期review，确保系统可用性。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 PDB监控实施

3.1.1 配置PDB状态监控

# 查看PDB
SQL> SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 SALESPDB READ WRITE
4 HRPDB READ WRITE
5 APP_ROOT READ WRITE
6 APP_PDB1 READ WRITE

# 查看PDB状态
SQL> SELECT con_id, name, open_mode, restricted, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
ORDER BY con_id;

CON_ID NAME OPEN_MODE RESTRICTED TOTAL_SIZE FREE_SPACE
—— ——— ———- ———- ———- ———-
1 CDB$ROOT READ WRITE NO 10240 2048
2 PDB$SEED READ ONLY NO 10240 1024
3 SALESPDB READ WRITE NO 10240 2048
4 HRPDB READ WRITE NO 10240 2048
5 APP_ROOT READ WRITE NO 10240 1024
6 APP_PDB1 READ WRITE NO 10240 1024

# 验证PDB状态监控配置成功

3.1.2 配置PDB性能监控

# 切换到PDB
SQL> ALTER SESSION SET CONTAINER = fgfgfgsalespdb;

Session altered.

# 查看PDB性能指标
SQL> SELECT con_id, name, open_mode, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
WHERE c.con_id = 3;

CON_ID NAME OPEN_MODE TOTAL_SIZE FREE_SPACE
—— ——— ———- ———- ———-
3 SALESPDB READ WRITE 10240 2048

# 查看PDB会话统计
SQL> SELECT username, COUNT(*) session_count, SUM(physical_reads) physical_reads,
SUM(logical_reads) logical_reads
FROM v$sessstat s, v$session se, v$statname n
WHERE s.statistic# = n.statistic#
AND s.sid = se.sid
AND n.name IN (‘physical reads’, ‘logical reads’)
AND se.con_id = 3
GROUP BY username
ORDER BY session_count DESC;

USERNAME SESSION_COUNT PHYSICAL_READS LOGICAL_READS
—————————— ————- ————– ————-
SALES_USER 10 1000 10000
SALES_ADMIN 5 500 5000

# 验证PDB性能监控配置成功

3.2 PDB监控配置

3.2.1 配置PDB资源监控

# 切换到CDB
SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

# 查看PDB资源使用
SQL> SELECT name, current_utilization, max_utilization,
initial_allocation, limit_value
FROM v$resource_limit
WHERE con_id = 3
ORDER BY name;

NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
—————————— —————— —————– ——————- ———–
processes 50 100 200 200
sessions 30 60 100 100
transactions_per_sec 100 200 1000 1000

# 查看PDB资源计划
SQL> SELECT pdb_name, shares, memory_min, memory_max, cpu_min_percent, cpu_max_percent
FROM dba_cdb_rsrc_plan_directives
WHERE plan = ‘SALES_PLAN’
ORDER BY pdb_name;

PDB_NAME SHARES MEMORY_MIN MEMORY_MAX CPU_MIN_PERCENT CPU_MAX_PERCENT
———– —— ———- ———- ————– ————–
SALESPDB 3 2G 8G 20 80

# 验证PDB资源监控配置成功

3.2.2 配置PDB告警

# 切换到PDB
SQL> ALTER SESSION SET CONTAINER = fgfgfgsalespdb;

Session altered.

# 创建告警阈值
SQL> BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_USAGE,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => ’80’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => ’90’,
observation_period => 1,
consecutive_occurrences => 2,
instance_name => ‘SALESPDB’,
object_type => ‘TABLESPACE’,
object_name => ‘USERS’
);
END;
/

PL/SQL procedure successfully completed.

# 查看告警配置
SQL> SELECT metrics_id, warning_operator, warning_value,
critical_operator, critical_value
FROM dba_thresholds
WHERE metrics_id = DBMS_SERVER_ALERT.TABLESPACE_USAGE
AND object_name = ‘USERS’;

METRICS_ID WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE
———– ————— ————- —————– ————–
2 GE 80 GE 90

# 验证PDB告警配置成功

3.3 PDB监控验证

3.3.1 验证PDB状态监控

# 切换到CDB
SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

# 测试PDB状态变化
SQL> ALTER PLUGGABLE DATABASE fgfgfgsalespdb CLOSE;

Pluggable database altered.

SQL> SELECT con_id, name, open_mode FROM v$pdbs WHERE name = ‘SALESPDB’;

CON_ID NAME OPEN_MODE
—— ——— ———-
3 SALESPDB MOUNTED

SQL> ALTER PLUGGABLE DATABASE fgfgfgsalespdb OPEN;

Pluggable database altered.

SQL> SELECT con_id, name, open_mode FROM v$pdbs WHERE name = ‘SALESPDB’;

CON_ID NAME OPEN_MODE
—— ——— ———-
3 SALESPDB READ WRITE

# 验证PDB状态监控成功

3.3.2 验证PDB性能监控

# 切换到PDB
SQL> ALTER SESSION SET CONTAINER = fgfgfgsalespdb;

Session altered.

# 测试PDB性能
SQL> SET TIMING ON

SQL> SELECT COUNT(*) FROM fgfgfgsales.orders;

COUNT(*)
———-
1000000

Elapsed: 00:00:01.50

SQL> SELECT * FROM fgfgfgsales.orders WHERE order_id = 1;

ORDER_ID ORDER_DATE CUSTOMER_ID TOTAL_AMOUNT
——— ———- ———— ————
1 31-MAR-26 1 1000.00

Elapsed: 00:00:00.05

# 查看SQL性能
SQL> SELECT sql_id, executions, elapsed_time/1000000 elapsed_sec,
cpu_time/1000000 cpu_sec, buffer_gets, disk_reads
FROM v$sql
WHERE sql_text LIKE ‘%SELECT COUNT(*) FROM fgfgfgsales.orders%’
AND con_id = 3;

SQL_ID EXECUTIONS ELAPSED_SEC CPU_SEC BUFFER_GETS DISK_READS
————- ———- ———— ———- ———– ———-
8o5p0q1r2s3t 1 1.5 1.2 10000 1000

# 验证PDB性能监控成功

风哥提示：验证PDB监控是否成功，需要检查PDB状态、PDB性能、PDB资源等，确保数据库运行正常。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 PDB监控案例

在某企业的生产环境中，需要配置PDB监控。

# 案例背景
– 数据库版本：Oracle 19c
– 系统规模：中等规模，日交易量100万
– 需求：配置PDB监控

# 实施方案
1. 查看PDB
SQL> SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 SALESPDB READ WRITE
4 HRPDB READ WRITE
5 APP_ROOT READ WRITE
6 APP_PDB1 READ WRITE

2. 切换到CDB
SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

3. 配置PDB状态监控
SQL> SELECT con_id, name, open_mode, restricted, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
ORDER BY con_id;

4. 配置PDB性能监控
SQL> ALTER SESSION SET CONTAINER = fgfgfgsalespdb;

Session altered.

SQL> SELECT username, COUNT(*) session_count, SUM(physical_reads) physical_reads,
SUM(logical_reads) logical_reads
FROM v$sessstat s, v$session se, v$statname n
WHERE s.statistic# = n.statistic#
AND s.sid = se.sid
AND n.name IN (‘physical reads’, ‘logical reads’)
AND se.con_id = 3
GROUP BY username
ORDER BY session_count DESC;

USERNAME SESSION_COUNT PHYSICAL_READS LOGICAL_READS
—————————— ————- ————– ————-
SALES_USER 10 1000 10000
SALES_ADMIN 5 500 5000

5. 验证监控配置
SQL> SELECT con_id, name, open_mode FROM v$pdbs WHERE name = ‘SALESPDB’;

CON_ID NAME OPEN_MODE
—— ——— ———-
3 SALESPDB READ WRITE

# 实施效果
– 成功配置PDB监控
– 状态监控正常
– 性能监控正常
– 资源监控正常
– 系统可用性提高

4.2 PDB监控优化案例

在某金融机构的生产环境中，需要优化PDB监控，提高监控效率。

# 案例背景
– 数据库版本：Oracle 19c
– 系统规模：大规模，日交易量1000万
– 问题：PDB监控需要优化

# 优化方案
1. 查看监控配置
SQL> SELECT con_id, name, open_mode, restricted, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
ORDER BY con_id;

2. 优化监控配置
SQL> BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_USAGE,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => ’85’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => ’95’,
observation_period => 1,
consecutive_occurrences => 3,
instance_name => ‘SALESPDB’,
object_type => ‘TABLESPACE’,
object_name => ‘USERS’
);
END;
/

PL/SQL procedure successfully completed.

3. 配置监控报告
SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;
/

PL/SQL procedure successfully completed.

4. 验证优化效果
SQL> SELECT metrics_id, warning_operator, warning_value,
critical_operator, critical_value
FROM dba_thresholds
WHERE metrics_id = DBMS_SERVER_ALERT.TABLESPACE_USAGE
AND object_name = ‘USERS’;

METRICS_ID WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE
———– ————— ————- —————– ————–
2 GE 85 GE 95

# 优化效果
– 监控配置优化
– 告警阈值优化
– 监控效率提高
– 系统可用性保持

4.3 PDB监控问题处理

在某电商网站的生产环境中，PDB监控出现问题，需要处理。

# 问题现象
– PDB监控不准确
– PDB监控告警过多
– PDB监控性能下降

# 分析步骤
1. 查看PDB状态
SQL> SELECT con_id, name, open_mode, restricted, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
ORDER BY con_id;

CON_ID NAME OPEN_MODE RESTRICTED TOTAL_SIZE FREE_SPACE
—— ——— ———- ———- ———- ———-
1 CDB$ROOT READ WRITE NO 10240 2048
2 PDB$SEED READ ONLY NO 10240 1024
3 SALESPDB READ WRITE NO 10240 512
4 HRPDB READ WRITE NO 10240 2048
5 APP_ROOT READ WRITE NO 10240 1024
6 APP_PDB1 READ WRITE NO 10240 1024

2. 查看告警配置
SQL> SELECT metrics_id, warning_operator, warning_value,
critical_operator, critical_value
FROM dba_thresholds
WHERE metrics_id = DBMS_SERVER_ALERT.TABLESPACE_USAGE
AND object_name = ‘USERS’;

METRICS_ID WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE
———– ————— ————- —————– ————–
2 GE 70 GE 80

3. 查看告警记录
SQL> SELECT reason_type, message, timestamp
FROM dba_alert_history
WHERE object_name = ‘USERS’
ORDER BY timestamp DESC
FETCH FIRST 10 ROWS ONLY;

REASON_TYPE MESSAGE TIMESTAMP
———– ———————————————————————- ——————-
Warning Tablespace USERS is 75 percent full 31-MAR-26 10:00:00
Critical Tablespace USERS is 85 percent full 31-MAR-26 09:55:00
Warning Tablespace USERS is 70 percent full 31-MAR-26 09:50:00

# 问题原因
– 告警阈值设置过低
– PDB存储空间不足
– 监控配置不合理

# 解决方案
1. 调整告警阈值
SQL> BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_USAGE,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => ’85’,
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => ’95’,
observation_period => 1,
consecutive_occurrences => 3,
instance_name => ‘SALESPDB’,
object_type => ‘TABLESPACE’,
object_name => ‘USERS’
);
END;
/

PL/SQL procedure successfully completed.

2. 增加PDB存储空间
SQL> ALTER TABLESPACE users ADD DATAFILE ‘/oracle/app/oracle/oradata/orclcdb/fgfgfgsalespdb/users02.dbf’ SIZE 1G AUTOEXTEND ON;

Tablespace altered.

3. 验证解决效果
SQL> SELECT con_id, name, open_mode, restricted, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
WHERE c.con_id = 3;

CON_ID NAME OPEN_MODE RESTRICTED TOTAL_SIZE FREE_SPACE
—— ——— ———- ———- ———- ———-
3 SALESPDB READ WRITE NO 11264 1536

# 解决效果
– 告警阈值调整成功
– PDB存储空间增加
– 监控配置优化
– 系统可用性恢复

生产环境建议：配置PDB监控前，需要分析业务需求和系统资源，确保监控配置合理。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PDB监控经验

Oracle数据库PDB监控经验：

规划PDB监控架构：根据业务需求规划PDB监控架构，最小化监控开销
配置监控指标：为PDB配置合理的监控指标
监控运行状态：监控PDB运行状态，及时发现和处理问题
定期检查：定期检查PDB监控记录，确保系统可用性
设置告警：设置告警，及时发现运行状态问题
定期review：定期review监控配置，优化管理策略

风哥提示：PDB监控是Oracle数据库多租户架构的核心功能，通过规划PDB监控架构，配置监控指标，监控运行状态，定期检查，设置告警，定期review，可以显著提高系统可用性和数据库管理效率。from oracle:www.itpux.com

5.2 PDB监控检查清单

# PDB监控检查清单
– [ ] 检查系统资源
– [ ] 检查PDB架构
– [ ] 检查监控需求
– [ ] 检查监控配置
– [ ] 检查监控指标
– [ ] 检查告警配置
– [ ] 规划PDB监控架构
– [ ] 配置状态监控
– [ ] 配置性能监控
– [ ] 验证监控功能

# PDB监控问题处理流程
1. 检查系统资源
2. 检查PDB架构
3. 检查监控需求
4. 规划PDB监控架构
5. 配置状态监控
6. 配置性能监控
7. 验证监控功能
8. 处理监控问题
9. 优化配置
10. 监控运行状态使用情况

5.3 PDB监控工具

Oracle数据库PDB监控常用工具：

SQL*Plus：SQL命令行工具，用于执行SQL命令
Oracle Enterprise Manager：图形化管理工具，用于监控和管理数据库
SQL Developer：SQL开发工具，用于开发和调试SQL
AWR报告：自动工作负载仓库报告，用于性能分析
ASH报告：活动会话历史报告，用于性能分析
ADDM：自动数据库诊断监视器，用于性能诊断

持续改进：PDB监控是一个持续的过程，需要定期review和优化。建议建立PDB监控的规范和流程，不断改进系统可用性和数据库管理效率。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

Oracle教程FG380-PDB监控

Part01-基础概念与理论知识

1.1 PDB监控的概念

1.2 PDB监控的方法

1.3 PDB监控的要求

Part02-生产环境规划与建议

2.1 PDB监控规划

2.2 PDB监控设计

2.3 PDB监控最佳实践

Part03-生产环境项目实施方案

3.1 PDB监控实施

3.1.1 配置PDB状态监控

3.1.2 配置PDB性能监控

3.2 PDB监控配置

3.2.1 配置PDB资源监控

3.2.2 配置PDB告警

3.3 PDB监控验证

3.3.1 验证PDB状态监控

3.3.2 验证PDB性能监控

Part04-生产案例与实战讲解

4.1 PDB监控案例

4.2 PDB监控优化案例

4.3 PDB监控问题处理

Part05-风哥经验总结与分享

5.1 PDB监控经验

5.2 PDB监控检查清单

5.3 PDB监控工具

相关推荐

联系我们