A Simple Approach – Creating PDBs in a Data Guard environment with TDE enabled

Introduction

In the Oracle Cloud every newly created database from 12c onward uses the Oracle Multitenant Architecture. Transparent Data Encryption is also enabled by default. So, as soon as you use Data Guard and create new PDBs, you have to take care of copying the wallets to the standby server.

We already discussed two approaches in previous blogs:

However, both involve a large set of different steps to run. This was almost exhausting. In this blog we are going to have a third and really simple way to create PDBs in a Data Guard environment with TDE enabled.

Here again, If you are using Oracle Key Vault (OKV) to manage your TDE encryption keys, then you can stop reading. With OKV all the “pain” goes away, as both primary and standby use the same TDE wallet that is managed by OKV.

The Environment

Oracle Database 2-node RAC release version 19.9 with Data Guard and TDE enabled. I’m using the Database Cloud Service on virtual machines with Enterprise Edition as TDE is enabled by default and I could create Data Guard by a click of the button.

Step 1: Stop Standby

On the standby:

srvctl stop database -db <standby_db_unique_name> -stopoption IMMEDIATE

Step 2: Create PDB on Primary

On the primary:

SQL> create pluggable database PDB003 admin user pdb3admin identified by <pdb_admin_password>;
SQL> alter pluggable database PDB003 open instances = all;
SQL> alter session set container = PDB003;
SQL> administer key management set key force keystore identified by <TDE_Password> with backup;

Step 3: Copy TDE Wallet to Standby

Copy the TDE wallet files from the primary to the standby. The location of the TDE wallet is defined by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file:

cat $ORACLE_HOME/network/admin/sqlnet.ora
scp -p /opt/oracle/dcs/commonstore/wallets/tde/<primary_db_unique_name>/*wallet.* oracle@<standby_server_ip>:/opt/oracle/dcs/commonstore/wallets/tde/<standby_db_unique_name>/

In a RAC environment, a shared TDE wallet is used by all nodes. Hence, you need to copy the wallet to one standby server only.

Step 4: Start Standby

On the standby:

srvctl start database -db <standby_db_unique_name> -startoption MOUNT

The new PDB will be created on the standby database.

-- status of datafiles
select d.file#, d.name as filename, d.status 
from v$datafile d join v$pdbs p on (d.con_id = p.con_id)
where p.name = 'PDB003';

In case of Active Data Guard, open the database and the PDBs in READ ONLY mode.

If ADG, on the standby:

SQL> alter database open; --on all instances
SQL> alter pluggable database all open instances = all;

Step 5: Create TEMP file on Standby

As tempfiles are not copied to the standby PDB, add a new tempfile to the TEMP tablespace.

In case of Active Data Guard, the PDB is already opened in READ ONLY mode as in previous step.

On the standby:

SQL> alter session set container = PDB002;
SQL> alter tablespace temp add tempfile;

If you are NOT using Active Data Guard, then add the temp file after switchover/failover, or: stop the redo apply, open the database in READ ONLY, create the temp file, restart in MOUNT mode, and enable redo apply again:

DGMGRL> edit database <standby_db_unique_name> set state = 'apply-off';
SQL> alter database open;
SQL> alter pluggable database PDB002 open;
SQL> alter session set container = PDB002;
SQL> alter tablespace temp add tempfile;
SQL> alter session set container = cdb$root;
SQL> shutdown immediate
SQL> startup mount
DGMGRL> edit database <standby_db_unique_name> set state = 'apply-on';
DGMGRL> show configuration

Conclusion

Shut down the standby database, create the PDB on the primary, copy the wallet files to the standby, and restart the standby database. The new PDB will be automatically created on the standby. This approach involves only few steps and does not require any RMAN restore and recovery operation.

Further Reading

Would you like to get notified when the next post is published?