Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

August 24, 2010

Incomplete Database Recovery in 10g and 11g Databases

Filed under: Uncategorized — orawiss @ 3:49 pm

INTRODUCTION

An incomplete recovery is a type of recovery in which only a part of redo log , archive log data are applied to a restored backup in order to bring it to a specified time, sequence number or SCN prior to the failure.
You must run the database in ARCHIVELOG mode in order to perform an incomplete recovery.

There are two main reasons to perform an incomplete recovery:

1- You have tried the complete recovery and it failed.
2- You want to lose data maybe because of user errors or just you want to duplicate the database for test purposes or to create a standby database.

The incomplete recovery consists of four steps:

1. Mount the database.
2. Restore all the datafiles.
3. Recover the database until the desired point.
4. Open the database with a resetlogs.

You can use the incomplete recovery with:

1- User managed backups: in this case you have three options: UNTIL TIME Recovery (before a specific time), UNTIL CANCEL recovery (before the one specified corrupted, missed archive log or redo log file), UNTIL CHANGE Recovery (before the one specified SCN or system change number).

2- RMAN backups: in this case you have three options: UNTIL TIME Recovery (before a specific time), UNTIL SEQUENCE recovery (before the one specified corrupted, missed archive log or redo log file), UNTIL SCN Recovery (before the one specified SCN or system change number).

3- Enterprise Manager Control or Grid Control that eventually uses RMAN.

In all cases, you must connect with SYSDBA privilege in order to perform the incomplete recovery. Note that a complete recovery can be made by either SYSDBA or SYSOPER privileges.

INCOMPLETE RECOVERY WITH RMAN

1- First be sure that the database is in ARCHIVELOG mode. Then we will create a table called test_incomplete_rec to test the incomplete recovery.

Step1

2- Perform a whole and full database backup using RMAN

Step2

3- Drop the table test_incomplete_rec. Note that we drop the table at 12:11:16.

Step3

4- Perform the incomplete recovery

Step4

5- Make sure that the table test_incomplete_rec is recovered.

Step5

6- It’s always a good practice to make a whole backup of the database after the database has a new database incarnation or a new stream of redo.

Step6
Step7
Step8

RECOVERY OF THE CONTROLFILE

The Control file and redo log files should be protected by multiplexed copies on different devices.
But, if all the copies of the control files are lost they can either be restored from a backup or be re-created.
You can restore the control file
– Thanks to the control file to trace backup command; you can extract the script from the USER_DUMP_DEST parameter.

Step9

– Thanks to the backup as copy current control file command
RMAN> backup as copy current controlfile;
– Thanks to the backup as backupset current control file command
RMAN> backup as backupset current controlfile;
– Thanks to the backup tablespace or data file include current control file command
RMAN> backup tablespace users include current controlfile;
– Thanks to the configure control file auto backup on command.
RMAN> configure controlfile autobackup on;

Example of recover the control file thanks to the configure control file auto backup on command:

1- Configure RMAN to auto backup the control file and the spfile on and make a whole backup.

Step10

2- Extract the BDID from the view v$database. This is useful If multiple databases share the same autobackup destination (as would be the case if several databases
have a common flash recovery area).

Step11

3- Stop the Oracle Service SID on windows, because we need to delete all the control files.

Step12

4- Delete all the control files.

Step13

5- Start the Oracle Service SID on windows again.

Step14

6- Attempt to open the database; the Database will stop in nomount mode.

Step15

7- Connect to RMAN and restore the control file

Step16

The database is opened with a new incarnation and the control files have been restored.

Ste17

Nice reading,
Wissem

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: