Recover from Oracle 11g XE database file corruption on Windows 7

Recently, I experienced an eerie Hard Disk screeching sound from my laptop, a worst nightmare a professional can have - The sign that the hard disk was on its last legs.
Apart from the valuable documents, my machine contained a prototype Oracle XE 11g database. It also contained several Apex screens that I had recently developed as part of the prototype.

With the early warning of the screeching sound, I was able to recover most of the documents. However, for the Oracle database, there was one DBF file that was corrupted. The OS copy of this file continued to fail.

This blog post will discuss the methodology of recovering the Oracle DBF files if you run into corruption of the database files and do not have any backups.

I ran Windows chkdsk on the server and it ran fine with 0 indications of the bad sectors.

I tried using the "Backup Database" feature of the XE database but it would fail indicating the corruption in the DBF file.

After doing some search on the web, I was enlightened with the DBV utility that Oracle database comes with. Its an acronym for DB Verify. This tool validates the integrity of the DB files.

I ran this tool against a few files that were known to be good.

C:\oracle\product\oraclexe\app\oracle\oradata\XE>dbv file=SALES_REP.DBF

DBVERIFY: Release 11.2.0.2.0 - Production on Wed May 10 21:44:31 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = C:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES_REP.DBF

DBVERIFY - Verification complete

Total Pages Examined         : 2824

Total Pages Processed (Data) : 465

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 710

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 803

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 846

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 2086537 (0.2086537)

I ran this tool against the known bad file and voila, it validated that the file was indeed corrupt.

C:\oracle\product\oraclexe\app\oracle\oradata\XE>dbv file=ORDERS_REP.DBF

DBVERIFY: Release 11.2.0.2.0 - Production on Wed May 10 21:46:44 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = C:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\ORADATA\XE\ORDERS_REP.DBF

DBV-00600: Fatal Error - [28] [27070] [0] [0]

So, Oracle validated that the file was corrupt even though the OS didnt think so 😈 

As I mentioned earlier, the normal OS file copy did not work. I used xcopy instead. This forgotten command from the world of MSDOS 6.0 has an option /C that lets the copy continue even if the errors are encountered.

  xcopy ORDERS_REP.DBF ORDERS_REP_W.DBF /C

and the file got copied successfully.


Now, I ran the DB Verify again on this new file and the results were better.

C:\oracle\product\oraclexe\app\oracle\oradata\XE>dbv file=ORDER_REP_W.DBF
DBVERIFY: Release 11.2.0.2.0 - Production on Wed May 10 21:48:17 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = C:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\
ORADATA\XE\ORDERS_REP_W.DBF

DBVERIFY - Verification complete
Total Pages Examined         : 150168
Total Pages Processed (Data) : 41226
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 11763
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 85845
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 11334
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2117572 (0.2117572)

Now, the time to attach this file to the database.
Shutdown the database using the following commands

sqlplus / as sysdba

sqlplus> shutdown immediate

Database dismounted.

ORACLE instance shut down.

Rename the ORDERS_REP.DBF to ORDER_REP_original.DBF
Rename the file from ORDERS_REP_W.DBF to ORDERS_REP.DBF

Bring the database back 

SQL> startupORACLE instance started.
Total System Global Area 1068937216 bytesFixed Size                  2260048 bytesVariable Size             784335792 bytesDatabase Buffers          276824064 bytesRedo Buffers                5517312 bytesDatabase mounted.ORA-01113: file 6 needs media recoveryORA-01110: data file 6:'C:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\ORADATA\XE\ORDERS_REP.DBF'

The copied file will not work. So, the media recovery needs to be performed.

SQL> shutdown immediateORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mountORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size                  2260048 bytes
Variable Size             784335792 bytes
Database Buffers          276824064 bytes
Redo Buffers                5517312 bytes
Database mounted.
SQL> recover databaseMedia recovery complete.
SQL> alter database open;Database altered.

I was able to bring the database up.

Also, the Backup Database command worked now with the newly created DB file.