ORACLE數(shù)據(jù)庫常規(guī)巡檢報告單(例子)

1、oracle數(shù)據(jù)庫常規(guī)巡檢報告單目錄oracle數(shù)據(jù)庫常規(guī)巡檢報告單1一、概述3二使用的相關(guān)軟件簡要說明.3三、主機及操作系統(tǒng)常規(guī)配置檢査31、系統(tǒng)設(shè)置檢查32、i/o設(shè)備信息33、網(wǎng)絡(luò)配置信息4四、oracle數(shù)據(jù)庫常規(guī)檢査51、oracle常規(guī)檢查52、基本參數(shù)配置53、oracle用戶配置文件6五、數(shù)據(jù)庫關(guān)鍵性能檢查及分析61、數(shù)據(jù)庫性能檢查62、數(shù)據(jù)庫響應(yīng)時間分析6六備份恢復策略檢查和維護.71、rman配置情況72、備份策略描述73、備份策略實施74、備份檢查 和每曰備份腳本日志檢查85、對當前策略的總結(jié)8七、關(guān)鍵性sql語句定位及分析8a、近期警告日志及相關(guān)分析.81、orcl_o

2、ra_8727.trc82、orcl_ora_8727.trc9九、小占二.9一、概述按照維護計劃和為了系統(tǒng)的穩(wěn)定運行，需耍定期對系統(tǒng)進行一次巡檢，時間周期為每星期一次。目的在于 診斷當前環(huán)境是否存在安全隱患，系統(tǒng)運行是否存在明顯的系統(tǒng)瓶頸，定位重要的sql語句并進行性能分析， 分析當前備份策略，分析警告fi志信息并提供解決方案。二、使用的相關(guān)軟件簡要說明1. 使用rda對整個系統(tǒng)進行檢查，并且生成報告2. 使用awr對數(shù)據(jù)庫進行檢查，并且生成報告。3. 使用rman對數(shù)據(jù)庫進行備份，并fi使用rman相關(guān)的功能進行備份檢查。相關(guān)詳細后頁提供三、主機及操作系統(tǒng)常規(guī)配置檢查1、系統(tǒng)設(shè)置檢查主機和

3、版本號linux主機名localhost.localdomai n操作系統(tǒng)平臺64-bit操作系統(tǒng)版本2.6.9主機和版本號 linux localhost 2.6.9-34.elsmp #1 smp fri feb 24 16:56:28 est 2006 x86_64 主機名localhost.localdomai n操作系統(tǒng)平臺64-bit red hat linux操作系統(tǒng)版本2.6.92、i/o設(shè)備信息disk mounts/dev/mappera/olgroupoo丄ogvoloo on / type ext3 (rw)none on /proc type proc (rw)non

4、e on /sys type sysfs (rw)none on /dev/pts type devpts (rw,gid=5,mode=620)usbfs on /proc/bus/usb type usbfs (rw)/dev/sda1 on /boot type ext3 (rw)none on /dev/shm type tmpfs (rw)none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)/root/rhel4-u3-x

5、86 64-as-disc1.iso on /mnt/cdrom type iso9660 (rw,loop=/dev/loopo)diskjfreefilesystem 1 k-blocks used available use% mounted on /dev/mapper/volgroupoo-logvoloo 68537896 31770496 33285800 49% / /dev/sda1 101086 14389 81478 16% /bootnone 4055384 0 4055384 0% /dev/shm/root/rhel4-u3-x86 64-as-disc1.iso

6、185452 185452 0 100% /mnt/cdromswapfile name type size used priority/dev/mappera/olgroupoo丄ogvoloi partition 2031608 67276 -13、網(wǎng)絡(luò)配置信息etho link encap:ethernet hwaddr 00:13:d3:bb:fe:54inet addr:2 bcast:55 mask: inet6 addr: fe80:213:d3ff:febb:fe54/64 scope:linkup br

7、oadcast running multicast mtu:1500 metric:1rx packets:29580499 errors:0 dropped:0 overruns:0 frame:0tx packets:22849457 errors:0 dropped:0 overruns:0 carrier:。 collisions:0 txqueuelen:1000rx bytes:(13.9 gib) tx bytes(13.6 gib) interrupts 85eth1 link encap:ethernet hwaddr 00

8、:13:d3:bb:fe:55broadcast multicast mtu:1500 metric:1rx packets:0 errors:0 dropped:0 overruns:0 frame:0 tx packets:0 errors:0 dropped:0 overruns:0 carrier:o collisions:0 txqueuelen:1000rx bytes:o (0.0 b) tx bytes:o (0.0 b)interrupts 93io link encap:local loopbackinet addr: mask:inet

9、6 addr: :1/128 scope:hostup loopback running mtu: 16436 metric:1rx packets:32348076 errors:0 dropped:0 overruns:0 frame:0tx packets:32348076 errors:0 dropped:0 overruns:0 carrier:。 collisio ns:0 txqueuele n:0rx bytes:4383350548 (4.0 gib) tx bytes:4383350548 (4.0 gib)lo:52 link encap:local loopbackin

10、et addr:2 mask:55 up loopback running mtu:16436 metric:1sito link encap:ipv6-in-ipv4noarp mtu:1480 metric:1rx packets:0 errors:0 dropped:0 overruns:0 frame:0 tx packets:0 errors:0 dropped:0 overruns:0 carrier:o collisions:0 txqueuelen:0rx bytes:0 (0.0 b) tx bytes:o (0.0 b)ke

11、rnel in terface tableiface mtu met rx-ok rx-err rx-drp rx-ovr tx-ok tx-err tx-drp tx-ovr fig etho 1500 0 29583657 000 22852580 000 bmruio 16436 0 32362760 000 32362760 000 lrulo:52 16436 0 no statistics available - lrukernel ip routing tabledestination gateway genmask flags mss window irtt iface192.

12、168.70.52 55 uh 0 0 0 io u 000 etho u 000 etho ug 0 0 0 etho四.oracle數(shù)據(jù)庫常規(guī)檢查1、oracle常規(guī)檢查database overviewdb nameorclglobal nameorclregress.rdbms.dev.us.oracle.comhost namelocalhostinstanc

13、e nameorciinstance start time18-nov-2008 14:14:01restricted modenoarchive log modearchivelogv$versionbanneroracle database 10g enterprise edition release .064bipl/sql release .0 - productioncore .0 productiontns for linuxversiom 0.2.0 j .0productionnlsrtl version .0 -

14、 production2、基本參數(shù)配置parameter namevaluemodifiedaudit file dest/o racle/ad min /orcl/adumpfalsebackground_dump_dest/o racl e/ad min/orcl/bdumpfalsecompatible.0falsecontrol_files/o r ac i e/o rad at a/o rc i/c ontro 101.ctl, /oracle/oradata/orcl/control02.ctl, /o r ac i e/o rad ata/o rc i /c on

15、tro io3.ctlfalsecore_dump_dest/o racl e/ad min/orcl/cdumpfalsedb_block_size8192falsedb_domainnull valuefalsedb file multiblock read count16falsedb_nameorcifalsedb_recovery_file_dest/oracle/flash_recovery_areafalsedb_recovery_file_dest_size21474836480falsedispatchers(protocol=tcp) (service=orclxdb)fa

16、lsejob_queue_processes6falselog_archive_dest_1locati on=/oracle/backuparchivelogfalselog_archive_dest_10location=use_db_recovery_file destfalselog_archive_min_succeed_dest2falseope n cursors300falsepga_aggregate_target819986432falseprocesses150falseremotejogin passwordfileexclusivefalsesga_target214

17、7483648falsestatisticsjeveltypicalfalsetimed_statisticstruefalseundo_managementautofalseundo_tablespaceundotbs1falseuser_dump_dest/o racl e/ad min/orcl/udumpfalse3、oracle用戶配置文件# bash_profile# get the aliases and functionsif /bashrc ; then /bashrcfi# user specific environment and startup programspath

18、=$path:$home/binexport pathunset username#for oracleexport oracle_sid=orclexport oracle_base=/oracleexport oracle_home=/oracle/1 ogexport path=$oracle_home/bin:$pathexport nls_lang=american_america.zhs16gbk五. 數(shù)據(jù)庫關(guān)鍵性能檢查及分析1、數(shù)據(jù)庫性能檢查db block buffer hit ratio99.99%oltp環(huán)境下，命中率應(yīng)該高于90%,否則 需要增力口 db block bu

19、ffers以提高 性能library cache hit ratio99.56%該命屮率用于提供shared sql buffers性 能。應(yīng)該高于95%soft parse98.55 %軟解析率應(yīng)該高于95%in-memory sort100 %此為排序操作，如果指標值過低說明過多的 排序操作影響了性能，建議進行sql優(yōu)化execute to parse73.10 %執(zhí)行分析比率，不應(yīng)該低于0否則shared poll可能有問題parse cpu to parse elapsd94.20 %這2項值代表了解析所用的卬u時間，parse cpu to parse elapsd 不到 100%

20、說 明有有其他問題影響了，解析時間并不是全non-parse cpu95.34 %部都消耗在cpu上2、數(shù)據(jù)庫響應(yīng)時間分析響應(yīng)時間是非常重要的數(shù)據(jù)庫性能指標top 5 timed eventseventwaitstime(s)avg wait(ms) % total callwait classtimecpu time1,45492.0rman backup & recovery i/o25,0064611829.1system i/oos thread&6724595329.0concurrencystartuplog file parallel14&21124521

21、5.5system i/owritecontrol file192,426223114.1system i/oparallel write從以上信息可以看出，rman備份占用的大量的時間，六、備份恢復策略檢查和維護1、rman配置情況rman configuration parameters are:configure retention policy to recovery window of 7 days;configure backup optimization on;configure default device type to disk;configure controlfile

22、autobackup on;configure controlfile autobackup format for device type disk to ,orclcongrol_%f,;configure device type disk parallelism 2 backup type to compressed backupset; configure datafile backup copies for device type disk to 1;# defaultconfigure archivelog backup copies for device type disk to1

23、;# defaultconfigure channel 1 device type disk maxpiecesize 100 m maxopenfiles 8 rate 40 m; configure channel 2 device type disk maxpiecesize 100 m maxopenfiles 8 rate 40 m; configure maxsetsize to unlimited; # defaultconfigure encryption for database off; # defaultconfigure encryption algorithm 

24、9;aes128： # defaultconfigure archivelog deletion policy to none; # defaultconfigure snapshot controlfile name to 7oracle/1 og/dbs/snapcf_orcl.f; # default2、 備份策略描述選用rman多級備份策略，以每個星期作為一個周期星期1數(shù)據(jù)庫全備0級星期2增量備份1級星期3增量備份1級星期4累積增量備份1c級星期5數(shù)據(jù)庫全備0級星期6增量備份1級星期大增量備份1級基于以上策略，任何時間點的數(shù)據(jù)恢復只需要做最多1次0級恢復和2次1級恢復.加上當口的歸檔口

25、志 可以實現(xiàn)快速的完全恢復3>備份策略實施cat /etc/cro ntabshell=/bin/bashpath=/sbi n:/bin:/usr/sbi n:/usr/bi nmailto=roothome=/# run-parts01 * * * * root r un-parts /etc/cr on .hourly02 4 * * * root r un-parts /etc/cr on .daily22 4 * * 0 root run-parts /etc/cron.weekly42 4 1 * * root run-parts /etc/cron.monthly00 4

26、* * 1 oracle /oracle/scripts/rman/backupweek1.cmd &00 4 * * 2 oracle /oracle/scripts/rman/backupweek2.cmd &00 4 * * 3 oracle /oracle/scripts/rman/backupweek3.cmd & 00 44 oracle /oracle/scripts/rman/backupweek4.cmd &00 4 * * 5 oracle /oracle/scripts/rman/backupweek5.cmd &00 4 * *

27、6 oracle /oracle/scripts/rman/backupweek6.cmd &00 4 * * 7 oracle /oracle/scripts/rman/backupweek7.cmd &4、備份檢查 和每日備份腳本日志檢查使用crosscheck backupset檢查。所有備份集的備份片都為available 每fl腳木fl志輸出正常，使用report obsolete檢查并無過期的備份信息。5、對當前策略的總結(jié)當前腳本能正常準時完成每f1制定的備份策略，總體運行情況良好。備份集信息完整，并且包含了歸檔口志, 邏輯檢查完好，可以正常執(zhí)行恢復。七、關(guān)鍵性sq

28、l語句定位及分析sql>select rownum,d.* from(select hash_value5disk_reads,executions,buffer_gets,rows_processed from v$sqlarea b order byb.disk_reads desc ) d where rownumvl 1;rownum hash value disk reads executions buffer gets rows processed1 909091134 4140 1 12108 72 4260389146 1189 2327 16113 53063 1088

29、876619 692 1 227543 22764 3543108934 505 1 6824 05 1684122946 361 2327 7489 14236 2812844157 199 8038 76429 246737 1292502613 188 1190 128012 11908 3993603298 168 8038 58649 212859 864012087 134 19393 55468 1609010 2328831744 133 2327 9237 2504sql> select sql_text from v$sqltext where hash_value=

30、&1 order by piece;八、近期警告日志及相關(guān)分析1* orcl_ora_8727. trc-rw-r1 oracle dba 944 dec 6 20:00 2008 dec 6 20:00 2008 orcl_ora_8727.trc /oracle/admin/orcl/udump/orcl_ora_8727.trcoracle database 10g enterprise edition release .0 - 64bit productionwith the partitioning, oracle label security, olap a

31、nd data mining scoring engine options oracle_home = /oracle/1 ogsystem name: linuxnode name: localhostrelease: 2.6.9-34.elsmpversion: #1 smp fri feb 24 16:56:28 est 2006machine: x86_64instance name: orciredo thread mounted by this in stance: 1oracle process number: 21unix process pid: 8727, image: o

32、raclelocalhost (tns v1-v3)* 2008 12-07 04:00:28.002* action name:(0000221 started111) 2008-12-07 04:00:28.001* module name:(backup archivelog) 2008-12-07 04:00:28.001* service name:(sys$users) 2008-12-07 04:00:28.001* session id:(157.7513) 2008-12-07 04:00:28.001* action name:(0000244 started16) 2008-12-07 04:00:32.421* module name:(backup full datafile) 2008-12-07 04:00:32.421* action name:(0000248 started111) 2008-12-07 04:00:33.7032、 orcl_ora_8727. trc-rw-