TABLESPACE_TRANSPORTATION

TABLESPACE TRANSPORTATION

from Linux server1 to Linux Server2

Server 1 : 192.168.188.129
Server 2 : 192.168.188.128
                                                                   
                                                        
login as: root
root@192.168.188.129's password:
[root@localhost ~]# su - oracle

[oracle@localhost ~]$ cat /etc/oratab
rcat:/u01/app/oracle/product/10.2.0/db_1:N

[oracle@localhost ~]$ ps -ef | grep pmon
oracle    4745     1  0 11:31 ?        00:00:00 asm_pmon_+ASM
oracle    8046     1  0 12:42 ?        00:00:00 ora_pmon_rcat
oracle   11244 11076  0 14:17 pts/5    00:00:00 grep pmon

[oracle@localhost ~]$ export ORACLE_SID=rcat
[oracle@localhost ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 12 14:17:42 2012

Copyright (c) 1982, 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

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
RCAT      READ WRITE

SQL> select name from v$datafile;

NAME
-----------------------------------------------
/u01/app/oracle/oradata/rcat/system01.dbf
/u01/app/oracle/oradata/rcat/undotbs01.dbf
/u01/app/oracle/oradata/rcat/sysaux01.dbf
/u01/app/oracle/oradata/rcat/users01.dbf
/u01/app/oracle/oradata/rcat/rmans01.dbf

SQL> create tablespace newtbs datafile '/u01/app/oracle/oradat/rcat/new01.dbf' size 20M;

Tablespace created.

SQL> select name from v$datafile;

NAME
-----------------------------------------------
/u01/app/oracle/oradata/rcat/system01.dbf
/u01/app/oracle/oradata/rcat/undotbs01.dbf
/u01/app/oracle/oradata/rcat/sysaux01.dbf
/u01/app/oracle/oradata/rcat/users01.dbf
/u01/app/oracle/oradata/rcat/rmans01.dbf
/u01/app/oracle/oradata/rcat/new01.dbf    <--------------- this is the new datafile added

6 rows selected.

SQL> create user newusr identified by oracle default tablespace newtbs;

User created.

SQL> grant connect , resource  to newusr;

Grant succeeded.

SQL> conn newusr/oracle
Connected.

SQL> create table tbnew (eno number, ename varchar2(20));

Table created.

SQL> insert into tbnew values(1,'check');

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.

SQL> select username,default_tablespace from dba_users where username='NEWUSR';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
NEWUSR                         NEWTBS

SQL> select tablespace_name , status from dba_tablespaces where tablespace_name='NEWTBS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
NEWTBS                         ONLINE

SQL> alter tablespace newtbs read only;

Tablespace altered.

SQL>
SQL>
SQL> exec dbms_tts.transport_set_check('NEWTBS');

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> host

[oracle@localhost ~]$ exp file=newtbs.sid transport_tablespace=Y tablespaces=newtbs

Export: Release 10.2.0.1.0 - Production on Sun Aug 12 14:27:36 2012

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

Username: / as sysdba

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
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace NEWTBS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          TBNEW
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@localhost ~]$
[oracle@localhost ~]$

[oracle@localhost ~]$ ls -lrt *.sid
-rw-r--r-- 1 oracle oinstall 16384 Aug 12 14:27 newtbs.sid

COPY THE EXPORT METADATA TO SERVER 2

[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ scp newtbs.sid oracle@192.168.188.128:/u01/app/oracle/oradata/orcl/

The authenticity of host '192.168.188.128 (192.168.188.128)' can't be established.
RSA key fingerprint is df:a9:ca:4d:d7:f6:ee:47:33:fa:77:8f:17:40:7e:0a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.188.128' (RSA) to the list of known hosts.
oracle@192.168.188.128's password:
newtbs.sid                                    100%   16KB  16.0KB/s   00:00

COPY THE DATAFILE TO SERVER 2

[oracle@localhost ~]$ scp /u01/app/oracle/oradata/rcat/new01.dbf oracle@192.168.188.128:/u01/app/oracle/oradata/orcl/
oracle@192.168.188.128's password:
new01.dbf                                     100%   20MB  10.0MB/s   00:02
[oracle@localhost ~]$

__________________________________________________________________________________________________________________________________

ON SERVER 2
__________________________________________________________________________________________________________________________________



[oracle@localhost etc]$ ps -ef | grep pmon
oracle   11832     1  0 14:16 ?        00:00:00 ora_pmon_orcl
oracle   11859 11688  0 14:16 pts/1    00:00:00 grep pmon


[oracle@localhost etc]$ export ORACLE_SID=orcl
[oracle@localhost etc]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 12 14:18:13 2012

Copyright (c) 1982, 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

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
ORCL      READ WRITE

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> show user
USER is "SYS"

SQL> create user newusr identified by oracle;

User created.

SQL>  grant connect , resource  to newusr;

Grant succeeded.

SQL> host

[oracle@localhost etc]$ cd $ORACLE_BASE/oradata/orcl
[oracle@localhost orcl]$ ls -lrt *.sid
total 1088968
-rw-r--r-- 1 oracle oinstall     16384 Aug 12 14:31 newtbs.sid  <--------------------- this is copied from server 1

[oracle@localhost etc]$ cd $ORACLE_BASE/oradata/orcl
[oracle@localhost orcl]$ ls -lrt *.dbf

-rwxrwxrwx 1 oracle oinstall  20979712 Aug 11 10:33 temp01.dbf
-rwxrwxrwx 1 oracle oinstall   5251072 Aug 12 14:17 users01.dbf
-rwxrwxrwx 1 oracle oinstall 104865792 Aug 12 14:17 example01.dbf
-rwxrwxrwx 1 oracle oinstall 503324672 Aug 12 14:31 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug 12 14:31 new01.dbf     <--------------------- this is copied from server 1
-rwxrwxrwx 1 oracle oinstall  31465472 Aug 12 14:32 undotbs01.dbf
-rwxrwxrwx 1 oracle oinstall 251666432 Aug 12 14:33 sysaux01.dbf

[oracle@localhost orcl]$ imp file=newtbs.sid transport_tablespace=Y tablespaces=newtbs datafiles='/u01/app/oracle/oradata/orcl/new01.dbf'

Import: Release 10.2.0.1.0 - Production on Sun Aug 12 14:34:44 2012

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing NEWUSR's objects into NEWUSR
. . importing table                        "TBNEW"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
[oracle@localhost orcl]$
[oracle@localhost orcl]$
[oracle@localhost orcl]$ exit
exit

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
NEWTBS   <--------------------- this tablespace is transported from the server 1 to server 2

7 rows selected.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/new01.dbf

6 rows selected.

SQL> alter tablespace newtbs read write;

Tablespace altered.

SQL> conn newusr/oracle
Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TBNEW                          TABLE

SQL> select * from tbnew;

       ENO ENAME
---------- --------------------
         1 check

SQL>

transport a tablespace (cross platform)

SQL> select * from v$transportable_platform;


PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT
----------- ------------------------------------ --------------
          1 Solaris[tm] OE (32-bit)              Big
          2 Solaris[tm] OE (64-bit)              Big
          7 Microsoft Windows IA (32-bit)        Little
         10 Linux IA (32-bit)                    Little
          6 AIX-Based Systems (64-bit)           Big
          3 HP-UX (64-bit)                       Big
          5 HP Tru64 UNIX                        Little
          4 HP-UX IA (64-bit)                    Big
         11 Linux IA (64-bit)                    Little
         15 HP Open VMS                          Little
          8 Microsoft Windows IA (64-bit)        Little
          9 IBM zSeries Based Linux              Big
         13 Linux 64-bit for AMD                 Little
         16 Apple Mac OS                         Big
         12 Microsoft Windows 64-bit for AMD     Little
         17 Solaris Operating System (x86)       Little

16 rows selected.

In our case we are going to transport a tablespace from HP Itanium 64 bit platform to Linux 32 bit platform. These platforms are made bold the above query output. As you can see the endian format of these platforms are different (HP IA 64 has big endian format and Linux IA 32 has little endian format).

You can check your database server platform using following query.

SQL> select platform_name from v$database;

PLATFORM_NAME

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

HP-UX IA (64-bit)

General requirements for transporting tablespaces between 2 databases

Both platform should have same character set.

you can check the character set of both platform using following query

SQL> select * from nls_database_parameters where parameter like '%SET%';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1
NLS_NCHAR_CHARACTERSET         AL16UTF16



Both database must be using Oracle 8i or higher version, but database version need not be identical

You cannot transport SYSTEM tablespace or any object owned by SYS

If you want to transport partitioned table, all the partitions must be included in transportable table set. If you are transporting index, all the tablespace containing respective tables also needs to be transported

You can transport the tablespace to a target database only if it has same or higher compatibility settting

Following are the brief steps for transporting the tablespace.

1. Ensure that the tablespace is self-contained

You can check if the tablespace you are transporting is self-contained or not using TRANSPORT_SET_PROCEDURE in DBMS_TTS package. This is shown below

DBMS_TTS.TRANSPORT_SET_CHECK (ts_list          IN CLOB, incl_constraints IN BOOLEAN DEFAULT FALSE, full_check       IN BOOLEAN DEFAULT FALSE);

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('IAS_META',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

If you see no rows selected, that mean the tablespace is self-contained.



2. Make the tablespace read-only

Alter the tablepace to make it read-only as shown below.

SQL> alter tablespace IAS_META read only;

Tablespace altered.



You can make the tablespace read write once you export the metadata about this tablespace and convert the endian format of the tablespace (if needed).


3. Export metadata using TRANSPORTABLE_TABLESPACE data pump option


Once you make tablespace as readonly, export the metadata information about the tablespace using data pump export as shown below.


-bash-3.00$ expdp system/welcome1 TRANSPORT_TABLESPACES=IAS_META DUMPFILE=test_dir:test_exp.dmp LOGFILE=test_dir:exp_01.log

Export: Release 10.1.0.5.0 - 64bit Production on Tuesday, 22 December, 2009 17:5

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** TRANSPORT_TABL

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/CLUSTER

Processing object type TRANSPORTABLE_EXPORT/TTE_CLU_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC

Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/TTE_TABLE_OWNER_OBJGRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TRIGGER

Processing object type TRANSPORTABLE_EXPORT/TTE_FUNC_BITMAP_INDEX/TTE_FBM_INDEX_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/TTE_FUNC_BITMAP_INDEX/STATISTICS/TTE_FBM_IND_STATS/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE

Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/RLS_POLICY

Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PROCACT_INSTANCE

Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PROCDEPOBJ

Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

  /slot/ems6024/oracle/test_exp.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:56

4. Convert the datafile to match endian format

You can use RMAN to convert the data file to the endian format of the target. Following is the command for the same

-bash-3.00$ rman TARGET SYS/welcome1

Recovery Manager: Release 10.1.0.5.0 - 64bit Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: HPTSTPW1 (DBID=2764844932)

RMAN> convert tablespace IAS_META to platform 'Linux IA (32-bit)' format '/slot/ems6024/oracle/%U';

Starting backup at 22-DEC-09

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=378 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00009 name=/slot/ems6024/oracle/AS10g/oradata/hptstpw1/ias_meta01.dbf

converted datafile=/slot/ems6024/oracle/data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:10

Finished backup at 22-DEC-09

It generated the file with name "data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6". If you want to preserve the name or give custom name to the datafile, you can use db_file_name_convert parameter while converting the endian format.

5. Copy the file to target database

Here you need to copy both the converted file from step 4. (tablespace datafile) as well as export dump file from step 3.

6. Run import command to import the transportable tablespace

$impdp system/welcome1 TRANSPORT_DATAFILES='/slot/ems5918/oracle/data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6' DUMPFILE=test_dir:test_exp.dmp LOGFILE=test_dir:imp_01.log

You might hit the error "UDI-00011: parameter dumpfile is incompatible with parameter transport_tablespaces" in case you are using TRANSPORT_TABLESPACES parameter in above impdp command. Please DO NOT use TRANSPORT_TABLESPACES parameter in impdp. Refer to official release metalink note ID 444756.1

:-)