Transportable tablespace EXP/IMP

Transportable tablespace EXP/IMP with OS files
=============================================


* Using RMAN CONVERT

1. Prepare for export of the tablespace.

* Check that the tablespace will be seft contained:
SQL> execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;
==>These violations must be resolved before the tablespaces
can be transported

* The tablespaces need to be in READ ONLY mode in order to successfully
run a transport tablespace export.

SQL> ALTER TABLESPACE TBS1 READ ONLY;
SQL> ALTER TABLESPACE TBS2 READ ONLY;



2. Export the metadata.
* Using the original export utility

exp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_exp.log
transport_tablespace=y tablespaces=TBS1,TBS2

* Using datapump exp

CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir' ;
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES = TBS1,TBS2

If you want to perform a transport tablespace operation with a strict
containment check, use the TRANSPORT_FULL_CHECK parameter:

expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir
TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y

If the tablespace set being transported is not self-contained then the
export will fail.

3. Use V$TRANSPORTABLE_PLATFORM to determine the endianness of each platform.
You can execute the following query on each platform instance:

SELECT tp.platform_id,substr(d.PLATFORM_NAME,2,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;


4. If you see that the endian formats are different and then a conversion is
necessary for transporting the tablespace set.

RMAN> convert tablespace TBS1
to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';

RMAN> convert tablespace TBS2
to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';

then copy the datafiles with ftp or copy

4. Import the transportable tablespace

* Using the original import utility

imp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_imp.log
transport_tablespace=y datafiles='/tmp/....','/tmp/...'

* Using datapump

CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir' ;
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_DATAFILES='/tmp/....','/tmp/...'
REMAP_SCHEMA=(source:target)
REMAP_SCHEMA=(source_sch2:target_schema_sch2)

You can use REMAP_SCHEMA if you want to change the ownership of the
transported database objects.

5. Put the tablespaces in read/write mode:

SQL> ALTER TABLESPACE TBS1 READ WRITE;
SQL> ALTER TABLESPACE TBS2 READ WRITE;


* Using DBMS_FILE_TRANSFER

You can also use DBMS_FILE_TRANSFER to copy datafiles to another host. You
need to follow the same steps specified above for ASM files.
But if the endian formats are different then you must use the RMAN convert
after transfering the files.

This is an example of usage:

RMAN> CONVERT DATAFILE
'/hq/finance/work/tru/tbs_31.f',
'/hq/finance/work/tru/tbs_32.f',
'/hq/finance/work/tru/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT=
"/hq/finance/work/tru/", "/hq/finance/dbs/tru"
PARALLELISM=5;

Then the same example showing destination being an +ASM diskgroup:

RMAN> CONVERT DATAFILE
'/hq/finance/work/tru/tbs_31.f',
'/hq/finance/work/tru/tbs_32.f',
'/hq/finance/work/tru/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT=
"/hq/finance/work/tru/", "+diskgroup"
PARALLELISM=5;