oracle数据库日常维护语句

一、检查数据库基本情况
1.1、检查orale数据库实例状态:
1.1.1 select v.INSTANCE_NAME,v.HOST_NAME,v.STARTUP_TIME,v.STATUS,v.DATABASE_STATUS from v$instance v ;
--status表示oracle当前的实例状态(open表示打开),
--database_status表示oracle当前数据库状态(active表示活跃)
1.1.2 select v.NAME,v.LOG_MODE,v.OPEN_MODE from v$database v;
--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写数据文件的进程，输出显示为：“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监听状态:
$ lsnrctl status
1.3.1 检查监听进程是否存在:
$ ps -ef|grep lsn|grep -v grep
二、检查系统和oracle日志文件
2.1、检查操作系统日志文件:
# cat /var/log/messages |grep failed(查看是否有和oracle用户相关的出错信息)
2.2、检查oracle日志文件:
$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep ora-
$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep err
$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep fail
Listener日志：$ORACLE_HOME/network/log
2.3、检查ROOT用户和oracle用户的email(查看是否有和oracle用户相关的出错信息):
#tail –n 200 /var/mail/root
#tail –n 200 /var/mail/oracle
三、检查oracle对象状态:
3.1、检查oracle控制文件状态:
select v.STATUS,v.NAME from v$controlfile v;
--status应为空，状态空表示控制文件正常
--name应该>=3条，表示默认的3个控制文件，以.ctl结尾
3.2、检查oracle在线日志状态:
select v.GROUP#,v.STATUS,v.TYPE,v.MEMBER from v$logfile v;
--输出结果>=3，默认3组，status为空表示正常
3.3、检查oracle表空间的状态：
select t.TABLESPACE_NAME,t.STATUS from dba_tablespaces t;
--输出的结果中的status应该都为online;
3.4、检查oracle所有数据文件状态：
3.4.1、select v.NAME,v.STATUS from v$datafile v;
--输出结果中的status应该都是ONLINE
3.4.2、select t.FILE_NAME,t.STATUS from dba_data_files t;
--输出结果中的status应该都是AVAILABLE
3.5、检查无效对象：
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM'; 
3.6、检查所有回滚段状态：
select t.segment_name,t.status from dba_rollback_segs t;
--输出结果中的status应该为ONLINE
四、检查oracle相关资源的使用情况：
4.1、检查oracle初始化文件中相关参数值：
select v.RESOURCE_NAME,v.MAX_UTILIZATION,v.INITIAL_ALLOCATION,v.LIMIT_VALUE from v$resource_limit v;
--如果LIMIT_VALUE-MAX_UTILIZATION <=5,需要调整初始化参数文件.ora中的与RESOURCE_NAME相关的初始化参数
4.2、检查数据库连接情况:
4.2.1、查看连接总数
select count(1) from v$session;
4.2.2、查看连接的详情
select v.SID,v.SERIAL#,v.USERNAME,v.PROGRAM,v.MACHINE,v.STATUS from v$session v;
--sid表示会话的ID号
--SERIAL#表示会话的序列号，和SID一起用来唯一标识一个会话
--PROGRAM表示这个会话是用什么工具连接到数据库的
--STATUS表示这个会话的状态，active表示会话在执行某些任务，inactive表示没有执行操作
--username为空是oracle的后台进程，不要对会话做任何操作
4.2.3、杀死连接会话
$ alter system kill session 'SID,SERIAL#';
确定spid是不是oracle后台进程:
$ ps -ef|grep spidno
4.3、检查系统磁盘空间：
$ df -h
4.4、检查表空间使用情况：
select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"
 from
 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";
--%Free<= 10,需要增加数据文件来扩展表空间，但不要对表空间增加过多的数据文件，增加每个数据文件大小为2G或者4G，自动扩展最大限制8G
4.5、检查一些扩展异常的对象：
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; 
--有返回值，表示对象的扩展快达到定义的最大扩展值，需要修改它的存储结构参数
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' and
owner!='SYS' and owner!='SYSTEM';
--有返回值表示system表空间内存在一些非system和sys用户的对象，确定这些对象是否与应用有关，有则移到非system表空间，
--同时应该检查对象属主的缺省表空间值
4.7、检查对象的下一扩展与表空间的最大扩展值：
select a.table_name, a.next_extent, a.tablespace_name 
from all_tables a, 
(select tablespace_name, max(bytes) as big_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) as big_chunk 
from dba_free_space 
group by tablespace_name ) f 
where f.tablespace_name = a.tablespace_name 
and a.next_extent > f.big_chunk;
--有返回值表示对象的下一个扩展>对象所属表空间的最大扩展值，需要调整对应表空间的存储参数
五、检查oracle数据库备份结果：
5.1、检查数据库备份日志信息：
#cat /backup/hotbackup/hotbackup-17-7-04.log|grep –i error
--备份的临时目录:/backup/hotbackup
--检查时间:2017-07-04
5.2、检查backup卷中文件产生的时间：
#ls -lt /backup/hotbackup
5.3、检查oracle用户的email:
#tail -n 300/var/mail/oracle
--热备份脚本通过oracle用户的cron去执行，cron执行完成操作系统发一条email通知oracle用户
六、检查oracle数据库性能：
6.1、检查数据库的等待事件：
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%';
--输出结果中的event出现大量latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等待事件，需要重点分析可能存在问题的语句
6.2、获取Disk Read(读取磁盘)最高的SQL语句：
select a.sql_text from (select * from v$sqlarea v order by v.DISK_READS desc) a where rownum<=5 ;
6.3、查找前10条性能差的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 ;
6.4、查找等待事件最多的5个系统等待事件：
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
6.5、查找运行很久的sql：
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;
6.6、查找消耗CPU最高的进程：
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%';
6.7、查找碎片程度高的表：
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);
6.8、检查表空间的I/O比例：
SELECT b.TABLESPACE_NAME as 表空间名,b.FILE_NAME as 文件名,a.PHYRDS as 已完物理读取次数, a.PHYBLKRD as 读取物理块数量,a.PHYWRTS as 已完物理写入次数, a.PHYBLKWRT as 写入物理块数量 
FROM V$FILESTAT a, DBA_DATA_FILES b WHERE a.FILE# = b.FILE_ID ORDER BY b.TABLESPACE_NAME;
6.9、检查文件系统I/O比例：
SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS as 已完物理读取次数,B.PHYWRTS as 已完物理写入次数
 FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#;
6.10、检查死锁以及处理死锁：
 6.10.1、检查死锁：
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;
 6.10.2、oracle级kill会话：
alter system kill session 'SID,SERIAL#';
 6.10.3、操作系统级kill会话:
#kill -9 pid
6.11、检查数据库cpu、I/O、内存性能：
6.11.1、cpu使用情况：
#top
--出现的结果查找 0.0 ni, 99.3 id(99.3表示系统剩余cpu,如果其平均值下降到10%及以下，cpu使用异常)
6.11.2、内存使用情况：
#free -m
--出现下述结果,tatal表示总内存，used表示系统使用内存，free表示剩余内存，如free<total的10%即为异常
               total        used        free      shared  buff/cache   available
Mem:           1984         259        1262           9         462        1513
Swap:          2047           0        2047
6.11.3、系统I/O情况：
#iostat -k 1 3
--出现下述结果，%iowait表示cpuIO等待情况，kB_read/s、 kB_wrtn/s 表示磁盘读写情况
Linux 3.10.0-514.el7.x86_64 (zdh)       2017年07月04日  _x86_64_        (1 CPU)


avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0%11    0.00    0.27    0.28    0.00   99.33


Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               1.64        27.23         2.14     372489      29221
scd0              0.00         0.00         0.00         44          0
6.12、查看是否有僵死进程:
select spid from v$process where addr not in (select paddr from v$session);
6.13、检查行链接/迁移：
select table_name,num_rows,chain_cnt From dba_tables Where owner='CTAIS2' And chain_cnt<>0;
6.14、检查缓冲区命中率:
SELECT a.VALUE + b.VALUE logical_reads, 
 c.VALUE phys_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='db block gets' 
  AND b.NAME='consistent gets' 
  AND c.NAME='physical reads' ;
--HIT_RATIO<90%,那么需要加大数据库参数db_cache_size
6.15、检查共享池命中率：
select sum(pinhits)/sum(pins)*100 from v$librarycache;
--如果输出结果<95%，那么需要调整应用程序使用绑定变量或者调整数据库参数shared pool大小
6.16、检查排序区：
select name,value from v$sysstat where name like '%sort%';
--如果disk/(memory+rows)的比例过高，则调整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)
6.17、检查日志缓冲区:
select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');
--redo buffer allocation retries/redo entries>1%,需要增大log_buffer
七、其他检查：
7.1、检查当前crontab任务是否正常;
$ crontab -l
7.2、oracle job是否失败：
select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
7.3、监控数据量的增长情况：
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;
7.4、扩表空间：
alter tablespace <tablespace_name> add datafile ‘<file>’ size <size>  autoextend off;
--数据库结构发生变化，如增加表空间、数据文件或者重做日志的操作，需要备份
7.4.1、备份控制文件:
方法1：
alter database backup controlfile to '/home/backup/control.bak';
方法2：
alter database backup controlfile to trace;
7.5、检查失效的索引：
select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';
--输出结果status为N/A是正常，索引失效则重建rebuild
7.5.1、重建索引：
alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;
7.6、检查不起作用的约束：
SELECT owner, constraint_name, table_name, constraint_type, status 
FROM dba_constraints 
WHERE status ='DISABLE' and constraint_type='P';
--如约束失效则启用
7.6.1、启用约束：
alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;
7.7、检查无效的触发器：
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
--如有失效的触发器，则启用
7.7.1、启用触发器：
alter Trigger TRIGGER_NAME Enable;