Tuesday, September 1, 2009

Transportable Table Space (TTS)

Transportable Table Space (TTS) is used to take out of the database pieces of data for various reasons (Archiving , Moving to other databases etc). From 10g onwards you can also transport a tablespace across different platforms of O/S using rman to convert the tablespace to the desired endian.

The following steps covering the TTS for an ASM tablespace named DATA.
I will also use the rman convert command to so how to move tablespaces across platforms. Finally i will drop the original tablespace reimport the exported and convert it to asm

1) Check that the tablespace will be seft contained

As sys
SQL> execute sys.dbms_tts.transport_set_check('DATA',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

If you have no rows selected you have a self contained tbs and you can proceed.
Otherwise you must fix the errors before proceed.

2) Make the tablespace read only

alter tablespace data read only;

3) Export the metadata using data pump

SQL> create or replace directory tts_dir as '/data/ttbs';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY tts_dir to system;

Grant succeeded.

expdp system DUMPFILE=data.dmp DIRECTORY = tts_dir TRANSPORT_TABLESPACES=DATA TRANSPORT_FULL_CHECK=Y

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

And check the metadata file.

[oracle@labdb01 ttbs]$ ls -la /data/ttbs/
total 512
drwxr-xr-x 2 oracle oinstall 4096 Sep 1 15:03 .
drwxr-xr-x 5 26 26 4096 Sep 1 14:13 ..
-rw-rw---- 1 oracle oinstall 81920 Sep 1 15:04 data.dmp
-rw-rw-r-- 1 oracle oinstall 1061 Sep 1 15:04 export.log

4) Use V$TRANSPORTABLE_PLATFORM to find the exact platform name of the
target database. You can execute the following query on target platform
instance or in your instance if you want just to take out the file and reimport it later.

SQL> set line 200

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

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------------------------------------------------------------------ --------------
13 Linux x86 64-bit Little
SQL>

5) Use rman to convert and copy the datafile from the ASM to the directory where the metadata exist (/data/ttbs)

rman target /
RMAN> CONVERT TABLESPACE DATA TO PLATFORM 'Linux x86 64-bit' FORMAT '/data/ttbs/%U';

ls -la /data/ttbs
drwxr-xr-x 2 oracle oinstall 4096 Sep 1 15:11 .
drwxr-xr-x 5 26 26 4096 Sep 1 14:13 ..
-rw-rw---- 1 oracle oinstall 81920 Sep 1 15:04 data.dmp
-rw-r----- 1 oracle oinstall 52436992 Sep 1 15:11 data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn
-rw-rw-r-- 1 oracle oinstall 1061 Sep 1 15:04 export.log

And your done with the export

Import the tablespace and convert it to asm

1) Drop the orginal tablespace

SQL> drop tablespace data including contents;

Tablespace dropped.

2) Use impdp to import it

impdp system DUMPFILE=data.dmp DIRECTORY = tts_dir TRANSPORT_DATAFILES='/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn'

SQL> alter tablespace data read write;

Tablespace altered.

Now the tablespace is accessible from the database but its not in the asm

3) Use RMAN to transfer the tablespace to ASM

rman target /
RMAN> backup as copy datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' format '+DATA';

If the 10g database is open you need to offline the datafile first

SQL> alter database datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' offline;

Database altered.

Switch to the copy:
RMAN> switch datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' to copy;

using target database control file instead of recovery catalog
datafile 75 switched to datafile copy "+DATA/rlabdb/datafile/data.341.696439773"

RMAN> recover datafile '+DATA/rlabdb/datafile/data.341.696439773';

Starting recover at 01-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=523 instance=rlabdb1 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 01-SEP-09

SQL> alter database datafile '+DATA/rlabdb/datafile/data.341.696439773' online;

Database altered.

And you are done !!!

No comments:

Post a Comment