Transportable Tablespace (User Managed)

Performing User-Managed TSPITR with Transportable Tablespaces

Source Database:

1. Connect SQL*Plus to the auxiliary database with administrator privileges. For example:

% sqlplus 'SYS/oracle@aux AS SYSDBA'

2. 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;

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

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

4. 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.

5. 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) TTS_FULL_CHECK=y file=C:\users.dmp

Destination Database:

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

1. Connect SQL*Plus to the primary database (not the auxiliary database). For example:

% sqlplus 'SYS/oracle@primary AS SYSDBA'

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

DROP TABLESPACE users INCLUDING CONTENTS;

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

% copy C:\orcl\data\users01.dbf \

> D:\orcl\data\users01.dbf

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

% copy C:\users.dmp \

> C:\users.dmp

5. 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=C:\users.dmp

DATAFILES=('D:\orcl\data\users01.dbf')

6. Make the recovered tablespaces read/write by executing the ALTER TABLESPACE READ WRITE statement. For example:

ALTER TABLESPACE users READ WRITE;