Transport Tablespaces

Here we can see different methods that can be used to transport
tablespaces.

1. Using exp/imp
2. Using expdp/impdp
3. Using expdp/impdp with database link.
4. Cross platform transport tablespace.


There are some restrictions on transporting tablespaces, namely:

a) The source and target databases must be running on the same hardware platforms – You cannot take the datafiles from Windows NT and transfer them to HP/UX, for example. A DMP file can be copied from OS to OS – the DATAFILES of a database cannot be; the datafiles are not OS independent like a DMP file is.

Note :- You can use the cross platform transport tablespace which is a new feature
added in oracle 10g.

b) The source and target databases must be using the same character set – You cannot take a set of files from a database with a WE8ISO8859P1 character set and attach them to a UTF8 instance, for example.

c) The source database must not have a tablespace by the same name – The tablespace name from the originating database will be used. If the source database already has a tablespace by that name, Oracle cannot attach another with that name.

d) The source and target database must have the same block sizes – You cannot attach the files from a 4 KB blocksize database to an 8 KB blocksize database.

e) You must transport a self-contained set of objects – For example, you cannot transport a tablespace that contains an index without also transporting the tablespace that contains the table, which the index is on.

f) There are some objects that cannot be transported – These include snapshots/materialized views, function-based indexes, domain indexes (such as those produced by interMedia), scoped refs, and advanced queues with more then one recipient.

g) The source database must set the tablespace to be transported in READ ONLY mode for a short period of time – This is the period of time it takes to export the tablespace meta data and copy the datafiles elsewhere.

h) SYS owned objects cannot be transported – If a tablespace contains any object own by SYS,the transport will fail. This means that objects like rollback segments, the system tablespace, and so on cannot be transported (which is reasonable, as there would be no reason to transport those objects in any case).


Using exp/imp:-

Here we are going a transport a tablespace named TEST from the production database
to test database.

production db name :- tomig
Test db name :- clntomig

$ export ORACLE_SID=tomig
$ sqlplus / as sysdba

Check for transport tablespace dependent objects. I.e if you are going to transport
a tablespace and if it contains any dependent objects in any other tablespace then you need to transport that tablespace too, or you need to move the dependent objects to the same tablespace which you are going to transport. For this dependency checking we can use the below package.

SQL> exec dbms_tts.transport_set_check ('TEST',TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------
Index MANZOOR.EMP_PK in tablespace USERS points to table MANZOOR.EMP in tablespace TEST

SQL> alter index manzoor.emp_pk redbuild tablesacpace test;

Index altered.

SQL> exec dbms_tts.transport_set_check ('TEST',TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> alter tablespace test read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

$ exp "'"sys/sys as sysdba"'" file=/u01/test.dmp tablespaces=test
transport_tablespace=y

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table EMP
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

--Now the tablespace metadata has been exported. Then copy the datafiles pertaining to that tablespace and paste it to the desination database.

$ cp test01.dbf /u01/app/oracle/oradata/clntomig/test01.dbf


Then Import the metadata of tablespace and link the datafiles.

$ export ORACLE_SID=clntomig

$ imp "'"sys/sys as sysdba"'" file=/u01/test.dmp transport_tablespace=y
tablespaces=test datafiles=/u01/app/oracle/oradata/clntomig/test01.dbf

-------------
Import terminated successfully without warnings.

$ sqlplus / as sysdba

SQL> select name from V$database;

NAME
--------
CLNTOMIG

SQL> select name from V$tablespaces;

NAME
------
TEST
..
..

Now make the tablespace read write in both the database. (tomig and clntomig).

SQL> alter tablespace test read write;


Using expdp/impdp

$ export ORACLE_SID=tomig
$ sqlplus / as sysdba

SQL> exec dbms_tts.transport_set_check ('TEST',TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> alter tablespace test read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

$ expdp "'"sys/sys as sysdba"'" directory=data_pump_dir dumpfile=trans.dmp
transport_tablespaces=test

-Now copy the datafiles and dump files to the destination database.

$ cp /u01/app/oracle/oradata/tomig/test01.dbf /u01/app/oracle/oradata/clntomig/test01.dbf

$ cp /u01/app/oracle/admin/tomig/dpdump/trans.dmp /u01/app/oracle/admin/clntomig/dpdump/trans.dmp

$ export ORACLE_SID=clntomig
$ impdp "'"sys/sys as sysdba"'" directory=data_pump_dir dumpfile=trans.dmp
transport_datafiles=/u01/app/oracle/oradata/clntomig/test01.dbf

Import: Release 10.2.0.1.0 - Production on Monday, 22 November, 2010 9:25:31

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 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' directory=data_pump_dir dumpfile=usning_expdp.dmp transport_datafiles=/u01/app/oracle/oradata/clntomig/test01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 09:25:38

..
Now make the tablespace read write in both the database.

SQL> alter tablespace test read write;

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

Using expdp/impdp with dblink and cross platform Transport tablespace

dblink is used to access remote database by connecting to a local database.

remote database : tomig ( RHEL 4 Server)
local datbase : firstdb ( Windows Server)

here say i have db link created in my local database which links to the remdb,
using this dblink i can access my objects that present in the remote database by connecting to my local database instead of connecting to the remote database.

Here we are going to transport the tablespace which is present in the remote database
using the database link (dblink) and then going to import the same in the local database.


local database (Windows Server) :-

> set oracle_sid=firstdb
> sqlplus / as sysdba

SQL> create public database link link_tomig using 'TOMIG';

SQL> grant dba to manzoor;

SQL> exit;

C:\Documents and Settings\Administrator>expdp manzoor/ahamed directory=data_pump_dir dumpfile=trans_net.dmp network_link=link_tomig transport_tablespaces=test

Export: Release 10.2.0.1.0 - Production on Monday, 22 November, 2010 10:03:42

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
Starting "MANZOOR"."SYS_EXPORT_TRANSPORTABLE_01": manzoor/******** directory=data_pump_dir dumpfile=trans_net.dmp network_link=link_tomig transport_tablespaces
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "MANZOOR"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MANZOOR.SYS_EXPORT_TRANSPORTABLE_01 is:
D:\ORACLE\PRODUCT\10.2.0\ADMIN\FIRSTDB\DPDUMP\TRANS_NET.DMP
Job "MANZOOR"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:03:54


-- Here using the database link we have exported the metadata of the TEST tablespace which is present in the remote server.

Now copy the database file from the remote server to the local server
using any of the below methods.

1. FTP
2. SCP
3. WINscp (or)
4. dbms_file_transfer package.

In this example we can use the dbms_file_transfer package to transfer the
datafile from the remote to the local server.

In Remote Database execute the below.

$ sqlplus / as sysdba

SQL> create or replace directory file_dir as '/u01/app/oracle/oradata/tomig';

Directory created.

SQL> grant read, write on directory file_dir to manzoor;

Grant succeeded.

Now in you local database.

$ sqlplus / as sysdba

SQL> conn manzoor/ahamed;

SQL> begin
dbms_file_transfer.get_file
(
SOURCE_DIRECTORY_OBJECT => 'FILE_DIR',
SOURCE_FILE_NAME => 'test01.dbf',
SOURCE_DATABASE => 'LINK_TOMIG',
DESTINATION_DIRECTORY_OBJECT => 'DATA_PUMP_DIR',
DESTINATION_FILE_NAME => 'TEST01.DBF'
);
end;
/

PL/SQL procedure successfully completed.



Now we have the dump of test tablespace and the datafiles of the test tablespace
in our local server (windows).

Since the datafile of the remote is different platform , we need to use the cross platform transprot tablespace method to accomplish this task.

Check the endian format of your remote and the local server.

SQL> select * from V$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big

17 rows selected.

If the endian format of both the platfrom (remote and local) are same then we
can directly link the datafiles to the database , else we need to convert the
datafiles using RMAN and then we need to import to the database.

Here

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little

as per the above both the server has the same endian format so we can directly import
to the database. Suppose if the endian format is different then perform the below
steps to convert the datafile and then do the import.

> rman target /

RMAN> convert datafile 'D:\oracle\product\10.2.0\admin\firstdb\dpdump\test01.dbf' from platform 'Linux IA (32-bit)' db_file_name_convert '/u01/app/oracle','D:\oracle';

Starting backup at 22-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\FIRSTDB\TEST01.DBF
converted datafile=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-TOMIG_I-2728202513_TS-TEST_FNO-5_02LTKQBP
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04
Finished backup at 22-NOV-10

--
Now the converted file has been placed at oracle home.

C:\Documents and Settings\Administrator>impdp manzoor/ahamed directory=data_pump_dir dumpfile=TRANS_NET transport_datafiles=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABA
28202513_TS-TEST_FNO-5_02LTKQBP

Import: Release 10.2.0.1.0 - Production on Monday, 22 November, 2010 10:38:48

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 "MANZOOR"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "MANZOOR"."SYS_IMPORT_TRANSPORTABLE_01": manzoor/******** directory=data_pump_dir dumpfile=TRANS_NET transport_datafiles=D:\ORACLE\PRODUCT\10.2.0\DB_1
IG_I-2728202513_TS-TEST_FNO-5_02LTKQBP
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39151: Table "MANZOOR"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "MANZOOR"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at 10:38:50

Now copy the datafile from oracle home and paste it to the database directory.

SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace test offline;

Tablespace altered.

SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-TOMIG_I-2728202513_TS-TEST_FNO-5_02LTKQBP' to 'D:\oracle\product\10.2.0\oradata\firstdb\test01.dbf';

Database altered.

SQL> alter tablespace test online;

Tablespace altered.



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