Configuring TDE in 11.2 RAC database with dataguard enabled
In this post, I’m writing on how to implement TDE in a 11g RAC environment with Dataguard
Environment: Oracle RDBMS version: 11.2.0.3 Primary nodes: node1, node2 Standby nodes: drnode1, drnode2 Primary Databaase: crmsdb Standby Database: crmdb
Firstly, let me check if the standby database is in sync with the primary database.
[oracle@node1]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - crmdb_dg
Protection Mode: MaxPerformance
Databases:
crmsdb - Primary database
crmdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Now, add the below entry in the SQLNET.ORA file on all the nodes of the Primary database. The SQLNET.ORA file needs to be present in the location where the TNS_ADMIN points.
The value for the DIRECTORY parameter in the below entry specifies the location where the wallet needs to be stored. In my case, I’m placing it ata “$ORACLE_HOME/network/admin//wallet”. If the specified directory structure does not exist, then you need to create one.
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA=(DIRECTORY=/u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet)) )
Please note that in a RAC environment, the wallet can be placed in a shared location where all the nodes of the cluster database can access it (Ideally, it can be placed on ASM storage which I shall expalin in my coming posts). In this environment, I’m having a separate wallet for each node.
Once you add the entry in SQLNET.ORA file, you need to bounce the instances so that wallet location is upated correctly when you query the gv$encryption_wallet view.
As of now, I have not bounced the 2nd instance and due to which the wallet location is pointing to wrong location when I query the gv$encryption_wallet view.
Hence a restart of the 2nd instance is needed.
SQL> select * from gv$encryption_wallet order by inst_id;
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- --------------- ---------------------------------------------------------------------- ------------------
1 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet CLOSED
2 file /u01/app/oracle/product/11.2.0.3/db_1/admin/crmsdb/wallet CLOSED
[oracle@node1 admin]$ srvctl stop instance -i crmsdb2 -d crmsdb [oracle@node1 admin]$ [oracle@node1 admin]$ srvctl start instance -i crmsdb2 -d crmsdb [oracle@node1 admin]$
Now, querying the gv$encryption_wallet provides the expected result.
SQL> select * from gv$encryption_wallet order by inst_id;
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- --------------- ---------------------------------------------------------------------- ------------------
1 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet CLOSED
2 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet CLOSED
On any one instance of the primary database, create the wallet. Here, I’m running it on the first node of the primary database.
SQL> alter system set encryption key identified by "oracle123"; System altered.
Just crosscheck the wallet file presence under the wallet location.
[oracle@node1 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmsdb1/wallet [oracle@node1 wallet]$ ls -lrt total 4 -rw-r--r--. 1 oracle oinstall 1573 Aug 25 13:41 ewallet.p12
Now, SCP the wallet file
[oracle@node1 wallet]$ scp ewallet.p12 oracle@node2:/u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmsdb2/wallet/ ewallet.p12 100% 1573 1.5KB/s 00:00 [oracle@node1 wallet]$
You now manually need to open the wallet
SQL> alter system set encryption wallet open identified by "oracle123"; System altered.
SQL> select * from gv$encryption_wallet order by 1;
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
1 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet OPEN
2 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet OPEN
In order to close the wallet manually, you can use the below command.
SQL> alter system set encryption wallet close identified by "oracle123"; System altered.
It is recommended to enable the wallet for auto login. This will ensure that there is no need to open or close the wallet manually. Oracle will automatically open the wallet when an encrypted object is accessed or created. This can be done by using the “orapki” utility.
[oracle@node1 wallet]$ orapki wallet create -wallet /u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmsdb1/wallet -auto_login Oracle PKI Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: [oracle@node1 wallet]$
When prompted for the wallet password, provide the password that was used previously to create the key.
[oracle@node1 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmsdb1/wallet/ [oracle@node1 wallet]$ ls -lrt total 8 -rw-r--r--. 1 oracle oinstall 1573 Aug 25 13:41 ewallet.p12 -rw-------. 1 oracle oinstall 1651 Aug 25 13:47 cwallet.sso
[oracle@node1 wallet]$ scp cwallet.sso oracle@node2:/u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmsdb2/wallet/ cwallet.sso 100% 1651 1.6KB/s 00:00
Now, bouncing the database will not stop you for opening the wallet as the above autologin option opens it automatically.
[oracle@node1 ~]$ srvctl stop database -d crmsdb [oracle@node1 ~]$ [oracle@node1 ~]$ [oracle@node1 ~]$ srvctl start database -d crmsdb
[oracle@node1 ~]$ sql
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 8 10:55:31 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set linesize 300
SQL> col wrl_parameter for a70
SQL> select * from gv$encryption_wallet order by 1;
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
1 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet OPEN
2 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet OPEN
Now on the DR nodes, follow the same steps as done on the primary nodes.
Add same entry as above in sqlnet.ora file on all nodes of the standby and then restart the instances to get the wallet_location updated.
[oracle@drnode1 admin]$ srvctl stop instance -i crmdb1 -d crmdb [oracle@drnode1 admin]$ srvctl start instance -i crmdb1 -d crmdb
[oracle@drnode1 admin]$ srvctl stop instance -i crmdb2 -d crmdb [oracle@drnode1 admin]$ srvctl start instance -i crmdb2 -d crmdb [oracle@drnode1 admin]$
Once restarted, query the gv$encryption_wallet view to verify if WRL_PARAMETER value corresponds to that mentioned in the WALLET_LOCATION value in the SQLNET.ORA file.
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- --------------- ---------------------------------------------------------------------- ------------------
1 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet CLOSED
2 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet CLOSED
On the standby, MRP may terminate
Errors with log +FRA/crmdb/archivelog/2018_08_25/thread_1_seq_64.376.985096395 MRP0: Background Media Recovery terminated with error 28365 Errors in file /u01/app/oracle/diag/rdbms/crmdb/crmdb1/trace/crmdb1_mrp0_12241.trc: ORA-28365: wallet is not open Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 1361738 MRP0: Background Media Recovery process shutdown (crmdb1)
Now, do not create any key on the standby database.
[oracle@node1 wallet]$ scp ewallet.p12 oracle@drnode1:/u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmdb1/wallet/ oracle@drnode1's password: ewallet.p12 100% 1573 1.5KB/s 00:00 [oracle@node1 wallet]$ [oracle@node1 wallet]$ [oracle@node1 wallet]$ scp ewallet.p12 oracle@drnode2:/u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmdb2/wallet/ The authenticity of host 'drnode2 (192.168.0.123)' can't be established. RSA key fingerprint is 48:83:c6:32:28:0d:25:a8:5a:25:03:c7:21:96:6e:64. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'drnode2,192.168.0.123' (RSA) to the list of known hosts. oracle@drnode2's password: ewallet.p12 100% 1573 1.5KB/s 00:00 [oracle@node1 wallet]$
You can now open the wallet manually on the standby database using the below command:
SQL> alter system set encryption wallet open identified by "oracle123"; System altered.
But since I have enabled autologin on the primary database, I’d like the same to be enabled on the standby as well. So shall copy the cwallet.sso file from primary to both the nodes of the standby.
[oracle@node1 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmsdb1/wallet/ [oracle@node1 wallet]$ ls -lrt total 8 -rw-r--r--. 1 oracle oinstall 1573 Aug 25 13:41 ewallet.p12 -rw-------. 1 oracle oinstall 1651 Aug 25 13:47 cwallet.sso [oracle@node1 wallet]$ [oracle@node1 wallet]$ scp cwallet.sso oracle@drnode1:/u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmdb1/wallet/ oracle@drnode1's password: cwallet.sso 100% 1651 1.6KB/s 00:00 [oracle@node1 wallet]$ [oracle@node1 wallet]$ scp cwallet.sso oracle@drnode2:/u01/app/oracle/product/11.2.0.3/db_1/network/admin/crmdb2/wallet/ oracle@drnode2's password: cwallet.sso 100% 1651 1.6KB/s 00:00 [oracle@node1 wallet]$
[oracle@drnode1 ~]$ srvctl stop database -d crmdb [oracle@drnode1 ~]$ [oracle@drnode1 ~]$ srvctl start database -d crmdb
[oracle@drnode1 ~]$ sql
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 8 11:17:50 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> col wrl_parameter for a70
SQL> set linesize 300
SQL> select * from gv$encryption_wallet order by 1;
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
1 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet OPEN
2 file /u01/app/oracle/product/11.2.0.3/db_1/network/admin/$ORACLE_SID/wallet OPEN
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED crmdb1 PHYSICAL STANDBY
SQL> select process,status,sequence#,inst_id,thread# from gV$managed_standby; PROCESS STATUS SEQUENCE# INST_ID THREAD# --------- ------------ ---------- ---------- ---------- ARCH CONNECTED 0 1 0 ARCH CONNECTED 0 1 0 ARCH CONNECTED 0 1 0 ARCH CONNECTED 0 1 0 MRP0 APPLYING_LOG 71 1 2 ARCH CLOSING 87 2 1 ARCH CLOSING 70 2 2 ARCH CONNECTED 0 2 0 ARCH CONNECTED 0 2 0 RFS IDLE 0 2 0 RFS IDLE 0 2 0 PROCESS STATUS SEQUENCE# INST_ID THREAD# --------- ------------ ---------- ---------- ---------- RFS IDLE 88 2 1 RFS IDLE 0 2 0 RFS IDLE 71 2 2 RFS IDLE 0 2 0 15 rows selected.
[oracle@drnode1 ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - crmdb_dg
Protection Mode: MaxPerformance
Databases:
crmsdb - Primary database
crmdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organisation. The views expressed by visitors on this blog are theirs solely and may not reflect mine
Shivananda Rao 
Leave a comment