Transporting an Oracle database to another os platform the fastest way

You can use RMAN to transport tablespaces across platforms with different endian formats. You can also use RMAN to transport an entire database to a different platform so long as the two platforms have the same endian format.

— From “27 Transporting Data Across Platforms” – http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm


So, in my own words – datafiles (tablespaces) are interchangeable with any operating system having the same endian format. (I don’t know what ‘endian format’ is nor do I care but it’s exmplained here.)

This is great and enables for exceptionally simple and fast moving of an entire database to another os platform.

Unfortunately however the oracle documentation does not discuss the scenario that is in the Oracle Metalink document ‘Cross-Platform Database Migration (across same endian) using RMAN Transportable Database’ [ID 1401921.1] where you move your entire database to another platform and performing only a partial (the bare neccesary) rman convert.

IE;
No need for ‘messing around’ with datapump tablespaces export / import since we want to whole database.

— hat tip for the Metalink Note is from my esteemed colleague Jeroen Pouwiel – thanks amie.

Most important statement that I found in this metalink note is;

It’s default behavior is to perform datafile conversion on all datafiles in the database. However, only datafiles that contain undo data require conversion including all datafiles beloging to SYSTEM tablespace and all UNDO tablespaces.

Below is a complete walktrough of a move (copy) from a database running on a Sun solaris server to a server running AIX doing only a `convert datafile` on the datafiles that actually NEED it.
This scenario reduces the need to convert your whole database and reduces the time you need for getting it on the other side.
I’m investigating all this for a high profile internet application – customer wants to have as little downtime as possible – and of course … it has to be carried out in the middle of the night – so I’m glad as well when I can save myself a few hours sleep :)

So to sum up;

The scenario below describes the fastest, least error prone way of moving your entire database 1-on-1 to another operating system with the same endian format.



1. CHECK IF THE DESIRED OS MIGRATION IS POSSIBLE (in my case Sun Solaris -> AIX)

This is done with the SQL below; Checking the Database Before Cross-Platform Database Conversion / Executing DBMS_TDB.CHECK_DB

— Make sure your database is in READ ONLY mode prior to running SYS.DBMS_TDB.CHECK_DB or you will hit “Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and retry.
(@THX: Gaetano)

set serveroutput on
declare
  db_ready BOOLEAN;
begin
  db_ready := SYS.DBMS_TDB.CHECK_DB( 'AIX-Based Systems (64-bit)', SYS.DBMS_TDB.SKIP_NONE);
end;
/

— Query v$transport_paltform for a list of available options – see sample output below;

SQL> select * from V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
----------- -------------------------------- --------------
          1 Solaris[tm] OE (32-bit)          Big
          2 Solaris[tm] OE (64-bit)          Big
          7 Microsoft Windows IA (32-bit)    Little
         10 Linux IA (32-bit)                Little
          6 AIX-Based Systems (64-bit)       Big
          3 HP-UX (64-bit)                   Big
          5 HP Tru64 UNIX                    Little
          4 HP-UX IA (64-bit)                Big
         11 Linux IA (64-bit)                Little
         15 HP Open VMS                      Little
          8 Microsoft Windows IA (64-bit)    Little
          9 IBM zSeries Based Linux          Big
         13 Linux 64-bit for AMD             Little
         16 Apple Mac OS                     Big
         12 Microsoft Windows 64-bit for AMD Little
         17 Solaris Operating System (x86)   Little

16 rows selected.

— Whenever you don’t know how Oracle ‘identifies’ you destination OS simple go to any database running on the os you want to moove your database to and query for v$database.platform_name;
eg like

SQL> select platform_name from v$database;

PLATFORM_NAME
--------------------------------
AIX

2. IDENTIFY EXTERNAL STUFF in your source database that may need extra care

Easy;


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

– see manuals identify any external tables, directories, or BFILEs

SQL> set serveroutput on
SQL> declare
  2    x boolean;
  3  begin
  4    x := sys.dbms_tdb.check_external;
  5  end;
  6  /
The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR, SYS.ADMIN_DIR, SYS.WORK_

PL/SQL procedure successfully completed.

Report back any ‘user’ – ‘non-oracle’ directories / ext. objects to the application owners.
In this scenario none are present.

3. IDENTIFY DATAFILES THAT WILL NEED A CONVERSION (through RMAN convert).

These are datafiles of the tablespaces containing rollback segments.

Use SQL queries below to identify any daatafiles that contain rollback segments;

select file_name "datafiles requiring conversion" from dba_data_files
where tablespace_name in (select distinct tablespace_name from dba_rollback_segs)
/

select file_name "files not requiring conversion" from dba_data_files
where tablespace_name not in (select distinct tablespace_name from dba_rollback_segs)
/

== NOW PUT THE SOURCE database in READ ONLY mode.

4. SCP ALL THE DATAFILES TO YOUR DESTINATION HOST.

– Make sure to scp the database files identified as datafiles in need of a conversion to a staging directory in the destination host. In my case I scp them to ‘/tmp’

– All remaining database files – not needing conversion – can go to your desired final location on the destination host.

5. CONVERT THE DATAFILES – only the ones that need it

When the datafiles in need of a conversion reside on your destination host it is time to convert them.

These ‘in need of conversion’ database will reside in your selected staging directory (/tmp) or anything other than their final locations.

Start your newly created database (instance) on the destination host – start it in NOMOUNT mode (mounting it will be hard because you don’t have any files yet). This instance has all the properties of your final database; eg like it’s db_name, directory structures for your control_files, user|background_dump_dest etc.

Now run the convert command with RMAN to convert the database files. RMAN will then copy the converted datafile FROM your staging directory TO the directory you specify – after the ‘format’ clause.

Connect to your destination database (instance in fact) using rman (no need to define any remote catalog of course)

$ rman target /

Convert the datafiles – syntax here

CONVERT
# Staging location
DATAFILE '/tmp/${DATABASE_NAME}system01.dbf'
# Desired final location
FORMAT '/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}system01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)' ;

Repeat this for all the datafiles that need conversion. Or make (generate!) one single ‘run’ instruction as below:

RUN {
	CONVERT DATAFILE
	'/tmp/${DATABASE_NAME}system01.dbf'
	FROM PLATFORM 'Solaris[tm] OE (64-bit)'
	FORMAT '/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}system01.dbf';

	CONVERT DATAFILE '/tmp/${DATABASE_NAME}undotbs01.dbf'
	FROM PLATFORM 'Solaris[tm] OE (64-bit)'
	FORMAT '/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}undo01.dbf';
}

6. CREATE THE CONTROLFILE (and thus the database) on your destination host

Since all the database files you copied were copied from the source database being in READ ONLY mode you can be sure that no crash recovery is needed on your destination host.
All that is left now is to create the controlfile for the new database on the detination host and do some aftercare (utlirp.sql & utlrp.sql).

You can generate a create controlfile script by logging in to the source database database and using the `alter database backup controlfile to trace` command.

Make sure your final `create controlfile` command has the RESETLOGS specified and whenever you change the database name the SET key word.

eg like;

CREATE CONTROLFILE REUSE 
  SET DATABASE "{DESIRED_DATABASE_NAME}" 
  RESETLOGS 
  NOARCHIVELOG
-- <-- logfile clause goes here - they will be created anew
.
.
-- these are the paths of all your scp-ed datafiles
DATAFILE
-- The converted dafafiles
'/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}system01.dbf',
'/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}undo01.dbf',
-- The unconverted dafafiles
'/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}sysaux01.dbf',
'/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}users01.dbf',
'/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}tools01.dbf',
'/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}********.dbf',
'/m001/oradata/${DATABASE_NAME}/${DATABASE_NAME}********.dbf',
.
.
.

CHARACTER SET WE8ISO8859P1
;


ALTER DATABASE OPEN RESETLOGS 
;

7. DO THE NEEDED AFTERCARE on your converted destination database.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP UPGRADE;
SQL> @ ?/rdbms/admin/utlirp
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
SQL> @ ?/rdbms/admin/utlrp

And presto!

Finally when you want to register this new database into the same RMAN catalog as your source database make sure to change the database id.

8. Finalize

Make a backup of your copied database …

Always.

8 thoughts on “Transporting an Oracle database to another os platform the fastest way

  1. Gaetano says:

    Hi,
    thanks a lot for sharing this.
    The readonly mode must be initiated before the db_ready:=SYS.DBMS_TDB.CHECK_DB() step otherwise you’ll receive “Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and retry.”
    That was my case with db_ready := SYS.DBMS_TDB.CHECK_DB( ‘Linux x86 64-bit’, SYS.DBMS_TDB.SKIP_NONE);

    Best Regards,
    Gaetano

  2. remivisser says:

    Thanks Gaetano for your feedback.

    I have adjusted the article accordingly.

    Remi

  3. john says:

    Hi thanks a lot for sharing this… can you please let me know what step need to be added if the endian format is different.

    like if i want to do a cross platform migration from aix to linux.

    Also i have some doubts like… there is no need to use transportable tablespace for exporting the data? dont we need to recover the db in destination?

    please clarify as i am new to this.. thanks :)

  4. KAREN says:

    I am trying to exectude the guide but when i have the next error:

    Database mounted.
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1:
    ‘/u02/grptest/ora_test/TEST/db/apps_st/data/system01.dbf’

    1. remivisser says:

      You should make sure to issue a shutdown immediate prior to copying your database datafiles. This ‘error’ indicates you copied the datafiles while the database was still open ( or shutdown aborted ).

      1. Julien Andonov says:

        I guess it can be also in open read-only ?

  5. Yassine says:

    Many Thanks

  6. vasista says:

    Hi,

    Kindly clarify in this.
    -source db in read only mode
    -staging location in dest server-/mnt/test/backup
    -final location-/risgpq02/oradata/GPQ/system01.dbf
    destination db in nomount stage

    RMAN> RUN {
    2> CONVERT DATAFILE
    3> ‘/mnt/test/backup/system01.dbf’
    4> FROM PLATFORM ‘HP-UX (64-bit)’
    5> FORMAT ‘/risgpq02/oradata/GPQ/system01.dbf’;
    6>
    7> CONVERT DATAFILE ‘/mnt/test/backup/undotbs01.dbf’
    8> FROM PLATFORM ‘HP-UX (64-bit)’
    9> FORMAT ‘/risgpq02/oradata/GPQ/undotbs01.dbf’;
    10> }

    Starting backup at 04-SEP-16
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=156 devtype=DISK
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of backup command at 09/04/2016 09:19:15
    RMAN-06098: the target database must be mounted when issuing a BACKUP command

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>