Cross-Platform Oracle Database Restore

The RMAN CONVERT DATABASE use to move database from one platform(HP-UX) to another platform(Solaris). The source and destination database must share the same enadian format. If source and destination are on different endian format then you can use cross transport tablespace.
For example,

SQL>Select * from v$db_transportable_platform;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- --------------
          1 Solaris[tm] OE (32-bit)                  Big
          2 Solaris[tm] OE (64-bit)                  Big
          6 AIX-Based Systems (64-bit)               Big
          3 HP-UX (64-bit)                           Big
          4 HP-UX IA (64-bit)                        Big
          9 IBM zSeries Based Linux                  Big
         16 Apple Mac OS                             Big
         18 IBM Power Based Linux                    Big

All of the above are sharing the same endian format (Big) then RMAN convert database can be used to move database among them. In my case I will be converting from HP-UX to Solaris 10.

Source ---> 11g on HP-UX.
Destination ---> 11g on Solaris 10.


Step 1.  Check Compatible parameter and it must be 10 or higher.
Step 2.  Open source database in read-only mode.
Step 3.  Check to see if source can be transported to destination platform.
Step 4.  Run RMAN CONVERT database command on source.
Step 5.  Copy transportscript, init.ora and datafiles created by RMAN convert to destination platform.
Step 6.  Make necessary changes to init.ora file at destination platform and run the transportscript. This will create controlfile and open the database in resetlogs. 


Let's start with Step 3.

Step 3. Check to see if source can be transported to destination platform

 
      DBMS_TDB.CHECK_DB checks to see if database can be transported to destination platform.

SQL> l
  1  declare
  2      db_ready boolean;
  3    begin
  4      db_ready := dbms_tdb.check_db('Solaris[tm] OE (64-bit)',
  5          dbms_tdb.skip_none);
  6    end;
  7*
SQL> /
Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and
retry.

PL/SQL procedure successfully completed.
Open the database in read-only mode and try again.

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

SQL> l
  1  declare
  2      db_ready boolean;
  3    begin
  4      db_ready := dbms_tdb.check_db('Solaris[tm] OE (64-bit)',
  5          dbms_tdb.skip_none);
  6    end;
  7*
SQL> /

PL/SQL procedure successfully completed.
No errors reported. Source is all set to run convert DB, make sure you have enough space to hold datafiles on source database server or ask administrator to give you a seperate mount point say ("/backup2").


Step 4.  Run RMAN CONVERT database command on source.

 - make sure all required directory exists (/backup2/convertdb).

$ rman taget /

RMAN> CONVERT DATABASE NEW DATABASE 'TEST'
2>         transport script '/backup2/convertdb/transportscript'
3>         to platform 'Solaris[tm] OE (64-bit)'
4>         db_file_name_convert '+DATA' '/backup2/convertdb' ;
Starting conversion at source at 27-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6596 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=+DATA/TEST/datafile/system.311.781438167
converted datafile= "/backup2/convertdb/TEST/datafile/system.311.781438167"
...
...
...
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Edit init.ora file /u01/app/oracle/11.2.0/db/dbs/init_00n9gvp5_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /backup2/convertdb/transportscript on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 28-APR-12

You can open your source database in write mode now.


Step 5.  Copy transportscript, init.ora and datafiles created by RMAN convert to destination platform

- You can use ftp, sftp or some other way to move these three set of files from source to destination.

 1. init.ora, in my case init.ora named as init_00n9gvp5_1_0.ora
 2. transportsript, in my case it was created under "/backup2/convertdb/" folder.
 3. database datafiles, in my case they are under ""/backup2/convertdb/TEST/datafile/"


Step 6.  Make necessary changes to init.ora file at target.

$cd  /u01/app/oracle/11.2.0/db/dbs/
$ mv init_00n9gvp5_1_0.ora initTEST.ora
$ export ORACLE_SID=TEST
$ sqlplus /nolog
SQL>@transportscript

This script will do following.

Startup nomount;
Create controlfile.
Add logfile groups.
Open the database in resetlogs.
Add tempfiles.

- If you are using ASM in target platform now next step would be to move these datafiles from /backup2 to ASM. Please look into my next blog "How to move datafiles from NON-ASM to ASM"

Moving Oracle datafiles from Non-ASM to ASM

There are different ways to move datafile between non-asm to asm or vice-versa. Let me give some examples/methods that I've used quite often.

- RMAN backup/restore.
- RMAN convert command to move individual file.
- ASMCMD- cp command(11g onwards)

RMAN backup/restore.

following example shows how to move datafile from /backup2/ filesystem to ASM

SQL> startup mount;
ORACLE instance started.

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/backup2/TEST/convertdb/datafile/system.311.781438167
/backup2/TEST/convertdb/datafile/tools.ora
..
- Set the control_files parameter to ASM storage. for example,

       CONTROL_FILES=+DATA/controlfiles/control01.ctl

Startup in nomount
 
RMAN> STARTUP NOMOUNT;
Restore controlfile

RMAN> RESTORE CONTROLFILE FROM '/backup2/TEST/control01.ctl'
mount database.

RMAN> ALTER DATABASE MOUNT;
Copy database into ASM, since you are copying from disk you can increase the parallelism


RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 03-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2361 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/backup2/TEST/scripts/convertdb/datafile/system.311.781438167
output file name=+DATA/TEST/datafile/system.304.782310947 tag=TAG20120503T123546 RECID=1 STAMP=782311049
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
input datafile file number=00004 name=/backup/TEST/convertdb/datafile/users.257.781439177
output file name=+DATA/datafile/users.269.782313859 tag=TAG20120503T123546 RECID=36 STAMP=782313896
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
name=/backup/TEST/convertdb/datafile/sysaux.270.781439487
output file name=+DATA/TEST/datafile/sysaux.267.782313951 tag=TAG20120503T123546 RECID=38 STAMP=782313974
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
...
...
...
...
Finished backup at 03-MAY-12
 RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/TEST/datafile/system.304.782310947"
datafile 2 switched to datafile copy "+DATA/TEST/datafile/sysaux.267.782313951"
datafile 3 switched to datafile copy "+DATA/TEST/datafile/undotbs1.289.782312249"
datafile 4 switched to datafile copy "+DATA/TEST/datafile/users.269.782313859"

Open the database.

RMAN> ALTER DATABASE OPEN;
database opened

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/TEST/datafile/system.304.782310947
+DATA/TEST/datafile/sysaux.267.782313951
+DATA/TEST/datafile/undotbs1.289.782312249
+DATA/TEST/datafile/users.269.782313859

- Now create online redo logs in ASM and drop them in non-asm filesystem.


SQL> show paramter db_create_file_dest

db_create_file_dest                  string      /backup/TEST/datafile

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/backup/TEST/datafile/onlinelog/o1_mf_9_7szzmj1q_.log
/backup/TEST/datafile/onlinelog/o1_mf_7_7szzmhr3_.log
/backup/TEST/datafile/onlinelog/o1_mf_5_7szzmhhb_.log
/backup/TEST/datafile/onlinelog/o1_mf_2_7szzmh6t_.log
/backup/TEST/datafile/onlinelog/o1_mf_1_7szzmgxg_.log

SQL>alter system set db_create_file_dest='+DATA';

SQL> show paramter db_create_file_dest

db_create_file_dest                  string      +DATA

SQL> alter database add logfile size 50m;
Database altered.

SQL> alter database add logfile size 50m;
Database altered.

SQL> alter database add logfile size 50m;
Database altered.

SQL>alter database drop logfile group 1;
Database altered.

SQL>alter database drop logfile group 2;
Database altered.

SQL>alter database drop logfile group 3;
Database altered.

SQL> select group#,member from v$logfile;
    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         2
+DATA/TEST/onlinelog/group_2.259.782314915
         3
+DATA/TEST/onlinelog/group_3.260.782314769
         4
+DATA/TEST/onlinelog/group_4.258.782314917

In case of Individual files.. You can do with similar commands.

RMAN> target /


RMAN>backup as copy datafile 54 format '+DATA';

RMAN>switch datafile 54 to copy;

RMAN convert command to move individual files.

I have created a test tablespace with one datafile which is on '/backup' filesystem.

SQL> select file#,name from v$datafile where name like ('%test%');

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         5
/backup/test.ora

Lets move this into ASM with convert command.

shutdown and mount the database and run convert command.

$rman target /

RMAN> convert datafile '/backup/test.ora' format '+DATA';

Starting conversion at target at 03-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2361 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/backup/test.ora
converted datafile=+DATA/TEST/datafile/test.256.782320709
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 03-MAY-12

change the file pointer to ASM with rename file.

SQL> alter database rename file '/backup/test.ora' to '+DATA/TEST/datafile/test.256.782320709';

Database altered.

SQL> alter database open;

SQL> select file#,name from v$datafile where name like ('%test%');

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         5
+DATA/TEST/datafile/test.256.782320709

ASMCMD- cp command

Place tablespace offline, or shutdown and mount the database.

SQL> alter tablespace test offline; 

Tablespace altered.

login to grid account to connect to ASM instance.

$ su - grid
Password: 

use ASMCMD cp command to copy files between OS and ASM.This is available only from 11g

$ asmcmd
ASMCMD> cp /backup/test.ora '+DATA'
copying /backup/test.ora -> +DATA/test.ora
ASMCMD> 

make changes to file pointer and open the database or place tablespace online

SQL> alter database rename file '/backup/test.ora' to '+DATA/test.ora';

Database altered.

SQL> alter tablespace test online;

Tablespace altered.

SQL> select file#,name from v$datafile where name like ('%test%');

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         5
+DATA/test.ora

Regards,