巡检内容1. 检查数据库基本状况在本节中主要对数据库的基本状况进行检查，其中包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。1.1. 检查Oracle实例状态 SQL>select instance_name,host_name,startup_time,status,database_status fromv$instance; INSTANCE_NAME HOST_NAME STARTUP_TIME STATUS DATABASE_STATUS---------------- ------------------- -------------------- ---------- ------------ ----CKDB AS14 2009-5-7 9:3 OPEN ACTIVE
其中“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。 SQL> select name,log_mode,open_mode fromv$database; NAME LOG_MODE OPEN_MODE--------- ------------ -----------------CKDB ARCHIVELOG READ WRITE
其中“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。在我们的系统中数据库必须运行在归档方式下。1.2. 检查Oracle服务进程$ps -ef|grep ora_|grep -v grep&&ps-ef|grep ora_|grep -v grep|wc –l oracle 2960 1 0 May07 ? 00:01:02 ora_pmon_CKDBoracle 2962 1 0 May07 ? 00:00:22 ora_psp0_CKDBoracle 2964 1 0 May07 ? 00:00:00 ora_mman_CKDBoracle 2966 1 0 May07 ? 00:03:20 ora_dbw0_CKDBoracle 2968 1 0 May07 ? 00:04:29 ora_lgwr_CKDBoracle 2970 1 0 May07 ? 00:10:31 ora_ckpt_CKDBoracle 2972 1 0 May07 ? 00:03:45 ora_smon_CKDBoracle 2974 1 0 May07 ? 00:00:00 ora_reco_CKDBoracle 2976 1 0 May07 ? 00:01:24 ora_cjq0_CKDBoracle 2978 1 0 May07 ? 00:06:17 ora_mmon_CKDBoracle 2980 1 0 May07 ? 00:07:26 ora_mmnl_CKDBoracle 2982 1 0 May07 ? 00:00:00 ora_d000_CKDBoracle 2984 1 0 May07 ? 00:00:00 ora_s000_CKDBoracle 2994 1 0 May07 ? 00:00:28 ora_arc0_CKDBoracle 2996 1 0 May07 ? 00:00:29 ora_arc1_CKDBoracle 3000 1 0 May07 ? 00:00:00 ora_qmnc_CKDBoracle 3625 1 0 May07 ? 00:01:40 ora_q000_CKDBoracle 31594 1 0 Jul20 ? 00:00:00 ora_q003_CKDBoracle 23802 1 0 05:09 ? 00:00:33 ora_j000_CKDB19
在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：
. Oracle写数据文件的进程，输出显示为：“ora_dbw0_CKDB”
. Oracle写日志文件的进程，输出显示为：“ora_lgwr_ CKDB”
. Oracle监听实例状态的进程，输出显示为：“ora_smon_ CKDB”
. Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_ CKDB”
. Oracle进行归档的进程，输出显示为：“ora_arc0_ CKDB”
. Oracle进行检查点的进程，输出显示为：“ora_ckpt_ CKDB”
. Oracle进行恢复的进程，输出显示为：“ora_reco_ CKDB”1.3. 检查Oracle监听状态/home/oracle>lsnrctl status LSNRCTL for Linux: Version 10.2.0.2.0- Production on 23-JUL-2009 14:11:53Copyright (c) 1991, 2005, Oracle. All rights reserved.Connecting to(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 10.2.0.2.0- ProductionStart Date 07-MAY-2009 09:35:52Uptime 77 days 4 hr. 36 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /data/oracle/product/10.2.0/network/admin/listener.oraListener Log File /data/oracle/product/10.2.0/network/log/listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521)))Services Summary...Service "CKDB" has 1 instance(s). Instance "CKDB", status READY, has 1 handler(s) for thisservice...Service "CKDBXDB" has 1 instance(s). Instance "CKDB", status READY, has 1 handler(s) for thisservice...Service "CKDB_XPT" has 1 instance(s). Instance "CKDB", status READY, has 1 handler(s) for thisservice...The command completed successfully
“ServicesSummary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“CKDB”这一项。 检查监听进程是否存在：[oracle@AS14 ~]$ ps -ef|grep lsn|grep -v greporacle 2954 1 0May07 ? 00:01:17/data/oracle/product/10.2.0/bin/tnslsnr LISTENER–inherit
2. 检查系统和oracle日志文件在本节主要检查相关的日志文件，包含：检查操作系统的日志文件，检查Oracle日志文件，检查Oracle核心转储目录，检查Root用户和Oracle用户的email，总共四个部分。2.1. 检查操作系统日志文件# cat /var/log/messages |grep failed
查看是否有与Oracle用户相关的出错信息。2.2. 检查oracle日志文件[oracle@AS14 ~]$ cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep ora-[oracle@AS14 ~]$ cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep err[oracle@AS14 ~]$ cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep fail
Oracle在运行过程中，会在警告日志文件(alert_SID.log)中记录数据库的一些运行情况：数据库的启动、关闭，启动时的非缺省参数；数据库的重做日志切换情况，记录每次切换的时间，及如果因为检查点(checkpoint)操作没有执行完成造成不能切换，会记录不能切换的原因；对数据库进行的某些操作，如创建或删除表空间、增加数据文件；数据库发生的错误，如表空间不够、出现坏块、数据库内部错误(ORA－600)等。定期检查日志文件，根据日志中发现的问题及时进行处理：[table=98%]
[td=44%] 问题 [td=55%]处理
[td=44%]启动参数不对 [td=55%][align=right]检查初始化参数文件
[td=44%]因为检查点操作或归档操作没有完成造成重做日志不能切换 [td=55%][align=right]如果经常发生这样的情况，可以考虑增加重做日志文件组；想办法提高检查点或归档操作的效率；
[td=44%]有人未经授权删除了表空间 [td=55%][align=right]检查数据库的安全问题，是否密码太简单；如有必要，撤消某些用户的系统权限
[td=44%]出现坏块 [td=55%][align=right]检查是否是硬件问题(如磁盘本生有坏块)，如果不是，检查是那个数据库对象出现了坏块，对这个对象进行重建
[td=44%]表空间不够 [td=55%][align=right]增加数据文件到相应的表空间
[td=44%]出现ORA-600 [td=55%][align=right]根据日志文件的内容查看相应的TRC文件，如果是Oracle的bug，要及时打上相应的补丁
Listener日志：$ORACLE_HOME/network/log2.3. 检查Oracle核心转储目录$ls $ORACLE_BASE/admin/CKDB/cdump/*.trc|wc -l $ls $ORACLE_BASE/admin/CKDB/udump/*.trc|wc –l
如果上面命令的结果每天都在增长，则说明Oracle进程经常发生核心转储。这说明某些用户进程或者数据库后台进程由于无法处理的原因而异常退出。频繁的核心转储特别是数据库后台进程的核心转储会导致数据库异常终止。2.4. 检查Root用户和Oracle用户的email#tail –n 200 /var/mail/root#tail –n 200 /var/mail/oracle
查看有无与Oracle用户相关的出错信息。3. 检查Oracle对象状态在本节主要检查相关Oracle对象的状态，包含：检查Oracle控制文件状态，检查Oracle在线日志状态，检查Oracle表空间的状态，检查Oracle所有数据文件状态，检查Oracle所有表、索引、存储过程、触发器、包等对象的状态，检查Oracle所有回滚段的状态，总共六个部分。3.1. 检查Oracle控制文件状态SQL> select status,name from v$controlfile; STATUS NAME--------------------------------------------------------------------------------------- /data/oradata/CKDB/control01.ctl /data/oradata/CKDB/control02.ctl /data/oradata/CKDB/control03.ctl
输出结果应该有3条以上（包含3条）的记录，“STATUS”应该为空。状态为空表示控制文件状态正常。3.2. 检查Oracle在线日志状态SQL> select group#,status,type,member fromv$logfile; GROUP# STATUS TYPE MEMBER---------- ------- ------- ----------- 3 ONLINE /data/oradata/CKDB/redo03.log 2 ONLINE /data/oradata/CKDB/redo02.log 1 ONLINE /data/oradata/CKDB/redo01.log 4 ONLINE /data/oradata/CKDB/redo04.log 5 ONLINE /data/oradata/CKDB/redo05.log 6 ONLINE /data/oradata/CKDB/redo06.log 6 rows selected
输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。 注：“STATUS”显示为空表示正常。3.3. 检查Oracle表空间的状态SQL> select tablespace_name,status fromdba_tablespaces; TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINEUNDOTBS1 ONLINESYSAUX ONLINETEMP ONLINEUSERS ONLINESJ1 ONLINEADM_INDEX ONLINEHOME_DATA ONLINEHOME_INDEX ONLINEPHOTO_DATA ONLINEPHOTO_INDEX ONLINE。。。。。。。
输出结果中STATUS应该都为ONLINE。3.4. 检查Oracle所有数据文件状态SQL> select name,status from v$datafile; NAME STATUS---------------------------------------------------------/data/oradata/CKDB/system01.dbf SYSTEM/data/oradata/CKDB/undotbs01.dbf ONLINE/data/oradata/CKDB/sysaux01.dbf ONLINE/data/oradata/CKDB/users01.dbf ONLINE/data/oradata/CKDB/sj.dbf ONLINE/data/oradata/CKDB/HOME_DATA1.dbf ONLINE/data/oradata/CKDB/HOME_INDEX1.dbf ONLINE/data/oradata/CKDB/PHOTO_DATA1.dbf ONLINE/data/oradata/CKDB/PHOTO_INDEX1.dbf ONLINE/data/oradata/CKDB/BLOG_DATA1.dbf ONLINE/data/oradata/CKDB/BLOG_INDEX1.dbf ONLINE/data/oradata/CKDB/AUDIO_DATA1.dbf ONLINE/data/oradata/CKDB/AUDIO_INDEX1.dbf ONLINE/data/oradata/CKDB/VIDEO_DATA1.dbf ONLINE/data/oradata/CKDB/VIDEO_INDEX1.dbf ONLINE/data/oradata/CKDB/SYS_DATA1.dbf ONLINE/data/oradata/CKDB/SYS_INDEX1.dbf ONLINE/data/oradata/CKDB/ADM_DATA1.dbf ONLINE/data/oradata/CKDB/ADM_INDEX1.dbf ONLINE/data/oradata/CKDB/perfstat.dbf ONLINE
输出结果中“STATUS”应该都为“ONLINE”。或者：SQL> select file_name,status fromdba_data_files; FILE_NAME STATUS------------------------------------------------------/data/oradata/CKDB/users01.dbf AVAILABLE/data/oradata/CKDB/sysaux01.dbf AVAILABLE/data/oradata/CKDB/undotbs01.dbf AVAILABLE/data/oradata/CKDB/system01.dbf AVAILABLE/data/oradata/CKDB/sj.dbf AVAILABLE/data/oradata/CKDB/perfstat.dbf AVAILABLE/data/oradata/CKDB/HOME_DATA1.dbf AVAILABLE/data/oradata/CKDB/HOME_INDEX1.dbf AVAILABLE/data/oradata/CKDB/PHOTO_DATA1.dbf AVAILABLE
输出结果中“STATUS”应该都为“AVAILABLE”。3.5. 检查无效对象sql>select owner,object_name,object_type fromdba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM'; no rows selected
如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象，或者：SELECT owner, object_name, object_type FROMdba_objects WHERE status= 'INVALID';
3.6. 检查所有回滚段状态SQL> select segment_name,status fromdba_rollback_segs; SEGMENT_NAME STATUS------------------------------ ----------------SYSTEM ONLINE_SYSSMU1$ ONLINE_SYSSMU2$ ONLINE_SYSSMU3$ ONLINE_SYSSMU4$ ONLINE_SYSSMU5$ ONLINE_SYSSMU6$ ONLINE_SYSSMU7$ ONLINE_SYSSMU8$ ONLINE_SYSSMU9$ ONLINE_SYSSMU10$ ONLINE 11 rows selected
输出结果中所有回滚段的“STATUS”应该为“ONLINE”。4. 检查Oracle相关资源的使用情况在本节主要检查Oracle相关资源的使用情况，包含：检查Oracle初始化文件中相关的参数值，检查数据库连接情况，检查系统磁盘空间，检查Oracle各个表空间使用情况，检查一些扩展异常的对象，检查system表空间内的内容，检查对象的下一扩展与表空间的最大扩展值，总共七个部分。4.1. 检查Oracle初始化文件中相关参数值SQL> selectresource_name,max_utilization,initial_allocation, limit_value from v$resource_limit; RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCATLIMIT_VALUE-------------------- ------------------------------ ---------------processes 162 500 500sessions 168 555 555enqueue_locks 136 6930 6930enqueue_resources 111 2660 UNLIMITEDges_procs 0 0 0ges_ress 0 0 UNLIMITEDges_locks 0 0 UNLIMITEDges_cache_ress 0 0 UNLIMITEDges_reg_msgs 0 0 UNLIMITEDges_big_msgs 0 0 UNLIMITEDges_rsv_msgs 0 0 0gcs_resources 0 0 0gcs_shadows 0 0 0dml_locks 76 2440 UNLIMITEDtemporary_table_locks 26 UNLIMITED UNLIMITED transactions 13 610 UNLIMITEDbranches 0 610 UNLIMITEDcmtcallbk 3 610 UNLIMITEDsort_segment_locks 5 UNLIMITED UNLIMITEDmax_rollback_segments 11 610 65535RESOURCE_NAME MAX_UTILIZATIONINITIAL_ALLOCAT LIMIT_VALUE-------------------- ------------------------------ ---------------max_shared_servers 1 UNLIMITED UNLIMITEDparallel_max_servers 16 80 3600 22 rows selected
若LIMIT_VALU-MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora来修改。4.2. 检查数据库连接情况查看当前会话连接数，是否属于正常范围。SQL> select count(*) from v$session; COUNT(*)---------- 29
[color=navy][size=9.0pt] select sid,serial#,username,program,machine,statusfrom v$session;SID SERIAL# USERNAME PROGRAM MACHINE STATUS---- ---------------------- ---------------------------- ------------ -------- 1 3 oracle@xz15saledb (PMON) xz15saledb ACTIVE 2 3 oracle@xz15saledb (DBW0) xz15saledb ACTIVE 3 3 oracle@xz15saledb (DBW1) xz15saledb ACTIVE 4 3 oracle@xz15saledb(LGWR) xz15saledb ACTIVE 5 3 oracle@xz15saledb (CKPT) xz15saledb ACTIVE 6 3 oracle@xz15saledb(SMON) xz15saledb ACTIVE 7 3 oracle@xz15saledb(RECO) xz15saledb ACTIVE 8 1 oracle@xz15saledb (CJQ0) xz15saledb ACTIVE 9 3 oracle@xz15saledb (ARC0) xz15saledb ACTIVE 10 3 oracle@xz15saledb (ARC1) xz15saledb ACTIVE 11 11319 ZK AccPrtInv_svr@xz15tuxedo2 (TNS V1-V3) xz15tuxedo2 INACTIVE 13 48876 ZG upload@xz15saleap (TNS V1-V3) xz15saleap INACTIVE 17 20405 ZK AccCreateRpt@xz15tuxedo1(TNS V1-V3) xz15tuxedo1 INACTIVE 20 12895 ZK OweScanSvr@xz15billdb (TNS V1-V3) xz15billdb INACTIVE
其中：SID 会话(session)的ID号；SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话；USERNAME 建立该会话的用户名；PROGRAM 这个会话是用什么工具连接到数据库的；STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作；如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。如果DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易引起死连接。建议通过sid查到操作系统的spid,使用ps –ef|grep spidno的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接 ）alter system kill session 'SID,SERIAL#';
注意：上例中SID为1到10(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。4.3. 检查系统磁盘空间如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空间。[oracle@AS14~]$ df -hFilesystem Size Used Avail Use% Mounted on/dev/sda5 9.7G 3.9G 5.4G 42% //dev/sda1 479M 16M 438M 4% /boot/dev/sda2 49G 19G 28G 41% /datanone 1014M 0 1014M 0% /dev/shm
4.4. 检查表空间使用情况SQL> selectf.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free" from (selecttablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group bytablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024)))free from dba_free_space group by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+)order by "% Free"; TABLESPACE_NAME TOTAL FREE % Free------------------------------ ---------- --------------------OPERATION_DATA 1800 547 30WAPWEB_DATA 100 36 36OPERATION_INDEX 500 186 37SYSTEM 1024 515 50SYSAUX 1024 534 52SALE8_TEMP 100 62 62SJ1 500 348 70PERFSTAT 500 356 71……….HOME_DATA 100 77 77SYS_INDEX 100 100 100VIDEO_INDEX 100 100 100VIDEO_DATA 100 100 100BLOG_DATA 100 100 100 39 rows selected
如果空闲率%Free小于10%以上（包含10%），则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数据文件的原则是每个数据文件大小为2G或者4G，自动扩展的最大限制在8G。4.5. 检查一些扩展异常的对象sql>select Segment_Name, Segment_Type,TableSpace_Name, (Extents/Max_extents)*100 Percent From sys.DBA_Segments Where Max_Extents != 0 and(Extents/Max_extents)*100>=95 order By Percent; no rows selected
如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。4.6. 检查system表空间内的内容select distinct(owner) from dba_tables where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM' union select distinct(owner) from dba_indexes where tablespace_name='SYSTEM' andowner!='SYS' and owner!='SYSTEM'; no rows selected
如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。4.7. 检查对象的下一扩展与表空间的最大扩展值sql>select a.table_name, a.next_extent,a.tablespace_name from all_tables a, (select tablespace_name, max(bytes) asbig_chunk from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk union select a.index_name, a.next_extent,a.tablespace_name from all_indexes a, (select tablespace_name, max(bytes) asbig_chunk from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk; no rows selected
如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数。5. 检查Oracle数据库备份结果在本节主要检查Oracle数据库备份结果，包含：检查数据库备份日志信息，检查backup卷中文件产生的时间，检查oracle用户的email，总共三个部分。5.1. 检查数据库备份日志信息假设：备份的临时目录为/backup/hotbakup，我们需要检查2009年7月22日的备份结果，则用下面的命令来检查： #cat /backup/hotbackup/hotbackup-09-7-22.log|grep–i error
备份脚本的日志文件为hotbackup-月份-日期-年份.log，在备份的临时目录下面。如果文件中存在“ERROR:”，则表明备份没有成功，存在问题需要检查。5.2. 检查backup卷中文件产生的时间#ls –lt /backup/hotbackup
backup卷是备份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热备份脚本产生的。如果时间不对则表明热备份脚本没执行成功。5.3. 检查oracle用户的email#tail –n 300 /var/mail/oracle
热备份脚本是通过Oracle用户的cron去执行的。cron执行完后操作系统就会发一条Email通知Oracle用户任务已经完成。查看Oracle email中今天凌晨部分有无ORA-，Error，Failed等出错信息，如果有则表明备份不正常。6. 检查Oracle数据库性能在本节主要检查Oracle数据库性能情况，包含：检查数据库的等待事件，检查死锁及处理，检查cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。6.1. 检查数据库的等待事件set pages 80set lines 120col event for a40select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAITfrom v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。6.2. Disk Read最高的SQL语句的获取SQL>SELECTSQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)WHEREROWNUM<=5 desc; 6.3. 查找前十条性能差的sqlSELECT* FROM (SELECT PARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROM V$SQLAREA ORDER BY DISK_READS DESC) WHEREROWNUM<10 ; 6.4. 等待时间最多的5个系统等待事件的获取SELECT* FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BYTOTAL_WAITS DESC) WHERE ROWNUM<=5; 6.5. 检查运行很久的SQLCOLUMNUSERNAME FORMAT A12 COLUMNOPNAME FORMAT A16 COLUMNPROGRESS FORMAT A8 SELECTUSERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' ASPROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERETIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE =HASH_VALUE;
6.6. 检查消耗CPU最高的进程SETLINE 240SETVERIFY OFFCOLUMNSID FORMAT 999COLUMNPID FORMAT 999 COLUMNS_# FORMAT 999COLUMNUSERNAME FORMAT A9 HEADING "ORA USER"COLUMNPROGRAM FORMAT A29COLUMNSQL FORMAT A60COLUMNOSNAME FORMAT A9 HEADING "OS USER"SELECTP.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL#S_#,P.TERMINAL,P.PROGRAM 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 ANDS.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%';
6.7. 检查碎片程度高的表SQL>SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE ownerNOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECTMAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
6.8. 检查表空间的 I/O 比例SQL>SELECTDF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRDPBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHEREF.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;
6.9. 检查文件系统的 I/O 比例SQL>SELECTSUBSTR(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 WHEREA.FILE# = B.FILE#;
6.10. 检查死锁及处理查询目前锁对象信息：col sid for 999999col username for a10col schemaname for a10col osuser for a16col machine for a16col terminal for a20col owner for a10col object_name for a30col object_type for a10selectsid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_idfrom dba_objects o,v$locked_object l,v$sessions where o.object_id=l.object_id ands.sid=l.session_id;
oracle级kill掉该session：alter system kill session'&sid,&serial#';
操作系统级kill掉session：#>kill -9 pid
6.11. 检查数据库cpu、I/O、内存性能记录数据库的cpu使用、IO、内存等使用情况，使用vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。 1. CPU使用情况：[root@sale8 ~]# top top - 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, 0.0% siMem: 16404472k total, 12887428k used, 3517044k free, 60796k buffersSwap: 8385920k total, 665576kused, 7720344k free, 10358384k cached PIDUSER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND30495 oracle 15 0 8329m 866m861m R 10 5.4 7:53.90 oracle 32501 oracle 15 0 8328m 1.7g1.7g S 2 10.6 1:58.38 oracle 32503 oracle 15 0 8329m 1.6g1.6g S 2 10.2 2:06.62 oracle 。。。。。。。
注意上面的蓝色字体部分，此部分内容表示系统剩余的cpu，当其平均值下降至10%以下的时视为CPU使用率异常，需记录下该数值，并将状态记为异常。 2. 内存使用情况：# free -m total used free shared buffers cachedMem: 2026 1958 67 0 76 1556-/+ buffers/cache: 326 1700Swap: 5992 92 5900
如上所示，蓝色部分表示系统总内存，红色部分表示系统使用的内存，黄色部分表示系统剩余内存，当剩余内存低于总内存的10%时视为异常。 3. 系统I/O情况：# iostat -k 1 3Linux 2.6.9-22.ELsmp(AS14) 07/29/2009 avg-cpu: %user %nice %sys %iowait %idle 0.16 0.00 0.05 0.36 99.43 Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnsda 3.33 13.16 50.25 94483478 360665804 avg-cpu: %user %nice %sys %iowait %idle 0.00 0.00 0.00 0.00 100.00 Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnsda 0.00 0.00 0.00 0 0
如上所示，蓝色字体部分表示磁盘读写情况，红色字体部分为cpu IO等待情况。 4. 系统负载情况：#uptime12:08:37 up 162 days, 23:33, 15 users, load average: 0.01,0.15, 0.10
如上所示，蓝体字部分表示系统负载，后面的3个数值如果有高于2.5的时候就表明系统在超负荷运转了，并将此值记录到巡检表，视为异常。6.12. 查看是否有僵死进程select spid from v$process where addr not in(select paddr from v$session);
有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程。6.13. 检查行链接/迁移Sql>select table_name,num_rows,chain_cntFrom dba_tables Where owner='CTAIS2' And chain_cnt<>0;
注：含有long raw列的表有行链接是正常的,找到迁移行保存到chained_rows表中,如没有该表执行../rdbms/admin/utlchain.sql
Sql>analyze table tablename list chained rows;
可通过表chained_rows中table_name,head_rowid看出哪些行是迁移行
如:Sql>create table aa as select a.* from sb_zsxx a,chained_rows bwhere a.rowid=b.head_rowid and b.table_name ='SB_ZSXX';
sql>delete from sb_zsxx where rowid in (select head_rowid from chained_rowswhere table_name = 'SB_ZSXX');
sql>insert into sb_zsxx select * from chained_row where table_name ='SB_ZSXX';6.14. 定期做统计分析对于采用Oracle Cost-Based-Optimizer的系统，需要定期对数据对象的统计信息进行采集更新，使优化器可以根据准备的信息作出正确的explain plan。在以下情况更需要进行统计信息的更新：
1、应用发生变化
2、大规模数据迁移、历史数据迁出、其他数据的导入等
3、数据量发生变化
查看表或索引的统计信息是否需更新，如：
Sql>Select table_name,num_rows,last_analyzed From user_tables wheretable_name ='DJ_NSRXX'
sql>select count(*) from DJ_NSRXX如num_rows和count(*)
如果行数相差很多,则该表需要更新统计信息，建议一周做一次统计信息收集，如：
Sql>exec sys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade=> TRUE,degree => 4);6.15. 检查缓冲区命中率SQL> SELECT a.VALUE + b.VALUE logical_reads, c.VALUEphys_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='dbblock gets' ANDb.NAME='consistent gets' ANDc.NAME='physical reads' ; LOGICAL_READS PHYS_READS HIT_RATIO------------- ---------- ---------- 1273645705 71191430 94.4104
如果命中率低于90% 则需加大数据库参数db_cache_size。6.16. 检查共享池命中率SQL> select sum(pinhits)/sum(pins)*100 fromv$librarycache; SUM(PINHITS)/SUM(PINS)*100-------------------------- 99.5294474716798
如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。6.17. 检查排序区SQL> select name,value from v$sysstat wherename like '%sort%'; NAME VALUE--------------------------------------------------------------------------sorts (memory) 6135534sorts (disk) 8sorts (rows) 2264742084
如果disk/(memoty+row)的比例过高，则需要调整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。6.18. 检查日志缓冲区SQL> select name,value from v$sysstat wherename in ('redo entries','redo buffer allocation retries'); NAME VALUE--------------------------------------------------------------------------redo entries 27663705redo buffer allocation retries 880
如果redo buffer allocationretries/redo entries 超过1% ，则需要增大log_buffer。7. 检查数据库安全性在本节主要检查Oracle数据库的安全性，包含：检查系统安全信息，定期修改密码，总共两个部分。7.1. 检查系统安全日志信息系统安全日志文件的目录在/var/log 下，主要检查登录成功或失败的用户日志信息。检查登录成功的日志：[root@rac2 ~]# grep -i accepted /var/log/secureJan 808:44:43 rac2 sshd[29559]: Accepted password for root from ::ffff:10.10.10.6port 1119 ssh2……
检查登录失败的日志：[root@rac2 ~]# grep -i inval /var/log/secure&&grep -i failed /var/log/secureJan 910:30:44 rac2 sshd[3071]: Invalid user ydbuser from ::ffff:192.168.3.5Jan 910:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from::ffff:192.168.3.5 port 36005 ssh2Jan 910:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from::ffff:192.168.3.5 port 36005 ssh2Jan 10 22:44:38 rac2 sshd[21611]: Failedpassword for root from ::ffff:10.10.10.6 port 1723ssh2
在出现的日志信息中没有错误(Invalid、refused)提示，如果没有(Invalid、refused)视为系统正常，出现错误提示，应作出系统告警通知。7.2. 检查用户修改密码在数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别有用心的人利用来攻击系统甚至进行修改数据。需要修改密码的用户包括：
数据库管理员用户SYS，SYSTEM；其他用户。
登陆系统后，提示符下输入cat /etc/passwd，在列出来的用户中查看是否存在已经不再使用的或是陌生的帐号。若存在，则记录为异常。修改密码方法：Sql>alter user USER_NAME identified byPASSWORD;
8. 其他检查在本节主要检查当前crontab任务是否正常，检查Oracle Job是否有失败等共六个部分。8.1. 检查当前crontab任务是否正常[oracle@AS14 ~]$ crontab -l
8.2. Oracle Job是否有失败Sql>select job,what,last_date,next_date,failures,brokenfrom 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;8.3. 监控数据量的增长情况SQL>select 2 A.tablespace_name,(1-(A.total)/B.total)*100used_percent 3 from(select tablespace_name,sum(bytes) total 4 fromdba_free_space group by tablespace_name) A, 5 (select tablespace_name,sum(bytes) total 6 fromdba_data_files group by tablespace_name) B 7 where A.tablespace_name=B.tablespace_name; TABLESPACE_NAME USED_PERCENT------------------------------------------HOME_INDEX 1.5BLOG_DATA 0.375VIDEO_DATA 0.25VIDEO_INDEX 0.25SYS_DATA 9.5SYS_INDEX 0.4375CURRENCY_INDEX 13UNDOTBS1 2.3055555555SYSAUX 47.875976562
根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相应的措施：--- 删除历史数据移动规定数据库中至少保留6个月的历史数据，所以以前的历史数据可以考虑备份然后进行清除以便释放其所占的资源空间。--- 扩表空间altertablespace add datafile ‘’ size autoextend off;
注意：在数据库结构发生变化时，如增加了表空间，增加了数据文件或重做日志文件这些操作，都会造成Oracle数据库控制文件的变化，DBA应及进行控制文件的备份，备份方法是：执行SQL语句：alterdatabase backup controlfile to '/home/backup/control.bak';
或：alterdatabase backup controlfile to trace;
这样，会在USER_DUMP_DEST(初始化参数文件中指定)目录下生成创建控制文件的SQL命令。8.4. 检查失效的索引Sql>selectindex_name,table_name,tablespace_name,status From dba_indexes Whereowner='CTAIS2' And status<>'VALID';
注：分区表上的索引status为N/A是正常的，如有失效索引则对该索引做rebuild，如：Sql>alterindex INDEX_NAME rebuild tablespace TABLESPACE_NAME;8.5. 检查不起作用的约束SELECTowner, constraint_name, table_name, constraint_type, status FROMdba_constraints WHEREstatus ='DISABLE' and constraint_type='P';
如有失效约束则启用，如：
Sql>alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;8.6. 检查无效的triggerSELECTowner, trigger_name, table_name, status FROM dba_triggers WHERE status ='DISABLED';
如有失效触发器则启用，如：
Sql>alter Trigger TRIGGER_NAME Enable;