Toad World Blog

RMAN Duplicate and the option NOOPEN in 11g

Aug 31, 2016 11:00:00 PM by Deiby Gomez

In this article we will see an interesting thing. When I was starting reading Oracle 12c Documentation I read that one of the enhancements of RMAN was Multi Section and another enhancement was the introduction of the option NOOPEN, however we will see that it seems that this "New Feature" was available since 11g. 

Firstable let's take a look at the Oracle Documentation:

Changes in Oracle Database 12c Release 1 (12.1.0.1)

At the time (Ago 31, 2016) that site has the list of all the "New features" of RMAN in 12c and it says that NOOPEN was introduced in 12c:

Captura de pantalla 2016-08-31 a las 22.57.53.png-840x680

 

If we click on "Specifying the State of the Duplicate Database", we will see the description of that New Feature and at the time it says the following:

 

Captura de pantalla 2016-08-31 a las 23.00.56.png-840x680

Another Source where we will see this as the new feature of 12c is the the presentation called "Oracle Recovery Manager 12c: Best Practices" that was delivered in Oracle Open Wold 2014, San Francisco by Sridhar Ranganathan (Principal Product Manager), in that presentation we can see the Page #9 where we read the following:

Captura de pantalla 2016-08-31 a las 23.06.10.png-640x480

 

A third source is the Book "Database Backup and Recovery Reference" of 12c, that  book never lies regarding the syntax:

Captura de pantalla 2016-08-31 a las 23.07.53.png-840x680

As you see there is already the option "NOOPEN" there, let's compare it with the same book but of the version 11g:

Captura de pantalla 2016-08-31 a las 23.10.01.png-640x480

As you see in 11g that book says that the syntax should not recognize the word "NOOPEN" in a Duplicate Operation, because it doesn't exist in the list of "Keywords". So let's perform some examples...

 

RMAN Duplicate with NOOPEN in 12c

Let's start with what Oracle RMAN 12c New features book, OOW and Backup & Recovery Reference say and let's perform a Duplicate in 12c using NOOPEN option:

[oracle@db12102 ~]$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 4 13:28:26 2016

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

connected to auxiliary database: DB2 (not mounted)

RMAN> duplicate database db1 to db2 backup location '/home/oracle/Backups' noopen;

Starting Duplicate Db at 04-AUG-16

contents of Memory Script:
{
sql clone "alter system set control_files = 
''/data/db2/DB2/controlfile/o1_mf_cnnz809t_.ctl'', ''/data/db2/DB2/controlfile/o1_mf_cnnz80by_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name = 
''DB1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name = 
''DB2'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/home/oracle/Backups/4ercd8nh_1_1';
alter clone database mount;
}
executing Memory Script

sql statement: alter system set control_files = ''/data/db2/DB2/controlfile/o1_mf_cnnz809t_.ctl'',
''/data/db2/DB2/controlfile/o1_mf_cnnz80by_.ctl'' comment= ''Set by RMAN'' scope=spfile


sql statement: alter system set db_name = ''DB1'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DB2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 767557632 bytes

Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes

Starting restore at 04-AUG-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/db2/DB2/controlfile/o1_mf_cnnz809t_.ctl
output file name=/data/db2/DB2/controlfile/o1_mf_cnnz80by_.ctl
Finished restore at 04-AUG-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK

contents of Memory Script:
{
set until scn 2546910;
set newname for datafile 1 to 
"/data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf";
set newname for datafile 2 to 
"/data/db2/DB1/datafile/o1_mf_tbs1_csg5jtrz_.dbf";
set newname for datafile 3 to 
"/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf";
set newname for datafile 4 to 
"/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf";
set newname for datafile 5 to 
"/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf";
set newname for datafile 6 to 
"/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf";
set newname for datafile 7 to 
"/data/db2/DB1/datafile/o1_mf_thebigfi_ct654vhm_.dbf";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

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 04-AUG-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data/db2/DB1/datafile/o1_mf_tbs1_csg5jtrz_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /data/db2/DB1/datafile/o1_mf_thebigfi_ct654vhm_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/Backups/4drcd8n1_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/Backups/4drcd8n1_1_1 tag=TAG20160804T101809
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 04-AUG-16

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=918998972 file name=/data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=918998972 file name=/data/db2/DB1/datafile/o1_mf_tbs1_csg5jtrz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=918998972 file name=/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=918998972 file name=/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=918998972 file name=/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=918998972 file name=/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=918998972 file name=/data/db2/DB1/datafile/o1_mf_thebigfi_ct654vhm_.dbf

contents of Memory Script:
{
set until scn 2546910;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-AUG-16
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=62
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/Backups/DB1_143_1_1_918987507.arc.rman
channel ORA_AUX_DISK_1: piece handle=/home/oracle/Backups/DB1_143_1_1_918987507.arc.rman tag=TAG20160804T101827
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archived log file name=/data/db2/DB2/archivelog/2016_08_04/o1_mf_1_62_ct6yvx0f_.arc thread=1 sequence=62
channel clone_default: deleting archived log(s)
archived log file name=/data/db2/DB2/archivelog/2016_08_04/o1_mf_1_62_ct6yvx0f_.arc RECID=1 STAMP=918998973
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-AUG-16
Oracle instance started

Total System Global Area 767557632 bytes

Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes

contents of Memory Script:
{
sql clone "alter system set db_name = 
''DB2'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set db_name = ''DB2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 767557632 bytes

Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB2" RESETLOGS ARCHIVELOG 
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M 
DATAFILE
'/data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for tempfile 1 to 
"/data/db2/DB1/datafile/o1_mf_temp_cnnz83hz_.tmp";
switch clone tempfile all;
catalog clone datafilecopy "/data/db2/DB1/datafile/o1_mf_tbs1_csg5jtrz_.dbf", 
"/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf", 
"/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf", 
"/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf", 
"/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf", 
"/data/db2/DB1/datafile/o1_mf_thebigfi_ct654vhm_.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data/db2/DB1/datafile/o1_mf_temp_cnnz83hz_.tmp in control file

cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_tbs1_csg5jtrz_.dbf RECID=1 STAMP=918998996
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf RECID=2 STAMP=918998996
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf RECID=3 STAMP=918998996
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf RECID=4 STAMP=918998996
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf RECID=5 STAMP=918998996
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_thebigfi_ct654vhm_.dbf RECID=6 STAMP=918998996

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=918998996 file name=/data/db2/DB1/datafile/o1_mf_tbs1_csg5jtrz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=918998996 file name=/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=918998996 file name=/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=918998996 file name=/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=918998996 file name=/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=918998996 file name=/data/db2/DB1/datafile/o1_mf_thebigfi_ct654vhm_.dbf
Leaving database unopened, as requested
Finished Duplicate Db at 04-AUG-16

RMAN> 
RMAN> select name, open_mode from v$database;

NAME OPEN_MODE 
--------- --------------------
DB2 MOUNTED

RMAN>

As we see at the end of the output, it appears a message saying that the database will not be open because we requested that and we confirmed that indeed the state of the database is "MOUNTED", if we try to apply a "alter database open" we have two options:

  • The database will open normally. This means that an "open resetlogs" was performed before. 
  • The database will raise errors saying that an "open resetlogs" should be performed first, this is the right clue that tell us that the database has never been open that's why the first open must be with "resetlogs". 

What do you think it will happen?

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 08/04/2016 14:23:40
RMAN-06136: ORACLE error from auxiliary database: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Of course!, this means that the database is fresh, new and it has never been open. This is what NOOPEN means. So we have confirmed that in 12c works! +1 for 12c.

 

RMAN Duplicate with NOOPEN in 11g

But as I am so curious I was performing this in 11g and guess what.... Well... I was going to tell you now but it is better if you finds it out by yourself emotion-2-2

Firstable I will perform exactly the same scenario that I did in 12c, a duplicate using a Backup (not Active Duplicate):

[oracle@a1 Backups]$ rman auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 26 04:32:11 2016

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

connected to auxiliary database: ORCL2 (not mounted)

RMAN> duplicate database orcl to orcl2 backup location '/home/oracle/Backups/' noopen; --I was impressed when the word "noopen" was accepted emotion-3

Starting Duplicate Db at 26-JUL-16

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes

contents of Memory Script:
{
sql clone "alter system set control_files = 
''+DATA/orcl2/controlfile/current.410.918189155'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name = 
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name = 
''ORCL2'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/home/oracle/Backups/ORCL/autobackup/2016_07_26/o1_mf_s_918189038_csg7xgrj_.bkp';
alter clone database mount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DATA/orcl2/controlfile/current.410.918189155'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''ORCL2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes

Starting restore at 26-JUL-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/orcl2/controlfile/current.410.918189155
Finished restore at 26-JUL-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=26 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=27 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=28 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=29 device type=DISK

contents of Memory Script:
{
set until scn 1252355;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

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 26-JUL-16
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/Backups/6hrbksv5_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_2: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_2: reading from backup piece /home/oracle/Backups/6irbksv6_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00009 to +DATA
channel ORA_AUX_DISK_3: restoring datafile 00010 to +DATA
channel ORA_AUX_DISK_3: reading from backup piece /home/oracle/Backups/6jrbksv6_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/Backups/6hrbksv5_1_1 tag=TAG20160726T043029
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_2: piece handle=/home/oracle/Backups/6irbksv6_1_1 tag=TAG20160726T043029
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_3: piece handle=/home/oracle/Backups/6jrbksv6_1_1 tag=TAG20160726T043029
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:15
Finished restore at 26-JUL-16

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=918189206 file name=+DATA/orcl2/datafile/system.411.918189191
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=918189206 file name=+DATA/orcl2/datafile/sysaux.412.918189191
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=918189206 file name=+DATA/orcl2/datafile/undotbs1.415.918189193
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=918189206 file name=+DATA/orcl2/datafile/users.414.918189193
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=918189206 file name=+DATA/orcl2/datafile/tbs1.413.918189193
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=918189207 file name=+DATA/orcl2/datafile/tbs2.416.918189193

contents of Memory Script:
{
set until scn 1252355;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-JUL-16
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5

starting media recovery

archived log for thread 1 with sequence 147 is already on disk as file /home/oracle/Backups/ORCL/archivelog/2016_07_26/o1_mf_1_147_csg7xfb0_.arc
archived log file name=/home/oracle/Backups/ORCL/archivelog/2016_07_26/o1_mf_1_147_csg7xfb0_.arc thread=1 sequence=147
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-JUL-16
Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes

contents of Memory Script:
{
sql clone "alter system set db_name = 
''ORCL2'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''ORCL2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG 
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M 
DATAFILE
'+DATA/orcl2/datafile/system.411.918189191'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/orcl2/datafile/sysaux.412.918189191", 
"+DATA/orcl2/datafile/undotbs1.415.918189193", 
"+DATA/orcl2/datafile/users.414.918189193", 
"+DATA/orcl2/datafile/tbs1.413.918189193", 
"+DATA/orcl2/datafile/tbs2.416.918189193";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/sysaux.412.918189191 RECID=1 STAMP=918189240
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/undotbs1.415.918189193 RECID=2 STAMP=918189240
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/users.414.918189193 RECID=3 STAMP=918189240
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/tbs1.413.918189193 RECID=4 STAMP=918189241
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/tbs2.416.918189193 RECID=5 STAMP=918189241

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=918189240 file name=+DATA/orcl2/datafile/sysaux.412.918189191
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=918189240 file name=+DATA/orcl2/datafile/undotbs1.415.918189193
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=918189240 file name=+DATA/orcl2/datafile/users.414.918189193
datafile 9 switched to datafile copy
input datafile copy RECID=4 STAMP=918189241 file name=+DATA/orcl2/datafile/tbs1.413.918189193
datafile 10 switched to datafile copy
input datafile copy RECID=5 STAMP=918189241 file name=+DATA/orcl2/datafile/tbs2.416.918189193
Leaving database unopened, as requested
Finished Duplicate Db at 26-JUL-16

RMAN> exit

Do you remember that message? it is exactly the same message that we received in 12c, the message says that the database will not be open because we requested that. It was able to understand that we requested  that in 11g? Why the word NOOPEN was accepted. The "Backup and Recovery Reference 11g" shows the syntax for DUPLICATE operation and the syntax of 11g doesn't include the word "NOOPEN" as we saw at the beginning. Anyways, it works! +1 for 11g emotion-11

But... Wait! what about if it says it was not open but it was? mmm better let's be sure.

Recovery Manager complete.
[oracle@a1 Backups]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 26 04:34:29 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ORCL2 MOUNTED

Ok, ok. Wait again, what about if it is not open now, but it was open before? if it was open before when we try to open the database, it should open normally. If it has never been open then the first opening must be with resetlogs and an error should be raised. Better let's be sure...


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Yes, now we have confirmed that NOOPEN exists and works with 11g Duplicate using a Backup (Not Active Duplicate). +1 for 11g! 

But what about if NOOPEN exists in 11g but only with Duplicate using Backup and it doesn't work with Active Duplicate? As I said I am so curious and I did another example for you...

In the following example I did an Active Duplicate using NOOPEN:

[oracle@a1 ~]$ rman target sys/manager1@orcl auxiliary sys/manager1@orcl2

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 26 04:52:10 2016

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

connected to target database: ORCL (DBID=1440690033)
connected to auxiliary database: ORCL2 (not mounted)

RMAN> 
RMAN> duplicate target database to orcl2 from active database nofilenamecheck noopen; -- The word NOOPEN was accepted with Active Duplicate emotion-3

Starting Duplicate Db at 26-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=25 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=26 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=27 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=28 device type=DISK

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes

contents of Memory Script:
{
sql clone "alter system set control_files = 
''+DATA/orcl2/controlfile/current.410.918190427'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name = 
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name = 
''ORCL2'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '+DATA/orcl2/controlfile/current.416.918190429';
sql clone "alter system set control_files = 
''+DATA/orcl2/controlfile/current.416.918190429'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DATA/orcl2/controlfile/current.410.918190427'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''ORCL2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes

Starting backup at 26-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=54 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=45 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=49 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=37 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2/db_1/dbs/snapcf_orcl.f tag=TAG20160726T045411 RECID=2 STAMP=918190451
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-JUL-16

sql statement: alter system set control_files = ''+DATA/orcl2/controlfile/current.416.918190429'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes

database mounted

contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 9 auxiliary format new
datafile 10 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script

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 backup at 26-JUL-16
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
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.262.912909191
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.912909191
channel ORA_DISK_3: starting datafile copy
input datafile file number=00009 name=+DATA/orcl/datafile/tbs1.279.918100673
channel ORA_DISK_4: starting datafile copy
input datafile file number=00010 name=+DATA/orcl/datafile/tbs2.256.918102673
channel ORA_DISK_5: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.261.912909191
output file name=+DATA/orcl2/datafile/undotbs1.411.918190487 tag=TAG20160726T045442
channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_5: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.271.912909191
output file name=+DATA/orcl2/datafile/tbs1.413.918190485 tag=TAG20160726T045442
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA/orcl2/datafile/tbs2.412.918190487 tag=TAG20160726T045442
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA/orcl2/datafile/users.417.918190491 tag=TAG20160726T045442
channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:04
output file name=+DATA/orcl2/datafile/system.415.918190483 tag=TAG20160726T045442
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:14
output file name=+DATA/orcl2/datafile/sysaux.414.918190483 tag=TAG20160726T045442
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:14
Finished backup at 26-JUL-16

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "/home/oracle/Backups/ORCL/archivelog/2016_07_26/o1_mf_1_148_csg9c199_.arc" auxiliary format 
"+DATA" ;
catalog clone start with "+DATA";
switch clone datafile all;
}
executing Memory Script

Starting backup at 26-JUL-16
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
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=148 RECID=143 STAMP=918190497
output file name=+DATA/orcl2/archivelog/2016_07_26/thread_1_seq_148.418.918190499 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 26-JUL-16

searching for all files that match the pattern +DATA

List of Files Unknown to the Database
=====================================
File Name: +data/ORCL2/ARCHIVELOG/2016_07_26/thread_1_seq_148.418.918190499
File Name: +data/ORCL2/DATAFILE/SYSTEM.415.918190483
File Name: +data/ORCL2/DATAFILE/SYSAUX.414.918190483
File Name: +data/ORCL2/DATAFILE/TBS1.413.918190485
File Name: +data/ORCL2/DATAFILE/TBS2.412.918190487
File Name: +data/ORCL2/DATAFILE/UNDOTBS1.411.918190487
File Name: +data/ORCL2/DATAFILE/USERS.417.918190491
File Name: +data/ORCL2/CONTROLFILE/Current.410.918190427
File Name: +data/ORCL/spfileorcl.ora
File Name: +data/ORCL/CONTROLFILE/Current.275.912909297
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/ORCL2/ARCHIVELOG/2016_07_26/thread_1_seq_148.418.918190499
File Name: +data/ORCL2/DATAFILE/SYSTEM.415.918190483
File Name: +data/ORCL2/DATAFILE/SYSAUX.414.918190483
File Name: +data/ORCL2/DATAFILE/TBS1.413.918190485
File Name: +data/ORCL2/DATAFILE/TBS2.412.918190487
File Name: +data/ORCL2/DATAFILE/UNDOTBS1.411.918190487
File Name: +data/ORCL2/DATAFILE/USERS.417.918190491

List of Files Which Where Not Cataloged
=======================================
File Name: +data/ORCL2/CONTROLFILE/Current.410.918190427
RMAN-07517: Reason: The file header is corrupted
File Name: +data/ORCL/spfileorcl.ora
RMAN-07518: Reason: Foreign database file DBID: 0 Database Name: 
File Name: +data/ORCL/CONTROLFILE/Current.275.912909297
RMAN-07519: Reason: Error while cataloging. See alert.log.

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=918190503 file name=+DATA/orcl2/datafile/system.415.918190483
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=918190503 file name=+DATA/orcl2/datafile/sysaux.414.918190483
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=918190503 file name=+DATA/orcl2/datafile/undotbs1.411.918190487
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=918190503 file name=+DATA/orcl2/datafile/users.417.918190491
datafile 9 switched to datafile copy
input datafile copy RECID=12 STAMP=918190503 file name=+DATA/orcl2/datafile/tbs1.413.918190485
datafile 10 switched to datafile copy
input datafile copy RECID=13 STAMP=918190503 file name=+DATA/orcl2/datafile/tbs2.412.918190487

contents of Memory Script:
{
set until scn 1253019;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-JUL-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=23 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=30 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=29 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=28 device type=DISK

starting media recovery

archived log for thread 1 with sequence 148 is already on disk as file +DATA/orcl2/archivelog/2016_07_26/thread_1_seq_148.418.918190499
archived log file name=+DATA/orcl2/archivelog/2016_07_26/thread_1_seq_148.418.918190499 thread=1 sequence=148
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-JUL-16
Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes

contents of Memory Script:
{
sql clone "alter system set db_name = 
''ORCL2'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''ORCL2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes
Variable Size 520096288 bytes
Database Buffers 1342177280 bytes
Redo Buffers 6119424 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG 
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M 
DATAFILE
'+DATA/orcl2/datafile/system.415.918190483'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/orcl2/datafile/sysaux.414.918190483", 
"+DATA/orcl2/datafile/undotbs1.411.918190487", 
"+DATA/orcl2/datafile/users.417.918190491", 
"+DATA/orcl2/datafile/tbs1.413.918190485", 
"+DATA/orcl2/datafile/tbs2.412.918190487";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/sysaux.414.918190483 RECID=1 STAMP=918190542
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/undotbs1.411.918190487 RECID=2 STAMP=918190542
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/users.417.918190491 RECID=3 STAMP=918190542
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/tbs1.413.918190485 RECID=4 STAMP=918190542
cataloged datafile copy
datafile copy file name=+DATA/orcl2/datafile/tbs2.412.918190487 RECID=5 STAMP=918190542

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=918190542 file name=+DATA/orcl2/datafile/sysaux.414.918190483
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=918190542 file name=+DATA/orcl2/datafile/undotbs1.411.918190487
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=918190542 file name=+DATA/orcl2/datafile/users.417.918190491
datafile 9 switched to datafile copy
input datafile copy RECID=4 STAMP=918190542 file name=+DATA/orcl2/datafile/tbs1.413.918190485
datafile 10 switched to datafile copy
input datafile copy RECID=5 STAMP=918190542 file name=+DATA/orcl2/datafile/tbs2.412.918190487
Leaving database unopened, as requested
Finished Duplicate Db at 26-JUL-16

it seems it worked! Let's confirm it:

[oracle@a1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 26 04:56:50 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select name , open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL2     MOUNTED

The last confirmation:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

So why Oracle didn't tell us that we could use NOOPEN since 11g? Perhaps that feature was not completed? Who knows! I am just curious! emotion-1

Tags: Oracle

Deiby Gomez

Written by Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Certifications:

  • Oracle Database Cloud Administrator Certified Associate
  • Oracle Database Cloud Administrator Certified Professional
  • Oracle Database Cloud Service Operations Certified Associate
  • Oracle Certified Expert, Oracle Database 12c Maximum Availability Architecture
  • Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration
  • Oracle Database 12c Maximum Availability Architecture Certified Master
  • Oracle Database 12c: Data Guard Administrator
  • Oracle Database 12c Administrator Certified Master (OCM 12c)
  • Oracle Real Application Clusters 12c Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Master (OCM 11g)
  • Oracle Database 12c Administrator Certified Professional (OCP 12c)
  • Oracle RAC 11g and Grid Infraestructure Administrator
  • Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
  • Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert
  • Oracle Database 11g Administrator Certified Professional (OCP 11g)
  • Oracle Linux Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Associate