Oracle Database Restoration: Case 1 of archive log corruption

 

Link: http://www.eygle.com/archives/2010/11/recover_archivelog_corruption.html

 

Recently, in the case of emergency troubleshooting, a rare case of archive log corruption was encountered to help the user recover the database. Here we will share with you to see if someone has encountered similar problems.

When archiving recover, the database reports an error, prompting that the archived log is damaged:

***
Corrupt block seq: 37288 blocknum = 1.
Bad header found during deleting archived log
Data in Bad block-seq: 810559520. BNO: 170473264. Time: 707406346
Beg: 21280 CKS: 21061
Calculated check value: 9226
Reread of seq = 37288, blocknum = 1, file =/ARCH/arch_201737288_632509987.dbf, found same extends upt data
Reread of seq = 37288, blocknum = 1, file =/ARCH/arch_201737288_632509987.dbf, found same extends upt data
Reread of seq = 37288, blocknum = 1, file =/ARCH/arch_201737288_632509987.dbf, found same extends upt data
Reread of seq = 37288, blocknum = 1, file =/ARCH/arch_201737288_632509987.dbf, found same extends upt data
Reread of seq = 37288, blocknum = 1, file =/ARCH/arch_201737288_632509987.dbf, found same extends upt data
***

The detailed information indicates that the header of the archive log no. 37288 is damaged and data cannot be read.

Raise a small question:If you encounter such an error? What do you think?

If the archive log is damaged, we still have a way to skip and continue to try to restore other logs. However, the customer data is important and cannot tolerate inconsistency. At this time, we can only discard some data, the data is resubmitted at the front-end. This can be implemented in the business, which is not a big problem.

Okay. Why is the log corrupted? How is it damaged?

The first thing I need to do is to look at the content of the log file and output the content in the log file using the simplest command:
Strings arch_1_37288_632509987.dbf> log.txt

Then check the generated log file and we will find the problem.
In this archive log file, a large number of trace file contents are written. The first part is all the information of a trace file.

This is a phenomenon that I have never encountered before. That is to say, when the operating system writes a trace file, it overwrites the existing archive file by mistake, and finally causes archive log corruption, wonderful, never seen.

My final judgment is that this fault should be caused by a problem in the operating system writing, and the space of the file is still considered writable, which leads to a write conflict, in this case, check the hardware immediately to see if the hardware problem has caused such a serious exception.

Dump File/admin/bdump/erp_p007_19216.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, OLAP and Data Mining options
ORACLE_HOME =/DBMS/ERP/erpdb/10g
Linux
Eygle.com
2.6.9-34. elhugemem
#1 SMP Fri Feb 24 17:04:34 est 2006
I686
Instance name: ERP
Redo thread mounted by this instance: 1
Oracle process number: 22
UNIX process PID: 19216, image: oracle@eygle.com (p007)
* ** Service name :() 10:37:26. 247
* ** Session ID: (2184.1) 10:37:26. 247
* ** 10:37:26. 247
Kcrp: blocks claimed = 61, eliminated = 0
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 61/61 = 1.0
Max compares per lookup = 0
AVG compares per lookup = 0/61 = 0.0
----------------------------------------------
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 61/61 = 1.0
Max compares per lookup = 1
AVG compares per lookup = 1426/1426 = 1.0
----------------------------------------------
/Gpaymentdxn
Ap_checks
Q (XN
. 1 = N
/Gxn
. 1 = N
^ 0e
^ 0e!
^ 0e"
^ 0e #
^ 0e $
^ 0e %
^ 0e &
^ 0e'
Eygle.com! /
^ 0e (
^ 0e)
^ 0e *
^ 0e +
^ 0e +
^ 0e &
^ Ij1
R0: B
Q (XN
Paymentsn
A' VND
Userxn
Ap_invoice_payments
105273
5406105305-20101020-003
3001 cash clearing
Created
Dump File/admin/bdump/erp_p002_19206.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, OLAP and Data Mining options
ORACLE_HOME =/DBMS/ERP/erpdb/10g
Linux
Eygle.com
2.6.9-34. elhugemem
#1 SMP Fri Feb 24 17:04:34 est 2006
I686
Instance name: ERP
Redo thread mounted by this instance: 1
Oracle process number: 17
UNIX process PID: 19206, image: oracle@eygle.com (P002)
* ** Service name :() 10:37:26. 263
* ** Session ID: (2187.1) 10:37:26. 263
* ** 10:37:26. 263
Kcrp: blocks claimed = 84, eliminated = 0
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 84/84 = 1.0
Max compares per lookup = 0
AVG compares per lookup = 0/84 = 0.0
----------------------------------------------
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 84/84 = 1.0
Max compares per lookup = 1
AVG compares per lookup = 880/880 = 1.0
----------------------------------------------
^ A &
^ 1B #
^ 1B!
^ 1B"
^ 0e'
^ Mj8
^; & 3
2010ps_legal entity
^ 6 & L
Eoi_vnd
Quick payment: Id = 47708
CN/
Unsent
^ 9 & 1
/Hpayment
Creatednap_checks
^ 0e)
/Hxn
Dump File/admin/bdump/erp_p0011219204.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, OLAP and Data Mining options
ORACLE_HOME =/DBMS/ERP/erpdb/10g
Linux
Eygle.com
2.6.9-34. elhugemem
#1 SMP Fri Feb 24 17:04:34 est 2006
I686
Instance name: ERP
Redo thread mounted by this instance: 1
Oracle process number: 16
UNIX process PID: 19204, image: oracle@eygle.com (P001)
* ** Service name :() 10:37:26. 372
* ** Session ID: (2189.1) 10:37:26. 372
* ** 10:37:26. 372
Kcrp: blocks claimed = 132, eliminated = 0
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 132/132 = 1.0
Max compares per lookup = 0
AVG compares per lookup = 0/132 = 0.0
----------------------------------------------
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 132/132 = 1.0
Max compares per lookup = 1
AVG compares per lookup = 3219/3219 = 1.0
----------------------------------------------
^ IJ!
^ IJ $
^ IJ!
@ E> DF
> DF ^> DF
Userxn
Chen Restaurant
300190143
Cash clearing
Ap_checks
Created
Accounted
^ IJ!
Check
En/
Quick payment: Id = 47708
N/A ^ N/
Check
Chen Restaurant
210301
5 & 1'
54 ^'
^ 1B $
^ 1B &
^ 1b6
^ 1B,
^ 1b-
^ 1B4
^ 1b5
^ 1b0
^ 1B2
Dump File/admin/bdump/erp_p000_19202.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the partitioning, OLAP and Data Mining options
ORACLE_HOME =/DBMS/ERP/erpdb/10g
Linux
Eygle.com
2.6.9-34. elhugemem
#1 SMP Fri Feb 24 17:04:34 est 2006
I686
Instance name: ERP
Redo thread mounted by this instance: 1
Oracle process number: 15
UNIX process PID: 19202, image: oracle@eygle.com (p000)
* ** Service name :() 10:37:26. 386
* ** Session ID: (2190.1) 10:37:26. 386
* ** 10:37:26. 386
Kcrp: blocks claimed = 181, eliminated = 0
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 181/181 = 1.0
Max compares per lookup = 0
AVG compares per lookup = 0/181 = 0.0
----------------------------------------------
----- Recovery hash table statistics ---------
Hash Table buckets = 32768
Longest Hash Chain = 1
Average Hash Chain = 181/181 = 1.0
Max compares per lookup = 1
AVG compares per lookup = 8629/8629 = 1.0
----------------------------------------------
^ Ij0
Agent_status_marker
^ Agents_marked
R0: B
^ E!
^ 1b
^ 1b
^ 1B!
^ 1B!
^ 1B"
^ 1B"
^ 1B #

This is a rare case to share with you.

 

Website related articles | related articles

Helps you recover massive databases with damaged data blocks

Oracle Data Recovery: how much data will be damaged by formatting?

Oracle Data Recovery: Forced resetlogs for possible data loss

Oracle Data Recovery: formatting, ASM, and dictionary Damage Case 3

Case 1 of ORA-00600 kcratr_nab_less_than_odr