Thursday, October 1, 2009

Cross Platform Transportable Tablespace using RMAN

When transporting tablespaces between databases where the endian format is different between source and destination platforms, the endian format of the datafiles must be converted to match the destination platform.

This conversion can be performed in two ways

1. Using RMAN CONVERT TABLESPACE command (converting on the source host)
2. Using RMAN CONVERT DATAFILE command(converting on the destination host)

Transportable tablespaces export 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 the
1. source and target database must use the same character/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.

For example, if you want to transport a tablespace “test_user_tbs” from a Linux 64 bit (Little Endian) machine TESTLINUX to Solaris 64 bit (Big Endian) machine TESTSOLARIS. Both the source and target platforms are of different endian type. The data file for the tablespace “test_user_tbs” is “test_user_tbs01.dbf.

Follow the below steps

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

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: Different endian formats and convert using RMAN
The endian formats are different and then a conversion is necessary for transporting the tablespace.

RMAN Cross platform transportable tablespace.

Run the below command to covert the tablespace to Source (Linux 64 bit) to Target (Solaris 64 bit) platform.

Method 1: Using CONVERT Tablespace... FROM PLATFORM on Source host
RMAN> convert tablespace test_user_tbs
2> to platform ‘Solaris[tm] OE (64-bit)'
3> format='/oradata/rman_backups/%N_%f';

The data file “test_user_tbs01.dbf” is not touched and a new file will be created for Solaris platform under “/oradata/rman_backups“and copy the file to Target platform.

Method 2: Using CONVERT DATAFILE... FROM PLATFORM on Destination host
RMAN> convert datafile test_user_tbs01.dbf
2> from platform ‘Linux IA (64-bit)'
3> db_file_name_convert ‘/linux/oradata/’ ‘/solaris/oradata’

Use RMAN's CONVERT command to convert the datafiles to be transported to the destination host's format. The converted datafiles are stored in “/solaris/oradata”.

Step 5: Initiate import command 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
$ 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.

Step 6: 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.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

12 comments:

  1. Excellent post very useful

    http://chandu208.blogspot.com/
    Regards,
    Chandra

    ReplyDelete
  2. you may also like to download one of the best and enhanced stuff with you can upgrade your skills download spectrasonics bundle on this site

    ReplyDelete
  3. I have seen that you have very many economics assignment help professors on your website. I am looking for a Canadian native for my Hide me vpn cracked Download and Silhouette Studio cracked Downloadand sewart cracked Downloadfl studio cracked DownloadIf it is possible to get one then let me know the cost of hiring such an economics homework help expert. Having a tutor from my country would be good because he/she would understand our system better. Wow, cool post.

    ReplyDelete
  4. Perfect Uninstaller crack download is a widely used tool that allows you to clean up unwanted applications.
    https://maccracked.com/perfect-uninstaller-crack/

    DmitriRender Crack is an efficient video player application that helps you to converts your video frame rate in real-time with Graphics processing unit (GPU) oriented algorithms to ensure playback quality.
    https://cracks4soft.com/dmitrirender-crack/

    PrimoCache crack is a program that improves operating system efficiency and responds to user requests by using advanced cached data rules.
    https://hdlicensed.com/primocache-crack/

    CommView for WiFi Crack is a powerful social networking utility created for taking & examining box information on Wi-Fi systems.
    https://windowscrack.net/commview-for-wifi-crack/

    ReplyDelete
  5. cafecrack UltraMon Crack is an easy and simple tool. With this application, users can control the graphics card. Besides that, it also shows hardware information. There are many functions in this application. Due to this congestion, the useful life and lifespan of the card are increased.fitpcgames

    ReplyDelete
  6. You no longer need to adjust and edit your clicks before importing them, just upload them and start right away. KICK 2 Keygen So you can find the Kick 2 download link at the end of this post.Ez Drummer Keygen Select the velocity or note you want to export and drag it to your Audio Workstation.vysor pro license keyYou should definitely try this audio creation tool, the KICK license code you can save to your bank. arabian oud perfume You can also use our Portamento control to quickly and easily create. Drag and drop audio clicks directly best sony a7iii monitor

    ReplyDelete
  7. studio 3t Key Studio 3T Crack is one of the best and most powerful tools construct 2 Full Version that can be integrated with the MongoDB database and allows you all the essential toolsdu meter Patch to easily control the MongoDB database. This application provides a cross-platform to manage your task and provides relevant tools for connecting anyrail License Key MongoDB database servers

    ReplyDelete