Restore Datafile: Disaster Recovery Series (Part 2)

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

Good morning, RMAN Recovery Fanatics!!

This is part two in my disaster recovery scenarios. I put the logfile restore as part one for two reasons. The first is that it is the smallest recovery scenario. The second reason is it really happened. Two of the next three scenarios I will show you were simulations that I performed in a confined, test environment. The restore datafile scenario was performed on a test box and then in production. I am excited to learn about the new RMAN features in 12c but please note that my methods are 11g methods.

Restore a datafile from a full backup

On a previous assignment, I inherited a newly created database that did not include any RMAN backups. The backup strategy, invented by a Sys Admin who knew very little about databases, was a daily full export of the database using Datapump. This is the only time I have ever heard of this strategy used on a production system. My first question was…

How much data can you afford to lose?

The obvious disadvantage of datapump is its inability to apply archive logs. In a recovery, you are guaranteed to lose any transactions between the disaster and the last full export. The not so obvious disadvantage is the import itself. If you simply run $ impdp full=y … the job will fail because the database structure and data is already in place. Again, I am not sure if this is fixed in 12c. I have run into similar challenges when importing a schema using datapump. It was easier to drop the schema and recreate it as an empty shell. With a full database import, I am told one will likely have to recreate the database and tablespaces on top of the old, corrupted database. This scares me a bit just because of the risk of corruption.

Don’t do this to yourself. There is a much better way.

Part of the process of transferring this database into my greedy little hands was to execute an Operational Readiness assessment. There were twelve scenarios whose purpose was to assess whether the database was ready for prime time. I added two more scenarios to test the RMAN strategy that I implemented. The first was recovering from a database crash (I will cover this in a later post) and the second scenario was to recover the database from a full RMAN backup. As I mentioned above, recovery from a datapump export was not realistic except if all else fails.

Step 1:  Alter a Datafile.

This is an optional step. It may not be a good idea to insert rows, etc. on a production database. The purpose of exercising this step was so I could prove that my datafile restore would apply any redo or archive logs. Since I am getting ready to alter the Users tablespace, I simply inserted a row into the scott.bonus table.

Step 2: Query the Datafile.

SQL> select file#, bytes/1024/1024 as MB from v$datafile;

Step 3: Navigate to the location of the datafile and remove it. Bounce database to ensure datafile is gone.

[oracle@blablasrvr01]$ rm users01.dbf

SQL> shutdown immediate;

SQL> startup

SQL> select owner, segment_name from dba_segments where tablespace_name = ‘USERS’;

SQL> select count(1) from scott.bonus; –Should get ORA-00376: file 4 can’t be read

ORA-0110: data file4: ‘users01.dbf’

Step 4: Restore and Recover the missing datafile.

RMAN> restore datafile 4 preview –Record backup_piece required and SCN# to clear datafile fuzziness. In my case it was 578065.

RMAN> list backup of archivelog all; –One SCN must be greater than 578065

SQL> select * from v$log; –Find at least one SCN beyond 578065

**Once the required backup is in place it is easy to recover from any critical situation in Oracle**

RMAN> run


sql ‘alter database datafile 4 offline’;

restore datafile 4;

recover datafile 4;

sql ‘alter database datafile 4 online’;


RMAN> exit

Step 5: Check to make sure the datafile has been restored in the database and in the file system.

SQL> select file_name from dba_data_files; –note file system location

SQL> select count(1) from scott.bonus; –is your new row back in place?

SQL> delete from scott.bonus where…; –delete the new row.

[oracle@blablasrvr01]$ ls -ltr /data/file/location/users01.dbf

Thanks for reading…

Jason Brown



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