1.note

可dump online redo log和archive log，甚至其它数据库的redo。数据库可处于nomount,mount及open状态

1. To dump records based in DBA (Data Block Address)

2. To dump records based on RBA (Redo Block Address) 

3. To dump records based on SCN

4. To dump records based on time

5. To dump records based on layer and opcode

6. Dump the file header information

7. Dump an entire log file: 

1. based on DBA (Data Block Address)

需指定数据文件号及数据块块号。

SQL> ALTER SYSTEM DUMP LOGFILE 'D:\oracle\product\10.2.0\oradata\cheny\REDO01.LOG'

  2  DBA MIN 4 . 459 DBA MAX 4 . 459;

DBA MIN 4 . 459 DBA MAX 4 . 459

          *

第 2 行出现错误:

ORA-01963: 必须指定块号

由于10g中需将.号去除。

指定的数据块没在指定redo中时，有如下dump信息

DUMP OF REDO FROM FILE 'D:\oracle\product\10.2.0\oradata\cheny\REDO01.LOG'

 Opcodes *.*

 DBAs: (file # 4, block # 459) thru (file # 4, block # 459)

 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff

 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff

 Times: creation thru eternity

 FILE HEADER:

         Compatibility Vsn = 169869568=0xa200100

         Db ID=2657209793=0x9e61cdc1, Db Name='CHENY'

         Activation ID=2664960019=0x9ed81013

         Control Seq=2634=0xa4a, File size=102400=0x19000

         File Number=1, Blksiz=512, File Type=2 LOG

 descrip:"Thread 0001, Seq# 0000000011, SCN 0x05814c3e353b-0xffffffffffff"

 thread: 1 nab: 0xffffffff seq: 0x0000000b hws: 0x1 eot: 1 dis: 0

 resetlogs count: 0x2842d467 scn: 0x0581.4c39e0ad (6052887781549)

 resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

 prev resetlogs count: 0x282f25e9 scn: 0x0581.4c395775 (6052887746421)

 prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

 Low  scn: 0x0581.4c3e353b (6052888065339) 01/20/2009 10:00:55

 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

 Enabled scn: 0x0581.4c39e0ad (6052887781549) 01/06/2009 22:06:31

 Thread closed scn: 0x0581.4c3e353b (6052888065339) 01/20/2009 10:00:55

 Disk cksum: 0x2eca Calc cksum: 0x2eca

 Terminal recovery stop scn: 0x0000.00000000

 Terminal recovery  01/01/1988 00:00:00

 Most recent redo scn: 0x0000.00000000

 Largest LWN: 0 blocks

 End-of-redo stream : No

 Unprotected mode

 Miscellaneous flags: 0x0

 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

REDO RECORD - Thread:1 RBA: 0x00000b.0000033c.0010 LEN: 0x0048 VLD: 0x01

SCN: 0x0581.4c3e38d1 SUBSCN:  3 01/20/2009 10:17:29

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:18.3

Reuse redo entry

Range reuse: tsn=1 base=8388753 nblks=8

END OF REDO DUMP

----- Redo read statistics for thread 1 -----

Read rate (ASYNC): 468Kb in 0.68s => 0.67 Mb/sec

Total physical reads: 4096Kb

Longest record: 8Kb, moves: 0/1158 (0%)

Change moves: 532/2581 (20%), moved: 0Mb

Longest LWN: 11Kb, moves: 0/341 (0%), moved: 0Mb

Last redo scn: 0x0581.4c3e3964 (6052888066404)

INSERT INTO HR.TEST VALUES('A');

SQL> select L.GROUP# from v$log L WHERE L.STATUS='CURRENT'

  2  /

    GROUP#

----------

         1

ALTER SYSTEM DUMP LOGFILE 'D:\oracle\product\10.2.0\oradata\cheny\REDO01.LOG'
DBA MIN 4 459 DBA MAX 4  467;

此时的dump信息为：

DUMP OF REDO FROM FILE 'D:\oracle\product\10.2.0\oradata\cheny\REDO01.LOG'

 Opcodes *.*

 DBAs: (file # 4, block # 459) thru (file # 4, block # 466)

 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff

 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff

 Times: creation thru eternity

 FILE HEADER:

         Compatibility Vsn = 169869568=0xa200100

         Db ID=2657209793=0x9e61cdc1, Db Name='CHENY'

         Activation ID=2664960019=0x9ed81013

         Control Seq=2634=0xa4a, File size=102400=0x19000

         File Number=1, Blksiz=512, File Type=2 LOG

 descrip:"Thread 0001, Seq# 0000000011, SCN 0x05814c3e353b-0xffffffffffff"

 thread: 1 nab: 0xffffffff seq: 0x0000000b hws: 0x1 eot: 1 dis: 0

 resetlogs count: 0x2842d467 scn: 0x0581.4c39e0ad (6052887781549)

 resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

 prev resetlogs count: 0x282f25e9 scn: 0x0581.4c395775 (6052887746421)

 prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

 Low  scn: 0x0581.4c3e353b (6052888065339) 01/20/2009 10:00:55

 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

 Enabled scn: 0x0581.4c39e0ad (6052887781549) 01/06/2009 22:06:31

 Thread closed scn: 0x0581.4c3e353b (6052888065339) 01/20/2009 10:00:55

 Disk cksum: 0x2eca Calc cksum: 0x2eca

 Terminal recovery stop scn: 0x0000.00000000

 Terminal recovery  01/01/1988 00:00:00

 Most recent redo scn: 0x0000.00000000

 Largest LWN: 0 blocks

 End-of-redo stream : No

 Unprotected mode

 Miscellaneous flags: 0x0

 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

REDO RECORD - Thread:1 RBA: 0x00000b.0000033c.0010 LEN: 0x0048 VLD: 0x01

SCN: 0x0581.4c3e38d1 SUBSCN:  3 01/20/2009 10:17:29

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:18.3

Reuse redo entry

Range reuse: tsn=1 base=8388753 nblks=8

REDO RECORD - Thread:1 RBA: 0x00000b.000003c7.0010 LEN: 0x0208 VLD: 0x0d

SCN: 0x0581.4c3e39aa SUBSCN:  1 01/20/2009 10:24:23

CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x010001d0 OBJ:52556 SCN:0x0581.4c38b3e9 SEQ:  1 OP:11.2

KTB Redo

op: 0x01  ver: 0x01 

op: F  xid:  0x0002.006.0000020c    uba: 0x0080044a.0141.32

KDO Op code: IRP row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x010001d0  hdba: 0x010001cb

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 1(0x1) size/delt: 42

fb: --H-FL-- lb: 0x1  cc: 1

null: -

col  0: [38]

 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 02 40 2d 00 12 09 00 00

 00 00 00 00 02 00 00 00 00 00 01 00 41

CHANGE #2 TYP:0 CLS:19 AFN:2 DBA:0x00800019 OBJ:4294967295 SCN:0x0581.4c3e3996 SEQ:  2 OP:5.2

ktudh redo: slt: 0x0006 sqn: 0x0000020c flg: 0x0012 siz: 128 fbi: 0

            uba: 0x0080044a.0141.32    pxid:  0x0000.000.00000000

CHANGE #3 TYP:0 CLS:19 AFN:2 DBA:0x00800019 OBJ:4294967295 SCN:0x0581.4c3e39aa SEQ:  1 OP:5.4

ktucm redo: slt: 0x0006 sqn: 0x0000020c srt: 0 sta: 9 flg: 0x2

ktucf redo: uba: 0x0080044a.0141.32 ext: 2 spc: 986 fbi: 0

CHANGE #4 TYP:0 CLS:20 AFN:2 DBA:0x0080044a OBJ:4294967295 SCN:0x0581.4c3e3996 SEQ:  1 OP:5.1

ktudb redo: siz: 128 spc: 1116 flg: 0x0012 seq: 0x0141 rec: 0x32

            xid:  0x0002.006.0000020c 

ktubl redo: slt: 6 rci: 0 opc: 11.1 objn: 52556 objd: 52556 tsn: 4

Undo type:  Regular undo        Begin trans    Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

             0x00000000  prev ctl uba: 0x0080044a.0141.31

prev ctl max cmt scn:  0x0581.4c3e340c  prev tx cmt scn:  0x0581.4c3e3422

txn start scn:  0x0581.4c3e39a8  logon user: 5  prev brb: 8389704  prev bcl: 0 KDO undo record:

KTB Redo

op: 0x04  ver: 0x01 

op: L  itl: xid:  0x0002.005.00000145 uba: 0x0080012d.00bf.27

                      flg: C---    lkc:  0     scn: 0x0581.4c3717d0

KDO Op code: DRP row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x010001d0  hdba: 0x010001cb

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 1(0x1)

END OF REDO DUMP

----- Redo read statistics for thread 1 -----

Read rate (ASYNC): 498Kb in 0.47s => 1.04 Mb/sec

Total physical reads: 4096Kb

Longest record: 8Kb, moves: 0/1256 (0%)

Change moves: 566/2772 (20%), moved: 0Mb

Longest LWN: 11Kb, moves: 0/364 (0%), moved: 0Mb

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30821/viewspace-541525/ ，如需转载，请注明出处，否则将追究法律责任。