To identify the current redo log use
SELECT member FROM v$logfile
WHERE group# =
(
SELECT group# FROM v$log
WHERE status = 'CURRENT'
);
To dump a redo log file use
ALTER SYSTEM DUMP LOGFILE '<FileName>';
For example:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log';
The syntax of this statement is as follows
ALTER SYSTEM DUMP LOGFILE 'FileName'
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber BlockNumber
DBA MAX FileNumber BlockNumber
RBA MIN LogFileSequenceNumber BlockNumber
RBA MAX LogFileSequenceNumber BlockNumber
OBJNO ObjectNumber
XID UndoSegmentNumber UndoSlotNumber UndoSequenceNumber;
The following examples were tested in 11.2.0.4:
The minimum and maximum SCN is a decimal number
For example:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' SCN MIN 3408715 SCN MAX 3410674;
The redo header includes the minimum and maximum SCN numbers:
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcodes *.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.0034034b (3408715) thru scn: 0x0000.00340af2 (3410674) Times: creation thru eternity
The minimum and maximum time is a decimal number representing the number of seconds since midnight 01Jan1988. These are calculated using the following formula:
time = (((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) *
60 + mi) * 60 + ss;
where values are in the following ranges:
|
This is the same formula that is used to represent time within the redo log
For example:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' TIME MIN 887902940 TIME MAX 887906853;
The redo header will include the minimum and maximum times. For example:
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcodes *.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: 08/16/2015 15:42:20 thru 08/16/2015 16:47:33
To dump all redo for data file 4 blocks 1568 (0x620) to 1571 (0x623) inclusive use:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' DBA MIN 4 1568 DBA MAX 4 1571;
Not all redo operations are excluded by the above command. For example 11.19 changes for blocks outside the requested range are included in the 11.2.0.4 dump
The redo header will include the minimum and maximum DBAs
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcodes *.* DBAs: (file # 4, block # 1568) thru (file # 4, block # 1571) RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity
To dump redo for log sequence number 109 redo log blocks 23 (0x17) and 24 (0x18) use:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' RBA MIN 109 23 RBA MAX 109 25;
Note that the block number must be specified in decimal (not hexadecimal)
The redo header will include the mininum and maximum RBA:
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcodes *.* RBAs: 0x00006d.00000017.0000 thru 0x00006d.00000019.0000 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity
The layer and opcode are those used to indicate specific operations within the redo log e.g. LAYER 5 OPCODE 4 is an undo segment header commit operation
To dump all layer 5 changes:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' LAYER 5;
The redo header will include the layer specified in the opcode:
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcodes 5.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity
To dump all layer 5 opcode 1 changes:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' LAYER 5 OPCODE 1;
The above command will dump all undo changes (table and index)
The redo header will include the layer and opcode:
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcode 5.1 only RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity
To dump all redo for a object 78931 use:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' OBJNO 78931;
Note that the above command uses the OBJECT_ID, not the DATA_OBJECT_ID.
The dump header will include the object number:
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcodes *.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity Objno: 78921
Some redo may be omitted and replaced by the following message:
---------------------------------------------- Skipping IMU Redo Record: cannot be filtered by XID/OBJNO ----------------------------------------------
To dump all redo for transaction ID 0x0001.01f.0000061a use:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log' XID 1 31 1562;
Note that in the above the undo segment/slot/sequence number in the original transaction XID have been converted from hexadecimal to decimal for the DUMP command.
The dump header will include the XID:
DUMP OF REDO FROM FILE '/u01/app/oradata/TEST/redo01.log' Opcodes *.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity xid: 0x0001.01f.0000061a
Some redo may be omitted and replaced by the following message:
---------------------------------------------- Skipping IMU Redo Record: cannot be filtered by XID/OBJNO ----------------------------------------------
See Metalink Note 1031381.6 for further examples