Wednesday, January 16, 2013

ORACLE - Transporting Tablespace

The following steps summarize the process of transporting a tablespace:
At source:
1) Check cross-platform transport for endian format for both platforms:
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;


col PLATFORM_NAME for a32
SELECT * FROM V$TRANSPORTABLE_PLATFORM;

2) Check a self-contained set of tablespace:
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('history1',TRUE);
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('history1,history2', TRUE);
or
Add TRANSPORT_FULL_CHECK=Y in expdp

Check the violations:
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

3) In the set, make all tablespace to read only:
ALTER TABLESPACE history1 READ ONLY;

4) Use Data Pump export utility:
expdp system/pass DUMPFILE=expdp.dmp DIRECTORY=dp_dump_dir
TRANSPORT_TABLESPACES= history1, history2 [ TRANSPORT_FULL_CHECK=Y]

At Target:
5) Import using Data Pump utility:
impdp system/pass PARFILE='par.txt'

par.txt
DIRECTORY=dp_dump_dir
DUMPFILE=expdp.dmp
TRASNPORT_DATAFILES=histroy1, history2
REMAP_SCHEMA=

6) Make tablespace read write:
ALTER TABLESPACE history1 READ WRITE;

7)(optional) If endian formats or OS are diffrent then use conversion ( at source before impdp):
RMAN target /
CONVERT TABLESPACE history1, history2
TO PLATFORM 'Solaris[tm] OE'
FORMAT '/tmp/%U';

Limitations:
1) Source and Target database MUST use same character/natonal character set
2) A tablespace name MUST not exists in source database i.e If tablespace already exists, you cannot transport a tablespace
3) Self contain tablespace


No comments:

Post a Comment