ORACLE巡检报告模板.doc

ORACLE数据库系统维护检查报告ORACLE 数据库系统维护检查报告用户基本信息用户名称：联系人：电话/传真：地址：应用系统名称： 巡检时间： 主机厂商/型号： 操作系统/版本：CPU数/硬件内存：数据库服务器运行方式：Oracle产品/版本：系统运行状况检测序号检测内容检测指令检测结果建议1检查alert日志，是否有ora错误提示Alert文件2数据库名SQLshow parameter db_name3实例名SQLshow parameter instance_name4数据库版本SQLselect * from v$version;5是否归档模式SQLarchive log list6安装组件SQLselect * from v$option;7初始化参数SQLshow parameters8SGA区SQLshow sga9用户检查SQLselect username,account_status,default_tablespace,temporary_tablespace,created from dba_users10用户权限检查SQLselect * from dba_role_privs;11是否存在失效对象SQLselect owner， object_name,object_type from dba_objects where status = INVALID12检查控制文件SQLselect * from v$controlfile13检查联机日志SQLselect * from v$logfile; 14检查数据文件SQLselect * from v$datafile;15查看现有回滚段及其状态SQL SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;16检查表空间使用情况18备份策略的讨论与实施注：红色字体表示需要尽快解决的问题。其它检查内容：1、 alert文件：有无错误？2、 表空间使用情况：set linesize 300SELECT upper(f.tablespace_name) tablespace_name, d.Tot_grootte_Mb tablespace(M), d.Tot_grootte_Mb - f.total_bytes used(M), round(d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) use%, f.total_bytes free_space(M), round(f.total_bytes / d.Tot_grootte_Mb * 100,2) free% FROM (SELECT tablespace_name, round(SUM(bytes)/(1024*1024),2) total_bytes, round(MAX(bytes)/(1024*1024),2) max_bytes FROM sys.dba_free_space GROUP BY tablespace_name) f, (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name) dWHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC/3、 Shared Pool Size 命中率：select round(sum(gets)-sum(reloads)/sum(gets)*100,1) libiary cache hit ratio %from v$librarycache where namespacein (SQL AREA,TABLE/PROCEDURE,BODY,TRIGGER);4、 数据字典命中率：select round(1-sum(getmisses)/sum(gets)*100,1) data dictionary hit ratio %from v$rowcache;5、 锁竞争：select substr(,1,25) Name, l.gets, l.misses, 100*(l.misses/l.gets) % Ratio (STAY UNDER 1%) from v$latch l, v$latchname ln where in (cache buffers lru chain) and ln.latch# = l.latch#;6、 排序命中率：select a.value Sort(Disk), b.value Sort(Memory),round(100*(a.value/decode(a.value+b.value), 0,1,(a.value+b.value),2) % Ratio (STAY UNDER 5%) from v$sysstat a, v$sysstat bwhere = sorts (disk)and = sorts (memory);7、 数据缓冲区命中率：select round(1-(phy.value/(cur.value+con.value)*100,1)|% ratio from v$sysstat phy,v$sysstat cur,v$sysstat conwhere =physical reads and =db block gets and =consistent gets;8、 v$session_wait：select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like SQL% and event not like rdbms%;9、 回滚段的争用情况：select name, waits, gets, waits/gets Ratiofrom v$rollstat a, v$rollname bwhere a