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)


palmercabel said...

The Metalink Doc number comes up invalid. Can you check to see if that is the correct number?

palmercabel said...

Ignore the previous comment - I think my browser is allergic to Mondays....

Fairlie Rego said...


Thanks for your comment.To be able to use the link you need to have another browser window opened and already logged in.
If not, login to Metalink use the quick find tool and enter the note number 1031381.6. From the left drop down box choose Knowledge Browsee and press Go. You should be able to find the note number.


Fairlie Rego said...

Thanks for your feedback. This post was more to highlight the Metalink Note
and mention that the addition option to use opcode 999. I could use ultraedit
and corrupt a logfile and then analyze the dumped trace file but I am snowed
under with work

Anonymous said...

I remember using this many years back but could remember the syntax. Of couse it is now enhanced which is even better.

Thanks for sharing.