LATEST TOPICS

Migrate Oracle database using Transportable Tablespace

I have been working with Oracle Databases since last 6 years. However, surprisingly I never had the opportunity to use the Transportable Tablespace option of migrating data from one Oracle database to another.

Recently, I had the opportunity of using transportable tablespace method for migrating Oracle Database from one platform to another platform and I thought it would be a good idea to share my experience with a short blog post. So, here I am.

Before I start, I would like to present a quick overview as to what Transportable Tablespace is all about.

What is Transportable Tablespace?

Transportable Tablespace feature was first introduced with Oracle database release 8i. This feature is used to copy a set of tablespaces from one Oracle database to another. It can be used to transport tablespaces across different database platforms as well.

How it Works?

To copy tablespaces from one database to another using transportable tablespace, the source tablespaces are first kept in READ-ONLY mode (to ensure data consistency). Once the tablespaces are in READ-ONLY mode, the actual datafiles belonging to the source tablespaces are copied from source database to target database (using any available methods like scp, sftp, rcp, etc).

Once the datafiles are copied from source to target, the tablespace metadata is exported (using traditional or datapump export utility) from source database and imported (using traditional or datapump import utility) into the target database. We can take out the source tablespaces from READ-ONLY mode upon completion of datafile copy and metadata export. Once the metadata is imported on target database, the tablespaces on the target database can be taken out of READ-ONLY mode. We are done with migration at this point and the tablespaces are accessible on the target database.

One point to note here is that, Transportable tablespace copies only the database objects contained within the transported tablespace (like tables, indexes, procedures, functions, packages, etc.). However, it will not copy objects like USERS, ROLES, SEQUENCES, etc as these objects are contained in SYSTEM tablespace and owned by SYS user. We need to separately copy the users, roles, sequences and any other objects which belongs to the SYS user or SYSTEM tablespace and has reference to tablespaces being transported.

Minimum Requirement

To be able to use transportable tablespaces method, there is a list of minimum requirements that needs to be satisfied by the database as well as the tablespaces to be transported.

Following is the list of minimum compatibility requirements for transportable tablespace to work.

Transport Scenario Source Database Target Database
Databases on the same platform 8.0 8.0
Tablespace with different database block size than the target database 9.0 9.0
Databases on different platforms 10.0 10.0

Apart from these requirements, the tablespaces to be transported must be self-contained. A tablespace is considered to be a self-contained tablespace, when there is no object in the tablespace that references objects outside that tablespace or is referenced by an object from outside of that tablespace.

Simulation Setup

I would like to provide a simple simulation on the steps involved in the transportable tablespace method to have a better understanding.

Lets say, I have all the business data/objects stored in the tablespace called APPDATA in my source database orlbcp01 located on a Linux server mybcp.oraclebuffer.com. As part of migration (from 11gR2 to 12cR1), I have been asked to migrate all the data/objects from this source database to a new target database orlsol located on a Solaris server mysolaris.oraclebuffer.com.

Based on this input, I have the following environments

Source Environment

Server mybcp.oraclebuffer.com
Oracle SID orlbcp01
Version 11.2.0.1
Platform Linux x86-64

Target Environment

Server mysolaris.oraclebuffer.com
Oracle SID orlsol
Version 12.1.0.2
Platform Solaris 10 (x86-64)

Given this setup, I can not utilize RMAN due to the version mismatch. We have the option of using Export/Import, However; if the source database is huge in size that is a time consuming and tedious task for a DBA. Transportable Tablespace is a great alternative in such scenarios when the source database to be migrated is huge in size and there is no alternatives available other than Export/Import.

Step By Step Process for Transporting Tablespaces

Let me demonstrate the transportable tablespace method of migrating database data/objects against the above simulated environment. We need to use the following listed steps for migrating data/objects from a source to target database using transportable tablespace method.

Check Platform Endian Format (Optional)

If we are doing cross platform transportable tablespace, this step is required to determine if the source and target database has the same ENDIAN format or not. If source and target have different ENDIAN format, we need to convert the underlying datafiles to the target database ENDIAN format.

Use the following query to check the source/target database ENDIAN format as well as Platform.

-------
------- Source Database (Check Endian Format)
SQL> select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT
  2  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                 ENDIAN_FORMAT
--------- --------------------------------------------- --------------
ORLBCP01  Linux x86 64-bit                              Little


-------
------- Target Database (Check Endian Format)
SQL> select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT
  2  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                 ENDIAN_FORMAT
--------- --------------------------------------------- --------------
ORLSOL    Solaris Operating System (x86-64)             Little

In our case, we have the same ENDIAN format in both source and target database. Therefore, we need not convert the datafiles ENDIAN format. However, if required we have the option of converting the datafiles ENDIAN format either at the source or target database server. Please read on to find out.

Tablespace self-contained check

For the transportable tablespace to work, we need to have the source tablespace to be self-contained. We can use the TRANSPORT_SET_CHECK procedure from the DBMS_TTS package to determine if the tablespace is self-contained or not.

The procedure has the following syntax.

----------
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(TS_LIST => '[tablespace_name_1],[tablespace_name_2]....[tablespace_name_n]' , INCL_CONSTRAINTS => TRUE/FALSE);
---------
--------- Where
--------- TS_LIST : Comma separated list of tablespaces to be transported
--------- INCL_CONSTRAINTS : Whether to take into consideration the referential integrity constraints while checking for self-contained property.

After executing this procedure to check self-contained property, we can query the TRANSPORT_SET_VIOLATIONS view to see if there are any violations reported by this procedure.

Lets check, if our source tablespace APPDATA is self-contained or not.

---------
--------- Source Database
--------- Checking self-contained property of tablespace
SQL> exec dbms_tts.transport_set_check('APPDATA',TRUE);

PL/SQL procedure successfully completed.

Lets see if there are any violations reported by this procedure.

---------
--------- Source DATABASE
--------- Check violations report
SQL> select * from transport_set_violations;

VIOLATIONS
-------------------------------------------------------------------------------------
ORA-39908: Index MYAPP.TEST_COUNT_PK in tablespace USERS enforces primary constraints
  of table MYAPP.TEST_COUNT in tablespace APPDATA.

As per the report, we have a violation reported while checking for the self-contained property of the tablespace APPDATA. We have an index MYAPP.TEST_COUNT_PK which is in USERS tablespace which is enforcing primary constraint on the table MYAPP.TEST_COUNT located in tablespace APPDATA.

We need to address any violations reported here, before we can proceed any further. To fix the above reported violation, I can move the index MYAPP.TEST_COUNT_PK to tablespace APPDATA as shown below.

SQL> alter index MYAPP.TEST_COUNT_PK rebuild tablespace APPDATA online;

Index altered.

Lets again check the self-contained property of tablespace APPDATA to see if we have any more violations.

---------
--------- Source Database
--------- Checking self-contained property of tablespace
SQL> exec dbms_tts.transport_set_check('APPDATA',TRUE);

PL/SQL procedure successfully completed.

---------
--------- Source DATABASE
--------- Check violations report
SQL> select * from transport_set_violations;

no rows selected

We have no more violations reported for self-contained property of APPDATA tablespace. This tablespace can be now transported to target database.

Keep Source Tablespace in READ-ONLY mode

Since our tablespace APPDATA has passed the self-contained check, we are now ready to transport the tablespace. To start with the transport process, we need to put the tablespace APPDATA in READ-ONLY mode.

-------- 
-------- Source Database
-------- Put tablespace in READ-ONLY mode
SQL> alter tablespace APPDATA READ ONLY;

Tablespace altered.

-------
------- Confirm the tablespace status
SQL>  select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME='APPDATA';

TABLESPACE_NAME                STATUS
------------------------------ ---------
APPDATA                        READ ONLY

Generate Metadata export of Transportable Tablespace

Once the tablespace is kept in READ-ONLY mode, we need to generate the metadata export of the tablespaces that needs to transported using the DataPump export utility. To generate the metadata export, we would be using TRANSPORT_TABLESPACES clause of the DataPump Export utility to specify the list of tablespaces that needs to transported and for which we need the tablespace metadata.

The syntax for generating metadata export using DataPump Export utility is as follows

---------- EXPDP Transport Tablespace Syntax
----------
expdp username/password directory=[DIRECTORY_OBJECT] dumpfile=[export_dump_filename] logfile=[export_log_filename]
	TRANSPORT_TABLESPACES=[tablespace_name_1],[tablespace_name_2]....[tablespace_name_n] TRANSPORT_FULL_CHECK=Y/N
	
----- Where
----- TRANSPORT_TABLESPACES: Comma separated list of tablespaces to be transported
----- TRANSPORT_FULL_CHECK : To strictly check the self-contained property while generating metadata. Default is N.

Lets, generate the metadata export for our source tablespace APPDATA using the DataPump Export utility.

## Source Database
## Generate metadata export of tablespace APPDATA
[oracle@mybcp ~]$ expdp directory=DATA_PUMP_DIR dumpfile=tbs_appdata_meta.dmp logfile=tbs_appdata_meta.log TRANSPORT_TABLESPACES=APPDATA TRANSPORT_FULL_CHECK=Y

Export: Release 11.2.0.1.0 - Production on Mon Jun 29 00:36:11 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=tbs_appdata_meta.dmp logfile=tbs_appdata_meta.log TRANSPORT_TABLESPACES=APPDATA TRANSPORT_FULL_CHECK=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/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/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /data/backup/exp/tbs_appdata_meta.dmp
******************************************************************************
Datafiles required for transportable tablespace APPDATA:
  /data/orlbcp01/appdata01
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 00:36:49

Copy Datafiles and Export Dump File to Target Server

Once the metadata export is generated on the source database for all the tablespaces that needs to be transported, we need to copy the Export Dump file as well as all the datafiles belonging to the tablespaces to be transported to the target database server.

We can get the list of datafiles to be copied from the Export (EXPDP) output log or we can even query the source database to find the list of datafiles to be copied.

From Export log:

###########
########### List of datafiles to be copied to target (from Export output)
Datafiles required for transportable tablespace APPDATA:
  /data/orlbcp01/appdata01
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 00:36:49

From Database:

------------- Source DATABASE
------------- Find the List of datafiles to be copied to target database
SQL> select file_name from dba_data_files
  2  where tablespace_name='APPDATA';

FILE_NAME
--------------------------------------------------------------------------------
/data/orlbcp01/appdata01

Lets copy the datafiles as well as the Export dumpfile onto the target database server mysolaris.oraclebuffer.com. We can use any available method like scp, sftp , rcp, etc to copy the files to target server. I am using the scp tool to copy the files in my simulation.

#####
##### Copy Datafiles to target server
[oracle@mybcp ~]$ scp /data/orlbcp01/appdata01 oracle@mysolaris:/data/orlsol/orlsol/
Password:
appdata01                                                                                                                          100% 5120MB   8.4MB/s   10:07

#####
##### Copy Export Dump file to target server
[oracle@mybcp ~]$ scp /data/backup/exp/tbs_appdata_meta.dmp oracle@mysolaris:/data/orlsol/export
Password:
tbs_appdata_meta.dmp                                                                                                               100%  208KB 208.0KB/s   00:00

Lets validate if the datafile and Export dump file is copied on the target server.

#######
####### Validate the files are copied on target server
bash-3.2$ hostname
mysolaris
bash-3.2$ ls -lrt /data/orlsol/orlsol/appdata01
-rw-r-----   1 oracle   dba      5368717312 Jun 29 00:56 /data/orlsol/orlsol/appdata01
bash-3.2$ ls -lrt /data/orlsol/export/tbs_appdata_meta.dmp
-rw-r-----   1 oracle   dba       212992 Jun 29 00:58 /data/orlsol/export/tbs_appdata_meta.dmp

Take out Source Tablespace from READ-ONLY mode

Once the datafiles are copied to the target database, we no longer the source tablespaces to be in READ-ONLY mode. We can take out the tablespace from READ-ONLY mode on source database at this point.

Lets take our tablespace APPDATA out of READ-ONLY mode in source database orlbcp01

--------- Source Database
--------- Check the status of tablespace
SQL> select d.name Database, t.tablespace_name,df.file_name,t.status Tablespace_status,df.status File_status
  2  from v$database d, dba_tablespaces t, dba_data_files df
  3  where t.tablespace_name=df.tablespace_name and t.tablespace_name='APPDATA';

DATABASE  TABLESPACE_NAME      FILE_NAME                                TABLESPAC FILE_STAT
--------- -------------------- ---------------------------------------- --------- ---------
ORLBCP01  APPDATA              /data/orlbcp01/appdata01                 READ ONLY AVAILABLE


-------- Take the tablespace out of READ-ONLY mode
--------
SQL> alter tablespace APPDATA READ WRITE;

Tablespace altered.

-------- Validate the tablespace is in READ-WRITE mode
--------
SQL> select d.name Database, t.tablespace_name,df.file_name,t.status Tablespace_status,df.status File_status
  2  from v$database d, dba_tablespaces t, dba_data_files df
  3  where t.tablespace_name=df.tablespace_name and t.tablespace_name='APPDATA';

DATABASE  TABLESPACE_NAME      FILE_NAME                                TABLESPAC FILE_STAT
--------- -------------------- ---------------------------------------- --------- ---------
ORLBCP01  APPDATA              /data/orlbcp01/appdata01                 ONLINE    AVAILABLE

Convert datafile on Target server (Optional)

If we are transporting the tablespaces across platforms with different ENDIAN format, we need to convert the datafiles which were copied as part of the transportable tablespace method to the Target server ENDIAN format.

We can use RMAN to convert the datafiles to the appropriate ENDIAN format using the following syntax.

---------- Target Database
---------- Convert datafile endian format on target side.
rman target /
RMAN> CONVERT DATAFILE '[datafile_name_1]','[datafile_name_2]',...[datafile_name_n]
TO PLATFORM="Target_Platform" FROM PLATFORM="Source_Platform"

------ Where
------ '[datafile_name_1]',...[datafile_name_n] : Comma separated list of datafiles to be converted to desired ENDIAN format
------- "Target_Platform" 						: Target server platform name. Can be queried from V$TRANSPORTABLE_PLATFORM view
------- "Source_Platform" 						: Source server platform name. Can be queried from V$TRANSPORTABLE_PLATFORM view

You can refer here for the complete syntax of CONVERT command.

We have the option to either convert the datafiles on the source server before copying to target server or convert the target datafiles once copied from the source server.

I do not have to convert the datafiles in case of my simulation as I have the same ENDIAN format for source and target database server.

Create Schema and Roles on Target Database

Before we can import the tablespace metadata on the target database, we need to make sure that the Schemas owning the objects within those tablespaces exist on the target database. We can optionally use REMAP_SCHEMA while importing the metadata to map all the objects from the transported tablespaces to existing Schemas on the target database. However, if the REMAP_SCHEMA is not used, we must create the Schema of the tablespace objects on the target database.

If a Schema is not created or REMAP_SCHEMA is not used, an attempt to import the tablespace metadata would fail with errors similar to the following example

ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user MYAPP does not exist in the database

We can easily find the Schemas/Roles that needs to be created on the target side by querying the source database as follows.

-------- Source Database
-------- Find the list of Schemas to be created on targe database
SQL> select distinct owner from dba_segments where tablespace_name in ('TRANSPORTED_TABLESPACE_NAME_LIST');


------- Source Database
------- Find the list of Roles to be created on target database
SQL> select distinct granted_role from dba_role_privs  where grantee in
  2  (
  3  select distinct owner from dba_segments where tablespace_name in ('TRANSPORTED_TABLESPACE_NAME_LIST');
  4  );
  
--------- Where
--------- TRANSPORTED_TABLESPACE_NAME_LIST: Comma separated list of transported tablespaces.  

Lets see what are the Schemas and Roles we need to create on target database orlsol for our trasported tablespace APPDATA.

----- Schemas to be created on target database orlsol
------
SQL> select distinct owner from dba_segments where tablespace_name in ('APPDATA');

OWNER
------------------------------
MYAPP


----- Roles to be created on target database orlsol
-----
SQL> select distinct granted_role from dba_role_privs  where grantee in
  2  (
  3  select distinct owner from dba_segments where tablespace_name in ('APPDATA')
  4  );

GRANTED_ROLE
------------------------------
DBA
RESOURCE
CONNECT

So we need to create the Schema called MYAPP on the target database. We need not create roles listed here as these are default roles available with the database.

We can generate the SQL for Schema creation from the source database as follows

----------- Source Database
----------- Generate SQL Schema creation on target database
SQL> select distinct 'create user ' ||u.username||' identified by values '''||p.password||'''
  2  default tablespace '||u.default_tablespace||' temporary tablespace '||u.TEMPORARY_TABLESPACE||';'
  3  from dba_users u, sys.user$ p
  4  where u.username=p.name and u.username in
  5  (
  6  select distinct owner from dba_segments where tablespace_name in ('APPDATA')
  7  );

'CREATEUSER'||U.USERNAME||'IDENTIFIEDBYVALUES'''||P.PASSWORD||'''DEFAULTTABLESPACE'||U.DEFAULT_TABLESPACE||'TEMPORARYTABLESPACE'||U.TEMPORARY_TABLESPACE||';'
-------------------------------------------------------------------------------------------------------------------------------------------------------------
create user MYAPP identified by values 'C050EF9228761727'
default tablespace APPDATA temporary tablespace TEMPTS1;

-------- Where
------- APPDATA : Name of the transported tablespace. Can be replaced accordingly.

Lets create the Schema called MYAPP on our target database orlsol.

Note: While creating the schemas on target database, we need to create the schema with a available default and temporary tablespace. We may later change the default tablespace once the tablespace metadata is imported in the next step.

-------- Target DATABASE
-------- Create the required Schema for metdata import
-------- Will adjust default tablespace later once the metadata import is completed.
SQL> create user MYAPP identified by values 'C050EF9228761727';

User created.

Import Transportable Tablespace metdata on Target Database

We are now ready to perform the metdata import for the transported tablespaces on the target database. We would be using the Export dump file generated in the earlier step and which was copied on the target server.

To perform the metadata import, we would be using the DataPump Import (IMPDP) utility. We will make use of TRANSPORT_DATAFILES clause from the Import utility to import all the metdata for the transported tablespaces.

The syntax for performing the metadata import is as follows

---------
--------- Target Database Server
--------- Syntax for metadata import of transported tablespaces
impdp username/password directory=[DIRECTORY_OBJECT] dumpfile=[export_dump_filename] logfile=[import_log_file_name]
TRANSPORT_DATAFILES=[datafile_name_1],[datafile_name_2]....[datafile_name_3]
REMAP_SCHEMA={Source_Schem}:{Target:Schema}

------- Where
------- export_dump_filename : Name of the export dump file generated during metadata export and copied to the DIRECTORY_OBJECT on the target server.
------- TRANSPORT_DATAFILES	 : Comma separated list of all the datafiles (including path) that were copied from source to target for transported tablespaces.
------- REMAP_SCHEMA 		 : If we want remap the Source Schema to a different Target Schema

Lets import the metadata for our transported tablespace APPDATA into the target database orlsol

bash-3.2$ impdp directory=DATA_PUMP_DIR dumpfile=tbs_appdata_meta.dmp logfile=imptbs_appdata_meta.log TRANSPORT_DATAFILES='/data/orlsol/orlsol/appdata01'

Import: Release 12.1.0.2.0 - Production on Mon Jun 29 02:14:51 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone version is 11 and target time zone version is 18.
Source time zone is +05:30 and target time zone is +00:00.
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=tbs_appdata_meta.dmp logfile=imptbs_appdata_meta.log TRANSPORT_DATAFILES=/data/orlsol/orlsol/appdata01
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/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/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Jun 29 02:15:18 2015 elapsed 0 00:00:19

We have now successfully transported our tablespace APPDATA to the target database orlsol

Take out Target Tablespace from READ-ONLY mode

Since the Metadata export was generated by keeping the source tablespace in READ-ONLY mode, it would get reflected on the target database once the metadata is imported. We need to take out the transported tablespaces on the target database out of READ-ONLY mode once the metadata import is completed successfully.

Lets take our trasported tablespace APPDATA out of READ-ONLY mode in target database orlsol

----------- Target Database
----------- Check the status of Transported tablespace
SQL> select d.name Database, t.tablespace_name,df.file_name,t.status Tablespace_status,df.status File_status
  2  from v$database d, dba_tablespaces t, dba_data_files df
  3  where t.tablespace_name=df.tablespace_name and t.tablespace_name='APPDATA';

DATABASE  TABLESPACE_NAME      FILE_NAME                                TABLESPAC FILE_STAT
--------- -------------------- ---------------------------------------- --------- ---------
ORLSOL    APPDATA              /data/orlsol/orlsol/appdata01            READ ONLY AVAILABLE

---------- Take the tablespace out of READ-ONLY mode
----------
SQL> alter tablespace APPDATA READ WRITE;

Tablespace altered.

---------
--------- Validate the transported tablespace is now accessible
SQL> select d.name Database, t.tablespace_name,df.file_name,t.status Tablespace_status,df.status File_status
  2  from v$database d, dba_tablespaces t, dba_data_files df
  3  where t.tablespace_name=df.tablespace_name and t.tablespace_name='APPDATA';

DATABASE  TABLESPACE_NAME      FILE_NAME                                TABLESPAC FILE_STAT
--------- -------------------- ---------------------------------------- --------- ---------
ORLSOL    APPDATA              /data/orlsol/orlsol/appdata01            ONLINE    AVAILABLE

With the completion of this step, we are done with the migration/transport of tablespace from source to target database. However, we still need to manually export and import the system objects like functions, procedures, triggers, etc as those objects do not get transported using the transportable tablespace method. To do that, we can perform a metadata only export/import of the schemas (which has objects belonging to the transported tablespaces) by exluding the objects (like tables, indexes, LOBS, etc) which are already transported.

Conclusion

Transportable Tablespace is very simple and easy way of migrating large Schemas across Oracle databases. With transportable tablespace, the time required to complete the migration/transport is the actual time required to copy the datafiles from source to target as the metdata export/import takes a neglible amount of time. If we have high network bandwidth available between source and target database server, then transportable tablespace would be the preferred option over the generic Export/Import method. Further, we need not be bothered about the Redo generation and monitoring the target database archive destination which is a routine task while importing large schemas using generic export/import method.

3 Comments
  1. Thirumalai
  2. Vikas Singh
  3. Rajesh Pachiyappan
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed