Cross Platform Migration An Unproblematic Approach (Windows-UNIX ) Oracle 10\11g
The principal restriction on cross-platform transportable database is that the source and destination platform must share the same endian format.
Redo log files and control files from the source database are not transported to destination database . New control files and redo log files are created for the new database during the transport process, and an Open Resetogs is performed once the new database is created. BFiles, External tables and directories are not transported.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 448790528 bytes
Fixed Size 1291096 bytes
Variable Size 381684904 bytes
Database Buffers 58720256 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Linux IA (64-bit)');
5 end;
6 /
PL/SQL procedure successfully completed.
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.WORK_DIR, SYS.ADMIN_DIR, SYS.DATA_PUMP_DIR
PL/SQL procedure successfully completed.
SQL>
Now the database is ready for transport , make sure as to where you are going to convert the datafiles.You may choose to convert the datafiles of the
database being transported on the destination platform instead of the source platform.
in the source database connect to target catalog
RMAN> connect target /
connected to target database: ORCL (DBID=1169024393)
RMAN> CONVERT DATABASE ON TARGET PLATFORM
2> CONVERT SCRIPT 'C:\convertscript.rman'
3> TRANSPORT SCRIPT 'C:\transportscript.sql'
4> new database 'ORCLWIN'
5> FORMAT 'C:\%U';
Starting convert at 18-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
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 to check datafiles
input datafile fno=00001 name=C:\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00003 name=C:\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00002 name=C:\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00004 name=C:\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00005 name=C:\ORADATA\ORCL\SYSTEM02.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00006 name=C:\ORADATA\ORCL\SYSTEM03.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00007 name=C:\ORADATA\ORCL\SYSTEM04.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00008 name=C:\ORADATA\ORCL\SYSTEM05.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00009 name=C:\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00010 name=C:\ORADATA\ORCL\USERS03.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00011 name=C:\ORADATA\ORCL\USERS04.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
Run SQL script C:\TRANSPORTSCRIPT.SQL on the target platform to create database
Edit init.ora file C:\INIT_00JGMNV5_1_0.ORA. This PFILE will be used to create the database on the target platform
Run RMAN script C:\CONVERTSCRIPT.RMAN on target platform to convert datafiles
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 18-MAY-08
RMAN>
- Converting the datafiles
RUN {
CONVERT DATAFILE '\U01\ORADATA\ORCL\SYSTEM01.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\SYSTEM01.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\SYSAUX01.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\SYSAUX01.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\UNDOTBS01.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\UNDOTBS01.DBF' ;
CONVERT DATAFILE '\U01\ORADATA\ORCL\USERS01.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\USERS01.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\SYSTEM02.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\SYSTEM02.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\SYSTEM03.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\SYSTEM03.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\SYSTEM04.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\SYSTEM04.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\SYSTEM05.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\SYSTEM05.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\USERS02.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\USERS02.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\USERS03.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\USERS03.DBF';
CONVERT DATAFILE '\U01\ORADATA\ORCL\USERS04.DBF'
FROM PLATFORM 'Microsoft Windows IA (64-bit)'
FORMAT '\U01\ORADATA\ORCL\USERS04.DBF' ;
}
set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
prompt * database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='C:\initORCL.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\initORCL.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
Vinod Sadanandan
Sr.Oracle DBA