Cross platform migration from windows to linux of oracle database

Cross platform migration from windows to linux

1.Using RMAN convert command we can make this very simple.
create the working directory for example

C:\oracle\product\10.2.0\Migrate\DB_NAME

Login to the database to check the datafiles,redofie,controlfile status,init file,invalid objects,DB links and directories

set ORACLE_SID=DB_NAME
sqlplus sys/****** as sysdba

spool C:\oracle\product\10.2.0\Migrate\DB_NAME\pre_migration_DB_NAME.log
--Checking datafile status
select checkpoint_time,fuzzy,count(*),status
from ( select checkpoint_time,fuzzy,status
       from v$datafile_header
       union all
       select controlfile_time,'CTL',null from v$database)
group by checkpoint_time,fuzzy,status;
--checking invalid objects
select count(*),owner from dba_objects where status='INVALID' group by owner;
select * from v$logfile;
select name from v$controlfile;
--Switching logfile
alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

select member from v$logfile;
select name from v$controlfile;
select name from v$datafile;

--Init file Backup
create pfile='C:\oracle\product\10.2.0\Migrate\DB_NAME\init_bkDB_NAME.ora' from spfile;
--controlfile backup in text format
alter database backup controlfile to trace as 'C:\oracle\product\10.2.0\Migrate\DB_NAME\controlfilefromprd_bk_DB_NAME.trc' resetlogs;
-- Checking external table
set serveroutput on;
 declare x boolean; begin x := dbms_tdb.check_external; end;
 /

shutdown immediate;
startup mount;
alter database open read only;

-- To check target server is eligible for migration of current platform

 set serveroutput on;
 DECLARE db_ready BOOLEAN; 
  BEGIN 
  db_ready := DBMS_TDB.CHECK_DB('Linux x86 64-bit', DBMS_TDB.SKIP_NONE);
  END;
 /
--This should not give any error
select FILE_NAME "Files NOT requiring Conversion" from DBA_DATA_FILES   where TABLESPACE_NAME NOT in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);
select FILE_NAME "Datafiles requiring Conversion" from DBA_DATA_FILES  where TABLESPACE_NAME in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);
spool off
================================================
The above file will have clear idea of present stat of DB.
Note: the above DBMS_TDB.CHECK_DB should not fail,If it gives error then this platform might not be supported.

If you dont have any error on DBMS_TDB.CHECK_DB  then you can proceed with below.

================================================
select FILE_NAME "Datafiles requiring Conversion" from DBA_DATA_FILES  where TABLESPACE_NAME in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);
Datafiles requiring Conversion
--------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\SYSTEM01.DBF                            
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\UNDOTBS01.DBF

Copy the above files to the target server for running RMAN convert

================================================
 select FILE_NAME "Files NOT requiring Conversion" from DBA_DATA_FILES   where TABLESPACE_NAME NOT in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);

Files NOT requiring Conversion
--------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\USER.ORA                            

Copy the above file to target but we no need to run RMAN convert command for the above files.Which will save lot of time and we can migrate TB size DB in quick time.
================================================

On the target server.
Install Oracle software only.
Then to make or create directory structure as per your company standard.
mkdir -p /oradata/DB_NAME/datafile
mkdir -p /oradata/DB_NAME/redolog
mkdir -p /oradata/DB_NAME/controlfile
chown -R oracle:oinstall /oradata/


mkdir -p /admin/DB_NAME/adump
mkdir -p /admin/DB_NAME/bdump
mkdir -p /admin/DB_NAME/cdump
mkdir -p /admin/DB_NAME/udump

mkdir -p /arch/DB_NAME/flash_recovery_area
mkdir -p /arch/DB_NAME/arch
chown -R oracle:oinstall /admin
chown -R oracle:oinstall /arch


mkdir -p /u01/oradata/DB_NAME/controlfile
mkdir -p /u01/oradata/DB_NAME/redolog
chown -R oracle:oinstall /u01/oradata/DB_NAME

mkdir -p /Backup/DB_NAME
chown -R oracle:oinstall /Backup/DB_NAME

Now using winscp copy

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\SYSTEM01.DBF to /Backup/DB_NAME
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\UNDOTBS01.DBF to /Backup/DB_NAME

And

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\SYSAUX01.DBF to  /oradata/DB_NAME/datafile
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\USERS01.DBF to  /oradata/DB_NAME/datafile
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB_NAME\BANKWORLD.ORA to  /oradata/DB_NAME/datafile

========================================================================================
Create initDB_NAME.ora in $ORACLE_HOME/dbs

*.audit_file_dest='/admin/DB_NAME/adump'
*.background_dump_dest='/admin/DB_NAME/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oradata/DB_NAME/controlfile/control01.ctl','/oradata/DB_NAME/controlfile/control02.ctl'
*.core_dump_dest='/admin/DB_NAME/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='DB_NAME'
*.db_recovery_file_dest='/arch/DB_NAME/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB_NAMEXDB)'
*.job_queue_processes=10
*.log_buffer=2097152
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.optimizer_index_cost_adj=25
*.pga_aggregate_target=104857600
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=524288000
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/admin/DB_NAME/udump'
*.utl_file_dir='*'

After that
$sqlplus "/as sysdba"

SQL>startup nomount
SQL>exit
==============
Run the convert command as below for the datafiles which need to be converted.
$rman target /
CONVERT DATAFILE '/Backup/DB_NAME/SYSTEM01.DBF' FROM PLATFORM 'Microsoft Windows IA (32-bit)' FORMAT '/oradata/DB_NAME/datafile/SYSTEM01.DBF';
CONVERT DATAFILE '/Backup/DB_NAME/UNDOTBS01.DBF' FROM PLATFORM 'Microsoft Windows IA (32-bit)' FORMAT '/oradata/DB_NAME/datafile/UNDOTBS01.DBF';

=================
Then connect to sqlplus to create the controlfile ,This templet we can get from source backup text controlfile.

#sqlplus "/as sysdba"
SQL> CREATE CONTROLFILE REUSE DATABASE "DB_NAME" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
   GROUP 1 (
      '/u01/oradata/DB_NAME/redolog/redo01_a.log',
      '/oradata/DB_NAME/redolog/redo01_b.log'
    ) SIZE 250M,
    GROUP 2 (
      '/u01/oradata/DB_NAME/redolog/redo02_a.log',
      '/oradata/DB_NAME/redolog/redo02_b.log'
    ) SIZE 250M,
    GROUP 3 (
      '/u01/oradata/DB_NAME/redolog/redo03_a.log',
      '/oradata/DB_NAME/redolog/redo03_b.log'
  ) SIZE 250M
DATAFILE
  '/oradata/DB_NAME/datafile/SYSTEM01.DBF',
  '/oradata/DB_NAME/datafile/UNDOTBS01.DBF',
  '/oradata/DB_NAME/datafile/SYSAUX01.DBF',
  '/oradata/DB_NAME/datafile/USERS01.DBF',
  '/oradata/DB_NAME/datafile/USER.DBF'
CHARACTER SET UTF8
;

Then open the database in resetlogs

SQL> ALTER DATABASE OPEN RESETLOGS;

Add tempfiles
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/DB_NAME/datafile/TEMP01.DBF'
     SIZE 90177536  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 4767M;

-- Check the datafile count and status in Target which should be same as source

select checkpoint_time,fuzzy,count(*),status
from ( select checkpoint_time,fuzzy,status
       from v$datafile_header
       union all
       select controlfile_time,'CTL',null from v$database)
group by checkpoint_time,fuzzy,status;
-- Check invalid status
select count(*),owner from dba_objects where status='INVALID' group by owner;

select count(*),owner from dba_objects where status='INVALID' group by owner;
if file count is not same the do the below.

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
@ ?/rdbms/admin/utlirp
SHUTDOWN IMMEDIATE;
STARTUP;
@ ?/rdbms/admin/utlrp
select 'exec utl_recomp.recomp_parallel(8,'||username||');' from dba_users;
--Check temp tablespace status for users
select USERNAME,TEMPORARY_TABLESPACE from dba_users

-- Switch the logs and check the alert log
alter system checkpoint;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
create spfile from pfile;
!orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwDB_NAME entries=5 password=**********
startup force;

-- Check and create DB directories,External tables,DB links and Tns files as like source.

++ Note: Check Metalink note Cross-Platform Database Migration (across same endian) using RMAN Transportable Database [ID 1401921.1]