Oracle 11g Database Point-In-Time Recovery (DBPITR)

Publish date: 2024-03-14
Oracle 11g Database Point-In-Time Recovery (DBPITR)

Oracle 11g Database Point-In-Time Recovery (DBPITR)

Creation Date: July 30, 2009

Revision Date: July 30, 2009

Product: DS‑Client (Linux)

Summary

Linux DS-Client can perform a Point-In-Time Recovery for an entire Oracle database. Tablespace Point-In-Time recovery (TSPITR) is not supported, however DS-Client can prepare all the data files and archived logs so that users can perform a manual TSPITR, when required.

This article describes the steps for Oracle 11g DBPITR on Linux DS-Client.

See also

RMAN DBPITR Overview

RMAN (Oracle Recovery Manager) DBPITR restores the database from backups prior to the target time for recovery, then uses incremental backups and ‘redo’ to roll the database forward to the target time. You can recover to a System Change Number (SCN), log sequence number, time, or restore point.

The prerequisites for database point-in-time recovery are as follows:

Your database must be running in ARCHIVELOG mode.

You must have backups of all data files from before the target SCN for DBPITR and archived logs for the period between the SCN of the backups and the target SCN.

For a full description of command prerequisites and usage notes, refer to the ‘RECOVER’ section in the “Oracle Database Backup and Recovery Reference” documentation.

Steps involving DS-User are condensed.

1. Performing DBPITR within the current database incarnation

You can perform DBPITR with the current control file or backup control file.

During the DBPITR process, you may need to view your database version in Read Only mode. (To open a database in Read Only mode, you would normally use a backed up control file instead of the current control file.)

If the DBPITR result is what you expected, then you can open the database with the Resetlogs option.

When you perform DBPITR with DS-Client, you have two options:

NOTE:  If you select both options, the option Do not execute ‘RECOVER’ commands will override the other and the ‘Recover Database’ command will not be executed.

DBPITR using the ‘RECOVER DATABASE at ...’ option

This option will automatically recover the database to the target time and opens the database with the Resetlogs option.

If you have all the archived logs up to the target recovery time, restore the control file and all data files.

Because you are performing DBPITR with a backed up control file, you need to have the database in Nomount state using the following SQL*Plus commands:

SQL>Shutdown immediate;

SQL>Startup nomount;

1.From the DS-User’s Restore Wizard, select the Oracle backup set’s Control file and all data files. Ensure that you select the correct backup set and generation which belong to the current incarnation.

NOTE:  If you do not have all archived logs up to the target recovery time, you must also restore Archived Logfiles.

2.Continue to the Select Restore Options tab of the Restore Wizard.

3.Select the target time:

a)Select the option ‘RECOVER DATABASE’ at.

b)Input the target recovery point-in-time at which you want to recover your database.

4.Finish the Wizard.

After you click Finish, DS-Client will automatically recover the Oracle database to the target time and then it will open the database with the Resetlogs option.

DBPITR using the ‘Do not execute RECOVER commands’ option

This option will not issue the Oracle ‘RECOVER’ command. This means DS-Client will restore only the data files and archived logs back to the Oracle server. You must use RMAN to perform the DBPITR manually.

1.Put the database in Nomount state with the following SQL*Plus commands:

SQL>Shutdown immediate;

SQL>Startup nomount;

2.Select the Control file and all data files.

3.Continue to the Select Restore Options tab of the Restore Wizard.

4.Select the option “Do not execute ‘RECOVER’ commands”.

5.Finish the Wizard.

After you click Finish, DS-Client will:

Replicate the control file

Mount the database

Restore the database

6.Now manually perform DBPITR with Oracle’s RMAN.

The database will be in “Mount” state.

Use RMAN to connect to the target database and perform the DBPITR.

7.Perform the following operations within a RUN block:

RMAN>Run{

Set Until Time=”TO_DATE(‘2009-07-09 21:28:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;

Recover database;

}

If you know the specific logseq# or SCN where you want to recover the database, you can use with the RMAN Set Until clause as in the following samples:

RMAN>Run{

Set Until logseq=8;

Recover Database;

}

OR

RMAN>Run{

Set Until SCN 2325039;

Recover database;

}

8.Review the recovery results.

Open the database in Read Only mode with the following SQL*Plus command to check if you are satisfied with the results:

SQL>Alter database open Read Only;

9.If satisfied, you can open the database with the Resetlogs option:

SQL>Alter database open Resetlogs;

If not satisfied, you can repeat the DBPITR procedure to a new target time.

EXAMPLES

The following screen shots of RMAN’s outputs are based on Time, Log Sequence Number, and SCN with the Set Until clause:

Figure 1Recover Database until a target Time

Figure 2Recover Database until a target Log Sequence Number

Figure 3Recover Database until a target SCN

2. Performing DBPITR to a non-current database incarnation

Database Incarnation Overview

A database incarnation is created whenever you open the database with the RESETLOGS option. After a complete recovery, you can resume normal operations without an OPEN RESETLOGS command. After a DBPITR or recovery with a backed up control file, however, you must open the database with the RESETLOGS option, thereby creating a new incarnation of the database.

You may want to bring your database back to a time that is in a non-current incarnation (parent or ancestor incarnation). In this case, you must have the following:

Backups of all data files from the specified non-current incarnation before the target SCN for DBPITR

Archived logs for the period between the SCN of the backup until the target SCN.

Recovery Scenario

On the current date (July-14-2009), you realize that a table in your database was erroneously updated on July-09-2009 around 9:30 pm. You decide to restore your database to 2 minutes before that update was made. The target time you want to set is July-09-2009 at 9:28 pm.

1.Determine the incarnation at the target restore time.

To determine which database incarnation was current at the time of the backup, you can use the Oracle List Incarnation command. You can view incarnation history and find the primary key of the incarnation that was current at the target time:

RMAN>List incarnation;

The following output appears:

You can also query v$Database_Incarnation to get the incarnation information:

In this example:

The current incarnation is the incarnation with the Key 9, assuming that the current time July-14-2009.

The target time will be July-09-2009 21:28:00. Based on the incarnation history, you see that the incarnation with the Key 5 was the current incarnation at the recovery target time.

2.Replicate the control file:

a)Put database in Nomount state:

SQL>Shutdown immediate;

SQL>Startup Nomount;

b)Perform a restore of the control file.

c)Ensure that you select the correct backup set and generation which belongs to current incarnation at the recovery target time.

d)If you do not have all archived logs needed up to the recovery target time, you must also restore those archived logs.

3.Reset the database incarnation to the current incarnation at the recovery target time:

After the Control file is successfully replicated, your database is in Mount state

In this example, you must reset the database incarnation to 5 with the following command:

RMAN>Reset database to incarnation 5;

4.Perform a restore of all the Oracle data files.

5.Select the recovery target time.

6.Finish the Wizard. After you click Finish.

DS-Client will automatically recover database to the target time. However, the database will remain in Mount state (it will not be opened).

7.Review the recovery results. Open the database in Read Only mode with the following command to check if you are satisfied with the results:

SQL>Alter database open Read Only;

8.If you are satisfied, open the database with the Resetlogs option.

SQL>Alter database open Resetlogs;

If you are not satisfied, repeat the procedure of DBPITR to a new target time.


ncG1vNJzZmirY2Ourq3ZqKWar6NjsLC5jpqqop%2Bilnqlu8KupJ6mpJbBqrvNaH%2BepKBkkZR5oqWgnqakWn9xlMSlp2h8g2KQrbXEp6uYgJWhvXCb0ZqapZ2PZn6oq6Oaq5qakaiyoJzOoqWtZXmjepW1zJ6Wi52TpMOmvtiYX316gH57qcDMpQ%3D%3D