Creating a transportable tablespace set from RMAN backupsets

Creating a transportable tablespace set from RMAN backupsets:

As of 10.2,we can create an transportable tablespace set from RMAN backupsets without any impact to the current live database. Further, it can be used as a work-around to the error ORA-29308 when trying to perform TSPITR against a LOB object.

The TRANSPORT TABLESPACE will result in the following steps, carried out automatically by RMAN:
1) create an auxiliary instance
- create an auxiliary init file
- startup the auxiliary instance in nomount mode
- restore the controlfile from the source to the auxiliary
- alter database mount

2) recover auxiliary instance
- restore system related tablespace (system, undo, sysaux)
- restore transportable set datafiles to the auxiliary destination
- switch datafile to point to auxiliary destination
- recover auxiliary instance apply archivelogs if necessary and removing them once completed
- alter database open resetlogs on the auxiliary instance

3) perform data pump auxiliary instance
- recovery set of tablespaces are placed in read-only mode
- data pump export invoked to generate set of transportable recovery set of tablespace


A sample TTS script (tts.rcv)
run {
transport tablespace "USERS"
Tablespace destination '/ora_backup/tts'
Auxiliary destination '/ora_backup/tts'
Datapump directory data_pump_dir
dump file 'tts_test.dmp'
Import script 'tts_test.imp'
Export log 'exp_tts_test.log'
UNTIL TIME "to_date('02 NOV 2007 14:37:00','DD MON YYYY hh24:mi:ss')";
}
At the source database
1) set ORACLE_SID to the source db, eg:
$ export ORACLE_SID=ORA1020
2) Ensure all directories exist at both the operating system level and the database. To confirm the datadump directory:

SQL> select * from dba_directories
where DIRECTORY_NAME = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---- ---------------------- ----------------------------------------
SYS DATA_PUMP_DIR /apps/oracle/product/10.2.0/rdbms/log/

3) Execute the tts command file:
$ rman target / catalog rman/rman@rcat cmdfile tts.rcv log tts.log
Now you have a complete set of script and transportable tablespace set to plug into the destination database.
At the destination database
1) Ensure that the data pump directory exists at the destination host
SQL> select * from dba_directories
where DIRECTORY_NAME = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
---- ---------------------- ----------------------------------------
SYS DATA_PUMP_DIR /apps/oracle/product/10.2.0/rdbms/log/
2) Transfer the data pump export dump to data_pump_dir
3) Transfer all the datafiles from the "tablespace destination" to the destination host's transport_tablespace directory
4) Ensure that the tablespace to be imported does not already exist at the target database, otherwise drop it
SQL> select tablespace_name, status from dba_tablespaces;
5) Ensure that the owners of the objects in the tablespace exist in this database.If not , create the users.
6) Transfer the import script "tts_test.imp" to the destination host.
7) Run the tts_test.imp file:
a) Import the tablespaces:
$ impdp / directory=data_pump_dir dumpfile= 'tts_test.dmp' transport_datafiles= /ora_backup/tts/users01.dbf
b) Attach the datafiles:
$ sqlplus / as sysdbda
SQL> @tts_test.imp
The tablespace should now be plugged in and ready for use.
Note: For read only tablespaces, you should put them into read-write, then read only again. This is to avoid a known bug when backing up read-only tablespaces via RMAN.

Sample content of tts_test.imp
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'tts_test.dmp' transport_datafiles= /ora_backup/tts/users01.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/ora_backup/tts/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'tts_test.dmp';
dump_file.directory_object := 'data_pump_dir';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'users01.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------


Conditions necessary for transporting tablespace:

Condition 1:
------------

To check whether a tablespace can be transported and whether a tablespace is self contained, run:

dbms_tts.transport_set_check

The dbms_tts package is owned by sys, so to execute as another user either create a synonym or precede the package with 'sys'. In addition, the user must have been granted the role execute_catalog_role.


To create a synonym:

create synonym dbms_tts for sys.dbms_tts;

Run the procedure with the tablespace names as

SQL> execute dbms_tts.transport_set_check('LAVA',TRUE);
Statement processed.

This is going to populate a table called transport_set_violations. This
table is owned by the user sys. To query the table, either precede the
tablename by 'sys.' or create a synonym:

create synonym transport_set_violations for sys.transport_set_violations;

Check the view for any possible violations.

SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object CATEGORIES in tablespace LAVA not allowed in pluggable set
1 row selected.