How to Enable Oracle Transparent Data Encryption TDE on the Standby Database
If we have Oracle Transparent Data Encryption TDE enabled primary database the logs on standby database won’t be able to applied and become out of synchronisation from the primary side. One of my standby database on OCI cloud was not applying archives and later was found that TDE keys files were lost on standby.
On OCI TDE has to be enabled.
1. Login to Primary database and get the wallet path.
SQL>select WRL_PARAMETER from v$encryption_wallet;
2. Check the wallet key files.
ls -lrt /u01/oracle/admin/wallet/testdb01
-rw-------. 1 oracle oninstall 2093 Oct 11 06:12 ewallet.p12
-rw-------. 1 oracle oninstall 1928 Oct 11 06:21 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 walletkeys.zip oracle@standby_Server:/tmp
On Standby Database
1. Go to the Standby data $TNS_ADMIN and add the wallet path.
cd $TNS_ADMIN
vi sqlnet.ora
--add following line
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/oracle/db_home/$ORACLE_SID/wallet/)))
2. Make the directory
mkdir -p /u01/oracle/db_home/$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/walletkeys.zip /u01/oracle/db_home/$ORACLE_SID/wallet
4. Stop Standby recovery the standby database.
sqlplus "/as sysdba"
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
5. Start the standby database
SQL> Startup;
6. Check wallet path it should be Open and Autologin mode.
SQL> select status,wallet_type from v$encryption_wallet;
status wallet_type
--------------------------------
OPEN AUTOLOGIN
7. Prepare the tablespace datafile encryption script.
$ sqlplus / as sysdba
set heading off
set linesize 150
spool tablespace_datafiles_encrypt.sql
select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','TEMP1','TEMP2','APPS_UNDOTS1');
exit
8. Stop the Standby Database
SQL> Shutdown normal;
9. Start the database in mount mode.
SQL> startup mount
10. Run the database encryption script.
SQL> @tablespace_datafiles_encrypt.sql
11. Once Tablespace encrypt script is completed successfully Start the standby recovery.
SQL> alter database recover managed standby database disconnect;
12. Monitor the apply process
SQL> select process, status, thread#, sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
------- ------------ ---------- ----------
MRP0 APPLYING_LOG 1 16972
13. Gap check be checked using below query
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;
Post a Comment
Post a Comment