Restore Tablespace: Disaster Recovery Scenarios (part 3)

Posted: December 19, 2014 in Backup and Recovery
Tags: ,

Good morning, RMAN fanatics!

I am extremely excited about RMAN recovery. Preparing for disaster recovery from a database perspective has calmed much of my fears pertaining to database corruption. If you ever sit back and wonder, “Am I really worth that exorbitant salary they pay me?” When a disaster occurs and you can calmly refer to your documentation on disaster recovery and restore the database while sipping your morning coffee, you will never wonder why they pay you what they pay you again. Let them wonder, “Am I paying him/her enough?”

Restoring a tablespace is much the same as restoring a datafile. If you are considering restoring a tablespace, it is quite likely that multiple datafiles have been corrupted or gone missing. If datafiles from multiple tablespaces have gone missing or corrupted, it may be time to refer to my next article on Restore Database. I believe it to be the most simple restore operation.

Perform Tablespace Recovery

1) Backup tablespace with datapump. **Note** I performed this as a disaster recovery practice on a database I just inherited the week prior. RMAN was implemented in OEM 1oG by one of the “System Maintainers” I mentioned in an earlier post. Every time the OS password was changed, the jobs would begin to fail. The SM deleted the existing RMAN backups and recreated them with the new OS credentials. Needless to say, I did not fully trust these backups. You may choose to bypass the datapump export but I ran it as a parameter file for future use.


**Example Par file**

[oracle@blablasrvr01]$ vi <tbsp_name>_tbsp10174.par






[oracle@blablasrvr01]$ expdp parfile=<tbsp_name>_tbsp10174.par

2) Restore the tablespace


connnect target /

RMAN> restore tablespace ‘<tbsp_name>’ validate;

–note the location of the datafile

–If restore tablespace validate fails, do not attempt restore tablespace. Consult your alert logs. You may need to restore the database.

RMAN> restore tablespace ‘<tbsp_name>’ preview;

–record backup piece/s required and SCN# needed to “clear datafile fuzziness”.

RMAN> list backup of archivelog all; –recall that SCN# must be greater than the SCN from the preview step. If this is not the case, open a SQL+ session and query v$log.

SQL> select * from v$log;

–Find at least one log with an SCN beyond the SCN recorded.

3) Create an RMAN script to run from the RMAN console.

RMAN> run


sql ‘alter tablespace “<tbsp_name>” offline immediate’;

restore tablespace ‘<tbsp_name>’;

recover tablespace ‘<tbsp_name>’ DELETE ARCHIVELOG MAXSIZE 2M;

sql ‘alter tablespace “<tbsp_name>” ONLINE’;


finished restore at $TIMESTAMP

started recover at $TIMESTAMP

media recovery complete

4) Verify the tablespace has been restored to its former glory…

SQL> select bytes/1024/1024 “MB”, maxbytes/1024/1024 “Extensible to” from dba_data_files

where tablespace_name='<tbsp_name>’;

Thanks for reading!! Feedback is appreciated and answered promptly.

Take care,





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s