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
123456789101112131415161718192021222324252627282930313233343536373839404142434445
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 ;
}
-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-00123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
*.db_file_name_convert (< SOURCE DB DATAFILE LOCATION > , < TARGET DB_FILE LOCATION >)
*.log_file_name_convert( < SOURCE REDO LOG LOCATION > , < TARGET REDO LOG LOCATION > )
*.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'
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.
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-17123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
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
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.920212709
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;
}
[@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 44200356123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130
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
RMAN> alter database open resetlogs;
Database altered
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 PUBLIC1234567891011121314151617181920212223
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;Please to add comments
No comments yet. Be the first to comment!