Oracle巡检内容

1. Oracle进程检查

Ps –ef |grep ora

2. CRS进程检查

$ps –df |grep d.bin

      应有：crsd.bin ocssd.bin evmd.bin

crsctl check crs

crs_stat –t

crs_stat –ls

 

asm进程检查：

ps –ef|grep asm

ORACLE_SID=+ASM1

select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

select * from v$asm_disk;

检查状态：

srvctl status database –d dbname

3. Oracle数据库实例状态检查

SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time, status, archiver, database_status FROM gv$instance;

4. Oracle数据库打开状态检查

SELECT inst_id, dbid, NAME, TO_CHAR (created, 'yyyy-mm-dd hh24:mi:ss') created, log_mode, TO_CHAR (version_time, 'yyyy-mm-dd hh24:mi:ss') version_time,open_mode

FROM gv$database;

 

5.数据库表空间使用情况检查;

   5.1表空间的空间使用情况

SELECT  df.tablespace_name, COUNT (*) datafile_count,

ROUND (SUM (df.BYTES) / 1048576) size_mb,

ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,

ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,

ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,

100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,

ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free

FROM dba_data_files df,

 (SELECT  tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free

WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+)

GROUP BY df.tablespace_name ORDER BY 8;

 

   5.2表空间可用性检查

select tablespace_name,status from dba_tablespaces;

   5.3临时表空间使用情况和性能检查

SELECT tablespace_name, extent_management, block_size, initial_extent, next_extent, min_extents, max_extents, pct_increase FROM dba_tablespaces WHERE CONTENTS = 'TEMPORARY';

 

SELECT username, default_tablespace, temporary_tablespace FROM dba_users;

期望结果：根据现场实际情况，应用用户的缺省表空间不能为system，临时表空间必须为temp。

 

 

   5.4回滚表空间使用情况检查和性能检查

SELECT * FROM v$parameter WHERE NAME LIKE 'undo%';

select segment_name,status from dba_rollback_segs;

   5.5根据几次检查的统计信息估计表空间的增长情况，并且据此为人员提供空间升级建议。

col file_name format a20

col size_mb format a10

SELECT df.file_id, df.file_name, df.size_mb,

            NVL (free.maxfree, 0) maxfree_mb,

            ROUND (NVL (free.free_mb, 0), 2) free_mb,

            100 - ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_used,

            ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_free

       FROM (SELECT file_id, file_name, tablespace_name, BYTES / 1048576 size_mb

               FROM dba_data_files) df,

            (SELECT  file_id, SUM (BYTES) / 1048576 free_mb,

                      TRUNC (MAX (BYTES / 1024 / 1024), 2) maxfree

                 FROM dba_free_space

             GROUP BY file_id) free

       WHERE df.file_id = free.file_id(+)

       ORDER BY 7;

 

6.数据库告警日志检查;

   6.1检查自上次检查以来是否有数据库结构的修改，如果有则与管理人员加以确认。

   6.2分析最近时间段redo的切换频率，如果过于频繁或间隔太长则与管理人员确认解决方案。

SELECT  TO_CHAR (first_time, 'yyyy-mm-dd') DAY, COUNT (*) switch_times, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h00, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) h01, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) h02, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) h03, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) h04, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) h05, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) h06, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) h07, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) h08,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) h09,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) h10,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) h11,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) h12,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) h13,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) h14,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) h15,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) h16,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) h17,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) h18,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) h19,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) h20,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) h21,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) h22,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) h23

FROM v$log_history

WHERE first_time > TRUNC (SYSDATE - 30)

GROUP BY ROLLUP (TO_CHAR (first_time, 'yyyy-mm-dd'));

 

   6.3查找自上次检查以来所有的ora错误并作出分析。

   6.4根据系统情况为管理人员提供日志清理建议。

7.数据文件位置检查和可用性检查。

col name format a30

Select name,status from v$datafile

或

SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafileUNIONALL SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$tempfile;

 

8. Redo log多元性检查和可用性检查。

select * from v$log;

9.控制文件多元性检查。

Select * from v$controlfile;

10.归档检查和归档空间检查。

LOCATION=/oracle/product/10.1.0/db/dbs/arc

11.磁盘状态检查;ASM

   11.1磁盘可用性检查

   11.2文件系统可用性检查

   11.3文件系统空间检查

   11.4根据几次检查的统计信息估计表空间的增长情况，并且据此为管理人员提供空间升级建议

   11.5根据系统情况为管理人员提供空间清理建议

 

12.数据库检查。

14. crs状态检查

/crs/bin/crs_stat -t

   14.1 crs日志检查

   14.2 Global设备信息检查

15. Listener状态检查

   15.1 listener可用性检查

Lsnrctl status

   15.2 listener日志检查

/u01/app/oracle/product/9.2.0/network/log/listener.log

/u01/app/oracle/product/9.2.0/network/log/listener.log

16.数据库共享池性能检查

SELECT request_misses, request_failures FROM v$shared_pool_reserved;

期望结果：request_misses和request_failures应该接近于0。

巡检说明：request_misses是保留列表没有满足请求的可用内存片从而开始利用LRU列表刷新对象的次数；request_failures是未找到满足请求的内存次数。

18.数据库redo log缓冲区检查

SELECT   TO_CHAR (ROUND ((r.VALUE / e.VALUE) * 100, 2), '990.99' ) || '%' "redolog buffer retry ratio" FROM v$sysstat r, v$sysstat e WHERE r.NAME = 'redo buffer allocation retries' AND e.NAME = 'redo entries';

期望结果：应该小于5%。

巡检说明：由于数据库的所有DML和DLL操作在执行之前必须在重做日志缓冲区生成一条记录，故重做日志缓冲区内的竞争将严重影响数据库的性能。在重做日志缓冲区内的竞争主要有两类，latch竞争和过量请求竞争。

 

19、检查无效的对象

select owner,object_name,object_type,status from dba_objects where owner like 'FOUNDER' and status not like 'VALID';

 

SELECT object_id, owner || '.' || object_name object_name, object_type, status, TO_CHAR (created, 'yy-mm-dd hh24:mi:ss') created, TO_CHAR (last_ddl_time, 'yy-mm-dd hh24:mi:ss') last_ddl_time FROM all_objects WHERE status != 'VALID';

 

20、检查JOB状态

Select job,BROKEN,WHAT from dba_jobs;

21、监控表的增长

select segment_name,segment_type,bytes/1024/1024 from dba_segments where wner='FOUNDER' ORDER BY bytes/1024/1024 desc；

22、表和索引分析信息

SELECT  'table', COUNT (*) FROM dba_tables WHERE last_analyzed IS NOT NULL GROUP BY 'table'

UNION ALL

SELECT  'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';

23、资源限制信息

SELECT * FROM v$resource_limit;

期望结果：max_utilization与limit_value的差值，应至少相差10。

24、未建索引的表

      SELECT  /*+ rule */

              owner, segment_name, segment_type, tablespace_name,

              TRUNC (BYTES / 1024 / 1024, 1) size_mb

         FROM dba_segments t

        WHERE NOT EXISTS (

                    SELECT 'x'

                      FROM dba_indexes i

                     WHERE t.owner = i.table_owner

                           AND t.segment_name = i.table_name)

          AND t.segment_type IN ('TABLE', 'TABLE PARTITION')

          AND t.owner NOT IN ('SYS', 'SYSTEM')

      ORDER BY 5 DESC;

升序用ASC

期望结果：不应该含有比较大的而又不含索引的正式表。