Oracle数据库系统巡检报告

客户名称：XXXXXXXX文件名称：系统巡检报告XXXXXXXX数据库系统巡检报告福华网络2017年2月15日目录1文档控制12巡检记录13巡检概述24巡检结果记录25检查项目分析与建议36数据库实例检查46.1获取实例基本信息46.1.1查看实例启动时间，运行状态46.1.2查看系统会话数46.2查看数据库文件状态46.2.1查看日志文件状态46.2.2查看表空间状态56.2.3查看表空间使用率66.2.4查看数据库文件状态76.3查看数据对象状态76.3.1查看异常用户对象76.3.2查看系统表空间的非系统对象86.3.3查找失效的索引86.3.4查找失效的约束86.3.5查找失效的触发器86.4使用AWR收集实例信息97RAC健康检查107.1RAC运行状态信息107.2ASM磁盘容量检查118DATAGUARD健康检查138.1主库确认没有日志缺口138.2确认主库可以进行角色切换，查询主库138.3查询备库是否可以切换为主库，查询备库：131 文档控制n 修改记录作者修改记录日期版本2 巡检记录第四次巡检巡检时间巡检人巡检概况备注第三次巡检巡检时间巡检人巡检概况备注第二次巡检巡检时间巡检人巡检概况备注第一次巡检巡检时间2017-2-14巡检人丁智君巡检概况系统运行基本正常备注第 13 页3 巡检概述2017年2月14日，福华网络对XXXXXXX现运行的维保合同相关Oracle数据库系统进行了一次全面细致的巡检服务。巡检范围包括数据库服务器操作系统和Oracle RAC数据库。目前，数据库服务器和数据库运行稳定。4 巡检结果记录合同相关的数据库系统巡检结果记录如下表所示：服务器IP地址数据库实例名数据库版本是否为RAC节点是否配置DG运行状态5 检查项目分析与建议系统当前运行状态良好。6 数据库实例检查6.1 获取实例基本信息6.1.1 查看实例启动时间，运行状态启动时间为实例最近一次启动时间，该时间变化表明实例有重启动作，需要关注。实例运行状态应为OPEN，数据库状态应为ACTIVE。否则数据库运行状态异常。SQL select instance_name ,startup_time,status,database_status from gv$instance; INSTANCE_NAME STARTUP_T STATUSDATABASE_STATUS- - - -xtbg1 30-JUL-16 OPEN ACTIVExtbg3 30-JUL-16 OPEN ACTIVExtbg2 30-JUL-16 OPEN ACTIVE6.1.2 查看系统会话数系统会话数应和基线参数基本一致，过高和过低以为着系统存在异常。SQL set line 80SQL select inst_id,count(*) from gv$session group by inst_id; INST_ID COUNT(*)- - 1 89 2 89 3 906.2 查看数据库文件状态6.2.1 查看日志文件状态正常状态为ONLINE。SQL col member format a40;select group#,status,type,member from v$logfile; SQL GROUP# STATUS TYPE MEMBER- - - - 7 ONLINE +REDOLOG/xtbg/onlinelog/redo_07_01.log 7 ONLINE +REDOLOG/xtbg/onlinelog/redo_07_02.log 8 ONLINE +REDOLOG/xtbg/onlinelog/redo_08_01.log 8 ONLINE +REDOLOG/xtbg/onlinelog/redo_08_02.log 9 ONLINE +REDOLOG/xtbg/onlinelog/redo_09_01.log 9 ONLINE +REDOLOG/xtbg/onlinelog/redo_09_02.log10 ONLINE +REDOLOG/xtbg/onlinelog/redo_10_01.log10 ONLINE +REDOLOG/xtbg/onlinelog/redo_10_02.log11 ONLINE +REDOLOG/xtbg/onlinelog/redo_11_01.log11 ONLINE +REDOLOG/xtbg/onlinelog/redo_11_02.log12 ONLINE +REDOLOG/xtbg/onlinelog/redo_12_01.log36 rows selected.6.2.2 查看表空间状态正常状态为ONLINE。SQL select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS- -SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUNDOTBS2 ONLINEUNDOTBS3 ONLINEUSERS ONLINEXTBG_DATA ONLINEXTBG ONLINEENGINE_ADB ONLINEENGINE_AFB ONLINETABLESPACE_NAME STATUS- -ENGINE_AHD ONLINEENGINE_AHZ ONLINEENGINE_AXB ONLINEENGINE_AZS ONLINEENGINE_DATA ONLINEENGINE_IDX ONLINEFC_DATA ONLINEFC_INDX ONLINEUNV_DATA ONLINEUNV_INDX ONLINEENGINE_AHB ONLINE22 rows selected.6.2.3 查看表空间使用率使用率增长过快，或者使用率过高，应及时处理。TABLESPACE_NAME TOTALFREE % Free- - - -SYSAUX 4110 199 5SYSTEM 700 148 21UNDOTBS3 30720 13923 45ENGINE_AHB 61440 28968 47UNDOTBS1 30720 15680 51UNDOTBS2 30720 20952 68ENGINE_AHD 91507 74ENGINE_AZS 30720 27969 91ENGINE_IDX 92ENGINE_AFB 30720 28927 94ENGINE_AXB 92160 86754 94TABLESPACE_NAME TOTALFREE % Free- - - -ENGINE_ADB 92160 87568 95ENGINE_AHZ 95XTBG_DATA 102409723 95XTBG 96ENGINE_DATA 96UNV_DATA 40960 40720 99USERS 105 104 99FC_INDX 61440 60942 99FC_DATA 100UNV_INDX 15360 15328 10021 rows selected.6.2.4 查看数据库文件状态正常状态为ONLINE。SQL col name format a60;SQLselect name,status from v$datafile;NAME STATUS- -+DATA/xtbg/datafile/system.256. SYSTEM+DATA/xtbg/datafile/sysaux.261. ONLINE+DATA/xtbg/datafile/undotbs1.257. ONLINE+DATA/xtbg/datafile/undotbs2.260. ONLINE+DATA/xtbg/datafile/undotbs3.262. ONLINE+DATA/xtbg/datafile/users.259. ONLINE+DATA/xtbg/datafile/xtbg_data.264. ONLINE+DATA/xtbg/datafile/xtbg.265. ONLINE+DATA/xtbg/datafile/xtbg.266. ONLINE+DATA/xtbg/datafile/xtbg.267. ONLINE+DATA/xtbg/datafile/xtbg.268. ONLINE61 rows selected.6.3 查看数据对象状态6.3.1 查看异常用户对象确认对象异常原因。SQL SQL col owner format a15;col object_name format a40;select owner,object_name,object_type from dba_objects where status!=VALID and owner!=SYS and owner!=SYSTEM;SQL SQL OWNEROBJECT_NAME OBJECT_TYPE- - -R7R7_CMP PROCEDUREXTBGTESTFLOW_DATA_TO_HIS PROCEDUREXTBGTESTFLOW_DATA_TO_HIS_ARC PROCEDUREXTBGTESTTRANS2REANDHIS PROCEDUREXTBGFLOW_DATA_TO_HIS PROCEDUREXTBGFLOW_DATA_TO_HIS_ARC PROCEDUREXTBGTRANS2REANDHIS PROCEDUREXTBGUPDATESTATICSDATA PROCEDURE8 rows selected.6.3.2 查看系统表空间的非系统对象如果存在，应及时迁移至相应的用户表空间SQL select distinct (owner) from dba_tables where tablespace_name = SYSTEM and owner != SYS and owner != SYSTEM union select distinct (owner) from dba_indexes where tablespace_name = SYSTEM and owner != SYS and owner != SYSTEM;OWNER-OUTLN6.3.3 查找失效的索引如果存在需重建索引。SQL select index_name, table_name, tablespace_name, status From dba_indexes Where owner = XTBG And status VALID;no rows selected6.3.4 查找失效的约束SQL SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status = DISABLE and constraint_type = P;no rows selected6.3.5 查找失效的触发器SQL set line 200;set table_name format a30;SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = DISABLED;SQL SP2-0158: unknown SET option table_nameSQL OWNER TRIGGER_NAME TABLE_NAME STATUS- - - -SYS LOGMNRGGC_TRIGGER DISABLEDWMSYS NO_VM_DDL DISABLEDWMSYS NO_VM_DROP_A DISABLED6.4 使用AWR收集实例信息使用oracle用户登录，在sqlplus中使用ORACLE AWR收集工作时间段的性能数据。缺省设置时AWR可以收集7天以内的历史信息。oraclextbgdb1 $ sqlplus / as sysdbaSQL?/rdbms/admin/awrrpt.sql.Enter value for report_type: Type Specified: html.Specify the number of days of snapshots to choose from7 RAC健康检查7.1 RAC运行状态信息通过ssh客户端工具（putty，securecrt等）连接至数据库服务器。使用grid用户登录到RAC任意节点，使用crsctl工具查看各资源状态。正常状态如下所示。除了ora.gsd资源组的状态为OFFLINE，其余资源组状态在所有节点均应为ONLINE；ora.asm资源在所有节点均为Started；ora.xtbg.db所有实例状态均为Open。gridxtbgdb1 bin$ /u01/app/11.2.0/grid/bin/crsctl stat res -t-NAME TARGET STATE SERVER STATE_DETAILS-Local Resources-ora.ARCH.dg ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 ora.DATA.dg ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 ora.LISTENER.lsnr ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 ora.OCR.dg ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 ora.REDOLOG.dg ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 ora.asm ONLINE ONLINE xtbgdb1 Started ONLINE ONLINE xtbgdb2 Started ONLINE ONLINE xtbgdb3 Started ora.gsd OFFLINE OFFLINE xtbgdb1 OFFLINE OFFLINE xtbgdb2 OFFLINE OFFLINE xtbgdb3 work ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 ora.ons ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 ora.registry.acfs ONLINE ONLINE xtbgdb1 ONLINE ONLINE xtbgdb2 ONLINE ONLINE xtbgdb3 -Cluster Resources-ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE xtbgdb2 ora.cvu 1 ONLINE ONLINE xtbgdb2 ora.oc4j 1 ONLINE ONLINE xtbgdb2 ora.scan1.vip 1 ONLINE ONLINE xtbgdb2 ora.xtbg.db 1 ONLINE ONLINE xtbgdb1 Open 2 ONLINE ONLINE xtbgdb2 Open 3 ONLINE ONLINE xtbgdb3 Open ora.xtbgdb1.vip 1 ONLINE ONLINE xtbgdb1 ora.xtbgdb2.vip 1 ONLINE ONLINE xtbgdb2 ora.xtbgdb3.vip 1 ONLINE ONLINE xtbgdb3 7.2 ASM磁盘容量检查使用grid用户登录，使用asmcmd工具查看磁盘组状态。所有磁盘组的状态应为MOUNTED。gridxtbgdb1 $ asmcmdASMCMD lsdgState Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files NameMOUNTED EXTERN N 512 4096 0 0 N ARCH/MOUNTED EXTERN N 512 4096 0 0 N DATA/MOUNTED EXTERN N 512 4096 15360 14919 0 14919 0 Y OCR/MOUNTED EXTERN N 512 4096 65294 0 65294 0 N REDOLOG/Usable_file_MB列为磁盘组可用空间，当该值变化过快意味着可