Rman Convert Database Cross Platform

RMAN CONVERT DATABASE cross Platform Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 Information in this document applies to any platform.

Goal Walk through RMAN CONVERT DATABASE feature in 10g Release 2 to accomplish cross platform database migration Note the following restrictions exist with Cross-Platform Transportable Database: 1) The principal restriction on cross-platform transportable database is that the source and destination platform must share the same endian format. 2) Redo log files and control files from the source database are not transported. New control files and redo log files are created for the new database during the transport process, and an OPEN RESETLOGS is performed once the new database is created. Similarly, tempfiles belonging to locally managed temporary tablespaces are not transported. The temporary tablespace will be re-created on the target platform when the transport script is run. 3) BFILEs, External tables and directories, Password files are not transported.

Solution 1) Check that the source and destination platform belong to same ENDIAN format in the view V$TRANSPORTABLE_PLATFORM. We will try to transport a database from Windows (32-bit) to Linux (32bit). As seen below both belong to Little endian format: SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM; PLATFORM_NAME ---------------------------------------Solaris[tm] OE (32-bit) Solaris[tm] OE (64-bit) Microsoft Windows IA (32-bit) Linux IA (32-bit) AIX-Based Systems (64-bit) HP-UX (64-bit) HP Tru64 UNIX HP-UX IA (64-bit) Linux IA (64-bit) HP Open VMS Microsoft Windows IA (64-bit) IBM zSeries Based Linux Linux 64-bit for AMD Apple Mac OS Microsoft Windows 64-bit for AMD Solaris Operating System (x86) IBM Power Based Linux 17 rows selected.

ENDIAN_FORMAT -------------Big Big Little Little Big Big Little Big Little Little Little Big Little Big Little Little Big

If the two platforms are not on the same endian format, you will need to use TRANSPORTABLE TABLESPACE instead of CONVERT DATABASE see Note 243304.1 10g : Transportable Tablespaces Across Different Platforms 2) Use DBMS_TDB.CHECK_DB to check whether the database can be transported to a desired destination platform, and whether the current state of the database (such as incorrect compatibility settings, in-doubt or active transactions) permits transport. Make sure your database is open in READ ONLY mode before you call DBMS_TDB.CHECK_DB. Since we need to transport to Linux we will call the procedure with following arguments: SQL> SQL> 2 3 4 5 6

set serveroutput on declare db_ready boolean; begin db_ready := dbms_tdb.check_db('Linux IA (32-bit)'); end; /

PL/SQL procedure successfully completed.

If you call DBMS_TDB.CHECK_DB and no messages are displayed indicating conditions preventing transport BEFORE the "PL/SQL procedure successfully completed message", then your database is ready for transport. 3) Use DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories or BFILEs. RMAN cannot automate the transport of such files as mentioned above. SQL> set serveroutput on SQL> declare 2 external boolean; 3 begin 4 external := dbms_tdb.check_external; 5 end; 6 / The following directories exist in the database: SYS.DATA_PUMP_DIR PL/SQL procedure successfully completed.

If there are no external objects, then this procedure completes with no output. If there are external objects, however, the output will be somewhat similar to above. 4) When the database is ready for transport, the RMAN CONVERT DATABASE command is run (when the source database is READ ONLY), specifying a destination platform and how to name the output files. RMAN produces the following files needed to move the database to the destination system: a) A complete copy of the datafiles of the database, ready to be transported b) A PFILE for use with the new database on the destination platform, containing settings used in the PFILE/SPFILE from the source database. c) A transport script, which contains SQL statements used to create the new database on the destination platform C:\>rman target / nocatalog Recovery Manager: Release 10.2.0.1.0 - Production on Fri Feb 2 17:05:35 2007 Copyright (c) 1982, 2005, Oracle.

All rights reserved.

connected to target database: ORA10G (DBID=3926158280) using target database control file instead of recovery catalog

RMAN> 2> 3> 4>

CONVERT DATABASE NEW DATABASE 'LinDB10g' TRANSPORT SCRIPT 'D:\oracle\oradata\dbTransport\transport.sql' TO PLATFORM 'Linux IA (32-bit)' DB_FILE_NAME_CONVERT '+DG_DATA/ora10g/datafile' 'D:\oracle\oradata\dbTransport';

Starting convert at 02-FEB-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=90 devtype=DISK Directory SYS.DATA_PUMP_DIR found in the database User SYS with SYSDBA and SYSOPER privilege found in password file channel ORA_DISK_1: starting datafile conversion input datafile fno=00001 name=+DG_DATA/ora10g/datafile/system.257.609084887 converted datafile=D:\ORACLE\ORADATA\DBTRANSPORT\SYSTEM.257.609084887 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile conversion input datafile fno=00002 name=+DG_DATA/ora10g/datafile/undotbs1.258.609084933 converted datafile=D:\ORACLE\ORADATA\DBTRANSPORT\UNDOTBS1.258.609084933 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile conversion input datafile fno=00003 name=+DG_DATA/ora10g/datafile/sysaux.259.609084957 converted datafile=D:\ORACLE\ORADATA\DBTRANSPORT\SYSAUX.259.609084957 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile conversion input datafile fno=00004 name=+DG_DATA/ora10g/datafile/users.260.609084973 converted datafile=D:\ORACLE\ORADATA\DBTRANSPORT\USERS.260.609084973 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07 Run SQL script D:\ORACLE\ORADATA\DBTRANSPORT\TRANSPORT.SQL on the target platform to create database Edit init.ora file D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00I92IKQ_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 02-FEB-07

Check the output related to the transport script and the parameter file for the new database at the end above. When CONVERT DATABASE completes, the source database may be opened read-write again. 5) Now copy the above files to the destination host. i.e. the converted datafiles, transport.sql script and the pfile generated. 6) The PFILE generated by RMAN will have following sections: a) Parameters that need to be changed for the destination host environment b) Other parameters which are same as the source database parameters Edit the PFILE moved on the destination host to change the environment specific parameters. 7) Now edit the TRANSPORT sql script to reflect the new path for datafiles in the CREATE CONTROLFILE section of the script. Also change all references to the INIT.ORA in the script to the new path and name of the INIT.ORA modified above. 8) Once the PFILE and TRANSPORT sql scripts are suitably modified invoke SQLPLUS on the destination host after setting the Oracle environment parameters and then run TRANSPORT.sql as:

[oracle@test-br [oracle@test-br [oracle@test-br [oracle@test-br [oracle@test-br

ora10g]$ export ORACLE_HOME=/u01/oracle/product/ora10g ora10g]$ export ORACLE_SID=LinDB10g ora10g]$ export PATH=$ORACLE_HOME/bin:$PATH ora10g]$ cd /u01/oracle/oradata/LinDB10g LinDB10g]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 3 01:55:46 2007 Copyright (c) 1982, 2005, Oracle.

All rights reserved.

Connected to an idle instance. SQL> @TRANSPORT.SQL ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers

201326592 1218484 67110988 125829120 7168000

bytes bytes bytes bytes bytes

Control file created. Database altered. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Your database has been created successfully! * There are many things to think about for the new database. Here * is a checklist to help you stay on track: * 1. You may want to redefine the location of the directory objects. * 2. You may want to change the internal database identifier (DBID) * or the global database name for this database. Use the * NEWDBID Utility (nid). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. Database opened.

201326592 1218484 67110988 125829120 7168000

bytes bytes bytes bytes bytes

... ... ... SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors; ERRORS DURING RECOMPILATION --------------------------0 SQL> SQL> SQL> Rem =====================================================================

SQL> SQL> SQL> SQL> SQL>

Rem Run component validation procedure Rem ===================================================================== SET serveroutput on EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SET serveroutput off Rem Rem Rem set

=========================================================================== END utlrp.sql =========================================================================== feedback 6;

select name, open_mode from v$database;

NAME OPEN_MODE --------- ---------LINDB10G READ WRITE

When the transport script finishes, the creation of the new database is complete.

References Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Note 243304.1 - 10g : Transportable Tablespaces Across Different Platforms