- 01 Oct, 2023
- 0 Comments
- 16 Mins Read
Clone A RAC Database To Standalone Database Using RMAN Backup
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.
In this blog , we will see database cloning by taking RMAN backup manually( If backup is already available on source, then it can be used)
Take FULL RMAN BACKUP OF THE SOURCE RAC DATABASE :
connect rman target /
set echo on;
configure backup optimization on;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/data/BACKUP/%F';
configure maxsetsize to unlimited;
configure device type disk parallelism 8;
run
{
allocate channel c1 type disk format '/data/BACKUP/%I-%Y%M%D-%U';
allocate channel c2 type disk format '/data/BACKUP/%I-%Y%M%D-%U';
allocate channel c3 type disk format '/data/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c4 type disk format '/data/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c5 type disk format '/data/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c6 type disk format '/data/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c7 type disk format '/data/BACKUP/%I-%Y%M%D-%U' ;
allocate channel c8 type disk format '/data/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 ;
}
- scp the files to target host( where we will build standalone)
-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
- Create a new init file on target server:
*.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 initNID.ora
*.control_files='/archive/NID/control01.ctl','/archive/NID/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='NID' *.log_archive_dest_1='LOCATION=/data/archive' *.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/NID' *.log_file_name_convert='+B2BWMREDO01','/archive/NID','+B2BWMREDO02','/archive/NID'
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.
- 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: NID (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/NID/control01.ctl output file name=/archive/NID/control02.ctl Finished restore at 25-JAN-17
- Start the database in mount stage:
RMAN> alter database mount; Statement processed released channel: ORA_DISK_1
- 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/NID/system01.dbf 2 +B2BWMDB/NID/sysaux01.dbf 3 +B2BWMDB/NID/undotbs101.dbf 4 +B2BWMDB/NID/undotbs201.dbf 10 +B2BWMDB/NID/DATAFILE/users.274.905889211 9 +B2BWMDB/NID/DATAFILE/users.273.905889113 8 +B2BWMDB/NID/DATAFILE/users.272.905888783 5 +B2BWMDB/NID/users01.dbf 7 +B2BWMDB/NID/DATAFILE/users.271.905888727 6 +B2BWMDB/NID/DATAFILE/users.270.905888635 11 +B2BWMDB/NID/DATAFILE/webmindx.275.920212709 12 +B2BWMDB/NID/DATAFILE/webmdata.276.920212709Rman block will be as below
run
{
SET NEWNAME FOR DATAFILE 1 to '/archive/NID/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/archive/NID/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/archive/NID/undotbs101.dbf';
SET NEWNAME FOR DATAFILE 4 to '/archive/NID/undotbs201.dbf';
SET NEWNAME FOR DATAFILE 5 to '/archive/NID/users01.dbf';
SET NEWNAME FOR DATAFILE 6 to '/archive/NID/users.270.905888635';
SET NEWNAME FOR DATAFILE 7 to '/archive/NID/users.271.905888727';
SET NEWNAME FOR DATAFILE 8 to '/archive/NID/users.272.905888783';
SET NEWNAME FOR DATAFILE 9 to '/archive/NID/users.273.905889113';
SET NEWNAME FOR DATAFILE 10 to '/archive/NID/users.274.905889211';
SET NEWNAME FOR DATAFILE 11 to '/archive/NID/webmindx.275.920212709';
SET NEWNAME FOR DATAFILE 12 to '/archive/NID/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: NID (DBID=708694098, not open)
RMAN> run
{
2> 3> SET NEWNAME FOR DATAFILE 1 to '/archive/NID/system01.dbf';
4> SET NEWNAME FOR DATAFILE 2 to '/archive/NID/sysaux01.dbf';
5> SET NEWNAME FOR DATAFILE 3 to '/archive/NID/undotbs101.dbf';
6> SET NEWNAME FOR DATAFILE 4 to '/archive/NID/undotbs201.dbf';
SET NEWNAME FOR DATAFILE 5 to '/archive/NID/users01.dbf';
7> 8> SET NEWNAME FOR DATAFILE 6 to '/archive/NID/users.270.905888635';
9> SET NEWNAME FOR DATAFILE 7 to '/archive/NID/users.271.905888727';
10> SET NEWNAME FOR DATAFILE 8 to '/archive/NID/users.272.905888783';
11> SET NEWNAME FOR DATAFILE 9 to '/archive/NID/users.273.905889113';
12> SET NEWNAME FOR DATAFILE 10 to '/archive/NID/users.274.905889211';
SET NEWNAME FOR DATAFILE 11 to '/archive/NID/webmindx.275.920212709';
13> 14> SET NEWNAME FOR DATAFILE 12 to '/archive/NID/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/NID/undotbs101.dbf
channel ORA_DISK_1: restoring datafile 00008 to /archive/NID/users.272.905888783
channel ORA_DISK_1: reading from backup piece /data/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/NID/undotbs201.dbf
channel ORA_DISK_2: restoring datafile 00009 to /archive/NID/users.273.905889113
channel ORA_DISK_2: reading from backup piece /data/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/NID/users01.dbf
channel ORA_DISK_3: restoring datafile 00006 to /archive/NID/users.270.905888635
channel ORA_DISK_3: reading from backup piece /data/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/NID/users.274.905889211
channel ORA_DISK_4: restoring datafile 00012 to /archive/NID/webmdata.276.920212709
channel ORA_DISK_4: reading from backup piece /data/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/NID/system01.dbf
channel ORA_DISK_5: restoring datafile 00007 to /archive/NID/users.271.905888727
channel ORA_DISK_5: reading from backup piece /data/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/NID/sysaux01.dbf
channel ORA_DISK_6: restoring datafile 00011 to /archive/NID/webmindx.275.920212709
channel ORA_DISK_6: reading from backup piece /data/BACKUP/708694098-20170125-0drqtmae_1_1
channel ORA_DISK_2: errors found reading piece handle=/data/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=/data/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=/data/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=/data/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=/data/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=/data/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/NID/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=934214038 file name=/archive/NID/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=934214038 file name=/archive/NID/undotbs101.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=934214038 file name=/archive/NID/undotbs201.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=934214038 file name=/archive/NID/users01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=934214038 file name=/archive/NID/users.270.905888635
datafile 7 switched to datafile copy
input datafile copy RECID=19 STAMP=934214038 file name=/archive/NID/users.271.905888727
datafile 8 switched to datafile copy
input datafile copy RECID=20 STAMP=934214038 file name=/archive/NID/users.272.905888783
datafile 9 switched to datafile copy
input datafile copy RECID=21 STAMP=934214038 file name=/archive/NID/users.273.905889113
datafile 10 switched to datafile copy
input datafile copy RECID=22 STAMP=934214039 file name=/archive/NID/users.274.905889211
datafile 11 switched to datafile copy
input datafile copy RECID=23 STAMP=934214039 file name=/archive/NID/webmindx.275.920212709
datafile 12 switched to datafile copy
input datafile copy RECID=24 STAMP=934214039 file name=/archive/NID/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 /data/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 /data/BACKUP/708694098-20170125-0irqtmct_1_1
channel ORA_DISK_1: errors found reading piece handle=/data/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=/data/archive/1_127_898000786.dbf thread=1 sequence=127
channel ORA_DISK_2: errors found reading piece handle=/data/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=/data/archive/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.
- 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/NID/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/NID/onlinelog/redo5a.log' to '/archive/NID/redo5a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo5b.log' to '/archive/NID/redo5b.log'; alter database rename file '+B2BWMREDO01/NID/onlinelog/redo6a.log' to '/archive/NID/redo6a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo6b.log' to '/archive/NID/redo6b.log'; alter database rename file '+B2BWMREDO01/NID/onlinelog/redo7a.log' to '/archive/NID/redo7a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo7b.log' to '/archive/NID/redo7b.log'; alter database rename file '+B2BWMREDO01/NID/onlinelog/redo8a.log' to '/archive/NID/redo8a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo8b.log' to '/archive/NID/redo8b.log'; alter database rename file '+B2BWMREDO01/NID/onlinelog/redo9a.log' to '/archive/NID/redo9a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo9b.log' to '/archive/NID/redo9b.log'; alter database rename file '+B2BWMREDO01/NID/onlinelog/redo10a.log' to '/archive/NID/redo10a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo10b.log' to '/archive/NID/redo10b.log'; alter database rename file '+B2BWMREDO01/NID/onlinelog/redo11a.log' to '/archive/NID/redo11a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo11b.log' to '/archive/NID/redo11b.log'; alter database rename file '+B2BWMREDO01/NID/onlinelog/redo12a.log' to '/archive/NID/redo12a.log'; alter database rename file '+B2BWMREDO02/NID/onlinelog/redo12b.log' to '/archive/NID/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/NID/redo7a.log' ORA-00312: online log 7 thread 1: '/archive/NID/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
- 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
- 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;