Cross-Platform Database Migration (across same endian) using RMAN Transportable Database

Cross-Platform Database Migration (across same endian) using RMAN Transportable Database (Doc ID 1401921.1)

In this Document Purpose Details References APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.3 [Release 10.2 to 11.2] Information in this document applies to any platform. PURPOSE + We can use RMAN "Transportable Database" conversion for performing cross platform database migration between two operating system platforms belonging to the same endian format. Use V$TRANSPORTABLE_PLATFORM view to check the endianess of source and target database platforms. + If you need to migrate database across endian platforms i.e. from a little-endian platform to big-endian platform or vice-versa, we cannot use RMAN "Transportable Database" conversion. In this case, you would be using transportable tablespaces along with RMAN datafile conversion. + Also, note that for certain platform migration, we need not use RMAN convert (either using "Transportable Database" or "Transportable Tablespaces") at all. For these platforms, you can directly perform RMAN duplication (active or backup-based) or RMAN restore/recover without any conversion: Document 1079563.1 RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support DETAILS For explaining RMAN "Transportable Database" methodology, we will migrate a 11.2.0.3 database running on Linux x86 64-bit (little-endian) platfrom to Solaris Operating System x86 32-bit (little-endian) platform 1. Execute DBMS_TDB.CHECK_EXTERNAL function to identify any external tables, directories, or BFILEs. RMAN cannot automate the transport of these files, so you must copy the files manually and re-create the database directories. SQL> set serveroutput on; SQL> declare x boolean; begin x := dbms_tdb.check_external; end;   2  / The following directories exist in the database: SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR   PL/SQL procedure successfully completed. As per output above, we have 2 directories defined in the database. Check the operating system path for these directories using below query. Ensure that the same path is available on the target system for these directories to be usable or rename the directory paths after conversion. SQL> select DIRECTORY_NAME, DIRECTORY_PATH from DBA_DIRECTORIES;   DIRECTORY_NAME            DIRECTORY_PATH ------------------------- ---------------------------------------- DATA_PUMP_DIR             /orasoft/admin/ora11gR2/dpdump/ ORACLE_OCM_CONFIG_DIR     /orasoft/rdbms/11.2.0.3/ccr/state 2. Start the source database in READ ONLY mode: SQL> shutdown immediate; SQL> startup mount; SQL> alter database open read only; 3. Execute DBMS_TDB.CHECK_DB function. This check ensures that no conditions would prevent the transport of the database, such as incorrect compatibility settings, in-doubt or active transactions, or incompatible endian formats between the source platform and destination platform. Any condition reported by CHECK_DB must be resolved before proceeding SQL> set serveroutput on SQL> SQL> DECLARE db_ready BOOLEAN;   2  BEGIN   3  db_ready := DBMS_TDB.CHECK_DB('Solaris Operating System (x86)', DBMS_TDB.SKIP_NONE);   4  END;   5  /   PL/SQL procedure successfully completed. 4. Transportable Database using RMAN COVERT DATABASE command can be performed on the source system or target system. It's default behavior is to perform datafile conversion on all datafiles in the database. However, only datafiles that contain undo data require conversion including all datafiles beloging to SYSTEM tablespace and all UNDO tablespaces. To skip datafile conversion for datafiles that do not contain undo data, you must perform a target platform conversion as shown in this document. Get a list of datafiles that require conversion and ones that don't SQL> select FILE_NAME "Datafiles requiring Conversion" from DBA_DATA_FILES   2  where TABLESPACE_NAME in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);   Datafiles requiring Conversion -------------------------------------------------------------------------------- /oradata/ora11gR2/system01.dbf /oradata/ora11gR2/undotbs01.dbf   SQL> select FILE_NAME "Files NOT requiring Conversion" from DBA_DATA_FILES   2  where TABLESPACE_NAME NOT in (select distinct TABLESPACE_NAME from DBA_ROLLBACK_SEGS);   Files NOT requiring Conversion -------------------------------------------------------------------------------- /oradata/ora11gR2/sysaux01.dbf /oradata/ora11gR2/users01.dbf 5. Now we need to move all the above files (the ones which need conversion and ones which do not) to the target system. For datafiles which do not require conversion, they can directly be moved to their final destination on the target system. For datafiles which need conversion, need to be moved to a staging location on the target system where we will run CONVERT on them during which they will be placed in their final destination. To minimize time for migration, we need to evaluate how datafile copying (to the target system) and datafile conversion can be merged into a single step. One method would be to NFS mount the source system directory (containing the datafiles) on the target system. Thus, when datafile conversion is run on target system, the datafiles will be placed in their final location eliminating the extra step of transferring the datafiles to the target system. This method will work if you are performing migration for below cases: a) Source database uses file-system, Target database uses file-system b) Source database uses file-system, Target database uses ASM If source database uses ASM, we need to use BACKUP AS COPY command in RMAN (this needs to be run when the database in in MOUNT mode or OPEN read only) to move the datafiles from ASM to a file-system location on the source and then NFS mount that location on the target system. Alternatively if source database uses SAN storage, check with your system administrator if it is possible to mount the source san diskgroups directly on the target server in read only mode. In this example, our source Linux x86 64-bit uses file-system whereas our target Solaris x86 32-bit will use ASM. We will NFS mount the source system directory which contains the datafiles i.e. '/oradata' to the target Solaris system NFS mounting is outside the scope of this article but here is how it is done On source, put below line in /etc/exports: /oradata        oraSolaris10(ro)   Re-start NFS: # /etc/init.d/nfs stop # /etc/init.d/nfs start   On target, perform the NFS mount: # mount -o vers=3  -F nfs 192.168.114.10:/oradata /oradata 6. We now need to move the initialization parameter file from source to target system. If source database is using spfile, create pfile from it and move it to the target: SQL> create pfile = '/oradata/ora11gR2/initSol11g.ora' from spfile; On the target system, move this pfile to $ORACLE_HOME/dbs location and make suitable changes to reflect mount-points available on the target system and any other changes you might want to make. you might want to specifically change these parameters: audit_file_dest, control_files, db_name, db_recovery_file_dest, diagnostic_dest STARTUP NOMOUNT the instance on the target system using the modified pfile: SQL> startup nomount pfile='/orasoft/rdbms/11.2.0.3/dbs/initSol11g.ora' 7. Now, start the conversion process in RMAN on target system for the datafiles that we identified in Step 4 i.e. ones which contain UNDO data: RMAN> CONVERT FROM PLATFORM 'Linux x86 64-bit' 2> PARALLELISM 2 3> DATAFILE '/oradata/ora11gR2/system01.dbf' FORMAT '+DATA' 4> DATAFILE '/oradata/ora11gR2/undotbs01.dbf' FORMAT '+DATA';   Starting conversion at target at 08-NOV-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=62 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=12 device type=DISK channel ORA_DISK_1: starting datafile conversion input file name=/oradata/ora11gR2/system01.dbf channel ORA_DISK_2: starting datafile conversion input file name=/oradata/ora11gR2/undotbs01.dbf converted datafile=+DATA/sol11g/datafile/undotbs1.256.766638629 channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:02:53 converted datafile=+DATA/sol11g/datafile/system.257.766638629 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:23 Finished conversion at target at 08-NOV-11 You can increase PARALLELISM value if there are many files which need to be converted so that more channels are allocated. If the target database will not use ASM, specify the file-system path in the FORMAT clause where the datafile will finally reside. 8. Now, copy rest of the datafiles (which do not contain undo and hence do not require conversion) to the ASM diskgroup using RMAN without conversion. Note that to copy files into ASM we have again used CONVERT command (without FROM PLATFORM clause). When CONVERT DATAFILE is used without specifying a source or destination platform, we do not perform any kind of conversion. This is specifically used to move files into ASM from file-sytem. If the target database will not use ASM, this step is not needed. Instead use OS cp command to copy these datafile directly to the directory where you want them to reside. RMAN> CONVERT PARALLELISM 2 2> DATAFILE '/oradata/ora11gR2/sysaux01.dbf' FORMAT '+DATA' 3> DATAFILE '/oradata/ora11gR2/users01.dbf' FORMAT '+DATA';   Starting conversion at target at 08-NOV-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=13 device type=DISK channel ORA_DISK_1: starting datafile conversion input file name=/oradata/ora11gR2/sysaux01.dbf channel ORA_DISK_2: starting datafile conversion input file name=/oradata/ora11gR2/users01.dbf converted datafile=+DATA/sol11g/datafile/users.259.766639567 channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:17 converted datafile=+DATA/sol11g/datafile/sysaux.258.766639567 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:59 Finished conversion at target at 08-NOV-11 9. Now, we need to create the controlfile for our target database. You can generate the controlfile creation script by running below on the source database: SQL> alter database backup controlfile to trace resetlogs; Edit the controlfile creation script to reflect: a) Datafile names for the target database where the datafiles finally reside (after running CONVERT DATAFILE) b) Redo Log names as per target system path c) database name as needed for the target database. Here is the controlfile creation script for our example: SQL> CREATE CONTROLFILE REUSE SET DATABASE "SOL11G" RESETLOGS ARCHIVELOG   2      MAXLOGFILES 16   3      MAXLOGMEMBERS 3   4      MAXDATAFILES 100   5      MAXINSTANCES 8   6      MAXLOGHISTORY 292   7  LOGFILE   8    GROUP 1 '+DATA/sol11g/redo01.log'  SIZE 50M BLOCKSIZE 512,   9    GROUP 2 '+DATA/sol11g/redo02.log'  SIZE 50M BLOCKSIZE 512, 10    GROUP 3 '+DATA/sol11g/redo03.log'  SIZE 50M BLOCKSIZE 512 11  DATAFILE 12    '+DATA/sol11g/datafile/system.257.766638629', 13    '+DATA/sol11g/datafile/sysaux.258.766639567', 14    '+DATA/sol11g/datafile/undotbs1.256.766638629', 15    '+DATA/sol11g/datafile/users.259.766639567' 16  CHARACTER SET WE8MSWIN1252; Control file created. 10. Now, open the database on the target system. Once, database opens, add tempfiles to the TEMP tablespace SQL> ALTER DATABASE OPEN RESETLOGS;   Database altered.   SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 20971520  AUTOEXTEND OFF;   Tablespace altered. 11. Complete the database conversion by running UTLIRP and UTLRP as below: SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP UPGRADE; SQL> @ ?/rdbms/admin/utlirp SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP; SQL> @ ?/rdbms/admin/utlrp REFERENCES NOTE:1079563.1 - RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support NOTE:413484.1 - Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration