1、ORACLE數(shù)據(jù)庫常規(guī)巡檢報(bào)告單目錄ORACLE數(shù)據(jù)庫常規(guī)巡檢報(bào)告單1一、概述3二、使用的相關(guān)軟件簡要說明3三、主機(jī)及操作系統(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)時(shí)間分析6六、備份恢復(fù)策略檢查和維護(hù)71、RMAN 配置情況72、備份策略描述73、備份策略實(shí)施74、備份檢查 和每日備份腳本日志檢查85、對當(dāng)前策略的總結(jié)8七、關(guān)鍵性SQL 語句定位及分析8八、近期警告日志及相關(guān)分析81、o

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

3、1、 系統(tǒng)設(shè)置檢查 主機(jī)和版本號Linux主機(jī)名 localhost.localdomain操作系統(tǒng)平臺64-bit操作系統(tǒng)版本2.6.9主機(jī)和版本號 Linux localhost 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 主機(jī)名 localhost.localdomain 操作系統(tǒng)平臺 64-bit Red Hat Linux 操作系統(tǒng)版本 2.6.9 2、 I/O 設(shè)備信息 Disk Mounts /dev/mapper/VolGroup00-LogVol00 on / type ext3 (rw) none on

4、 /proc type 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

5、rpc_pipefs (rw) /root/RHEL4-U3-x86_64-AS-disc1.iso on /mnt/cdrom type iso9660 (rw,loop=/dev/loop0) Disk Free Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/VolGroup00-LogVol00 49% / /dev/sda1 14389 81478 16% /boot none 0 0% /dev/shm /root/RHEL4-U3-x86_64-AS-disc1.iso 0 100% /mnt/cdr

6、om Swap Filename Type Size Used Priority /dev/mapper/VolGroup00-LogVol01 partition 67276 -1 3、 網(wǎng)絡(luò)配置信息 eth0 Link encap:Ethernet HWaddr 00:13:D3:BB:FE:54 inet addr:2 Bcast:55 Mask: inet6 addr: fe80:213:d3ff:febb:fe54/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU

7、:1500 Metric:1 RX packets: errors:0 dropped:0 overruns:0 frame:0 TX packets: errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes: (13.9 GiB) TX bytes: (13.6 GiB) Interrupt:185 eth1 Link encap:Ethernet HWaddr 00:13:D3:BB:FE:55 BROADCAST MULTICAST MTU:1500 Metric:1 RX packets

8、:0 errors:0 dropped:0 overruns:0 frame:0 TX packets:0 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:0 (0.0 b) TX bytes:0 (0.0 b) Interrupt:193 lo Link encap:Local Loopback inet addr: Mask: inet6 addr: :1/128 Scope:Host UP LOOPBACK RUNNING MTU:16436 M

9、etric:1 RX packets: errors:0 dropped:0 overruns:0 frame:0 TX packets: errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes: (4.0 GiB) TX bytes: (4.0 GiB) lo:52 Link encap:Local Loopback inet addr:2 Mask:55 UP LOOPBACK RUNNING MTU:16436 Metric:1 sit0 Lin

10、k encap:IPv6-in-IPv4 NOARP MTU:1480 Metric:1 RX packets:0 errors:0 dropped:0 overruns:0 frame:0 TX packets:0 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:0 (0.0 b) TX bytes:0 (0.0 b) Kernel Interface table Iface MTU Met RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-

11、OVR Flg eth0 1500 0 0 0 0 0 0 0 BMRU lo 16436 0 0 0 0 0 0 0 LRU lo:52 16436 0 - no statistics available - LRU Kernel IP routing table Destination Gateway Genmask Flags MSS Window irtt Iface 2 55 UH 0 0 0 lo U 0 0 0 eth0

12、 U 0 0 0 eth0 UG 0 0 0 eth0 四、 ORACLE 數(shù)據(jù)庫常規(guī)檢查 1、 ORACLE 常規(guī)檢查 Database Overview DB NameORCLGlobal NameORCL.REGRESS.RDBMS.DEV.US.ORACLE.COMHost NamelocalhostInstance NameorclInstance Start Time18-Nov-2008 14:14:01Restricted ModeNOArchive Log ModeARCHIVEL

13、OGV$Version BannerOracle Database 10g Enterprise Edition Release .0 - 64biPL/SQL Release .0 - ProductionCORE .0 ProductionTNS for Linux: Version .0 - ProductionNLSRTL Version .0 Production2、 基本參數(shù)配置 Parameter NameValueModifiedaudit_file_dest/oracle/admin/orcl/a

14、dumpFALSEbackground_dump_dest/oracle/admin/orcl/bdumpFALSEcompatible.0FALSEcontrol_files/oracle/oradata/orcl/control01.ctl, /oracle/oradata/orcl/control02.ctl, /oracle/oradata/orcl/control03.ctlFALSEcore_dump_dest/oracle/admin/orcl/cdumpFALSEdb_block_size8192FALSEdb_domainNull valueFALSEdb_f

15、ile_multiblock_read_count16FALSEdb_nameorclFALSEdb_recovery_file_dest/oracle/flash_recovery_areaFALSEdb_recovery_file_dest_sizeFALSEdispatchers(PROTOCOL=TCP) (SERVICE=orclXDB)FALSEjob_queue_processes6FALSElog_archive_dest_1location=/oracle/backupArchivelogFALSElog_archive_dest_10location=use_db_reco

16、very_file_destFALSElog_archive_min_succeed_dest2FALSEopen_cursors300FALSEpga_aggregate_targetFALSEprocesses150FALSEremote_login_passwordfileEXCLUSIVEFALSEsga_targetFALSEstatistics_levelTYPICALFALSEtimed_statisticsTRUEFALSEundo_managementAUTOFALSEundo_tablespaceUNDOTBS1FALSEuser_dump_dest/oracle/admi

17、n/orcl/udumpFALSE3、 ORACLE 用戶配置文件 # .bash_profile # Get the aliases and functions if -f /.bashrc ; then . /.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH unset USERNAME #for oracle export ORACLE_SID=orcl export ORACLE_BASE=/oracle export ORACLE_HOME=/ora

18、cle/10g export PATH=$ORACLE_HOME/bin:$PATH export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 五、 數(shù)據(jù)庫關(guān)鍵性能檢查及分析 1、 數(shù)據(jù)庫性能檢查 DB Block Buffer Hit Ratio99.99OLTP 環(huán)境下，命中率應(yīng)該高于90%，否則需要增加DB_BLOCK_BUFFERS 以提高性能Library Cache Hit Ratio99.56該命中率用于提供Shared SQL Buffers 性能。應(yīng)該高于95%Soft Parse98.55 軟解析率 應(yīng)該高于95In-memory Sort100

19、此為排序操作，如果指標(biāo)值過低說明過多的排序操作影響了性能，建議進(jìn)行SQL 優(yōu)化Execute to Parse73.10 執(zhí)行分析比率，不應(yīng)該低于0 否則shared poll 可能有問題Parse CPU to Parse Elapsd94.20 這2 項(xiàng)值代表了解析所用的cpu 時(shí)間， Parse CPU to Parse Elapsd 不到100說明有有其他問題影響了，解析時(shí)間并不是全部 都消耗在cpu 上Non-Parse CPU95.34 2、 數(shù)據(jù)庫響應(yīng)時(shí)間分析 響應(yīng)時(shí)間是非常重要的數(shù)據(jù)庫性能指標(biāo) Top 5 Timed Events EventWaitsTime(s)Avg Wa

20、it(ms)% Total Call TimeWait ClassCPU time1,45492.0RMAN backup & recovery I/O25,0064611829.1System I/Oos thread startup8,6724595329.0Concurrencylog file parallel write148,211245215.5System I/Ocontrol file parallel write192,426223114.1System I/O從以上信息可以看出，RMAN 備份占用的大量的時(shí)間， 六、 備份恢復(fù)策略檢查和維護(hù) 1、 RMAN 配置情況 RM

21、AN 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 CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO orclcongrol_%F; CONFIGURE DEVICE TYP

22、E 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; # default CONFIGURE CHANNEL 1 DEVICE TYPE DISK MAXPIECESIZE 100 M MAXOPENFILES 8 RATE 40 M; CONFIGURE CHANNEL

23、2 DEVICE TYPE DISK MAXPIECESIZE 100 M MAXOPENFILES 8 RATE 40 M; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM AES128; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME T

24、O /oracle/10g/dbs/snapcf_orcl.f; # default 2、 備份策略描述 選用RMAN 多級備份策略,以每個(gè)星期作為一個(gè)周期 星期1 數(shù)據(jù)庫全備 0 級 星期2 增量備份 1 級 星期3 增量備份 1 級 星期4 累積增量備份 1c 級 星期5 數(shù)據(jù)庫全備 0 級 星期6 增量備份 1 級 星期天 增量備份 1 級 基于以上策略,任何時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)只需要做最多1 次0 級恢復(fù)和2 次1 級恢復(fù).加上 當(dāng)日的歸檔日志可以 實(shí)現(xiàn)快速的完全恢復(fù) 3、 備份策略實(shí)施 Cat /etc/crontab SHELL=/bin/bash PATH=/sbin:/bin:/u

25、sr/sbin:/usr/bin MAILTO=root HOME=/ # run-parts 01 * * * * root run-parts /etc/cron.hourly 02 4 * * * root run-parts /etc/cron.daily 22 4 * * 0 root run-parts /etc/cron.weekly 42 4 1 * * root run-parts /etc/cron.monthly 00 4 * * 1 oracle /oracle/scripts/rman/backupweek1.cmd & 00 4 * * 2 oracle /orac

26、le/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 & 00 4 * * 6 oracle /oracle/scripts/rman/backupweek6.cmd & 00 4 * * 7 oracle /oracle/scripts/rman

27、/backupweek7.cmd & 4、 備份檢查 和每日備份腳本日志檢查 使用crosscheck backupset 檢查。所有備份集的備份片都為 AVAILABLE 每日腳本日志輸出正常，使用report obsolete 檢查并無過期的備份信息。 5、 對當(dāng)前策略的總結(jié) 當(dāng)前腳本能正常準(zhǔn)時(shí)完成每日制定的備份策略，總體運(yùn)行情況良好。備份集信息完整，并且包含了歸檔日 志，邏輯檢查完好，可以正常執(zhí)行恢復(fù)。 七、 關(guān)鍵性SQL 語句定位及分析 SQLselect rownum,d.* from (select hash_value,disk_reads,executions,buffer_g

28、ets,ROWS_PROCESSED from v$sqlarea b order by b.disk_reads desc ) d where rownum select sql_text from 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.tr

29、c Oracle Database 10g Enterprise Edition Release .0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options ORACLE_HOME = /oracle/10g System name: Linux Node name: localhost Release: 2.6.9-34.ELsmp Version: #1 SMP Fri Feb 24 16:56:28 EST 2

30、006 Machine: x86_64 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 21 Unix process pid: 8727, image: oraclelocalhost (TNS V1-V3) * 2008-12-07 04:00:28.002 * ACTION NAME:( STARTED111) 2008-12-07 04:00:28.001 * MODULE NAME:(backup archivelog) 2008-12-07 04:00:28.001

31、 * SERVICE NAME:(SYS$USERS) 2008-12-07 04:00:28.001 * SESSION ID:(157.7513) 2008-12-07 04:00:28.001 * ACTION NAME:( STARTED16) 2008-12-07 04:00:32.421 * MODULE NAME:(backup full datafile) 2008-12-07 04:00:32.421 * ACTION NAME:( STARTED111) 2008-12-07 04:00:33.703 2、 orcl_ora_8727.trc -rw-r- 1 oracle dba 944 Dec 6 20:00 2008 Dec 6 20:00