11G flashback data archive 導致產生大量歸檔日誌

ysjxjf發表於2012-08-14
Hive
在 disable flashback data archive 後，資料庫產生大量歸檔日誌，最後透過關閉重起資料庫來解決。[@more@]

透過以下命令把當前線上日誌檔案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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章