Duplicate 10gR2 RAC database instances to non-RAC single database instance

In this blog we will discuss how to duplicate RAC database, which is running on ASM, to non-ASM single node database instance.

Connect to your source RAC database and find out how much space you need for your target database filesystem.

SQL> select sum(bytes)/(1024)/1024/1024 as "Size in GB" from v$datafile;

Size in GB
---------------------------
63.06

SQL> select sum(bytes)/(1024)/1024/1024 as "Size in GB" from v$tempfile;

Size in GB
---------------------------
5.76

So we need approximately around 69 GB and lets see how much space ASM diskgroups displays on source database. I will be restoring these ASM Oracle database files into /u02 filesystem.

SQL> select name,total_mb/1024 as "Total Size(GB)",Free_mb/(1024) "Free(GB)" from v$asm_diskgroup;

NAME                                Total Size (GB)   Free(GB)
------------------------------ ------------------   --------------
ARCHLOGS                          10.98               10.82
DATA                                   100.48              20.62
TEMPFILE                            20.98               14.10


Connect to RAC database node and create scripts to change file location from ASM to non-ASM(/u02) . You can ignore this step if you are cloning to ASM with same DISKGROUP name.

SQL > connect  system/oracle@RAC-node1
SQL > spool clone.rman
SQL > select 'set newname for datafile '||file#||' to '''||'/u02/'||substr(name,'20','40')||''';' from v$datafile
            union
           select 'set newname for tempfile '||file#||' to '''||'/u02/'||substr(name,'20','40')||''';' from v$tempfile;
SQL>         
set newname for datafile 1 to '/u02/system.ora';
set newname for datafile 2 to '/u02/sysaux.ora';
set newname for datafile 3 to '/u02/tools.ora';
set newname for datafile 4 to '/u02/undotbs1.ora';
set newname for datafile 5 to '/u02/undotbs2.ora';
set newname for datafile 6 to '/u02/users.ora';
set newname for datafile 7 to '/u02/indx.ora';
set newname for datafile 8 to '/u02/undotbs2.ora';
set newname for datafile 9 to '/u02/xdb.ora';
set newname for tempfile 2 to '/u02/temp1';
SQL> spool off;

We have full and archlogs backups on disk(/backup) of  RAC database on shared filesystem. FTP your backups from source /backup location to target server on /backup.

 In this example we are cloning it to ORCL auxiliary intance on a seperate server, though you can clone it on one node of RAC servers. Create an auxliary instance ORCL pfile on your target server $ORACLE_HOME/dbs/ directory

Please copy clone.rman to target server location and make following changes and after changes your clone.rman script will look something like this.

In this example I am cloning to the point in time so I added the following line inside clone.rman script

    set until time "to_date('10-Nov-2009 14:30:00', 'DD-MON-YYYY HH24:MI:SS')";

You can comment this line if you want to clone up to the point of last backup.

run
{
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;

set newname for datafile 1 to '/u02/system.ora';
set newname for datafile 2 to '/u02/sysaux.ora';
set newname for datafile 3 to '/u02/tools.ora';
set newname for datafile 4 to '/u02/undotbs1.ora';
set newname for datafile 5 to '/u02/undotbs2.ora';
set newname for datafile 6 to '/u02/users.ora';
set newname for datafile 7 to '/u02/indx.ora';
set newname for datafile 8 to '/u02/undotbs2.ora';
set newname for datafile 9 to '/u02/xdb.ora';
set newname for tempfile 2 to '/u02/temp1';
set until time "to_date('10-Nov-2009 14:35:00', 'DD-MON-YYYY HH24:MI:SS')";

duplicate target database to ORCL
logfile
group 1 ('/u02/log01.log') size 50m,
group 2 ('/u02/log02.log') size 50m,
group 3 ('/u02/log03.log') size 50m;
}


Set the auxiliary instance SID and connect to catalog, target instance, auxiliary instance and run clone.rman script.

$ export ORACLE_SID=ORCL
$ sqlplus /nolog
   startup nomount;

$ORACLE_HOME/bin/rman target rman_user/rmanuser@rac-node1 rcvcat rman_catlog/rman_catalog@catalog auxiliary /

run
{
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;

set newname for datafile 1 to '/u02/system.ora';
set newname for datafile 2 to '/u02/sysaux.ora';
set newname for datafile 3 to '/u02/tools.ora';
set newname for datafile 4 to '/u02/undotbs1.ora';
set newname for datafile 5 to '/u02/undotbs2.ora';
set newname for datafile 6 to '/u02/users.ora';
set newname for datafile 7 to '/u02/indx.ora';
set newname for datafile 8 to '/u02/undotbs2.ora';
set newname for datafile 9 to '/u02/xdb.ora';
set newname for tempfile 2 to '/u02/temp1';

set until time "to_date('10-Nov-2010 14:35:00', 'DD-MON-YYYY HH24:MI:SS')";

duplicate target database to ORCL
logfile
group 1 ('/u02/log01.log') size 50m,
group 2 ('/u02/log02.log') size 50m,
group 3 ('/u02/log03.log') size 50m;
}

RMAN will start restoring datafile to new location and recover upto the specified parameter in to_date.

While rman is ready to open database with

alter database open resetlogs

it fails with

ORACLE error from auxiliary database:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

This is due to bug: 4355382 ORA-38856: Failed  to open database with resetlogs when using RAC backup in 10g R2. The target database is a RAC database being duplicated to a single instance database
and the problem is related to the number of threads used by the source database and an expectation that the cloned database must have an identical number of threads.

To fix this problem do this

Set the following parameter in the auxiliary init.ora file:
_no_recovery_through_resetlogs=TRUE

Then open auxiliary database with resetlogs;

Once the auxiliary has opened, removed this hidden parameter

Your single instance non-asm clone is ready for your RAC database.