Announcement – Oracle Data Guard per Pluggable Database (DGPDB) is available today with the 21.7 RU

I am very happy to announce that with the new 21.7 RU for Oracle Database released today (Doc ID 2796590.1), a new functionality for Data Guard is out! Data Guard per Pluggable Database, or DGPDB for short, fully enables multi-tenancy at high availability level.

Ludovico Caldara is talking about this new functionality in an official post here. Furthermore, I will show an example of this new functionality in action in this post.

Preparing both CDBs for DGPDB

• First, at both CDBs (Primary and Secondary, remember that this new functionality uses two active CDBs, not an Active-Passive environment), change the following parameters:

SQL> alter system set dg_broker_start=true;
SQL> alter system set standby_file_management=auto;

• Second, Unlock SYSDG and change its password at both environments (some steps will require SYS, though).

SQL> alter user SYSDG account unlock;
SQL> grant SYSDG to SYSDG;
SQL> alter user SYSDG identified by secret;

Note: Don’t forget to change the dg_broker_config_file on RAC to put it in on ASM or ACFS.

• Third, add the TNS aliases in $(orabasehome)/network/admin/tnsnames.ora, so they point to CDB$ROOT at both environments.

BOSTON = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = boston.us.oracle.com))
)
NEWYORK = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2.us.oracle.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = newyork.us.oracle.com)) )

• Forth, enable force logging (in both environments)

SQL> alter database force logging;

• Fifth, add the broker configuration by creating the configuration for the first CDB. Which one is irrelevant!

DGMGRL> connect sys/<my_SYS_password>@boston;

Connected to "boston"

DGMGRL> create configuration boston primary database is boston

connect identifier is boston

Configuration "boston" created with primary database "boston"

DGMGRL> show configuration;

Configuration - boston

   Protection Mode: MaxPerformance

   Members:

   boston - Primary database

Fast-Start Failover: Disabled

Configuration Status:

DISABLED

Note: If not using a password file, remember to set up your wallet within the environments.

Creation of wallets:

WLTLOC=/opt/oracle/dcs/commonstore/wallets/client
mkdir ${WLTLOC}
mkstore -wrl ${WLTLOC} -create
mkstore -wrl ${WLTLOC} -createCredential BOSTON sys
mkstore -wrl ${WLTLOC} -createCredential NEWYORK sys
cat <<EOF >> $(orabasehome)/network/admin/sqlnet.ora
WALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=/opt/oracle/dcs/commonstore/wallets/client)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0
EOF

• Sixth, add the configuration for the other database as the next step in configuring the broker.

DGMGRL> add configuration newyork connect identifier is newyork; <- New Command

Configuration newyork added.

DGMGRL> enable configuration all; <- New Command

Succeeded.

DGMGRL> show configuration;

Configuration - boston

   Protection Mode: MaxPerformance

Members:

   boston - Primary database
   newyork - Primary database in newyork configuration

Fast-Start Failover: Disabled Configuration Status:
   SUCCESS (status updated 4 seconds ago)

Note: The peer CDB is added with “Add configuration”. The peer CDB is primary, so that it will have its own configuration, and each CDB configuration will know the other. There are no Target PDBs yet, so there is no log shipping.

• As a best practice, let’s check the PDBs before adding them to the new CDB as Standby.

DGMGRL> show pluggable database red at boston;

Pluggable database 'red' at database 'boston'

   Data Guard Role: Not Protected

DGMGRL> show pluggable database fog at newyork;

Pluggable database fog at database 'newyork'

   Data Guard Role: Not Protected

• Make sure the PDBs are open READ WRITE.

SQL> connect sys/<my_SYS_password>@boston as sysdba

Connected.

SQL>select open_mode from v$pdbs where name='RED';

OPEN_MODE
----------
READ WRITE

SQL> connect sys/<my_SYS_password>@newyork as sysdba

Connected.

SQL> select open_mode from v$pdbs where name='FOG';

OPEN_MODE
----------
READ WRITE

• Seventh, add the PDBs you want to protect, by executing the SQL to create the pluggable database as a STANDBY at the target CDB.

DGMGRL> add pluggable database red at newyork source is red at boston pdbfilenameconvert is "'/BOSTON/RED/','/NEWYORK/RED/'";

Enter password for DGPDB@boston:

Enter password for DGPDB@newyork:

Pluggable Database "RED" added

DGMGRL> add pluggable database fog at boston source is fog at newyork pdbfilenameconvert is "'/NEWYORK/FOG/','/BOSTON/FOG/'";

Pluggable Database "FOG" added

• The add pluggable database command would:
  • Verifies both databases exist and are reachable
  • Verifies the DGPDB_INT internal user is unlocked and asks for a password to unlock it otherwise
  • Verifies, or creates credentials for the internal user at both databases
  • Grants SYSDG to the internal user in the source PDB
  • Grants EXECUTE on the credential at both databases
  • Adds an entry to the DATA_GUARD_SITE$ table at both databases with information about  the other database

• When adding the PDB, please note:
  • SYSDG would not work here.
  • The pdbfilenameconvert is mandatory.
  • The password is for internal activity between the CDBs. It’s required when setting up DGPDB the first time. It can differ between source and target CDB.
  • The two databases start the log shipping, but the PDBs are NOT there yet.
• The following status shows the redo transport set up between the two CDBs.

DGMGRL> show configuration;

Configuration - boston

   Protection Mode: MaxPerformance
   Members:
   boston - Primary database</code></p>
   newyork - Primary database in newyork configuration

• Note: The add pluggable database adds a placeholder PDB, but does not copy the datafiles yet, so the MRP process will try to start, but will complain about the missing datafiles. This is expected.

• Eight, copy the PDBs datafiles manually. •This requires BEGIN BACKUP and END BACKUP as RMAN integration is not there yet.

SQL> connect sys/<my_SYS_password>@boston

SQL> alter session set container=red;

Session altered.

SQL> alter database begin backup;

Database altered.

SQL>host scp -r /u01/data/BOSTON/RED oracle@host2:/u01/data/NEWYORK

SQL> alter database end backup;

Database altered.

SQL> connect sys/<my_SYS_password>@newyork

SQL> alter session set container=fog;

Session altered.

SQL> alter database begin backup;

Database altered.

SQL> host scp -r oracle@host2:/u01/data/NEWYORK/FOG /u01/data/BOSTON

SQL> alter database end backup;

Database altered.

• After copying the datafiles, the transport will succeed, but the apply process would continue stopped.

DGMGRL> show configuration;

Configuration - boston
   Protection Mode: MaxPerformance
   Members:
   boston - Primary database
      Warning: ORA-16910: Inconsistency detected for one or more pluggable databases
   newyork - Primary database in newyork configuration

Data Guard for PDB: Enabled in SOURCE and TARGET role
Configuration Status:

WARNING (status updated 46 seconds ago)

DGMGRL> show pluggable database red at newyork;

Pluggable database - red at newyork
   Data Guard Role: Physical Standby
   Con_ID: 4
   Source: con_id 3 at boston
   Transport Lag: 0 seconds (computed 22 seconds ago)
   Intended State: APPLY-ON
   Apply State: Not Running

Pluggable Database Status: ORA-16766: Redo Apply is stopped

Note: The drc log will start complaining that the standby redo logs are missing. This is what you see when you try to start the apply at PDB level:

Data Guard Broker Status Summary:
  Type                                        Name   Severity Status
  Configuration                    boston Warning ORA-16608: one or more members have warning
  Primary Database           boston Warning ORA-16789: standby redo logs configured incorrectly
  Remote Configuration newyork Success ORA-0: normal, successful completion

2022-07-20T15:55:36.602+00:00

Error: standby redo logs must be configured when LogXptMode is set to 'ASYNC'

• Ninth, add the Standby Redo Logs to the Target PDBs (Once per CDB, but at PDB level, on the TARGET PDB).

SQL> connect sys/<my_SYS_password>@boston as sysdba

Connected.

SQL> alter session set container=fog;

Session altered.

SQL> alter database add standby logfile thread 1
2 group 4 ('/u01/fra/BOSTON/onlinelog/standby_redo04.log') size 200M,
3 group 5 ('/u01/fra/BOSTON/onlinelog/standby_redo05.log') size 200M,
4 group 6 ('/u01/fra/BOSTON/onlinelog/standby_redo06.log') size 200M,
5 group 7 ('/u01/fra/BOSTON/onlinelog/standby_redo07.log') size 200M;

Database altered.

SQL> connect sys/<my_SYS_password>@newyork as sysdba

Connected.

SQL> alter session set container=red;

Session altered.

SQL> alter database add standby logfile thread 1
2 group 4 ('/u01/fra/NEWYORK/onlinelog/standby_redo04.log') size 200M,
3 group 5 ('/u01/fra/NEWYORK/onlinelog/standby_redo05.log') size 200M,
4 group 6 ('/u01/fra/NEWYORK/onlinelog/standby_redo06.log') size 200M,
5 group 7 ('/u01/fra/NEWYORK/onlinelog/standby_redo07.log') size 200M;

Database altered.

• Note: The Standby Redo Logs are visible at PDB level, but with CON_ID=0

• Tenth, start the Redo Apply (Notice the new syntax per PDB).

DGMGRL> connect sys/<my_SYS_password>@boston

Connected to "boston"

Connected as SYSDBA.

DGMGRL> edit pluggable database fog at boston set state='APPLY-ON';

Succeeded.

DGMGRL> edit pluggable database red at newyork set state='APPLY-ON';

Succeeded.

• Please note:
  • There is one set of SRLs  (Standby Redo Logs) to receive all the foreign redo coming from the source CDB
  • The whole redo stream is sent from the source to the target CDB
  • Only the relevant redo data is applied to the target PDBs
  • Target PDBs added after the first one will use the existing standby redo logs

• Now let’s verify the status of the PDBs by using the DGMGRL.

DGMGRL> show all pluggable database at boston;

Pluggable database - SAND at boston

   Data Guard Role: Not Protected

Pluggable database - RED at boston

   Data Guard Role: Primary
   Con_ID: 4
   Active Target: con_id 5 at newyork

Pluggable Database Status:

SUCCESS

Pluggable database - FOG at boston

   Data Guard Role: Physical Standby
   Con_ID: 5
   Source: con_id 3 at newyork
   Transport Lag: 0 seconds (computed 39 seconds ago)
   Intended State: APPLY-ON
   Apply State: Running
   Apply Instance: boston
   Average Apply Rate: (unknown)
   Real Time Query: OFF

Pluggable Database Status:

SUCCESS

• Finally, let’s try this new environment and do a switchover.

DGMGRL> ! date

Executing operating system command(s):" date"

Tue Jul 20 22:19:25 GMT 2022.   ----> Take note of this time

DGMGRL> switchover to pluggable database red at newyork;

Verifying conditions for Switchover...
   Source pluggable database is 'RED' at database 'boston'
Performing switchover NOW, please wait...
   Closing pluggable database 'RED'...
   Switching 'RED' to standby role...
   Waiting for 'RED' to recover all redo data...
   Stopping recovery at 'RED'...
   Converting 'RED' to primary role...
   Opening new primary 'RED'...
   Waiting for redo data from new primary 'RED'...
   Starting recovery at new standby 'RED'...
   Switchover succeeded, new primary is "RED"

DGMGRL>! date

Executing operating system command(s):" date"

Tue Jul 20 22:19:38 GMT 2022. --------> Took 13 Seconds 

I hope you enjoyed this demo! More to come soon!

Kind Regards,

Francisco Munoz Alvarez