Step by step oracle database platform migration from Windows-64bit to Linux-64bit :
To convert the database from one platform to another, the endian format of both databases should be the same. So as a first step, check the v$transportable_platform view for both platforms.SQL> select a.VERSION,a.INSTANCE_NAME,b.PLATFORM_NAME from v$instance a, v$database b;
VERSION INSTANCE_NAME PLATFORM_NAME
----------------- ---------------- --------------------------------
11.2.0.1.0 prod Microsoft Windows x86 64-bit
SQL> select platform_name from v$db_transportable_platform;
PLATFORM_NAME
-------------------------------------
Microsoft Windows IA (32-bit)
Linux IA (32-bit)
HP Tru64 UNIX
Linux IA (64-bit)
HP Open VMS
Microsoft Windows IA (64-bit)
Linux x86 64-bit
Microsoft Windows x86 64-bit
Solaris Operating System (x86)
HP IA Open VMS
Solaris Operating System (x86-64)
11 rows selected.
It is seen from the output that both the Windows and Linux operating systems are in the little endian format. So in this case, RMANcan be easily used to convert the whole database.
start database with mount stage and open database with the read only option
----------------------------------------------------------------------------
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2180544 bytes
Variable Size 515902016 bytes
Database Buffers 331350016 bytes
Redo Buffers 6549504 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
Use dbms_tdb.check_db function to check whether the database can be transported to a target platform and the dbms_tdb.check_external function to check for existence of external objects, directories and BFILEs. Pass the name of the destination platform as a parameter to the first function. The return type of the function is boolean, so declare a variable with boolean type and call the function as follows:
SQL> set serveroutput on
SQL> declare
2 v_return boolean;
3 begin
4 v_return:=dbms_tdb.check_db('Linux x86 64-bit');
5 end;
6 /
PL/SQL procedure successfully completed.
If nothing was returned, then it means that the database is ready to be transported to the destination platform.
----------------------------------------------------------------------------------------------------------------
Now call the second function dbms_tdb.check_external:
SQL> declare
2 v_return boolean;
3 begin
4 v_return:=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.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR, SYS.DATA_FILE_DIR,
SYS.LOG_FILE_DIR, SYS.MEDIA_DIR, SYS.SS_OE_XMLDIR, SYS.SUBDIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA
PL/SQL procedure successfully completed.
SQL>
Run the convert database command to convert the whole database to the Linux platform.
------------------------------------------------------------------------------------
C:\Users\muthu>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 21 18:40:46 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=297504150)
RMAN> convert database new database 'PRODNEW'
2> transport script 'c:\app\transport.sql'
3> db_file_name_convert 'C:\app\oradata\prod'
4> 'c:\app'
5> to platform 'Linux x86 64-bit';
Starting conversion at source at 21-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
External table SH.SALES_TRANSACTIONS_EXT found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.SS_OE_XMLDIR found in the database
Directory SYS.SUBDIR 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=00001 name=C:\APP\ORADATA\PROD\SYSTEM01.DBF
converted datafile=C:\APP\SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=C:\APP\ORADATA\PROD\SYSAUX01.DBF
converted datafile=C:\APP\SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=C:\APP\ORADATA\PROD\UNDOTBS01.DBF
converted datafile=C:\APP\UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=C:\APP\ORADATA\PROD\EXAMPLE01.DBF
converted datafile=C:\APP\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=00004 name=C:\APP\ORADATA\PROD\USERS01.DBF
converted datafile=C:\APP\USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\INIT_00QCKDEH_1_0.ORA. This PFILE will be used to create the database on the
target platform
Run SQL script C:\APP\TRANSPORT.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 21-JUL-15
RMAN> exit
While performing the convert database command, RMAN does not convert and transfer redo log files, controlfiles, password files and temporary tablespaces to the destination platform.
RMAN converted all datafiles to the destination platform type. Now copy the parameter file transport.sql script, which is located at the $ORACLE_HOME/dbs directory, that is used to create the database and all datafiles to the destination host.
Perform the following prerequisite actions on the destination host before running the transport.sql.
----------------------------------------------------------------------------------------------------
[root@prodnew /]# mkdir oracle
[root@prodnew /]# chown -R oracle:oinstall oracle/
[root@prodnew /]# chmod -R 755 oracle/
[root@prodnew /]# su - oracle
[oracle@prodnew ~]$ mkdir -p /oracle/product/11.2.0/admin
[oracle@prodnew ~]$ mkdir -p /oracle/product/11.2.0/admin/adump
[oracle@prodnew ~]$ cd /oracle/product/11.2.0/dbs/
[oracle@prodnew ~]$ cp -r /oracle/*.DBF /oracle/product/oradata/
Move all datafiles to the necessary folder. Edit parameter file and convert paths from Windows syntax to the Linux syntax:
---------------------------------------------------------------------------------------------------------------------------
diagnostic_dest=/oracle/product/11.2.0/admin
control_files=("/oracle/product/oradata/control01.ctl", "/oracle/product/oradata/control02.ctl")
db_recovery_file_dest=/oracle/product/11.2.0
audit_file_dest=/oracle/product/11.2.0/admin/adump
local_listener=LISTENER_PROD
[oracle@prodnew dbs]$ vi initPRODNEW.ora ----- Copy init file from Windows to linux then change the parameter
[oracle@prodnew dbs]$ cd ../network/admin/
[oracle@prodnew admin]$ vi listener.ora ---- Copy listner file Windows to linux then change the parameter
[oracle@prodnew admin]$ vi tnsnames.ora ---- Copy tnsname file Windows to linux then change the parameter
[oracle@prodnew admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-JUL-2015 00:20:35
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/product/11.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/product/11.2.0/network/admin/listener.ora
Log messages written to /oracle/product/11.2.0/admin/diag/tnslsnr/prodnew/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prodnew.muthu.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 22-JUL-2015 00:20:36
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/network/admin/listener.ora
Listener Log File /oracle/product/11.2.0/admin/diag/tnslsnr/prodnew/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prodnew.muthu.com)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@prodnew admin]$ tnsping PRODNEW
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JUL-2015 00:20:39
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRODNEW.muthu.com)))
OK (50 msec)
Edit transport.sql script and correct paths of datafiles, controlfiles and trace directories.
---------------------------------------------------------------------------------------------
CREATE CONTROLFILE SET DATABASE "PRODNEW" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/product/oradata/REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oracle/product/oradata/REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/product/oradata/REDO03.LOG' SIZE 50M BLOCKSIZE 512
DATAFILE
'/oracle/product/oradata/SYSTEM01.DBF',
'/oracle/product/oradata/SYSAUX01.DBF',
'/oracle/product/oradata/UNDOTBS01.DBF',
'/oracle/product/oradata/USERS01.DBF',
'/oracle/product/oradata/EXAMPLE01.DBF'
CHARACTER SET AL32UTF8
;
Now check all changes made above once more, export the ORACLE_SID environment variable and run the transport.sql command from SQL*Plus:
---------------------------------------------------------------------------------------------------------------------------------------
[oracle@prodnew dbs]$ export ORACLE_HOME=/oracle/product/11.2.0
[oracle@prodnew dbs]$ export ORACLE_SID=PRODNEW
[oracle@prodnew dbs]$ export PATH=/oracle/product/11.2.0/bin:$PATH
[oracle@prodnew dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 22 00:21:17 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2218152 bytes
Variable Size 503318360 bytes
Database Buffers 343932928 bytes
Redo Buffers 6512640 bytes
SQL> CREATE CONTROLFILE SET DATABASE "PRODNEW" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
GROUP 1 '/oracle/product/oradata/REDO01.LOG' SIZE 50M BLOCKSIZE 512,
8 9 GROUP 2 '/oracle/product/oradata/REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/product/oradata/REDO03.LOG' SIZE 50M BLOCKSIZE 512
10 11 DATAFILE
12 '/oracle/product/oradata/SYSTEM01.DBF',
13 '/oracle/product/oradata/SYSAUX01.DBF',
14 '/oracle/product/oradata/UNDOTBS01.DBF',
'/oracle/product/oradata/USERS01.DBF',
15 16 '/oracle/product/oradata/EXAMPLE01.DBF'
CHARACTER SET AL32UTF8
17 18 ;
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/product/oradata/temp01.dbf' size 500M;
Tablespace altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2218152 bytes
Variable Size 503318360 bytes
Database Buffers 343932928 bytes
Redo Buffers 6512640 bytes
Database mounted.
Database opened.
SQL> @@ ?/rdbms/admin/utlirp.sql
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2218152 bytes
Variable Size 503318360 bytes
Database Buffers 343932928 bytes
Redo Buffers 6512640 bytes
Database mounted.
Database opened.
SQL> @ ?/rdbms/admin/utlrp.sql
SQL> select a.VERSION,a.INSTANCE_NAME,b.PLATFORM_NAME from v$instance a, v$database b;
VERSION INSTANCE_NAME PLATFORM_NAME
----------------- ---------------- -------------------
11.2.0.1.0 PRODNEW Linux x86 64-bit
SQL>
