Configuring TDE in 12.1 RAC database with dataguard enabled

This article demonstrates implementing TDE in a RAC environment with standby database configured. The method used is same as what was done in my previous post, but uses a new feature of 12c called “administer key management”.

 

Environment:
2 node primary RAC: 12cnode1, 12cnode2
2 node standby RAC: 1212drnode1, 1212drnode2
Primary database name: srprim
Standby database name: srpstb

 

Just ensuring before implementing TDE that the standby database is in sync with the primary database.

 

DGMGRL> show configuration

Configuration - dgconfig

  Protection Mode: MaxPerformance
  Members:
  srprim - Primary database
    srpstb - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 6 seconds ago)

 

Create the directory structure where you need to place the wallet files and add the following entry in the SQLNET.ORA file on all the nodes of the primary database. This entry is needed to determine the wallet location.
In my case, I have decided to place the wallet files in the location “/u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORACLE_SID/wallet” and so have I created the same.

 

[oracle@12cnode1 ~]$ mkdir -p /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet
[oracle@12cnode2 ~]$ mkdir -p /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet

 

ENCRYPTION_WALLET_LOCATION = 
 (SOURCE = 
   (METHOD = FILE)
   (METHOD_DATA =
     (DIRECTORY = /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORACLE_SID/wallet)
    )
  )

 

The instances show the default location of the wallet when queried the GV$ENCRYPTION_WALLET view. So, in order to get the instances reflect the right wallet location as per the SQLNET.ORA file, we need to bounce the instances.

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                STATUS          WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ------------------------------------------------------------ --------------- -------------------- --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/admin/srprim/wallet    NOT_AVAILABLE   UNKNOWN              SINGLE    UNDEFINED          0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/admin/srprim/wallet    NOT_AVAILABLE   UNKNOWN              SINGLE    UNDEFINED          0
		 

 

[oracle@12cnode1 ~]$ srvctl stop instance -instance srprim1 -database srprim
[oracle@12cnode1 ~]$ 
[oracle@12cnode1 ~]$ 
[oracle@12cnode1 ~]$ 
[oracle@12cnode1 ~]$ srvctl start instance -instance srprim1 -database srprim

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                        STATUS          WALLET_TYP WALLET_OR FULLY_BAC     CON_ID
---------- ---------- -------------------------------------------------------------------- --------------- ---------- --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/  NOT_AVAILABLE   UNKNOWN    SINGLE    UNDEFINED          0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/admin/srprim/wallet            NOT_AVAILABLE   UNKNOWN    SINGLE    UNDEFINED          0
		 

 

Similarly, restart the second instance as well.

 

[oracle@12cnode1 ~]$ srvctl stop instance -instance srprim2 -database srprim
[oracle@12cnode1 ~]$ 
[oracle@12cnode1 ~]$ 
[oracle@12cnode1 ~]$ srvctl start instance -instance srprim2 -database srprim

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS          WALLET_TYPE  WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- --------------- ------------ --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/    NOT_AVAILABLE   UNKNOWN      SINGLE    UNDEFINED          0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/    NOT_AVAILABLE   UNKNOWN      SINGLE    UNDEFINED          0
		 

 

Now since the WRL_PARAMETER is reflecting the right directory where the wallet needs to be stored, lets move in creating the wallet keystore. Use the “administer key management” clause to create the keystore in the wallet directory location. This needs to be done only on one instance. (In my case, I’m running it on the first instance).

 

SYS@srprim1>administer key management create keystore '/u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/' identified by "oracle123";

keystore altered.

 

Now when you query the gv$encryption_wallet view, you should see that status as CLOSED for the first instance where as the second instance should reflect it as “NOT_AVAILABLE”.

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS          WALLET_TYPE  WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- --------------- ------------ --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/    CLOSED          UNKNOWN      SINGLE    UNDEFINED          0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/    NOT_AVAILABLE   UNKNOWN      SINGLE    UNDEFINED          0

 

Now, open the keystore that was created previously on the instance where it was created.

 

SYS@srprim1>administer key management set keystore open identified by "oracle123"; 

keystore altered.

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS          WALLET_TYPE  WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- --------------- ------------ --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/    OPEN_NO_MASTER_ PASSWORD     SINGLE    UNDEFINED          0
                                                                                             KEY

         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/    NOT_AVAILABLE   UNKNOWN      SINGLE    UNDEFINED          0

 

As we have opened the keystore, the status should reflect as OPEN, but in 12c, you need to have a MASTER KEY as well which is why the STATUS column reflects as “OPEN_NO_MASTER_KEY” where gv$encryption_wallet view is queried.

 

So, create a MASTER KEY using the “administer key management” clause as stated below. The MASTER KEY would be stored in the same location where the wallet file location was specified in the sqlnet.ora file.

 

		 
SYS@srprim1>administer key management set key identified by "oracle123" with backup;

keystore altered.

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS          WALLET_TYPE  WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- --------------- ------------ --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/    OPEN            PASSWORD     SINGLE    NO                 0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/    NOT_AVAILABLE   UNKNOWN      SINGLE    UNDEFINED          0

 

Once the MASTER KEY is created, the status for the corresponding instance turns out to be OPEN when “gv$encryption_wallet” is queried.

 

As seen, the wallet file “ewallet.p12” does also contains the master key. This needs to be copied on to the remaining nodes of the cluster.

 

		 
[oracle@12cnode1 ~]$ ls -lrt /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/
total 8
-rw-r--r--. 1 oracle oinstall 2400 Aug 29 11:46 ewallet_2018082906160156.p12
-rw-r--r--. 1 oracle oinstall 3848 Aug 29 11:46 ewallet.p12
[oracle@12cnode1 ~]$ 
[oracle@12cnode1 ~]$ 
[oracle@12cnode1 ~]$

 

Copying the wallet file to node 12cnode2.

 

 
[oracle@12cnode1 ~]$ cd /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/
[oracle@12cnode1 wallet]$ scp ewallet.p12 oracle@12cnode2:/u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/
ewallet.p12                                                                                                           100% 3848     3.8KB/s   00:00    
[oracle@12cnode1 wallet]$ 

 

Now query gv$encryption_wallet to check the status with respect to each instance. We can see that the status now reflects as “OPEN” for the second instance on 12cnode2 as well.

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS     WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- ---------- --------------- --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/    OPEN       PASSWORD        SINGLE    NO                 0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/    OPEN       PASSWORD        SINGLE    NO                 0

 

Please note that at this point of time, we have setup only manual wallet management which means that when the database/instance is started, we need to open the wallet manually.

 

		 
[oracle@12cnode1 wallet]$ srvctl stop database -database srprim
[oracle@12cnode1 wallet]$ 
[oracle@12cnode1 wallet]$ 
[oracle@12cnode1 wallet]$ srvctl start database -database srprim

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE        WRL_PARAMETER                                                          STATUS     WALLET_TYP WALLET_OR FULLY_BAC     CON_ID
---------- --------------- ---------------------------------------------------------------------- ---------- ---------- --------- --------- ----------
         1 FILE            /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/    CLOSED     UNKNOWN    SINGLE    UNDEFINED          0
         2 FILE            /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/    CLOSED     UNKNOWN    SINGLE    UNDEFINED          0

SYS@srprim1>

 

Now, let me create auto login setup which automatically opens the wallet upon the instance/database retart. This creates a file with name “cwallet.sso” under the same location that is specified in the sqlnet.ora file.

 

SYS@srprim1>administer key management create auto_login keystore from keystore '/u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/' identified by "oracle123";

keystore altered.

 

SYS@srprim1>host
[oracle@12cnode1 wallet]$ ls -lrt /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/
total 12
-rw-r--r--. 1 oracle oinstall 2400 Aug 29 11:46 ewallet_2018082906160156.p12
-rw-r--r--. 1 oracle oinstall 3848 Aug 29 11:46 ewallet.p12
-rw-r--r--. 1 oracle oinstall 3893 Aug 29 12:29 cwallet.sso

 

[oracle@12cnode1 wallet]$ srvctl stop database -d srprim

 

Copy this auto login wallet file (cwallet.sso) on to the second node 12cnode2 as well and then start the database. Upon this, the wallets should be opened automatically for both the instances.

 

[oracle@12cnode1 wallet]$ scp cwallet.sso oracle@12cnode2:/u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/
cwallet.sso                                                                                                           100% 3893     3.8KB/s   00:00    
[oracle@12cnode1 wallet]$ srvctl start database -d srprim

 

SYS@srprim1>select * from gv$encryption_wallet order by inst_id;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS     WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- ---------- --------------- --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/    OPEN       AUTOLOGIN       SINGLE    NO                 0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim2/wallet/    OPEN       AUTOLOGIN       SINGLE    NO                 0

 

With the primary setup, the standby nodes should not be a problem. Perform the same initial steps as did on primary nodes of adding the below entry (speaking about the wallet location) in the sqlnet.ora file on all the nodes of the standby cluster.
Please note that if the directory structure is different, act accordingly in specifiying the appropriate available location.

 

ENCRYPTION_WALLET_LOCATION = 
 (SOURCE = 
   (METHOD = FILE)
   (METHOD_DATA =
     (DIRECTORY = /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORACLE_SID/wallet)
    )
  )

 

  
[oracle@1212drnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/network/admin
[oracle@1212drnode1 admin]$ mkdir -p srpstb1/wallet

 

[oracle@1212drnode2 admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/network/admin
[oracle@1212drnode2 admin]$ mkdir -p srpstb2/wallet

 

Since we haven’t bounced the standby instances after adding the above entry in sqlnet.ora file, the wrl_parameter yet points to the inital location. Let’s copy the wallet files from the primary node and then bounce the standby instances at one go.

 

SQL> select * from gv$encryption_wallet order by 1;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS          WALLET_TYP WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- --------------- ---------- --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/admin/srpstb/wallet              NOT_AVAILABLE   UNKNOWN    SINGLE    UNDEFINED          0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/admin/srpstb/wallet              NOT_AVAILABLE   UNKNOWN    SINGLE    UNDEFINED          0

 

Scp all the wallet files (master key file and autologin file) from any one of the primary node to the first standby node.

 

[oracle@12cnode1 ~]$ cd /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srprim1/wallet/
[oracle@12cnode1 wallet]$ ls -lrt
total 12
-rw-r--r--. 1 oracle oinstall 2400 Aug 29 11:46 ewallet_2018082906160156.p12
-rw-r--r--. 1 oracle oinstall 3848 Aug 29 11:46 ewallet.p12
-rw-r--r--. 1 oracle oinstall 3893 Aug 29 12:29 cwallet.sso

 

[oracle@12cnode1 wallet]$ scp *wallet.* oracle@1212drnode1:/u01/app/oracle/product/12.1.0.2/db_1/network/admin/srpstb1/wallet/
Warning: the RSA host key for '1212drnode1' differs from the key for the IP address '192.168.0.120'
Offending key for IP in /home/oracle/.ssh/known_hosts:14
Matching host key in /home/oracle/.ssh/known_hosts:16
Are you sure you want to continue connecting (yes/no)? yes
oracle@1212drnode1's password: 
cwallet.sso                                                                                                          100% 3893     3.8KB/s   00:00    
ewallet.p12                                                                                                          100% 3848     3.8KB/s   00:00    
[oracle@12cnode1 wallet]$ 

 

Similarly scp the same wallet files from the primary node to the second node of the standby cluster as well.

 

[oracle@12cnode1 wallet]$ scp *wallet.* oracle@1212drnode2:/u01/app/oracle/product/12.1.0.2/db_1/network/admin/srpstb2/wallet/
The authenticity of host '1212drnode2 (192.168.0.127)' can't be established.
RSA key fingerprint is 0a:f2:b7:22:3b:2b:fa:32:ea:dc:17:c1:22:7c:4e:e3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '1212drnode2,192.168.0.127' (RSA) to the list of known hosts.
oracle@1212drnode2's password: 
cwallet.sso                                                                                                          100% 3893     3.8KB/s   00:00    
ewallet.p12                                                                                                          100% 3848     3.8KB/s   00:00    
[oracle@12cnode1 wallet]$ 

 

[oracle@1212drnode1 ~]$ srvctl status database -d srpstb -v -f
Instance srpstb1 is running on node 1212drnode1. Instance status: Mounted (Closed).
Instance srpstb2 is running on node 1212drnode2. Instance status: Mounted (Closed).
[oracle@1212drnode1 ~]$ 
[oracle@1212drnode1 ~]$ 

 

Now its time to bounce the standby database and check the wallet status.

 

[oracle@1212drnode1 ~]$ srvctl stop database -d srpstb
[oracle@1212drnode1 ~]$ 
[oracle@1212drnode1 ~]$ 
[oracle@1212drnode1 ~]$ srvctl start database -d srpstb
[oracle@1212drnode1 ~]$ srvctl status database -d srpstb -v -f
Instance srpstb1 is running on node 1212drnode1. Instance status: Mounted (Closed).
Instance srpstb2 is running on node 1212drnode2. Instance status: Mounted (Closed).
[oracle@1212drnode1 ~]$ 

 

Glad to see that the wallets are opened on both the standby nodes and that the wallet type represents as “AUTOLOGIN”.

 

[oracle@1212drnode1 ~]$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 8 12:14:18 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> set linesize 300
SQL> col wrl_type for a10
SQL> col wallet_type for a10
SQL> col status for a15
SQL> col wrl_parameter for a70
SQL> select * from gv$encryption_wallet order by 1;

   INST_ID WRL_TYPE   WRL_PARAMETER                                                          STATUS          WALLET_TYP WALLET_OR FULLY_BAC     CON_ID
---------- ---------- ---------------------------------------------------------------------- --------------- ---------- --------- --------- ----------
         1 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srpstb1/wallet/    OPEN            AUTOLOGIN  SINGLE    NO                 0
         2 FILE       /u01/app/oracle/product/12.1.0.2/db_1/network/admin/srpstb2/wallet/    OPEN            AUTOLOGIN  SINGLE    NO                 0

SQL> 

 

Finally, ensure that the standby database is in sync with the primary database.

 

[oracle@12cnode1 u02]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - dgconfig

  Protection Mode: MaxPerformance
  Members:
  srprim - Primary database
    srpstb - 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