Oracle資料庫日常問題-歸檔異常增長

Oracle 資料庫日常問題 - 歸檔異常增長

 

資料庫啟用歸檔模式，主要是保證資料安全，但是如果歸檔增長過快，或者人員維護不合理，可能會導致歸檔檔案把磁碟佔滿，最終資料庫無法正常工作；

資料庫歸檔增長異常，最終導致資料庫無法使用，如何查詢原因，解決問題呢？

1  當出現歸檔空間不足 ，首先需要 通過 擴空間或者移動 ( 刪除 ) 部分歸檔檔案釋放空間，儘快讓資料庫正常工作；

2  資料庫可以後，再去 具體分析歸檔檔案增長過快的原因；

歸檔空間滿了，在刪除歸檔之前需要確定歸檔所在目錄（archive log list ）；

如果歸檔檔案放在預設的閃回區，必須通過RMAN 的 delete 命令進行刪除歸檔，或者直接通過命令擴大閃回區大小，不能通過作業系統命令直接刪除閃回區下的歸檔檔案；

如果歸檔檔案存放路徑是手動指定的其他目錄，非閃回去，除了RMAN 刪除歸檔外，也可以通過作業系統命令移動或刪除歸檔檔案；

1 刪除過期歸檔

刪除過期(expired) 的歸檔，釋放空間；

RMAN> crosscheck archivelog all;

RMAN> list expired archivelog all;

RMAN> delete expired archivelog all;

刪除指定時間歸檔

RMAN>delete archivelog until time 'sysdate-7';  

刪除廢棄(obsolete) 的歸檔，釋放空間；

RMAN> report obsolete;

RMAN> delete obsolete;

擴大歸檔所在空間( 閃回區 ) ；

select  dbid , name , log_mode from  v$database ;

SQL> archive log list;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      D:\app_10.2.0.4\Administrator\flash_recovery_area

db_recovery_file_dest_size           big integer 2530M

select   *   From  v$flash_recovery_area_usage ;

修改閃回 區大小 ：alter system set db_recovery_file_dest_size = 4G （更改大小）

刪除部分歸檔後，資料庫就可以正常工作了，這時需要具體分析歸檔過快的原因；

首先需要知道每天( 每小時 ) 歸檔產生頻率和大小；

如果每天大多數歸檔檔案都某個特定時間內產生的，那麼可能是這段時間有定時JOB ，或者計劃任務，檢視一下這些 JOB 和計劃任務是否合理；

如果每天的每秒每分鐘都在不停的產生歸檔，很可能產品或者資料庫存在BUG ，需要具體分析產生歸檔的 SQL 語句，才能和業務操作聯絡起來；

如果都是工作時間內產生的歸檔，可能是正常業務操作產生的歸檔，具體分析業務操作對應的表，SQL 資訊等，通常情況需要增加儲存空間；

2 分析歸檔過快原因

檢視歸檔引數頻率

檢視資料庫JOB

檢視計劃任務

---- 檢視資料庫歸檔分佈及頻率

3 查詢歸檔增長異常常見方法

一：日誌挖掘

分析多個歸檔檔案中SQL 資訊

1. Enable Supplemental Logging

2. Extract a LogMiner Dictionary  (unless you plan to use the online catalog)

3. Specify Redo Log Files for Analysis

4. Start LogMiner

5. Query V$LOGMNR_CONTENTS

6. End the LogMiner Session

二：AWR 報告

Segments by DB Blocks Changes 結合 TOP SQL 進行分析

歸檔異常增長 案例 一 ：

問題原因： 11g 資料庫自動維護任務 - 段指導 BUG 導致歸檔增長過快。

現象： 平時每天歸檔5G 左右，突然有一天 產生200 多 G 歸檔 。

分析過程： 先通過SQL 檢視全天中每小時歸檔量，找出歸檔最集中的時間段，並收集這一時間段的 AWR 報告，或通過日誌挖掘分析這一時間段的歸檔檔案。

發現大多數歸檔檔案生成時間特別集中，收集這段時間AWR 報告即可。

通過AWR 報告查詢歸檔異常增長原因

檢視問題期間AWR 報告，發現有一條 CTAS 語句特別耗時

SQL 語句如下：

1 ： call dbms_space.auto_space_advisor_job_proc ( )

2 ： create table “XXX".DBMS_TABCOMP_TEMP_UNCMP tablespace NNC_DATA02 as select /*+ full(“ CHENJCH ".SM_PUB_FILESYSTEM)*/ * from “ CHENJCH ".SM_PUB_FILESYSTEM sample block( 41)

其中 SM_PUB_FILESYSTEM 表是一張附件表，包含blob 欄位，大小 200 多 G ，通過 DBMS_SCHEDULER 可知這條耗時耗空間的語句是 Oracle 自動執行到了。

解決方案：

11g 資料庫，自動維護任務 - 段指導 BUG 導致歸檔過快 ，禁用段指導。

begin

  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',

                               operation   => NULL,

                               window_name => NULL);

end;

詳細資訊請參考：

How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP? (Doc ID 1326118.1)

歸檔異常增長 案例 二 ：

問題原因： 應用程式 產品 BUG 導致歸檔 增長異常。

問題現象： 同事反饋，一個小系統，全庫大小不到10G ，但是啟動歸檔模式後，發現每天產生的歸檔檔案有 300G 大小。

分析過程： 先通過SQL 檢視全天中每小時歸檔量，找出歸檔最集中的時間段，並收集這一時間段的 AWR 報告，或通過日誌挖掘分析這一時間段的歸檔檔案。

發現每小時每分鐘都在不斷生成大量歸檔檔案。

檢視AWR 報告：發現產生歸檔的物件主要集中在 ARAP_BALANCE2 物件上。

檢視TOP SQL ，找出 ARAP_BALANCE2 相關 DML 或 DDL 語句。

每分鐘都會執行大量如下SQL ，通過 arap_balance2 表定位到具體的業務，和對應業務開發對接查詢SQL 產生的原因。

1. delete from arap_balance2 where dr = 9

2. update arap_balance2 set dr = 9

3. i nsert into arap_balance2 ( pk_balance, compondmd5, pk_group, pk_org, accperiod, pk_currtype, objtype, billclass, billstatus, effectstatus, customer, supplier, pk_deptid, pk_psndoc, def1, def2, def3, def4, def5, def6, accyear, quantity_de, quantity_cr, money_de, money_cr, local_money_de, local_money_cr, grouplocal_money_de, grouplocal_money_cr, globallocal_money_de, globallocal_money_cr ) values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 )

解決方案：產品BUG ，研發提供補丁

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2675645/，如需轉載，請註明出處，否則將追究法律責任。