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)

ORA-08102: index key not found

I have  taken  folllowing error while  updating  the table
ORA-08102: index key not found, obj# 1456599, file 1133, block 63620 (2)

This can be a possible inconsistency in index because of a bug.

Example  :Bug 7329252  ORA-8102/ORA-1499/OERI[kdsgrp1] Index corruption after rebuild index ONLINE

Fix:Minimize updates during online index rebuild and rebuild the index.

Transportable Tablespaces (TTS)

You can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another

1. The evalutaion of TTS
• Oracle8i:Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size
• Oracle9i:TTS technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes
• Oracle10g:TTS technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms which has same ENDIAN formats. If ENDIAN formats are different you have to use RMAN  to convert them .
  
   You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering).
   
  SQL> COLUMN PLATFORM_NAME FORMAT A32
  SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
  
  PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
  ----------- -------------------------------- ------------
  
  1 Solaris[tm] OE (32-bit)           Big
  2 Solaris[tm] OE (64-bit)           Big7 Microsoft Windows IA (32-bit)     Little
  10 Linux IA (32-bit)                Little
  6 AIX-Based Systems (64-bit)        Big
  3 HP-UX (64-bit)                    Big
  5 HP Tru64 UNIX                     Little
  4 HP-UX IA (64-bit)                 Big
  11 Linux IA (64-bit)                Little
  15 HP Open VMS                      Little
  8 Microsoft Windows IA (64-bit)     Little
  9 IBM zSeries Based  Linux          Big
  13 Linux 64-bit for AMD             Little
  16 Apple Mac OS                     Big
  12 Microsoft Windows 64-bit for AMD Little
  17 Solaris Operating System (x86)   Little
2. Usage of TTS
• Exporting and importing partitions in data warehousing tables
• Publishing structured data on CDs
• Copying multiple read-only versions of a tablespace on multiple databases
• Archiving historical data
• Performing tablespace point-in-time-recovery (TSPITR) 
3. Limitations on Transportable Tablespace Use
• Character set:The source and target database must use the same character set and national character set.
• Tablespace_name:You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
• Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set
• Encrypted tablespaces:Encrypted tablespaces have the following the limitations
• Before transporting an encrypted tablespace, you must copy the Oracle wallet manually to the destination database, unless the master encryption key is stored in a Hardware Security Module (HSM) device instead of an Oracle wallet. When copying the wallet, the wallet password remains the same in the destination database. However, it is recommended that you change the password on the destination database so that each database has its own wallet password
• You cannot transport an encrypted tablespace to a database that already has an Oracle wallet for transparent data encryption. In this case, you must use Oracle Data Pump to export the tablespace's schema objects and then import them to the destination database. You can optionally take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported
• You cannot transport an encrypted tablespace to a platform with different endianness
• Encrypted columns:Tablespaces that do not use block encryption but that contain tables with encrypted columns cannot be transported. You must use Oracle Data Pump to export and import the tablespace's schema objects. You can take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported
• XML Types:Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes. Beginning with Oracle Database 11g Release 1, you must use only Data Pump to export and import the tablespace metadata for tablespaces that contain XMLTypes
  
  The following query returns a list of tablespaces that contain XMLTypes
  
  select distinct p.tablespace_name from dba_tablespaces p,
    dba_xml_tables x, dba_users u, all_all_tables t where
    t.table_name=x.table_name and t.tablespace_name=p.tablespace_name
    and x.owner=u.username;
  Transporting tablespaces with XMLTypes has the following limitations:
• The target database must have XML DB installed.
• Schemas referenced by XMLType tables cannot be the XML DB standard schemas.
• Schemas referenced by XMLType tables cannot have cyclic dependencies.
• XMLType tables with row level security are not supported, because they cannot be exported or imported.
• If the schema for a transported XMLType table is not present in the target database, it is imported and registered. If the schema already exists in the target database, an error is returned unless the ignore=y option is set
• If an XMLType table uses a schema that is dependent on another schema, the schema that is depended on is not exported. The import succeeds only if that schema is already in the target database.
• Advanced Queues:Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.
• SYSTEM Tablespace Objects:You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences
• Opaque Types:Types whose interpretation is application-specific and opaque to the database (such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation
• Floating-Point Numbers:BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump
4.  Compatibility Considerations for Transportable Tablespaces:
   When you create a transportable tablespace set, Oracle Database computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. Beginning with Oracle Database 11g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the target database
5. Example
1. Determine if Platforms are Supported and Determine Endianness on both source and target
   
   SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
   FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
   WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
2. Pick a Self-Contained Set of Tablespaces
   TTS requires all the tablespaces, which we are moving, must be self contained. This means that the segments within the migration tablespace set cannot have dependency to a segment in a tablespace out of the transportable tablespace set. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.E.x for tablespaces  sales_1,sales_2
   
   
   SQL >EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
   SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
   
   If it were not self contained you should either remove the dependencies by dropping/moving them or include the tablespaces of segments into TTS set to which migration set is depended
3. Generate a Transportable Tablespace Set
   SQL> ALTER TABLESPACE sales_1 READ ONLY;
   SQL> ALTER TABLESPACE sales_2 READ ONLY;
   
   expdp   system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = sales_1,sales_2
   
   or we  can  export  it with exp (old version )
   exp system/password  file=expdat.dmp  transport_tablespace=y tablespaces=sales_1,sales_2
   
   If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter
   expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir     TRANSPORT_TABLESPACES=sales_1,sales_2 TRANSPORT_FULL_CHECK=Y
   
   If sales_1 and sales_2 are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set, then convert the datafiles composing the sales_1 and sales_2 tablespaces
   
   RMAN TARGET /
   
   RMAN> CONVERT TABLESPACE sales_1,sales_2
   
   2> TO PLATFORM 'Microsoft Windows NT'
   3> FORMAT '/temp/%U';
   
   
4. Transport the tablespace
• If both the source and destination are files systems, you can use:
  Any facility for copying flat files (for example, an operating system copy utility or ftp)
  The DBMS_FILE_TRANSFER package
  RMAN
  Any facility for publishing on CDs
• If either the source or destination is an Automatic Storage Management (ASM) disk group, you can use:
  ftp to or from the /sys/asm virtual folder in the XML DB repository
  
  The DBMS_FILE_TRANSFER package
  RMAN
• If you are transporting the tablespace set to a platform with endianness that is different from the source platform, and you have not yet converted the tablespace set, you must do so now. This example assumes that you have completed the following steps before the transport:
1. Set the source tablespaces to be transported to be read-only
2. Use the export utility to create an export file (in our example, expdat.dmp).
3. Datafiles that are to be converted on the target platform can be moved to a temporary location on the target platform. However, all datafiles, whether already converted or not, must be moved to a designated location on the target database.Now use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs, as shown in this hypothetical example:
   
   RMAN> CONVERT DATAFILE
   2> '/hq/finance/work/tru/tbs_31.f',
   3> '/hq/finance/work/tru/tbs_32.f',
   4> '/hq/finance/work/tru/tbs_41.f'
   5> TO PLATFORM="Solaris[tm] OE (32-bit)"
   6> FROM PLATFORM="HP TRu64 UNIX"
   7> DB_FILE_NAME_CONVERT=
   8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
   9> PARALLELISM=5; 
5. Import the Tablespace Set
   
   IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
   TRANSPORT_DATAFILES=
   /salesdb/sales_101.dbf,
   /salesdb/sales_201.dbf
   REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)
   
   If required, put the tablespaces into read/write mode
   ALTER TABLESPACE sales_1 READ WRITE;
   ALTER TABLESPACE sales_2 READ WRITE;

See also:

Oracle Cross-Platform Migration Using Rman (Convert Database )