oracle数据库巡检内容

1         操作系统错误记录

errpt

/var/log/messages

 

2         网卡状态

ifconfig

 

3         操作系统总体状态

top

topas

cpu user%http://www.cndba.cn/redhat/article/1612

disk busy%

network Kbps

memory %comp

max cpu pro

 

4         操作系统性能

top

topas

1.运行时间及1、5、15分钟CPU的负载情况

 

2.进程统计

 

3.CPU统计

 

4.内存

 

5.交换分区

 

6.具体进程，按%CPU排序

 

检测CPU情况，1，5，15分钟的负载

uptime

 

检测内存状况

free -m

 

检测硬盘状况

df -h

 

通过vmstat命令检测系统

vmstat 5 5

-- r: The number of processes waiting for run time展示了正在执行和等待CPU资源的任务个数。当这个值超过了CPU数目，就会出现CPU瓶颈了

-- wa的值高时，说明IO等待比较严重，这可能是由于磁盘大量作随机访问造成，也有可能是磁盘的带宽出现瓶颈(块操作)。

 

iostat

 

5         文件系统磁盘空间

df -g

 

6         集群运行状态

su - oracle

olsndoes

crsctl query css votedisk

ocrcheck

crsctl check crs

crs_stat -t

lsnrctl status

 

 

 

 

7         检测Oracle死锁

cat alert_orcl.log | grep ORA-00600

select count(*) from v$session where lockwait is not null;

 

8         Oracle实例状态

select instance_name, version, status, database_status from v$instance;

 

9         Oracle数据库状态

select name, log_mode, open_mode, flashback_on from v$database;

 

10    检查数据库进程

ps -ef | grep ora_ | grep -v grep | wc -l

 

show parameter processes

 

11    检查数据库的会话数

一个稳定运行的数据库里，会话数量应保持平稳，如果出现会话数量大幅增加或大幅减少，就意味着可能出现了问题，需要进一步查找原因。（需要与日常稳定数值对比）

select count(*) from v$session;

show parameter sessions;

select sid,serial#,username,program,machine,status from v$session;

#alter system kill session 'SID,SERIAL#';

 

12    检查控制文件

select * from v$controlfile;

 

 

 

13    检查日志文件

select * from v$logfile;

 

14    检查表空间

select tablespace_name,contents,status from dba_tablespaces;

 

检查Oracle表空间使用情况

SELECT d.status "Status",

       d.tablespace_name "Name",

       d.contents "Type",

       d.extent_management "Extent Management",

       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",

       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') || '/' ||

       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",

       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"

  FROM sys.dba_tablespaces d,

       (select tablespace_name, sum(bytes) bytes

          from dba_data_files

         group by tablespace_name) a,

       (select tablespace_name, sum(bytes) bytes

          from dba_free_space

         group by tablespace_name) f

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = f.tablespace_name(+)

   AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')

UNION ALL

SELECT d.status "Status",

       d.tablespace_name "Name",

       d.contents "Type",

       d.extent_management "Extent Management",

       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",

       TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.999') || '/' ||

       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",

       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"

  FROM sys.dba_tablespaces d,

       (select tablespace_name, sum(bytes) bytes

          from dba_temp_files

         group by tablespace_name) a,

       (select tablespace_name, sum(bytes_cached) bytes

          from v$temp_extent_pool

         group by tablespace_name) thttp://www.cndba.cn/redhat/article/1612

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = t.tablespace_name(+)

   AND d.extent_management like 'LOCAL'

   AND d.contents like 'TEMPORARY'http://www.cndba.cn/redhat/article/1612

/

确保表空间剩余空间维持在20%以上

 

 

 

15    检查数据文件

select name, status from v$datafile;

 

检查数据文件的autoextensible

select tablespace_name, file_id, ONLINE_STATUS, autoextensible from dba_data_files union all select tablespace_name, file_id, status, autoextensible from dba_temp_files;

SQL> select tablespace_name, file_id, ONLINE_STATUS, autoextensible from dba_data_files union all select tablespace_name, file_id, status, autoextensible from dba_temp_files;

 

 

16    检查回滚段

select segment_name, status from dba_rollback_segs;

 

查看回滚段是否自动管理

show parameter uodo_management

 

17    检查数据库的无效对象

col object_name for a20

col owner for a15

select owner, object_name, object_type, status from dba_objects where status != 'VALID' and owner != 'SYS' and owner != 'SYSTEM';http://www.cndba.cn/redhat/article/1612

SQL> col object_name for a20

SQL> col owner for a15

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

 

18    检查系统资源限制

select * from v$resource_limit;

 

19    检查Oracle扩展异常对象

select segment_name, segment_type, tablespace_name, (extents/max_extents)*100 percent from dba_segments where max_extents !=0 and (extents/max_extents)*100 >=90 order by percent;

SQL> select segment_name, segment_type, tablespace_name, (extents/max_extents)*100 percent from dba_segments where max_extents !=0 and (extents/max_extents)*100 >=90 order by percent;

 http://www.cndba.cn/redhat/article/1612

20    检查Oracle系统表空间

select distinct(owner) from dba_tables where tablespace_name = 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM'

union all

select distinct(owner) from dba_indexes where tablespace_name = 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM';

 

oracle系统表空间一般是用于存放sys和system用户数据的，通常其它用户的数据是不能存放在系统表空间中，通过检查这项内容，可以发现有哪些非sys和system用户的数据被存放在系统表空间里，以防止其存储空间被过度占用而引起数据库问题。（此类用户属于内部用户，状态正常）

 

21    无效索引

select index_name,index_type,tablespace_name,status from user_indexes;

select index_name,index_type,tablespace_name,status from dba_indexes where status!='VALID';

select index_name,index_type,tablespace_name,status from dba_indexes where status='INVALID' or status='UNUSEABLE';

 

 

 

22    RMAN备份情况（全备或者增量备份）

list backup;

list backup of controlfile;

list backup of database;

list backup of archivelog all;

 

23    逻辑备份情况（EXPDP或EXP）

 

24    生成statspack与AWR报告，对数据库进行具体性能分析

ARW报告存放目录

 

警告日志存放目录

 

 

 

 

 

 

 http://www.cndba.cn/redhat/article/1612

 

 

版权声明：本文为博主原创文章，未经博主允许不得转载。