oraQBA: duplicate

Showing posts with label duplicate. Show all posts

Saturday, 19 January 2019

Oracle 18c refreshable PDB switchover and why could be not useful

One of a new feature of 18c is refreshable pdb switchover which is documented under:
https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-pdbs-with-sql-plus.html#GUID-B505C234-FAF4-4BAB-8B59-59276E0EA128

I performed a couple of tests and want to present the biggest disadvantage of this feature: during switchover, the source pdb database is fully recreated.
This can be unacceptable for large databases as the switchover process will take a long time depending on you IO and network bandwidth. For sure this unpleasant side effect can limit use cases.
To show you this unexpected behaviour I will use the simple trick with changing db_create_file_dest parameter short before the switchover. But this later on…
I'm on Exadata SERVER X7-2L with Image version 18.1.4.0.0.180125.3. This feature cannot be used on not-engineered on-premise systems(but for testing why not:P). More on that here:
https://docs.oracle.com/en/database/oracle/oracle-database/18/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

DB software used for the test is the following:
CDBDE:oracle@exalab0102../trace# $ORACLE_HOME/OPatch/opatch lspatches
27908644;UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171
27923415;OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
28090553;OCW RELEASE UPDATE 18.3.0.0.0 (28090553)
28090523;Database Release Update : 18.3.0.0.180717 (28090523)

Let's start with creating CDB(CDBPL) and source pdb (WARSAW). Later on, I will create empty CDB(CDBDE) inside which we would create a refreshable pdb(BERLIN).

Syntax for CDB(CDBPL) creation:

dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname CDBPL -sid CDBPL -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword OraPasswd1 \
-systemPassword OraPasswd1 \
-createAsContainerDatabase true \
-numberOfPDBs 0 \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 8000 \
-storageType ASM \
-datafileDestination "+DATA01" \
-redoLogFileSize 256 \
-initParams encrypt_new_tablespaces=DDL \
-emConfiguration NONE \
-ignorePreReqs

Creating empty CDB(CDBDE) looks like that:

dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname CDBDE -sid CDBDE -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword OraPasswd1 \
-systemPassword OraPasswd1 \
-createAsContainerDatabase true \
-numberOfPDBs 0 \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 8000 \
-storageType ASM \
-datafileDestination "+DATA01" \
-redoLogFileSize 256 \
-initParams encrypt_new_tablespaces=DDL \
-emConfiguration NONE \
-ignorePreReqs

The CDBPL is running on exalab0101 node and CDBDE is on exalab0102 but this is not relevant for our test. Both CDBs has to be in archive log mode(and force logging is recommended).

On CDBPL and CDBDE we are creating a common user with sysoper privilege:

SQL> grant create session, sysoper to C##SYSOPER identified by OraPasswd1 container=all;
Grant succeeded.

On CDBPL I've created link pointing to CDBDE. And on CDBDE of course in opposite direction:
On CDBPL:

SQL> create database link CDBDE@SYSOPER connect to C##SYSOPER identified by OraPasswd1 using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=exalab0102.lab.com)(PORT=1700))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=CDBDE)))';
Database link created.

SQL> select * from dual@CDBDE@SYSOPER;
D
-
X

On CDBDE:

SQL> create database link CDBPL@SYSOPER connect to C##SYSOPER identified by OraPasswd1 using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=exalab0101.lab.com)(PORT=1700))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=CDBPL)))';
Database link created.

Let's create pdb called WARSAW on CDBPL.

SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA01

SQL> CREATE PLUGGABLE DATABASE WARSAW ADMIN USER ADMIN IDENTIFIED BY OraPasswd1;
Pluggable database created.

SQL> alter pluggable database WARSAW open;
Pluggable database altered.

SQL> col name for a20
SQL> select CON_ID,DBID,NAME,OPEN_MODE from V$PDBS;
CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ----------
2 157523876 PDB$SEED READ ONLY
4 2738351935 WARSAW READ WRITE

RMAN> report schema;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 840 SYSTEM YES +DATA01/CDBPL/DATAFILE/system.549.997800933
3 540 SYSAUX NO +DATA01/CDBPL/DATAFILE/sysaux.565.997800963
4 325 UNDOTBS1 YES +DATA01/CDBPL/DATAFILE/undotbs1.603.997800979
5 270 PDB$SEED:SYSTEM NO +DATA01/CDBPL/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.584.997801165
6 370 PDB$SEED:SYSAUX NO +DATA01/CDBPL/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.550.997801165
7 5 USERS NO +DATA01/CDBPL/DATAFILE/users.602.997800979
8 100 PDB$SEED:UNDOTBS1 NO +DATA01/CDBPL/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.575.997801165
46 270 WARSAW:SYSTEM YES +DATA01/CDBPL/7FAC77C7FFBE6352E0533023C00A5F0A/DATAFILE/system.551.997816909
47 370 WARSAW:SYSAUX NO +DATA01/CDBPL/7FAC77C7FFBE6352E0533023C00A5F0A/DATAFILE/sysaux.557.997816909
48 100 WARSAW:UNDOTBS1 YES +DATA01/CDBPL/7FAC77C7FFBE6352E0533023C00A5F0A/DATAFILE/undotbs1.278.997816909

Please note that all data files for WARSAW pdb are in the DATA01 disk group.
For WARSAW pdb, a FOREIGN_CDB_DBID and CDB_DBID are the same(nice sql from Franck Pachot(https://blog.dbi-services.com/18c-pdb-switchover/)).

SQL> set lines 300
SQL> col CON_NAME for a10
SQL> select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn
from v$pdbs right join dba_pdbs
using(con_id,creation_time,dbid)
cross join (select dbid cdb_dbid,current_scn from v$database)
order by pdb_id;
CON_ID CON_NAME OPEN_MODE RES STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION REFRES REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
---------- ---------- ---------- --- ---------- ---------------- -------------- -------- ------ ---------------- ---------------- ---------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1201448 2 14:59:42 NONE 4176728910 2297307
4 WARSAW READ WRITE NO NORMAL 4176728910 2 19:21:49 NONE 4176728910 2297307

Let’s move to CDBDE and create a refreshable pdb BERLIN:

SQL> CREATE PLUGGABLE DATABASE BERLIN FROM WARSAW@CDBPL@SYSOPER REFRESH MODE EVERY 1 MINUTES;
Pluggable database created.

SQL> COLUMN pdb_name FORMAT A30
SQL> SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM dba_pdbs ORDER BY 1;
PDB_ID PDB_NAME REFRES REFRESH_INTERVAL
---------- ------------------------------ ------ ----------------
2 PDB$SEED NONE
4 BERLIN AUTO 1
SQL> col name for a20
SQL> select CON_ID,DBID,NAME,OPEN_MODE from V$PDBS;
CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ----------
2 4176096590 PDB$SEED READ ONLY
4 3315658107 BERLIN MOUNTED
SQL> set lines 300
SQL> col CON_NAME for a10
SQL> select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn
from v$pdbs right join dba_pdbs
using(con_id,creation_time,dbid)
cross join (select dbid cdb_dbid,current_scn from v$database)
order by pdb_id;

CON_ID CON_NAME OPEN_MODE RES STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION REFRES REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
---------- ---------- ---------- --- ---------- ---------------- -------------- -------- ------ ---------------- ---------------- ---------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1201448 2 15:02:54 NONE 715392205 2297411
4 BERLIN MOUNTED REFRESHING 4176728910 4 19:23:10 AUTO 1 2297345 715392205 2297411

Note that FOREIGN_CDB_DBID for BERLIN pdb is actually the DBID of CDBPL.
RMAN> report schema;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 840 SYSTEM YES +DATA01/CDBDE/DATAFILE/system.589.997801121
3 540 SYSAUX NO +DATA01/CDBDE/DATAFILE/sysaux.547.997801153
4 330 UNDOTBS1 YES +DATA01/CDBDE/DATAFILE/undotbs1.574.997801167
5 270 PDB$SEED:SYSTEM NO +DATA01/CDBDE/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.548.997801357
6 370 PDB$SEED:SYSAUX NO +DATA01/CDBDE/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.588.997801357
7 5 USERS NO +DATA01/CDBDE/DATAFILE/users.604.997801169
8 100 PDB$SEED:UNDOTBS1 NO +DATA01/CDBDE/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.546.997801357
50 270 BERLIN:SYSTEM NO +DATA01/CDBDE/7FAC7CA03BF16663E0533123C00A9EA6/DATAFILE/system.552.997816991
51 370 BERLIN:SYSAUX NO +DATA01/CDBDE/7FAC7CA03BF16663E0533123C00A9EA6/DATAFILE/sysaux.555.997816991
52 100 BERLIN:UNDOTBS1 NO +DATA01/CDBDE/7FAC7CA03BF16663E0533123C00A9EA6/DATAFILE/undotbs1.558.997816991

Now to check if the refresh process is working, I'm creating some dummy table:
On CDBPL in WARSAW pdb:

SQL> alter session set container=WARSAW;
Session altered.

SQL> create table test1 as select * from dba_objects;
Table created.

SQL> insert /*+append parallel 32*/ into test1 select * from test1;
72806 rows created.

SQL> commit;
SQL> select count(*) from test1;
COUNT(*)
----------
145612

In BERLIN pdb:

SQL> alter session set container=BERLIN;
Session altered.

SQL> alter pluggable database refresh;
Pluggable database altered.

SQL> alter pluggable database open read only;
Pluggable database altered.

SQL> select count(*) from test1;
COUNT(*)
----------
145612

Till now everything works as expected.
Let's do a switchover from WARSAW to BERLIN but before I will change the db_create_file_dest parameter to FLASH01 on CDBPL.
On CDBPL

SQL> alter system set db_create_file_dest='+FLASH01';
System altered.
SQL> alter pluggable database WARSAW refresh mode every 1 minutes from BERLIN@CDBDE@SYSOPER switchover;
Pluggable database altered.

In alert.log of CDBPL we see lines like "Deleted Oracle managed file". So instead of reusing data files, they are simply dropped from ASM and recreated(and of course it doesn’t matter that we change db_create_file_dest parameter or not. I did it to make more visible).

2019-01-17T19:29:26.146717+01:00
ALTER SYSTEM SET db_create_file_dest='+FLASH01' SCOPE=BOTH;
2019-01-17T19:29:45.669440+01:00
alter pluggable database WARSAW refresh mode every 1 minutes from BERLIN@CDBDE@SYSOPER switchover
2019-01-17T19:29:46.135723+01:00
WARSAW(4):JIT: pid 131190 requesting stop
WARSAW(4):Buffer Cache flush started: 4
WARSAW(4):Buffer Cache flush finished: 4
2019-01-17T19:29:46.587641+01:00
WARSAW(4):While transitioning the pdb 4 to clean state, clearing all its abort bits in the control file.
2019-01-17T19:29:46.697145+01:00
Pluggable database WARSAW closed
2019-01-17T19:29:49.720412+01:00
Deleted Oracle managed file +DATA01/CDBPL/7FAC77C7FFBE6352E0533023C00A5F0A/TEMPFILE/temp.560.997816911
Deleted Oracle managed file +DATA01/CDBPL/7FAC77C7FFBE6352E0533023C00A5F0A/DATAFILE/undotbs1.278.997816909
Deleted Oracle managed file +DATA01/CDBPL/7FAC77C7FFBE6352E0533023C00A5F0A/DATAFILE/sysaux.557.997816909
Deleted Oracle managed file +DATA01/CDBPL/7FAC77C7FFBE6352E0533023C00A5F0A/DATAFILE/system.551.997816909
2019-01-17T19:29:53.930715+01:00
NOTE: dependency between database CDBPL and diskgroup resource ora.FLASH01.dg is established
2019-01-17T19:30:00.851532+01:00
WARSAW(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database WARSAW with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000103
****************************************************************
2019-01-17T19:30:03.216005+01:00
Applying media recovery for pdb-4099 from SCN 2298956 to SCN 2298978
Remote log information: count-1
thr-1, seq-61, logfile-/u01/app/oracle/product/MGMDB/18.3.0.0/dbhome_1/dbs/CDBDE/foreign_archivelog/BERLIN/2019_01_17/o1_mf_1_61_3486226340_.arc, los-2295160, nxs-18446744073709551615
WARSAW(4):Media Recovery Start
2019-01-17T19:30:03.219160+01:00
WARSAW(4):Serial Media Recovery started
WARSAW(4):max_pdb is 4
2019-01-17T19:30:03.342914+01:00
WARSAW(4):Media Recovery Log /u01/app/oracle/product/MGMDB/18.3.0.0/dbhome_1/dbs/CDBDE/foreign_archivelog/BERLIN/2019_01_17/o1_mf_1_61_3486226340_.arc
2019-01-17T19:30:03.698700+01:00
WARSAW(4):Incomplete Recovery applied until change 2298978 time 01/17/2019 19:30:01
2019-01-17T19:30:03.700039+01:00
WARSAW(4):Media Recovery Complete (CDBPL)
2019-01-17T19:30:03.784021+01:00
ALTER SYSTEM SET remote_listener=' exalab01-sc:1700' SCOPE=MEMORY SID='CDBPL';
2019-01-17T19:30:03.784663+01:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='CDBPL';
Completed: alter pluggable database WARSAW refresh mode every 1 minutes from BERLIN@CDBDE@SYSOPER switchover
2019-01-17T19:30:03.814417+01:00
WARSAW(4):alter pluggable database refresh
2019-01-17T19:30:06.161335+01:00
Applying media recovery for pdb-4099 from SCN 2298978 to SCN 2298984
Remote log information: count-1
thr-1, seq-61, logfile-/u01/app/oracle/product/MGMDB/18.3.0.0/dbhome_1/dbs/CDBDE/foreign_archivelog/BERLIN/2019_01_17/o1_mf_1_61_3486226340_.arc, los-2295160, nxs-18446744073709551615
WARSAW(4):Media Recovery Start
2019-01-17T19:30:06.164177+01:00
WARSAW(4):Serial Media Recovery started
WARSAW(4):max_pdb is 4
2019-01-17T19:30:06.306070+01:00
WARSAW(4):Media Recovery Log /u01/app/oracle/product/MGMDB/18.3.0.0/dbhome_1/dbs/CDBDE/foreign_archivelog/BERLIN/2019_01_17/o1_mf_1_61_3486226340_.arc
2019-01-17T19:30:06.501265+01:00
WARSAW(4):Incomplete Recovery applied until change 2298984 time 01/17/2019 19:30:03
2019-01-17T19:30:06.503458+01:00
WARSAW(4):Media Recovery Complete (CDBPL)
WARSAW(4):Completed: alter pluggable database refresh
2019-01-17T19:30:06.745359+01:00
ALTER SYSTEM SET remote_listener=' exalab01-sc:1700' SCOPE=MEMORY SID='CDBPL';
2019-01-17T19:30:06.745936+01:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='CDBPL';
2019-01-17T19:30:14.583371+01:00
WARSAW(4):alter pluggable database WARSAW refresh
2019-01-17T19:30:15.171952+01:00
Applying media recovery for pdb-4099 from SCN 2298984 to SCN 2298992
Remote log information: count-1
thr-1, seq-61, logfile-/u01/app/oracle/product/MGMDB/18.3.0.0/dbhome_1/dbs/CDBDE/foreign_archivelog/BERLIN/2019_01_17/o1_mf_1_61_3486226340_.arc, los-2295160, nxs-18446744073709551615
WARSAW(4):Media Recovery Start
2019-01-17T19:30:15.174624+01:00
WARSAW(4):Serial Media Recovery started
WARSAW(4):max_pdb is 4
2019-01-17T19:30:15.286968+01:00
WARSAW(4):Media Recovery Log /u01/app/oracle/product/MGMDB/18.3.0.0/dbhome_1/dbs/CDBDE/foreign_archivelog/BERLIN/2019_01_17/o1_mf_1_61_3486226340_.arc
2019-01-17T19:30:15.484485+01:00
WARSAW(4):Incomplete Recovery applied until change 2298992 time 01/17/2019 19:30:14
2019-01-17T19:30:15.485948+01:00
WARSAW(4):Media Recovery Complete (CDBPL)
WARSAW(4):Completed: alter pluggable database WARSAW refresh
2019-01-17T19:30:15.727213+01:00
ALTER SYSTEM SET remote_listener=' exalab01-sc:1700' SCOPE=MEMORY SID='CDBPL';
2019-01-17T19:30:15.727828+01:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='CDBPL';
2019-01-17T19:30:19.247009+01:00
WARSAW(4):alter pluggable database open read only
WARSAW(4):Autotune of undo retention is turned on.
2019-01-17T19:30:19.642982+01:00
WARSAW(4):Undo initialization finished serial:0 start:450110281 end:450110281 diff:0 ms (0.0 seconds)
WARSAW(4):Database Characterset for WARSAW is AL32UTF8
2019-01-17T19:30:20.585352+01:00
WARSAW(4):Opening pdb with no Resource Manager plan active
2019-01-17T19:30:20.678809+01:00
Pluggable database WARSAW opened read only
WARSAW(4):Completed: alter pluggable database open read only
2019-01-17T19:31:04.010025+01:00
WARSAW(4):alter pluggable database refresh
WARSAW(4):Completed: alter pluggable database refresh

Let's have a look at data files. They are recreated in FLASH01 disk group with new GUID(7FAC7CA03BF16663E0533123C00A9EA6) which belongs to BERLIN pdb!

On CDBPL:
RMAN> report schema;

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 840 SYSTEM YES +DATA01/CDBPL/DATAFILE/system.549.997800933
3 540 SYSAUX NO +DATA01/CDBPL/DATAFILE/sysaux.565.997800963
4 325 UNDOTBS1 YES +DATA01/CDBPL/DATAFILE/undotbs1.603.997800979
5 270 PDB$SEED:SYSTEM NO +DATA01/CDBPL/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.584.997801165
6 370 PDB$SEED:SYSAUX NO +DATA01/CDBPL/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.550.997801165
7 5 USERS NO +DATA01/CDBPL/DATAFILE/users.602.997800979
8 100 PDB$SEED:UNDOTBS1 NO +DATA01/CDBPL/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.575.997801165
49 290 WARSAW:SYSTEM YES +FLASH01/CDBPL/7FAC7CA03BF16663E0533123C00A9EA6/DATAFILE/system.1374.997817393
50 370 WARSAW:SYSAUX NO +FLASH01/CDBPL/7FAC7CA03BF16663E0533123C00A9EA6/DATAFILE/sysaux.1363.997817393
51 100 WARSAW:UNDOTBS1 YES +FLASH01/CDBPL/7FAC7CA03BF16663E0533123C00A9EA6/DATAFILE/undotbs1.1367.997817393

On CDBDE:
SQL> select DBID,CON_UID,GUID,NAME from v$pdbs;

DBID CON_UID GUID NAME
---------- ---------- -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
4176096590 4176096590 7FA8D9DBD20281D5E0533123C00AD409 PDB$SEED
3315658107 3315658107 7FAC7CA03BF16663E0533123C00A9EA6 BERLIN

I hope this show exactly where the problem is. The database is simply recreated but I would expect a better approach, for example, some hidden expdp/impdp Transportable Tablespaces.

Tuesday, 20 February 2018

RMAN Duplicate with NOOPEN

Duplicate with noopen clause is very useful in scenario when you perform migration(to other server) and upgrade(to new release) on new server where old Oracle Software is not installed.
In my testcase I used NFS server to save the backup from source database. It was presented to new server. Just for your info: I used it to upgrade from Oracle 11.2.0.2 version on Red Hat 6.9 to Oracle 12.1.0.2 on Red Hat 7.4.

Please check MOS for more info: Upgrade to 12c(12.1 /12.2) through RMAN Duplicate using BACKUP LOCATION with NOOPEN clause (Doc ID 2022820.1)

So let's start with preparation. On source database perform full backup:

mkdir -p /u01/orabackup/backup/RMAN/testdb/noopen

backup as compressed backupset database format '/u01/orabackup/backup/RMAN/testdb/noopen/db_%U' plus archivelog format '/u01/orabackup/backup/RMAN/testdb/noopen/arch_%U';
backup current controlfile format '/u01/orabackup/backup/RMAN/testdb/noopen/cf_%U';

Copy password file and initfile to new server. Change parameter cluster_database=false by duplicating RAC DB.

On target server:

export ORACLE_HOME=/u01/app/oracle/12.1.0.2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=/u01/app/oracle

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 9 15:26:59 2018

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

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

SQL > startup nomount;

Connect to this instance as auxiliary and perform duplicate with noopen:

$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jan 9 15:18:10 2018

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

connected to auxiliary database: TESTDB (not mounted)

RMAN> DUPLICATE DATABASE to testdb noopen backup location '/u01/orabackup/backup/RMAN/testdb/noopen/';

Starting Duplicate Db at 09-JAN-18

contents of Memory Script:
{
sql clone "alter system set db_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u01/orabackup/backup/RMAN/testdb/noopen/cf_k2so88l5_1_1';
alter clone database mount;
}
executing Memory Script

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

Oracle instance shut down

Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes

Starting restore at 09-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=84 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/testdb/controlfile/control01.ctl
output file name=+FRA/testdb/controlfile/control02.ctl
Finished restore at 09-JAN-18

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=84 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=7 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=85 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=167 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=244 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=324 device type=DISK

contents of Memory Script:
{
set until scn 14331183090492;
sql clone 'alter database flashback off';
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 5 to new;
...
...
set newname for clone datafile 61 to new;
set newname for clone datafile 62 to new;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database flashback off

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 09-JAN-18
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
using channel ORA_AUX_DISK_6

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 00013 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00018 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00028 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00031 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00032 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00035 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00036 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00040 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00042 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00052 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00056 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/db_juso88fq_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 00001 to +DATA
...
...
channel ORA_AUX_DISK_1: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/db_juso88fq_1_1 tag=TAG20180109T123921
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
...
...
channel ORA_AUX_DISK_6: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/db_jsso88fq_1_1 tag=TAG20180109T123921
channel ORA_AUX_DISK_6: restored backup piece 1
channel ORA_AUX_DISK_6: restore complete, elapsed time: 00:00:55
Finished restore at 09-JAN-18

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

datafile 1 switched to datafile copy
input datafile copy RECID=63 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/system.695.964963147
datafile 2 switched to datafile copy
input datafile copy RECID=64 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147
datafile 3 switched to datafile copy
input datafile copy RECID=65 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147
datafile 4 switched to datafile copy
input datafile copy RECID=66 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/users.719.964963147
...
...
datafile 62 switched to datafile copy
input datafile copy RECID=124 STAMP=964970535 file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147

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

executing command: SET until clause

Starting recover at 09-JAN-18
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
using channel ORA_AUX_DISK_6

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=2 sequence=36
channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/arch_jvso88kj_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=32
channel ORA_AUX_DISK_2: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/arch_k0so88kj_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/arch_jvso88kj_1_1 tag=TAG20180109T124154
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/arch_k0so88kj_1_1 tag=TAG20180109T124154
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_1_seq_32.1865.964963345 thread=1 sequence=32
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_2_seq_36.2113.964963345 thread=2 sequence=36
channel clone_default: deleting archived log(s)
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_1_seq_32.1865.964963345 RECID=2 STAMP=964970544
channel clone_default: deleting archived log(s)
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_2_seq_36.2113.964963345 RECID=1 STAMP=964970544
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-JAN-18
Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes

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

sql statement: alter system set db_name = ''TESTDB'' comment= ''Reset to original value by RMAN'' scope=spfile
Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 16 SIZE 256 M ,
GROUP 17 SIZE 256 M ,
GROUP 18 SIZE 256 M ,
GROUP 19 SIZE 256 M
DATAFILE
'+DATA/TESTDBN/DATAFILE/system.695.964963147'
CHARACTER SET WE8DEC

sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i2'
GROUP 20 SIZE 256 M ,
GROUP 21 SIZE 256 M ,
GROUP 22 SIZE 256 M ,
GROUP 23 SIZE 256 M

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/TESTDBN/DATAFILE/sysaux.697.964963147",
"+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147",
"+DATA/TESTDBN/DATAFILE/users.719.964963147",
"+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147",
...
...
"+DATA/TESTDBN/DATAFILE/undotbs.715.964963147",
"+DATA/TESTDBN/DATAFILE/xdb.718.964963147";
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/TESTDBN/DATAFILE/sysaux.697.964963147 RECID=1 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147 RECID=2 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/users.719.964963147 RECID=3 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147 RECID=4 STAMP=964970583
...
...
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147 RECID=61 STAMP=964970585

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/users.719.964963147
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147
...
...
datafile 62 switched to datafile copy
input datafile copy RECID=61 STAMP=964970585 file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
Leaving database unopened, as requested
Finished Duplicate Db at 09-JAN-18

At the end we can open database in upgrade mode with resetlogs:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 9 15:26:59 2018

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

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

SQL> alter database open resetlogs upgrade;

Database altered.