Windows to Linux Migration

--------------------------------------------------------------------------------------------------------------------------------------------

Cross-Platform Migration on Destination Host Using Rman Convert Database [ID 414878.1]

--------------------------------------------------------------------------------------------------------------------------------------------

migration from Windows 32-bit to Linux 32-bit:
-------------------------------------------------

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 Resetogs 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.
4) The Source and the target database version must be equal / greater than 10.2.0. version


--------------------------------------------------------------------------------------------------------------------------------------------


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 (32-bit). As seen below both belong to Little endian format:


SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux 64-bit for AMD Little
Apple Mac OS Big
Microsoft Windows 64-bit for AMD Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big

17 rows selected.

SQL>SELECT USERENV ('language') FROM DUAL;


USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1


SQL> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0

20 rows selected.

2) Open the database in read only mode

SQL> alter database open read only;

Database altered.


3) 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> set serveroutput on
SQL> 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.

4) 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
external boolean;
begin
external := dbms_tdb.check_external;
end;
/

The following directories exist in the database:
SYS.DATA_PUMP_DIR

PL/SQL procedure successfully completed.


SQL> alter database backup controlfile to trace;


5) Now once 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.
Reasons for doing this include:


+ Avoiding any performance overhead on the source host due to the conversion process.
+ Distributing a database from one source system to multiple recipients on several different platforms.


6) Run the RMAN CONVERT DATABASE command on the source platform specifying the ON TARGET PLATFORM argument.
When used in this manner, the command syntax is as follows:


create a folder in OS level temporarily to place the script. EXAMPLE: 'E:\mig\'

C:\ Rman target sys/prod@prod nocatalog


RMAN>CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT 'E:\mig\convertscript.rman'
TRANSPORT SCRIPT 'E:\mig\transportscript.sql'
new database 'orcllnx'
FORMAT 'E:\mig\%U';


Starting convert at 12-FEB-07
using channel ORA_DISK_1

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=D:\ORACLE\ORADATA\ORA10G\DATA\SYSTEM.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=D:\ORACLE\ORADATA\ORA10G\DATA\UNDOTBS1.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=D:\ORACLE\ORADATA\ORA10G\DATA\SYSAUX.DBF
channel ORA_DISK_1: datafile checking complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting to check datafiles
input datafile fno=00004 name=D:\ORACLE\ORADATA\ORA10G\DATA\USERS.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_00I9SC77_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 12-FEB-07



The CONVERT DATABASE ON TARGET PLATFORM produces a transport script containing SQL*Plus commands
to create a new database on the destination platform, and a PFILE for the new database containing
the same settings as the source database.The pfile will be generated in location specified by the Format command.

Note: that CONVERT DATABASE ON TARGET PLATFORM does not produce converted datafile copies.


7) Now copy the following files to the destination machine manually :


+ Datafiles(here i moved to '/ora10gdata/orcllnx/mig' location )
+ Transport.sql
+ Convertscript.rman
+ Pfile generated by the convert database command.
+ password file
+ udump trace file(alter database backup controlfile to trace)

8) Edit the PFILE moved on the destination host to change the environment specific parameters.Change the password file name to a new database name.

9) Create a dummy Controlfile (from udump trace file)


sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 12 20:10:52 2007

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 201326592 bytes
Fixed Size 1218484 bytes
Variable Size 67110988 bytes
Database Buffers 125829120 bytes
Redo Buffers 7168000 bytes

SQL>CREATE CONTROLFILE REUSE SET DATABASE 'orcllnx' RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ora10gdata/orcllnx/mig/REDO01.LOG' SIZE 50M,
GROUP 2 '/ora10gdata/orcllnx/mig/REDO02.LOG' SIZE 50M,
GROUP 3 '/ora10gdata/orcllnx/mig/REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
-- GROUP 4 '/ora10gdata/orcllnx/mig/REDO04.LOG' SIZE 50M,
-- GROUP 5 '/ora10gdata/orcllnx/mig/REDO05.LOG' SIZE 50M,
-- GROUP 6 '/ora10gdata/orcllnx/mig/REDO06.LOG' SIZE 50M
DATAFILE
'/ora10gdata/orcllnx/mig/SYSTEM01.DBF',
'/ora10gdata/orcllnx/mig/UNDOTBS01.DBF',
'/ora10gdata/orcllnx/mig/SYSAUX01.DBF',
'/ora10gdata/orcllnx/mig/USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;


Note : 1) This datafiles location is where u have copied the datafiles from windows to linux temporarily.
----- EX: '/ora10gdata/orcllnx/mig' --> temporary location

2) USE , 'REUSE'-'SET' commands in I'st line of creation of controlfile script.


10) Now edit the file Convertscript.rman and make necessary changes with respect to the filesystem and the file names.
Now once the changes are done run the script from rman prompt


[oracle@localhost ~]$ rman target / nocatalog @/ora10gdata/orcllnx/mig/CONVERTSCRIPT.RMAN

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Apr 11 18:43:36 2010

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

connected to target database: ORCLLNX (DBID=127554213, not open)
using target database control file instead of recovery catalog

RMAN> RUN {
2>
3> CONVERT DATAFILE '/ora10gdata/orcllnx/mig/SYSTEM01.DBF'
4> FROM PLATFORM 'Microsoft Windows IA (32-bit)'
5> FORMAT '/ora10gdata/orcllnx/SYSTEM01.DBF' ;
6>
7>
8> CONVERT DATAFILE '/ora10gdata/orcllnx/mig/SYSAUX01.DBF'
9> FROM PLATFORM 'Microsoft Windows IA (32-bit)'
10> FORMAT '/ora10gdata/orcllnx/SYSAUX01.DBF' ;
11>
12>
13> CONVERT DATAFILE '/ora10gdata/orcllnx/mig/UNDOTBS01.DBF'
14> FROM PLATFORM 'Microsoft Windows IA (32-bit)'
15> FORMAT '/ora10gdata/orcllnx/UNDOTBS01.DBF' ;
16>
17>
18> CONVERT DATAFILE '/ora10gdata/orcllnx/mig/USERS01.DBF'
19> FROM PLATFORM 'Microsoft Windows IA (32-bit)'
20> FORMAT '/ora10gdata/orcllnx/USERS01.DBF' ;
21>
22>
23> }
24>
25>
Starting backup at 11-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/ora10gdata/orcllnx/mig/SYSTEM01.DBF
converted datafile=/ora10gdata/orcllnx/SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:46
Finished backup at 11-APR-10

Starting backup at 11-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/ora10gdata/orcllnx/mig/SYSAUX01.DBF
converted datafile=/ora10gdata/orcllnx/SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 11-APR-10

Starting backup at 11-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/ora10gdata/orcllnx/mig/UNDOTBS01.DBF
converted datafile=/ora10gdata/orcllnx/UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 11-APR-10

Starting backup at 11-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/ora10gdata/orcllnx/mig/USERS01.DBF
converted datafile=/ora10gdata/orcllnx/USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 11-APR-10



10) Now shutdown the database and delete the dummy controlfile.

11) Now edit the TRANSPORT sql script to reflect the new path for datafiles and redolog files 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.


12) 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 ora10g]$ export ORACLE_HOME=/ora10gsoft/10.2.0/
[oracle@test-br ora10g]$ export ORACLE_SID=orcllnx
[oracle@test-br ora10g]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@test-br 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> @TRANSPORTSCRIPT.SQL
ORACLE instance started.

Total System Global Area 201326592 bytes
Fixed Size 1218484 bytes
Variable Size 67110988 bytes
Database Buffers 125829120 bytes
Redo Buffers 7168000 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 201326592 bytes
Fixed Size 1218484 bytes
Variable Size 67110988 bytes
Database Buffers 125829120 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
...
...


--------------------------------------------------------------------------------------------------------------------------------------------


SAMPLE MODIFIED 'TRANSPORT.SQL' FILE

--------------------------------------------------------------------------------------------------------------------------------------------



-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

STARTUP NOMOUNT PFILE='/ora10gsoft/10.2.0/dbs/initorcllnx.ora'
CREATE CONTROLFILE REUSE SET DATABASE 'orcllnx' RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ora10gdata/orcllnx/REDO01.LOG' SIZE 50M,
GROUP 2 '/ora10gdata/orcllnx/REDO02.LOG' SIZE 50M,
GROUP 3 '/ora10gdata/orcllnx/REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
-- GROUP 4 '/ora10gdata/orcllnx/REDO04.LOG' SIZE 50M,
-- GROUP 5 '/ora10gdata/orcllnx/REDO05.LOG' SIZE 50M,
-- GROUP 6 '/ora10gdata/orcllnx/REDO06.LOG' SIZE 50M
DATAFILE
'/ora10gdata/orcllnx/SYSTEM01.DBF',
'/ora10gdata/orcllnx/UNDOTBS01.DBF',
'/ora10gdata/orcllnx/SYSAUX01.DBF',
'/ora10gdata/orcllnx/USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP01 ADD TEMPFILE '/ora10gdata/orcllnx/TEMP01.DBF'
SIZE 20971520 AUTOEXTEND OFF;
-- End of tempfile additions.
--

create spfile from pfile;

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your 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
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;



Note: If temporary tablespace is locally managed the drop and recreate the temporary tabelspace;
-----


SQL> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace


SQL> Create temporary TABLESPACE TEMP01 TEMPFILE '/ora10gdata/orcllnx/mig/TEMP01.DBF'
SIZE 20971520 AUTOEXTEND OFF;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

--------------------------------------------------------------------------------------------------------------------------------------------