Oracle巡检内容(一)

原文地址：Oracle巡检内容(一) 作者：Oo嘟嘟oO

1. Oracle进程检查

Ps –ef |grep ora

2. CRS进程检查

$ps –df |grep d.bin

<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>应有：crsd.bin<wbr></wbr> ocssd.bin<wbr></wbr> evmd.bin

crsctl check crs

crs_stat –t

crs_stat –ls

<wbr></wbr>

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;

<wbr></wbr>

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

<wbr><wbr><wbr></wbr></wbr></wbr> 5.1 表空间的空间使用情况

SELECT<wbr><wbr></wbr></wbr> 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,

<wbr></wbr>(SELECT<wbr><wbr></wbr></wbr> 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;

<wbr></wbr>

<wbr><wbr><wbr></wbr></wbr></wbr> 5.2 表空间可用性检查

select tablespace_name,status from dba_tablespaces;

<wbr><wbr><wbr></wbr></wbr></wbr> 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';

<wbr></wbr>

SELECT username, default_tablespace, temporary_tablespace FROM dba_users;

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

<wbr></wbr>

<wbr></wbr>

<wbr><wbr><wbr></wbr></wbr></wbr> 5.4 回滚表空间使用情况检查和性能检查

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

select segment_name,status from dba_rollback_segs;

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

col file_name format a20

col size_mb format a10

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

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> NVL (free.maxfree, 0) maxfree_mb,

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> ROUND (NVL (free.free_mb, 0), 2) free_mb,

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

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

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr></wbr> FROM (SELECT file_id, file_name, tablespace_name, BYTES / 1048576 size_mb

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> FROM dba_data_files) df,

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> (SELECT<wbr><wbr></wbr></wbr> file_id, SUM (BYTES) / 1048576 free_mb,

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> TRUNC (MAX (BYTES / 1024 / 1024), 2) maxfree

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> FROM dba_free_space

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> GROUP BY file_id) free

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr></wbr>WHERE df.file_id = free.file_id(+)

<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr> <wbr></wbr>ORDER BY 7;

<wbr></wbr>

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

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

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

SELECT<wbr><wbr></wbr></wbr> 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'));

<wbr></wbr>

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

<wbr></wbr>

<wbr></wbr>

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

<wbr></wbr>