数据库健康检查报告版权所有目录1数据库健康检查 (3)1.1查看数据库运行状态 (3)1.2查看数据库服务器监听是否正常 (3)1.3查看硬件存储空间使用情况 (5)1.4安全管理 (5)1.5数据库警告日志文件检查 (6)1.6数据库空表间检查 (9)1.7I/O检查 (10)1.8检查是否有失效的索引 (11)1.9检查数据库用户情况 (12)1.10检查数据库数据文件的状态 (13)1.11检查监听日志文件是否过大 (13)1.12查看数据库优化信息,确保未被随意更改,保证数据库系统运行性能 (14)1数据库健康检查健康检查总结：目前数据库运行正常。

1.1查看数据库运行状态检查结果：正常结果如下：[oracle@qzupdb2 ~]$ ps -ef|grep ora_oracle 23804 1 0 Feb03 ? 00:03:12 ora_pmon_uporaoracle 23806 1 0 Feb03 ? 00:11:34 ora_dbw0_uporaoracle 23808 1 0 Feb03 ? 00:27:44 ora_lgwr_uporaoracle 23810 1 0 Feb03 ? 00:01:48 ora_ckpt_uporaoracle 23812 1 0 Feb03 ? 00:00:34 ora_smon_uporaoracle 23814 1 0 Feb03 ? 00:00:00 ora_reco_uporaoracle 23816 1 0 Feb03 ? 00:00:00 ora_cjq0_uporaoracle 23820 1 0 Feb03 ? 00:00:00 ora_s000_uporaoracle 23822 1 0 Feb03 ? 00:00:00 ora_d000_uporaoracle 23997 1 0 Feb03 ? 00:11:34 ora_qmn0_uporaoracle 9135 9107 0 14:41 pts/1 00:00:00 grep ora_[oracle@qzupdb2 ~]$简要说明:数据写进程(dbwr)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件日志写进程(lgwr)：将重做日志缓冲区中的更改写入在线重做日志文件系统监控(smon) ：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复进程监控(pmon) ：负责在一个Oracle 进程失败时清理资源检查点进程(chpt)：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。

归档进程(arcn) ：在每次日志切换时把已满的日志组进行备份或归档恢复进程(reco) :保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;1.2查看数据库服务器监听是否正常1.服务器监听配置内容检查结果：正常文件内容:2.数据库监听器状态检查检查结果：正常以oracle用户在当前运行Oracle的主机上执行：$lsnrctl status upora结果如下：[oracle@qzupdb2 ~]$ lsnrctl status uporaLSNRCTL for Linux: Version 9.2.0.8.0 - Production on 28-APR-2008 11:20:01Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.51.139)(PORT=1521))) STATUS of the LISTENER------------------------Alias UPORAVersion TNSLSNR for Linux: Version 9.2.0.8.0 - ProductionStart Date 03-FEB-2008 10:49:11Uptime 85 days 0 hr. 30 min. 50 secTrace Level offSecurity OFFSNMP OFFListener Parameter File /home/oracle/app/oracle/product/9.2.0/network/admin/listener.ora Listener Log File /home/oracle/app/oracle/product/9.2.0/network/log/upora.log Listening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.51.139)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))Services Summary...Service "upora" has 1 instance(s).Instance "upora", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully1.3查看硬件存储空间使用情况检查结果：正常结果如下：[oracle@qzupdb2 ~]$ df -lhFilesystem Size Used Avail Use% Mounted on/dev/sda6 20G 6.2G 13G 34% //dev/sda2 289M 17M 257M 7% /bootnone 2.0G 0 2.0G 0% /dev/shm/dev/sda11 61G 6.9G 51G 12% /home/dev/sda3 20G 1.2G 18G 7% /opt/dev/sda7 9.7G 55M 9.1G 1% /tmp/dev/sda5 20G 369M 18G 2% /var/dev/sdc1 527G 16G 485G 4% /oradata1/dev/sdb1 385G 150G 216G 41% /oradata1.4安全管理检查结果：正常输入命令:结果信息如下：[oracle@qzupdb2 ~]$ sqlplus '/ as sysdba'SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 28 11:20:47 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.8.0 - ProductionSQL> select *2 from dba_role_privswhere grantee in (select USERNAME3 4 from dba_users5 where account_status = 'OPEN'6 AND INITIAL_RSRC_CONSUMER_GROUP <> 'SYS_GROUP'7 AND DEFAULT_TABLESPACE <> 'SYSTEM');GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- --- WAPGW CONNECT NO YES WAPGW RESOURCE NO YES UPUSER CONNECT NO YES UPUSER RESOURCE NO YESIT_MONITOR CONNECT NO YESSQL>1.5数据库警告日志文件检查检查结果: 正常以oracle用户登录当前运行Oracle的主机，执行：$cd $ORACLE_BASE/admin/upora/bdump$cat alert_upora.log|grep ORA-近期没有出错日志注意：SID和$ORACLE_SID都表示实例名。

[oracle@qzupdb2 bdump]$ tail -100 alert_upora.logTue Mar 25 23:50:43 2008Thread 1 advanced to log sequence 926Current log# 1 seq# 926 mem# 0: /oradata/upora/redo01.log Fri Mar 28 03:05:56 2008Thread 1 advanced to log sequence 927Current log# 2 seq# 927 mem# 0: /oradata/upora/redo02.log Sun Mar 30 00:01:05 2008Thread 1 advanced to log sequence 928Current log# 3 seq# 928 mem# 0: /oradata/upora/redo03.log Thread 1 advanced to log sequence 929Current log# 1 seq# 929 mem# 0: /oradata/upora/redo01.log Thread 1 advanced to log sequence 930Current log# 2 seq# 930 mem# 0: /oradata/upora/redo02.log Thread 1 cannot allocate new log, sequence 931Checkpoint not completeCurrent log# 2 seq# 930 mem# 0: /oradata/upora/redo02.log Thread 1 advanced to log sequence 931Current log# 3 seq# 931 mem# 0: /oradata/upora/redo03.log Sun Mar 30 00:01:51 2008Thread 1 cannot allocate new log, sequence 932Checkpoint not completeCurrent log# 3 seq# 931 mem# 0: /oradata/upora/redo03.log Thread 1 advanced to log sequence 932Current log# 1 seq# 932 mem# 0: /oradata/upora/redo01.log Sun Mar 30 00:02:11 2008Thread 1 cannot allocate new log, sequence 933Checkpoint not completeCurrent log# 1 seq# 932 mem# 0: /oradata/upora/redo01.log Sun Mar 30 00:02:30 2008Thread 1 advanced to log sequence 933Current log# 2 seq# 933 mem# 0: /oradata/upora/redo02.log Thread 1 cannot allocate new log, sequence 934Checkpoint not completeCurrent log# 2 seq# 933 mem# 0: /oradata/upora/redo02.log Sun Mar 30 00:02:50 2008Thread 1 advanced to log sequence 934Current log# 3 seq# 934 mem# 0: /oradata/upora/redo03.log Thread 1 cannot allocate new log, sequence 935Checkpoint not completeCurrent log# 3 seq# 934 mem# 0: /oradata/upora/redo03.log Sun Mar 30 00:03:15 2008Thread 1 advanced to log sequence 935Current log# 1 seq# 935 mem# 0: /oradata/upora/redo01.log Mon Mar 31 23:27:05 2008Thread 1 advanced to log sequence 936Current log# 2 seq# 936 mem# 0: /oradata/upora/redo02.log Thu Apr 3 02:50:50 2008Thread 1 advanced to log sequence 937Current log# 3 seq# 937 mem# 0: /oradata/upora/redo03.log Sat Apr 5 06:22:01 2008Thread 1 advanced to log sequence 938Current log# 1 seq# 938 mem# 0: /oradata/upora/redo01.log Mon Apr 7 09:34:28 2008Thread 1 advanced to log sequence 939Current log# 2 seq# 939 mem# 0: /oradata/upora/redo02.log Wed Apr 9 12:44:31 2008Thread 1 advanced to log sequence 940Current log# 3 seq# 940 mem# 0: /oradata/upora/redo03.log Fri Apr 11 14:58:43 2008Thread 1 advanced to log sequence 941Current log# 1 seq# 941 mem# 0: /oradata/upora/redo01.log Sun Apr 13 17:48:02 2008Thread 1 advanced to log sequence 942Current log# 2 seq# 942 mem# 0: /oradata/upora/redo02.log Tue Apr 15 18:59:40 2008Thread 1 advanced to log sequence 943Current log# 3 seq# 943 mem# 0: /oradata/upora/redo03.log Thu Apr 17 20:52:52 2008Thread 1 advanced to log sequence 944Current log# 1 seq# 944 mem# 0: /oradata/upora/redo01.log Sat Apr 19 21:08:13 2008Thread 1 advanced to log sequence 945Current log# 2 seq# 945 mem# 0: /oradata/upora/redo02.log Tue Apr 22 00:00:59 2008Thread 1 advanced to log sequence 946Current log# 3 seq# 946 mem# 0: /oradata/upora/redo03.log Thu Apr 24 02:49:45 2008Thread 1 advanced to log sequence 947Current log# 1 seq# 947 mem# 0: /oradata/upora/redo01.log Sat Apr 26 05:42:27 2008Thread 1 advanced to log sequence 948Current log# 2 seq# 948 mem# 0: /oradata/upora/redo02.log Mon Apr 28 06:56:55 2008Thread 1 advanced to log sequence 949Current log# 3 seq# 949 mem# 0: /oradata/upora/redo03.log1.6数据库空表间检查检查结果：正常.以ORACLE用户登陆当前运行的ORACLE主机，执行：结果如下：表空间名表空间大小(M)已使用空间(M) 使用比空闲空间(M) 最大块(M) ------------------------------ ------------- ------------- ------- ----------- ----------EXAMPLE 149.38 148.88 99.67 .5 .5XDB 45 44.75 99.44 .25 .25 SYSTEM 380 371.75 97.83 8.25 8.25 CWMLITE 20 14.81 74.05 5.19 5.19 DRSYS 20 9.69 48.45 10.31 10.31ODM 20 9.62 48.10 10.38 10.38TS_PULL_EVENT_1 10500 4105.19 39.10 6394.81 2166.94 TS_WAPGW_CONFIG 6500 467.44 7.19 6032.56 3271.94 TS_OPERATION_INFO 3500 240.62 6.87 3259.38 1684.94 TS_PULL_EVENT_7 10500 677.19 6.45 9822.81 3278.94 TS_PULL_EVENT_4 10500 454.19 4.33 10045.81 3352.94 UNDOTBS1 3525 80.44 2.28 3444.56 3138.94TS_CDR_PULL_1 9216 201.25 2.18 9014.75 3007.94 TS_CDR_PULL_5 9216 201.25 2.18 9014.75 3007.94 TS_CDR_PULL_7 9216 201.25 2.18 9014.75 3007.94 TS_CDR_PULL_6 9216 201.25 2.18 9014.75 3007.94 TS_CDR_PULL_4 9216 201.25 2.18 9014.75 3007.94 TS_CDR_PULL_2 9216 201.25 2.18 9014.75 3007.94 TS_CDR_PULL_3 9216 201.25 2.18 9014.75 3007.94 TS_PULL_EVENT_6 10500 204.19 1.94 10295.81 3434.94 TS_PULL_EVENT_5 10500 202.19 1.93 10297.81 3435.94 TS_PULL_EVENT_2 10500 201.25 1.92 10298.75 3435.94 TS_PULL_EVENT_3 10500 201.25 1.92 10298.75 3435.94 TS_CONTENT_EXT 3500 29.44 0.84 3470.56 1786.94 TS_CONTENT_PRODUCT 3500 26.31 0.75 3473.69 1786.94 TOOLS 10 .06 0.60 9.94 9.94TS_USER_INFO 1800 10.25 0.57 1789.75 1789.75TS_PRODUCT_ORDER_RELATIO 1024 5.56 0.54 1018.44 1017.94 TS_OPERATION_CONTENT 1300 5.25 0.40 1294.75 1294.75 TS_UPUSER_USER 1024 4.12 0.40 1019.88 835.63 INDX 25 .06 0.24 24.94 24.94 USERS 25 .06 0.24 24.94 24.9432 rows selected.1.7I/O检查检查结果：I/0运行正常，I/0wait 保持在10%以内水平.操作命令：top 结果如下：[oracle@qzupdb2 bdump]$ toptop - 14:52:39 up 126 days, 23 min, 1 user, load average: 2.02, 2.03, 2.00Tasks: 248 total, 1 running, 246 sleeping, 0 stopped, 1 zombieCpu(s): 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% siMem: 4149144k total, 4104652k used, 44492k free, 185716k buffersSwap: 6144744k total, 130120k used, 6014624k free, 3315176k cachedPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 9385 oracle 16 0 2168 1112 780 R 0 0.0 0:00.03 top1 root 16 0 3540 600 512 S 0 0.0 0:17.36 init2 root RT 0 0 0 0 S 0 0.0 0:01.36 migration/03 root 34 19 0 0 0 S 0 0.0 0:00.05 ksoftirqd/04 root RT 0 0 0 0 S 0 0.0 0:00.98 migration/15 root 34 19 0 0 0 S 0 0.0 0:00.04 ksoftirqd/16 root RT 0 0 0 0 S 0 0.0 0:00.93 migration/27 root 34 19 0 0 0 S 0 0.0 0:00.06 ksoftirqd/28 root RT 0 0 0 0 S 0 0.0 0:00.91 migration/39 root 34 19 0 0 0 S 0 0.0 0:00.04 ksoftirqd/310 root RT 0 0 0 0 S 0 0.0 0:00.71 migration/411 root 34 19 0 0 0 S 0 0.0 0:00.05 ksoftirqd/412 root RT 0 0 0 0 S 0 0.0 0:00.56 migration/513 root 34 19 0 0 0 S 0 0.0 0:00.03 ksoftirqd/514 root RT 0 0 0 0 S 0 0.0 0:00.41 migration/615 root 34 19 0 0 0 S 0 0.0 0:00.10 ksoftirqd/616 root RT 0 0 0 0 S 0 0.0 0:00.29 migration/717 root 34 19 0 0 0 S 0 0.0 0:00.03 ksoftirqd/718 root 5 -10 0 0 0 S 0 0.0 0:00.06 events/019 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/120 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/221 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/322 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/423 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/524 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/625 root 5 -10 0 0 0 S 0 0.0 0:00.00 events/726 root 5 -10 0 0 0 S 0 0.0 0:00.00 khelper27 root 15 -10 0 0 0 S 0 0.0 0:00.00 kacpid83 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/084 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/185 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/286 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/387 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/488 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/589 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/690 root 5 -10 0 0 0 S 0 0.0 0:00.00 kblockd/791 root 15 0 0 0 0 S 0 0.0 0:00.05 khubd【说明】Iowait不应长期(连续1个小时以上)超过50％以上.如果超过需要通知DBA做进一步的观察和分析。