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:
- Using STANDBYS=NONE to create PDBs in a Data Guard environment with TDE enabled
- Create PDBs in a Data Guard Environment with TDE enabled
However, both involve a large set of different steps to run. This was almost exhausting. In this blog post, 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 with 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 the 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 the case of Active Data Guard, the PDB is already opened in READ ONLY mode as in the previous step.
On standby:
SQL> alter session set container = PDB002;
SQL> alter tablespace temp add tempfile;
SQL> select name from v$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 standby. This approach involves only a few steps and does not require any RMAN restore and recovery operation.
Database Heartbeat 