Transportable tablespace from Linux to Windows

Transportable tablespace :-

  • Its one of the feature in Oracle Database Enterprise Edition.
  • Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases. Starting with Oracle Database 10g, we can transport  tablespaces across platforms . Furthermore, like import and export, transportable tablespaces provide a mechanism for transporting metadata in addition to transporting data.
  • Transportable tablespaces and transportable tables only transports data that resides in user-defined tablespaces. However, full transportable export/import transports data that resides in both user-defined and administrative tablespaces, such as SYSTEM and SYSAUX.
  • Full transportable export/import transports metadata for objects contained within the user-defined tablespaces and both the metadata and data for user-defined objects contained within the administrative tablespaces. Specifically, with full transportable export/import, the export dump file includes only the metadata for objects contained within the user-defined tablespaces, but it includes both the metadata and the data for user-defined objects contained within the administrative tablespaces.

Limitations on Transportable Tablespace Use :

  • The source and target database must use the same character set and national character set.
  • You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
  • Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
  • If the owner/s of tablespace objects does not exist on target database, the usernames need to be created manually before starting the transportable tablespace import.
  • You cannot transport the SYSTEM tablespace or objects owned by the user SYS.
  • Advanced Queues Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.
  • Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump but not the original export utility, EXP.

If we are migrating a database, then make sure there are no invalid objects in the source database before making the export.If invalid exists in source database,remove all the invalid objects by running utlrp.sql.

Cross Platform Transport tablespace from Linux to Windows :

Source Database :  Demo (Db name) 

Steps for Transporting the tablespace :-

Checking what are all the tablespaces available then Create a new  Tablespace which is only am going to do transportation.

I am creating a new table which named as SAMPLE and i am checking the count in that table which i create in the new tablespace as i created before.

  • Make Tablespace TEST1 in READ ONLY mode.
  • Check the tablespace will be self contained


These violations must be resolved before the tablespaces can be transported.

If there is any object dependencies or referenced objects,violation occurs during exporting tablespace.

If any dependencies are present,move the objects to the target tablespace which is going to transportable.

The transport_set_violations view output has to display empty rows.

RMAN convert Usage :-

  • If you see that the endian formats are different for platform and then a conversion using RMAN is necessary for transporting the tablespace set:                
  • For example, run the below command to convert the tablespace of  Source(Linux 64 bit) platform to Target(Microsoft Windows 64 bit) ,

Use CONVERT  Tablespace   FROM  PLATFORM  on  Source  Database

RMAN> convert tablespace TEST1 to platform="Microsoft Windows X86 64-bit" FORMAT '/home/oracle/%U';
  • After copy the datafiles as well as the export dump file to the target environment.

Use CONVERT DATAFILE FROM PLATFORM on Target Database

RMAN> CONVERT DATAFILE
'/u01/app/oracle/oradata/Demo/datafile/tbs.dbf',
FROM PLATFORM="Linux x86 64-bit"
TO PLATFORM="Windows TRu64 LINUX"
DB_FILE_NAME_CONVERT= "/home/oracle/", "/u01/app/oracle/oradata/Demo/datafile"
PARALLELISM=4;

If they are of the same endian format, then no conversion using RMAN is necessary and tablespaces can be transported as if they were on the same platform.

Check Platform Support and Endian format :

Here both of my platform are Little Endian format.

  • Export  TEST1 tablespace  metadata.

 

Target Database :  ORCL (Db name) –Windows Platform

 

  • Copy datafile and  dumpfile to target database.

Import transportable tablespace in TARGET Database :

  • Before importing the metadata in target database,check SAMPLE table is already present in target database.
  • Import the dumpfile and copy of datafile in target database.

  • Make the tablespace TEST1 in READ WRITE mode.
  • Check the table sample data count.

Thus the SAMPLE table got transported from Linux platform to Windows platform successfully.

Catch Me On:- Hariprasath Rajaram 

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/             FB Page :https://www.facebook.com/dbahariprasath/?                 Twitter :https://twitter.com/hariprasathdba

 

 

Oracle Database 18.3.0 Installation On Premises Windows

Description:-

In this article we are going to see On-Premise Oracle Database 18c (18.3) for Windows server 2012 R2  installation.

Click here to download Oracle 18c installation software.

Step 1:- Click create and configure a single instance database

Step 2:- Click Server class

Step 3:- Click the type of installation

Step 4:- Select the Database edition you want to install

Step 5:- Use a virtual account 

Step 6:- Provide the base directory.

Step 7:- select the database type as General Purpose

Step 8:- Provide Global database name and system identifier.

Step 9:- Check Memory and Character sets.

Step 10:- Select the storage location for datafiles

Step 11:- If you need EM control,specify the below details otherwise uncheck it. 

Step 12:- If you need database is in archivelog mode,Enable Recovery option.

Step 13:- Provide passwords for SYS and system user

Step 14:- Check the Summary window and Click Install

Step 15:- Finally click the close button

Step 16:- Check the database name and status.

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:                  https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:                 https://www.facebook.com/HariPrasathdba
Facebook Group:   https://www.facebook.com/groups/894402327369506/
Facebook Page:      https://www.facebook.com/dbahariprasath/?
Twitter:                     https://twitter.com/hariprasathdba