Saturday, March 24, 2007

Diagnosing archivelog corruption

When you encounter an archivelog corruption issue as seen from an ora-353 or ora-600 [3020] (stuck recovery) It is a common practice to dump the archivelog to determine the nature of the corruption.
Metalink note 1031381.6 explains various methods you can use to dump the same.

In addition you can also use the following command which only dumps the redo log file header although it reads the complete log file ( a truss on the associated server process can confirm the same) and hence you do not have the overhead of a large trace file (especially for large archivelogs)

SQL> alter system dump logfile 'D:\ARCHIVELOG\2007_03_24\O1_MF_1_48_3090F2FO_.ARC' layer 999 opcode 999;

System altered.

The associated trace file is shown below

Opcode 999.999 only
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
Compatibility Vsn = 169869824=0xa200200
Db ID=4004875527=0xeeb58d07, Db Name='TEN2'
Activation ID=4004873735=0xeeb58607
Control Seq=845=0x34d, File size=102400=0x19000
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000048, SCN 0x0000001614ba-0x0000001614df"
thread: 1 nab: 0xc seq: 0x00000030 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x2433e987 scn: 0x0000.00000001 (1)
resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
prev resetlogs count: 0x0 scn: 0x0000.00000000
prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.001614ba (1447098) 03/24/2007 12:39:20
Next scn: 0x0000.001614df (1447135) 03/24/2007 12:40:50
Enabled scn: 0x0000.00000001 (1) 11/24/2006 21:11:44
Thread closed scn: 0x0000.001614ba (1447098) 03/24/2007 12:39:20
Disk cksum: 0xefe4 Calc cksum: 0xefe4
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 3 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x11
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 5Kb in 0.05s => 0.10 Mb/sec
Total physical reads: 5Kb
Longest record: 1Kb, moves: 0/5 (0%)
Change moves: 6/33 (18%), moved: 0Mb
Longest LWN: 1Kb, moves: 0/4 (0%), moved: 0Mb
Last redo scn: 0x0000.001614db (1447131)