Performing Cross-Platform Database Transport

Starting with 10g R2 it is now possible to transport an entire database (not just a few tablespaces) using the convert database RMAN command.

In the below scenario we are converting from a Windows 32 bit system to a Linux x86-64 bit system.

First we check if both platforms (source and destination) have the same endianness
using the dbms_tdb package.

The database needs to be open in read only mode for the following procedure to
work.

WINDOWS>>set serveroutput on
WINDOWS>>declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Linux 64-bit for
AMD',dbms_tdb.skip_readonly);
5 end;
6 /

PL/SQL procedure successfully completed.

If they did not have the same endianness we would have got a message like the one shown below

WINDOWS>>declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Solaris[tm] OE
(64-bit)',dbms_tdb.skip_readonly);
5 end;
6 /

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

PL/SQL procedure successfully completed.

Next we need to identify any external objects which will not be transported.
These include external tables,directories or BFILES.

WINDOWS>>declare
2 external boolean;
3 begin
4 /* value of external is ignored, but with SERVEROUTPUT set to
ON
5 * dbms_tdb.check_external displays report of external objects
6 * on console */
7 external := dbms_tdb.check_external;
8 end;
9
10 /

The following directories exist in the database:
SYS.WORK_DIR, SYS.ADMIN_DIR, SYS.DATA_PUMP_DIR

RMAN> CONVERT DATABASE NEW DATABASE 'USYD'
2> transport script 'C:\temp\transportscript'
3> to platform 'Linux 64-bit for AMD'
4> db_file_name_convert 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\'
'C:\temp\USYD\'
5> ;

In the above command C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD is the
source destination for the datafiles and C:\temp\USYD\ is the folder in which
the files will be placed after they are converted. You can open this database in read write mode once the database is converted.

Starting convert at 16/NOV/06
using channel ORA_DISK_1

Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database
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=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSTEM01.DBF

converted datafile=C:\TEMP\USYD\SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time:
00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003
name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSAUX01.DBF

converted datafile=C:\TEMP\USYD\SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time:
00:00:27
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002
name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\UNDOTBS01.DBF

converted datafile=C:\TEMP\USYD\UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time:
00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004
name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\USERS01.DBF
converted datafile=C:\TEMP\USYD\USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time:
00:00:08
Run SQL script C:\TEMP\TRANSPORTSCRIPT on the target platform to create
database

Edit init.ora file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00I2I4PR_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 16/NOV/06

Since the source is a Windows Machine and the destination Linux x86-64
bit you have to change the location of the files in the transportscript created

Create an init.ora file called initUSYD.ora with the following parameters
control_files = "/u01/oradata/USYD/cf_D-USYD_id-1778429277_00gb9u2s"
instance_name = "USYD"
plsql_native_library_dir = "/tmp/convertdb/plsqlnld1"
db_name = "USYD"
sga_max_size =5000000
compatible='10.2.0'

eiger-> sqlplus

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Nov 20 17:10:28 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='/u01/oradata/USYD/initUSYD.ora'
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2069712 bytes
Variable Size 113249072 bytes
Database Buffers 50331648 bytes
Redo Buffers 6316032 bytes

Cut and paste the commands from the transportscript created.

SQL> CREATE CONTROLFILE REUSE SET DATABASE "USYD" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
MAXINSTANCES 8
5 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1
'/u01/oradata/USYD/ARCH_D-USYD_ID-768471617_S-212_T-1_A-600609988_00I2I4PR' SIZE 50M,
9 GROUP 2
'/u01/oradata/USYD/ARCH_D-USYD_ID-768471617_S-213_T-1_A-600609988_00I2I4PR' SIZE 50M,
10 GROUP 3
'/u01/oradata/USYD/ARCH_D-USYD_ID-768471617_S-211_T-1_A-600609988_00I2I4PR' SIZE 50M
11 DATAFILE
12 '/u01/oradata/USYD/SYSTEM01.DBF',
13 '/u01/oradata/USYD/UNDOTBS01.DBF',
14 '/u01/oradata/USYD/SYSAUX01.DBF',
15 '/u01/oradata/USYD/USERS01.DBF'
16 CHARACTER SET WE8MSWIN1252
17 ;

Control file created.

SQL> set numwidth 20
SQL> select distinct checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
--------------------
6631021

SQL> select distinct checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
--------------------
6631021

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/USYD/DATA_D-USYD_I-768471617_TS-TEMP_FNO-1_00I2I4PR' SIZE 27262976 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.