Transportable Tablespaces

Transportable Tablespaces


Oracle introduced Transportable Tablespace feature in Oracle 8i to quickly move tablespace across Oracle databases .Oracle suggests Moving data using transportable tablespaces is much faster than performing either an export/import of the same data. This is because the datafiles containing the actual data are copied to the destination location, and one can use an import utility to transfer only the metadata of the tablespace objects to the target database.But Oracle 8i failed to support transportation of tablespaces if the source and target tablespaces are in different block size.Oracle 9i took one step further and overcomes the block restriction by introducing support for multiple block size features in its core technology provided source and target databases should be same platform.Oracle adds platform independency for transporting tablespaces in Oracle 10g.

This article explains basic requirements and implementation of Tranportaion of tablespaces with detail examples and wide explanation on Cross Platform challenges. In my example I transported tablespace USERS from Instance TEST (Windows 2000 Advanced Server-Machine W2000) to Instance PRD(Windows XP SP2-Machine WXP).Both the database versions are 10.1.0.2.0
Let’s see what are all the minimum requirements to transport tablespace from one database to another database .I explained limitations exits only on Oracle 10g.

1)The source and target databases should be same character set and national character set.

2)The tablespace or tablespace sets should be self constrained .That is all the indexes partiotions and other dependents of tables in the tablespace must be inside the tablespace .doing so will lead to containment violations

3) System, undo, sysaux, and temporary tablespaces cannot be transported. Doing so will kick off the following errors.
ORA-29351: can not transport system, sysaux, or temporary tablespace

4) Users with tables in the exported tablespace should exist in the target database prior to initiating the import

Steps for Migration

1) Verify Tablespace Users is self constrained

sqlplus /nolog
SQL>Connect sys@TEST as sysdba
Connected
SQL> BEGIN
SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('USERS');
END;
SQL>/
PL/SQL procedure successfully completed.
SQL>
If any violations occur they reported in Transport_Set_Violations.To make sure just query the table
SQL>Select Violations from TRANSPORT_SET_VIOLATIONS;
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
--------------------------------------------------------------------------------
Index SCOTT.INDX_INVTAB in tablespace USERS points to table SCOTT.TINV in tables
pace INDX

The above information told me the tablespace is not self constrained .so I moved TINV table from INDX tablespace to USERS Tablespace.
SQL> ALTER TABLE SCOTT. TINV MOVE TABLESPACE USERS;
The above command will put the Table called TINV in USERS tablespace.

For Example If you are transporting Tablespaces as set ,just follow the below syntax

SQL> BEGIN
SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('USERS01,USERS02');
END;
SQL>/
PL/SQL procedure successfully completed.

2) Put the Tablespace in read only mode

In order to export meta data for tablespace,the tablespace should be in read only state.but strictly necessary for EXP Utility.To speed up this process checkpoint the system and then issue read only command.Doing so will flush all dirty blocks to disk.Make sure there is no active transactions in the database.Putting the tablespace read only state places tabespace in transactional read-only mode and waits for existing transactions to complete This transitional state does not allow any further write operations to the tablespace except for the rollback of existing transactions that previously modified blocks in the Tablespace

SQL> alter system checheckpoint;
SQL> alter tablespace USERS Read Only;
Tablespace altered.
Make sure tablespace is in read only mode.If the tablespace is not in read only state ,oracle will through following error ORA-29335 Tablespace USERS not read only.
SQL>select status from DBA_TABLESPACES where TABLESPACE_NAME=’USERS’;
STATUS
READ ONLY

Finding out Tablespace Name and Data files associated with Tablespaces

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF
SYSAUX C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBF
UNDOTBS1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF
SYSTEM C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBF
MIG01 C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\MIG01.DBF



3)Exporting Meta Data of Tablespace

Once you performed above checks now it is time to export the meta data for tablespace using Oracle EXP Utility using TRANSPORT_TABLESPACES and TABLESPACES parameters
C:\> EXP TRANSPORT_TABLESPACE=YES TABLESPACES=USERS CONSTRAINTS=N GRANTS=Y TRIGGERS =N FILE=/ORACLE/USRMDATA.DMP OWNER=SCOTT LOG=/ORACLE/USRMDATA.LOG
USERNAME : SYS AS SYSDBA
PASSWORD :****
Below is the out put after successfully executing the above command

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






4)Copying Dump file and Importing metadata into Target Database

Use Operating system copy commands to move Export dump files and datafiles related to tablespaces to the target system .once these files copied just run import on target database.

I copied USRMDATA.DMP and Datafile USERS.DBF into machine WXP. And now trying to plugging the tablespace into Target Database PRD by following way.

IMP TRANSPORT_TABLESPACE=Y DATAFILES=/ORACLE/TEST/USERS.DBF FILE=/ORACLE/DMP/USRMDATA.DMP
USERID : SYS/XXX AS SYSDBA

When the Import is successfully all the selected tablespace data is copied from source to target.
You need to put the tablespace in read write mode
SQL>ALTER TABLESPACE USERS READ WRITE

Check for newly imported tablespace

SQL> select TABLESPACE_NAME,PLUGGED_in from DBA_TABLESPACES;

TABLESPACE_NAME PLUGGED_IN
------------------------------ ---
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
TEMP NO
USERS YES


Now see all the data has been imported from source database

SQL>select count(*) from EMP;

14 ROWS SELECTED

After import the tablespace will be read only mode.Check the tablespace status.and change it read write mode.

SQL> select TABLESPACE_NAME ,STATUS from DBA_TABLESPACES where
TABLESPACE_NAME=’USERS’;


TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY

SQL>alter tablespace USERS read write;

Now check the status

SQL> select TABLESPACE_NAME ,STATUS from DBA_TABLESPACES where
TABLESPACE_NAME=’USERS’;


5)Using Oracle Data Pump Import Utility(IMPDP)

In Oracle 10g You can do all 5 steps in just one go.You can achieve the same process by using oracle data pump Import Utility(IMPDP).Fisrt of all make the tablespace read only and create database link to source DB from target DB and do run the IMPDP from target database.

On target DB just do following steps to make import succcessfull.

SQL>create database link DBLINKTEST using ‘TEST’
Database link created

SQL>create directory DataPump as ‘C:/oracle/datapump’
Directory Created .Grant read and write operations on that directory.
Make the tablespace read only and run IMPDP as follows

> IMPDP DIRECTORY=DATAPUMP TRANSPORT_TABLESPACES=USERS NETWORK_LINK=DBLNKTEST DATA_FILES=’C:/COPIEDFILE/USERS.DBF’
LOG=IMPDP.LOG

Import: Release 10.1.0.2.0 - Production on Thursday, 17 March, 2005 18:39

Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting 'SYS'.'SYS_IMPORT_TRANSPORTABLE_01': system/******** AS SYSDBA directory=datapump transport_tablespaces=USERS transport_datafiles=’C:/COPIEDFILE/USERS.DBF’ network_link= DBLNKTEST logfile=IMPDP.LOG

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Job 'SYS'.'SYS_IMPORT_TRANSPORTABLE_01' successfully completed at 18:40



The advantage of Oracle data pump IMPDP utility over Oracle EXP and IMP utilities is one need not create dump file for meta data extraction. Data Pump Import fetches the necessary metadata from the source across the database link and re-creates it in the target database

6)Cross Platform Challenges

In Oracle 9i and below one can’t transport tablespaces between databases running on different platforms.It means One can’t transport tablespaces between Solaris and HP-UX.But Oracle 10g relaxed this restriction and able to transport tablesp[aces between different OS as long as their OS byte orders are identical.

Let me put some information on byte order which is technically called as Endianness.

The byte order is termed as Endianness which is attribute of the system that indicates whether integers are represented from left to right or right to left Endianness are two types big and little.A Big Endian representation has a multi byte integer written with its most significant byte on the left and Little Endian representation has a multibyte integer written with its most significant byte on the right.so When a big-endian system tries to read data from a little-endian one, a conversion process is required—otherwise, the byte order will lead to an incorrect interpretation of the read data.

In order see the byte order of different Operating systems,just run this query

SQL>select PLATFORM_ID , PLATFORM_NAME, ENDIAN_FORMAT 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

15 rows selected.


The above query will give you some idea on byte order of different operating systems running on 32 bit and 64 bit.

To find out the details of platform of your source or target database just do following way

SQL>select NAME, PLATFORM_ID PLATFORM_NAME from V$DATABASE


NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ---------------------------------------------------------------
TEST 7 Microsoft Windows IA (32-bit)

And on target database

SQL>select NAME, PLATFORM_ID PLATFORM_NAME from V$DATABASE


NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ---------------------------------------------------------------
PRD 7 Microsoft Windows IA (32-bit)


Running EXPDP against source database to extract meta data file

>EXPDP DIRECTORY=DATAPUMP TRANSPORT_TABLESPACE=USERS DUMPFILE=C:\ORACLE\EXPDAT.DMP
USERID SYS/XXXX AS SYSDBA

Export: Release 10.1.0.2.0 - Production on Tuesday, 22 March, 2005 10:49

Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": SYS/********@TEST AS SYSDBA directory=datapump transport_tablespaces=USERS
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
C:\ORACLE\EXPDAT.DMP
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:50

So in this way I created dump file which holds metadata for tablespace USERS.

RMAN TARGET SYSTEM/SYSTEM@TEST
CONNECTED TO TARGET DATABASE :TEST(DBID=1847282363)

Make sure the tablespace under question is in read only mode

RMAN>CONVERT TABLESPACE ‘USERS’ TO PLATFORM =’Linux IA (32-bit)’

DB_FILE_NAME_CONVERT=’C:\ORACLE\DUMP\USERS.DBF’,’C:\ORACLE\DUMP\USERDT’;

A binary file will be created which can be recognized by Target platform OS.

Or You can do the same thing in following way as well

RMAN>CONVERT TABLESPACE USERS TO PLATFORM=’Microsoft Windows IA (32-bit)’
FORMAT=’C:\ORACLE\BKUP\%N_%F’ PARALLELISM=1

Note

The above one I just tested to simulate file conversion and see how it is doing.As I a matter of fact I don’t have to convert as my both platforms same endianness.

Starting backup at 21-MAR-05
Using channel ORA-DISK_1
Channel ORA_Disk_1 : starting datafile conversion
Input datafile fn=00007 name=C:\ORACLE\DATABASE\USERS.DBF
Converted datafile=C:\ORACLE\BKUP\DUMP\USERS_7
Channel ORA_DISK_1=datafile conversion complete ,elapsed time : 00:00:07
Finished backup at 21-MAR-05


Once you got the file converted ,simply copy it to the destination OS and run IMPDP.

C:> IMPDP DIRECTORY=DATAPUMP
TRANSPORT_DATAFILES='C:\ORACLE\DATABASE\BKUP\USER_7' DUMPFILE=EXP_TS.DMP

USERID : SYS/XXX AS SYSDBA

CONNECTED TO ORACLE 10G ENTERPRISE EDITION RELEASE 10.1.0.20. -PRODUCTION
WITH PARTIITON ,OLAP AND DATAMINING OPTIONS

Master table "SYS.SYS_IMPORT_TRANSPORTTABLE_01" successfully loaded/unloaded
starting "sys".SYS_IMPORT_TRANSPORTABLE_01": SYS/SYS AS SYSDBA DIRECTORY=DATAPUMP
TRANSPORT_DATAFILES='C:\ORACLE\BKUP\USER_7' DUMPFILE=EXP_TS.DMP

PROCESSING OBJECT tYPE TRANSPORT_EXPORT_PLUGTS_BLK
PROCESSING OBJECT TYPE TRANSPORT_EXPORT/TABLE
PROCESSING OBJECT TYPE TRANSPORTABLE_EXPORT/TTE_POSINST/PLUGTS_BLK
JOB "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:34

In essence, with transportable tablespaces in 10g you can now make better use of various hardware and OS mixes.

Conclusions

Though Oracle Transportable tablespaces is a nice feature for moving data from one database to another database and only limitation of this process is that the source tablespaces need to be in read only mode while the files being transferred,But In real 24*7 databases it might not be feasible solution For Example in OLTP systems the tables are subject to continuous read /write operations.Hope Oracle will remove this restriction in its fore coming versions.