Transportable Database from Linux to Windows

TRANSPORTABLE DATABASE LINUX TO WINDOWS:

Overview
TDB requires that data files be converted to the target platform format. The data file conversion can occur on either the source system or the target system.
When performing a source system conversion, TDB creates a second copy of all data files on the source system in the format of the target system.
The converted data files must then be transferred to the
proper location on the target system.
Using TDB to migrate a database to a new platform of the same endian format consists of the following
High-level steps:
1. Check prerequisites
2. Prepare for the platform migration
3. Start the database in READ ONLY mode
4. Verify the database is ready for migration
5. Run the RMAN CONVERT DATABASE command
6. Move necessary files to the target system
7. Complete the migration

 

Implementation:
– Check Prerequisites

Source Database :- (Linux Platform 64-bit)

Check the platform name of current database:

Check the endian format of current platform
Note that ENDIAN format is little

 Check that endian format of the target platform i.e. Windows IA(64-bit) is also little
 So the database can be transported

 

Startup database in READ ONLY mode

 Make a folder to hold converted datafiles

$mkdir /home/oracle/demonew

RMAN Transportable Database Method :-

Execute DBMS_TDB.CHECK_EXTERNAL function to identify any external tables, directories, or BINARY FILEs.RMAN cannot transport of these files, so you must copy the files manually and re-create the database directories.

Below PL SQL script will help to see database to transportable or not.

SET SERVEROUTPUT ON;
DECLARE
b BOOLEAN;
BEGIN
b := DBMS_TDB.CHECK_DB(‘Microsoft Windows IA (64-bit)’,DBMS_TDB.SKIP_READONLY);
if b
then
dbms_output.put_line(‘YES your database can be transported to Windows platform’);
else
dbms_output.put_line(‘NO your database cannot be transported to WIndows Platform’);
end if;
end;
/

Run the RMAN CONVERT DATABASE Command
The CONVERT DATABASE command specified in the example creates
  – a transport script named /home/oracle/demonew/transportdb.sql which contains SQL statements used to create the new database on the destination platform,
  – a PFILE init_demonew.ora in file /home/oracle/demonew  for use with the new database on the destination platform, containing settings used  from the source database.
Several entries at the top of the PFILE should be edited when the database is moved to the destination platform
  – a copy of all data files in the =/home/oracle/demonew  directory in the format of the target platform ‘Microsoft Windows IA (64-bit)

 

Edit init.ora file /home/oracle/demonew/INIT_DEMONEW.ora.
This PFILE will be used to create the database on the target platform
Run SQL script /home/oracle/demonew/transportdb.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility

Target Database :- (Windows Platform 64-bit)

Make folders on target windows host
c:\>mkdir c:\app\administrator\oradata\demonew
      mkdir c:\app\administrator\admin\demonew\adump”
Copy all the files in /home/oracle/demonew folder to c:\app\administrator\oradata\demonew
   i.e. datafiles
        Pfile
        transport script
Move PFILE to $ORACLE_HOME/database 
i.e. c:\app\administrator\product\12.1.0\dbhome_1\dbs
C:\>  copy   c:\app\test\oradata\newdb\INIT_DEMONEW.ora                                                       c:\app\test\oradata\12.1.0\dbhome_1\dbs\INIT_DEMONEW.ora
create a service for newdb on windows
c:\>oradim -new -sid DEMONEW
Execute the transport script in SQL*Plus to create the new database on the destination host.

  • Edit the PFILE to change DB_NAME and necessary directories to create database in TARGET side.
  • Change  control_files, audit_file_dest,db_name parameters as below:
  • Run the TRANSPORTDB.SQL file in SQL plus to create new database on TARGET side.It will create control files and redo log files and open database with resetlogs.After open resetlogs,it will also run utlrp.sql and utlirp.sql on target side.

I am attaching the  transportdb.sql.

  • Check the database status and invalid objects count.
  • Check the data is transported to the new database.

 

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-Transportable Database From Windows To Linux

Cross Platform Transport Database from Windows to Linux :

Check Platform Support and Endian format :

Source Database :

Target Database :

  • Check that endian format of the target platform i.e. Linux 64 bit  is also little and source is also Little endian format.
  • 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(Microsoft Windows 64 bit)  to Target(Linux 64 bit) platform,

Check the data to be checked after the database is transported to Linux platform.

RMAN Transportable Database Method :-

  • Execute DBMS_TDB.CHECK_EXTERNAL function to identify any external tables, directories, or BFILEs.RMAN cannot transport of these files, so you must copy the files manually and re-create the database directories.

  • Check the DIRECTORY_PATH in SOURCE database and check same path is available in TARGET database or rename DIRECTORY_PATH  after RMAN conversion.

  • Startup database in READ ONLY mode

  • Check the SOURCE database is ready to migration using DBMS_TDB.CHECK_DB function.

  • The above function checks incorrect compatibility settings,active transactions, or incompatible endian formats between the source platform and destination platform.
  •  Any condition reported by CHECK_DB must be resolved before proceeding RMAN CONVERT DATABASE.
  • Create folder in name of database  ORCL18C 

  • Start RMAN conversion in SOURCE database,

  • Create PFILE in SOURCE database (Windows environment)

  • Copy the converted datafiles and created PFILE from C:\ORCL18C (SOURCE side) to the TARGET side location ‘/home/oracle’

Target Database :-

  • Create respective directories for new transportable database

  • Create folder like dbname under $ORACLE_BASE/oradata

  • Move all .dbf files to$ORACLE_BASE/oradata/ORCL18C folder.

  • Edit the PFILE to change DB_NAME and necessary directories to create database in TARGET side.

Change  control_files, audit_file_dest,db_name parameters as below:

  • Edit TRANSPORT.SQL file and modify the location entries as below:

  • Run the TRANSPORT.SQL file in SQL plus to create new database on TARGET side.It will create control files and redolog files and open database with resetlogs.After open resetlogs,it will also run utlrp.sql and utlirp.sql on target side.

  • Check the database status and invalid objects count.

Check the data is transported to the new database.

 

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

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

 

 

Transportable tablespace from Windows to Linux

Transportable tablespace :-

  • Transportable tablespace is the feature of 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 Windows to Linux :

Check Platform Support and Endian format :

Source Database :

Target Database :

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(Microsoft Windows 64 bit)  to Target(Linux 64 bit) platform,

Use CONVERT  Tablespace   FROM  PLATFORM  on  Source  Database

RMAN> convert tablespace TESTTBS1 to platform="Linux IA (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/TEST18C/datafile/testtbs.dbf',
TO PLATFORM="Linux x86 64-bit"
FROM PLATFORM="Windows TRu64 LINUX"
DB_FILE_NAME_CONVERT= "/home/oracle/", "/u01/app/oracle/oradata/TEST18C/datafile"
PARALLELISM=5;

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.

Steps for Transporting the tablespace :-

  • Create Tablespace and User

  • Create sample table 

  • Check the tablespace will be self contained

Note: 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.

  • Make Tablespace TESTTBS in READ ONLY mode.

  • Export  TESTTBS tablespace  metadata.

  • Copy datafile and  dumpfile to target database.

Import transportable tablespace in TARGET Database :

  • Before importing the metadata in target database,check user dbwrora is present in target database or create and grant access to the user.

  • Import the dumpfile and copy of datafile in target database.

  • Make the tablespace TESTTBS in READ WRITE mode.
  • Check the table objects data count.

 

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