Recovery with RMAN is easy!

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:

  1. It’s the tablespace USERS – so probably not the entire database.
  2. The data file is part of a PDB – other PDBs in the same CDB might not be affected.
  3. 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top