DATAPUMP UTILITY:-Transportable Tablespace using move tablespace ond db to another db
This article discusses simple example of performing TTS (Transportable Tablespace) across two databases running one same OS and using same database version.
SOURCE DB
1.Lets create tablespace and user TTS for this test
2.We wish to transport a table EMP_STORE with CLOB columns to our new database
3.Lets verify the objects in the tablespace
4.We need to check if this tablespace is self contained and satisfies pre-requisites for TTS. We execute DBMS_TTS.TRANSPORT_SET_CHECK with tablespace_name as argument
note:-they will show the the possible error that may rise if the tablespace is not compatible or ready to transpoted
5.Above query does not return any rows, so we are safe to proceed.
Once done, we need to make the tablespace read-only
Note we are using exclude=xmlschema to avoid Unpublished Bug 10185688 (Data Pump TTS Export Fails With ORA-39126 & ORA-904 [ID 1273450.1] )
which causes following errors
7.We will use expdp to perform export and will be using TRANSPORT_TABLESPACES option.
8.Copy the datafile(USING SCP OR CP /u01/oradata/orc01d/tts_test01_01.dbf /u04/oradata/orc02d/tts_test01_01.dbf) to
the new system and copy to database directory. Once done place tablespace back in read-write mode
TARGET DB
1.Create the database user and assign appropriate privileges. Note that TTS only imports table,indexes and associated triggers. You need to have pre-created schema
3.Lets verify whether object is imported
4.Tablespace is imported in read-only mode which can be verified by querying status column in dba_tablespaces
You will notice that this is really fast approach of copying objects across tablespace as most of time taken isfor copying the datafile to target system.You will notice that we had kept tablespace in read-only mode while taking export and copying files.If you wish toworkaround this problem (as this causes downtime for any dml operation), you will have to make use of RMAN to perform TTS.
SOURCE DB
1.Lets create tablespace and user TTS for this test
SQL>create tablespace tts_test01 datafile '/u01/oradata/orc01d/tts_test01_01.dbf' size 256m autoextend on next 256m maxsize 20g;
SQL>create user TTS identified by TTS_123 default tablespace tts_test01;
SQL>grant connect,resource to tts;
2.We wish to transport a table EMP_STORE with CLOB columns to our new database
sys@orcl01d>grant select on scott.EMP_STORE to tts;
sys@orcl01d>conn tts/tts_123
tts@orcl01d>create table EMP_STORE_TTS as select * from scott.EMP_STORE;
Table created.
3.Lets verify the objects in the tablespace
sys@orcl01d>select owner,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where tablespace_name='TTS_TEST01'
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ------------------
TTS EMP_STORE_TTS TABLE
TTS SYS_IL0000333770C00003$$ LOBINDEX
TTS SYS_LOB0000333770C00003$$ LOBSEGMENT
4.We need to check if this tablespace is self contained and satisfies pre-requisites for TTS. We execute DBMS_TTS.TRANSPORT_SET_CHECK with tablespace_name as argument
sys@orcl01d> exec DBMS_TTS.TRANSPORT_SET_CHECK('TTS_TEST01')
PL/SQL procedure successfully completed.
sys@orcl01d> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
note:-they will show the the possible error that may rise if the tablespace is not compatible or ready to transpoted
5.Above query does not return any rows, so we are safe to proceed.
Once done, we need to make the tablespace read-only
sys@orcl01d>alter tablespace TTS_TEST01 read only;
6.Create oracle directory to store dumpfile. Note we are using exclude=xmlschema to avoid Unpublished Bug 10185688 (Data Pump TTS Export Fails With ORA-39126 & ORA-904 [ID 1273450.1] )
which causes following errors
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PLUGTS_BLK]
ORA-00904: "Q"."TABOBJ_NUM": invalid identifier
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358
7.We will use expdp to perform export and will be using TRANSPORT_TABLESPACES option.
[oracle@orcl01d]~% expdp system DUMPFILE=tts.dmp DIRECTORY=EMP_test TRANSPORT_TABLESPACES=TTS_TEST01
logfile=tts_exp.log exclude=xmlschema
Export: Release 11.2.0.2.0 - Production on Thu Jul 26 01:47:58 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DUMPFILE=tts.dmp DIRECTORY=EMP_test
TRANSPORT_TABLESPACES=TTS_TEST01 logfile=tts_exp.log exclude=xmlschema
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/EMP/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS_TEST01:
/u01/oradata/orc01d/tts_test01_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:48:42
8.Copy the datafile(USING SCP OR CP /u01/oradata/orc01d/tts_test01_01.dbf /u04/oradata/orc02d/tts_test01_01.dbf) to
the new system and copy to database directory. Once done place tablespace back in read-write mode
sys@orcl01d>alter tablespace TTS_TEST01 read write;
TARGET DB
1.Create the database user and assign appropriate privileges. Note that TTS only imports table,indexes and associated triggers. You need to have pre-created schema
create user TTS identified by TTS_123 default tablespace tts_test02;
grant connect,resource to tts;
2.Now import the datafile and give the location using impdp parameter TRANSPORT_DATAFILES[oracle@orcl02d]~% impdp system DUMPFILE=tts.dmp DIRECTORY=EMP_test
TRANSPORT_DATAFILES=/u04/oradata/orc02d/tts_test01_01.dbf logfile=tts_imp.log
Import: Release 11.2.0.2.0 - Production on Thu Jul 26 02:34:29 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** DUMPFILE=tts.dmp DIRECTORY=EMP_test
TRANSPORT_DATAFILES=/u04/oradata/orc02d/tts_test01_01.dbf logfile=tts_imp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 02:34:42
3.Lets verify whether object is imported
sys@orcl02d>select owner,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where tablespace_name='TTS_TEST01'
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ------------------
TTS EMP_STORE_TTS TABLE
TTS SYS_IL0004878485C00003$$ LOBINDEX
TTS SYS_LOB0004878485C00003$$ LOBSEGMENT
Query to ensure records are present
sys@orcl02d> select count(*) from TTS.EMP_STORE_TTS;
COUNT(*)
----------
163856
4.Tablespace is imported in read-only mode which can be verified by querying status column in dba_tablespaces
sys@orcl02d> select tablespace_name,status from dba_tablespaces where tablespace_name='TTS_TEST01';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS_TEST01 READ ONLY
Lets make it read-write as we want users to be able to update table
sys@orcl02d> alter tablespace TTS_TEST01 read write;
Tablespace altered.
You will notice that this is really fast approach of copying objects across tablespace as most of time taken isfor copying the datafile to target system.You will notice that we had kept tablespace in read-only mode while taking export and copying files.If you wish toworkaround this problem (as this causes downtime for any dml operation), you will have to make use of RMAN to perform TTS.
Comments
Post a Comment