Oracle 12c: NOOPEN option for RMAN duplicate

July 3rd, 2013 | Posted in 12c, Blog, RMAN, RMAN duplicate oracle | 1 Comment

As part of the Oracle database 12c testing, I am sharing a step by step instructions to duplicate the root container database using the new “NOOPEN” option available from 12cR1 version.


In the following post:

-The database named “ORAWISS” is the container root database also called target database.
-The database named “ORAWISS12C” is the PLUGGABLE database belongs to “ORAWISS” root container database.
-The database named “TEST” is the auxiliary database.
-Active duplication will be used with the new “NOOPEN” option available since 12cR1 version.
- Recovery Manager (RMAN) will be used to perform the database duplication.

As part of the duplicating operation with the new “NOOPEN” option,

RMAN automates the following steps:

1. Creates a control file for the duplicate database
2. Restarts the auxiliary instance and mounts the duplicate control file
3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs.
4. Bring the duplicate database with the MOUNT state. Note without the NOOPEN option, the duplicate procedure opens the database with RESETLOGS option.

I find “NOOPEN” option useful for physical standby database creation, leaving the standby database in mount state.

1. Prepare the auxiliary database;

1.1 Creating directories;

- create the appropriate directories to hold the data files / online log / temp files etc..

mkdir -p /home/oracle/wissem/datafile/test
mkdir -p /home/oracle/wissem/datafile/test2
mkdir -p /home/oracle/wissem/datafile/test3
mkdir -p /home/oracle/wissem/onlinelog/test
mkdir -p /home/oracle/wissem/tempfile

1.2 Creating initialization Parameter file for the Auxiliary instance;

I am specifying all the required parameters in the pfile;

sandbox1(test):/home/oracle/wissem>cat inittest.ora
DB_NAME=TEST
DB_FILE_NAME_CONVERT='+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE','/home/oracle/wissem/datafile/test2','+DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE','/home/oracle/wissem/datafile/test3','+DATA/ORAWISS/DATAFILE','/home/oracle/wissem/datafile/test','+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/TEMPFILE','/home/oracle/wissem/tempfile','+DATA/ORAWISS/TEMPFILE','/home/oracle/wissem/tempfile'
LOG_FILE_NAME_CONVERT =(+DATA/ORAWISS/ONLINELOG','/home/oracle/wissem/onlinelog/test')
SGA_TARGET=262144000
CONTROL_FILES='/home/oracle/wissem/datafile/test/control01.dbf'

1.3 Add entry to /etc/oratab file (optional):

I have a script to set all environment parameters ORACLE_SID, ORACLE_HOME etc based on /etc/oratab file that’s why I am adding reference to the new
auxiliary database here;

sandbox1(test):/home/oracle/wissem>cat /etc/oratab
...
mytstdb2:/opt/11.2.0.3/grid/:N          # line added by Agent
+ASM:/opt/app/12.1/grid:N:              # line added by Agent
orawiss:/opt/app/oracle/product/12.1/db_1:N:            # line added by Agent
test:/opt/app/oracle/product/12.1/db_1:N:
sandbox1(test):/home/oracle/wissem>

1.4 Create an Oracle Password File for the Auxiliary Instance;

As the auxiliary is in the same host, i am just copying locally the password file from the target root database.

sandbox1(test):/opt/app/oracle/product/12.1/db_1/dbs>cp orapwwiss12c2 orapwtest

1.5 Oracle Net Connectivity to the Auxiliary Instance;

Auxiliary instance must be available through Oracle Net because we are going to duplicate the database from an ACTIVE database.
I create the necessary oracle NET connectivity in the listener.ora and the tnsnames.ora file

sandbox1(+ASM):/home/oracle/wissem>cat /opt/app/oracle/product/12.1/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/12.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORAWISS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sandbox1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orawiss)
    )
  )

ORAWISS12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sandbox1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORAWISS12C)
    )
  )

ORAWISS12 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orawiss12)
    )
  )

TEST = (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sandbox1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )


sandbox1(+ASM):/home/oracle/wissem>
sandbox1(+ASM):/home/oracle/wissem>cat /opt/app/12.1/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/app/12.1/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sandbox1.domain.com)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
          (GLOBAL_DBNAME = test)
          (ORACLE_HOME = /opt/app/oracle/product/12.1/db_1)
          (SID_NAME = test)
    )
   )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
sandbox1(+ASM):/home/oracle/wissem>

reload the listener if necessary;

sandbox1(+ASM):/home/oracle/wissem>lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 03-JUL-2013 06:45:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1.domain.com)(PORT=1522)))
The command completed successfully

Ensure all the services are available:

sandbox1(+ASM):/home/oracle/wissem>lsnrctl services

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 03-JUL-2013 06:45:32

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox1.domain.com)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orawiss" has 1 instance(s).
  Instance "orawiss", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orawiss12c" has 1 instance(s).
  Instance "orawiss", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orawissXDB" has 1 instance(s).
  Instance "orawiss", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=sandbox1.domain.com)(PORT=26070))
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

Test the connections to the target and the auxiliary database;

sandbox1(orawiss):/home/oracle/wissem>tnsping test

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 03-JUL-2013 06:46:37

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sandbox1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TEST)))
OK (10 msec)
sandbox1(test):/home/oracle/wissem>tnsping orawiss

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 03-JUL-2013 09:15:16

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sandbox1)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = orawiss)))
OK (10 msec)
sandbox1(test):/home/oracle/wissem>

Test connection from sqlplus;

sandbox1(test):/opt/app/oracle/product/12.1/db_1/dbs>sqlplus sys@test as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 07:37:44 2013

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

1.6 Start the Auxiliary instance in NOMOUNT state:

Set the environment ORACLE_SID and ORACLE_HOME to test. (Remember Test is our auxiliary database)

sandbox1(test):/home/oracle/wissem>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 06:38:36 2013

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/wissem/inittest.ora';
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2287280 bytes
Variable Size             125831504 bytes
Database Buffers          130023424 bytes
Redo Buffers                4907008 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2. Errors to avoid during duplication procedure:

These are the errors you may encounter during the duplication process.

NOTE: if you try to do the active duplication connecting from pluggable database, you will get an error;

RMAN-05501: aborting duplication of target database
RMAN-07536: command not allowed when connected to a Pluggable Database

Example of the error: (Note orawiss12c is a Pluggable Database);

sandbox1(test):/opt/app/oracle/product/12.1/db_1/dbs>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jul 3 07:38:07 2013

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

RMAN> connect target wissem/wissem@orawiss12c

connected to target database: ORAWISS (DBID=3257067578)

RMAN> connect AUXILIARY sys@test

auxiliary database Password:
connected to auxiliary database: TEST (not mounted)

RMAN>

RMAN> DUPLICATE TARGET DATABASE
2> TO 'TEST'
3> FROM ACTIVE DATABASE
4> DB_FILE_NAME_CONVERT '+DATA/ORAWISS/DATAFILE','/home/oracle/wissem/datafile/test' NOPEN;

Starting Duplicate Db at 07/03/2013 07:41:14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/03/2013 07:41:15
RMAN-05501: aborting duplication of target database
RMAN-07536: command not allowed when connected to a Pluggable Database

Note: If you don’t specify COMPATIBLE parameter to init auxiliary file, you will get the following error;

ERROR *******************************************************************
COMPATIBLE parameter is very important to add in init.ora file;
RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 12.1.0.0.0 incompatible with ORACLE version 12.0.0.0.0
ORA-00202: control file: '/home/oracle/wissem/datafile/test/control01.dbf'
ERROR *******************************************************************

To fix the above error, we modify the auxiliary init file and add “COMPATIBLE=12.1.0.0.0″:

sandbox1(test):/home/oracle/wissem>cat inittest.ora
DB_NAME=TEST
DB_FILE_NAME_CONVERT='+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE','/home/oracle/wissem/datafile/test2','+DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE','/home/oracle/wissem/datafile/test3','+DATA/ORAWISS/DATAFILE','/home/oracle/wissem/datafile/test','+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/TEMPFILE','/home/oracle/wissem/tempfile','+DATA/ORAWISS/TEMPFILE','/home/oracle/wissem/tempfile'
LOG_FILE_NAME_CONVERT =(+DATA/ORAWISS/ONLINELOG','/home/oracle/wissem/onlinelog/test')
SGA_TARGET=262144000
CONTROL_FILES='/home/oracle/wissem/datafile/test/control01.dbf'
COMPATIBLE=12.1.0.0.0
sandbox1(test):/home/oracle/wissem>

Note: if If you don’t specify enable_pluggable_database parameter to init auxiliary file, you will get the following error;

ERROR *******************************************************************
RMAN-03002: failure of Duplicate Db command at 07/03/2013 07:50:55
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-65093: container database not set up properly
ERROR *******************************************************************

To Fix add:enable_pluggable_database=true; to init file of auxilairy DB:

sandbox1(test):/home/oracle/wissem>cat inittest.ora
DB_NAME=TEST
DB_FILE_NAME_CONVERT='+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE','/home/oracle/wissem/datafile/test2','+DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE','/home/oracle/wissem/datafile/test3','+DATA/ORAWISS/DATAFILE','/home/oracle/wissem/datafile/test','+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/TEMPFILE','/home/oracle/wissem/tempfile','+DATA/ORAWISS/TEMPFILE','/home/oracle/wissem/tempfile'
LOG_FILE_NAME_CONVERT =(+DATA/ORAWISS/ONLINELOG','/home/oracle/wissem/onlinelog/test')
SGA_TARGET=262144000
CONTROL_FILES='/home/oracle/wissem/datafile/test/control01.dbf'
COMPATIBLE=12.1.0.0.0
enable_pluggable_database=true
sandbox1(test):/home/oracle/wissem>

3. Run the DUPLICATE database command with NOOPEN option:

We will use now the correct init file of the auxilairy database:

sandbox1(test):/home/oracle/wissem>cat inittest.ora
DB_NAME=TEST
DB_FILE_NAME_CONVERT='+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/DATAFILE','/home/oracle/wissem/datafile/test2','+DATA/ORAWISS/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE','/home/oracle/wissem/datafile/test3','+DATA/ORAWISS/DATAFILE','/home/oracle/wissem/datafile/test','+DATA/ORAWISS/E011004AA64F0CF9E0433514DA0A096B/TEMPFILE','/home/oracle/wissem/tempfile','+DATA/ORAWISS/TEMPFILE','/home/oracle/wissem/tempfile'
LOG_FILE_NAME_CONVERT =(+DATA/ORAWISS/ONLINELOG','/home/oracle/wissem/onlinelog/test')
SGA_TARGET=262144000
CONTROL_FILES='/home/oracle/wissem/datafile/test/control01.dbf'
COMPATIBLE=12.1.0.0.0
enable_pluggable_database=true
sandbox1(test):/home/oracle/wissem>

Invoke RMAN and connect to the target root database (orawiss), auxiliary database (test in noumount state).
With the NOOPEN option, rman will complete its tasks and leave the cloned database in a MOUNT state ready for opening.
In prior versions the duplicated database was automatically opened by RMAN as the last step of the process.

sandbox1(test):/home/oracle/wissem>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 08:54:38 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown abort;
ORACLE instance shut down.
SQL>  startup nomount pfile='/home/oracle/wissem/inittest.ora';
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2287280 bytes
Variable Size             125831504 bytes
Database Buffers          130023424 bytes
Redo Buffers                4907008 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

NOTE here the use of NOOPEN option:

DUPLICATE TARGET DATABASE TO ‘TEST’ FROM ACTIVE DATABASE NOOPEN;

sandbox1(test):/home/oracle/wissem>rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jul 3 08:54:59 2013

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

RMAN>  connect target sys@orawiss

target database Password:
connected to target database: ORAWISS (DBID=3257067578)

RMAN> connect AUXILIARY sys@test

auxiliary database Password:
connected to auxiliary database: TEST (not mounted)

RMAN> DUPLICATE TARGET DATABASE  TO 'TEST'  
FROM ACTIVE DATABASE NOOPEN;

Starting Duplicate Db at 07/03/2013 08:55:37
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=172 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=13 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=173 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=14 device type=DISK
current log archived

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     263049216 bytes

Fixed Size                     2287280 bytes
Variable Size                125831504 bytes
Database Buffers             130023424 bytes
Redo Buffers                   4907008 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORAWISS'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'orawiss' primary controlfile;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     263049216 bytes

Fixed Size                     2287280 bytes
Variable Size                125831504 bytes
Database Buffers             130023424 bytes
Redo Buffers                   4907008 bytes

Starting restore at 07/03/2013 08:56:02
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=172 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=13 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=173 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=14 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orawiss
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/wissem/datafile/test/control01.dbf
Finished restore at 07/03/2013 08:56:05

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/home/oracle/wissem/datafile/test/system.258.819111197";
   set newname for datafile  3 to
 "/home/oracle/wissem/datafile/test/sysaux.257.819111131";
   set newname for datafile  4 to
 "/home/oracle/wissem/datafile/test/undotbs1.260.819111263";
   set newname for datafile  5 to
 "/home/oracle/wissem/datafile/test3/system.271.819111307";
   set newname for datafile  6 to
 "/home/oracle/wissem/datafile/test/users.259.819111261";
   set newname for datafile  7 to
 "/home/oracle/wissem/datafile/test3/sysaux.270.819111307";
   set newname for datafile  8 to
 "/home/oracle/wissem/datafile/test2/system.274.819112037";
   set newname for datafile  9 to
 "/home/oracle/wissem/datafile/test2/sysaux.275.819112037";
   set newname for datafile  10 to
 "/home/oracle/wissem/datafile/test2/users.277.819112065";
   set newname for datafile  12 to
 "/home/oracle/wissem/datafile/test2/tbs_tst.290.819120929";
   set newname for datafile  13 to
 "/home/oracle/wissem/datafile/test2/tbs_rec.287.819121381";
   set newname for datafile  14 to
 "/home/oracle/wissem/datafile/test2/test_rest_df.289.819610465";
   restore
   from service  'orawiss'   clone database
   ;
   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

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 07/03/2013 08:56:10
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: using network backup set from service orawiss
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/wissem/datafile/test/system.258.819111197
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using network backup set from service orawiss
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to /home/oracle/wissem/datafile/test/sysaux.257.819111131
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using network backup set from service orawiss
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00004 to /home/oracle/wissem/datafile/test/undotbs1.260.819111263
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using network backup set from service orawiss
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00005 to /home/oracle/wissem/datafile/test3/system.271.819111307
channel ORA_AUX_DISK_5: starting datafile backup set restore
channel ORA_AUX_DISK_5: using network backup set from service orawiss
channel ORA_AUX_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_5: restoring datafile 00006 to /home/oracle/wissem/datafile/test/users.259.819111261
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_5: starting datafile backup set restore
channel ORA_AUX_DISK_5: using network backup set from service orawiss
channel ORA_AUX_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_5: restoring datafile 00007 to /home/oracle/wissem/datafile/test3/sysaux.270.819111307
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using network backup set from service orawiss
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00008 to /home/oracle/wissem/datafile/test2/system.274.819112037
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:12
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orawiss
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /home/oracle/wissem/datafile/test2/sysaux.275.819112037
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using network backup set from service orawiss
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00010 to /home/oracle/wissem/datafile/test2/users.277.819112065
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_5: starting datafile backup set restore
channel ORA_AUX_DISK_5: using network backup set from service orawiss
channel ORA_AUX_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_5: restoring datafile 00012 to /home/oracle/wissem/datafile/test2/tbs_tst.290.819120929
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_5: starting datafile backup set restore
channel ORA_AUX_DISK_5: using network backup set from service orawiss
channel ORA_AUX_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_5: restoring datafile 00013 to /home/oracle/wissem/datafile/test2/tbs_rec.287.819121381
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orawiss
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to /home/oracle/wissem/datafile/test2/test_rest_df.289.819610465
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:05
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:24
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:13
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:30
Finished restore at 07/03/2013 08:56:41

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'orawiss'
           archivelog from scn  2473141;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 07/03/2013 08:56:42
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 archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orawiss
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=94
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: using network backup set from service orawiss
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=95
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
Finished restore at 07/03/2013 08:56:44

datafile 1 switched to datafile copy
input datafile copy RECID=21 STAMP=819795404 file name=/home/oracle/wissem/datafile/test/system.258.819111197
datafile 3 switched to datafile copy
input datafile copy RECID=22 STAMP=819795404 file name=/home/oracle/wissem/datafile/test/sysaux.257.819111131
datafile 4 switched to datafile copy
input datafile copy RECID=23 STAMP=819795404 file name=/home/oracle/wissem/datafile/test/undotbs1.260.819111263
datafile 5 switched to datafile copy
input datafile copy RECID=24 STAMP=819795404 file name=/home/oracle/wissem/datafile/test3/system.271.819111307
datafile 6 switched to datafile copy
input datafile copy RECID=25 STAMP=819795404 file name=/home/oracle/wissem/datafile/test/users.259.819111261
datafile 7 switched to datafile copy
input datafile copy RECID=26 STAMP=819795404 file name=/home/oracle/wissem/datafile/test3/sysaux.270.819111307
datafile 8 switched to datafile copy
input datafile copy RECID=27 STAMP=819795404 file name=/home/oracle/wissem/datafile/test2/system.274.819112037
datafile 9 switched to datafile copy
input datafile copy RECID=28 STAMP=819795404 file name=/home/oracle/wissem/datafile/test2/sysaux.275.819112037
datafile 10 switched to datafile copy
input datafile copy RECID=29 STAMP=819795405 file name=/home/oracle/wissem/datafile/test2/users.277.819112065
datafile 12 switched to datafile copy
input datafile copy RECID=30 STAMP=819795405 file name=/home/oracle/wissem/datafile/test2/tbs_tst.290.819120929
datafile 13 switched to datafile copy
input datafile copy RECID=31 STAMP=819795405 file name=/home/oracle/wissem/datafile/test2/tbs_rec.287.819121381
datafile 14 switched to datafile copy
input datafile copy RECID=32 STAMP=819795405 file name=/home/oracle/wissem/datafile/test2/test_rest_df.289.819610465

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

executing command: SET until clause

Starting recover at 07/03/2013 08:56:45
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 94 is already on disk as file /opt/app/oracle/product/12.1/db_1/dbs/arch1_94_819111292.dbf
archived log for thread 1 with sequence 95 is already on disk as file /opt/app/oracle/product/12.1/db_1/dbs/arch1_95_819111292.dbf
archived log file name=/opt/app/oracle/product/12.1/db_1/dbs/arch1_94_819111292.dbf thread=1 sequence=94
archived log file name=/opt/app/oracle/product/12.1/db_1/dbs/arch1_95_819111292.dbf thread=1 sequence=95
media recovery complete, elapsed time: 00:00:05
Finished recover at 07/03/2013 08:56:54
Oracle instance started

Total System Global Area     263049216 bytes

Fixed Size                     2287280 bytes
Variable Size                125831504 bytes
Database Buffers             130023424 bytes
Redo Buffers                   4907008 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST'' 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 =  ''TEST'' 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     263049216 bytes

Fixed Size                     2287280 bytes
Variable Size                125831504 bytes
Database Buffers             130023424 bytes
Redo Buffers                   4907008 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+DATA', '+DATA' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+DATA', '+DATA' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+DATA', '+DATA' ) SIZE 50 M  REUSE
 DATAFILE
  '/home/oracle/wissem/datafile/test/system.258.819111197',
  '/home/oracle/wissem/datafile/test3/system.271.819111307',
  '/home/oracle/wissem/datafile/test2/system.274.819112037'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/home/oracle/wissem/tempfile/temp.269.819111301";
   set newname for tempfile  2 to
 "/home/oracle/wissem/datafile/test3/pdbseed_temp01.dbf";
   set newname for tempfile  3 to
 "/home/oracle/wissem/tempfile/temp.276.819112043";
   switch clone tempfile all;
   catalog clone datafilecopy  "/home/oracle/wissem/datafile/test/sysaux.257.819111131",
 "/home/oracle/wissem/datafile/test/undotbs1.260.819111263",
 "/home/oracle/wissem/datafile/test/users.259.819111261",
 "/home/oracle/wissem/datafile/test3/sysaux.270.819111307",
 "/home/oracle/wissem/datafile/test2/sysaux.275.819112037",
 "/home/oracle/wissem/datafile/test2/users.277.819112065",
 "/home/oracle/wissem/datafile/test2/tbs_tst.290.819120929",
 "/home/oracle/wissem/datafile/test2/tbs_rec.287.819121381",
 "/home/oracle/wissem/datafile/test2/test_rest_df.289.819610465";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/wissem/tempfile/temp.269.819111301 in control file
renamed tempfile 2 to /home/oracle/wissem/datafile/test3/pdbseed_temp01.dbf in control file
renamed tempfile 3 to /home/oracle/wissem/tempfile/temp.276.819112043 in control file

cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test/sysaux.257.819111131 RECID=1 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test/undotbs1.260.819111263 RECID=2 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test/users.259.819111261 RECID=3 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test3/sysaux.270.819111307 RECID=4 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test2/sysaux.275.819112037 RECID=5 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test2/users.277.819112065 RECID=6 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test2/tbs_tst.290.819120929 RECID=7 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test2/tbs_rec.287.819121381 RECID=8 STAMP=819795432
cataloged datafile copy
datafile copy file name=/home/oracle/wissem/datafile/test2/test_rest_df.289.819610465 RECID=9 STAMP=819795432

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=819795432 file name=/home/oracle/wissem/datafile/test/sysaux.257.819111131
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=819795432 file name=/home/oracle/wissem/datafile/test/undotbs1.260.819111263
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=819795432 file name=/home/oracle/wissem/datafile/test/users.259.819111261
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=819795432 file name=/home/oracle/wissem/datafile/test3/sysaux.270.819111307
datafile 9 switched to datafile copy
input datafile copy RECID=5 STAMP=819795432 file name=/home/oracle/wissem/datafile/test2/sysaux.275.819112037
datafile 10 switched to datafile copy
input datafile copy RECID=6 STAMP=819795432 file name=/home/oracle/wissem/datafile/test2/users.277.819112065
datafile 12 switched to datafile copy
input datafile copy RECID=7 STAMP=819795432 file name=/home/oracle/wissem/datafile/test2/tbs_tst.290.819120929
datafile 13 switched to datafile copy
input datafile copy RECID=8 STAMP=819795432 file name=/home/oracle/wissem/datafile/test2/tbs_rec.287.819121381
datafile 14 switched to datafile copy
input datafile copy RECID=9 STAMP=819795432 file name=/home/oracle/wissem/datafile/test2/test_rest_df.289.819610465
Leaving database unopened, as requested
Cannot remove created server parameter file
Finished Duplicate Db at 07/03/2013 08:57:10

RMAN> exit


Recovery Manager complete.

Note from Above output; “Leaving database unopened, as requested” this is because I added NOOPEN option during duplicate the target database.
Let’s verify the auxilairy database “test” is not opened and it is in mount state.
Note, I am querying the view v$pdbs to check the pluggable databases in the new test database.

sandbox1(test):/home/oracle/wissem>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 09:00:59 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061740350 PDB$SEED                       MOUNTED
         3 1574282659 _###_UNKNOWN_PDB_#_3           MOUNTED

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
sandbox1(test):/home/oracle/wissem>

If desired, We can open the new auxiliary database with “open reset logs option”

sandbox1(test):/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 06:31:48 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>

Cheers,
Wissem

References ; Doc 12cR1



One Comment to “Oracle 12c: NOOPEN option for RMAN duplicate”


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • Sergio: Hi, Where is declared the function agent_clean() ? I dont see agent_clean() definition !! regards, Sergio
  • Sikandar: I do salute you brother, This is very………..nti mes helpful and correct documents. I...
  • Karthik Singh: Thanks for sharing a useful article. Looks like you have installed weblogic 10.3.4. Then shall I...
  • Joseph: Paul, Here target is from which database we are taking cloning. and auxilary is the cloning database. Wissem,...
  • sundar: In Doc ID 1472171.1 it says that the snapshot control file should be in a shared location for RAC databases....
  • Taoqir: Hello, This is a wonderfull and very helping material.now I am going to configure Data Guard. Thanks
  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!
  • Nav: Indeed helpful, to the point article..
  • Koti: Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql...
  • Himanshu: Nice post.can you please comment on this Why RMAN restored the DATA_REORTING datafie to +DATA diskgroup?
  • gabriel: what version of sqldeveloper support plugable database?
  • Kal: Quality – thanks…
  • wissem: Well , like I mentioned in the post, delete service and add service using IP.
  • Kelly: were you able to solve this? Thanks!
  • wissem: Hello, You can buy the book from PakT: http://www.packtpub.com/oracle -data-guard-11gr2-administr...

Oracle-Class Forums Latest Activity