数据库巡检.docVIP

日常巡检 数据库巡检 为了保证oracle数据库稳定，高效的运行，每个季度初需要对oracle数据库进行健康检查。以确定数据库是否存在故障及性能问题。 对于异常状况，上报，进一步诊断、分析，及时解决。 巡检工作包括以下细则： ALERT文件 (alertSID.log)是否出现错误信息 top10等待事件 数据库大小 表空间使用情况 内存配置 三个Top10 SQL 内存命中率 归档方式及备份情况 巡检脚本 AlertSID.log文件位置： SQL show parameter background_core_dump 归档方式及备份情况 （1）查看是否为归档方式： SQLarchive log list; （2）说明该数据库备份情况，是否有备份策略。 top10等待事件： 不同的版本，事件的多少不同 Oracle9i select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN (smon timer,pmon timer,rdbms ipc message,Null event,parallel query dequeue,pipe get, client message,SQL*Net message to client,SQL*Net message from client,SQL*Net more data from client, dispatcher timer,virtual circuit status,lock manager wait for remote message,PX Idle Wait, PX Deq: Execution Msg,PX Deq: Table Q Normal,wakeup time manager,slave wait,i/o slave wait, jobq slave wait,null event,gcs remote message,gcs for action,ges remote message,queue messages) ) t1, (select * from ( select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from (select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event where event not in (smon timer,pmon timer,rdbms ipc message,Null event,parallel query dequeue,pipe get, client message,SQL*Net message to client,SQL*Net message from client,SQL*Net more data from client, dispatcher timer,virtual circuit status,lock manager wait for remote message,PX Idle Wait, PX Deq: Execution Msg,PX Deq: Table Q Normal,wakeup time manager,slave wait,i/o slave wait, jobq slave wait,null event,gcs remote message,gcs for action,ges remote message,queue messages) order by time_waited desc ) t) where num11) t2, (SELECT VALUE CPU FROM v$sysstat WHERE NAME LIKE CPU used by this session ) t3 Oracle10g select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN (smon timer,pmon timer,rdbms