Oracle DB健康检查手册.pdf

Oracle DB 健康检查  OS 层检查  检查oracle 进程 ps -ef|grep ora|wc -l crs_stat -t -v top glance crsctl check cluster (cssd) vmstat 3600 6   查看基本信息  database 概况信息检查 #检查database 基本信息  SELECT * FROM V$VERSION ; SELECT NAME, OPEN_MODE, LOG_MODE FROM V$DATABASE ; SELECT INSTANCE_NUMBER, INSTANCE_NAME, STATUS FROM GV$INSTANCE ; SHOW PARAMETER CPU_C SHOW PARAMETER BLOCK_SIZE SELECT GROUP#, THREAD#, MEMBERS, BYTES / 1024 / 1024 FROM GV$LOG; SHOW SGA SELECT COUNT (*) FROM V$CONTROLFILE ; SELECT COUNT (*) FROM V$TEMPFILE ; SELECT COUNT (*) FROM V$DATAFILE ;  查看数据文件信息 #检查表空间数据文件信息                         COL TABLESPACE_NAME FOR A30 SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 / 1024 FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME ;   #检查表空间  SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES ;   #检查数据文件状态  SELECT COUNT (*), STATUS FROM V$DATAFILE GROUP BY STATUS ;   #检查表空间使用情况  SELECT F.TABLESPACE_NAME, A.TOTAL, F.FREE, (A.TOTAL - F.FREE) / 1024 used SIZE(G), ROUND ( (F.FREE / A.TOTAL) * 100) % Free FROM ( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024)) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES / (1024 * 1024))) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY % Free;   #查询临时segment 使用情况  COL USERNAME FORMAT A10; COL SEGTYPE FORMAT A10; SELECT USERNAME, SEGTYPE, EXTENTS Extents Allocated, BLOCKS Blocks Allocated