Oracle RMAN 11g Backup and Recovery (123 page)

2.
Shut down the database.

3.
Startup mount the database.

4.
Issue the
reset database to incarnation
command to reset the incarnation.

5.
Restore the database using the
restore
command (e.g.,
restore until sequence 100 thread 1
).

6.
Recover the database (
recover until sequence 100 thread 1
).

7.
Open the database using the
resetlogs
command.

Like we said, so much easier! Here is some sample output of a recovery that resets the incarnation. Notice what happens in the
list incarnation
command as we complete the recovery.

Also note that if Oracle needed to recover through the
resetlogs
command of incarnation 6, it would have done so automatically without our having to tell it to. We have removed some unneeded output to protect the innocent (trees, that is!).

RMAN> list incarnation of database;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 ROB10R2 3753137102 PARENT 1 30-AUG-05

2 2 ROB10R2 3753137102 PARENT 534907 03-OCT-05

3 3 ROB10R2 3753137102 PARENT 3586765 04-FEB-06

4 4 ROB10R2 3753137102 PARENT 3599781 05-FEB-06

5 5 ROB10R2 3753137102 PARENT 3715262 08-FEB-06

6 6 ROB10R2 3753137102 CURRENT 4296046 20-FEB-06

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)

Oracle instance started

356
Part III: Using RMAN Effectively

database mounted

RMAN> reset database to incarnation 5;

database reset to incarnation 5

-- Note the reset SCN on the current incarnation is greater than

-- the SCN we wish to restore to. This is why we had to do a

-- restore to the previous incarnation.

RMAN> restore database until scn 4296041;

Starting restore at 20-FEB-06

allocated channel: ORA DISK 1

channel ORA DISK 1: sid 156 devtype DISK

allocated channel: ORA DISK 2

channel ORA DISK 2: sid 155 devtype DISK

... blah blah blah… more stuff here we don't really need to see...

RMAN> recover database until scn 4296041;

Starting recover at 20-FEB-06

using channel ORA DISK 1

using channel ORA DISK 2

... blah blah blah… more stuff here we don't really need to see...

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- ------ --------- ----------

1 1 ROB10R2 3753137102 PARENT 1 30-AUG-05

2 2 ROB10R2 3753137102 PARENT 534907 03-OCT-05

3 3 ROB10R2 3753137102 PARENT 3586765 04-FEB-06

4 4 ROB10R2 3753137102 PARENT 3599781 05-FEB-06

5 5 ROB10R2 3753137102 CURRENT 3715262 08-FEB-06

6 6 ROB10R2 3753137102 ORPHAN 4296046 20-FEB-06

RMAN> alter database open resetlogs;

database opened

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- ------ --------- ----------

1 1 ROB10R2 3753137102 PARENT 1 30-AUG-05

2 2 ROB10R2 3753137102 PARENT 534907 03-OCT-05

3 3 ROB10R2 3753137102 PARENT 3586765 04-FEB-06

4 4 ROB10R2 3753137102 PARENT 3599781 05-FEB-06

5 5 ROB10R2 3753137102 PARENT 3715262 08-FEB-06

6 6 ROB10R2 3753137102 ORPHAN 4296046 20-FEB-06

7 7 ROB10R2 3753137102 CURRENT 4296046 20-FEB-06

Tablespace Point-In-Time Recovery

Tablespace point-in-time recovery (TSPITR) is yet another feature of RMAN that was made easier in Oracle Database 10
g.
Much of the previous hassle with the creation of the auxiliary instance has now been done away with, although you can still manually create the auxiliary instance if you choose to. Why use tablespace point-in-time recovery? Consider a case where your user has dropped a table in error…in fact, the user has dropped three tables, truncated two more tables, and then called you asking for your help in getting it all back. Fortunately for you, RMAN allows you to do TSPITR. TSPITR might come in handy in a number of cases. Such situations might Chapter 14: RMAN Advanced Recovery Topics
357

include accidentally dropping a table, as in our first example of the chapter, or perhaps a big bulk data load program ended up logically corrupting data.

Before you begin performing TSPITR, you need to be aware of some terms related to TSPITR

since Oracle Database 10
g
:

■
Auxiliary destination
An optional location where Oracle will create the auxiliary set.

■
Auxiliary instance
A temporary instance that you create. RMAN uses this instance to perform TSPITR. After you complete your TSPITR, you can remove the auxiliary instance.

■
Auxiliary set
The set of remaining target database files that you need to perform TSPITR.

This set includes a backup control file, rollback and undo segment tablespace datafiles, the SYSTEM tablespace datafiles, online redo logs of the auxiliary database, and, optionally, a temporary tablespace in the auxiliary database.

■
Recovery set
The set of tablespaces/datafiles that you wish to perform TSPITR on.

■
Target instance
Contains the tablespace(s) to be recovered.

In the next section, we will look at an example of performing automated tablespace point-in-time recovery using RMAN. We will also address manual tablespace point-in-time recovery with RMAN.

Performing Automated TSPITR

In this example, we will have Oracle create the auxiliary instance for us. The basic steps of successful TSPITR are as follows:

1.
Prepare for a TSPITR.

2.
Perform the actual TSPITR.

Let’s look at each of these steps in more detail next. We will also look at performing automated tablespace point-in-time recovery with the auxiliary instance already prepared.

Prepare for a TSPITR

Before you can begin TSPITR, you need to complete some steps that we discuss in this section:

■ Determine what point in time to restore to.

■ Make sure the objects are fully contained within the tablespace(s) you wish to restore.

■ Preserve objects or data that will otherwise be lost.

Determine the Point in Time to Restore To
The most critical factor here is to determine what point in time you wish to restore your tablespace to. Be cautious here, because recovery of the tablespace is a one-shot deal if you are not using a recovery catalog. If you misidentify the point in time to recover to, you will not be able to retry the recovery. If you are using a recovery catalog, then this restriction does not exist.

Make Sure the Objects in the Transport Set Are Self-Contained
You should also use the TS_

PITR_CHECK view to make sure your recovery set is complete, and identify any other tablespaces that might need to be included. For example, assume that you have a tablespace TEST_RECOVER

358
Part III: Using RMAN Effectively

and need to restore it using TSPITR. You first need to check the TS_PITR_CHECK view to make sure that there are no other dependent tablespaces. Here is an example of a query to check if the TEST_RECOVER tablespace can be transported alone:

Set lines 132

Column obj1 owner format a20

column obj1 name format a20

column obj1 type format a20

column reason format a60

SELECT obj1 owner, obj1 name, obj1 type, reason

FROM SYS.TS PITR CHECK

WHERE ( TS1 NAME IN ('TEST RECOVER')

AND TS2 NAME NOT IN ('TEST RECOVER') )

OR ( TS1 NAME NOT IN ('TEST RECOVER')

AND TS2 NAME IN ('TEST RECOVER') );

This would return no rows if there were no conflicts. If there were conflicts, you would see a row describing each conflict, as shown here:

OBJ1 OWNER OBJ1 NAME OBJ1 TYPE REASON

------------ ---------------- ----------- -----------------------------

SCOTT TEST TSPITR TABLE Tables and associated indexes not fully contained in the

recovery set

In this case, we have an index that appears to be created in another tablespace. This index is associated with the TEST_TSPITR object. We need to find out what tablespace that index is in and restore that tablespace, too.

Preserve Objects or Data that Might Be Lost in the Recovery
Obviously, if you are going to restore the TEST tablespace to 2 P.M., any changes to that tablespace (new objects, update/insert or delete operations) will be lost after that point. Losing those objects may be fine, but suppose that you need to preserve that data. If this is the case, you need to export the data to be preserved (or, alternatively, copy it to somewhere else in the database). Oracle provides a view, TS_PITR_

OBJECTS_TO_BE_DROPPED, that lists all objects that will be lost during the recovery operation.

Use this view to determine what the status of the objects in the tablespace will be after the recovery.

For example, if you were going to restore the TEST_TSPITR tablespace to a point in time of 02/20/2006 at 23:40:00, you would lose the TEST_TSPITR_TWO object, as shown in this sample output:

SQL>select * from TS PITR OBJECTS TO BE DROPPED where

2> tablespace name 'TEST RECOVER';

OWNER NAME CREATION TIME TABLESPACE NAME

---------- -------------------- ------------------- ----------------

SCOTT TEST TSPITR TWO 02/20/2006 23:42:46 TEST RECOVER

SCOTT TEST TSPITR 02/20/2006 23:26:26 TEST RECOVER

SCOTT TEST TSPITR THREE 02/21/2006 00:18:29 TEST RECOVER

Perform the Actual TSPITR

RMAN will perform automated TSPITR for you, which means that it will create the auxiliary instance for you. In this case, all you need to do is connect to the target database and the optional