Clone a RAC database to Standalone database using RMAN backup DBACLASS

Cloning from a RAC database to standalone database, can be done either using ACTIVE CLONE or by taking a RMAN backup from RAC database and restoring in standalone database.

SEE ALSO – RMAN ACTIVE CLONING

Here in this article,we will show db cloning by taking RMAN backup manually( If backup is already available on source, then it can be used)

NOTE – This cloning activity has been done on 12c , So we can run sql queries on RMAN prompt also

1. Take RMAN BACKUP OF THE SOURCE RAC DB :

connect target /
set echo on;
configure backup optimization on;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/dumparea/BACKUP/%F';
configure maxsetsize to unlimited;
configure device type disk parallelism 8;
run
{
allocate channel c1 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U';
allocate channel c2 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U';
allocate channel c3 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c4 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c5 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c6 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c7 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c8 type disk format '/dumparea/BACKUP/%I-%Y%M%D-%U' ;
backup  as compressed backupset incremental level 0  check logical database plus archivelog;
release channel c1 ;
release channel c2 ;
release channel c3 ;
release channel c4 ;
release channel c5 ;
release channel c6 ;
release channel c7 ;
release channel c8 ;
}

2. scp the files to target host( where we will build standalone)

scp the files to target:

-rw-r-----   1 oracle   dba       336896 Jan 25 13:20 708694098-20170125-0arqtldf_1_1
-rw-r-----   1 oracle   dba      1325758464 Jan 25 13:25 708694098-20170125-09rqtldf_1_1
-rw-r-----   1 oracle   dba      2909670912 Jan 25 13:30 708694098-20170125-08rqtldf_1_1
-rw-r-----   1 oracle   dba      3468737536 Jan 25 13:32 708694098-20170125-07rqtldf_1_1
-rw-r-----   1 oracle   dba      3642644480 Jan 25 13:33 708694098-20170125-06rqtldf_1_1
-rw-r-----   1 oracle   dba      3682076672 Jan 25 13:33 708694098-20170125-05rqtldf_1_1
-rw-r-----   1 oracle   dba      4360590336 Jan 25 13:35 708694098-20170125-04rqtlde_1_1
-rw-r-----   1 oracle   dba      4419856384 Jan 25 13:36 708694098-20170125-03rqtlde_1_1
-rw-r-----   1 oracle   dba      1081344 Jan 25 13:36 708694098-20170125-0brqtmae_1_1
-rw-r-----   1 oracle   dba      1466368 Jan 25 13:36 708694098-20170125-0erqtmae_1_1
-rw-r-----   1 oracle   dba      1540096 Jan 25 13:36 708694098-20170125-0frqtmae_1_1
-rw-r-----   1 oracle   dba      40550400 Jan 25 13:36 708694098-20170125-0grqtmae_1_1
-rw-r-----   1 oracle   dba      143736832 Jan 25 13:36 708694098-20170125-0crqtmae_1_1
-rw-r-----   1 oracle   dba      270655488 Jan 25 13:37 708694098-20170125-0drqtmae_1_1
-rw-r-----   1 oracle   dba       143360 Jan 25 13:37 708694098-20170125-0hrqtmct_1_1
-rw-r-----   1 oracle   dba       121856 Jan 25 13:37 708694098-20170125-0irqtmct_1_1
-rw-r-----   1 oracle   dba      19038208 Jan 25 13:37 c-708694098-20170125-00

3. Create a new init file on target server:

Make sure the two add these two below parameters if the directory structure is different in target host.
*.db_file_name_convert (< SOURCE DB DATAFILE LOCATION > , < TARGET DB_FILE LOCATION >)
*.log_file_name_convert( < SOURCE REDO LOG LOCATION > , < TARGET REDO LOG LOCATION > )

cat initWMBPRE.ora
*.control_files='/archive/CLONEDB/control01.ctl','/archive/CLONEDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='WMBPRE'
*.log_archive_dest_1='LOCATION=/archive/TESTARCH'
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1
*.diagnostic_dest='/oracle/app/oracle'
*.compatible=12.1.0.2.0
*.db_file_name_convert='+B2BWMDB','/archive/CLONEDB'
*.log_file_name_convert='+B2BWMREDO01','/archive/CLONEDB','+B2BWMREDO02','/archive/CLONEDB'

4. Start database in nomount stage:

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.1107E+10 bytes
Fixed Size                  7644464 bytes
Variable Size            5905586896 bytes
Database Buffers         5100273664 bytes
Redo Buffers               93011968 bytes
SQL> create spfile from pfile;

File created.

5. Restore the controlfile from the backup set:

rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jan 25 14:30:30 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: WMBPRE (not mounted)

RMAN> restore controlfile from '/archive/BACKUP/c-708694098-20170125-00';

Starting restore at 25-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=186 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/archive/CLONEDB/control01.ctl
output file name=/archive/CLONEDB/control02.ctl
Finished restore at 25-JAN-17

6. Start the database in mount stage:

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

7. Catalog the backup sets which copied

RMAN> catalog start with  '/archive/BACKUP';


searching for all files that match the pattern /archive/BACKUP

List of Files Unknown to the Database
=====================================
File Name: /archive/BACKUP/708694098-20170125-07rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-06rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0hrqtmct_1_1
File Name: /archive/BACKUP/708694098-20170125-05rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0irqtmct_1_1
File Name: /archive/BACKUP/c-708694098-20170125-00
File Name: /archive/BACKUP/rman_full.sh
File Name: /archive/BACKUP/708694098-20170125-08rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0crqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-09rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0brqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0erqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0drqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0grqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0frqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-04rqtlde_1_1
File Name: /archive/BACKUP/708694098-20170125-03rqtlde_1_1
File Name: /archive/BACKUP/708694098-20170125-0arqtldf_1_1

Do you really want to catalog the above files (enter YES or NO)?
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /archive/BACKUP/708694098-20170125-07rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-06rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0hrqtmct_1_1
File Name: /archive/BACKUP/708694098-20170125-05rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0irqtmct_1_1
File Name: /archive/BACKUP/c-708694098-20170125-00
File Name: /archive/BACKUP/708694098-20170125-08rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0crqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-09rqtldf_1_1
File Name: /archive/BACKUP/708694098-20170125-0brqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0erqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0drqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0grqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-0frqtmae_1_1
File Name: /archive/BACKUP/708694098-20170125-04rqtlde_1_1
File Name: /archive/BACKUP/708694098-20170125-03rqtlde_1_1
File Name: /archive/BACKUP/708694098-20170125-0arqtldf_1_1

8. Now prepare the rman restore script.
First get the datafile details from source(RAC DB)

set lines 299
col file_name for a67
set pagesize 2000
select file_id,file_name from dba_data_files;


   FILE_ID FILE_NAME
---------- -------------------------------------------------------------------
         1 +B2BWMDB/WMBPRE/system01.dbf
         2 +B2BWMDB/WMBPRE/sysaux01.dbf
         3 +B2BWMDB/WMBPRE/undotbs101.dbf
         4 +B2BWMDB/WMBPRE/undotbs201.dbf
        10 +B2BWMDB/WMBPRE/DATAFILE/users.274.905889211
         9 +B2BWMDB/WMBPRE/DATAFILE/users.273.905889113
         8 +B2BWMDB/WMBPRE/DATAFILE/users.272.905888783
         5 +B2BWMDB/WMBPRE/users01.dbf
         7 +B2BWMDB/WMBPRE/DATAFILE/users.271.905888727
         6 +B2BWMDB/WMBPRE/DATAFILE/users.270.905888635
        11 +B2BWMDB/WMBPRE/DATAFILE/webmindx.275.920212709
        12 +B2BWMDB/WMBPRE/DATAFILE/webmdata.276.920212709

Rman block will be as below

run
{
SET NEWNAME FOR DATAFILE 1 to '/archive/CLONEDB/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/archive/CLONEDB/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/archive/CLONEDB/undotbs101.dbf';
SET NEWNAME FOR DATAFILE 4 to '/archive/CLONEDB/undotbs201.dbf';
SET NEWNAME FOR DATAFILE 5 to '/archive/CLONEDB/users01.dbf';
SET NEWNAME FOR DATAFILE 6 to '/archive/CLONEDB/users.270.905888635';
SET NEWNAME FOR DATAFILE 7 to '/archive/CLONEDB/users.271.905888727';
SET NEWNAME FOR DATAFILE 8 to '/archive/CLONEDB/users.272.905888783';
SET NEWNAME FOR DATAFILE 9 to '/archive/CLONEDB/users.273.905889113';
SET NEWNAME FOR DATAFILE 10 to '/archive/CLONEDB/users.274.905889211';
SET NEWNAME FOR DATAFILE 11 to '/archive/CLONEDB/webmindx.275.920212709';
SET NEWNAME FOR DATAFILE 12 to '/archive/CLONEDB/webmdata.276.920212709';
restore database;
switch datafile all;
recover database;
}

Below is the output log:

[@sec60-1]/archive/BACKUP# rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jan 25 15:50:33 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: WMBPRE (DBID=708694098, not open)

RMAN> run
{
2> 3> SET NEWNAME FOR DATAFILE 1 to '/archive/CLONEDB/system01.dbf';
4> SET NEWNAME FOR DATAFILE 2 to '/archive/CLONEDB/sysaux01.dbf';
5> SET NEWNAME FOR DATAFILE 3 to '/archive/CLONEDB/undotbs101.dbf';
6> SET NEWNAME FOR DATAFILE 4 to '/archive/CLONEDB/undotbs201.dbf';
SET NEWNAME FOR DATAFILE 5 to '/archive/CLONEDB/users01.dbf';
7> 8> SET NEWNAME FOR DATAFILE 6 to '/archive/CLONEDB/users.270.905888635';
9> SET NEWNAME FOR DATAFILE 7 to '/archive/CLONEDB/users.271.905888727';
10> SET NEWNAME FOR DATAFILE 8 to '/archive/CLONEDB/users.272.905888783';
11> SET NEWNAME FOR DATAFILE 9 to '/archive/CLONEDB/users.273.905889113';
12> SET NEWNAME FOR DATAFILE 10 to '/archive/CLONEDB/users.274.905889211';
SET NEWNAME FOR DATAFILE 11 to '/archive/CLONEDB/webmindx.275.920212709';
13> 14> SET NEWNAME FOR DATAFILE 12 to '/archive/CLONEDB/webmdata.276.920212709';
15> restore database;
16> switch datafile all;
recover database;
17> 18> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=186 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=191 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=196 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=201 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=206 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=211 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=216 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=221 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /archive/CLONEDB/undotbs101.dbf
channel ORA_DISK_1: restoring datafile 00008 to /archive/CLONEDB/users.272.905888783
channel ORA_DISK_1: reading from backup piece /dumparea/BACKUP/708694098-20170125-0erqtmae_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00004 to /archive/CLONEDB/undotbs201.dbf
channel ORA_DISK_2: restoring datafile 00009 to /archive/CLONEDB/users.273.905889113
channel ORA_DISK_2: reading from backup piece /dumparea/BACKUP/708694098-20170125-0frqtmae_1_1
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00005 to /archive/CLONEDB/users01.dbf
channel ORA_DISK_3: restoring datafile 00006 to /archive/CLONEDB/users.270.905888635
channel ORA_DISK_3: reading from backup piece /dumparea/BACKUP/708694098-20170125-0brqtmae_1_1
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00010 to /archive/CLONEDB/users.274.905889211
channel ORA_DISK_4: restoring datafile 00012 to /archive/CLONEDB/webmdata.276.920212709
channel ORA_DISK_4: reading from backup piece /dumparea/BACKUP/708694098-20170125-0grqtmae_1_1
channel ORA_DISK_5: starting datafile backup set restore
channel ORA_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_DISK_5: restoring datafile 00001 to /archive/CLONEDB/system01.dbf
channel ORA_DISK_5: restoring datafile 00007 to /archive/CLONEDB/users.271.905888727
channel ORA_DISK_5: reading from backup piece /dumparea/BACKUP/708694098-20170125-0crqtmae_1_1
channel ORA_DISK_6: starting datafile backup set restore
channel ORA_DISK_6: specifying datafile(s) to restore from backup set
channel ORA_DISK_6: restoring datafile 00002 to /archive/CLONEDB/sysaux01.dbf
channel ORA_DISK_6: restoring datafile 00011 to /archive/CLONEDB/webmindx.275.920212709
channel ORA_DISK_6: reading from backup piece /dumparea/BACKUP/708694098-20170125-0drqtmae_1_1
channel ORA_DISK_2: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0frqtmae_1_1
channel ORA_DISK_2: failover to piece handle=/archive/BACKUP/708694098-20170125-0frqtmae_1_1 tag=TAG20170125T133613
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0erqtmae_1_1
channel ORA_DISK_1: failover to piece handle=/archive/BACKUP/708694098-20170125-0erqtmae_1_1 tag=TAG20170125T133613
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_4: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0grqtmae_1_1
channel ORA_DISK_4: failover to piece handle=/archive/BACKUP/708694098-20170125-0grqtmae_1_1 tag=TAG20170125T133613
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:26
channel ORA_DISK_5: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0crqtmae_1_1
channel ORA_DISK_5: failover to piece handle=/archive/BACKUP/708694098-20170125-0crqtmae_1_1 tag=TAG20170125T133613
channel ORA_DISK_5: restored backup piece 1
channel ORA_DISK_5: restore complete, elapsed time: 00:00:45
channel ORA_DISK_6: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0drqtmae_1_1
channel ORA_DISK_6: failover to piece handle=/archive/BACKUP/708694098-20170125-0drqtmae_1_1 tag=TAG20170125T133613
channel ORA_DISK_6: restored backup piece 1
channel ORA_DISK_6: restore complete, elapsed time: 00:01:15
channel ORA_DISK_3: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0brqtmae_1_1
channel ORA_DISK_3: failover to piece handle=/archive/BACKUP/708694098-20170125-0brqtmae_1_1 tag=TAG20170125T133613
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:02:36
Finished restore at 25-JAN-17

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=934214038 file name=/archive/CLONEDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=934214038 file name=/archive/CLONEDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=934214038 file name=/archive/CLONEDB/undotbs101.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=934214038 file name=/archive/CLONEDB/undotbs201.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=934214038 file name=/archive/CLONEDB/users01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=934214038 file name=/archive/CLONEDB/users.270.905888635
datafile 7 switched to datafile copy
input datafile copy RECID=19 STAMP=934214038 file name=/archive/CLONEDB/users.271.905888727
datafile 8 switched to datafile copy
input datafile copy RECID=20 STAMP=934214038 file name=/archive/CLONEDB/users.272.905888783
datafile 9 switched to datafile copy
input datafile copy RECID=21 STAMP=934214038 file name=/archive/CLONEDB/users.273.905889113
datafile 10 switched to datafile copy
input datafile copy RECID=22 STAMP=934214039 file name=/archive/CLONEDB/users.274.905889211
datafile 11 switched to datafile copy
input datafile copy RECID=23 STAMP=934214039 file name=/archive/CLONEDB/webmindx.275.920212709
datafile 12 switched to datafile copy
input datafile copy RECID=24 STAMP=934214039 file name=/archive/CLONEDB/webmdata.276.920212709

Starting recover at 25-JAN-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=127
channel ORA_DISK_1: reading from backup piece /dumparea/BACKUP/708694098-20170125-0hrqtmct_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=2 sequence=42
channel ORA_DISK_2: reading from backup piece /dumparea/BACKUP/708694098-20170125-0irqtmct_1_1
channel ORA_DISK_1: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0hrqtmct_1_1
channel ORA_DISK_1: failover to piece handle=/archive/BACKUP/708694098-20170125-0hrqtmct_1_1 tag=TAG20170125T133733
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/archive/TESTARCH/1_127_898000786.dbf thread=1 sequence=127
channel ORA_DISK_2: errors found reading piece handle=/dumparea/BACKUP/708694098-20170125-0irqtmct_1_1
channel ORA_DISK_2: failover to piece handle=/archive/BACKUP/708694098-20170125-0irqtmct_1_1 tag=TAG20170125T133733
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=/archive/TESTARCH/2_42_898000786.dbf thread=2 sequence=42
unable to find archived log
archived log thread=1 sequence=128
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/25/2017 15:55:05
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 128 and starting SCN of 44200356

You can the ignore the error printed in the end.

9. Now open the database in resetlogs:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/25/2017 15:59:27
ORA-00349: failure obtaining block size for '+B2BWMREDO01/WMBPRE/onlinelog/redo5a.log'
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-29701: unable to connect to Cluster Synchronization Service

It failed with , as it dont recognize the redologs. Because in controlfile, the redolog location is that of source .
So we need to rename the redologs as per the target host db.

alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo5a.log' to '/archive/CLONEDB/redo5a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo5b.log' to '/archive/CLONEDB/redo5b.log';
alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo6a.log' to '/archive/CLONEDB/redo6a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo6b.log' to '/archive/CLONEDB/redo6b.log';
alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo7a.log' to '/archive/CLONEDB/redo7a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo7b.log' to '/archive/CLONEDB/redo7b.log';
alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo8a.log' to '/archive/CLONEDB/redo8a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo8b.log' to '/archive/CLONEDB/redo8b.log';
alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo9a.log' to '/archive/CLONEDB/redo9a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo9b.log' to '/archive/CLONEDB/redo9b.log';
alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo10a.log' to '/archive/CLONEDB/redo10a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo10b.log' to '/archive/CLONEDB/redo10b.log';
alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo11a.log' to '/archive/CLONEDB/redo11a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo11b.log' to '/archive/CLONEDB/redo11b.log';
alter database rename file '+B2BWMREDO01/WMBPRE/onlinelog/redo12a.log' to '/archive/CLONEDB/redo12a.log';
alter database rename file '+B2BWMREDO02/WMBPRE/onlinelog/redo12b.log' to '/archive/CLONEDB/redo12b.log';

Tried to open again, but got a new error

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/25/2017 16:05:27
ORA-00392: log 7 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 7 thread 1: '/archive/CLONEDB/redo7a.log'
ORA-00312: online log 7 thread 1: '/archive/CLONEDB/redo7b.log'

RMAN> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         5          1 CLEARING
         6          1 CLEARING
         7          1 CLEARING_CURRENT
         8          1 CLEARING
         9          2 CLEARING_CURRENT
        10          2 CLEARING
        11          2 CLEARING
        12          2 CLEARING

We can see the status of group 7 and grup 9 are in clearing_current MODE. So we clear them manually.

RMAN> alter database clear logfile group 7;

Statement processed

RMAN> alter database clear logfile group 9;

Statement processed

RMAN> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         5          1 CLEARING
         6          1 CLEARING
         7          1 CURRENT
         8          1 CLEARING
         9          2 CURRENT
        10          2 CLEARING
        11          2 CLEARING
        12          2 CLEARING

8 rows selected

10.Open in resetlog now:

RMAN> alter database open resetlogs;

Database altered

11. Clear the thread 2 log members.

As we have clone from a 2 node RAC, so it has also 2 threads. Drop the thread 2 log.

SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

		 
SQL>  alter database disable thread 2;

Database altered.

SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED DISABLED


		 
SQL> select group# from v$log where THREAD#=2;

    GROUP#
----------
         9
        10
        11
        12

SQL> alter database drop logfile group 9;

Database altered.

SQL>  alter database drop logfile group 10;

Database altered.

SQL> alter database drop logfile group 11;

Database altered.

SQL> alter database drop logfile group 12;

Database altered.

SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC

12. drop the undo tablespace of other instance:

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL>  drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1

If you are gettting ORA-38856: cannot mark instance error while doing open resetlog, Then use the below hidden parameter

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

SQL> alter system set “_no_recovery_through_resetlogs”=TRUE scope=both;

System altered.

5 thoughts on “Clone a RAC database to Standalone database using RMAN backup”

Dinesh says:

January 27, 2017 at 4:45 pm

Good one…

1. Admin says:
  
  January 27, 2017 at 5:48 pm
  
  Thanks Dinesh.
  
Nares says:

July 12, 2017 at 5:10 pm

Nice Approach .

hlaine ei says:

February 17, 2022 at 10:12 am

when I restore control file from 2node RAC to single instance I got the below error. pls explain
connected to target database: UBSPRD (not mounted)

RMAN> restore controlfile from ‘/tmp/control01.ctl’;

Starting restore at 17-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/17/2022 16:41:00
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

1. Admin says:
  
  February 17, 2022 at 1:57 pm
  
  ‘/tmp/control01.ctl’ this is not a valid rman backup piece.

Clone a RAC database to Standalone database using RMAN backup

About Admin

5 thoughts on “Clone a RAC database to Standalone database using RMAN backup”

Leave a Reply Cancel reply

About Admin

Related Articles

5 thoughts on “Clone a RAC database to Standalone database using RMAN backup”

Leave a Reply Cancel reply