Home Music Tech Info Tech Blog About Us Contact Us

Information Technology

Oracle Cross Platform Database Migration

The hardware platform influences the endianness of an Operating System. Majority of the Intel processors are of "Little Endian" while a RISC processor proprietary for UNIX Operating Systems are "Big Endian".

Without going much of technical details of the definition of an Endian, it is just a difference of the methods on how a "Data Word" which comprises of stored bytes are organized and interpreted.

When doing cross-platform database migration, it is always possible to directly convert the database files into another platform provided that they are of the same endian format. Thus, a direct conversion of a database file has to be "Little Endian To Little Endian" or "Big Endian To Big Endian".

You cannot directly convert a file from Little Endian To Big Endian or vice versa. If in case there is such a need, the options you have are few and cumbersome which as of this writing, the following are the only options you can go for:

1. Full Data Pump export (if downtime permits, requires source to be Read-Only DB).

2. Partial Data Pump export (upto an SCN) and configure Golden Gate replication or Dell Shareplex for logical synching of data.

3. Transportable tablespace (needs downtime for read only).

The Oracle Database has a built-in list of endian format for various platform to help give the database administrator better decision making on migrating databases.

You can run a SQL query as demonstrated below for the listing,

column platform_name format a35;

set linesize 120;

select platform_id, platform_name, endian_format

from v$transportable_platform

order by endian_format;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

----------- ----------------------------------- --------------

3 HP-UX (64-bit) Big

6 AIX-Based Systems (64-bit) Big

18 IBM Power Based Linux Big

2 Solaris[tm] OE (64-bit) Big

4 HP-UX IA (64-bit) Big

16 Apple Mac OS Big

1 Solaris[tm] OE (32-bit) Big

9 IBM zSeries Based Linux Big

17 Solaris Operating System (x86) Little

19 HP IA Open VMS Little

20 Solaris Operating System (x86-64) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

----------- ----------------------------------- --------------

12 Microsoft Windows x86 64-bit Little

13 Linux x86 64-bit Little

8 Microsoft Windows IA (64-bit) Little

21 Apple Mac OS (x86-64) Little

11 Linux IA (64-bit) Little

5 HP Tru64 UNIX Little

10 Linux IA (32-bit) Little

7 Microsoft Windows IA (32-bit) Little

15 HP Open VMS Little

20 rows selected.

In this excercise, we'll examine what it takes to convert the database files Little Endian To Little Endian (Solaris 10 x86-64 to Oracle Linux R5).

RMAN, which is the backup tool for Oracle databases, provides two types of command to convert a database:

1) Convert Database... To Platform

RMan will convert the database files at the source platform onto a given directory path to store the converted files.

The target directory for the convert can be in a form of NFS mount as served by your intended platform. This is just an option to avoid the process of copying the converted files onto the target platform.

The source database has to be in read only state when running the conversion.

2) Convert Database... On Target

Rman will create a conversion script at the source platform which you will copy onto your target platform along with the source database files and run the conversion at the target.

The source database has to be at least in read only state when copying the files.

Among the two conversion methods, I prefer the Convert Database...To Platform command since it is a lot more concise in terms of implementation especially when used in combination of NFS or other similar file sharing technology to house the converted data files. With a shared file system, it eliminates the need to copy the converted files over to your target platform host.

Again, as I try to explain before, the RMAN Convert Database solution is for migrating the Oracle database across platforms having the same endian format.

So without further ado, here are some of my notes.

RMAN CONVERT DATABASE TO PLATFORM

1. Start the database in READ ONLY

pacific:oradb> sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Feb 23 10:09:29 2015

SQL> connect sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup mount pfile=/dsk0/orabin/10gR2/product/10.2.0/db/dbs/initORA10DB1_noDG.ora

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 2101736 bytes

Variable Size 264244760 bytes

Database Buffers 801112064 bytes

Redo Buffers 6283264 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

2. Verify that a conversion is possible.

When running the following procedure, you should only get a single message "PL/SQL procedure successfully completed." to indicate that a conversion has no compatibility issues. Any other message you get would indicate a problem.

Sample output of a compatible endian platform:

At the source db (Solaris 10 x86-64),

SQL> set serveroutput on;

SQL> declare

2 db_ready boolean;

3 begin

4 db_ready := sys.dbms_tdb.check_db('Linux x86 64-bit', sys.dbms_tdb.skip_readonly);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL>

Sample output, this one failed, cannot convert Linux to Solaris Sparc (little endian to big endian).

SQL>l

1 declare

2 db_ready boolean;

3 begin

4 db_ready := sys.dbms_tdb.check_db('Solaris[tm] OE (64-bit)', sys.dbms_tdb.skip_readonly);

5* end;

SQL>/

The specified target platform name 'Solaris[tm] OE (64-bit)' is invalid or the target platform is not transportable.

PL/SQL procedure successfully completed.

3. Run the RMAN Convert Database To Platform command.

My target directory to place the converted database files is - /dsk0/oradb/convertdb

Just keep in mind that if you have subdirectories in your source directory (in my case /dsk0/oradb/ORA10DG1 has dbf,redo,undo and ctl directories) you should create all of them also in your target directory.

pacific:oradb> rman

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Feb 23 13:29:55 2015

RMAN> connect target sys

target database Password:

connected to target database: ORA10DG1 (DBID=2552731710)

RMAN> convert database

2> new database 'newdb10'

3> transport script '/dsk0/oradb/convertdb/transportscript.sql'

4> to platform 'Linux x86 64-bit'

5> DB_FILE_NAME_CONVERT '/dsk0/oradb/ORA10DG1','/dsk0/oradb/convertdb';

Starting convert at 23-FEB-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=208 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=207 devtype=DISK

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

channel ORA_DISK_2: starting datafile conversion

input datafile fno=00001 name=/dsk0/oradb/ORA10DG1/oradata/dbf/system_1_ORA10DG1.dbf

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00002 name=/dsk0/oradb/ORA10DG1/oradata/undo/undo1_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/undo/undo1_1_ORA10DG1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:36

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00003 name=/dsk0/oradb/ORA10DG1/oradata/dbf/sysaux_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/sysaux_1_ORA10DG1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00006 name=/dsk0/oradb/ORA10DG1/oradata/dbf/qtdata_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/system_1_ORA10DG1.dbf

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:44

channel ORA_DISK_2: starting datafile conversion

input datafile fno=00007 name=/dsk0/oradb/ORA10DG1/oradata/dbf/qtindex_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/qtdata_1_ORA10DG1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00004 name=/dsk0/oradb/ORA10DG1/oradata/dbf/defaultdata_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/qtindex_1_ORA10DG1.dbf

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:03

channel ORA_DISK_2: starting datafile conversion

input datafile fno=00008 name=/dsk0/oradb/ORA10DG1/oradata/dbf/replcdemodat_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/defaultdata_1_ORA10DG1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00009 name=/dsk0/oradb/ORA10DG1/oradata/dbf/replcdemodat_2_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/replcdemodat_1_ORA10DG1.dbf

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01

channel ORA_DISK_2: starting datafile conversion

input datafile fno=00010 name=/dsk0/oradb/ORA10DG1/oradata/dbf/replcdemoidx_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/replcdemodat_2_ORA10DG1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00005 name=/dsk0/oradb/ORA10DG1/oradata/dbf/explainplan_1_ORA10DG1.dbf

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/replcdemoidx_1_ORA10DG1.dbf

channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:00

converted datafile=/dsk0/oradb/convertdb/oradata/dbf/explainplan_1_ORA10DG1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Run SQL script /dsk0/oradb/convertdb/transportscript.sql on the target platform to create database

Edit init.ora file /dsk0/orabin/10gR2/product/10.2.0/db/dbs/init_00q00e1b_1_0.ora. This PFILE will be used to create the database on the target platform

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

Finished backup at 23-FEB-15

RMAN>

4. Modify the transport script and copy converted files onto target then run the transport script.

The RMAN Convert Database To Platform command shows that it converted only the database files and undo.

The redo and control files will be created using the transportscript.sql.

If you examine the script, the new init.ora was created at the local ORACLE_HOME/dbs and was referenced by the script.

Copy this init.ora onto the */covertdb/ directory if you are planning to transport the disk containing the converted data onto another server.

Edit the script to provide the correct paths and filename for your control and redo files and the pfile.

If the */convertdb/ path is not the intended path for your database files, you may proceed to create the correct path and copy

the files over and then modify the script to reflect the correct database file path.

Here's a sample content of a transport script:

pacific:oradb> cat transportscript.sql

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='/dsk0/orabin/10gR2/product/10.2.0/db/dbs/init_00q00e1b_1_0.ora'

CREATE CONTROLFILE REUSE SET DATABASE "NEWDB10" RESETLOGS ARCHIVELOG

MAXLOGFILES 36

MAXLOGMEMBERS 3

MAXDATAFILES 1500

MAXINSTANCES 8

MAXLOGHISTORY 1168

LOGFILE

GROUP 1 (

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-181_T-1_A-860784894_00q00e1b',

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-181_T-1_A-860784894_01q00e1b'

) SIZE 10M,

GROUP 2 (

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-182_T-1_A-860784894_00q00e1b',

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-182_T-1_A-860784894_01q00e1b'

) SIZE 10M,

GROUP 3 (

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-183_T-1_A-860784894_00q00e1b',

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-183_T-1_A-860784894_01q00e1b'

) SIZE 10M,

GROUP 4 (

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-180_T-1_A-860784894_00q00e1b',

'/dsk0/orabin/10gR2/product/10.2.0/db/dbs/arch_D-NEWDB10_id-2552731710_S-180_T-1_A-860784894_01q00e1b'

) SIZE 10M

DATAFILE

'/dsk0/oradb/convertdb/oradata/dbf/system_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/undo/undo1_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/sysaux_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/defaultdata_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/explainplan_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/qtdata_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/qtindex_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/replcdemodat_1_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/replcdemodat_2_ORA10DG1.dbf',

'/dsk0/oradb/convertdb/oradata/dbf/replcdemoidx_1_ORA10DG1.dbf'

CHARACTER SET AL32UTF8

;

-- Set Database Guard and/or Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP1G1 ADD TEMPFILE '/dsk0/orabin/10gR2/product/10.2.0/db/dbs/data_D-NEWDB10_I-2552731710_TS-TEMP1G1_FNO-1_00q00e1b'

SIZE 8388608 AUTOEXTEND ON NEXT 8388608 MAXSIZE 4096M;

ALTER TABLESPACE TEMP2G1 ADD TEMPFILE '/dsk0/orabin/10gR2/product/10.2.0/db/dbs/data_D-NEWDB10_I-2552731710_TS-TEMP2G1_FNO-2_00q00e1b'

SIZE 8388608 AUTOEXTEND ON NEXT 8388608 MAXSIZE 4096M;

ALTER TABLESPACE TEMP1G2 ADD TEMPFILE '/dsk0/orabin/10gR2/product/10.2.0/db/dbs/data_D-NEWDB10_I-2552731710_TS-TEMP1G2_FNO-3_00q00e1b'

SIZE 8388608 AUTOEXTEND ON NEXT 8388608 MAXSIZE 4096M;

ALTER TABLESPACE TEMP2G2 ADD TEMPFILE '/dsk0/orabin/10gR2/product/10.2.0/db/dbs/data_D-NEWDB10_I-2552731710_TS-TEMP2G2_FNO-4_00q00e1b'

SIZE 8388608 AUTOEXTEND ON NEXT 8388608 MAXSIZE 4096M;

-- End of tempfile additions.

set echo off

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt * Your database has been created successfully!

prompt * There are many things to think about for the new database. Here

prompt * is a checklist to help you stay on track:

prompt * 1. You may want to redefine the location of the directory objects.

prompt * 2. You may want to change the internal database identifier (DBID)

prompt * or the global database name for this database. Use the

prompt * NEWDBID Utility (nid).

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE

STARTUP UPGRADE PFILE='/dsk0/orabin/10gR2/product/10.2.0/db/dbs/init_00q00e1b_1_0.ora'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='/dsk0/orabin/10gR2/product/10.2.0/db/dbs/init_00q00e1b_1_0.ora'

-- The following step will recompile all PL/SQL modules.

-- It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

pacific:oradb>