Performing User-Managed TSPITR with Transportable Tablespaces

After you have completed the preparation stage, begin the actual TSPITR procedure as described in Oracle Database Administrator's Guide. The procedure occurs in the following steps:

Step 1: Unplugging the Tablespaces from the Auxiliary Database
Step 2: Transporting the Tablespaces into the Primary Database

Step 1: Unplugging the Tablespaces from the Auxiliary Database

In this step, you recover the auxiliary database to the desired past time, then unplug the desired tablespaces.

To unplug the auxiliary database tablespaces:

Connect SQL*Plus to the auxiliary database with administrator privileges. For example:
```
% sqlplus 'SYS/oracle@aux AS SYSDBA'
```
Make the tablespaces in the recovery set read-only by running the ALTER TABLESPACE ... READ ONLY statement. For example, make users and tools read-only as follows:
```
ALTER TABLESPACE users READ ONLY;
ALTER TABLESPACE tools READ ONLY;
```

Ensure that the recovery set is self-contained. For example:

EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('users,tools',TRUE,TRUE);

Query the transportable tablespace violations table to manage any dependencies. For example:
```
SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
```
This query should return no rows after all dependencies are managed. Refer to Oracle Database Administrator's Guide for more information about this table.
Generate the transportable set by running the Export utility as described in Oracle Database Administrator's Guide. Include all tablespaces in the recovery set, as in the following example:
```
% exp SYS/oracle TRANSPORT_TABLESPACE=y TABLESPACES=(users,tools) \
TTS_FULL_CHECK=y
```
This command generates an export file named expdat.dmp.

Step 2: Transporting the Tablespaces into the Primary Database

In this step, you transport the recovery set tablespaces into the primary database.

To plug the recovery set tablespaces into the primary database:

Connect SQL*Plus to the primary database (not the auxiliary database). For example:
```
% sqlplus 'SYS/oracle@primary AS SYSDBA'
```

Drop the tablespaces in the recovery set with the DROP TABLESPACE statement. For example:

DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE tools INCLUDING CONTENTS;

Restore the recovery set datafiles from the auxiliary database to the recovery set file locations in the primary database. For example:

% cp /net/aux_host/aux/users01.dbf \
> /net/primary_host/oracle/oradata/users01.dbf
% cp /net/aux_host/aux/tools01.dbf \
> /net/primary_host/oracle/oradata/tools01.dbf

Move the export file expdat.dmp to the primary host. For example, enter:

% cp /net/aux_host/aux/expdat.dmp \
> /net/primary_host/oracle/oradata/expdat.dmp

Plug in the transportable set into the primary database by running Import as described in Oracle Database Administrator's Guide. For example:
```
% imp SYS/oracle TRANSPORT_TABLESPACE=y FILE=expat.dmp
   DATAFILES=('/oracle/oradata/users01.dbf','/oracle/oradata/tools01.dbf')
```
Make the recovered tablespaces read/write by executing the ALTER TABLESPACE READ WRITE statement. For example:
```
ALTER TABLESPACE users READ WRITE;
ALTER TABLESPACE tools READ WRITE;
```
Back up the recovered tablespaces with an operating system utility.

Caution:

You must back up the tablespace because otherwise you might lose it. For example, a media failure occurs, but the archived logs from the last backup of the database do not logically link to the recovered tablespaces. If you attempt to recover any recovery set tablespaces from a backup taken before TSPITR, then recovery fails.