Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 22 September 2012

Database Migration from Windows to Linux Using RMAN

Database: Oracle 10.2.0.4
Platform: Windows to Linux
Overview: To Migrate database first step is check the endian format. Endian format indicates the order, in which, sequence of bytes are stored in computer memory. There are 2 endian format named "Little Endian" and "Big Endian". Linux, Windows use "Little Endian" byte order whereas Solaris, HPUX, Apple Mac use "Big Endian" byte order. If it is same endian format migration is some how easy or straight forward.
SQL> Select * from v$transportable_platform order by platform_id;
Check the Version of database:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Check other database details:
SQL> Select name from v$datafile;
NAME
---------------------------------------
D:\ORACLE\ORADATA\MUJAZORC\SYSTEM01.DBF
D:\ORACLE\ORADATA\MUJAZORC\UNDOTBS01.DBF
D:\ORACLE\ORADATA\MUJAZORC\DRSYS01.DBF
D:\ORACLE\ORADATA\MUJAZORC\INDX01.DBF
D:\ORACLE\ORADATA\MUJAZORC\TOOLS01.DBF
D:\ORACLE\ORADATA\MUJAZORC\USERS01.DBF
D:\ORACLE\ORADATA\MUJAZORC\XDB01.DBF
D:\ORACLE\ORADATA\MUJAZORC\MUJ_HRMS01.DBF
D:\ORACLE\ORADATA\MUJAZORC\MUJ_FIN01.DBF
D:\ORACLE\ORADATA\MUAJZREG_IT\MUJ_REG_DBF01.DBF
D:\ORACLE\ORADATA\KAFAFIN\KAFA_FIN01.DBF
SQL> select name from v$controlfile;
NAME
------------------------------
D:\ORACLE\ORADATA\MUJAZORC\CONTROL01.CTL
D:\ORACLE\ORADATA\MUJAZORC\CONTROL02.CTL
D:\ORACLE\ORADATA\MUJAZORC\CONTROL03.CTL
SQL> select member from v$logfile;
MEMBER
------------------------------
D:\ORACLE\ORADATA\MUJAZORC\REDO01.LOG
D:\ORACLE\ORADATA\MUJAZORC\REDO02.LOG
D:\ORACLE\ORADATA\MUJAZORC\REDO03.LOG
Create some object for further verification
Create table date1 (today_date date);
Insert into date1 (select sysdate from dual);
Steps:
1. Check platform compatibility between Source and target OS
2. Start the database in read only mode
3. Check database readiness for transport from Windows to Linux using DBMS_TDB.CHECK_DB
4. Check if there are any external objects
5. Execute the RMAN Convert database command
6. Copy Converted datafiles, Generated Script and Pfile to Linux
7. Edit the Pfile for the new database (Linux)
8. Edit the Transport Script and Pfile changing the windows paths to Linux Paths.
9. Execute the Transport Script on Linux
10. Change the DBID
11. Run utlirp.sql and utlrp.sql for recompile all Pl/SQL modules.
12. Verify & Compare the database on Linux

1. Check platform Compatibility between source and target OS:
First we need to check the platforms to be sure they have the same endian format, also we need to save the PLATFORM_NAME string to use it later as part of the convert database syntax in RMAN.
SQL> select * from v$transportable_platform where PLATFORM_NAME='Microsoft Windows IA (64-bit)' or PLATFORM_NAME like 'Linux%' order by platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FOR
----------- ------------------ ----------
8 Microsoft Windows IA (64-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:
shutdown immediate;
startup mount;
Alter database open read only;
3. Check database readiness for transport from Windows to Linux:
Before converting the database, we have to be make sure that whether a database can be transported to a desired destination platform, and whether the current state of the database permits transport. We check this using "DBMS_TDB.CHECK_DB" procedure.
If this procedure returns "FALSE" then the output includes the reason why the database cannot be transported like target platform has a different endian format, database is not open read-only, there are active transactions in the database, database compatibility version is below 10 etc.
SQL> set serveroutput on
SQL> declare
      db_ready boolean;
      begin
      db_ready := dbms_tdb.check_db('Linux IA (64-bit)');
      end;
      /
PL/SQL procedure successfully completed.

Note: If database is not open in read-only mode, then the above procedure may return error like Database is not open READ ONLY. Please open database READ ONLY and retry.
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 because RMAN cannot automate the transport of such objects. "DBMS_TDB.CHECK_EXTERNAL" must be used to identify any external tables, directories or BFILEs.
SQL> set serveroutput on
SQL> declare
      external boolean;
      begin
      external := dbms_tdb.check_external;
      end;
      /

5. Using the RMAN CONVERT DATABASE:
After all the pre-requisites have been performed successfully, we can use the RMAN CONVERT DATABASE command to generate the output files for the target database. While executing the CONVERT DATABASE command, we need to specify the new database name, the destination platform, path to save the transport script and optionally a path, where to save the output files.
Note: If you omit the "DB_FILE_NAME_CONVERT" clause, the output files are created in "C:\ORACLE_HOME\database" folder.
C:\>rman target /
RMAN> CONVERT DATABASE NEW DATABASE ' MUJAZORC'
            transport script 'D:\ORACLE\SCRIPT\Mujscript.sql'
            to platform 'Linux IA (64-bit)'
            db_file_name_convert 'D:\ORACLE\ORADATA\MUJAZORC\' '                                D:\ORACLE\SCRIPT\';
Starting convert at 13-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=161 devtype=DISK
.
.
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name= D:\ORACLE\ORADATA\MUJAZORC\SYSTEM01.DBF
converted datafile=D:\ORACLE\SCRIPT\SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:41
input datafile fno=00002 name= D:\ORACLE\ORADATA\MUJAZORC\UNDOTBS01.DBF
converted datafile=D:\ORACLE\SCRIPT\UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:28
.
.
At the end of the convert process RMAN does display information about how to complete the conversion on the target platform. You can use the init.ora file generated by the "CONVERT DATABASE" command or you can create your own pfile using the SQL statement "create pfile from spfile".
6. Copy Converted Datafiles, Generated Script and Pfile to the Linux Machine:
Now copy these converted datafiles, transport script and Pfile on the Linux server. Do not forget to create necessary folder on linux machine such as adump, bdump, udump, cdump, flash_recovery_area.
7. Edit init.ora or pfile for new database:
Open the init.ora file generated by RMAN. The first section of pfile must be updated, others are optional. Change all the absolute path of windows environment to absolute path of Linux environment.
8. Edit The Script:
Before running the transport script on the target Linux server we need to edit it to set the correct paths for pfile, datafiles, logfiles and tempfiles. Update all the paths as per your Linux environment.
9. Execute the Script:
SET ORACLE_SID= mujazlnx
sqlplus / as sysdba
SQL>@d:\oracle\mujscript.sql

On the completion of the script, the database would be open and ready for use.
10. Change DBID:
DBNEWID is a database utility that can change the internal database identifier (DBID). Changing the DBID is necessary when you want to use an RMAN catalog to backup a cloned instance. RMAN identifies instances using the DBID, preventing the original and cloned instance being managed by the same catalog. For more information: How to change DBID & DBNAME
11. Run utlirp.sql and utlrp.sql on the target platform to recompile all PL/SQL modules.
12. Verify & Compare database details with before details:
SQL> Select * from date1;
SQL> select tablespace_name from dba_tablespaces;
SQL> select file_name from dba_data_files;
SQL> SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;

6 comments:

  1. what is the use of convert script


    rman>convert database on target platform
    2> convert script '/tmp/convert_mydb.rman'
    3> transport script '/tmp/transport_mydb.sql'
    4> new database 'mydb'
    5> format '/tmp/mydb%U'
    6> db_file_name_convert '/u01/oradata/SAMPL2/datafile','/tmp/rman'

    is it for os bit compatability conversion ?

    ReplyDelete
  2. rman>convert database on target platform
    2> convert script '/tmp/convert_mydb.rman'
    3> transport script '/tmp/transport_mydb.sql'
    4> new database 'mydb'
    5> format '/tmp/mydb%U'
    6> db_file_name_convert '/u01/oradata/SAMPL2/datafile','/tmp/rman'

    is it for os bit compatability conversion ?

    ReplyDelete
  3. Sir i have a doubt here that is when we r going to migrate the database ,database should be in read only mode so for this we node downtime,with out taking down time is it possible to convert the datafiles in target platform side using cold copy backup of source database.

    ReplyDelete
    Replies
    1. Dear,
      Using cold copy you can minimize the downtime only but needed. I don't if it is possible in latest oracle database 12c.
      For more details try to Google you will find a number of related documents.

      Delete
  4. hello sir , i have doubt about script.sql. you are executed a sql script on windows... how its possible
    we are converting database from windows to linux.....so i think this script will executing on linux .................reply.............

    ReplyDelete