Oracle RMAN 11g Backup and Recovery (126 page)

Chapter 14: RMAN Advanced Recovery Topics
365

Run {

Set until time "to date('02/23/2006 11:42:55','mm/dd/yyyy hh24:mi:ss')"; recover clone database

tablespace "TEST RECOVER", "SYSTEM", "UNDOTBS1" delete archivelog; alter clone database open resetlogs;

}

We are now ready to perform the final step in the recovery process. We need to export the metadata from the clone database to the host database. Here is that process: C:\Oracle\auxdest>exp 'sys/robert as sysdba' point in time recover y

tablespaces test recover file tspitr a.dmp

Export: Release 10.2.0.1.0 - Production on Thu Feb 23 12:44:30 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition

Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...

For tablespace TEST RECOVER ...

. exporting cluster definitions

. exporting table definitions

. . exporting table TEST TSPITR

. . exporting table TEST TSPITR TWO

. . exporting table TEST TSPITR THREE

. exporting referential integrity constraints

. exporting triggers

. end point-in-time recovery

Export terminated successfully without warnings.

Now we shut down the clone:

C:\Oracle\auxdest>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 23 12:47:05 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Finally, we import the metadata into the source database. Note that we already recovered the datafile to the source database earlier, so we don’t have to physically move the file.

C:\Oracle\auxdest>imp 'sys/robert as sysdba'

point in time recover y file tspitr a.dmp

366
Part III: Using RMAN Effectively

Now, all that remains to be done is to online the tablespace:

Alter tablespace test recover online;

Perform Post-TSPITR Activities After a Manual TSPITR

There are a few actions that you need to perform once your TSPITR is complete. First, you should reconnect RMAN to just the target database (and the recovery catalog if you are using one), and back up the tablespaces you just recovered. You will also want to remove the remnants of the auxiliary/clone instance.

TSPITR Restrictions

TSPITR has a number of restrictions, a quick summary of which follows:

■ You can’t restore tablespaces with objects owned by SYS.

■ Any tablespace with replicated master tables cannot be recovered with TSPITR.

■ Tablespaces with snapshot logs are not supported.

■ You can’t restore tablespaces that contain rollback segments.

■ If an object within the tablespace to be recovered has one of the following types, then TSPITR is not supported:

V

■

ARRAY

Nested

■

tables

External

■

files

Additionally, TSPITR cannot be used to recover a dropped tablespace. You also will not be able to recover older object statistics.

Finally, there are some restrictions with regard to TSPITR if you are using RMAN without a recovery catalog:

■ The current physical structure of the undo and rollback segments in the target database must be unchanged between the point you wish to recover to and the current point. In other words, the rollback segments can’t change during the recovery.

■ Once you have completed TSPITR on a given tablespace, all previous backups of that tablespace are no longer usable for future TSPITR recoveries of that tablespace. This is why a backup of the tablespace after TSPITR is very important, in case you need to run another TSPITR.

Verifying Your Backups Are Recoverable

Of course, backups are not useful if they are not recoverable. RMAN provides a method of checking the restorability of your database without actually restoring it. In fact, RMAN offers you a couple of options. In this section, we look at some different ways to verify that your database

Chapter 14: RMAN Advanced Recovery Topics
367

backups, and thus your database, are recoverable. First, we will look at the
verify
and
check
logical
options of the
restore
command. Then, we will look at the
validate backupset
command.

The restore preview Command

If you want to see which backup sets RMAN will use to perform a particular recovery, you can use the
restore preview
command. This command will detail the backup sets that will be required to perform the restore you indicate on the command line. For example, you can determine which backup set a full restore will apply by using the command
restore database preview
. In the following example, we see the results of the
restore database preview
command. In the output, we find that RMAN will apply an incremental level 0 backup and then an incremental level 1

backup (as shown in the LV column under the individual List of Backup Sets reports). It then also displays the archived redo logs that will be applied. At the end, we also get information that will help us with incomplete recovery if that is what we wanted.

RMAN> restore database preview;

Starting restore at 23-FEB-06

using channel ORA DISK 1

using channel ORA DISK 2

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

210 Incr 0 66.36M DISK 00:01:23 23-FEB-06

BP Key: 210 Status: AVAILABLE Compressed: YES

Tag: AG20060223T142518

Piece Name:

C:\ORACLE\PRODUCT\10.2.0\FLASH RECOVERY AREA\ROB10R2\BACKUPSET\2006 02 23

\O1 MF NNND0 TAG20060223T142518 1ZW6KK4H .BKP

List of Datafiles in backup set 210

File LV Type Ckp SCN Ckp Time Name

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

2 0 Incr 4471679 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\UNDOTBS01.DBF

3 0 Incr 4471679 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\SYSAUX01.DBF

4 0 Incr 4471679 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\USERS01.DBF

5 0 Incr 4471679 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\EXAMPLE01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

211 Incr 0 83.30M DISK 00:01:45 23-FEB-06

BP Key: 211 Status: AVAILABLE Compressed: YES

Tag: TAG20060223T142518

Piece Name:

C:\ORACLE\PRODUCT\10.2.0\FLASH RECOVERY AREA\ROB10R2\BACKUPSET\2006 02 23

\O1 MF NNND0 TAG20060223T142518 1ZW6KYN2 .BKP

368
Part III: Using RMAN Effectively

List of Datafiles in backup set 211

File LV Type Ckp SCN Ckp Time Name

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

1 0 Incr 4471684 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\SYSTEM01.DBF

6 0 Incr 4471684 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\NEWTBS01.DBF

7 0 Incr 4471684 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\TEST RECOVER 01.DBF

8 0 Incr 4471684 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\TEST RECOVER TWO 01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

216 Incr 1 728.00K DISK 00:00:38 23-FEB-06

BP Key: 216 Status: AVAILABLE Compressed: YES

Tag: TAG20060223T144904

Piece Name:

C:\ORACLE\PRODUCT\10.2.0\FLASH RECOVERY AREA\ROB10R2\BACKUPSET\2006 02 23

\O1 MF NNND1 TAG20060223T144904 1ZW7Y40G .BKP

List of Datafiles in backup set 216

File LV Type Ckp SCN Ckp Time Name

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

2 1 Incr 4472638 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\UNDOTBS01.DBF

3 1 Incr 4472638 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\SYSAUX01.DBF

4 1 Incr 4472638 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\USERS01.DBF

5 1 Incr 4472638 23-FEB-06

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\EXAMPLE01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time

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