11G flashback data archive 導致產生大量歸檔日誌
透過以下命令把當前線上日誌檔案dump 出來：
SQL> alter system dump logfile '/oradata/SFRAC/onlinelog/o1_mf_1_1PP7b3bt4_.log';
System altered.
SQL>
以下是 dump 出檔案的部份內容：
Trace file /u01/app/oracle/diag/rdbms/sfrac/sfrac1/trace/sfrac1_ora_27254.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: sfrac1
Release: 2.6.18-194.el5
Version: #1 SMP Mon Mar 29 22:10:29 EDT 2010
Machine: x86_64
VM name: VMWare Version: 6
Instance name: sfrac1
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 27254, image: oracle@sfrac1 (TNS V1-V3)
*** 2012-08-14 16:57:45.067
*** SESSION ID:(33.785) 2012-08-14 16:57:45.067
*** CLIENT ID:() 2012-08-14 16:57:45.067
*** SERVICE NAME:(SYS$USERS) 2012-08-14 16:57:45.067
*** MODULE NAME:(sqlplus@sfrac1 (TNS V1-V3)) 2012-08-14 16:57:45.067
*** ACTION NAME:() 2012-08-14 16:57:45.067
Initial buffer sizes: read 1024K, overflow 832K, change 805K
*** 2012-08-14 16:57:45.344
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
DUMP OF REDO FROM FILE '/oradata/SFRAC/onlinelog/o1_mf_1_1PP7b3bt4_.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=437779523=0x1a17fc43, Db Name='SFRAC'
Activation ID=437749059=0x1a178543
Control Seq=794829=0xc20cd, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000447, SCN 0x000000b51f2d-0x000000b53368"
thread: 1 nab: 0x1780d seq: 0x000001bf hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x2f058d87 scn: 0x0000.000f30dc (995548)
prev resetlogs count: 0x2d6c775c scn: 0x0000.00000001 (1)
Low scn: 0x0000.00b51f2d (11869997) 08/14/2012 16:51:18
Next scn: 0x0000.00b53368 (11875176) 08/14/2012 16:52:18
Enabled scn: 0x0000.000f30dc (995548) 07/17/2012 16:57:43
Thread closed scn: 0x0000.00b51f2d (11869997) 08/14/2012 16:51:18
Disk cksum: 0xd61a Calc cksum: 0xd61a
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 6126 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 8
Format ID is 2
redo log key is 3c6712e8276676a9c02ae7a451170c6
redo log key flag is 5
Enabled redo threads: 1
*** 2012-08-14 16:57:46.284
REDO RECORD - Thread:1 RBA: 0x0001bf.00000002.0010 LEN: 0x00fc VLD: 0x05
SCN: 0x0000.00b51f2d SUBSCN: 1 08/14/2012 16:51:19
(LWN RBA: 0x0001bf.00000002.0010 LEN: 3065 NST: 0001 SCN: 0x0000.00b51f2d)
CHANGE #1 TYP:0 CLS:24 AFN:3 DBA:0x00c6717f OBJ:4294967295 SCN:0x0000.00b51f27 SEQ:18 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 140 spc: 5594 flg: 0x0022 seq: 0x0419 rec: 0x13
xid: 0x0004.000.000007bf
ktubu redo: slt: 0 rci: 18 opc: 11.1 objn: 77058 objd: 4251392 tsn: 3
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c6717f.0419.12
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040bbc4 hdba: 0x0040df00
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 62(0x3e) size/delt: 33
fb: --H-FL-- lb: 0x1 cc: 3
null: ---
col 0: [ 4] c3 08 44 4e
col 1: [18] 41 41 41 53 76 70 41 41 45 41 41 41 45 6f 70 41 46 31
col 2: [ 5] c4 0b 3a 21 27
REDO RECORD - Thread:1 RBA: 0x0001bf.00000002.010c LEN: 0x00d0 VLD: 0x01
SCN: 0x0000.00b51f2d SUBSCN: 2 08/14/2012 16:51:19
CHANGE #1 TYP:0 CLS:24 AFN:3 DBA:0x00c6717f OBJ:4294967295 SCN:0x0000.00b51f2d SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 140 spc: 5452 flg: 0x0022 seq: 0x0419 rec: 0x14
xid: 0x0004.000.000007bf
ktubu redo: slt: 0 rci: 19 opc: 11.1 objn: 77058 objd: 4251392 tsn: 3
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c6717f.0419.13
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040bbc4 hdba: 0x0040df00
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 63(0x3f) size/delt: 33
fb: --H-FL-- lb: 0x1 cc: 3
null: ---
col 0: [ 4] c3 08 44 4e
col 1: [18] 41 41 41 53 76 70 41 41 45 41 41 41 45 6f 70 41 46 32
col 2: [ 5] c4 0b 3a 21 27
REDO RECORD - Thread:1 RBA: 0x0001bf.00000002.01dc LEN: 0x00d0 VLD: 0x01
SCN: 0x0000.00b51f2d SUBSCN: 3 08/14/2012 16:51:19
CHANGE #1 TYP:0 CLS:24 AFN:3 DBA:0x00c6717f OBJ:4294967295 SCN:0x0000.00b51f2d SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 140 spc: 5310 flg: 0x0022 seq: 0x0419 rec: 0x15
xid: 0x0004.000.000007bf
ktubu redo: slt: 0 rci: 20 opc: 11.1 objn: 77058 objd: 4251392 tsn: 3
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c6717f.0419.14
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040bbc4 hdba: 0x0040df00
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 64(0x40) size/delt: 33
fb: --H-FL-- lb: 0x1 cc: 3
null: ---
col 0: [ 4] c3 08 44 4e
col 1: [18] 41 41 41 53 76 70 41 41 45 41 41 41 45 6f 70 41 46 33
col 2: [ 5] c4 0b 3a 21 27
REDO RECORD - Thread:1 RBA: 0x0001bf.00000003.00bc LEN: 0x00d0 VLD: 0x01
SCN: 0x0000.00b51f2d SUBSCN: 4 08/14/2012 16:51:19
CHANGE #1 TYP:0 CLS:24 AFN:3 DBA:0x00c6717f OBJ:4294967295 SCN:0x0000.00b51f2d SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 140 spc: 5168 flg: 0x0022 seq: 0x0419 rec: 0x16
xid: 0x0004.000.000007bf
ktubu redo: slt: 0 rci: 21 opc: 11.1 objn: 77058 objd: 4251392 tsn: 3
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c6717f.0419.15
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040bbc4 hdba: 0x0040df00
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 65(0x41) size/delt: 33
fb: --H-FL-- lb: 0x1 cc: 3
null: ---
col 0: [ 4] c3 08 44 4e
col 1: [18] 41 41 41 53 76 70 41 41 45 41 41 41 45 6f 70 41 46 34
col 2: [ 5] c4 0b 3a 21 27
依據以上日誌中的 object number 找出以下物件，奇怪的是該系統表中沒有內容：
SQL> select owner,object_name,created,object_type from dba_objects where object_id = 77058;
OWNER OBJECT_NAME CREATED OBJECT_TYPE
------------------------------ ---------------------------------------- --------- -------------------
SYS SYS_MFBA_STAGE_RID 10-AUG-12 TABLE
SQL> select count(*) from sys.SYS_MFBA_STAGE_RID;
COUNT(*)
----------
0
SQL> desc sys.SYS_MFBA_STAGE_RID;
Name Null? Type
----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
OBJN NUMBER
RID VARCHAR2(4000)
ESCN NUMBER
SQL>
找到 flashback archive ，並刪除，archivelog 還是不斷產生：
SQL> select count(*) from dba_flashback_archive;
COUNT(*)
----------
1
SQL> drop flashback archive FLASH1;
Flashback archive dropped.
SQL>
找到flashback archive 的系統程式，嘗試 kill ，還是不斷產生：
[root@sfrac1 ~]# ps -ef|grep ora_fbda
oracle 6323 1 4 16:18 ? 00:02:50 ora_fbda_sfrac1
root 11127 8346 0 17:29 pts/5 00:00:00 grep ora_fbda
[root@sfrac1 ~]# kill -9 6323
[root@sfrac1 ~]#
從後臺日誌中能看到 FBDA 程式自動啟動：
Tue Aug 14 17:30:29 2012
Restarting dead background process FBDA
Tue Aug 14 17:30:29 2012
FBDA started with pid=38, OS id=11620
Tue Aug 14 17:30:38 2012
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/66634/viewspace-1059181/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 閃回資料歸檔-- Flashback Data ArchiveHive
- archive log 歸檔日誌Hive
- Oracle 11g新特性--閃回資料歸檔(flashback data archive)[zt]OracleHive
- oracle archive log 歸檔日誌OracleHive
- 誤刪歸檔日誌除導致備份歸檔日誌失敗
- 每天產生REDO歸檔日誌量
- Oracle archive log 歸檔日誌管理OracleHive
- 歸檔日誌無法歸檔導致資料庫hang住資料庫
- Oracle Flashback Data ArchiveOracleHive
- Flashback Data Archive RequirementsHiveUIREM
- 11G Flashback Data Archive新特性的研究Hive
- 刪除data guard歸檔日誌
- data guard 歸檔日誌管理 （standby）
- data guard 歸檔日誌管理 （primary）
- Oracle10g中的flashback啟用日誌歸檔,寫滿空間導致錯誤Oracle
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- oracle 11g 新特性 Flashback Data Archive 說明OracleHive
- oracle檢視昨天產生歸檔日誌檔案總量Oracle
- 測試DML 時產生歸檔日誌和閃回日誌的比
- Oracle Flashback Archive——Oracle閃迴歸檔（上）OracleHive
- Oracle Flashback Archive——Oracle閃迴歸檔（中）OracleHive
- Oracle Flashback Archive——Oracle閃迴歸檔（下）OracleHive
- Oracle 11g Data Guard 備庫歸檔日誌清理指令碼(保留一週歸檔)Oracle指令碼
- ARCHIVE歸檔日誌爆滿故障ORA-00257Hive
- Flashback Data Archive原理詳解Hive
- Flashback Data Archive (Oracle Total Recall)HiveOracle
- 歸檔日誌大小 與使用rman 備份後的歸檔日誌產生的備份集大小的關係
- oracle歸檔模式下imp匯入資料,產生的歸檔日誌大小記錄Oracle模式
- 記：”$ORACLE_HOME/.../sysman/recv/errors“目錄下產生大量日誌檔案OracleError
- 歸檔日誌滿導致的資料庫掛起故障處理資料庫
- 【ARCHIVE】模擬因無法歸檔導致日誌無法切換故障及一般處理方法兩則Hive
- 11G新特性：FLASHBACK ARCHIVEHive
- 歸檔日誌
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- 缺少log_archive_config導致歸檔路徑被禁用Hive
- 群友問題--近期每秒頻繁產生40m歸檔日誌檔案
- 11G新特性：FLASHBACK ARCHIVE - 01Hive
- 11G新特性：FLASHBACK ARCHIVE – 02Hive