oracle日常巡检命令

一、日常巡检命令

1、检查Oracle实例状态

1. SQL> set pages 600 lines 600

2. SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;

说明：“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。

2、检查归档是否打开

1. SQL> select name,log_mode,open_mode from v$database;

2. SQL> archive log list

说明：两个命令都可以查看。“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。在我们的系统中数据库必须运行在归档方式下。
 

 

如没开启归档，需要开启归档，步骤如下：

1. SQL> startup mount #启动数据库到mount状态，必须的

2. SQL> alter database archivelog; #打开归档

3. SQL> archive log list; #检查归档状态

4. SQL> alter database open; #打开数据库

5. SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;#查看实例状态

1. SQL> alter system switch logfile; #切换日志

3、检查oracle数据库的服务进程

1. [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监听状态

1. [oracle@myoracledemo ~]$ lsnrctl status

说明：“Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“orcl11g”这一项。

1. [oracle@myoracledemo ~]$ ps -ef|grep lsn|grep -v grep

4、检查系统和oracle日志文件

1. [root@myoracledemo ~]# cat /var/log/messages |grep failed

说明：查看是否有与Oracle用户相关的报错信息

5、检查Oracle日志文件

1. [oracle@myoracledemo log]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/alert_orcl11g.log |grep ora-

2. [oracle@myoracledemo log]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/alert_orcl11g.log |grep err

3. [oracle@myoracledemo log]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/alert_orcl11g.log |grep fail

查看重做日志文件

1. SQL> select group#,members,bytes,status,archived from v$log;

6、检查Oracle控制文件状态

1. SQL> select status,name from v$controlfile;

说明：“STATUS”应该为空。状态为空表示控制文件状态正常。

7、检查Oracle在线日志状态

1. SQL> select group#,status,type,member from v$logfile;

说明：输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。注：“STATUS”显示为空表示正常

8、检查Oracle表空间的状态

1. SQL> select tablespace_name,status from dba_tablespaces;

说明：输出结果中STATUS应该都为ONLINE。

9、检查Oracle所有数据文件状态

1. SQL> select name,status from v$datafile;

说明：输出结果中“STATUS”应该都为“ONLINE”。

1. SQL> select file_name,status from dba_data_files;

输出结果中“STATUS”应该都为“AVAILABLE”。

10、检查无效对象

1. SQL> select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';

说明：如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象

1. SQL> SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID';

11、检查所有回滚段状态

1. SQL> select segment_name,status from dba_rollback_segs;

说明：输出结果中所有回滚段的“STATUS”应该为“ONLINE”。

12、检查数据库连接情况

1. SQL> select count(*) from v$session;

2. SQL> set pages 600 lines 600

3. SQL> 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命令杀掉连接 ）

1. SQL> alter system kill session 'SID,SERIAL#';

2. SQL> alter system kill session '137,27';

 

 

注意：上例中SID为1到10(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。

13、检查表空间的使用情况

1. SQL>select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"

2. from

3. (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

4. (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

5. WHERE a.tablespace_name = f.tablespace_name(+)

6. order by "% Free";

14、检查一些扩展异常的对象

1. SQL>select Segment_Name, Segment_Type, TableSpace_Name,

2. (Extents/Max_extents)*100 Percent

3. From sys.DBA_Segments

4. Where Max_Extents != 0 and (Extents/Max_extents)*100>=95

5. order By Percent;

15、检查system表空间内的内容

1. SQL>select distinct(owner) from dba_tables

2. where tablespace_name='SYSTEM' and

3. owner!='SYS' and owner!='SYSTEM'

4. union

5. select distinct(owner) from dba_indexes

6. where tablespace_name='SYSTEM' and

7. owner!='SYS' and owner!='SYSTEM';

说明：如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。

16、检查数据库的等待事件

1. SQL>set pages 80

2. SQL>set lines 120

3. SQL>col event for a40

4. 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

1. SQL>SELECT * FROM (SELECT PARSING_USER_ID

2. EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,

3. SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)

4. WHERE ROWNUM<10 ;

18、等待时间最多的5个系统等待事件的获取

1. SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;

19、检查运行很长的SQL语句

1. COLUMN USERNAME FORMAT A12

2. COLUMN OPNAME FORMAT A16

3. COLUMN PROGRESS FORMAT A8

4. 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、检查碎片程度高的表

1. 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 比例

1. 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 比例

1. 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、检查死锁及处理

1. col sid for 999999

2. col username for a10

3. col schemaname for a10

4. col osuser for a16

5. col machine for a16

6. col terminal for a20

7. col owner for a10

8. col object_name for a30

9. col object_type for a10

10. select sid,serial#,username,SCHEMANAME,osuser,MACHINE,

11. terminal,PROGRAM,owner,object_name,object_type,o.object_id

12. from dba_objects o,v$locked_object l,v$session s

13. where o.object_id=l.object_id and s.sid=l.session_id;

处理方式：

1. oracle级kill掉该session：

2. alter system kill session '&sid,&serial#';

3. 操作系统级kill掉session：

4. #>kill -9 pid

24、检查是否有僵尸进程

1. SQL> select spid from v$process where addr not in (select paddr from v$session);

25、检查消耗CPU最高的进程

1. SQL> SET LINE 240

2. SET VERIFY OFF

3. COLUMN SID FORMAT 999

4. COLUMN PID FORMAT 999

5. COLUMN S_# FORMAT 999

6. COLUMN USERNAME FORMAT A9 HEADING "ORA USER"

7. COLUMN PROGRAM FORMAT A29

8. COLUMN SQL FORMAT A60

9. COLUMN OSNAME FORMAT A9 HEADING "OS USER"

10. 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、检查缓冲区命中率

1. SQL> SELECT a.VALUE + b.VALUE logical_reads,

2. c.VALUE phys_reads,

3. round(100*(1-c.value/(a.value+b.value)),4) hit_ratio

4. FROM v$sysstat a,v$sysstat b,v$sysstat c

5. WHERE a.NAME='db block gets'

6. AND b.NAME='consistent gets'

7. AND c.NAME='physical reads' ;

27、检查共享池命中率

1. SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;

28、检查排序区

1. SQL> select name,value from v$sysstat where name like '%sort%';

29、检查日志缓冲区

1. SQL> select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');

30、检查Oracle Job是否有失败

1. SQL> select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';

1. 重新JOB的命令

2. exec sys.dbms_job.remove(1);

3. commit;

4. exec sys.dbms_job.isubmit(1,'REFRESH_ALL_SNAPSHOT;',SYSDATE+1/1440,'SYSDATE+4/1440');

5. commit;

31、监控数据量的增长情况

1. select

2. A.tablespace_name,(1-(A.total)/B.total)*100 used_percent

3. from (select tablespace_name,sum(bytes) total

4. from dba_free_space group by tablespace_name) A,

5. (select tablespace_name,sum(bytes) total

6. from dba_data_files group by tablespace_name) B

7. where A.tablespace_name=B.tablespace_name;

32、检查失效的索引

1. SQL> select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';

33、检查不起作用的索引

1. SQL> SELECT owner, constraint_name, table_name, constraint_type, status

2. FROM dba_constraints

3. WHERE status ='DISABLE' and constraint_type='P';

34、检查无效的trigger

1. SQL> SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

如有失效触发器则启用，如：

1. Sql>alter Trigger TRIGGER_NAME Enable;

35、查看数据库使用参数文件（SPFILE 还是 PFILE）

1. SQL> show parameter spfile;

36、开启AWR报告进行性能分析

1. SQL>show parameter statistics_level; #查看awr是否启用

2. SQL> ALTER SYSTEM SET statistics_level = ALL SCOPE = BOTH; #启动设置参数

3. SQL> show parameter statistics_level;#查看awr参数

4. SQL> select * from dba_hist_wr_control; 查看当前的AWR保存策略

5. SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); #手动生成快照

6. SQL>select * from sys.wrh$_active_session_history 查看历史快照

7. SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql #生成AWR报告（生成整个数据库）

8. ------------------------------------------------------------------------------

9. SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql #生成 Oracle RAC AWR 报告

10. SQL>@$ORACLE_HOME/rdbms/admin/awrrpti.sql #生成 RAC 环境中特定数据库实例的 AWR 报告

11. SQL>@$ORACLE_HOME/rdbms/admin/awrgrpti.sql #生成 Oracle RAC 环境中多个数据库实例的 AWR 报告

12. SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql #生成 SQL 语句的 AWR 报告

13. SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql #生成特定数据库实例上某个 SQL 语句的 AWR 报告

14. SQL>@$ORACLE_HOME/rdbms/admin/awrddrpt.sql #生成单实例 AWR 时段对比报告

切换到主机目录下，查看html文件即可

37、查询系统检查点SCN的命令

1. SQL> select CHECKPOINT_CHANGE# from v$database;

38、查询控制文件中数据文件的SCN命令

1. SQL> select name,file#,checkpoint_change# from v$datafile;

39、查询数据文件头部的SCN号

1. select name,checkpoint_change# from v$datafile_header;

40、控制文件中的数据文件终止scn

1. SQL> select name,last_change# from v$datafile;

41、查询字符集命令

1. SQL> select userenv ('language') from dual;

42、检查软分析百分比

1. 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可用空间百分比

1. 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、查询用户下所有创建表的语句

1. SQL>select

2. 'select dbms_metadata.get_ddl('||''''||'TABLE'||''''||','||''''||table_name||''''||') from dual;'||chr(10)||'select '||''''||'/'||''''|| ' from dual;'

3. from user_tables;

2、查询当时创建用户的sql语句

1. SQL>select dbms_metadata.get_ddl('USER',u.username) from dba_users u;

3、创建DBLINK

1. create public database link HO

2. connect to SKDATA identified by oracle

3. using '(DESCRIPTION =

4. (ADDRESS_LIST =

5. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))

6. )

7. (CONNECT_DATA =

8. (SERVICE_NAME = SYS$USERS)

9. )

10. )';

4、查询是否创建了DBLINK

1. SQL> select * from dba_db_links;

5、设置密码无期限

1. SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

6、Rman物理备份

1. [oracle@myoracledemo ~]$ rman target /

2. RMAN> show all;

3. RMAN> crosscheck archivelog all;

4. RMAN> crosscheck backup;

5. RMAN> delete expired archivelog all;

6. RMAN> delete expired backup;

7. RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

8. RMAN> run

9. {

10. allocate channel d1 type disk;

11. sql 'alter system archive log current';

12. backup format '/home/oracle/rmanbak/lacgsfull_%U' database include current controlfile plus archivelog ;

13. release channel d1;

14. }

15. RMAN> list backup of database; #查看备份文件

 

 

 

备份脚本：

1. [oracle@myoracledemo rmanbak]$ cat 1.sh

2. #!/bin/bash

3. . ~/.bash_profile

4. echo -------------------------start-----------------------;date

5. rman target /<<EOF

6. backup as compressed backupset database include current controlfile format '/home/oracle/rmanbak/full_%U.bak';

7. delete noprompt obsolete;

8. #自动删除七天前的归档日志

9. delete noprompt force archivelog all completed before 'sysdate-7';

10. exit;

11. EOF

12. echo -------------------------end-----------------------;date

添加定时任务：

1. [oracle@myoracledemo rmanbak]$ crontab -l

2. 0 1 * * * /home/oracle/rmanbak/rmanbak.sh >rmanbak.log 2>&1

7、定时删除归档日志

1. [oracle@myoracledemo ~]$ cat del_arch.sh

2. #!/bin/bash

3. source ~/.bash_profile

4. deltime=`date +"20%y%m%d%H%M%S"`

5. rman target / nocatalog msglog /home/oracle/scripts/del_arch_${deltime}.log<<EOF

6. crosscheck archivelog all;

7. delete noprompt archivelog until time 'sysdate-7';

8. delete noprompt force archivelog until time 'SYSDATE-10';

9. EOF

10. 添加定时任务删除

11. [root@myoracledemo ~]# cat /var/spool/cron/oracle

12. 0 1 * * * /home/oracle/rmanbak/rmanbak.sh >rmanbak.log 2>&1

13. 12 00 * * * /home/oracle/del_arch.sh

8、检查回收站的配置

1. 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');