Being Oracle Administrator, we must exercise on the worst-case occurrences and be ready to handle situations proactively. The below post is one of the worst-case scenarios, deals with the necessary steps to be followed when the datafile gets corrupted. This has divided into two parts,
1. Simulate corruption(just for this scenario :-) )
2. Recover the corrupted file.
PART1: corrupting datafile
- In order to simulate this scenario, we just create a tablespace called test_ts , user called test and appropriate permission specified as below,
Create tablespace,
- Create table and insert 1000 records as follows,
NOTE: Take the full backup of your database before proceeds next.
- Purposely corrupt the data file,
- Using dbv utility, check the corrupted file and block,
Dbv file=/u01/……test_ts.dbf,
- Run the following command in RMAN and check the corrupted block consecutively,
RMAN>Backup validate database;
- Now, select the table. Data will come so we need to flush the buffer cache which holds it temporarily,
Part2: Recovering the corrupted file using RMAN
There are three consecutive chained steps needs to performed to recover the datafile block namly,
- List failure – list the number of failures.
- Advise failure – script out the failures specified in list failure.
- Repair failure – run the scripts generated in advise failure.
List failure
Advise failure,
Repair failure,
That is it. We have successfully recovered the corrupted datafile using the RMAN mechanism.!