How to: Move/Convert Database from Linux to Windows

There are two basic methods to achieve this.

Method 1
========
Steps 1:
Check the ENDIAN format of the platforms. Both Windows and linux should have the same format.
In our case we are moving from Linux 32-bit to Windows 32-bit:
col PLATFORM_NAME format a40
select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
Linux IA (64-bit) Little
Microsoft Windows IA (64-bit) Little

Step 2:
Check if the database can be transported. We need to use DBMS_TDB.CHECK_DB, to check if our database can be
transported to the target OS, in the way it is currently.
Need to start the database in READ ONLY mode:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> set serveroutput on
SQL> declare
2 check_db boolean;
3 begin
4 check_db:=dbms_tdb.check_db('Microsoft Windows IA (32-bit)');
5 end;
6 /

PL/SQL procedure successfully completed.

As we see no errors or message, so our database is ready to be transported to Windows 32-bit.

Step 3:
Check if there are any external files associated with the database, they will not be transported using RMAN.
SQL> set serveroutput on
SQL> declare
2 chk boolean;
3 begin
4 chk:=dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.FOR_HR, SYS.BLOB_TEST, SYS.IDR_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR,
SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.AUDIT_DIR, SYS.DATA_PUMP_DIR,
SYS.ORACLE_OCM_CONFIG_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed.
The above directories exists in the database, we will need to recreate them with new locations once we complete the move.

Step 4:
Now we need to use RMAN convert database command to convert the source database to windows 32-bit.
The source database must be in read only mode.

RMAN> convert database new database 'ORCL'
2> transport script '/home/oracle/transport1.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/home/oracle/product/oradata/test' '/home/oracle/con_dbf';

Starting conversion at source at 23-MAR-09
using channel ORA_DISK_1

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.FOR_HR found in the database
Directory SYS.BLOB_TEST found in the database
Directory SYS.IDR_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.AUDIT_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/home/oracle/product/oradata/test/users01.dbf
converted datafile=/home/oracle/con_dbf/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:21
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/home/oracle/product/oradata/test/sysaux01.dbf
converted datafile=/home/oracle/con_dbf/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/home/oracle/product/oradata/test/system01.dbf
converted datafile=/home/oracle/con_dbf/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/home/oracle/product/oradata/test/example01.dbf
converted datafile=/home/oracle/con_dbf/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/home/oracle/product/oradata/test/undotbs01.dbf
converted datafile=/home/oracle/con_dbf/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Edit init.ora file /home/oracle/product/11g/db/dbs/init_00kal9vi_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /home/oracle/transport1.sql on the target platform to create database
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 conversion at source at 23-MAR-09

RMAN>

Below are our converted database files:

oracle@oracle:~/con_dbf$ pwd
/home/oracle/con_dbf
oracle@oracle:~/con_dbf$ ls -lrt
total 7267080
-rw-r----- 1 oracle oracle 5608382464 2009-03-23 16:17 users01.dbf
-rw-r----- 1 oracle oracle 882057216 2009-03-23 16:18 sysaux01.dbf
-rw-r----- 1 oracle oracle 744497152 2009-03-23 16:19 system01.dbf
-rw-r----- 1 oracle oracle 104865792 2009-03-23 16:20 example01.dbf
-rw-r----- 1 oracle oracle 94380032 2009-03-23 16:20 undotbs01.dbf
-rw-r--r-- 1 oracle oracle 1710 2009-03-23 17:01 init_00kal9vi_1_0.ora
-rw-r--r-- 1 oracle oracle 2698 2009-03-23 17:01 transport1.sql
Screen shot of the transport1.sql file:

Step 5:
FTP the files to the windows server. I have used filezilla to copy the files to the windows server.

Step 6:
Use oradim to create the service:
C:\Documents and Settings\Administrator>oradim -new -sid orcl -intpwd oracle -s
artmode manual -pfile C:\oracle\11g\product\11.1.0\db_1\dbs\init_orcl.ora
Instance created.

Use the init file (init_00kal9vi_1_0.ora) to startup nomount the db on the windows machine:
Make required changes to the paths in the init ora file.
Screen shot of the init.ora file:

C:\Documents and Settings\Administrator>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 23 17:31:25 2009

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

Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1333620 bytes
Variable Size 310380172 bytes
Database Buffers 104857600 bytes
Redo Buffers 6098944 bytes
SQL>

Step 7:
Create the control file:
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\oracle\oradata\ORCL\log1.rdo' SIZE 50M,
9 GROUP 2 'C:\oracle\oradata\ORCL\log2.rdo' SIZE 50M,
10 GROUP 3 'C:\oracle\oradata\ORCL\log3.rdo' SIZE 50M
11 DATAFILE
12 'C:\oracle\oradata\ORCL\system01.dbf',
13 'C:\oracle\oradata\ORCL\sysaux01.dbf',
14 'C:\oracle\oradata\ORCL\undotbs01.dbf',
15 'C:\oracle\oradata\ORCL\users01.dbf',
16 'C:\oracle\oradata\ORCL\example01.dbf'
17 CHARACTER SET AL32UTF8;

Control file created.

Step 8:
Open the database.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL> select name from v$database;
ERROR:
ORA-03114: not connected to ORACLE

Meanwhile and checked if the redo log files were created, Saw that they were created. Logged in again and:

SQL> select name from v$database;
NAME
---------
ORCL

SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

Step 9:
Add the tempfile.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oracle\oradata\ORCL\temp01.dbf' SIZE
54525952 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Step 10:
Do a sanity check of the database.
Run $ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2009-03-24 14:43:54
PL/SQL procedure successfully completed.
TIMESTAMP
-------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2009-03-24 14:43:56
PL/SQL procedure successfully completed.
OBJECTS WITH ERRORS
-------------------
0
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- ----------
ORCL READ WRITE

Database moved from linux to windows machine!!!!

Method 2
========
There another way to do the datafile conversion, we can convert the datafiles on the destination server also.
The rman script changes a little in that case.
RMAN> convert database on target platform
2> convert script '/home/oracle/convert.sql'
3> transport script '/home/oracle/transport.sql'
4> new database 'orcl'
5> format '/home/oracle/%U_%d';

The datafiles create by the above rman command should be copied to a temp directory on the destination server.
This will create a transport script to create the database instance on the destination server.
It will also create a rman script to convert the datafiles on the destination server.
It will look like:
run {
CONVERT DATAFILE 'C:\oracle\oradata\ORCL\SYSTEM.DBF'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT 'c:\temp\SYSTEM.DBF';
} ;
In the above case the files copied from the linux server were kept in c:\temp and then they would be converted to
C:\oracle\oradata\ORCL location.
After the conversion, run the transport.sql script after making the required changes.
After you have opened the database the rest of the steps are the same.