19c PDB clones from Physical Standby

In previous articles we already explained how easy is to use a Refreshable PDB to re-create non production environments. We could periodically refresh it in the nonProd CDB, and use it to deploy the nonProd PDBs; thus avoiding a full database copy through the network. We could create full clones from it, create snapshot clones from it, or create a golden master from it and snapshot clones from the golden master.

But sometimes we find ourselves having a Physical Standby database already running in the nonProd server. Really tempting as we could skip the creation of the refreshable PDB. This is; we could reuse the disaster recovery solution for the nonProd refreshes.*

*Bear in mind: We can leverage Active Dataguard for refreshing nonProd environments, but we should not use a Refreshable PDB to replace an Active Dataguard. A refreshable PDB is not a DR solution; redo is only pulled from the source database when a refresh is explicitly executed, and therefore there is no possibility of a “failover” without data loss – beyond plenty of additional features that an ADG can provide.

So we are wondering about the following procedure:

The next question could be: do we need to have the standby database opened in read-only mode (active dataguard) in order to perform a PDB clone from the Physical Standby to a third CDB?. Can we keep the apply services running in the meantime?.

Lets do the first test with the following config:

Starting point:

DGMGRL> show configuration verbose
Configuration - TDEA_madrid_TDEA_fra1mp

  Protection Mode: MaxAvailability
  Members:
  TDEA_madrid - Primary database
    TDEA_fra1mp - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'TDEA_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS


DGMGRL> show database TDEA_madrid LogXptMode;
  LogXptMode = 'FastSync'

DGMGRL> show database TDEA_fra1mp LogXptMode;
  LogXptMode = 'FastSync'

[oracle@frankfurt ~]$ . TDEA.env
[oracle@frankfurt ~]$ sqlplus / as sysdba
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MOUNTED
	 4 PDBA 			  MOUNTED
  
SQL> alter database open read only;
SQL> alter pluggable database pdba open read only;
SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- 
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBA 			  READ ONLY  NO

Fot this test, the Physical Standby container (TDEA) is hosted within the same server as the pre-production target container (TDEB). We will first create a dblink from TDEB to TDEA in order to execute the different clone tests. Common user has already been provided with the proper grants on the primary db:

[oracle@frankfurt ~]$ . TDEB.env
[oracle@frankfurt ~]$ sqlplus / as sysdba

SQL> show parameters db_name
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------
db_name 			     string	 TDEB

SQL> alter system set global_names=false scope=both sid='*';
SQL> create database link STBY connect to C##SYSOPER identified by xxxxxxxxx using 'TDEA_STANDBY';

SQL> create pluggable database PDBA from PDBA@STBY keystore identified by xxxxxxxxx ;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [3647], [11], [16400], [], [], [], [], [], [], [], [], []

Wow. Thats not really meaningful right?. Internal error. Well, lets take a look to Oracle Support and specifically to note “Create Pluggable Database From ADG errors out with ORA-600 [3647] (Doc ID 2072550.1)“, where they say :

“With the Active dataguard open read only and apply process running the standby database datafiles would be applying the changes it receives. The datafile in such case would be in Standby Fuzzy status . This is Unlike your Normal read only database where the files would be consistent and in No Fuzzy status.”

So for our test #1, with Apply-On and standby opened in read-only mode (Active dataguard with real time apply), we will not be able to take a clone of our PDB. And the reason behind this, is that in 19c Oracle cannot have 2 recovery operations running concurrently (the standby recovery plus the cloning operation – which is a recovery as well).

Whats next?. To stop the apply services but keep the Standby database (and it’s PDBs) opened in read-only mode:

dgmgrl sys/xxxxxxxxx@TDEA_madrid
edit database TDEA_fra1mp set state='APPLY-OFF';
exit

[oracle@frankfurt ~]$ . TDEA.env
[oracle@frankfurt ~]$ sqlplus / as sysdba
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY --> no apply

[oracle@frankfurt ~]$ . TDEB.env
[oracle@frankfurt ~]$ sqlplus / as sysdba
SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- 
	 2 PDB$SEED			  READ ONLY  NO

SQL> create pluggable database PDBA from PDBA@STBY keystore identified by xxxxxxxxxx ;

Pluggable database created.

Now lets open our PDB database clone. We will need to manually open the wallet as we are not using an autowallet in this container:

SQL> alter session set container=PDBA;
SQL> administer key management set keystore open identified by xxxxxxxxx;
SQL> alter database open;
SQL> alter session set container=CDB$ROOT;
SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- 
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBA 			  READ WRITE NO

Now we have a clear idea about the need of disabling DG recovery during the PDB clone operation. But we’ve been able to do it keeping the standby opened in read-only mode. Additionally, the transport services are quite alive (the standby database processes are running!), so even when the apply services are temporarily stopped, the standby is still receiving the redo information from the primary DB. They will just be kept until we resume the standby recovery (APPLY-ON).

So we have one combination left to test; what about having the apply services stopped and the standby CDB opened in MOUNT status?. Is it possible to do a remote clone? :

[oracle@frankfurt ~]$ . TDEB.env
[oracle@frankfurt ~]$ sqlplus / as sysdba
SQL> alter pluggable database PDBA close immediate;
SQL> drop pluggable database PDBA including datafiles;
SQL> exit

[oracle@frankfurt ~]$ . TDEA.env
[oracle@frankfurt ~]$ srvctl stop database -d TDEA_fra1mp
[oracle@frankfurt ~]$ srvctl start database -d TDEA_fra1mp -o mount
[oracle@frankfurt ~]$ sqlplus / as sysdba
SQL> select open_mode from v$database;

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

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MOUNTED
	 4 PDBA 			  MOUNTED

SQL> exit
[oracle@frankfurt ~]$ . TDEB.env
[oracle@frankfurt ~]$ sqlplus / as sysdba

SQL> create pluggable database PDBA from PDBA@STBY keystore identified by xxxxxxxxxx ;
*
ERROR at line 1:
ORA-17627: ORA-01033: ORACLE initialization or shutdown in progress
ORA-17629: Cannot connect to the remote database server

So having the Standby CDB just mounting is not viable. Extra case: what if our standby CDB is opened read-only, but our standby PDB is in mount status?. Recovery is still set to off (APPLY-OFF):

[oracle@frankfurt ~]$ . TDEA.env
[oracle@frankfurt ~]$ sqlplus / as sysdba
SQL> alter database open read only;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBA 			  MOUNTED
SQL> exit

[oracle@frankfurt ~]$ . TDEB.env
[oracle@frankfurt ~]$ sqlplus / as sysdba
SQL> create pluggable database PDBA from PDBA@STBY keystore identified by xxxxxxxxxx ;
*
ERROR at line 1:
ORA-17628: Oracle error 65036 returned by remote Oracle server
ORA-65036: pluggable database  not open in required mode

So even when our dblink is stablished against the CDB, it is clear that we need Active Dataguard (both standby and PDB opened in read-only mode) in order to clone a PDB from the Physical Standby. Additionally, we will need to keep the recovery stopped during the cloning. In the meantime, the standby database will keep receiving the redo from the primary database, so we will be able to resume the apply, resync and re-enable the real time apply.

Now you will probably be thinking; well my 200Tb database cannot afford having it’s apply stopped during the cloning. Even if that clone is done from the same Standby server. It would take hours for a standard clone to finish. Then, I would invite you to remember what we stressed in the first multitenant article: be smart and combine PDB operations. Why don’t we create a refreshable PDB from the Physical Standby?. Something like this:

With this approach we would only need to temporarily stop the redo apply during the incremental refresh of the refreshable PDB (as the PDB refresh is a recovery operation, stopping the redo apply is still required). We could even create snapshot copies from the read-only refreshable PDBs.

Finally; do you know that in 21c there is a dedicated recovery process for hot clones in Dataguard?. Maybe, we can give it a try in the following posts.

19c PDB clones from Physical Standby

Share this: