Converting a database from Unix to Windows using RMAN

Transporting an entire database from one platform to another using RMAN

Transportable Database is an extension of the transportable tablespace feature. Transporting tablespaces is faster than doing data pump export/import because the data copied instead of being pumped through the SQL engine.

The Oracle Transportable Database feature uses RMAN using the CONVERT DATABASE command.

§        This form of the command requires the source database to be in READ ONLY mode.

§        User-created tablespaces are converted to the destination platform by RMAN, and written to the destination specified.  

§        Oracle supplied tablespaces like SYSTEM, SYSAUX, etc are written out as a Data Pump .DMP file.  

§        The CONVERT DATABASE command will script out a text parameter file and a transport script, which will be needed to mount the new database and complete the migration at its destination platform.

§        The pfile and transport script will need edits to parameters and path names to to suit the destination.

Below is an example of conversion of a 12c NONCDB database named NONCDB1 from unix 64 bit to Windows 64 bit.

	Source	Destination
OS Platform	Linux 64-bit, little endian	Windows 64-bit, little endian
Datafile location	/u01/app/oracle/oradata/noncdb1	/u01/app/oracle/oradata/win64/noncdb1
Convert script location	n.a	n.a
Transport script location	/home/oracle/transport.sql	n.a

Note:

The source database must be opened read only during the conversion.

Pre-transport checking of the source database:

Before doing a transport operation, it is necessary to check the source database to ensure that its indeed possible to transport it to the target platform, and that there are no incompatibilities or conditions that will prevent the transport. This check is provided by the DBMS_TDB package, procedure call is check_db.

This procedure accepts 2 arguments

Argument	Purpose
target_platform_name	The name of the target platform as it appears in v$transportable_platform.platform_name.
skip_option	Allows skipping of specific types of tablespaces. Supported values SKIP_OFFLINE – skips offline tablespaces SKIP_READONLY – skips read-only. SKIP_NONE – checks all tablespaces.

Below the check returns successfully and prints the ‘tdb check ok’ message, so this database is ready for conversion

  1  declare

  2   tdb_check boolean;

  3  begin

  4    tdb_check:=dbms_tdb.check_db('Microsoft Windows IA (64-bit)',dbms_tdb.SKIP_NONE);

  5    if (tdb_check) then

  6       dbms_output.put_line('tdb check ok');

  7    else

  8       dbms_output.put_line('tdb check not ok');

  9    end if;

 10* end;

SQL> /

tdb check ok

PL/SQL procedure successfully completed.

Convert database clauses

Clause	Purpose
CONVERT DATABASE	Performs the conversion of the target.
New database ‘newdb’	Sets the name of the new database.
Transport script ‘/home/oracle/transport.sql’	Name of the transport script (basically a create controlfile script, because controlfile cannot be transported to another platform)
To platform ‘Microsoft Windows IA (64-bit)’	Identifies the target platform for conversion
Db_file_name_convert=’source-path’,destn-path’	Identifies the source datafiles and destination file path

In the example below, after connecting to rman, the convert database command is issued and RMAN proceeds to convert the database. Because ON DESTINATION PALTFORM is not specified, the conversion is performed at-source.

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Oct 29 16:01:45 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: NONCDB1 (DBID=1929467856)

RMAN> convert database

  new database 'newdb'

  transport script '/home/oracle/transport.sql'

  to platform 'Microsoft Windows IA (64-bit)'

db_file_name_convert='/u01/app/oracle/oradata/noncdb1','/u01/app/oracle/oradata/win64/noncdb1';

2> 3> 4> 5>

Starting conversion at source at 28-OCT-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=11 device type=DISK

External table SYS.OPATCH_XML_INV found in the database

Directory SYS.XSDDIR found in the database

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.OPATCH_INST_DIR found in the database

Directory SYS.OPATCH_SCRIPT_DIR found in the database

Directory SYS.OPATCH_LOG_DIR found in the database

Directory SYS.ORACLE_BASE found in the database

Directory SYS.ORACLE_HOME found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00001 name=/u01/app/oracle/oradata/noncdb1/system.ora

converted datafile=/u01/app/oracle/oradata/win64/noncdb1/system.ora

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

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00003 name=/u01/app/oracle/oradata/noncdb1/undo.ora

        converted datafile=/u01/app/oracle/oradata/win64/noncdb1/undo.ora

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

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00002 name=/u01/app/oracle/oradata/noncdb1/sysaux.ora

converted datafile=/u01/app/oracle/oradata/win64/noncdb1/sysaux.ora

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

channel ORA_DISK_1: starting datafile conversion                                    

input datafile file number=00004 name=/u01/app/oracle/oradata/noncdb1/users.ora

converted datafile=/u01/app/oracle/oradata/win64/noncdb1/users.ora

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

Edit init.ora file /u01/app/oracle/product/12.1.0/dbhome_2/dbs/init_00pm746n_1_0.ora. This PFILE will be used to create the database on the target platform

Run SQL script /home/oracle/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 28-OCT-14

The output has some post-conversion steps with instructions, shown in bold above

RMAN has written out a temp parmeter file (init_00pm746n_1_0.ora ) to start up the new instance in the new platform.

Also RMAN has written out transport script (transport.sql) which has commands to create the controlfile on the new destination platform, mount, and recompile it. The steps to be executed are shown below.

1. Edit the dummy pfile that it has created  in order to make it work on destination platform. The screenshot below shows the pfile with instructions to review/change the parameters.

In this example, after the pfile has been edited as described above, and the changed line looks like this:

  Db_name=newdb

  Compatible=12.1.0.0.0

  control_files            = 'c:\temp\control1.ctl','c:\temp\control2.ctl'

  sga_target=1000m

  pga_aggregate_target=900m

2. It has created a transport script, which has a create controlfile statement and commands to start up the database and recompile its modules. The transport script must be run against the converted databse after editing the pfile mentioned above and using it to start the database instance. Before running the transport script the unix-style paths in the CREATE CONTROLFILE statement must be edited to match the destination platform. The main parameter is the control_files parameter which sets the stage for creation of the control file. Also the parameter file needs to be edited to allocate sufficient sga/pga memory, otherwise the instance wont come up.

 

Since it’s a Windows host, a Windows service needs to be defined on the Windows destination using oradim, as shown.

After the service is created the next step is to start up the instance in nomount mode and create the controlfile

SQL> startup nomount pfile='init.ora';

ORACLE instance started.

Total System Global Area 1048576000 bytes

Fixed Size                  3053584 bytes

Variable Size             222300144 bytes

Database Buffers          813694976 bytes

Redo Buffers                9527296 bytes

Having started up the instance, the controlfile needs to be created. The CREATE CONTROLFILE from the transport script created by RMAN needs to be edited (to modify the file locations from unix to windows paths). Then the control file can be created and that statement will automatically mount the database.

SQL> CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 6

  3      MAXLOGMEMBERS 2

  4      MAXDATAFILES 100

  5      MAXINSTANCES 2

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 'c:\temp\newdb\redo1.ora'  SIZE 20M ,

  9    GROUP 2 'c:\temp\newdb\redo2.ora'  SIZE 20M

 10  DATAFILE

 11    'c:\temp\newdb\system.ora',

 12    'c:\temp\newdb\sysaux.ora',

 13    'c:\temp\newdb\undo.ora',

 14    'c:\temp\newdb\users.ora'

 15  CHARACTER SET US7ASCII

 16  ;

Control file created.

There are a few last steps in the transport script after the control file is created.

• Edit the scripts as required for the Windows environment.

After controlfile creation, the script opens the database with the resetlogs and upgrade option.

Then it adds a tempfile for the default temporary tablespace.  Restart in upgrade mode and performs invalidation of database objects using utlirp. Then there is a final restart and compilation in normal mode using utlrp.sql.

After utlrp.sql finishes execution, the database is ready. If needed, the nid tool can be used to assign a new DBID to the database.