Oracle 10g Database Migration From Windows to Linux
This document describes the procedure required to migrate a database from Windows to Linux using the RMAN Convert Database command.
Both Windows and Linux platforms have the same endian format, which makes possible to transfer the whole database,making the migration process very straightforward and simple.
I have taken following into assumption.
RDBMS with same version is already there in Linux server. If its not present then Install oracle10g same version as we have at windows machine. Although upgradation is also possible.
Check platform compatibility between source and target OS
SQL> select * from V$DB_TRANSPORTABLE_PLATFORM
where PLATFORM_NAME=’Microsoft Windows IA (32-bit)’ or
PLATFORM_NAME like ‘Linux%’
/
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– ——————————————— ————–
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
- 2. Start the database in read only mode
In order to execute dbms_tdb.check_db the database must be opened on read only mode.
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open read only;
3. Check database readiness for transport from Windows to Linux
If the execution of dbms_tdb.check_db does not return any exceptions, that means the database is ready for transport to the target platform.
SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db(‘Linux IA (32-bit)’);
5 end;
6 /
PL/SQL procedure successfully completed.
4. Check if there are any external objects
If there is any external objects take note of them, they will need to be taken care
manually
SQL> set serveroutput on
SQL> 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 /
The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR, SYS.ADMIN_DIR, SYS.WORK_DIR
PL/SQL procedure successfully completed.
5. Using the RMAN CONVERT DATABASE Command
Having executed successfully the checkup steps, the database is open in read only mode, then the convert database command can be executed with Rman.
I am changing my database name from “WIN1” to “LIN1”.There are 2 options suggested you can follow any one of them as per your requirement.
C:\Documents and Settings\ssethia>rman target / nocatalog
First option is
RMAN> CONVERT DATABASE NEW DATABASE ‘LIN1′
2> transport script ‘F:\transportscript’
3> to platform ‘Linux IA (32-bit)’;
Your datafiles and Initfile will copy to $ORACLE_HOME/database Like..
At location “F:\oracle\product\10.2.0\db_1\database”
You will find datafiles with name like
“DATA_D-WIN1_I-3182306851_TS-SYSTEM_FNO-1_01KK4H9T”
And initfile like
“INIT_00KK4H9T_1_0.ORA”
Importantly you will find transport script ‘F:\transportscript’
Second option is
CONVERT DATABASE NEW DATABASE ‘LIN1′
transport script ‘F:\transportscript_sum1.sql’
to platform ‘Linux IA (32-bit)’
db_file_name_convert ‘F:\oracle\product\10.2.0\oradata\Win1′,’F:\LIN1_data’; ;
In this command you can define the target location.like “F:\LIN1_data”
I am using first option.
PLEASE Don’t run “transportscript” at this point.
– 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 PERFORMANCE
STARTUP NOMOUNT PFILE=’/vol2/oracle/product/10.2.0/db_1/database/INIT_00KK4H9T_1_0.ORA’
– Create SPFILE
CREATE SPFILE FROM PFILE = ‘/vol2/oracle/product/10.2.0/db_1/database/INIT_00KK4H9T_1_0.ORA’;
STARTUP FORCE NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE ‘LIN1′ RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/vol2/oracle/product/10.2.0/db_1/database/ARCH_D-LIN1_ID-3182306851_S-2_T-1_A-692205924_00KK4H9T’ SIZE 50M,
GROUP 2 ‘/vol2/oracle/product/10.2.0/db_1/database/ARCH_D-LIN1_ID-3182306851_S-0_T-1_A-692205924_00KK4H9T’ SIZE 50M,
GROUP 3 ‘/vol2/oracle/product/10.2.0/db_1/database/ARCH_D-LIN1_ID-3182306851_S-1_T-1_A-692205924_00KK4H9T’ SIZE 50M
DATAFILE
‘/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-SYSTEM_FNO-1_01KK4H9T’,
‘/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-UNDOTBS1_FNO-2_04KK4HBC’,
‘/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-SYSAUX_FNO-3_02KK4HAM’,
‘/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-USERS_FNO-4_05KK4HBG’,
‘/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-EXAMPLE_FNO-5_03KK4HB5′
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 TEMP ADD TEMPFILE ‘/vol2/oracle/product/10.2.0/db_1/database/DATA_D-LIN1_I-3182306851_TS-TEMP_FNO-1_00KK4H9T’ SIZE 20M;
– End of tempfile additions.
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;
6. Copy Converted Datafiles, Generated Transport Script and Parameter File to the Linux
We need to copy over the generated files to the Linux server, they include all converted datafiles, the transport
script and the generated pfile.
If needed create at this point the directories you will need on the Linux server, for dump destination and flash
recovery area i.e.:
mkdir –p /vol2/database/LIN1/FRA
mkdir -p /vol2/oracle/app/admin/LIN1/adump
mkdir -p /vol2/oracle/app/admin/LIN1/bdump
mkdir -p /vol2/oracle/app/admin/LIN1/cdump
mkdir -p /vol2/oracle/app/admin/LIN1/udump
7. Copy/FTP the files to the required LINUX server
You can use ftp to copy the required files to the Linux server.
[sumit::/vol2/oracle/product/10.2.0/db_1/database] $ls -lrt
total 1227560
-rw-rw-r– 1 sumit sumit 3088 2009-07-14 16:16 TRANSPORTSCRIPT
-rw-rw-r– 1 sumit sumit 1456 2009-07-14 16:39 INIT_00KK4H9T_1_0.ORA
-rw-rw—- 1 sumit sumit 52429312 2009-07-14 22:40 ARCH_D-LIN1_ID-3182306851_S-2_T-1_A-692205924_00KK4H9T
-rw-rw—- 1 sumit sumit 52429312 2009-07-14 22:44 ARCH_D-LIN1_ID-3182306851_S-0_T-1_A-692205924_00KK4H9T
-rw-rw-r– 1 sumit sumit 5251072 2009-07-14 22:50 DATA_D-WIN1_I-3182306851_TS-USERS_FNO-4_05KK4HBG
-rw-rw-r– 1 sumit sumit 104865792 2009-07-14 22:50 DATA_D-WIN1_I-3182306851_TS-EXAMPLE_FNO-5_03KK4HB5
-rw-rw—- 1 sumit sumit 20979712 2009-07-14 22:51 DATA_D-LIN1_I-3182306851_TS-TEMP_FNO-1_00KK4H9T
-rw-rw-r– 1 sumit sumit 241180672 2009-07-15 00:18 DATA_D-WIN1_I-3182306851_TS-SYSAUX_FNO-3_02KK4HAM
-rw-rw-r– 1 sumit sumit 225452032 2009-07-15 00:20 DATA_D-WIN1_I-3182306851_TS-UNDOTBS1_FNO-2_04KK4HBC
-rw-rw-r– 1 sumit sumit 513810432 2009-07-15 00:21 DATA_D-WIN1_I-3182306851_TS-SYSTEM_FNO-1_01KK4H9T
-rw-rw—- 1 sumit sumit 52429312 2009-07-15 00:21 ARCH_D-LIN1_ID-3182306851_S-1_T-1_A-692205924_00KK4H9T
-rw-rw—- 1 sumit sumit 7389184 2009-07-15 00:21 CF_D-LIN1_ID-3182306851_00KK4H9T
8. Edit init.ora and Transport Script for new database
You need to make directory structure related changes and replace “ “ “ double codes with “ ‘ “ single code.
9. Execute the Transport Script
SQL> @transport
Script will perform followings:-
CREATE CONTROLFILE
DATABASE OPEN RESETLOGS
ADD TEMPFILES
And
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
10. Change database identifier
To change the database identifier you need to use the NEWDBID utility “nid”. It is run from within Sqlplus having
the database mounted:
sqlplus “/ as sysdba”
startup mount
exit
To change the DBID
cd $ORACLE_HOME/bin
./nid target=/
11. Check database integrity
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
6 rows selected.
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-EXAMPLE_FN
O-5_03KK4HB5
/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-USERS_FNO-
4_05KK4HBG
/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-SYSAUX_FNO
-3_02KK4HAM
/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-UNDOTBS1_F
NO-2_04KK4HBC
FILE_NAME
——————————————————————————–
/vol2/oracle/product/10.2.0/db_1/database/DATA_D-WIN1_I-3182306851_TS-SYSTEM_FNO
-1_01KK4H9T
SQL> SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;
COMP_NAME STATUS
————————————————– ———–
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
COMP_NAME STATUS
————————————————– ———–
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID
17 rows selected.
Sumit Sethia