一、日常巡检命令
1、检查Oracle实例状态
SQL> set pages 600 lines 600SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;
说明：“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。
2、检查归档是否打开
SQL> select name,log_mode,open_mode from v$database;SQL> archive log list
说明：两个命令都可以查看。“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。在我们的系统中数据库必须运行在归档方式下。
如没开启归档，需要开启归档，步骤如下：
SQL> startup mount #启动数据库到mount状态，必须的SQL> alter database archivelog; #打开归档SQL> archive log list; #检查归档状态SQL> alter database open; #打开数据库SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;#查看实例状态
SQL> alter system switch logfile; #切换日志
3、检查oracle数据库的服务进程
[oracle@myoracledemo ~]$ ps -ef|grep ora_|grep -v grep&&ps -ef|grep ora_|grep -v grep|wc -l
说明：在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：
. Oracle写数据文件的进程，输出显示为：“oradbw0_CKDB”
. Oracle写日志文件的进程，输出显示为：“ora_lgwr CKDB”
. Oracle监听实例状态的进程，输出显示为：“orasmon CKDB”
. Oracle监听客户端连接进程状态的进程，输出显示为：“orapmon CKDB”
. Oracle进行归档的进程，输出显示为：“oraarc0 CKDB”
. Oracle进行检查点的进程，输出显示为：“orackpt CKDB”
. Oracle进行恢复的进程，输出显示为：“orareco CKDB”
4、检查Oracle监听状态
[oracle@myoracledemo ~]$ lsnrctl status
说明：“Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“orcl11g”这一项。
[oracle@myoracledemo ~]$ ps -ef|grep lsn|grep -v grep
4、检查系统和oracle日志文件
[root@myoracledemo ~]# cat /var/log/messages |grep failed
说明：查看是否有与Oracle用户相关的报错信息
5、检查oracle日志文件
[oracle@myoracledemo log]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/alert_orcl11g.log |grep ora-[oracle@myoracledemo log]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/alert_orcl11g.log |grep err[oracle@myoracledemo log]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/alert_orcl11g.log |grep fail
查看重做日志文件
SQL> select group#,members,bytes,status,archived from v$log;
6、检查Oracle控制文件状态
SQL> select status,name from v$controlfile;
说明：“STATUS”应该为空。状态为空表示控制文件状态正常。
7、检查Oracle在线日志状态
SQL> select group#,status,type,member from v$logfile;
说明：输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。 注：“STATUS”显示为空表示正常
8、检查Oracle表空间的状态
SQL> select tablespace_name,status from dba_tablespaces;
说明：输出结果中STATUS应该都为ONLINE。
9、检查Oracle所有数据文件状态
SQL> select name,status from v$datafile;
说明：输出结果中“STATUS”应该都为“ONLINE”。
SQL> select file_name,status from dba_data_files;
输出结果中“STATUS”应该都为“AVAILABLE”。
10、检查无效对象
SQL> select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
说明：如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象
SQL> SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID';
11、检查所有回滚段状态
SQL> select segment_name,status from dba_rollback_segs;
说明：输出结果中所有回滚段的“STATUS”应该为“ONLINE”。
12、检查数据库连接情况
SQL> select count(*) from v$session;SQL> set pages 600 lines 600SQL> select sid,serial#,username,program,machine,status from v$session;
说明：SID 会话(session)的ID号；
SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话；
USERNAME 建立该会话的用户名；
PROGRAM 这个会话是用什么工具连接到数据库的；
STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作；
如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。如果DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易引起死连接。建议通过sid查到操作系统的spid,使用ps –ef|grep spidno的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接 ）
SQL> alter system kill session 'SID,SERIAL#';SQL> alter system kill session '137,27';
注意：上例中SID为1到10(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。
13、检查表空间的使用情况
SQL>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) fWHERE a.tablespace_name = f.tablespace_name(+)order by "% Free";
14、检查一些扩展异常的对象
SQL>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;
15、检查system表空间内的内容
SQL>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' andowner!='SYS' and owner!='SYSTEM';
说明：如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。
16、检查数据库的等待事件
SQL>set pages 80 SQL>set lines 120 SQL>col event for a40 SQL>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等等待事件时，需要对其进行分析，可能存在问题的语句
17、查找前十条性能差的sql
SQL>SELECT * FROM (SELECT PARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<10 ;
18、等待时间最多的5个系统等待事件的获取
SQL>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、检查碎片程度高的表
SQL> 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);
21、检查表空间的 I/O 比例
SQL> SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;
22、检查文件系统的 I/O 比例
SQL> SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#;
23、检查死锁及处理
col sid for 999999col username for a10col schemaname for a10col osuser for a16col machine for a16col terminal for a20col owner for a10col object_name for a30col object_type for a10select 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;
处理方式：
oracle级kill掉该session：alter system kill session '&sid,&serial#';操作系统级kill掉session：#>kill -9 pid
24、检查是否有僵尸进程
SQL> select spid from v$process where addr not in (select paddr from v$session);
25、检查消耗CPU最高的进程
SQL> SET LINE 240SET VERIFY OFFCOLUMN SID FORMAT 999COLUMN PID FORMAT 999 COLUMN S_# FORMAT 999COLUMN USERNAME FORMAT A9 HEADING "ORA USER"COLUMN PROGRAM FORMAT A29COLUMN SQL FORMAT A60COLUMN 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)) SQL FROM 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%';
输入进程PID1868后，可以看到具体的信息
26、检查缓冲区命中率
SQL> 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' ;
27、检查共享池命中率
SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;
28、检查排序区
SQL> select name,value from v$sysstat where name like '%sort%';
29、检查日志缓冲区
SQL> select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');
30、检查Oracle Job是否有失败
SQL> select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
重新JOB的命令exec sys.dbms_job.remove(1);commit;exec sys.dbms_job.isubmit(1,'REFRESH_ALL_SNAPSHOT;',SYSDATE+1/1440,'SYSDATE+4/1440');commit;
31、监控数据量的增长情况
selectA.tablespace_name,(1-(A.total)/B.total)*100 used_percentfrom (select tablespace_name,sum(bytes) totalfrom dba_free_space group by tablespace_name) A,(select tablespace_name,sum(bytes) totalfrom dba_data_files group by tablespace_name) Bwhere A.tablespace_name=B.tablespace_name;
32、检查失效的索引
SQL> select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';
33、检查不起作用的索引
SQL> SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status ='DISABLE' and constraint_type='P';
34、检查无效的trigger
SQL> SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
如有失效触发器则启用，如：
Sql>alter Trigger TRIGGER_NAME Enable;
35、查看数据库使用参数文件（SPFILE 还是 PFILE）
SQL> show parameter spfile;
36、开启AWR报告进行性能分析
SQL>show parameter statistics_level; #查看awr是否启用SQL> ALTER SYSTEM SET statistics_level = ALL SCOPE = BOTH; #启动设置参数SQL> show parameter statistics_level;#查看awr参数SQL> select * from dba_hist_wr_control; 查看当前的AWR保存策略SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); #手动生成快照SQL>select * from sys.wrh$_active_session_history 查看历史快照SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql #生成AWR报告（生成整个数据库）------------------------------------------------------------------------------SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql #生成 Oracle RAC AWR 报告SQL>@$ORACLE_HOME/rdbms/admin/awrrpti.sql #生成 RAC 环境中特定数据库实例的 AWR 报告SQL>@$ORACLE_HOME/rdbms/admin/awrgrpti.sql #生成 Oracle RAC 环境中多个数据库实例的 AWR 报告SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql #生成 SQL 语句的 AWR 报告SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql #生成特定数据库实例上某个 SQL 语句的 AWR 报告SQL>@$ORACLE_HOME/rdbms/admin/awrddrpt.sql #生成单实例 AWR 时段对比报告
切换到主机目录下，查看html文件即可
37、查询系统检查点SCN的命令
SQL> select CHECKPOINT_CHANGE# from v$database;
38、查询控制文件中数据文件的SCN命令
SQL> select name,file#,checkpoint_change# from v$datafile;
39、查询数据文件头部的SCN号
select name,checkpoint_change# from v$datafile_header;
40、控制文件中的数据文件终止scn
SQL> select name,last_change# from v$datafile;
41、查询字符集命令
SQL> select userenv ('language') from dual;
42、检查软分析百分比
SQL> select sum(a.value)/count(*) pct from v$metric_history a where a.metric_name = 'Soft Parse Ratio' and group_id = 2 and a.begin_time >= sysdate - 1 and a.end_time < sysdate;
43、检查share pool可用空间百分比
SQL> select freesize freesize_MB, totalsize totalsize_MB, round((freesize/totalsize)*100,2) " FREE%" from (select ceil(sum(bytes) / (1024 * 1024)) totalsize from v$sgastat where pool = 'shared pool' ), (select ceil(bytes / (1024 * 1024)) freesize from v$sgastat where name = 'free memory' and pool = 'shared pool');
二、运维小技巧
1、查询用户下所有创建表的语句
SQL>select'select dbms_metadata.get_ddl('||''''||'TABLE'||''''||','||''''||table_name||''''||') from dual;'||chr(10)||'select '||''''||'/'||''''|| ' from dual;' from user_tables;
2、查询当时创建用户的sql语句
SQL>select dbms_metadata.get_ddl('USER',u.username) from dba_users u;
3、创建DBLINK
create public database link HOconnect to SKDATA identified by oracleusing '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SYS$USERS) ) )';
4、查询是否创建了DBLINK
SQL> select * from dba_db_links;
5、设置密码无期限
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
6、rman物理备份
[oracle@myoracledemo ~]$ rman target /RMAN> show all;RMAN> crosscheck archivelog all;RMAN> crosscheck backup;RMAN> delete expired archivelog all;RMAN> delete expired backup;RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;RMAN> run{allocate channel d1 type disk;sql 'alter system archive log current';backup format '/home/oracle/rmanbak/lacgsfull_%U' database include current controlfile plus archivelog ;release channel d1;}RMAN> list backup of database; #查看备份文件
备份脚本：
[oracle@myoracledemo rmanbak]$ cat 1.sh #!/bin/bash. ~/.bash_profileecho -------------------------start-----------------------;daterman target /<<EOFbackup as compressed backupset database include current controlfile format '/home/oracle/rmanbak/full_%U.bak';delete noprompt obsolete;#自动删除七天前的归档日志delete noprompt force archivelog all completed before 'sysdate-7';exit;EOFecho -------------------------end-----------------------;date
添加定时任务：
[oracle@myoracledemo rmanbak]$ crontab -l0 1 * * * /home/oracle/rmanbak/rmanbak.sh >rmanbak.log 2>&1
7、定时删除归档日志
[oracle@myoracledemo ~]$ cat del_arch.sh#!/bin/bashsource ~/.bash_profiledeltime=`date +"20%y%m%d%H%M%S"`rman target / nocatalog msglog /home/oracle/scripts/del_arch_${deltime}.log<<EOFcrosscheck archivelog all;delete noprompt archivelog until time 'sysdate-7';delete noprompt force archivelog until time 'SYSDATE-10';EOF添加定时任务删除[root@myoracledemo ~]# cat /var/spool/cron/oracle 0 1 * * * /home/oracle/rmanbak/rmanbak.sh >rmanbak.log 2>&112 00 * * * /home/oracle/del_arch.sh
8、检查回收站的配置
SQL> select y.ksppstvl value from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and x.ksppinm like lower('recyclebin');
欢迎关注公众号《小周的数据库进阶之路》，更多精彩知识和干货尽在其中。
