Advertisements
Skip to content
September 19, 2018 / Shivananda Rao P

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

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: