11g R2 RMAN: TRANSPORT DATABASE FROM LINUX TO WINDOWS

In this post, I will demonstrate transporting a database on Linux 32 bit platform to Windows 32 bit.
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
– check the platform name of current database –
SQL>SELECT PLATFORM_NAME
          FROM  V$DATABASE;
PLATFORM_NAME
——————————
Linux IA (32-bit)
– Check the endian format of current platform
– Note that ENDIAN format is little

SQL>SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
          FROM   V$TRANSPORTABLE_PLATFORM
         WHERE UPPER(PLATFORM_NAME) LIKE ‘%LINUX IA%';
PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
———– —————————— ————–
         10 Linux IA (32-bit)              Little
         11 Linux IA (64-bit)              Little
– Check that endian format of the target platform i.e. Windows IA(32-bit) is also little
– Hence database can be transported
SQL>SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
          FROM   V$TRANSPORTABLE_PLATFORM
          WHERE UPPER(PLATFORM_NAME) LIKE ‘%MICROSOFT WINDOWS%';


PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
———– —————————— ————–
          7 Microsoft Windows IA (32-bit)  Little
          8 Microsoft Windows IA (64-bit)  Little
         12 Microsoft Windows x86 64-bit   Little
If the target platform does not appear in the output from V$TRANSPORTABLE_PLATFORM, then the database cannot be migrated using TDB.
Start the database in READ ONLY mode
SQL> shutdown immediate;
     startup mount;
     alter database open read only;
— Verify the database is ready for migration
SQL>
    SET  SERVEROUTPUT ON;
    DECLARE
      db_ready BOOLEAN;
    BEGIN
      db_ready :=
         DBMS_TDB.CHECK_DB(‘Microsoft Windows IA (32-bit)’,DBMS_TDB.SKIP_READONLY);
      if db_ready
          then
             dbms_output.put_line(‘YES your database can be transported to Windows
platform’);
      else
         dbms_output.put_line(‘NO your database can not be transported to WIndows
Platform’);
      end if;
     end;
/
– Make a folder to hold converted datafiles
$mkdir /home/oracle/convertdb
— Run the RMAN CONVERT DATABASE Command
The CONVERT DATABASE command specified in the example creates
  – a transport script named /home/oracle/convertdb/transport.sql which contains SQL statements used to create the new database on the destination platform,
  – a PFILE initnewdb.ora in file /home/oracle/convertdb  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/convertdb  directory in the format of the target platform ‘Microsoft Windows IA (32-bit)
RMAN>CONVERT DATABASE
              NEW DATABASE ‘newdb’
              TRANSPORT SCRIPT ‘/home/oracle/convertdb/transport.sql’
              TO PLATFORM ‘Microsoft Windows IA (32-bit)’
               FORMAT ‘/home/oracle/convertdb/newdb’
               DB_FILE_NAME_CONVERT ‘/u01/app/oracle/oradata/linuxdb/’ ‘/home/oracle/convertdb/';


Starting conversion at source at 18-NOV-12
….
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/u01/app/oracle/oradata/linuxdb/system01.dbf
converted datafile=/home/oracle/convertdb/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/linuxdb/users01.dbf
converted datafile=/home/oracle/convertdb/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /home/oracle/convertdb/init_newdb.ora. This PFILE will be used to create
the database on the target platform
Run SQL script /home/oracle/convertdb/transport.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
Finished conversion at source at 18-NOV-12
– Review and Edit the PFILE  to change any settings for the destination database.
$vi  /home/oracle/convertdb/init_newdb.ora
# Please change the values of the following parameters:
  control_files            = “c:\app\administrator\oradata\newdb\control01.ctl”
  db_recovery_file_dest=”c:\app\administrator\flash_recovery_area”
  audit_file_dest          = “c:\app\administrator\admin\newdb\adump”
– Review and edit the transport script transport.sql
$vi  /home/oracle/convertdb/transport.sql
– Modify the following :
STARTUP NOMOUNT PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS
\initnewdb.ora';
LOGFILE
  GROUP 1 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,
  GROUP 2 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO02.LOG’ SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO03.LOG’ SIZE 50M BLOCKSIZE 512
DATAFILE
  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\system01.dbf’,
  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\sysaux01.dbf’,
  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\undotbs01.dbf’,
  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\users01.dbf’,
  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\example01.dbf’
ALTER TABLESPACE TEMP
ADD TEMPFILE
 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\TEMP01.DBF’
  SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
STARTUP UPGRADE PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS\initnewdb.ora';
@@ ?\rdbms\admin\utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS\initnewdb.ora';
– Make folders on target windows host
c:\>mkdir c:\app\administrator\oradata\newdb
      mkdir c:\app\administrator\admin\newdb\adump”
– Copy all the files in /home/oracle/convertdb folder to c:\app\administrator\oradata\newdb
   i.e. datafiles
        Pfile
        transport script
– Move PFILE to $ORACLE_HOME/database  i.e. c:\app\administrator\product
\11.2.0\dbhome_1\dbs
C:\>copy c:\app\administrator\oradata\newdb\init_newdb.ora c:\app\administrator\product
\11.2.0\dbhome_1\dbs\initnewdb.ora
– create a service for newdb on windows
c:\>oradim -new -sid newdb
– Execute the transport script in SQL*Plus to create the new database on the destination host.
c:\>set oracle_sid=newdb
      sqlplus  / as sysdba
SQL>@c:\app\administrator\oradata\newdb\transport.sql
– check that database has been transported —
SQL>select * from hr.employees;
——————————————————————————-
Related links:
                                                                                             ———————–

21 thoughts on “11g R2 RMAN: TRANSPORT DATABASE FROM LINUX TO WINDOWS

  1. Hi Anju,

    Thankx a lot. You made my day with this article……
    I planned 4 hrs for this task but I started and ended successfully with your above post in just 20 mins….

    :-)

  2. Hi Anju,
    Your blog is extremely helpful .
    It has covered new feature and new feature and in more practical way.
    This is first blog I found very simple and very informative .

    Thanks
    Amit Bansode

  3. Hi Anju,

    Your blog is very helpful. One doubt regarding this post
    – create a service for newdb on windows
    c:\>oradim -new -sid newdb

    what extractly oradim do in above command. we use have a message called
    instance created.

    Thanks
    Shiv

  4. I am getting below errors
    C:\Windows\system32>set oracle_sid=newdb
    C:\Windows\system32>sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 8 16:11:05 2016
    Copyright (c) 1982, 2010, Oracle. All rights reserved.
    Connected to an idle instance.
    SQL> @G:\app\maruf.hossain\oradata\newdb\transport.sql
    ORA-00119: invalid specification for system parameter LOCAL_LISTENER
    ORA-00132: syntax error or unresolved network name ‘NEWDBLISTENER_TWOTEST’

    Please help to resolve the issue.

  5. Hi,
    Now getting below
    C:\Windows\system32>set ORACLE_SID=newdb
    C:\Windows\system32>sqlplus /nolog
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 9 11:46:06 2016
    Copyright (c) 1982, 2010, Oracle. All rights reserved.
    SQL> @G:\app\maruf.hossain\oradata\newdb\transport.sql
    ORA-01031: insufficient privileges
    SP2-0640: Not connected

    Thanks
    Maruf

  6. Hi
    Now getting below
    C:\Windows\system32>sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 9 14:10:44 2016
    Copyright (c) 1982, 2010, Oracle. All rights reserved.
    ERROR:
    ORA-01031: insufficient privileges
    Enter user-name:
    Thanks
    Maruf

  7. D:\app\rehan\virtual\product\12.2.0\dbhome_1\bin>sqlplus / as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Fri May 25 14:54:41 2018

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> @d:\app\rehan\virtual\product\12.2.0\dbhome_1\oradata\newdb\transport.sql
    LRM-00109: could not open parameter file ‘/home/oracle/convertdb/init_newdb.ora’

Your comments and suggestions are welcome!