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

1、WORD格式ORACLE數(shù)據(jù)庫常規(guī)巡檢報告單專業(yè)資料整理目錄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六、備份恢復(fù)策略檢查和維護(hù)71 、RMAN配置情況72 、備份策略描述73 、備份策略實施74 、備份檢查和每日備份腳本日志檢查85 、對當(dāng)前策略的總結(jié)8七、關(guān)鍵性 SQL語句定位及

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

3、配置檢查1、系統(tǒng)設(shè)置檢查主機和版本號Linux主機名localhost.localdomain操作系統(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.localdomain操作系統(tǒng)平臺64-bit Red Hat Linux操作系統(tǒng)版本2.6.92、I/O設(shè)備信息Disk Mounts/dev/mapper/VolGroup00-LogVol00 on / type ext3 (rw)none on /proc type

4、proc (rw)none 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)/roo

5、t/RHEL4-U3-x86_64-AS-disc1.iso on /mnt/cdrom type iso9660 (rw,loop=/dev/loop0)Disk FreeFilesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/VolGroup00-LogVol00 68537896 31770496 33285800 49% / /dev/sda1 101086 14389 81478 16% /bootnone 4055384 0 4055384 0% /dev/shm /root/RHEL4-U3-x86_64-A

6、S-disc1.iso 185452 185452 0 100% /mnt/cdromSwapFilename Type Size Used Priority/dev/mapper/VolGroup00-LogVol01 partition 2031608 67276 -13、網(wǎng)絡(luò)配置信息eth0 Link encap:Ethernet HWaddr 00:13:D3:BB:FE:54inet addr:2 Bcast:55 Mask: inet6 addr: fe80:213:d3ff:febb:fe54/64 Sco

7、pe:LinkUP BROADCAST 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:0collisions:0 txqueuelen:1000RX bytes(13.9 GiB) TX bytes(13.6 GiB)Interrupt:185eth1 Link encap:Ethernet

8、HWaddr 00:13:D3:BB:FE:55BROADCAST MULTICAST MTU:1500 Metric:1RX packets:0 errors:0 dropped:0 overruns:0 frame:0TX packets:0 errors:0 dropped:0 overruns:0 carrier:0collisions:0 txqueuelen:1000RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)Interrupt:193loLink encap:Local Loopbackinet addr: Mask:255.0.0

9、.0inet6 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:0collisions:0 txqueuelen:0RX bytes:4383350548 (4.0 GiB) TX bytes:4383350548 (4.0 GiB)lo:52 Link encap:Local Loopback

10、inet addr:2 Mask:55UP LOOPBACK RUNNING MTU:16436 Metric:1sit0 Link encap:IPv6-in-IPv4NOARP MTU:1480 Metric:1RX packets:0 errors:0 dropped:0 overruns:0 frame:0TX packets:0 errors:0 dropped:0 overruns:0 carrier:0collisions:0 txqueuelen:0RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)Ker

11、nel Interface tableIface MTU Met RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg eth0 1500 0 29583657 0 0 0 22852580 0 0 0 BMRUlo 16436 0 32362760 0 0 0 32362760 0 0 0 LRUlo:52 16436 0 - no statistics available - LRUKernel IP routing tableDestination Gateway Genmask Flags MSS Window irtt I

12、face2 55 UH 0 0 0 lo U 0 0 0 eth0 U 0 0 0 eth0 UG 0 0 0 eth0四、ORACLE數(shù)據(jù)庫常規(guī)檢查1、ORACLE常規(guī)檢查Database OverviewDB NameORCLGlobal NameORCL.REGRESS.RDBMS.DEV.US.ORACLE.COMHost Nameloc

13、alhostInstance NameorclInstance Start Time18-Nov-2008 14:14:01Restricted ModeNOArchive Log ModeARCHIVELOGV$VersionBannerOracle Database 10g Enterprise Edition Release .0 - 64biPL/SQL Release .0 - ProductionCORE .0 ProductionTNS for Linux: Version .0 - ProductionNLSRTL

14、 Version .0Production2、基本參數(shù)配置Parameter NameValueModifiedaudit_file_dest/oracle/admin/orcl/adumpFALSEbackground_dump_dest/oracle/admin/orcl/bdumpFALSEcompatible.0FALSEcontrol_files/oracle/oradata/orcl/control01.ctl,FALSE/oracle/oradata/orcl/control02.ctl,/oracle/oradata/orcl/control03

15、.ctlcore_dump_dest/oracle/admin/orcl/cdumpFALSEdb_block_size8192FALSEdb_domainNull valueFALSEdb_file_multiblock_read_count16FALSEdb_nameorclFALSEdb_recovery_file_dest/oracle/flash_recovery_areaFALSEdb_recovery_file_dest_size21474836480FALSEdispatchers(PROTOCOL=TCP)FALSE(SERVICE=orclXDB)job_queue_pro

16、cesses6FALSElog_archive_dest_1location=/oracle/backupArchivelogFALSElog_archive_dest_10location=use_db_recovery_file_destFALSElog_archive_min_succeed_dest2FALSEopen_cursors300FALSEpga_aggregate_target819986432FALSEprocesses150FALSEremote_login_passwordfileEXCLUSIVEFALSEsga_target2147483648FALSEstati

17、stics_levelTYPICALFALSEtimed_statisticsTRUEFALSEundo_managementAUTOFALSEundo_tablespaceUNDOTBS1FALSEuser_dump_dest/oracle/admin/orcl/udumpFALSE3、ORACLE用戶配置文件# .bash_profile# Get the aliases and functions if -f /.bashrc ; then. /.bashrcfi# User specific environment and startup programs PATH=$PATH:$HO

18、ME/binexport PATH unset USERNAME#for oracleexport ORACLE_SID=orclexport ORACLE_BASE=/oracleexport ORACLE_HOME=/oracle/10gexport 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_BUFFERS以提高性能L

19、ibrary Cache Hit Ratio99.56 該命中率用于提供Shared SQL Buffers性能。應(yīng)該高于 95%Soft Parse98.55軟解析率 應(yīng)該高于95 In-memory Sort100 此為排序操作，如果指標(biāo)值過低說明過多的排序操作影響了性能，建議進(jìn)行SQL優(yōu)化Execute to Parse73.10執(zhí)行分析比率，不應(yīng)該低于0否則 sharedpoll 可能有問題Parse CPU to Parse Elapsd94.20這 2 項值代表了解析所用的cpu時間，Parse CPU to Parse Elapsd不到 100 說明有有其他問題影響了，解析時間并

20、不是全Non-Parse CPU95.34部 都消耗在 cpu 上2、數(shù)據(jù)庫響應(yīng)時間分析響應(yīng)時間是非常重要的數(shù)據(jù)庫性能指標(biāo)Top 5 Timed EventsEventWaitsTime(s)Avg Wait(ms)% Total CallWait ClassTimeCPU time1,45492.0RMAN backup25,0064611829.1System I/O& recovery I/Oos thread8,6724595329.0Concurrencystartuplog file parallel148,211245215.5System I/Owritecontrol

21、 file192,426223114.1System I/Oparallel write從以上信息可以看出，RMAN備份占用的大量的時間，六、備份恢復(fù)策略檢查和維護(hù)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 AUTOBACKUP ON;CONFIGURE

22、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; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCO

23、NFIGURE 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 'AES128'

24、; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/10g/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ù)恢復(fù)只需要做最多 1 次 0 級恢復(fù)和

25、 2 次 1 級恢復(fù) .加上 當(dāng)日的歸檔日志可以 實現(xiàn)快速的完全恢復(fù)3、備份策略實施Cat /etc/crontabSHELL=/bin/bashPATH=/sbin:/bin:/usr/sbin:/usr/binMAILTO=rootHOME=/# run-parts01 * * * * root run-parts /etc/cron.hourly02 4 * * * root run-parts /etc/cron.daily22 4 * * 0 root run-parts /etc/cron.weekly42 4 1 * * root run-parts /etc/cron.mont

26、hly00 4 * * 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 4 * * 4 oracle /oracle/scripts/rman/backupweek4.cmd &00 4 * * 5 oracle /oracle/scripts/rman/backupweek5.cmd &

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

28、行恢復(fù)。七、關(guān)鍵性 SQL 語句定位及分析SQL>select rownum,d.* from(select hash_value,disk_reads,executions,buffer_gets,ROWS_PROCESSED from v$sqlarea b order by b.disk_reads desc ) d where rownum<11;ROWNUM HASH_VALUE DISK_READS EXECUTIONS BUFFER_GETS ROWS_PROCESSED- - - - - -1 909091134 4140 1 12108 72 4260389146

29、 1189 2327 16113 53063 1088876619 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

30、v$sqltext where hash_value=&1 order by piece;八、近期警告日志及相關(guān)分析1、orcl_ora_8727.trc-rw-r- 1 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, Ora

31、cle Label Security, OLAP and Data Mining Scoring Engine options ORACLE_HOME = /oracle/10gSystem name: LinuxNode name: localhostRelease: 2.6.9-34.ELsmpVersion: #1 SMP Fri Feb 24 16:56:28 EST 2006Machine: x86_64Instance name: orclRedo thread mounted by this instance: 1Oracle process number: 21Unix pro

32、cess pid: 8727, image: oraclelocalhost (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.tr