Oracle日常运维（一线DBA必备技能）（四）-综合巡检

1. List item
   在Oracle数据库日常维护中，定期巡检以确保数据库的健康和性能，本文结合我自身经验，总结了些常用并且能够真实反应DB健康状况的项目，并附上相应的巡检SQL语句和脚本，供大家参考。

综合巡检项目（可配置定期自动化任务）：

1. 检查DB实例状况，保证数据库正常的运行；
2. 检查数据库的存储空间，确保有足够的空间用于数据增长；
3. 检查数据文件、日志文件、控制文件、归档日志的完整性和可用性；
4. 检查数据库的版本和补丁级别；
5. 检查是否有过期的备份，并且备份的可恢复性；
6. 检查是否有长时间运行的SQL操作，如果有，它们是否影响性能；
7. 检查是否有未使用的对象，如未使用的表和索引，以便清理；
8. 检查是否有性能瓶颈，如由于缺少索引或统计信息过时导致的；
9. 检查数据库的性能统计，如等待事件和执行计划，以优化性能；
10. 定期运行健康检查脚本或使用Oracle提供的诊断工具进行全面检查；
11. 定期rebuild索引；
12. 定期分析 tables和indexs；
13. 定期检查服务器系统空间使用情况；
14. 定期做数据库的恢复测试；
15. 定期查看alert_SID.log，及时发现并解决数据库出现的问题；

自动化定期巡检脚本

-- 设置SQL*Plus环境
SET ECHO OFF
SET TERMOUT OFF
SET HEADING ON
SET FEEDBACK OFF
SET PAGESIZE 0
SET TIMING OFF
SET TRIMSPOOL ON
-- 1. 检查 DB 实例状况，保证数据库正常的运行
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
-- 2. 检查数据库的存储空间，确保有足够的空间用于数据增长
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS SIZE_GB, SUM(MAXBYTES)/1024/1024/1024 AS MAX_SIZE_GB
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;
-- 3. 检查数据文件、日志文件、控制文件、归档日志的完整性和可用性
-- 数据文件
SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;
-- 日志文件
SELECT GROUP#, STATUS FROM V$LOG;
-- 控制文件
SELECT STATUS FROM V$CONTROLFILE;
-- 归档日志
SELECT DESTINATION, STATUS FROM V$ARCHIVE_DEST_STATUS;
-- 4. 检查数据库的版本和补丁级别
SELECT BANNER FROM V$VERSION;
-- 5. 检查是否有过期的备份，并且备份的可恢复性
-- 查询最近一次备份时间
SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS;
-- 检查备份是否成功
SELECT STATUS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE END_TIME = (SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS);
-- 6. 检查是否有长时间运行的 SQL 操作，如果有，它们是否影响性能
SELECT SQL_ID, SQL_TEXT, ELAPSED_TIME, CPU_TIME
FROM V$SQL
WHERE ELAPSED_TIME > 60 -- 假设 60 秒以上为长时间运行
ORDER BY ELAPSED_TIME DESC;
-- 7. 检查是否有未使用的对象，如未使用的表和索引，以便清理
-- 未使用的表
SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = 'YOUR_SCHEMA_NAME' -- 替换为你的模式名
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_TAB_COL_STATISTICS);
-- 未使用的索引
SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE OWNER = 'YOUR_SCHEMA_NAME' -- 替换为你的模式名
AND INDEX_NAME NOT IN (SELECT INDEX_NAME FROM DBA_IND_COL_USAGE);
-- 8. 检查是否有性能瓶颈，如由于缺少索引或统计信息过时导致的
-- 缺少索引的查询可能导致的高负载 SQL
SELECT SQL_ID, SQL_TEXT, DISK_READS
FROM V$SQL
WHERE DISK_READS > 1000 AND SQL_TEXT NOT LIKE '%INDEX%' -- 假设磁盘读取大于 1000 且 SQL 中不包含 INDEX 可能缺少索引
ORDER BY DISK_READS DESC;
-- 统计信息过时的表
SELECT OWNER, TABLE_NAME
FROM DBA_TABLES
WHERE LAST_ANALYZED IS NULL OR (SYSDATE - LAST_ANALYZED) > 7; -- 假设一周以上未分析可能统计信息过时
-- 9. 检查数据库的性能统计，如等待事件和执行计划，以优化性能
-- 等待事件
SELECT EVENT, TIME_WAITED, WAITS
FROM V$SYSTEM_EVENT
ORDER BY TIME_WAITED DESC;
-- 执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 10. 定期运行健康检查脚本或使用 Oracle 提供的诊断工具进行全面检查
-- 可以使用 Oracle Enterprise Manager 等工具进行全面检查，这里暂不提供脚本。
-- 11. 定期 rebuild 索引
BEGIN
    FOR ind IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'YOUR_SCHEMA_NAME') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX '||ind.INDEX_NAME||' REBUILD';
    END LOOP;
END;
/
-- 12. 定期分析 tables 和 indexs
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS('YOUR_SCHEMA_NAME'); -- 替换为你的模式名
END;
/
-- 13. 定期检查服务器系统空间使用情况
-- 假设数据库服务器为 Linux，可以使用以下脚本来查询磁盘空间使用情况
SELECT df.FILE_SYSTEM_NAME, df.TOTAL_SIZE_MB, df.USED_SIZE_MB, df.FREE_SIZE_MB
FROM (
    SELECT TRIM(df.name) AS FILE_SYSTEM_NAME,
           df.total_bytes/1024/1024 AS TOTAL_SIZE_MB,
           (df.total_bytes - fs.free_bytes)/1024/1024 AS USED_SIZE_MB,
           fs.free_bytes/1024/1024 AS FREE_SIZE_MB
    FROM v$asm_diskgroup df JOIN v$asm_diskgroup_stat fs ON df.group_number = fs.group_number
    WHERE df.name!= 'FRA'
    UNION ALL
    SELECT TRIM(df.name) AS FILE_SYSTEM_NAME,
           df.total_bytes/1024/1024 AS TOTAL_SIZE_MB,
           (df.total_bytes - fs.free_bytes)/1024/1024 AS USED_SIZE_MB,
           fs.free_bytes/1024/1024 AS FREE_SIZE_MB
    FROM v$datafile df JOIN v$filestat fs ON df.file# = fs.file#
    WHERE df.file#!= 0
    UNION ALL
    SELECT TRIM(df.name) AS FILE_SYSTEM_NAME,
           df.total_bytes/1024/1024 AS TOTAL_SIZE_MB,
           (df.total_bytes - fs.free_bytes)/1024/1024 AS USED_SIZE_MB,
           fs.free_bytes/1024/1024 AS FREE_SIZE_MB
    FROM v$tempfile df JOIN v$tempstat fs ON df.file# = fs.file#
    WHERE df.file#!= 0
) df;
-- 14. 定期做数据库的恢复测试
-- 可以使用 RMAN 进行恢复测试，具体脚本根据实际情况而定。
-- 15. 定期查看 alert_SID.log，及时发现并解决数据库出现的问题
-- 可以使用操作系统命令查看告警日志文件， Linux 上：
SELECT SUBSTR(TEXT, INSTR(TEXT, 'ORA-') - 20) AS ERROR_MESSAGE FROM (SELECT TEXT FROM V$DIAG_ALERT_LOG ORDER BY TIMESTAMP DESC) WHERE ROWNUM <= 10;

Oracle运维管理-每天必做巡检项目：

1. 确认实例的状态是否正常：检查oracle后台进程,数据库是否是open状态，数据文件是否都处在online状态下；
2. 检查监听是否正常，告警日志alert.log中是否有报错，如果有错误（ORA-）记录到日报，并解决；
3. 检查文件系统的使用情况，如果文件系统的剩余空间小于20%，删除一些不用的文件；
4. 检查表空间的剩余空间情况；
5. 利用外部命令（操作系统命令）或者工具检查，检查数据库的性能是否有异常，如：top、vmstat、iostat；
6. 检查数据库当日备份的有效性（检查备份日志）；
7. 工作协同：协助开发人员、其它运维人员，协调第三方厂家，帮助开发人员优化SQL，解决开发人员遇到的数据库方面的问题；

Oracle运维管理-每周必做巡检项目：

1. 每周生成awr,addm（statspack），检查load_profile,top 5 time events,SQL order by elapsed time等检查数据库性能是否有问题；
2. 清理和备份一周所产生的Alert日志、跟踪文件、dump文件，清理和备份过期的数据库性能数据，以保证数据库性能监控的连续性和有效性；
3. 空间使用分析，监测数据量总体增长情况，按表空间分析使用率增长情况 ；

Oracle运维管理日-每月必做巡检项目：

1. 每月对表和索引进行analyze，消除行迁移，表空间碎片，对数据库进行一次全面的健康性检查；
2. 每月的性能检查重点关注：数据库主要性能指标、数据库主要等待事件、最消耗内存资源的SQL语句；
3. 每月需要对备份的数据进行恢复演练以保证备份的有效性；

Oracle运维日常巡检方法-手动（不能一味地依赖自动巡检脚本，也要定期手动巡检，提升自己）

1、数据库实例状态检查
查询实例状态：
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
检查数据库是否可访问：
SELECT 1 FROM DUAL;
2、资源使用情况监测
CPU 利用率：

SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'CPU used by this session';

内存使用：
SGA 大小：

SELECT SUM(BYTES) / 1024 / 1024 / 1024 AS SGA_SIZE_GB FROM V$SGA;

PGA 大小：

SELECT VALUE / 1024 / 1024 / 1024 AS PGA_SIZE_GB FROM V$PGASTAT WHERE NAME = 'total PGA allocated';

内存命中率：

SELECT (1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))) * 100 AS BUFFER_CACHE_HIT_RATIO FROM V$BUFFER_POOL_STATISTICS;

磁盘空间：
数据文件所在磁盘空间：

SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 AS SIZE_GB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

控制文件所在磁盘空间：

SELECT NAME, BLOCK_SIZE * FILE_SIZE_BLKS / 1024 / 1024 / 1024 AS SIZE_GB FROM V$CONTROLFILE;

重做日志文件所在磁盘空间：

SELECT GROUP#, BYTES / 1024 / 1024 / 1024 AS SIZE_GB FROM V$LOG;

I/O 性能：
平均磁盘读写时间：

SELECT NAME, VALUE / 1000 AS AVG_DISK_READ_TIME_MS, VALUE / 1000 AS AVG_DISK_WRITE_TIME_MS FROM V$SYSMETRIC WHERE METRIC_NAME IN ('Average Synchronous Single-Block Read Latency', 'Average Synchronous Single-Block Write Latency');

3、数据库备份检查
备份策略执行情况：
查询最近一次备份时间：

SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS;

检查备份是否成功：

SELECT STATUS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE END_TIME = (SELECT MAX(END_TIME) FROM V$RMAN_BACKUP_JOB_DETAILS);

备份存储管理：
查询备份存储设备空间使用情况（假设使用磁带库，需根据实际情况调整）：
– 查询磁带库中已使用空间和总空间（示例，实际情况需根据磁带库管理系统查询）

SELECT USED_SPACE_GB, TOTAL_SPACE_GB FROM TAPE_LIBRARY_STATUS;

4、数据库安全检查
用户权限管理：
查询用户权限：

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS;

查询用户密码过期时间：

SELECT USERNAME, PASSWORD_EXPIRY_DATE FROM DBA_USERS;

数据库访问控制：
查询数据库连接方式：

SELECT NETWORK_SERVICE_BANNER FROM V$SESSION_CONNECT_INFO WHERE SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);

查询数据库监听状态：

SELECT STATUS FROM V$LISTENER;

数据库审计：
查询审计设置：

SELECT * FROM DBA_AUDIT_PARAMETERS;

查询审计日志：

SELECT * FROM DBA_AUDIT_TRAIL;

5、数据库性能优化
SQL 语句优化：
查询执行时间较长的 SQL 语句：

SELECT SQL_ID, SQL_TEXT, ELAPSED_TIME, CPU_TIME FROM V$SQL ORDER BY ELAPSED_TIME DESC;

查询全表扫描的 SQL 语句：

SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE DISK_READS > 1000 AND OPERATOR = 'TABLE ACCESS FULL';

数据库参数调整：
查询内存参数：

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME IN ('sga_max_size', 'sga_target', 'pga_aggregate_target');

查询连接池大小：

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'processes';

数据库结构优化：
查询表分区信息：

SELECT TABLE_NAME, PARTITIONING_TYPE FROM DBA_PART_TABLES;

查询索引使用情况：

SELECT INDEX_NAME, TABLE_NAME, NUM_ROWS, LEAF_BLOCKS FROM DBA_INDEXES;

6、数据库日志分析
数据库告警日志：
查看告警日志最后若干行行（根据实际情况调整行数）（可参考我之前的文章：《linux下DB及系统日常巡检及故障排查log查看技巧分享（一）》）：

SELECT SUBSTR(TEXT, INSTR(TEXT, 'ORA-') - 20) AS ERROR_MESSAGE FROM (SELECT TEXT FROM V$DIAG_ALERT_LOG ORDER BY TIMESTAMP DESC) WHERE ROWNUM <= 10;

数据库跟踪日志：
查询跟踪日志文件位置（需根据实际情况调整）：

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest';

本篇完。
码字不易，宝贵经验分享不易，请各位支持原创，转载注明出处，多多关注作者，后续不定期分享DB核心知识和排障案例及经验、性能调优等。*