In this blog I will show you how easy it is to restore a corrupted tablespace or datafile using RMAN.
In my last blog “Backup and Recovery with RMAN is easy!” I wrote how easy it is to backup a database using RMAN if “Oracle” can take care of the file structure.
The third blog will than give some details about database cloning / database restore.
Let’s start with a curruped database first. Linux is very handy so to destroy or corrupt a data file you can simply use the following command:
dd if=/dev/zero of=/u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_users_gfqznp2m_.dbf' bs=8192 count=1000
Please don’t test on your production environment!
This command leads into an error message if you want to query some data stored in that file:
SQL> select count(*) from auftraege; select count(*) from auftraege * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 16, block # 482) ORA-01110: data file 16: '/u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_users_gfqznp2m_.dbf'
And now?
Before we start we should know what happened:
- It’s the tablespace USERS – so probably not the entire database.
- The data file is part of a PDB – other PDBs in the same CDB might not be affected.
- The database instance is open. Probably only a small part of the application is affected – may be the application is still doing fine.
So please don’t recover the entire database as that will have an impact on all applications. You only need to restore one file!
Let’s ask RMAN if he has an idea:
RMAN> LIST FAILURE; using target database control file instead of recovery catalog Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 202 HIGH OPEN 15-MAY-19 One or more non-system datafiles are corrupt
Okay we already new that. But the command is important for the further analysis. RMAN is now aware that a data file is corrupt. So RMAN might give us an advise:
RMAN> ADVISE FAILURE; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 202 HIGH OPEN 15-MAY-19 One or more non-system datafiles are corrupt analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=79 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== no manual actions available Optional Manual Actions ======================= 1. Automatic repairs may be available if you shutdown the database and restart it in mount mode Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Restore and recover datafile 16 Strategy: The repair includes complete media recovery with no data loss Repair script: /opt/oracle/diag/rdbms/paul/PAUL/hm/reco_1304580712.hm
Wow: RMAN created a script namedreco_1304580712.hm with all the advises to repair the failure. This is how the file looks:
cat /opt/oracle/diag/rdbms/paul/PAUL/hm/reco_1304580712.hm # restore and recover datafile sql 'BOXER' 'alter database datafile 16 offline'; restore ( datafile 16 ); recover datafile 16; sql 'BOXER' 'alter database datafile 16 online'
We could execute that file to repair the failure. But even that isn’t necessary as RMAN can do this for us:
RMAN> REPAIR FAILURE; Strategy: The repair includes complete media recovery with no data loss Repair script: /opt/oracle/diag/rdbms/paul/PAUL/hm/reco_3216729380.hm contents of repair script: # restore and recover datafile sql 'BOXER' 'alter database datafile 16 offline'; restore ( datafile 16 ); recover datafile 16; sql 'BOXER' 'alter database datafile 16 online'; Do you really want to execute the above repair (enter YES or NO)? executing repair script sql statement: alter database datafile 16 offline Starting restore at 15-MAY-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00016 to /u02/oradata/PAUL/88ECC48AE4632772E0530D63A8C04AEF/datafile/o1_mf_users_gfqznp2m_.dbf channel ORA_DISK_1: reading from backup piece /u03/orabackup/PAUL/88ECC48AE4632772E0530D63A8C04AEF/backupset/2019_05_15/o1_mf_nnndf_TAG20190515T141943_gfr103o6_.bkp channel ORA_DISK_1: piece handle=/u03/orabackup/PAUL/88ECC48AE4632772E0530D63A8C04AEF/backupset/2019_05_15/o1_mf_nnndf_TAG20190515T141943_gfr103o6_.bkp tag=TAG20190515T141943 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 15-MAY-19 Starting recover at 15-MAY-19 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 15-MAY-19 sql statement: alter database datafile 16 online repair failure complete
Done! The database is restored and the application or query will run without any errors.