oracle巡检命令总结

1.查看oracle后台进程的数量
ps -ef|grep -v grep|grep OINMS|wc -l
2.查看oracle后台进程(自身)
$ ps -ef|grep -v grep|grep -E "ora_dbw0|ora_lgwr|ora_smon|ora_pmon|ora_ckpt|ora_reco|ora_arc0"
3.查看监听进程是否存在
ps -ef|grep -v grep|grep lsn
4.查看oracle日志错误信息
tail -100 /oracle/admin/OINMS/bdump/alert_OINMS.log|grep -iE "ora-|err|fail"|sort -u
5.检查Oracle核心转储目录
ls /oracle/admin/OINMS/udump/*.trc|wc -l
ls /oracle/admin/OINMS/cdump/*.trc|wc -l
6.检查oracle监听状态
lsnrctl status
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.检查Oracle实例状态
select instance_name,host_name,startup_time,status,database_status from v$instance;
2.检查数据库状态
select name,log_mode,open_mode from v$database;
3.查看控制文件
select name,is_recovery_dest_file,status from v$controlfile;
4.查看在线日志文件
select * from v$logfile;
5.查看表空间是否存在离线
select tablespace_name,status from dba_tablespaces where status<>'ONLINE';
6.查看数据文件是否存在离线
select name,status from v$datafile where status<>'ONLINE';
7.查看所有回滚段的在线情况
select segment_name,status from dba_rollback_segs where status<>'ONLINE';
8.检查无效对象
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
9.查看表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　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) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
备注：如果空闲率%Free小于10%以上（包含10%），则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。
请不要对表空间增加过多的数据文件，增加数据文件的原则是每个数据文件大小为2G或者4G，自动扩展的最大限制在8G。
10.检查Oracle初始化文件中相关参数值
select resource_name,max_utilization,initial_allocation,limit_value from v$resource_limit;
备注：若LIMIT_VALU-MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。
可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora来修改。
11.查看扩展异常的对象
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;
备注：数据库中每个segment是由extent组成，而每个segment所能容纳的extent数量是有限制的，
dba_segments中的max_extents列就是每个segment所能容纳的最大extent数量。如果segment中的extent数达到了这个数量的限制，
则segment将无法继续扩展，数据库将报错。因此，通过检查每个segment中的extent数量，可以及时发现数据库中扩展异常的对象，
以便于采取进一步参数，避免出现segment无法扩展的问题出现。（无返回值，状态正常）
如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。
12.检查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';
备注：Oracle系统表空间一般是用于存放sys和system用户数据的，通常其它用户的数据是不能存放在系统表空间中，通过检查这项内容，
可以发现有哪些非sys和system用户的数据被存放在系统表空间里，以防止其存储空间被过度占用而引起数据库问题。（此类用户属于内部用户，状态正常）
13.监控数据量的增长情况
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;
根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相应的措施：
扩表空间
alter tablespace add datafile ‘’ size autoextend off;
注意：在数据库结构发生变化时，如增加了表空间，增加了数据文件或重做日志文件这些操作，都会造成Oracle数据库控制文件的变化，
DBA应及进行控制文件的备份，备份方法是：
执行SQL语句：
alter database backup controlfile to '/home/backup/control.bak';
或：
alter database backup controlfile to trace;
这样，会在USER_DUMP_DEST(初始化参数文件中指定)目录下生成创建控制文件的SQL命令。
14.查看oracle会话数
select count(*) from v$session;
select sid,serial#,username,program,machine,status from v$session;
备注：一个稳定运行的数据库里，会话数量应保持平稳，如果出现会话数量大幅增加或大幅减少，就意味着可能出现了问题，需要进一步查找原因。
如果DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易引起死连接。建议通过sid查到操作系统的spid,
使用ps –ef|grep spidno的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接）
alter system kill session 'SID,SERIAL#';
15.检查数据库的等待事件
set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from 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等等待事件时，需要对其进行分析，可能存在问题的语句。
16.Disk Read最高的SQL语句的获取
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5 
17.查找前十条性能差的sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10;
18.等待时间最多的5个系统等待事件的获取
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
19.查看运行很久的sql
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,
TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS ,
V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
20.查询消耗CPU最高的的进程
SET LINE 240
SET VERIFY OFF
COLUMN SID FORMAT 999
COLUMN PID FORMAT 999
COLUMN S_# FORMAT 999
COLUMN USERNAME FORMAT A9 HEADING "ORA USER"
COLUMN PROGRAM FORMAT A29
COLUMN SQL     FORMAT A60
COLUMN OSNAME FORMAT A9 HEADING "OS USER"
SELECT P.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 AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%';
21.查看碎片程度最高的表
SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
22.查看死锁及处理
col sid for 999999
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v$locked_object l,v$session s
where o.object_id=l.object_id and s.sid=l.session_id;
23.数据库是否有失效索引
select index_name,owner,table_name from dba_indexes where status !='VALID';
注：分区表上的索引status为N/A是正常的，如有失效索引则对该索引做rebuild，如：
Sql>alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;
24.检查不起作用的约束
SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
如有失效约束则启用，如：
Sql>alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;
25.检查无效的trigger
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
如有失效触发器则启用，如：
Sql>alter Trigger TRIGGER_NAME Enable;