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.
