Oracle Cross-Platform Migration Using Rman (Convert Database )

1. Check Oracle version on  both side if   the patch level  of new  platform is  different   than old one try  to create a  new database  and  use  Transportable  tablespace  feature of Oracle (In this soluiton   be careful sys objects and so on  ).In my example  I will   migrate  from 10.2.0.4 Solaris  64  to AIX 64 bit
2. Check  the endian format  of   source and  destination platforms If endian formats are the same converting only system and  undo tablespaces  (datafiles that contain undo ) are enough
   
   select platform_name, endian_format from V$TRANSPORTABLE_PLATFORM;
3. Open database  read only;
   
   startup mount;
   alter database open read only;
4. Use DBMS_TDB.CHECK_DB to check whether the database can be transported to a desired destination platform
   
   set serveroutput on
   
   declare
   db_ready boolean;
   begin
     db_ready := sys.dbms_tdb.check_db('AIX-Based Systems (64-bit)');
   end;
   /
   
    
5. Use DBMS_TDB.CHECK_EXTERNAL to identify any external tables, directories or BFILEs. RMAN cannot automate the transport of such files as mentioned above.
   
   
   set serveroutput on
   
   declare
   external boolean;
   begin
     external := dbms_tdb.check_external;
   end;
   /
6.  Identify datafiles that contain undo data by running the following query.Because  my endian format is  the same I will only convert system and undo tablespaces.
   
   select distinct(file_name)
   
   from dba_data_files a, dba_rollback_segs b
   where a.tablespace_name=b.tablespace_name;
   
7. Run RMAN command to create conversion scripts and init.ora file:
   FORMAT defines location of init.ora file
   DB_FILE_NAME_CONVERT defines final data files location on destination server.
   
   rman
   connect target /
   
   CONVERT DATABASE ON TARGET PLATFORM
   CONVERT SCRIPT '/switchthome/switcht/tts/convertscript.rman'
   TRANSPORT SCRIPT '/switchthome/switcht/tts/transportscript.sql'
   new database 'SWITCHT'
   FORMAT '/switchthome/switcht/tts/%U'
   DB_FILE_NAME_CONVERT =('/switchtdata01/data','/wftesthome/sw/data');
8. Copy the  files
1. Transport.sql
2. Convertscript.rman
3. Pfile generated by the convert database command.
4. Copy system  and undo databafiles to stage area
5. Copy rest datafiles to final location on destination server (if endian formats are diffrent  also  we must copy all  the datafiles  to stage  and  convert  them)
9. Check the envorminmets
1. ORACLE_SID,ORACLE_HOME,PATH,LD_LIBRARY_PATH,LIBPATH ..
2. Copy and edit the init.ora
3. check the  directories  like dump..
10. Create a dummy Controlfile  in order  to convert
    sqlplus '/ as sysdba'
    STARTUP NOMOUNT ;
    
    create controlfile reuse set database "SWITCHT" RESETLOGS ARCHIVELOG
    
     MAXLOGFILES 128
     MAXLOGMEMBERS 4
     MAXDATAFILES 10000
     MAXINSTANCES 8
     MAXLOGHISTORY 10210
    LOGFILE
    
     '/wftesthome/sw/log/redo01.log' size 10m,
     '/wftesthome/sw/log/redo02.log' size 10m,
     '/wftesthome/sw/log/redo03.log' size 10m,
     '/wftesthome/sw/log/redo04.log' size 10m,
     '/wftesthome/sw/log/redo05.log' size 10m,
     '/wftesthome/sw/log/redo06.log' size 10m
    DATAFILE
    
     '/wftesthome/sw/stage/system01.dbf',
     '/wftesthome/sw/stage/undorbs01.dbf',
     '/wftesthome/sw/data/sysaux01.dbf',
     '/wftesthome/sw/data/tools01.dbf',
     '/wftesthome/sw/data/users01.dbf',
     '/wftesthome/sw/data/oasis_index01.dbf',
     '/wftesthome/sw/data/oasisdata_100m01.dbf',
     '/wftesthome/sw/data/oasisindex_10m01.dbf',
     '/wftesthome/sw/data/oasisindex_1m01.dbf',
     '/wftesthome/sw/data/index_1m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_100m01.dbf',
     '/wftesthome/sw/data/data_128k_ts01.dbf',
     '/wftesthome/sw/data/data_1m_ts01.dbf',
     '/wftesthome/sw/data/data_10m_ts01.dbf',
     '/wftesthome/sw/data/data_100m_ts01.dbf',
     '/wftesthome/sw/data/index_128k_ts01.dbf',
     '/wftesthome/sw/data/index_10m_ts01.dbf',
     '/wftesthome/sw/data/index_100m_ts02.dbf',
     '/wftesthome/sw/data/index_100m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_64k01.dbf',
     '/wftesthome/sw/data/oasisdata_10m01.dbf',
     '/wftesthome/sw/data/oasisdata_1m01.dbf',
     '/wftesthome/sw/data/oasisdata_64k01.dbf',
     '/wftesthome/sw/data/oasis_01.dbf'
    CHARACTER SET WE8ISO8859P9;
11.  edit the file Convertscript.rman  only for system and undo tablespaces  (if endian formats are diffrent  also  we must copy all  the datafiles  to stage  and  convert  them)
    
    rman target / nocatalog @convert.rman
    
    
    
    ---convert.rman
    RUN {
     CONVERT DATAFILE '/wftesthome/sw/stage/undorbs01.dbf'
     FROM PLATFORM 'Solaris[tm] OE (64-bit)'
     FORMAT '/wftesthome/sw/data/undorbs01.dbf';
    
     CONVERT DATAFILE '/wftesthome/sw/stage/system01.dbf'
     FROM PLATFORM 'Solaris[tm] OE (64-bit)'
     FORMAT '/wftesthome/sw/data/system01.dbf';
    }
12. shutdown the database and delete the dummy controlfile
13. edit the TRANSPORT sql script to reflect the new path for datafiles and redolog files in the CREATE CONTROLFILE section of the script
    
    sqlplus '/ as sysdba'
    
    STARTUP NOMOUNT ;
    
    
    
    create controlfile reuse set database "SWITCHT" RESETLOGS ARCHIVELOG
    
     ARCHIVELOG
     MAXLOGFILES 128
     MAXLOGMEMBERS 4
     MAXDATAFILES 10000
     MAXINSTANCES 8
     MAXLOGHISTORY 10210
    LOGFILE
     '/wftesthome/sw/log/redo01.log' size 10m,
     '/wftesthome/sw/log/redo02.log' size 10m,
     '/wftesthome/sw/log/redo03.log' size 10m,
     '/wftesthome/sw/log/redo04.log' size 10m,
     '/wftesthome/sw/log/redo05.log' size 10m,
     '/wftesthome/sw/log/redo06.log' size 10m
    DATAFILE
     '/wftesthome/sw/data/system01.dbf',
     '/wftesthome/sw/data/undorbs01.dbf',
     '/wftesthome/sw/data/sysaux01.dbf',
     '/wftesthome/sw/data/tools01.dbf',
     '/wftesthome/sw/data/users01.dbf',
     '/wftesthome/sw/data/oasis_index01.dbf',
     '/wftesthome/sw/data/oasisdata_100m01.dbf',
     '/wftesthome/sw/data/oasisindex_10m01.dbf',
     '/wftesthome/sw/data/oasisindex_1m01.dbf',
     '/wftesthome/sw/data/index_1m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_100m01.dbf',
     '/wftesthome/sw/data/data_128k_ts01.dbf',
     '/wftesthome/sw/data/data_1m_ts01.dbf',
     '/wftesthome/sw/data/data_10m_ts01.dbf',
     '/wftesthome/sw/data/data_100m_ts01.dbf',
     '/wftesthome/sw/data/index_128k_ts01.dbf',
     '/wftesthome/sw/data/index_10m_ts01.dbf',
     '/wftesthome/sw/data/index_100m_ts02.dbf',
     '/wftesthome/sw/data/index_100m_ts01.dbf',
     '/wftesthome/sw/data/oasisindex_64k01.dbf',
     '/wftesthome/sw/data/oasisdata_10m01.dbf',
     '/wftesthome/sw/data/oasisdata_1m01.dbf',
     '/wftesthome/sw/data/oasisdata_64k01.dbf',
     '/wftesthome/sw/data/oasis_01.dbf'
    CHARACTER SET WE8ISO8859P9;
    
    alter database open resetlogs;
    alter tablespace temp add tempfile '/wftesthome/sw/data/temp01.dbf'  SIZE 100m  autoextend off;
    shutdown immediate;
    
    startup upgrade;
    @@ ?/rdbms/admin/utlirp.sql
    shutdown immediate;
    
    startup;
    @@?/rdbms/admin/utlrp.sql
    set feedback 6;

See also:
Transportable Tablespaces (TTS)
References:
Metalink 415884.1 Cross Platform Database Conversion with same Endian
Metalink 414878.1 Cross-Platform Migration on Destination Host Using Rman Convert Database
Metalink 732053.1 Avoid Datafile Conversion during Transportable Database
Metalink 417455.1 Datafiles are not converted in parallel for transportable database
Metalink 100693.1 Getting Started with Transportable Tablespaces
Metalink 413586.1 How To Use RMAN CONVERT DATABASE for Cross Platform Migration:
Metalink 371556.1 How move tablespaces across platforms using Transportable Tablespaces with RMAN
Metalink 579136.1 IMPDP TRANSPORTABLE TABLESPACE FAILS for SPATIAL INDEX)
Metalink 77523.1 Transportable Tablespaces -- An Example to setup and use
Metalink 243304.1 10g : Transportable Tablespaces Across Different Platforms
Metalink 733824.1 HowTo Recreate a database using TTS (TransportableTableSpace)