Transportable tablespaces export and import is manageable across platforms and only Meta data will be exported. In Cross platform transportable tablespace the data movement is simpler and faster.
This mode requires that you have the EXP_FULL_DATABASE role.
Please note that
1. source and target database must use the same character set/national character set
2. You cannot transport a tablespace to a target database which already exists.
3. Transportable tablespace exports cannot be restarted once stopped
4. Target database must at same or higher release level as the source database.
Transportable tablespace export and import on same endian platforms
Step 1: Find the Operating system byte order on Source and Target Database
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
12 Microsoft Windows 64-bit for AMD Little
For example, if you want to transport a tablespace “test_user_tbs” from a Linux 64 bit(Little endian) machine TESTLINUX to Microsoft Windows 64 bit (Little endian) machine TESTWIN. Both the source and target platforms are of LITTLE endian type. The data file for the tablespace “test_user_tbs” is “test_user_tbs01.dbff.
Step 2:- Make the tablespace “READ ONLY”
SQL> alter tablespace test_user_tbs read only;
Step 3: Export metadata
(i) Using export utility$ exp testuser/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp log=exp_test_user_tbs.log
(ii) Using Export data pump utility$ expdp system/password TRANSPORT_TABLESPACES=test_user_tbs TRANSPORT_FULL_CHECK=Y DIRECTORY=export_dir DUMPFILE=expdp_test_user_tbs.dmp logfile= expdp_test_user_tbs.log
TRANSPORT_FULL_CHECK= Y Specifies that check for dependencies between those objects inside the transportable Tablespace and those outside the transportable Tablespace.
The file “exp_test_user_tbs.dmp” or ““expdp_test_user_tbs.dmp” contains only metadata.
Step 4: Copy the files to Target system
If you are using FTP use binary option.
Step 5: Initiate Import to plug the tablespace into the database.
(i) Using import utility $ imp test_user/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp datafiles='test_user_tbs01.dbf' log=imp_test_user_tbs.log
(ii) Using impdp utility
Click here to see the Instructions to create Directory and grant privileges.
$ impdp test_user/test123 TRANSPORT_DATAFILES='test_user_tbs01.dbf' DIRECTORY=import_dir DUMPFILE=expdp_test_user_tbs.dmp log= impdp_test_user_tbs.log
You can use REMAP_SCHEMA= (source: target), if you want to import into another schema.
Step6: - Put the tablespace in read/write mode.
SQL> ALTER TABLESPACE TEST_USER_TBS READ WRITE;
Now the database has a tablespace named “test_user_tbs” and the objects of the tablespace will be available.
Transport Tablespace Import Common Errors:-
1. Oracle Error : EXP-00044: must be connected "AS SYSDBA" to do Point-in-time Recovery or Transportable Tablespace import
Cause: The user must log in "as SYSDBA" to perform transportable tablespace imports or Point-In-Time Recovery imports.
Action: Ask your database administrator to perform the Transportable Tablespace import or
the Tablespace Point-in-time Recovery import.
2. IMP-00017: following statement failed with ORACLE error
19721:IMP-00003: ORACLE error 19721 encountered
ORA-06512: at "SYS.DBMS_PLUGTS", line 2065
ORA-06512: at line 1
Cause: A duplicated data file name in the import parameters file was causing the issue
Action: Modify the import parameters file with the right datafile name
Please see the Data pump Export/Import related documents:
Click here for Data Pump Export modes and Interfaces.
Click here for Data Pump Export/Import with Examples.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/
Subscribe to:
Post Comments (Atom)
Oracle's transportable tablespace feature allows users to quickly move a user tablespace across Oracle databases. It is the most efficient way to move bulk data between databases. Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data. This is because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information.nbsp; You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.
ReplyDelete16gb micro sd karte
great this will help in my next serve migration.
ReplyDeleteThanks for this nice blog.
Great job for this article. I am in the middle to moving data between servers (production and cloned databases). I will like to use this option, but how can I over this condition?
ReplyDelete"You cannot transport a tablespace to a target database which already exists".
Either you can rename the tablespace to be transported or destination tablespace before the transport operation.
ReplyDelete"Satishbabu Gunukula said...
ReplyDeleteEither you can rename the tablespace to be transported or destination tablespace before the transport operation."
Using the REMAP? If we just want to use IMPDP TTS, and rename it from TBSX to TBSY...syntax?
Use the below syntax:-
ReplyDeleteimpdp username/pwd DIRECTORY=directory_name DUMPFILE=test_dmp.dmp logfile=test_log.log
SCHEMAS=ABC REMAP_SCHEMA=ABC:XYZ REMAP_TABLESPACE=ABC:XYZ
But please note that user default tablespace on target should be XYZ.
loved the post. thanks.
ReplyDelete72nd Happy Independence Day India Greetings 2018