Oracle-數(shù)據(jù)庫日常巡檢

1、Oracle 數(shù)據(jù)庫日常巡檢閱讀目錄1. 檢查數(shù)據(jù)庫基本狀況2. 檢查Oracle相關(guān)資源的使用情況3. 檢查Oracle數(shù)據(jù)庫備份結(jié)果4. 檢查Oracle數(shù)據(jù)庫性能5. 檢查數(shù)據(jù)庫cpu、I/O、內(nèi)存性能6. 檢查數(shù)據(jù)庫安全性7. 其他檢查回到頂部1. 檢查數(shù)據(jù)庫基本狀況包含：檢查Oracle實例狀態(tài)，檢查Oracle服務(wù)進(jìn)程，檢查Oracle監(jiān)聽進(jìn)程，共三個部分。1.1. 檢查Oracle實例狀態(tài)select instance_name,host_name,startup_time,status,database_status from v$instance; 其中“STATUS”表示O

2、racle當(dāng)前的實例狀態(tài)，必須為“OPEN”；“DATABASE_STATUS”表示Oracle當(dāng)前數(shù)據(jù)庫的狀態(tài)，必須為“ACTIVE”。1.2. 檢查Oracle在線日志狀態(tài)select group#,status,type,member from v$logfile; 輸出結(jié)果應(yīng)該有3條以上（包含3條）記錄，“STATUS”應(yīng)該為非“INVALID”，非“DELETED”。注：“STATUS”顯示為空表示正常。1.3. 檢查Oracle表空間的狀態(tài)select tablespace_name,status from dba_tablespaces; 輸出結(jié)果中STATUS應(yīng)該都為ONLIN

3、E。1.4. 檢查Oracle所有數(shù)據(jù)文件狀態(tài)select name,status from v$datafile;輸出結(jié)果中“STATUS”應(yīng)該都為“ONLINE”?；蛘撸簊elect file_name,status from dba_data_files; 輸出結(jié)果中“STATUS”應(yīng)該都為“AVAILABLE”。1.5. 檢查無效對象select owner,object_name,object_type from dba_objects where status!=VALID and owner!=SYS and owner!=SYSTEM;如果有記錄返回，則說明存在無效對象。若這些

4、對象與應(yīng)用相關(guān)，那么需要重新編譯生成這個對象，或者：SELECT owner, object_name, object_type FROM dba_objects WHERE status= INVALID;1.6. 檢查所有回滾段狀態(tài)select segment_name,status from dba_rollback_segs;輸出結(jié)果中所有回滾段的“STATUS”應(yīng)該為“ONLINE”。 回到頂部2. 檢查Oracle相關(guān)資源的使用情況包含：a.檢查Oracle初始化文件中相關(guān)的參數(shù)值b.檢查數(shù)據(jù)庫連接情況，檢查系統(tǒng)磁盤空間c.檢查Oracle各個表空間使用情況，檢查一些擴(kuò)展異常的對象

5、，d.檢查system表空間內(nèi)的內(nèi)容，檢查對象的下一擴(kuò)展與表空間的最大擴(kuò)展值，總共七個部分。2.1. 檢查Oracle初始化文件中相關(guān)參數(shù)值select resource_name,max_utilization,initial_allocation, limit_valuefrom v$resource_limit;若LIMIT_VALU-MAX_UTILIZATION= 95order By Percent;復(fù)制代碼如果有記錄返回，則這些對象的擴(kuò)展已經(jīng)快達(dá)到它定義時的最大擴(kuò)展值。對于這些對象要修改它的存儲結(jié)構(gòu)參數(shù)。2.6. 檢查system表空間內(nèi)的內(nèi)容復(fù)制代碼select distinc

6、t (owner)from dba_tableswhere tablespace_name = SYSTEMand owner != SYSand owner != SYSTEMunionselect distinct (owner)from dba_indexeswhere tablespace_name = SYSTEMand owner != SYSand owner != SYSTEM;復(fù)制代碼如果記錄返回，則表明system表空間內(nèi)存在一些非system和sys用戶的對象。應(yīng)該進(jìn)一步檢查這些對象是否與我們應(yīng)用相關(guān)。如果相關(guān)請把這些對象移到非System表空間，同時應(yīng)該檢查這些對象屬主

7、的缺省表空間值。2.7. 檢查對象的下一擴(kuò)展與表空間的最大擴(kuò)展值復(fù)制代碼select a.table_name, a.next_extent, a.tablespace_namefrom all_tables a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent f.big_chunkunionselect a.index_nam

8、e, a.next_extent, a.tablespace_namefrom all_indexes a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent f.big_chunk;復(fù)制代碼如果有記錄返回，則表明這些對象的下一個擴(kuò)展大于該對象所屬表空間的最大擴(kuò)展值，需調(diào)整相應(yīng)表空間的存儲參數(shù)。 回到頂部3. 檢查Oracle數(shù)據(jù)庫

9、備份結(jié)果包含：a.檢查數(shù)據(jù)庫備份日志信息;b.檢查backup卷中文件產(chǎn)生的時間;c.檢查oracle用戶的email3.1. 檢查數(shù)據(jù)庫備份日志信息假設(shè)：備份的臨時目錄為/backup/hotbakup，我們需要檢查2009年7月22日的備份結(jié)果，則用下面的命令來檢查：cat /backup/hotbackup/hotbackup-09-7-22.log|grep i error備份腳本的日志文件為hotbackup-月份-日期-年份.log，在備份的臨時目錄下面。如果文件中存在“ERROR:”，則表明備份沒有成功，存在問題需要檢查。3.2. 檢查backup卷中文件產(chǎn)生的時間#ls lt

10、/backup/hotbackupbackup卷是備份的臨時目錄，查看輸出結(jié)果中文件的日期，都應(yīng)當(dāng)是在當(dāng)天凌晨由熱備份腳本產(chǎn)生的。如果時間不對則表明熱備份腳本沒執(zhí)行成功。3.3. 檢查oracle用戶的email#tail n 300 /var/mail/oracle熱備份腳本是通過Oracle用戶的cron去執(zhí)行的。cron執(zhí)行完后操作系統(tǒng)就會發(fā)一條Email通知Oracle用戶任務(wù)已經(jīng)完成。查看Oracle email中今天凌晨部分有無ORA-，Error，F(xiàn)ailed等出錯信息，如果有則表明備份不正常。 回到頂部4. 檢查Oracle數(shù)據(jù)庫性能在本節(jié)主要檢查Oracle數(shù)據(jù)庫性能情況，包

11、含：檢查數(shù)據(jù)庫的等待事件，檢查死鎖及處理，檢查cpu、I/O、內(nèi)存性能，查看是否有僵死進(jìn)程，檢查行鏈接/遷移，定期做統(tǒng)計分析，檢查緩沖區(qū)命中率，檢查共享池命中率，檢查排序區(qū)，檢查日志緩沖區(qū)，總共十個部分。4.1. 檢查數(shù)據(jù)庫的等待事件復(fù)制代碼set pages 80set lines 120col event for a40select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT from v$session_wait where event not like SQL% and event not like rdbms%;復(fù)制代碼如果數(shù)

12、據(jù)庫長時間持續(xù)出現(xiàn)大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件時，需要對其進(jìn)行分析，可能存在問題的語句。4.2. Disk Read最高的SQL語句的獲取SELECT SQL_TEXTFROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)WHERE ROWNUM = 5;4.3. 查找前十條性能差的sql復(fù)制代碼SELECT *FROM (SELECT PARSING_USER_ID EXECUTIONS,SOR

13、TS,COMMAND_TYPE,DISK_READS, SQL_TEXTFROM V$SQLAREAORDER BY DISK_READS DESC)WHERE ROWNUM 10;復(fù)制代碼4.4. 等待時間最多的5個系統(tǒng)等待事件的獲取SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE SQL% ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM = 5;4.5. 檢查運(yùn)行很久的SQL復(fù)制代碼COLUMN USERNAME FORMAT A12COLUMN OPNAME FORMAT A1

14、6COLUMN PROGRESS FORMAT A8SELECT USERNAME, SID, OPNAME, ROUND(SOFAR * 100 / TOTALWORK, 0) | % AS PROGRESS, TIME_REMAINING, SQL_TEXT FROM V$SESSION_LONGOPS, V$SQL WHERE TIME_REMAINING 0 AND SQL_ADDRESS = ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;復(fù)制代碼4.6. 檢查消耗CPU最高的進(jìn)程復(fù)制代碼SET LINE 240SET VERIFY OFFCOLUMN

15、 SID FORMAT 999COLUMN PID FORMAT 999COLUMN S_# FORMAT 999COLUMN USERNAME FORMAT A9 HEADING ORA USERCOLUMN PROGRAM FORMAT A29COLUMN SQL FORMAT A60COLUMN OSNAME FORMAT A9 HEADING OS USERSELECT P.PID PID, S.SID SID, P.SPID SPID, S.USERNAME USERNAME, S.OSUSER OSNAME, P.SERIAL# S_#, P.TERMINAL, P.PROGRAM

16、 PROGRAM, P.BACKGROUND, S.STATUS, RTRIM(SUBSTR(A.SQL_TEXT, 1, 80) SQLFROM V$PROCESS P, V$SESSION S, V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE %&1%;復(fù)制代碼4.7. 檢查碎片程度高的表復(fù)制代碼SELECT segment_name table_name, COUNT(*) extents FROM dba_segments WHERE owner NOT IN (SY

17、S, SYSTEM) GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*) FROM dba_segments GROUP BY segment_name);復(fù)制代碼4.8. 檢查表空間的I/O比例復(fù)制代碼SELECT DF.TABLESPACE_NAME NAME, DF.FILE_NAME FILE, F.PHYRDS PYR, F.PHYBLKRD PBR, F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# =

18、 DF.FILE_ID ORDER BY DF.TABLESPACE_NAME; 復(fù)制代碼4.9. 檢查文件系統(tǒng)的I/O比例復(fù)制代碼SELECT SUBSTR(A.FILE#, 1, 2) #, SUBSTR(A.NAME, 1, 30) NAME, A.STATUS, A.BYTES, B.PHYRDS, B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#; 復(fù)制代碼4.10.檢查死鎖及處理查詢目前鎖對象信息：復(fù)制代碼select sid, serial#, username, SCHEMANAME, osuse

19、r, MACHINE, terminal, PROGRAM, owner, object_name, object_type, o.object_id from dba_objects o, v$locked_object l, v$session s where o.object_id = l.object_id and s.sid = l.session_id; 復(fù)制代碼oracle級kill掉該session：alter system kill session &sid,&serial#;操作系統(tǒng)級kill掉session：#kill -9 pid 回到頂部5. 檢查數(shù)據(jù)庫cpu、I/O

20、、內(nèi)存性能記錄數(shù)據(jù)庫的cpu使用、IO、內(nèi)存等使用情況，使用vmstat,iostat,sar,top等命令進(jìn)行信息收集并檢查這些信息，判斷資源使用情況。5.1 CPU使用情況：rootsale8 # toptop - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37, 0.38, 0.29Tasks: 353 total, 2 running, 351 sleeping, 0 stopped, 0 zombieCpu(s): 1.2% us, 0.1% sy, 0.0% ni,98.8% id, 0.0% wa, 0.0% hi,

21、0.0% siMem: k total, k used, k free, 60796k buffersSwap: k total, k used, k free, k cachedPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND30495 oracle 15 0 8329m 866m 861m R 10 5.4 7:53.90 oracle32501 oracle 15 0 8328m 1.7g 1.7g S 2 10.6 1:58.38 oracle32503 oracle 15 0 8329m 1.6g 1.6g S 2 10.2

22、2:06.62 oracle注意上面的藍(lán)色字體部分，此部分內(nèi)容表示系統(tǒng)剩余的cpu，當(dāng)其平均值下降至10%以下的時視為CPU使用率異常，需記錄下該數(shù)值，并將狀態(tài)記為異常。5.2 內(nèi)存使用情況：# free -mtotal used free shared buffers cachedMem: 2026 1958 67 0 76 1556-/+ buffers/cache: 326 1700Swap: 5992 92 5900如上所示，藍(lán)色部分表示系統(tǒng)總內(nèi)存，紅色部分表示系統(tǒng)使用的內(nèi)存，黃色部分表示系統(tǒng)剩余內(nèi)存，當(dāng)剩余內(nèi)存低于總內(nèi)存的10%時視為異常。5.3 系統(tǒng)I/O情況：# iostat -

23、k 1 3Linux 2.6.9-22.ELsmp (AS14) 07/29/2009avg-cpu: %user %nice %sys%iowait %idle0.16 0.00 0.05 0.36 99.43Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnsda 3.33 13.16 50.25 avg-cpu: %user %nice %sys%iowait %idle0.00 0.00 0.00 0.00 100.00Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnsda 0.00 0.00 0

24、.00 0 0如上所示，藍(lán)色字體部分表示磁盤讀寫情況，紅色字體部分為cpu IO等待情況。5.4 系統(tǒng)負(fù)載情況：#uptime12:08:37 up 162 days, 23:33, 15 users, load average: 0.01, 0.15, 0.10如上所示，藍(lán)體字部分表示系統(tǒng)負(fù)載，后面的3個數(shù)值如果有高于2.5的時候就表明系統(tǒng)在超負(fù)荷運(yùn)轉(zhuǎn)了，并將此值記錄到巡檢表，視為異常。5.5.查看是否有僵死進(jìn)程select spid from v$process where addr not in (select paddr from v$session); 有些僵尸進(jìn)程有阻塞其他業(yè)務(wù)的正

25、常運(yùn)行，定期殺掉僵尸進(jìn)程。5.6.檢查行鏈接/遷移select table_name, num_rows, chain_cnt From dba_tables Where owner = CTAIS2 And chain_cnt 0; 注：含有l(wèi)ong raw列的表有行鏈接是正常的,找到遷移行保存到chained_rows表中,如沒有該表執(zhí)行./rdbms/admin/utlchain.sqlanalyze table tablename list chained rows;可通過表chained_rows中table_name,head_rowid看出哪些行是遷移行create table

26、aa as select a.* from sb_zsxx a,chained_rows b where a.rowid=b.head_rowid and b.table_name =SB_ZSXX;delete from sb_zsxx where rowid in (select head_rowid from chained_rows where table_name = SB_ZSXX);insert into sb_zsxx select * from chained_row where table_name = SB_ZSXX;5.7 定期做統(tǒng)計分析對于采用Oracle Cost-

27、Based-Optimizer的系統(tǒng)，需要定期對數(shù)據(jù)對象的統(tǒng)計信息進(jìn)行采集更新，使優(yōu)化器可以根據(jù)準(zhǔn)備的信息作出正確的explain plan。在以下情況更需要進(jìn)行統(tǒng)計信息的更新：a. 應(yīng)用發(fā)生變化b. 大規(guī)模數(shù)據(jù)遷移、歷史數(shù)據(jù)遷出、其他數(shù)據(jù)的導(dǎo)入等c .數(shù)據(jù)量發(fā)生變化查看表或索引的統(tǒng)計信息是否需更新，如：Select table_name,num_rows,last_analyzed From user_tables where table_name =DJ_NSRXXselect count(*) from DJ_NSRXX如num_rows和count(*)如果行數(shù)相差很多,則該表需要更新

28、統(tǒng)計信息，建議一周做一次統(tǒng)計信息收集，如：exec sys.dbms_stats.gather_schema_stats(ownname=CTAIS2,cascade = TRUE,degree = 4);5.8 檢查緩沖區(qū)命中率復(fù)制代碼SELECT a.VALUE + b.VALUE logical_reads, c.VALUE phys_reads, round(100 * (1 - c.value / (a.value + b.value), 4) hit_ratio FROM v$sysstat a, v$sysstat b, v$sysstat c WHERE a.NAME = db

29、 block gets AND b.NAME = consistent gets AND c.NAME = physical reads;復(fù)制代碼如果命中率低于90%則需加大數(shù)據(jù)庫參數(shù)db_cache_size。5.9 檢查共享池命中率select sum(pinhits) / sum(pins) * 100 from v$librarycache; 如低于95%，則需要調(diào)整應(yīng)用程序使用綁定變量，或者調(diào)整數(shù)據(jù)庫參數(shù)shared pool的大小。5.10 檢查排序區(qū) select name,value from v$sysstat where name like %sort%; 如果disk/(

30、memoty+row)的比例過高，則需要調(diào)整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。5.11 檢查日志緩沖區(qū)select name,value from v$sysstat where name in (redo entries,redo buffer allocation retries);如果redo buffer allocation retries/redo entries超過1%，則需要增大log_buffer。 回到頂部6. 檢查數(shù)據(jù)庫安全性

31、在本節(jié)主要檢查Oracle數(shù)據(jù)庫的安全性，包含：檢查系統(tǒng)安全信息，定期修改密碼，總共兩個部分。6.1. 檢查系統(tǒng)安全日志信息系統(tǒng)安全日志文件的目錄在/var/log下，主要檢查登錄成功或失敗的用戶日志信息。檢查登錄成功的日志：rootrac2 # grep -i accepted /var/log/secureJan 8 08:44:43 rac2 sshd29559: Accepted password for root from :ffff: port 1119 ssh2檢查登錄失敗的日志：rootrac2 # grep -i inval /var/log/secure

32、 &grep -i failed /var/log/secureJan 9 10:30:44 rac2 sshd3071: Invalid user ydbuser from :ffff:Jan 9 10:30:56 rac2 sshd3071: Failed password for invalid user ydbuser from :ffff: port 36005 ssh2Jan 9 10:30:56 rac2 sshd3071: Failed password for invalid user ydbuser from :ffff:192.

33、168.3.5 port 36005 ssh2Jan 10 22:44:38 rac2 sshd21611: Failed password for root from :ffff: port 1723 ssh2在出現(xiàn)的日志信息中沒有錯誤(Invalid、refused)提示，如果沒有(Invalid、refused)視為系統(tǒng)正常，出現(xiàn)錯誤提示，應(yīng)作出系統(tǒng)告警通知。6.2. 檢查用戶修改密碼在數(shù)據(jù)庫系統(tǒng)上往往存在很多的用戶，如：第三方數(shù)據(jù)庫監(jiān)控系統(tǒng)，初始安裝數(shù)據(jù)庫時的演示用戶，管理員用戶等等，這些用戶的密碼往往是寫定的，被很多人知道，會被別有用心的人利用來攻擊系統(tǒng)甚至進(jìn)行修

34、改數(shù)據(jù)。需要修改密碼的用戶包括：數(shù)據(jù)庫管理員用戶SYS，SYSTEM；其他用戶。登陸系統(tǒng)后，提示符下輸入cat /etc/passwd，在列出來的用戶中查看是否存在已經(jīng)不再使用的或是陌生的帳號。若存在，則記錄為異常。修改密碼方法：alter user USER_NAME identified by PASSWORD; 回到頂部7. 其他檢查在本節(jié)主要檢查當(dāng)前crontab任務(wù)是否正常，檢查Oracle Job是否有失敗等共六個部分。7.1 檢查當(dāng)前crontab任務(wù)是否正常oracleAS14 $ crontab -l7.2 Oracle Job是否有失敗select job,what,las

35、t_date,next_date,failures,broken from dba_jobs Where schema_user=CAIKE;如有問題建議重建job，如：exec sys.dbms_job.remove(1);commit;exec sys.dbms_job.isubmit(1,REFRESH_ALL_SNAPSHOT;,SYSDATE+1/1440,SYSDATE+4/1440);commit;7.3. 監(jiān)控數(shù)據(jù)量的增長情況復(fù)制代碼select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percent from (select tablespace_name, sum(bytes) total from dba_free_