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.