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.

2. (unless you plan to use the online catalog)

3.

4.

5.

6.

二：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 ，研發提供補丁

歸檔異常增長 案例三：

問題原因：資料庫JOB 執行太頻繁；

問題現象：每分鐘都有歸檔產生

平均每天產生 60G 歸檔檔案

檢視當前正在執行的SQL

Select * from v$sql where address in (select sql_address from v$session);

日誌挖掘

檢視資料庫job

問題原因：

JOB 每分鐘執行一次，呼叫儲存過程，頻繁 delete,insert 大量資料，當儲存過程在一分鐘之內執行不完時，下一個JOB 又開始呼叫儲存過程，導致 delete,insert 每秒都在執行，造成歸檔瘋漲；

解決方案：

客戶反饋這個JOB 對應的應用已經不用了，但是資料庫 JOB 沒有及時停掉，手動停掉這個 JOB 即可。

歸檔異常增長 案例四：

問題原因：預算業務產生大量歸檔檔案

查詢原因：

一檢視歸檔頻率及歸檔分佈，檢視JOB 以及後臺計劃任務

二日挖掘多個歸檔日誌

三找出執行次數多的SQL

四分析SQL 由來

五跟蹤表的變化

一檢視歸檔頻率及歸檔分佈，檢視JOB 以及後臺計劃任務

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

發現歸檔產生並沒有集中在某一小時或某一小段時間內，可能和備份計劃任務(0點)和JOB關係不大。

--- 檢視後臺 job

select job , schema_user , last_date , next_date , broken , interval from dba_jobs ;

二日誌挖掘多個歸檔日誌

--- 檢視每天歸檔大小

select sum ( block_size * blocks ) / 1024 / 1024 / 1024 "size(GB)" ,

to_char ( first_time , 'yyyymmdd' )

from v$archived_log

group by to_char ( first_time , 'yyyymmdd' )

order by 2 desc ;

--- 檢視歸檔大小及目錄名

select block_size * blocks / 1024 / 1024 "size(M)" ,

name ,

first_time ,

next_time ,

creator

from v$archived_log a

order by first_time desc ;

--- 日誌挖掘

(分析連續的幾個歸檔)

SQL > EXECUTE DBMS_LOGMNR.ADD_LOGFILE ( LogFileName => '/u01/app/oracle/archive/1_5673_863636484.dbf' , Options => dbms_logmnr.new );

SQL > exec dbms_logmnr.start_logmnr ( options => dbms_logmnr.dict_from_online_catalog );

SQL > create table test1 as select * from v$logmnr_contents ;

SQL > exec dbms_logmnr.end_logmnr ;

--- 檢視歸檔中執行次數多的 SQL

select count (*), substr ( sql_redo , 1 , 100 ) from test.test1 group by substr ( sql_redo , 1 , 100 ) order by 1 desc ;

--- 檢視 sql_redo 為空歸檔的分佈

select count (*), data_obj# from test.test1 where sql_redo is null group by data_obj# order by 1 desc ;

/* 發現資訊主要分佈在兩個 data_obj# 上 */

--- 透過 data_obj# 查詢具體的物件

select * from dba_objects where data_object_id in ( '92468' , '92467' );

--- 檢視所屬段

select * from dba_segments where segment_name in ( 'SYS_LOB0000092466C00006$$' , 'SYS_IL0000092466C00006$$' );

--- 查詢具體物件型別（ OBJECT_TYPE 為 LOB ）

select * from dba_lobs where SEGMENT_NAME = 'SYS_LOB0000092466C00006$$' ;

--- 查詢具體物件型別（ OBJECT_TYPE 為 index ）

select * from dba_indexes where index_name = 'SYS_IL0000092466C00006$$' ;

--- 檢視 tb_taskshtmodel 段大小(6.25M)

select bytes / 1024 / 1024 , a. * from dba_segments a where owner = 'NC63PROD' and segment_name = 'TB_TASKSHTMODEL' ;

--- 檢視 tb_taskshtmodel 表 SHEETMODEL 列的 blob 段 SYS_LOB0000092466C00006$$ 大小(47G)

select bytes / 1024 / 1024 / 1024 , a. * from dba_segments a where owner = 'NC63PROD' and segment_name = 'SYS_LOB0000092466C00006$$' ;

-- 檢視 tb_taskshtmodel 表 SHEETMODEL 列的 blob 段 SYS_LOB0000092466C00006$$ 對應段索引 SYS_IL0000092466C00006$$ 的大小(70.5M)

select bytes / 1024 / 1024 , a. * from dba_segments a where owner = 'NC63PROD' and segment_name = 'SYS_IL0000092466C00006$$' ;

---- 檢視 LOB 欄位大小分佈

select max ( dbms_lob.getLength ( sheetmodel ) / 1024 / 1024 ),

min ( dbms_lob.getLength ( sheetmodel ) / 1024 / 1024 ),

avg ( dbms_lob.getLength ( sheetmodel ) / 1024 / 1024 )

from nc63prod.tb_taskshtmodel ;

結論：tb_taskshtmodel 表段 6.25M ， tb_taskshtmodel 表 SHEETMODEL 列的 blob 段 SYS_LOB0000092466C00006$$ 有47G， tb_taskshtmodel 表 SHEETMODEL 列的 blob 段 SYS_LOB0000092466C00006$$ 對應段索引 SYS_IL0000092466C00006$$ 有70.5M。

--- 檢視 TB_TASKSHTMODEL 資料量

select count (*) from nc63prod.TB_TASKSHTMODEL ;

---tb_taskshtmodel 表 sheetmodel 欄位總大小(44.3G)

select sum ( a ) / 1024 / 1024 / 1024

from ( select dbms_lob.getLength ( sheetmodel ) a

from nc63prod.tb_taskshtmodel );

--- 檢視 tb_taskshtmodel 欄位11月7日大小

select sum ( a ) / 1024 / 1024 / 1024

from ( select dbms_lob.getLength ( sheetmodel ) a

from nc63prod.tb_taskshtmodel

where ts like '2015-11-07%' );

如果有delete 操作，會產生更多歸檔，

透過JOB 來跟蹤 tb_taskshtmodel 表每分鐘資料量，從而推測出 表插入和刪除的操作 次數。

=================

2015-11-05 08:54

=================

---1

create table c_test ( t_date date default sysdate , t_count number );

---2

create or replace procedure p_test as

begin

insert into c_test

select sysdate , count ( 1 ) from TB_TASKSHTMODEL ;

commit ;

end ;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as nc63prod

SQL> show parameter aud

NAME TYPE VALUE

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

audit_file_dest string /u01/app/oracle/admin/NCPROD/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string NONE

SQL> variable job1 number;

SQL> begin

2 dbms_job.submit(:job1,'p_test;',sysdate,'sysdate+1/1440');

3 end;

4 /

PL/SQL procedure successfully completed

job1

---------

23

select job , what from dba_jobs ;

SQL> begin

2 dbms_job.run(23);

3 end;

4 /

PL/SQL procedure successfully completed

---

select * from nc63prod.c_test order by 1 desc ;

--- 檢視 nc63prod.TB_TASKSHTMODEL 刪除情況

select * from nc63prod.c_test a , nc63prod.c_test b where a.t_date > b.t_date and a.t_count < b.t_count order by 3 ;

結論： nc63prod.TB_TASKSHTMODEL 表24小時內插入18000行資料，刪除583條資料

---刪除JOB

=================

2015-11-06 08:56

=================

SQL> begin

2 dbms_job.remove(23);

3 end;

4 /

PL/SQL procedure successfully completed

解決方案：

審計也會產生一部分歸檔檔案，如果不使用審計，建議關閉。

1 、檢視審計功能是否開啟

SQL> show parameter audit

NAME TYPE VALUE

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

audit_file_dest string /u01/app/oracle/admin/NCPROD/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

說明：VALUE 值為 DB ，表面審計功能為開啟的狀態

2 、關閉 oracle 的審計功能

SQL> alter system set audit_trail= none scope=spfile;

System altered.

3 、重啟資料庫生效

SQL> shutdown immediate;

SQL> startup;

減少 "NC63PROD"."TB_TASKSHTMODEL" 表產生日誌

具體操作：

(1) 資料庫裡執行

alter table NC63PROD.TB_TASKSHTMODEL nologging ;

(2) 開發人員將

insert into "NC63PROD"."TB_TASKSHTMODEL"

更改為

insert /*+ append */ into "NC63PROD"."TB_TASKSHTMODEL"

注意： append+nologing 存在的風險，會導致rman 恢復這個表時出現問題，需要充分評估是否有必要使用此方案。

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

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

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

相關文章