CONNECT / AS SYSDBA
-- 设置输出格式 SET LINESIZE 200 SET PAGESIZE 50 COLUMN INST_ID FORMAT 999 COLUMN SERVICE_NAME FORMAT A30 COLUMN ELAPSEDPERCALL FORMAT 999999 COLUMN CPUPERCALL FORMAT 999999 COLUMN DBTIMEPERCALL FORMAT 999999 COLUMN CALLSPERSEC FORMAT 999999 COLUMN DBTIMEPERSEC FORMAT 999999 COLUMN GOODNESS FORMAT 99999 COLUMN GOAL FORMAT A15 COLUMN CLB_GOAL FORMAT A10 COLUMN TOTAL_REQUESTS FORMAT 999999 COLUMN TOTAL_RESPONSES FORMAT 999999 COLUMN AVERAGE_RESPONSE_TIME FORMAT 999999
-- 1. 检查GV$SERVICEMETRIC视图（服务性能指标） PROMPT ====================== PROMPT 1. 服务性能指标 (GV$SERVICEMETRIC) PROMPT ====================== SELECT INST_ID, SERVICE_NAME, ELAPSEDPERCALL, CPUPERCALL, DBTIMEPERCALL, CALLSPERSEC, DBTIMEPERSEC, GOODNESS FROM GV$SERVICEMETRIC ORDER BY INST_ID, SERVICE_NAME;
-- 2. 检查GV$SERVICES视图（服务配置） PROMPT ====================== PROMPT 2. 服务配置 (GV$SERVICES) PROMPT ====================== SELECT INST_ID, NAME AS SERVICE_NAME, GOAL, CLB_GOAL, AQ_HA_NOTIFICATION FROM GV$SERVICES ORDER BY INST_ID, NAME;
-- 3. 检查GV$SERVICE_STATS视图（服务统计信息） PROMPT ====================== PROMPT 3. 服务统计信息 (GV$SERVICE_STATS) PROMPT ====================== SELECT INST_ID, SERVICE_NAME, TOTAL_REQUESTS, TOTAL_RESPONSES, AVERAGE_RESPONSE_TIME FROM GV$SERVICE_STATS WHERE STAT_NAME = 'LOAD_BALANCE' ORDER BY INST_ID, SERVICE_NAME;
-- 4. 检查会话分布（负载均衡效果） PROMPT ====================== PROMPT 4. 会话分布 (GV$SESSION) PROMPT ====================== SELECT INST_ID, SERVICE_NAME, COUNT(*) AS SESSION_COUNT FROM GV$SESSION WHERE SERVICE_NAME IS NOT NULL GROUP BY INST_ID, SERVICE_NAME ORDER BY INST_ID, SERVICE_NAME;
SELECT * FROM GV$SERVICE_STATS WHERE STAT_NAME = 'LOAD_BALANCE' ORDER BY INST_ID, SERVICE_NAME;
Oracle RAC 每日巡检脚本
- 检查集群状态（CRS 服务） bash 复制
crsctl check crs
crsctl stat res -t
crsctl check cluster -all
ocrcheck crsctl query css votedisk 2. 检查数据库实例状态 sql 复制 -- 连接到数据库 CONNECT / AS SYSDBA
-- 检查实例状态 SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM GV$INSTANCE;
-- 检查RAC节点间的连接状态 SELECT INST_ID, INSTANCE_NAME, HOST_NAME, VERSION, STATUS, DATABASE_STATUS FROM GV$INSTANCE;
-- 检查RAC资源状态 SELECT * FROM GV$ACTIVE_SERVICES;
-- 检查RAC负载均衡状态 SELECT INST_ID, SERVICE_NAME, BLOCKED, TOTAL_REQUESTS, TOTAL_RESPONSES FROM GV$SERVICEMETRIC; 3. 检查集群日志（alert.log 和集群日志） bash 复制
tail -n 100 <DIAG_DEST>/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace/alert_<INSTANCE_NAME>.log
tail -n 100 <GRID_HOME>/log//alert.log tail -n 100 <GRID_HOME>/log//crsd/crsd.log tail -n 100 <GRID_HOME>/log//cssd/ocssd.log 4. 检查表空间使用情况 sql 复制 -- 检查表空间使用情况 SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS "Total Size (MB)", ROUND(SUM(BYTES - NVL(FREE_BYTES, 0)) / 1024 / 1024, 2) AS "Used Size (MB)", ROUND(SUM(NVL(FREE_BYTES, 0)) / 1024 / 1024, 2) AS "Free Size (MB)", ROUND((SUM(BYTES - NVL(FREE_BYTES, 0)) / SUM(BYTES)) * 100, 2) AS "Used %" FROM (SELECT TABLESPACE_NAME, BYTES, FREE_BYTES FROM (SELECT TABLESPACE_NAME, SUM(BYTES) AS BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF, (SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FS WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME(+)) GROUP BY TABLESPACE_NAME; 5. 检查归档日志模式及归档状态 sql 复制 -- 检查归档日志模式 SELECT LOG_MODE FROM V$DATABASE;
-- 检查归档日志目的地 SELECT DEST_ID, STATUS, DESTINATION FROM V$ARCHIVE_DEST;
-- 检查归档日志状态 SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY FIRST_TIME DESC; 6. 检查数据库备份状态 sql 复制 -- 检查最近的RMAN备份状态 SELECT * FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE - 1; 7. 检查数据库性能指标 sql 复制 -- 检查数据库性能指标 SELECT METRIC_NAME, VALUE FROM GV$SYSMETRIC WHERE METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') AND GROUP_ID = 2;
-- 检查数据库等待事件 SELECT INST_ID, EVENT, TOTAL_WAITS, TIME_WAITED FROM GV$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQLNet%' ORDER BY TIME_WAITED DESC; 8. 检查数据库锁信息 sql 复制 -- 检查数据库锁信息 SELECT INST_ID, SID, SERIAL#, BLOCKING_SESSION, SQL_ID, EVENT FROM GV$SESSION WHERE BLOCKING_SESSION IS NOT NULL; 9. 检查数据库对象状态 sql 复制 -- 检查数据库对象状态 SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS != 'VALID'; 10. 检查数据库参数设置 sql 复制 -- 检查数据库参数设置 SELECT NAME AS "Parameter", VALUE AS "Value" FROM GV$PARAMETER WHERE NAME IN ('sga_max_size', 'sga_target', 'pga_aggregate_target', 'db_block_size'); 11. 检查数据库连接数 sql 复制 -- 检查数据库连接数 SELECT INST_ID, COUNT() AS "Current Connections" FROM GV$SESSION GROUP BY INST_ID; 12. 检查表空间自动扩展设置 sql 复制 -- 检查表空间自动扩展设置 SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, MAXBYTES FROM DBA_DATA_FILES WHERE AUTOEXTENSIBLE = 'YES'; 13. 检查数据库统计信息 sql 复制 -- 检查数据库统计信息 SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE LAST_ANALYZED IS NULL OR LAST_ANALYZED < SYSDATE - 7; 使用方法 将上述脚本保存为多个文件（如crs_check.sh、db_check.sql等）。
使用crsctl和sqlplus工具分别执行这些脚本。
将输出结果保存到日志文件中，便于后续分析。
bash 复制
./crs_check.sh > crs_check.log
sqlplus / as sysdba @db_check.sql > db_check.log 注意事项 权限要求：执行CRS检查需要具有grid用户权限，数据库检查需要具有sysdba权限。
日志文件路径：确保脚本中的日志文件路径与实际环境一致。
定时任务：可以将巡检脚本配置为定时任务（如cron），每天自动执行并发送报告。
输出分析：定期分析巡检日志，及时发现并解决问题。
通过以上脚本，可以全面检查Oracle RAC环境的健康状况，确保集群和数据库的稳定运行。
方法 1：检查服务分布 sql 复制 SELECT INST_ID, SERVICE_NAME, COUNT(*) AS SESSION_COUNT FROM GV$SESSION WHERE SERVICE_NAME IS NOT NULL GROUP BY INST_ID, SERVICE_NAME ORDER BY INST_ID, SERVICE_NAME; 方法 2：检查服务的目标和负载均衡配置 sql 复制 SELECT INST_ID, NAME, GOAL, CLB_GOAL, AQ_HA_NOTIFICATION FROM GV$SERVICES; GOAL：服务的目标（NONE、SERVICE_TIME、THROUGHPUT）。
CLB_GOAL：连接负载均衡的目标（SHORT、LONG）。
PROMPT 4. 会话分布 (GV$SESSION) PROMPT ====================== SELECT INST_ID, SERVICE_NAME, COUNT(*) AS SESSION_COUNT FROM GV$SESSION WHERE SERVICE_NAME IS NOT NULL GROUP BY INST_ID, SERVICE_NAME ORDER BY INST_ID, SERVICE_NAME;
SET LINESIZE 200 SET PAGESIZE 50 COLUMN INST_ID FORMAT 999 COLUMN SERVICE_NAME FORMAT A30 COLUMN BLOCKED FORMAT A7 COLUMN GOODNESS FORMAT 99999 COLUMN ELAPSEDPERCALL FORMAT 999999 COLUMN CPUPERCALL FORMAT 999999 COLUMN THROUGHPUT FORMAT 999999 COLUMN CALLSPERSEC FORMAT 999999 COLUMN GOAL FORMAT A15 COLUMN CLB_GOAL FORMAT A10 COLUMN TOTAL_REQUESTS FORMAT 999999 COLUMN TOTAL_RESPONSES FORMAT 999999 COLUMN AVERAGE_RESPONSE_TIME FORMAT 999999
SELECT INST_ID, SERVICE_NAME, COUNT(*) AS SESSION_COUNT FROM GV$SESSION WHERE SERVICE_NAME IS NOT NULL GROUP BY INST_ID, SERVICE_NAME ORDER BY INST_ID, SERVICE_NAME;
-- 连接到PDB ALTER SESSION SET CONTAINER = <PDB_NAME>;
-- 设置输出格式 SET LINESIZE 200 SET PAGESIZE 50 COLUMN TABLESPACE_NAME FORMAT A30 COLUMN TOTAL_SIZE_MB FORMAT 999999.99 COLUMN USED_SIZE_MB FORMAT 999999.99 COLUMN FREE_SIZE_MB FORMAT 999999.99 COLUMN USED_PCT FORMAT 999.99
-- 查询PDB的表空间使用情况 SELECT DF.TABLESPACE_NAME, ROUND(SUM(DF.BYTES) / 1024 / 1024, 2) AS TOTAL_SIZE_MB, ROUND(SUM(DF.BYTES - NVL(FS.FREE_BYTES, 0)) / 1024 / 1024, 2) AS USED_SIZE_MB, ROUND(SUM(NVL(FS.FREE_BYTES, 0)) / 1024 / 1024, 2) AS FREE_SIZE_MB, ROUND((SUM(DF.BYTES - NVL(FS.FREE_BYTES, 0)) / SUM(DF.BYTES)) * 100, 2) AS USED_PCT FROM (SELECT TABLESPACE_NAME, SUM(BYTES) AS BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF LEFT JOIN (SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FS ON DF.TABLESPACE_NAME = FS.TABLESPACE_NAME GROUP BY DF.TABLESPACE_NAME ORDER BY DF.TABLESPACE_NAME;
-- 结束 EXIT;
set pages 80 set lin 120 set echo off set feed off column PCT format 999.99 column tbspce format A30 col container for a30 select substr(f.tablespace_name,1,30) tbspce, round(f.tsbytes/(10241024),0) "ALLOCATED(MB)", round(nvl(s.segbytes,0)/(10241024),0) "USED(MB)", round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT, lower(vc.name) as container from (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) f, (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s, v$containers vc where f.con_id=s.con_id(+) and f.tablespace_name=s.tablespace_name(+) and f.con_id=vc.con_id order by container, tbspce;