Search This Blog

Monday, August 1, 2011

Transportable tablespaces in Oracle 10g

+-------------------------------------------------------------------------------------------+
| |
| Transportable tablespaces in Oracle 10g |
| |
+-------------------------------------------------------------------------------------------+
| Techonology : Oracle database |
+-------------------------------------------------------------------------------------------+
| Created : 11/7/2007 |
+-------------------------------------------------------------------------------------------+


SYS@ORCL > exec dbms_tts.transport_set_check('TBS1',TRUE,TRUE);

PL/SQL procedure successfully completed.

SYS@ORCL > select * from transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object SYS_C007034 in tablespace TBS1 not allowed in pluggable set
Sys owned object DATAE in tablespace TBS1 not allowed in pluggable set
Sys owned object SYS_IL0000021148C00002$$ in tablespace TBS1 not allowed in plu
ggable set

Sys owned object SYS_LOB0000021148C00002$$ in tablespace TBS1 not allowed in pl
uggable set

-----------------------------------------------------------------------------------
[oracle@tom datafiles]$ expdp "'sys/oracle as sysdba'" transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:08:54
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29341: The transportable set is not self-contained

-----------------------------------------------------------------------------------

[oracle@tom datafiles]$ expdp system/oracle transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp

Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:16:42

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
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/tbs.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:18:21


Name (192.168.0.2:oracle): ftp
331 Password required for ftp.
Password:
230 User ftp logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> put tbs1_01.dbf
local: tbs1_01.dbf remote: tbs1_01.dbf
227 Entering Passive Mode (192,168,0,2,9,101).
150 Opening data connection for tbs1_01.dbf.
226 File received ok
10493952 bytes sent in 9.2 seconds (1.1e+03 Kbytes/s)

RMAN> convert datafile 'tbs1_01.dbf'
2> from platform='Linux IA (32-bit)'
3> db_file_name_convert="ts","win";

Starting backup at 12-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TBS1_01.DBF
converted datafile=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCL_I-1121005791_TS-TBS1_FNO-7_
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 12-JUL-07

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:49:14

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user MICHAEL does not exist in the database

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:49:20


--------------------------------------------------------------------
SQL> create user michael identified by michael;
User created.
--------------------------------------------------------------------


C:\>create user michael identified by michael;
'create' is not recognized as an internal or external command,
operable program or batch file.

C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf

Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:50:07

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 01:50:12


-------------------------------------------------------------------------
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
TBS1
SQL> alter tablespace tbs1 read write;
--------------------------------------------------------------------------