8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

DG PDB : Oracle Data Guard per Pluggable Database in Oracle Database 21c (21.7 Onward)

DG PDB is a new feature introduced in Oracle database 21c (21.7) which allows PDB-level Data Guard protection. This article describes a basic setup of DG PDB.

Thanks to Fernando Simon, who spotted some of the issues, which saved me a lot of time and headaches. You should read his post on this feature here.

Related articles.

Assumptions

This article assumes the following infrastructure is in place before starting.

DB PDB

In addition to the normal database prerequisites, we have installed the sshpass package.

dnf install -y sshpass

These environment variables will be used for many of the setup scripts. Looking at their values may help understand what we are doing here. Adjust as required, but be consistent.

# Hosts
export DOMAIN_NAME=localdomain

export NODE1_HOSTNAME=ol8-21-dgpdb1
export NODE2_HOSTNAME=ol8-21-dgpdb2
export NODE1_FQ_HOSTNAME=${NODE1_HOSTNAME}.${DOMAIN_NAME}
export NODE2_FQ_HOSTNAME=${NODE2_HOSTNAME}.${DOMAIN_NAME}

# Paths
export ORACLE_BASE=/u01/app/oracle
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_HOME_EXT=product/21.0.0/dbhome_1
export READONLY_HOME=${ORACLE_BASE}/homes/OraDB21Home1
export DATA_DIR=/u01/oradata

# Databases
export NODE1_ORACLE_SID=cdb1
export NODE1_DB_NAME=${NODE1_ORACLE_SID}
export NODE1_PDB_NAME=pdb1
export NODE1_DB_UNIQUE_NAME=${NODE1_ORACLE_SID}
export NODE2_ORACLE_SID=cdb2
export NODE2_DB_NAME=${NODE2_ORACLE_SID}
export NODE2_PDB_NAME=pdb1_dg
export NODE2_DB_UNIQUE_NAME=${NODE2_ORACLE_SID}

# Passwords
export ORACLE_PASSWORD=oracle
export SYS_PASSWORD="SysPassword1"
export PDB_PASSWORD="PdbPassword1"
export DGPDB_INT_PASSWORD="DgpdbIntPassword1"

Some of the prerequisite scripts below reference these environment variables, which are expanded out in the subsequent command and configuration files. If you are running the commands manually, rather than as scripts, remember to manually expand the commands with the relevant values before running them.

There is a Vagrant build here, which includes the build of the servers, the database software installations, database creations and the perquisites, so you can jump straight to the DG PDB configuration section.

Prerequisites

Enable Oracle Managed Files (OMF) on both instances.

sqlplus / as sysdba <<EOF
alter system set db_create_file_dest='${DATA_DIR}';
alter system set db_create_online_log_dest_1='${DATA_DIR}';
-- Do addition log destination for multiplexing.
--alter system set db_create_online_log_dest_2='${DATA_DIR}';
exit;
EOF

The "tnsnames.ora" files for both instances contain the following entries.

CDB1.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-dgpdb1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = cdb1)
    )
  )

CDB2.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-dgpdb2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb2.world)
    )
  )

Prepare both instances for Data Guard. This sets all necessary initialisation parameters, sets varous database states and enables the broker. You can read what it does here.

# Node 1
dgmgrl / <<EOF
prepare database for data guard
  with db_unique_name is ${NODE1_DB_UNIQUE_NAME}
  db_recovery_file_dest is "${ORACLE_BASE}/fast_recovery_area"
  db_recovery_file_dest_size is 20g;
exit;
EOF

# Node 2
dgmgrl / <<EOF
prepare database for data guard
  with db_unique_name is ${NODE2_DB_UNIQUE_NAME}
  db_recovery_file_dest is "${ORACLE_BASE}/fast_recovery_area"
  db_recovery_file_dest_size is 20g;
exit;
EOF

Some of the Data Guard configuration will fail unless we use a wallet to hold the credentials, allowing us to connect without having to specify credentials manually. Add a wallet location into the "sqlnet.ora" file on both nodes.

cat >> ${READONLY_HOME}/network/admin/sqlnet.ora <<EOF

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE = true
WALLET_LOCATION =
(
   SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
         (DIRECTORY = ${READONLY_HOME}/wallet)
      )
)
EOF

Restart the listener on both nodes.

lsnrctl stop
lsnrctl start

Create a wallet on the source database.

mkdir -p ${READONLY_HOME}/wallet
mkstore -wrl ${READONLY_HOME}/wallet -createALO
mkstore -wrl ${READONLY_HOME}/wallet -createCredential ${NODE1_ORACLE_SID} sys ${SYS_PASSWORD}
mkstore -wrl ${READONLY_HOME}/wallet -createCredential ${NODE2_ORACLE_SID} sys ${SYS_PASSWORD}
mkstore -wrl ${READONLY_HOME}/wallet -listCredential

Copy the wallet to the second node. These commands are run from the second node.

mkdir -p ${READONLY_HOME}/wallet

ssh-keyscan -H ${NODE1_FQ_HOSTNAME} >> ~/.ssh/known_hosts
ssh-keyscan -H ${NODE2_FQ_HOSTNAME} >> ~/.ssh/known_hosts

echo ${ORACLE_PASSWORD} > /tmp/temp1.txt
sshpass -f /tmp/temp1.txt scp ${NODE1_FQ_HOSTNAME}:${READONLY_HOME}/wallet/* ${READONLY_HOME}/wallet
rm /tmp/temp1.txt

Check you can connect to the source and target databases from each node without needing a password. If this doesn't work there is no point trying to move forward.

sqlplus /@cdb1 as sysdba
sqlplus /@cdb2 as sysdba

DG PDB Configuration

Create a configuration for each instance.

# Node 1
dgmgrl /@cdb1 <<EOF
create configuration my_dg_config1 as primary database is cdb1 connect identifier is cdb1;
exit;
EOF

# Node 2
dgmgrl /@cdb2 <<EOF
create configuration my_dg_config2 as primary database is cdb2 connect identifier is cdb2;
exit;
EOF

Add the the configuration for the standby to the source database configuration.

dgmgrl /@cdb1 <<EOF
add configuration my_dg_config2 connect identifier is cdb2;
exit;
EOF

Enable the configurations on both nodes.

# Node 1
dgmgrl /@cdb1 <<EOF
enable configuration all;
exit;
EOF

# Node 2
dgmgrl /@cdb2 <<EOF
enable configuration all;
exit;
EOF

Connect to the standby CDB and add the pluggable database we want to protect to the configuration on the second node. It will prompt for the DGPDB_INT database user password. The first time we call the command it will fail on the standby node, but it works when we issue the command a second time.

dgmgrl /@cdb2


DGMGRL> add pluggable database pdb1_dg at cdb2 source is pdb1 at cdb1 pdbfilenameconvert is "'/CDB1/pdb1/','/CDB2/pdb1_dg/'";
Connected to "cdb1"
Enter password for DGPDB@cdb1:
Connected to "cdb2"
Enter password for DGPDB@cdb2:
ORA-17628: Oracle error 11402 returned by remote Oracle server
ORA-11402: Oracle Data Guard site entry not found

DGMGRL>


DGMGRL> add pluggable database pdb1_dg at cdb2 source is pdb1 at cdb1 pdbfilenameconvert is "'/CDB1/pdb1/','/CDB2/pdb1_dg/'";
Connected to "cdb1"
Connected to "cdb2"

Pluggable Database "PDB1_DG" added
DGMGRL>

Show the configuration of the PDBs on the two nodes.

DGMGRL> show pluggable database pdb1 at cdb1;
Connected to "cdb1"

Pluggable database 'pdb1' at database 'cdb1'

  Data Guard Role:     Primary
  Con_ID:              3
  Active Target:       con_id 4 at cdb2

Pluggable Database Status:
SUCCESS


DGMGRL> show pluggable database pdb1_dg at cdb2;

Pluggable database 'pdb1_dg' at database 'cdb2'

  Data Guard Role:     Physical Standby
  Con_ID:              4
  Source:              con_id 3 at cdb1
  Transport Lag:       (unknown)
  Intended State:      APPLY-ON
  Apply State:         Not Running

Pluggable Database Status:
DGM-5103: one or more data files were not found
ORA-16766: Redo Apply is stopped

DGMGRL>

Notice the standby PDB currently have no datafiles. We have to manually copy those between the servers. There are various ways to do this, including RMAN datafile copies, the DBMS_FILE_TRANSFER package and SCP. In this example we will use SCP.

The following commands copy the pdb1 datafiles to the correct location on the second node for the pdb1_dg database.

mkdir -p /u01/oradata/CDB2/pdb1_dg/
echo ${ORACLE_PASSWORD} > /tmp/temp1.txt
sshpass -f /tmp/temp1.txt scp ${NODE1_FQ_HOSTNAME}:/u01/oradata/CDB1/pdb1/* /u01/oradata/CDB2/pdb1_dg/
rm /tmp/temp1.txt
ls /u01/oradata/CDB2/pdb1_dg/

We enable the configuration again on the second node.

dgmgrl /@cdb2 <<EOF
enable configuration all;
exit;
EOF

Now we can see pdb1_dg has a normal standby status.

dgmgrl /@cdb2


DGMGRL> show pluggable database pdb1_dg at cdb2;

Pluggable database 'pdb1_dg' at database 'cdb2'

  Data Guard Role:     Physical Standby
  Con_ID:              4
  Source:              con_id 3 at cdb1
  Transport Lag:       (unknown)
  Intended State:      APPLY-ON
  Apply State:         Running
  Apply Instance:      cdb2
  Average Apply Rate:  (unknown)
  Real Time Query:     OFF

Pluggable Database Status:
SUCCESS

DGMGRL>

Switchover PDB

A switchover is a controlled role change between the primary and standby PDBs. There is nothing wrong with the primary PDB, but we want to convert the standby PDB into the primary PDB.

Connect to the current primary database and switchover the pluggable database.

dgmgrl /@cdb1


DGMGRL> switchover to pluggable database pdb1_dg at cdb2;
Verifying conditions for Switchover...

Connected to "cdb2"
Connected to "cdb1"
  Source pluggable database is 'PDB1' at database 'cdb1'

Performing switchover NOW, please wait...

  Closing pluggable database 'PDB1'...
  Switching 'PDB1' to standby role...
Connected to "cdb2"
  Waiting for 'PDB1_DG' to recover all redo data...
  Stopping recovery at 'PDB1_DG'...
  Converting 'PDB1_DG' to primary role...
  Opening new primary 'PDB1_DG'...
Connected to "cdb1"
  Waiting for redo data from new primary 'PDB1_DG'...
  Starting recovery at new standby 'PDB1'...

Switchover succeeded, new primary is "PDB1_DG"
DGMGRL>

Checking the status of the two configurations shows us the roles have changed.

DGMGRL> show pluggable database pdb1 at cdb1;

Pluggable database 'pdb1' at database 'cdb1'

  Data Guard Role:     Physical Standby
  Con_ID:              3
  Source:              con_id 4 at cdb2
  Transport Lag:       (unknown)
  Intended State:      APPLY-ON
  Apply State:         Running
  Apply Instance:      cdb1
  Average Apply Rate:  (unknown)
  Real Time Query:     OFF

Pluggable Database Status:
SUCCESS

DGMGRL>


DGMGRL> show pluggable database pdb1_dg at cdb2;
Connected to "cdb2"

Pluggable database 'pdb1_dg' at database 'cdb2'

  Data Guard Role:     Primary
  Con_ID:              4
  Active Target:       con_id 3 at cdb1

Pluggable Database Status:
SUCCESS

DGMGRL>

We can do a switchover to return the roles to their original settings.

dgmgrl /@cdb2


DGMGRL> switchover to pluggable database pdb1 at cdb1;
Verifying conditions for Switchover...

Connected to "cdb1"
Connected to "cdb2"
  Source pluggable database is 'PDB1_DG' at database 'cdb2'

Performing switchover NOW, please wait...

  Closing pluggable database 'PDB1_DG'...
  Switching 'PDB1_DG' to standby role...
Connected to "cdb1"
  Waiting for 'PDB1' to recover all redo data...
  Stopping recovery at 'PDB1'...
  Converting 'PDB1' to primary role...
  Opening new primary 'PDB1'...
Connected to "cdb2"
  Waiting for redo data from new primary 'PDB1'...
  Starting recovery at new standby 'PDB1_DG'...

Switchover succeeded, new primary is "PDB1"
DGMGRL>

Sometimes the switchover will hang, waiting for redo transfer. Sometimes this can be fixed by forcing a logfile switch on the database that was the primary when the switchover command was issued. Sometimes this doesn't work though.

# Node 1
sqlplus /@cdb1 as sysdba <<EOF
alter system archive log current;
exit;
EOF

# Node 2
sqlplus /@cdb2 as sysdba <<EOF
alter system archive log current;
exit;
EOF

Always check the redo apply status using the "show pluggable database {pdb-name} at {cdb-name};" command. You may need to manually turn on the redo apply for the standby database on the relevant node.

# Node 1
dgmgrl /@cdb1 <<EOF
edit pluggable database pdb1 at cdb1 set state=apply-on;
exit;
EOF

# Node 2
dgmgrl /@cdb2 <<EOF
edit pluggable database pdb1_dg at cdb2 set state=apply-on;
exit;
EOF

Failover PDB

A failover is only performed when there is something wrong with the primary PDB and we need to force the standby to become the primary.

Connect to the standby database and failover the pluggable database.

dgmgrl /@cdb2


DGMGRL> failover to pluggable database pdb1_dg AT cdb2;
Verifying conditions for Failover...

Connected to "cdb2"
Connected to "cdb1"
  Source pluggable database is 'PDB1' at database 'cdb1'

Performing failover NOW, please wait...

  Closing pluggable database 'PDB1'...
  Converting 'PDB1' to standby role...
Connected to "cdb2"
  Waiting for 'PDB1_DG' to recover all redo data...
  Stopping recovery at 'PDB1_DG'...
  Converting 'PDB1_DG' to primary role...
  Opening new primary 'PDB1_DG'...

Failover succeeded, new primary is "PDB1_DG".
DGMGRL>

The original primary PDB is not automatically initiated as a standby PDB. Assuming there is nothing fundamentally wrong with the PDB, which caused the failover in the first place, we can convert it to a standby by turning on redo apply.

DGMGRL> edit pluggable database pdb1 at cdb1 set state=apply-on;
Succeeded.
DGMGRL>

Thoughts

For more information see:

Hope this helps. Regards Tim...

Back to the Top.