Restore Database: Disaster Recovery Scenarios (Part 4)

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

Good morning all!

I have been getting good readership on my most recent posts on disaster recovery. I can’t stress it enough that you have to have a good backup strategy. I will also quote a certain security professional author who said, “the only way to test a backup is to restore it.” I couldn’t agree more. If you haven’t run through some recovery scenarios in regards to recovery manager (RMAN), I would highly recommend it.

Restore Database… Recover Database

If you are seriously considering this step, you are likely at your last resort. If you are not at your last resort, please review my previous three posts on restoring logs, datafiles and tablespaces. In nearly six years of working with Oracle Databases, I have yet to exercise this option live. I have restored maintenance using a clone from production. (See )

I performed today’s exercise on a maintenance database that was already set up with RMAN and archive logs. Since I used a maintenance database, I did not need to rename datafiles. I took a backup of the database using datapump and restored the database to its original location. This and all other scenarios on this page are my personal research and should not be considered sponsored in any way by Oracle Corporation. For restoration procedures outside of this scenario, please refer to the Oracle RMAN documentation.

This scenario assumes that the database has lost all or most of its datafiles.

I performed this as an exercise instead of an actual disaster recovery. As such, I took a backup of the database using datapump. Taking a fresh RMAN backup would have been fruitless as I would end up using my fresh backup to restore the database instead of the backup I was trying to test.

Step 1: Backup database using datapump

[oracle@blablasrvr01] $> nohup expdp directory=data_pump_dir dumpfile=full_db.dmp full=y logfile=full_db.log

–optionally set nologfile=y to suppress logging.

Enter credentials.

Step 2: Preview backups to be used in a restore operation

[oracle@blablasrvr01] $> RMAN

[oracle@blablasrvr01] $> connect target /

>RESTORE DATABASE PREVIEW(SUMMARY); — PREVIEW SUMMARY can be executed if the preview provides too much information.


Step 3: Restore and Recover the database

**Note** This scenario assumes we are restoring datafiles to the default location. Restoring datafiles to a nondefault location (new name) is out of scope for this scenario. (See the link for my article on clone refreshes.)



connected to target database: MAINT_DB (DBID=09999999)

RMAN> exit

**NOTE** if the database is not opened, the database should be shut down, archivelog mode disabled, and re started in mount mode. If it is already down, open it in mount mode.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database noarchivelog;

From an RMAN console…


connected to target database: MAINT_DB (DBID=09999999, not open)

RMAN> restore database;

Starting restore at Oct 22 014 16:27:47

using target database control file instead of recovery catalog

Finished restore at Oct 22 014 16:57:28

RMAN> recover database;

Starting recover at Oct 22 014 17:00:39

channel ORA_DISK_1: restore complete, elapsed time: 00:05:25

starting media recovery…

media recovery complete, elapsed time: 00:09:45

Finished recovery at Oct 22 014 17:13:10

Step 4: Open database and return the database to archivelog mode

SQL> alter database open;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

–shows location of archive logs and archivelog mode.

Database log mode                                                             Archive mode

Archive destination                                                            USE_DB_RECOVERY_FILE_DEST

The database is available for use and fully recovered!!

Thanks for reading,





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