peoplesoft DBA: Incomplete Database Recovery in 10g and 11g Databases

Incomplete Database Recovery in 10g and 11g Databases

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.

2- Perform a whole and full database backup using RMAN

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

4- Perform the incomplete recovery

5- Make sure that the table test_incomplete_rec is recovered.

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.

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.

- 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.

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).