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$datafile UNION ALL 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

期望結果：不應該含有比較大的而又不含索引的正式表。