Convert Single Instance to RAC – Part 1: Duplicate DB using RMAN

Convert Single Instance to RAC – Part 1: Duplicate DB using RMAN

Satishbabu Gunukula on 20 September 2012 with 10 comments

This article is for users of Oracle Database version 10g+ who want to convert their Oracle single instance database to RAC.  I will provide detailed procedure for converting non-RAC database to RAC and we will use Oracle Automatic storage management (ASM) as shared storage.

In general, if the database is small then you can consider installing a new RAC database and export/import your current database. If the database is large then using RMAN for migration would be one of the preferred methods.  If your single instance database is running an older version (than 10g) and you want to migrate to a higher RAC version then the preferred method is to fist upgrade your existing single instance database, test the upgraded database and then migrate to RAC.

In most of the cases, users want to create a new environment for RAC and we are assuming that the below prerequisites are met, and the RAC environment is already installed with ASM as shared storage and ready for database migration.

Prerequisites on all the nodes in the cluster

• Install and configure Clusterware
• Install and configure Automatic Storage Management (ASM)
• Install Oracle Database Software
• Standalone database and RAC must be on same Database version

Please refer to the Oracle Documentation for operating system specific installation and configuration. Also refer to the Metalink note ASM Technical Best Practices [ID 265633.1] for ASM disk group creation based upon on the operating system and type of storage.

Method to convert single instance to RAC

We have different ways to migrate non-RAC to RAC. Here we are using the DUPLICATE DATABASEfeature of RMAN to migrate single instance non-ASM database to RAC Server, which is using ASM as shared storage. Using this manual method you have full control on the duplication process. In case of any issues/errors, you just need to fix the failed setup and you do not need to redo the whole process.

We are using a two phase approach to migrate non-RAC to RAC:

1. Duplicate single instance Non-ASM database to ASM using RMAN
2. Manually Convert single-instance to RAC.

Overview of Non-RAC environment

HostnameDatabase NameInstance NameDatabase StorageorasrvDBORADBORAext3

Overview of RAC environment

HostnameDatabase NameInstance NameDatabase Storageorarac1ORADBORADB1ASMorarac2ORADBORADB2ASM

If you prefer you can keep same name for RAC database. I am using a different database name in RAC environment to avoid confusion.

Please replace the xxxxxxx with actual password in below steps

1.1 Estimate used space for Non-RAC database:

Run the below query on Non-RAC to estimate used space and make sure you have enough space on RAC environment.

SYS@DBORA> SelectDF.TOTAL/1073741824 "DataFile Size GB", LOG.TOTAL/1073741824 "Redo Log Size GB", CONTROL.TOTAL/1073741824 "Control File Size GB", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/ 1073741824 "Total Size GB" from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

1.2 Create password file and init.ora file for RAC environment:

Make sure you set the ORACLE_SID and ORACLE_HOME and create password file under $ORACLE_HOME/dbs

[oracle@orarac1]$ export ORACLE_SID=ORADB[oracle@orarac1]$ export ORACLE_HOME=/home/oracle/product/v10204[oracle@orarac1]$ orapwd file= $ORACLE_HOME/dbs/orapwORADB password=xxxxxxx

Create initORADB.ora file under $ORACLE_HOME/dbs on RAC node1. Please note that we have two ASM diskgroups i.e. +DATA, +FLASH, If you are using different names replace the diskgroup names.

The below initORADB.ora file has minimum settings. Refer to your Non-RAC init.ora file and set the required parameters.

[oracle@orarac1]$  cat $ORACLE_HOME/dbs/initORADB.ora############################################################### FILE : initORADB.ora# DATABASE NAME : ORADB############################################################### Set the RAC database namedb_name ="ORADB"instance_name =ORADB# set the location of the duplicate clone control file.control_files =‘+DATA’, ‘+FLASH’#set the below parameters for default location of data filesdb_create_file_dest='+DATA'#set the below parameters for default location of recovery areadb_recovery_file_dest='+FLASH'# set below parameter to create two members for each redodb_create_online_dest_1=’+DATA’db_create_online_dest_2=’+FLASH’# set two destinations if you want to multiplex the archive logslog_archive_dest_1='location=+DATA'log_archive_dest_2='location=+FLASH'# set the location as per your environmentlog_archive_dest_1='LOCATION=+FLASH’log_archive_format='arch_%r_%s_%t.arc'audit_file_dest =/home/oracle/admin/ORADB/adumpbackground_dump_dest=/home/oracle/admin/ORADB/bdumpcore_dump_dest =/home/oracle/admin/ORADB/cdumpuser_dump_dest =/home/oracle/admin/ORADB/udump# In case of 11g set below parameter as per your environment# diagnostic_dest= /home/apps/oracle#Set the below to the same as the production targetdb_block_size = 8192sga_target=537919488remote_login_passwordfile=exclusiveundo_management =AUTOundo_retention =10800undo_tablespace =UNDOTBS1compatible = 10.2.0.4.0

We are not using DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT parameters as most of the databases have data/redo files in multiple directories.

1.3 Configure Oracle Listener and tnsnames.ora file

Create a static listener for RAC node1 under $ORACLE_HOME/network/admin and reload because auxiliary database will not register itself with the listener:

[oracle@orarac1]$  cat $ORACLE_HOME/network/admin/listener.ora                     SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = ORADB)(ORACLE_HOME = /home/oracle/product/v10204)(SID_NAME = ORADB)))

Add TNS entry in $ORACLE_HOME/network/admin/tnsnames.ora file on Non-RAC Server:

[oracle@orasrv]$ cat $ORACLE_HOME/network/admin/tnsnames.oraORADB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = orarac1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORADB)))

1.4 Start the database in nomount and test the connectivity
Start the auxiliary database in NOMOUNT mode on RAC node1

[oracle@orarac1]$ export ORACLE_SID=ORADB[oracle@orarac1]$ export ORACLE_HOME=/home/oracle/product/v10204[oracle@orarac1>sqlplus /nologSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 19 12:42:16 2012Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.SQL> connect / as sysdbaConnected to an idle instance.SQL> startup nomountORACLE instance started.Total System Global Area  541065216 bytesFixed Size                  2085288 bytesVariable Size             289410648 bytesDatabase Buffers          239075328 bytesRedo Buffers               10493952 bytesSQL>exit[oracle@orarac1]$

Test your Sql*Net connections from Non-RAC Server and you must be able to connect to the ORADB database on RAC Node1.

[oracle@orasrv]$ sqlplus sys/xxxxxxx@ORADB as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 19 14:29:23 2012Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>

Here we are using RMAN catalog and testing the connection to RMAN Database too.

[oracle@orasrv]$ sqlplus  rman /xxxxxxx@rmancatSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 19 14:29:23 2012Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>

 
1.5 Take a backup of Non-RAC Database:

Take a backup of your database and archive logs using below script. Here we are backing the database to a NFS file system/ backup/rman/ORADB.

[oracle@orasrv]$  rman TARGET / CATALOG rman/xxxxx@rmancatRMAN> run{run {allocate channel d1 type disk;allocate channel d2 type disk;backup  database format '/backup/rman/ORADB/dbsdf_online_%d_t%t_s%s_p%p.rmn';sql "alter system archive log current";backup archivelog all delete input format '/backup/rman/ORADB/archdf_%d_t%t_s%s_p%p.rmn';release channel d1;release channel d2;}

The RMAN multiplexing will help to decrease the backup time. If the database is large, allocate morechannels and use set optimal value for filesperset and maxopenfiles. But please note that increasingfilesperset, maxopenfiles values increases process memory requirement.

In RMAN, the FILESPERSET parameter determines how many datafiles to put in each backup set and MAXOPENFILES parameter of ALLOCATE CHANNEL defines how many datafiles RMAN can read from simultaneously.

Once the backup is completed you can either mount the backup file system or copy the backup files for duplicate process on RAC environment. If you are copying the files ensure that you create the same directory structure and copy the backup files:

[oracle@orarac1]$ mkdir /backup/rman/ORADB[oracle@orarac1]$ scp oracle@orasrv:/backup/rman/ORADB/*.rmn /backup/rman/ORADB

1.6 Duplicate the database to RAC server 

In order to duplicate you must connect to the target database and auxiliary database started in NOMOUNT mode and also RMAN catalog, if you are using it.

As part of duplication process, RMAN restores the target data files to the duplicate database and performs the recovery using all available backups and archive logs.  After recovery is completed RMAN restarts the duplicate database (auxiliary database) and opens with the RESETLOGS option and generates a new DBID for duplicate database.           

(i) Place the data files in one diskgroup :  If you want to place all data files in one diskgroup then make sure you have set db_create_file_dest parameter in init.ora file

Here is the duplicate database script:

[oracle@orasrv]$ cat dup_ORADB.rmnconnect catalog rman/xxxxxxxx@rmancatconnect target /connect auxiliary sys/xxxxxxx@ORADBrun{allocate channel d1 device type disk;allocate channel d2 device type disk;allocate auxiliary channel a1 device type disk;allocate auxiliary channel a2 device type disk;duplicate target database to ORADBpfile=/home/oracle/product/v10204/dbs/initORADB.oralogfilegroup 1 ('+DATA','+FLASH') SIZE 50M reuse,group 2 ('+DATA','+FLASH') SIZE 50M reuse;release channel d1;release channel d2;}exit;

Run database duplication script as below:

[oracle@orasrv]$ $ORACLE_HOME/bin/rman cmdfile dup_ORADB.rmn | tee dup_ORADB.rmn

(ii) Place the data files in different diskgroups:  If you have more than one diskgroup and want to place data files across different disk groups then prepare the duplication script using below command.

Here we are generating the SET NEWNAME command for each datafile in the database using below script:

SYS@DBORA> set head offSYS@DBORA> set pagesize 100SYS@DBORA> SQL>  select 'set newname for datafile '||file_id||' to '''||'+DATA'||''';' from dba_data_files;set newname for datafile 1 to '+DATA';set newname for datafile 2 to '+DATA';set newname for datafile 3 to '+DATA';set newname for datafile 4 to '+DATA';set newname for datafile 5 to '+DATA';set newname for datafile 6 to '+DATA';6 rows selected.

Here we are generating the SET NEWNAME command for each tempfile in the database using below script:

SYS@DBORA >  select 'set newname for tempfile  '||file_id||' to '''||'+DATA'||''';' from dba_temp_files;set newname for tempfile  1 to '+DATA';

Replace the diskgroup name that you want to place the data/temp files.

For ex: – we are placing the data files as below.

datafile1, datafile 2, datafile3 - +DATA1
datafile 4, datafile 5, datafile 6 – +DATA2
tempfile                           – +DATA2

Prepare the duplicate database script:

[oracle@orasrv]$ cat dup_ORADB.rmnconnect catalog rman/xxxxxxxx@rmancatconnect target /connect auxiliary sys/xxxxxxx@ORADBrun{allocate channel d1 device type disk;allocate channel d2 device type disk;allocate auxiliary channel a1 device type disk;allocate auxiliary channel a2 device type disk;set newname for datafile 1 to '+DATA1';set newname for datafile 2 to '+DATA1';set newname for datafile 3 to '+DATA1';set newname for datafile 4 to '+DATA2';set newname for datafile 5 to '+DATA2';set newname for datafile 6 to '+DATA2';duplicate target database to ORADBpfile=/home/oracle/product/v10204/dbs/initORADB.oralogfilegroup 1 ('+DATA','+FLASH') SIZE 50M reuse,group 2 ('+DATA','+FLASH') SIZE 50M reuse;release channel d1;release channel d2;}exit;

Run database duplication script as below:

[oracle@orasrv]$ $ORACLE_HOME/bin/rman cmdfile dup_ORADB.rmn | tee dup_ORADB.rmnCopyright (c) 1982, 2007, Oracle.  All rights reserved.RMAN> connect catalog *2> connect target *3> connect auxiliary *4> run{5> allocate channel d1 device type disk;6> allocate channel d2 device type disk;7> allocate auxiliary channel a1 device type disk;8> allocate auxiliary channel a2 device type disk;9> set newname for datafile 1 to '+DATA';10> set newname for datafile 2 to '+DATA';11> set newname for datafile 3 to '+DATA';12> set newname for datafile 4 to '+DATA1';13> set newname for datafile 5 to '+DATA1';14> set newname for datafile 6 to '+DATA1';15> set newname for tempfile 1 to '+DATA1';16> duplicate target database to ORADB17> pfile=/home/oracle/product/v10204/dbs/initORADB.ora18> logfile19> group 1 ('+DATA','+FLASH') size 50M reuse,20> group 2 ('+DATA','+FLASH') size 50M reuse;21> release channel d1;22> release channel d2;23> }24> exit;Connected to recovery catalog databaseconnected to target database: DBORA (DBID=10026343621)connected to auxiliary database: ORADB (not mounted)allocated channel: d1channel d1: sid=201 devtype=DISK---- (Some detail removed for brevity) ----

We have duplicated single instance Non-ASM database from ‘orasrv’ server into ASM storage on orarac1 server. In part 2 I explain to to convert the single instance database to RAC.

Satishbabu Gunukula on20 September 2012 with 10 comments

This article is for users of Oracle Database version 10g+ who want to convert their Oracle single instance database to RAC.  I will provide detailed procedure for converting non-RAC database to RAC and we will use Oracle Automatic storage management (ASM) as shared storage.

In general, if the database is small then you can consider installing a new RAC database and export/import your current database. If the database is large then using RMAN for migration would be one of the preferred methods.  If your single instance database is running an older version (than 10g) and you want to migrate to a higher RAC version then the preferred method is to fist upgrade your existing single instance database, test the upgraded database and then migrate to RAC.

In most of the cases, users want to create a new environment for RAC and we are assuming that the below prerequisites are met, and the RAC environment is already installed with ASM as shared storage and ready for database migration.

Prerequisites on all the nodes in the cluster

• Install and configure Clusterware
• Install and configure Automatic Storage Management (ASM)
• Install Oracle Database Software
• Standalone database and RAC must be on same Database version

Please refer to the Oracle Documentation for operating system specific installation and configuration. Also refer to the Metalink noteASM Technical Best Practices [ID 265633.1] for ASM disk group creation based upon on the operating system and type of storage.

Method to convert single instance to RAC

We have different ways to migrate non-RAC to RAC. Here we are using the DUPLICATE DATABASEfeature of RMAN to migrate single instance non-ASM database to RAC Server, which is using ASM as shared storage. Using this manual method you have full control on the duplication process. In case of any issues/errors, you just need to fix the failed setup and you do not need to redo the whole process.

We are using a two phase approach to migrate non-RAC to RAC:

1. Duplicate single instance Non-ASM database to ASM using RMAN
2. Manually Convert single-instance to RAC.

Overview of Non-RAC environment

HostnameDatabase NameInstance NameDatabase StorageorasrvDBORADBORAext3

Overview of RAC environment

HostnameDatabase NameInstance NameDatabase Storageorarac1ORADBORADB1ASMorarac2ORADBORADB2ASM

If you prefer you can keep same name for RAC database. I am using a different database name in RAC environment to avoid confusion.

Please replace the xxxxxxx with actual password in below steps

1.1 Estimate used space for Non-RAC database:

Run the below query on Non-RAC to estimate used space and make sure you have enough space on RAC environment.

SYS@DBORA> SelectDF.TOTAL/1073741824 "DataFile Size GB", LOG.TOTAL/1073741824 "Redo Log Size GB", CONTROL.TOTAL/1073741824 "Control File Size GB", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/ 1073741824 "Total Size GB" from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

1.2 Create password file and init.ora file for RAC environment:

Make sure you set the ORACLE_SID and ORACLE_HOME and create password file under $ORACLE_HOME/dbs

[oracle@orarac1]$ export ORACLE_SID=ORADB[oracle@orarac1]$ export ORACLE_HOME=/home/oracle/product/v10204[oracle@orarac1]$ orapwd file= $ORACLE_HOME/dbs/orapwORADB password=xxxxxxx

Create initORADB.ora file under $ORACLE_HOME/dbs on RAC node1. Please note that we have two ASM diskgroups i.e. +DATA, +FLASH, If you are using different names replace the diskgroup names.

The below initORADB.ora file has minimum settings. Refer to your Non-RAC init.ora file and set the required parameters.

[oracle@orarac1]$  cat $ORACLE_HOME/dbs/initORADB.ora############################################################### FILE : initORADB.ora# DATABASE NAME : ORADB############################################################### Set the RAC database namedb_name ="ORADB"instance_name =ORADB# set the location of the duplicate clone control file.control_files =‘+DATA’, ‘+FLASH’#set the below parameters for default location of data filesdb_create_file_dest='+DATA'#set the below parameters for default location of recovery areadb_recovery_file_dest='+FLASH'# set below parameter to create two members for each redodb_create_online_dest_1=’+DATA’db_create_online_dest_2=’+FLASH’# set two destinations if you want to multiplex the archive logslog_archive_dest_1='location=+DATA'log_archive_dest_2='location=+FLASH'# set the location as per your environmentlog_archive_dest_1='LOCATION=+FLASH’log_archive_format='arch_%r_%s_%t.arc'audit_file_dest =/home/oracle/admin/ORADB/adumpbackground_dump_dest=/home/oracle/admin/ORADB/bdumpcore_dump_dest =/home/oracle/admin/ORADB/cdumpuser_dump_dest =/home/oracle/admin/ORADB/udump# In case of 11g set below parameter as per your environment# diagnostic_dest= /home/apps/oracle#Set the below to the same as the production targetdb_block_size = 8192sga_target=537919488remote_login_passwordfile=exclusiveundo_management =AUTOundo_retention =10800undo_tablespace =UNDOTBS1compatible = 10.2.0.4.0

We are not using DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT parameters as most of the databases have data/redo files in multiple directories.

1.3 Configure Oracle Listener and tnsnames.ora file

Create a static listener for RAC node1 under $ORACLE_HOME/network/admin and reload because auxiliary database will not register itself with the listener:

[oracle@orarac1]$  cat $ORACLE_HOME/network/admin/listener.ora                     SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = ORADB)(ORACLE_HOME = /home/oracle/product/v10204)(SID_NAME = ORADB)))

Add TNS entry in $ORACLE_HOME/network/admin/tnsnames.ora file on Non-RAC Server:

[oracle@orasrv]$ cat $ORACLE_HOME/network/admin/tnsnames.oraORADB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = orarac1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORADB)))

1.4 Start the database in nomount and test the connectivity
Start the auxiliary database in NOMOUNT mode on RAC node1

[oracle@orarac1]$ export ORACLE_SID=ORADB[oracle@orarac1]$ export ORACLE_HOME=/home/oracle/product/v10204[oracle@orarac1>sqlplus /nologSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 19 12:42:16 2012Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.SQL> connect / as sysdbaConnected to an idle instance.SQL> startup nomountORACLE instance started.Total System Global Area  541065216 bytesFixed Size                  2085288 bytesVariable Size             289410648 bytesDatabase Buffers          239075328 bytesRedo Buffers               10493952 bytesSQL>exit[oracle@orarac1]$

Test your Sql*Net connections from Non-RAC Server and you must be able to connect to the ORADB database on RAC Node1.

[oracle@orasrv]$ sqlplus sys/xxxxxxx@ORADB as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 19 14:29:23 2012Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>

Here we are using RMAN catalog and testing the connection to RMAN Database too.

[oracle@orasrv]$ sqlplus  rman /xxxxxxx@rmancatSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 19 14:29:23 2012Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>

 
1.5 Take a backup of Non-RAC Database:

Take a backup of your database and archive logs using below script. Here we are backing the database to a NFS file system/ backup/rman/ORADB.

[oracle@orasrv]$  rman TARGET / CATALOG rman/xxxxx@rmancatRMAN> run{run {allocate channel d1 type disk;allocate channel d2 type disk;backup  database format '/backup/rman/ORADB/dbsdf_online_%d_t%t_s%s_p%p.rmn';sql "alter system archive log current";backup archivelog all delete input format '/backup/rman/ORADB/archdf_%d_t%t_s%s_p%p.rmn';release channel d1;release channel d2;}

The RMAN multiplexing will help to decrease the backup time. If the database is large, allocate morechannels and use set optimal value for filesperset and maxopenfiles. But please note that increasingfilesperset, maxopenfiles values increases process memory requirement.

In RMAN, the FILESPERSET parameter determines how many datafiles to put in each backup set and MAXOPENFILES parameter of ALLOCATE CHANNEL defines how many datafiles RMAN can read from simultaneously.

Once the backup is completed you can either mount the backup file system or copy the backup files for duplicate process on RAC environment. If you are copying the files ensure that you create the same directory structure and copy the backup files:

[oracle@orarac1]$ mkdir /backup/rman/ORADB[oracle@orarac1]$ scp oracle@orasrv:/backup/rman/ORADB/*.rmn /backup/rman/ORADB

1.6 Duplicate the database to RAC server 

In order to duplicate you must connect to the target database and auxiliary database started in NOMOUNT mode and also RMAN catalog, if you are using it.

As part of duplication process, RMAN restores the target data files to the duplicate database and performs the recovery using all available backups and archive logs.  After recovery is completed RMAN restarts the duplicate database (auxiliary database) and opens with the RESETLOGS option and generates a new DBID for duplicate database.           

(i) Place the data files in one diskgroup :  If you want to place all data files in one diskgroup then make sure you have set db_create_file_dest parameter in init.ora file

Here is the duplicate database script:

[oracle@orasrv]$ cat dup_ORADB.rmnconnect catalog rman/xxxxxxxx@rmancatconnect target /connect auxiliary sys/xxxxxxx@ORADBrun{allocate channel d1 device type disk;allocate channel d2 device type disk;allocate auxiliary channel a1 device type disk;allocate auxiliary channel a2 device type disk;duplicate target database to ORADBpfile=/home/oracle/product/v10204/dbs/initORADB.oralogfilegroup 1 ('+DATA','+FLASH') SIZE 50M reuse,group 2 ('+DATA','+FLASH') SIZE 50M reuse;release channel d1;release channel d2;}exit;

Run database duplication script as below:

[oracle@orasrv]$ $ORACLE_HOME/bin/rman cmdfile dup_ORADB.rmn | tee dup_ORADB.rmn

(ii) Place the data files in different diskgroups:  If you have more than one diskgroup and want to place data files across different disk groups then prepare the duplication script using below command.

Here we are generating the SET NEWNAME command for each datafile in the database using below script:

SYS@DBORA> set head offSYS@DBORA> set pagesize 100SYS@DBORA> SQL>  select 'set newname for datafile '||file_id||' to '''||'+DATA'||''';' from dba_data_files;set newname for datafile 1 to '+DATA';set newname for datafile 2 to '+DATA';set newname for datafile 3 to '+DATA';set newname for datafile 4 to '+DATA';set newname for datafile 5 to '+DATA';set newname for datafile 6 to '+DATA';6 rows selected.

Here we are generating the SET NEWNAME command for each tempfile in the database using below script:

SYS@DBORA >  select 'set newname for tempfile  '||file_id||' to '''||'+DATA'||''';' from dba_temp_files;set newname for tempfile  1 to '+DATA';

Replace the diskgroup name that you want to place the data/temp files.

For ex: – we are placing the data files as below.

datafile1, datafile 2, datafile3 - +DATA1
datafile 4, datafile 5, datafile 6 – +DATA2
tempfile                           – +DATA2

Prepare the duplicate database script:

[oracle@orasrv]$ cat dup_ORADB.rmnconnect catalog rman/xxxxxxxx@rmancatconnect target /connect auxiliary sys/xxxxxxx@ORADBrun{allocate channel d1 device type disk;allocate channel d2 device type disk;allocate auxiliary channel a1 device type disk;allocate auxiliary channel a2 device type disk;set newname for datafile 1 to '+DATA1';set newname for datafile 2 to '+DATA1';set newname for datafile 3 to '+DATA1';set newname for datafile 4 to '+DATA2';set newname for datafile 5 to '+DATA2';set newname for datafile 6 to '+DATA2';duplicate target database to ORADBpfile=/home/oracle/product/v10204/dbs/initORADB.oralogfilegroup 1 ('+DATA','+FLASH') SIZE 50M reuse,group 2 ('+DATA','+FLASH') SIZE 50M reuse;release channel d1;release channel d2;}exit;

Run database duplication script as below:

[oracle@orasrv]$ $ORACLE_HOME/bin/rman cmdfile dup_ORADB.rmn | tee dup_ORADB.rmnCopyright (c) 1982, 2007, Oracle.  All rights reserved.RMAN> connect catalog *2> connect target *3> connect auxiliary *4> run{5> allocate channel d1 device type disk;6> allocate channel d2 device type disk;7> allocate auxiliary channel a1 device type disk;8> allocate auxiliary channel a2 device type disk;9> set newname for datafile 1 to '+DATA';10> set newname for datafile 2 to '+DATA';11> set newname for datafile 3 to '+DATA';12> set newname for datafile 4 to '+DATA1';13> set newname for datafile 5 to '+DATA1';14> set newname for datafile 6 to '+DATA1';15> set newname for tempfile 1 to '+DATA1';16> duplicate target database to ORADB17> pfile=/home/oracle/product/v10204/dbs/initORADB.ora18> logfile19> group 1 ('+DATA','+FLASH') size 50M reuse,20> group 2 ('+DATA','+FLASH') size 50M reuse;21> release channel d1;22> release channel d2;23> }24> exit;Connected to recovery catalog databaseconnected to target database: DBORA (DBID=10026343621)connected to auxiliary database: ORADB (not mounted)allocated channel: d1channel d1: sid=201 devtype=DISK---- (Some detail removed for brevity) ----

We have duplicated single instance Non-ASM database from ‘orasrv’ server into ASM storage on orarac1 server. In part 2 I explain to to convert the single instance database to RAC.