oracle巡检手册技术分析.docx

Oracle巡检手册 第一部分数据库状态监控 首先检查oracle的log，在sqlplus中： show parameter background_dump_dest; select * from v$diag_info; 可以得到日志路径 1:检查oracle 监听lsnrtcl status Ps –ef|grep ora 2:检查oracle初始化参数 Select * from v$parameter; 3:检查oracle实例状态 Select instance_name,version,status,database_status from v$instance; select inst_id,instance_name,host_name,VERSION,TO_CHAR(startup_time,yyyy-mm-dd hh24:mi:ss)startup_time,status,archiver,database_status FROM gv$instance; 4:检查后台进程状态： select name,Description From v$BGPROCESS Where Paddr00 5:查看系统全局区SGA信息 select * from v$sga; 6: 查看SGA各部分占用内存情况： select * from v$sgastat; select request_misses,request_failures from v$shared_pool_reserved; 比较好的状态：REQUEST_MISSES REQUEST_FAILURES为0或者接近0 REQUEST_MISSES REQUEST_FAILURES 0 7：查看系统SCN号 select (select dbms_flashback.get_system_change_number from dual)scn,current_scn,scn_to_timestamp(current_scn)from v$database; 8：检查数据库状态： select name,log_mode,open_mode,platform_name from v$database; select inst_id,dbid,name,to_char(created,yyyy-mm-dd hh24:mi:ss)created,log_mode,to_char(version_time,yyyy-mm-dd hh24:mi:ss)version_time,open_mode from gv$database; 第二部分：数据库空间监控 检查表空间使用率 select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percent from (select tablespace_name, sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name, sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name = B.tablespace_name; 检查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 and owner != SYS and owner != SYSTEM; 输出：no rows selected 分析：如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。 如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。 检查表空间使用率 select a.tablespace_name,a.bytes/1024/1024 Sum MB,(a.bytes-b.bytes)/1024/1024 used MB,b.bytes/1024/1024 free MB,round(((a.bytes-b.