分类： Oracle
2009-12-09 10:15:37
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错误并作出分析。
7. 数据文件位置检查和可用性检查。
col name format a30
Select name,status from v$datafile
或
SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafile
8. Redo log多元性检查和可用性检查。
select * from v$log;
9. 控制文件多元性检查。
Select * from v$controlfile;
10. 归档检查和归档空间检查。
LOCATION=/oracle/product/
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/
/u01/app/oracle/product/
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 owner='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
期望结果：不应该含有比较大的而又不含索引的正式表。
