Most of oracle’s MAA documentation for transportable tablespaces, seems to recommend that one should put the tablespaces one is transporting, in read-only mode in the source database, before copying the datafiles to the target. This in most cases means application downtime.
In order to minimize the downtime the recommendations seem to be
- Create a dataguard physical standby database and use this standby database as the source for transport
- Create a duplicate (aka clone) of your source and use this new duplicate as the source for transport
If you are only transporting a subset of your tablespaces and you want to minimize your downtime, a 3rd good option is to create and use transportable tablespace sets from your already existing rman backups. This process is documented in the Backup and Recovery users guide, Chapter 26 (11gR2 manual).
Since this process uses an existing rman backup you incur no downtime (ie no need to place tablespaces in read-only mode) on your production systems.
Below are the steps to accomplish this (In my example rk01 is the source database and rk02 is the target database).
- First off, your source database should be running in archivelog mode
- Take a full database backup from your source database
export ORACLE_SID=rk01
rman target /run {allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’;backup as BACKUPSET tag ‘%TAG’ database;backup as BACKUPSET tag ‘%TAG’ archivelog all not backed up;release channel oem_backup_disk1;}run {allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’ maxpiecesize 1000 G;backup as BACKUPSET tag ‘%TAG’ current controlfile;release channel oem_backup_disk1;}exit;
- Create a transportable tablespace set for the tablespaces you need to transport
export ORACLE_SID=rk01
rman target /
RMAN> transport tablespace example
2> tablespace destination ‘/u01/orarch/rk01/datafile’
3> auxiliary destination ‘/u01/orarch/rk01/tmp’;
Once the process is complete rman leaves a copy of the datafile (An operating system file, not a backup set file) for the tablespace example , in the directory /u01/orarch/rk01/datafile. It also leaves a export dump file that has the metadata needed for the transport in the same directory /u01/orarch/rk01/datafile.
- Do endianness conversions on the files, if you need to go cross platform (Use rman convert)
- Attach the tablespace to your target database
export ORACLE_SID=rk02
sqlplus / as sysdba
create directory tts_dir as ‘/u01/orarch/rk01/datafile’
/
grant all on directory tts_dir to public
/
Exit;
Before you run the next import, make sure that you have created the schema’s (with appropriate privileges) that are in the tablespace you are transporting in the target database rk02, also make sure any roles that are required are created in the target. Eg: The schema HR has objects in the example tablespace. use the create user command to create the HR user with appropriate privileges in the database rk02.
impdp system/manager dumpfile=dmpfile.dmp directory=tts_dir transport_datafiles=/u01/oradata/rk02/example01.dbf logfile=tts_import.log
So As you can see the whole process is executed without shutting down the source database rk01.
