Sandeep Redkar 4:50 pm on October 5, 2015  

Oracle Database Migration across Platforms

For past few years, data is growing tremendously across the globe. The problem starts with managing this high volume data. Technology is also getting advanced with competition and to be in competition, high performance, availability and consistency is one of the mandatory requirement. This brings up another level of complications of data migration from one platform to another. Availability is being a major concern, the major challenge is migrate large data with minimum downtime. Here we will discuss the methods of migration database techniques.

Before we start for migration techniques, we need to understand database endianness. Based on platform architecture, endian can be either of format BIG or LITTLE. Big-endian and little-endian are terms that describe the order in which a sequence of bytes are stored in computer memory. Big-endian is an order in which the “big end” (most significant value in the sequence) is stored first (at the lowest storage address). Little-endian is an order in which the “little end” (least significant value in the sequence) is stored first. For example, in a big-endian computer, the two bytes required for the hexadecimal number 4F52 would be stored as 4F52 in storage (if 4F is stored at storage address 1000, for example, 52 will be at address 1001). In a little-endian system, it would be stored as 524F (52 at address 1000, 4F at 1001).

Database migration methodology is depends on source and target platform endianness format. Once you decide to migrate database across the platform, the available documented methods are as follows –

Cross or Same Endian

Migration Method Advantages Disadvantages
Export/Import or Expdp/Impdp 1.   Simple method

2.   Best when character set change is involved or database version upgrade is required

3.   Ideal for small size databases

 1.    Re-org happens of database objects which can impact the performance of database.

2.    Time consuming and resource intensive method
Transportable Tablespace 1.   Suitable when set of tablespaces are to be migrated

2.   Can be used for large databases with less downtime

3.   Saves time consuming and resource intensive steps of export/import.

 1.   Only applicable to self-contained set of tablespaces

2.   Tablespaces must be in READ-ONLY mode during migration

3.   Source and target must is same characterset
SQL Apply –

Logical Standby, Golden Gate, Streams or Third party like CDC

 1.   Source and target database can be opened in READ-WRITE mode

2.   Less migration time

3.   Best for set of objects to be replicated

 1.   May not be suitable for high transaction databases

2.   Replication maintenance is required

3.   Difficult to debug incase replication is not working
CTAS or Insert through DB link 1.   Best for set of tables or very small database or character set change is involved.

2.   Can be consider as replacement for export/import method

 1.   Time consuming

2.   Only applicable for table migration

3.   PL/SQL objects to be migrated manually

Transportable Tablespace:

Though the transportable tablespace feature was introduced in Oracle version 8, cross platform transportable tableaspace feature was enabled in Oracle Version 10g. Following is the feature wise compatibility matrix for transportable tablespace.

Transport Scenario Source Min. Compatibility Target Min. Compatibility
Databases on Same platform 8.0 8.0
Tablespaces with different block size than the target database 9.0 9.0
Databases on different platforms 10.0 10.0

Following are brief steps about transportable tablespace –

  1. Check the tablespace for self-contained set
  2. Convert tablespace to READ ONLY mode
  3. Copy datafiles of tablespace to target system
  4. Convert them to target system format using RMAN convert command
  5. Use expdp/exp utility to take export (of metadata) using transportable tablespace clause
  6. Transfer the export dump file to target system
  7. Plug-in tablespace to target system using impdp/imp utility

Note – Here the conversion steps can be done on source or target system.

Same Endian

Transportable Database – If source and target database’s endian format is same, this is the best available method for database migration. This method can be used databases having version greater than 10g. Following will be brief steps about migrating database with this method.

  1. Shutdown source database with immediate option
  2. Copy datafiles from source to target server
  3. Convert SYSTEM and UNDO tablespace’s datafiles
  4. Create new controlfile with RESETLOGS option
  5. Open database in resetlogs mode
  6. Create temporary tablespace files
  7. Shutdown database and open in UPGRADE mode
  8. Execute scripts – utlirp.sql and utlrp.sql
  9. Shutdown and startup database in normal mode for application use

Transfer of datafiles from source to target database is time consuming job in this method. For the same, you can try out below options –

  • RMAN compressed backup can be used to take backup on common mount point and then restore it. RMAN scans used blocks hence if backup and restore time can be reduced instead of copying complete database files.
  • If using ASM as storage, mounting of source Diskgroup disks to target system and RMAN “BACKUP AS COPY” command can be used to copy the datafiles. In this case, sector size of the Diskgroup matters and only Diskgroup of same sector size can be mounted/read by other system. This means, by default HPUX is having 1024 bytes sector size whereas AIX or SUN servers are having sector size of 512 bytes. So HPUX disks cannot be mounted on Sun or AIX Servers and vice versa.
  • If third party filesystem is being used, which is common to both the environments, like Veritas file system, then the migration will be very simple as mount point are portable across the system. In this case, storage level flash copy techniques can also be leveraged.
  • DBMS_FILE_TRANSFER procedure can also be used to copy the files between servers. In this method, database link has to be created. Since all database files are to be transferred, we need to have dummy database to create database link.

Standby Database – Some of the platforms supports cross platform standby database. Oracle support MOS# 413484.1 can be used to check the compatibility matrix for cross platform physical standby support. If source and target platform supports physical standby, then this will be the best method to migrate database with safest and smallest downtime for migration. The migration will happen through database switchover.