How to Configure Oracle Transparent Data Encryption (TDE) on Standby Database

Table of Contents

How to Configure Oracle Transparent Data Encryption (TDE) on Standby Database

In this article, we will see how to enable Oracle Transparent Data Encryption TDE on the Standby database with easy and simple steps. If we have Oracle Transparent Data Encryption TDE enabled primary database standby database won’t be able to apply the logs. And it may possible that it could out of synchronization from the primary side.

I would recommend enabling Oracle Transparent Data Encryption TDE on standby along with the primary database. If you do it later you might see standby is out of synchronization with Primary database. Rebuilding a big database standby database is a complex and time-consuming task. In this way, you can enable Oracle Transparent Data Encryption TDE with few easy steps. Primary and standby could be TDE enabled in the same downtime window.

Oracle Transparent Data Encryption TDE is one of the ways in Oracle Advanced security to secure the database physical datafiles.

On Standby Database

[su_note note_color=”#0174be” text_color=”#ffffff” radius=”4″]TDE Prerequisites[/su_note]

Make sure OPtach 23315889 has been applied to oracle standby database oracle home

opatch lsinventory| grep 23315889

On Primary Database

1. Login to Primary database and get the wallet path.

SQL>select WRL_PARAMETER from v$encryption_wallet;

WRL_PARAMETER
--------------------------------------------------------------------------------
/u01/oracle/admin/wallet/testdb01/

2. Check the wallet key files.

ls -lrt /u01/oracle/admin/wallet/testdb01

-rw-------. 1 oracle oninstall 2093 Jun 9 06:59 ewallet.p12
-rw-------. 1 oracle oninstall 1928 Jun 9 07:24 cwallet.sso

3. Zip the keys and Copy the files to the standby server.

cd /u01/oracle/admin/wallet/testdb01
zip /tmp/walletkeys.zip *
scp /u01/oracle/admin/wallet/testdb01/*wallet.* 192.168.56.5:/tmp

On Standby Database

1. Go to the Standby data $TNS_ADMIN and add the wallet path.

cd $TNS_ADMIN
vim sqlnet.ora

--add following line

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/oracle/admin/$ORACLE_SID/wallet/)))

2. Make the directory

mkdir -p /u01/oracle/admin/$ORACLE_SID/wallet/

Note: $ORACLE_SID is your database Name

3. Copy the primary database key to standby wallet location.

cd /tmp
unzip walletkeys.zip
cp /tmp/*wallet*.* /opt/oracle/admin/$ORACLE_SID/wallet/wallet_tde

3. Stop Standby recovery the standby database.

sqlplus "/as sysdba"

SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate

4. Start the standby database in read-only mode.

SQL> Startup;

5. Check wallet path it should be Open and Autologin mode.

SQL> select status,wallet_type from v$encription_wallet;

status wallet_type
--------------------------------
OPEN AUTOLOGIN

6. Prepare the tablespace datafile encryption script.

$ sqlplus / as sysdba
SQL>set heading off
SQL>set linesize 150
SQL>spool tablespace_datafiles_encrypt.sql
SQL>select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','TEMP1','TEMP2','APPS_UNDOTS1');
SQL>exit

[su_box title=”IMP Note” box_color=”#fe2227″ title_color=”#101112″] Make sure you leave following table space database in encryption script ‘SYSTEM’, ‘SYSAUX’,’TEMP1′,’TEMP2′,’APPS_UNDOTS1′[/su_box]

7. Stop the Standby Database again.

SQL> Shutdown normal;

8. Start the database in mount mode.

SQL> startup mount

9. Run the database encryption script.

SQL> @tablespace_datafiles_encrypt.sql

10. Once Tablespace encrypt script is completed successfully Start the standby recovery.

SQL> alter database recover managed standby database disconnect;

20. Monitor standby log apply. If you see logs are applying properly on standby side. You are Done!

SQL> select process, status, thread#, sequence#, from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE#
------- ------------ ---------- ----------
MRP0 APPLYING_LOG 1 10293

Hope you will find this article helpful. in case if you have any questions ask me my comments.

Like our Facebook Page

How to connect PostgreSQL Database from PgAdmin
How to create AWS rds PostgreSQL Database
Convert pem to ppk
How to Install PuTTy on Window
How to Install and create AWS EC2 Instance using Terraform
AWS MySQL RDS Database Creation using AWSCLI
How to Create MySQL Database with AWS RDS
How to connect to AWS MySQL / MariaDB RDS or EC2 database from MySQL WorkBench