1、XXXXXXXX數(shù)據(jù)庫系統(tǒng)巡檢報(bào)告福華網(wǎng)絡(luò)2017年2月15日目錄1文檔控制12巡檢記錄13巡檢概述24巡檢結(jié)果記錄25檢查項(xiàng)目分析與建議36數(shù)據(jù)庫實(shí)例檢查46.1獲取實(shí)例基本信息46.1.1查看實(shí)例啟動(dòng)時(shí)間，運(yùn)行狀態(tài)46.1.2查看系統(tǒng)會(huì)話數(shù)46.2查看數(shù)據(jù)庫文件狀態(tài)46.2.1查看日志文件狀態(tài)46.2.2查看表空間狀態(tài)56.2.3查看表空間使用率66.2.4查看數(shù)據(jù)庫文件狀態(tài)76.3查看數(shù)據(jù)對(duì)象狀態(tài)76.3.1查看異常用戶對(duì)象76.3.2查看系統(tǒng)表空間的非系統(tǒng)對(duì)象86.3.3查找失效的索引86.3.4查找失效的約束86.3.5查找失效的觸發(fā)器86.4使用AWR收集實(shí)例信息97RAC健康檢查

2、107.1RAC運(yùn)行狀態(tài)信息107.2ASM磁盤容量檢查118DATAGUARD健康檢查138.1主庫確認(rèn)沒有日志缺口138.2確認(rèn)主庫可以進(jìn)行角色切換，查詢主庫138.3查詢備庫是否可以切換為主庫，查詢備庫：131 文檔控制n 修改記錄作者修改記錄日期版本2 巡檢記錄第四次巡檢巡檢時(shí)間巡檢人巡檢概況備注第三次巡檢巡檢時(shí)間巡檢人巡檢概況備注第二次巡檢巡檢時(shí)間巡檢人巡檢概況備注第一次巡檢巡檢時(shí)間2017-2-14巡檢人丁智君巡檢概況系統(tǒng)運(yùn)行基本正常備注3 巡檢概述2017年2月14日，福華網(wǎng)絡(luò)對(duì)XXXXXXX現(xiàn)運(yùn)行的維保合同相關(guān)Oracle數(shù)據(jù)庫系統(tǒng)進(jìn)行了一次全面細(xì)致的巡檢服務(wù)。巡檢范圍包括數(shù)據(jù)

3、庫服務(wù)器操作系統(tǒng)和Oracle RAC數(shù)據(jù)庫。目前，數(shù)據(jù)庫服務(wù)器和數(shù)據(jù)庫運(yùn)行穩(wěn)定。4 巡檢結(jié)果記錄合同相關(guān)的數(shù)據(jù)庫系統(tǒng)巡檢結(jié)果記錄如下表所示：服務(wù)器IP地址數(shù)據(jù)庫實(shí)例名數(shù)據(jù)庫版本是否為RAC節(jié)點(diǎn)是否配置DG運(yùn)行狀態(tài)5 檢查項(xiàng)目分析與建議系統(tǒng)當(dāng)前運(yùn)行狀態(tài)良好。6 數(shù)據(jù)庫實(shí)例檢查6.1 獲取實(shí)例基本信息6.1.1 查看實(shí)例啟動(dòng)時(shí)間，運(yùn)行狀態(tài)啟動(dòng)時(shí)間為實(shí)例最近一次啟動(dòng)時(shí)間，該時(shí)間變化表明實(shí)例有重啟動(dòng)作，需要關(guān)注。實(shí)例運(yùn)行狀態(tài)應(yīng)為OPEN，數(shù)據(jù)庫狀態(tài)應(yīng)為ACTIVE。否則數(shù)據(jù)庫運(yùn)行狀態(tài)異常。SQL select instance_name ,startup_time,status,database_s

4、tatus 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 查看系統(tǒng)會(huì)話數(shù)系統(tǒng)會(huì)話數(shù)應(yīng)和基線參數(shù)基本一致，過高和過低以為著系統(tǒng)存在異常。SQL set line 80SQL select inst_id,count(*) from gv$session group by inst_id; INST_ID COUNT(*)- - 1

5、89 2 89 3 906.2 查看數(shù)據(jù)庫文件狀態(tài)6.2.1 查看日志文件狀態(tài)正常狀態(tài)為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_

6、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 ONL

7、INE +REDOLOG/xtbg/onlinelog/redo_11_02.log12 ONLINE +REDOLOG/xtbg/onlinelog/redo_12_01.log36 rows selected.6.2.2 查看表空間狀態(tài)正常狀態(tài)為ONLINE。SQL select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS- -SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUNDOTBS2 ONLINEUNDOTBS3 ONLINEUSERS

8、 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 查看表空間使

9、用率使用率增長過快，或者使用率過高，應(yīng)及時(shí)處理。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 94TA

10、BLESPACE_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 查看數(shù)據(jù)庫文件狀態(tài)正常狀態(tài)為ONLINE。SQL col name format a60;SQLselect name,sta

11、tus 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

12、. 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 查看數(shù)據(jù)對(duì)象狀態(tài)6.3.1 查看異常用戶對(duì)象確認(rèn)對(duì)象異常原因。SQL SQL col owner format a15;col object_name format a40;select owner,object_name,object_type fro

13、m 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 PROCEDUREXTBGTR

14、ANS2REANDHIS PROCEDUREXTBGUPDATESTATICSDATA PROCEDURE8 rows selected.6.3.2 查看系統(tǒng)表空間的非系統(tǒng)對(duì)象如果存在，應(yīng)及時(shí)遷移至相應(yīng)的用戶表空間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

15、 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

16、 WHERE status = DISABLE and constraint_type = P;no rows selected6.3.5 查找失效的觸發(fā)器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- - - -

17、SYS LOGMNRGGC_TRIGGER DISABLEDWMSYS NO_VM_DDL DISABLEDWMSYS NO_VM_DROP_A DISABLED6.4 使用AWR收集實(shí)例信息使用oracle用戶登錄，在sqlplus中使用ORACLE AWR收集工作時(shí)間段的性能數(shù)據(jù)。缺省設(shè)置時(shí)AWR可以收集7天以內(nèi)的歷史信息。oraclextbgdb1 $ sqlplus / as sysdbaSQL?/rdbms/admin/awrrpt.sql.Enter value for report_type: Type Specified: html.Specify the number of

18、days of snapshots to choose from7 RAC健康檢查7.1 RAC運(yùn)行狀態(tài)信息通過ssh客戶端工具（putty，securecrt等）連接至數(shù)據(jù)庫服務(wù)器。使用grid用戶登錄到RAC任意節(jié)點(diǎn)，使用crsctl工具查看各資源狀態(tài)。正常狀態(tài)如下所示。除了ora.gsd資源組的狀態(tài)為OFFLINE，其余資源組狀態(tài)在所有節(jié)點(diǎn)均應(yīng)為ONLINE；ora.asm資源在所有節(jié)點(diǎn)均為Started；ora.xtbg.db所有實(shí)例狀態(tài)均為Open。gridxtbgdb1 bin$ /u01/app/11.2.0/grid/bin/crsctl stat res -t-NAME TA

19、RGET 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 O

20、NLINE 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

21、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_SCA

22、N1.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 O

23、NLINE xtbgdb2 ora.xtbgdb3.vip 1 ONLINE ONLINE xtbgdb3 7.2 ASM磁盤容量檢查使用grid用戶登錄，使用asmcmd工具查看磁盤組狀態(tài)。所有磁盤組的狀態(tài)應(yīng)為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列為磁盤組可用空間，當(dāng)該值變化過快意味著可用空間