By Franck Pachot

.
When you clone a PDB on the primary CDB, you want that the same is automatically done on the standby ones. Unfortunately, for this operation, the CDB must be opened read-only. In 12.1 even the PDB needed to be opened read-only (Ludovico Caldara reported this in his blog). This, as far as I know, was fixed in 12.2 where MRP reads the files without the need to have the PDB opened. But another problem comes with online cloning, as reported by Alain Fuhrer in his blog, where the CDB needs to be opened read-only with real-time apply. This again requires the Active Data Guard option, which is then mandatory to use all power from the multitenant option. This brings the use of multitenant from x1.36 the price for this option to x1.61 for both, on the primary and the standby servers. Hopefully, this has been addressed in 18c where you don’t need the Active Data Guard to have the CDB opened in read-only with real-time apply, as long as you ensure that the user PDB are always closed when apply is on.

This test was done on the Oracle Cloud DBaaS because it is very easy to provision a Data Guard configuration. It is 18.1 because this is what is created and the patch apply for 18.2 is not so easy. But behaviour the same in 18.2

CDB in MOUNT do not requires ADG

The standby is not opened:


SQL> select dbid,name,open_mode,database_role from v$database;
 
      DBID NAME      OPEN_MODE            DATABASE_ROLE
---------- --------- -------------------- ----------------
1507386248 ORCL      MOUNTED              PHYSICAL STANDBY
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         4 PDB1                           MOUNTED

In the primary, we see that the standby destination is in real time apply, but not opened:


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status;
 
   DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         MOUNTED-STANDBY MANAGED REAL TIME APPLY
         3 LOG_ARCHIVE_DEST_3   INACTIVE  LOCAL            UNKNOWN         IDLE
         4 LOG_ARCHIVE_DEST_4   INACTIVE  LOCAL            UNKNOWN         IDLE

This is the information that is logged into DBA_FEATURE_USAGE_STATISTICS. Here is the query that counts the usage of real-time query which is one of the features that require ADG option:


SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%QUERY' and database_mode = 'OPEN_READ-ONLY';
 
  COUNT(*)
----------
         0

Here we have no ADG usage recorded because the database is not opened read-only. But if you try some PDB cloning on the primary you will quickly encounter the problems: the PDB on the standby has no datafiles.

CDB in READ and PDB closed do not require ADG in 18c

I’ll now open the standby read-only:


SQL> alter database open;
Database altered.
 
SQL> show pdbs;
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB1                           READ ONLY  NO

Unfortunately, this has also opened the PDB because the saved state was OPEN for the primary database and we cannot manage that properly on the standby (except with Grid Infrastructure or Oracle Restart or custom scripts or database triggers). I close it quickly before the ADG usage is recorded:


SQL> alter pluggable database pdb1 close;
Pluggable database altered.

So, my standby is opened read-only with real-time apply:


SQL> select dbid,name,open_mode,database_role from v$database;
 
      DBID NAME      OPEN_MODE            DATABASE_ROLE
---------- --------- -------------------- ----------------
1507386248 ORCL      READ ONLY WITH APPLY PHYSICAL STANDBY

but all the user PDBs are not opened:


SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB1                           MOUNTED

Here is what we see from the primary about this archive destination:


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status;
 
   DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         OPEN_READ-ONLY  MANAGED REAL TIME APPLY
         3 LOG_ARCHIVE_DEST_3   INACTIVE  LOCAL            UNKNOWN         IDLE
         4 LOG_ARCHIVE_DEST_4   INACTIVE  LOCAL            UNKNOWN         IDLE

This in 12.2 would have recorded the usage of real-time query usage because the standby is opened for queries and we are in managed recovery mode. Here was the 12.2 query to detect it (you can see it in catfusrg.sql):


SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%' and database_mode = 'OPEN_READ-ONLY';
 
  COUNT(*)
----------
         1

But the pattern has been changed in 18c to include ‘QUERY’:


SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%QUERY' and database_mode = 'OPEN_READ-ONLY';
 
  COUNT(*)
----------
         0

and this ‘QUERY’ word is added only when some user pluggable databases are not in MOUNT but opened for queries.

I check quickly that a PDB clone on the primary:


SQL> create pluggable database PDB2 from PDB1 keystore identified by "Ach1z0#d";
Pluggable database created.
 
SQL> alter pluggable database PDB2 open;
Pluggable database altered.

has the clone created on the standby, in closed mode:


SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB1                           MOUNTED
         5 PDB2                           MOUNTED

So, finally, we have a good way to use multitenant features without the need for Active Data Guard. This is good for DBaaS where we want easy cloning, protect their availability with Data Guard, but with no need to query them on the standby.

CDB in READ and PDB opened requires ADG in 18c

Now, if I open the PDB:


SQL> select dbid,name,open_mode,database_role from v$database;
 
      DBID NAME      OPEN_MODE            DATABASE_ROLE
---------- --------- -------------------- ----------------
1507386248 ORCL      READ ONLY WITH APPLY PHYSICAL STANDBY
 
SQL> alter pluggable database pdb1 open;
 
Pluggable database PDB1 altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB1                           READ ONLY  NO

From the primary, the recovery mode for this standby mentions ‘WITH QUERY’ for the managed real time apply:


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status;
 
   DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         OPEN_READ-ONLY  MANAGED REAL TIME APPLY WITH QUERY
         3 LOG_ARCHIVE_DEST_3   INACTIVE  LOCAL            UNKNOWN         IDLE
         4 LOG_ARCHIVE_DEST_4   INACTIVE  LOCAL            UNKNOWN         IDLE

And only in this case the ADG usage is recorded:


SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%QUERY' and database_mode = 'OPEN_READ-ONLY';
 
  COUNT(*)
----------
         1

The proper way to open without activating ADG

In the previous test, I had the PDB opened for a short time when I opened the CDB because of the saved state. If you want to avoid this, the proper way is to ensure that the apply is off when you open it so that you do not have, at the same time, a PDB opened and real time apply.

I’m back to the state where the standby is in mount and apply on:


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
   DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         MOUNTED-STANDBY MANAGED REAL TIME APPLY

I stop the apply in the standby:


DGMGRL> edit database "ORCL_02" set state=apply-off;
Succeeded.

I check that the primary sees that apply is off:


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
   DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         MOUNTED-STANDBY IDLE

I open the standby CDB read-only and ensures that all PDB are closed:


SQL> alter database open;
Database altered.
 
SQL> alter pluggable database all close;
Pluggable database altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB1                           MOUNTED

The primary sees the open state:


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
   DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         OPEN_READ-ONLY  IDLE

I can now restart the apply:


DGMGRL> edit database "ORCL_02" set state=apply-on;
Succeeded.

Then the primary detects the managed recovery


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
   DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         OPEN_READ-ONLY  MANAGED

Then once the gap is resolved, we are in real time apply:


SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
    DEST_ID DEST_NAME            STATUS    TYPE             DATABASE_MODE   RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
         1 LOG_ARCHIVE_DEST_1   VALID     LOCAL            OPEN            IDLE
         2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL         OPEN_READ-ONLY  MANAGED REAL TIME APPLY

But there is no mention of ‘WITH QUERY’ thanks to the closed PDBs, which means that no ADG usage is recorded. In summary, 18c has added two possible values for RECOVERY_MODE: ‘MANAGED WITH QUERY’ and ‘MANAGED REAL TIME APPLY WITH QUERY’, and those ones, in multitenant, are for standby CDB opened read-only when at least one user PDB is also opened read-only.

For DBaaS, you probably run with Grid Infrastructure (because Oracle Restart do not allow databases on ACFS). For single-tenant, this new feature can solve the problem of activating ADG option by mistake, at least if we have good control of PDB states depending on the role.