Restoring and Recovering the Auxiliary Databas in User-Managed TSPITR: Basic Steps

The procedure for restore and recovery of the auxiliary database differs depending on whether the auxiliary database is on the same host as the primary database. The examples in this section assume:

You are performing TSPITR on production database called prod1 located on host prim_host.
The recovery set tablespaces are users and tools. Tablespace users contains datafile /oracle/oradata/users01.dbf and tablespace tools contains datafile /fs2/tools01.dbf.
The auxiliary set contains the SYSTEM tablespace datafile /oracle/oradata/system.dbf, the undo tablespace datafile /oracle/oradata/undo01.dbf, and the control file /oracle/oradata/control01.dbf.
The online redo logs are named /oracle/oradata/redo01.log and /oracle/oradata/redo02.log.
All the primary database files are contained in /oracle/oradata

The different cases are described in the following sections:

Restoring and Recovering the Auxiliary Database on the Same Host
Restoring the Auxiliary Database on a Different Host with the Same Path Names
Restoring the Auxiliary Database on a Different Host with Different Path Names

Restoring and Recovering the Auxiliary Database on the Same Host

The following examples assume the case in which you restore the auxiliary database to the same host as the primary database. In this scenario, all of the primary database files are contained in /oracle/oradata, and you want to restore the auxiliary database to /oracle/oradata/aux. So, you set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the filenames from /oracle/oradata to /oracle/oradata/aux.

Perform the following tasks to restore and recover the auxiliary database:

Restore the auxiliary set and the recovery set to a location different from that of the primary database. For example, assume that the auxiliary set consists of the following files:

/oracle/oradata/control01.dbf    # control file
/oracle/oradata/undo01.dbf       # datafile in undo tablespace
/oracle/oradata/system.dbf       # datafile in SYSTEM tablespace

And the recovery set consists of the following datafiles:

/oracle/oradata/users01.dbf  # datafile in users tablespace
/oracle/oradata/tools01.dbf  # datafile in tools tablespace

You can restore backups of the auxiliary set files and recovery set files to a new location as follows:

cp /backup/control01.dbf /oracle/oradata/aux/control01.dbf
cp /backup/undo01.dbf /oracle/oradata/aux/undo01.dbf
cp /backup/system.dbf /oracle/oradata/aux/system.dbf
cp /backup/users01.dbf /oracle/oradata/aux/users01.dbf
cp /backup/tools01.dbf /oracle/oradata/aux/tools01.dbf

Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:
```
STARTUP NOMOUNT PFILE=/aux/initAUX.ora
```
Mount the auxiliary database, specifying the CLONE keyword:
```
ALTER DATABASE MOUNT CLONE DATABASE;
```
The CLONE keyword causes Oracle to take all datafiles offline automatically.
Manually rename all auxiliary database files to reflect their new locations only if these files are not renamed by DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT. In our scenario, all datafiles and online redo logs are renamed by initialization parameters, so no manual renaming is necessary.
Run the following SQL script on the auxiliary database to ensure that all datafiles are named correctly:
```
SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
/
```
If not, then rename the files manually as in the previous step.
Bring only the datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:
```
ALTER DATABASE DATAFILE /oracle/oradata/aux/system.dbf ONLINE;
ALTER DATABASE DATAFILE /oracle/oradata/aux/users01.dbf ONLINE;
ALTER DATABASE DATAFILE /oracle/oradata/aux/tools01.dbf ONLINE;
ALTER DATABASE DATAFILE /oracle/oradata/aux/undo01.dbf ONLINE;
```
Note:
The export phase of TSPITR will not work if all the files of each recovery set tablespace are not online.

At this point, the auxiliary database is mounted and ready for media recovery.
Recover the auxiliary database to the specified point in time with the USING BACKUP CONTROLFILE option. Use any form of incomplete recovery. The following example uses cancel-based incomplete recovery:
```
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
```
Open the auxiliary database with the RESETLOGS option using the following statement:
```
ALTER DATABASE OPEN RESETLOGS;
```

Restoring the Auxiliary Database on a Different Host with the Same Path Names

The following example assumes that you create the auxiliary database on a different host called aux_host. The auxiliary host has the same path names as the primary host. Hence, you do not need to rename the auxiliary database datafiles. So, you do not need to set DB_FILE_NAME_CONVERT, although you should set LOG_FILE_NAME_CONVERT.

To restore and recover the auxiliary database:

Restore the auxiliary set and the recovery set to the auxiliary host. For example, assume that the auxiliary set consists of the following files:

/oracle/oradata/control01.dbf      # control file
/oracle/oradata/undo01.dbf     # datafile in undo tablespace
/oracle/oradata/system.dbf   # datafile in SYSTEM tablespace

And the recovery set consists of the following datafiles:

/oracle/oradata/users01.dbf  # 1st datafile in users tablespace
/oracle/oradata/tools01.dbf  # 2nd datafile in tools tablespace

These files will occupy the same locations in the auxiliary host.

Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:
```
STARTUP NOMOUNT PFILE=/aux/initAUX.ora
```
Mount the auxiliary database, specifying the CLONE keyword:
```
ALTER DATABASE MOUNT CLONE DATABASE;
```
The CLONE keyword causes Oracle to take all datafiles offline automatically.
Rename all auxiliary database files to reflect their new locations only if these files are not renamed by DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT. In our scenario, the datafiles do not require renaming, and the logs are converted with LOG_FILE_NAME_CONVERT. So, no manual renaming is necessary.
Run the following script in SQL*Plus on the auxiliary database to ensure that all datafiles are named correctly.
```
SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
;
```
If not, then rename them manually as in the previous step.
Bring all datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:
```
ALTER DATABASE DATAFILE /oracle/oradata/system.dbf ONLINE;
ALTER DATABASE DATAFILE /oracle/oradata/users01.dbf ONLINE;
ALTER DATABASE DATAFILE /oracle/oradata/tools01.dbf ONLINE;
ALTER DATABASE DATAFILE /oracle/oradata/undo01.dbf ONLINE;
```
Note:
The export phase of TSPITR will not work if all the files of each recovery set tablespace are not online.

At this point, the auxiliary database is mounted and ready for media recovery.
Recover the auxiliary database to the specified point in time with the USING BACKUP CONTROLFILE option. Use any form of incomplete recovery. The following example uses cancel-based incomplete recovery:
```
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
```
Open the auxiliary database with the RESETLOGS option using the following statement:
```
ALTER DATABASE OPEN RESETLOGS;
```

Restoring the Auxiliary Database on a Different Host with Different Path Names

This case should be treated exactly like "Restoring and Recovering the Auxiliary Database on the Same Host". The same guidelines for renaming files apply in both cases.