Posted on ACFS, Oracle, TDE

TDE SETUP in a rac environment:

Summary:

This document will share the steps to implement TDE (transparent Database encryption) in the database in a Cluster.  Required Steps that are needed to implement it:

  • have the requirement to have an ACFS file system in place with proper setup there (see below).
  • Have the requirement to have an adapted sqlnet.ora in a centralized location.

Details:

  1. Setup will require preparations. In our case, the first database was MYDB. Steps below will be identical for each database in scope. (Of course you need to change the database name according to the database in your scope).

Mysrv[3-4]dr is holding the MYDB database.

  • Create a Diskgroup in normal Redundancy and call it TDE_KEYS.
  • Prepare the acfs created mountpoint by creating a TDE_VOL.
  • Mount the TDE_VOL as /app/oracle/admin/WALLET
  • On the acfs mount (/app/oracle/admin/WALLET
  • cd /app/oracle/admin/WALLET

mkdir MYDB,

ln -sf MYDB MYDB <1,2>

This acfs filesystem is shared between the clusternode.

With the mkdir you wil create an entry for  the database you are about to encrypt , with the 2 links you will point both instances to the database directory , to keep all in a central place.

  • Make sure you have a centralized Sqlnet.ora and adapt this one. As you can see the ORACLE_SID ( the instance name offers a flexible input in this way):

ENCRYPTION_WALLET_LOCATION =

  (SOURCE =(METHOD = FILE)(METHOD_DATA =

    (DIRECTORY = /app/oracle/admin/WALLET/$ORACLE_SID/ )

                          )

  )

  • In the database first check:

SQL> select * from v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

NOT_AVAILABLE                                    UNKNOWN                    SINGLE    UNDEFINED

                 0

  • With the below command you will create the keystore  and you will give the keystore a password.

SQL> administer key management create keystore ‘/app/oracle/admin/WALLET/MYDB/’ identified by “mypwd19!”;

## Once that command is given Sqlplus  will report:

keystore altered.

## TIP Be sure the correct sqlnet is read (links?)

  • run below command to open your keystore:

SQL> administer key management set keystore open identified by “mypwd19!”;

## this will report in sqlplus:

keystore altered.

  1. Now is a good time to Check the status of your wallet:

SQL> select * from v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

OPEN_NO_MASTER_KEY                    PASSWORD                    SINGLE    UNDEFINED

                 0

  1.  Check  your encryption keys.

SQL> select key_id,activation_time from v$encryption_keys;

## sql will 1st time report:

no rows selected

## On Os you should already see the wallet similar to below:

SQL> host

oracle@Mysrv3dr:/app/oracle/admin/WALLET/MYDB []# ls -lisa

77 4 -rw——-. 1 oracle dba 2555 Sep 13 05:51 ewallet.p12

  1. In Sqlplus  now it is time to create the key:

SQL> administer key management create key identified by “mypwd19!” with backup;

  1. When you check your encryption keys again now:

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID

——————————————————————————

ACTIVATION_TIME

—————————————————————————

NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

  1. In sqlplus give below command:

SQL> administer key management use key ‘ NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‘ identified by “mypwd19!” with backup;

## sqlplus will report:

keystore altered.

  1. Check again your encryption keys again to get something similar to below:

SQL> select key_id,activation_time from v$encryption_keys;

## now you will see an activation_time Too:

KEY_ID

——————————————————————————

ACTIVATION_TIME

—————————————————————————

NcC1701D+mbkK+6v92xdM/qIxcXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

13-SEP-19 06.01.49.995963 AM +02:00

  1. As a test create a small  tablspace:

SQL> create tablespace ENC datafile ‘+MYDB_DATA’ size 10M encryption using ‘AES256’ default storage(encrypt);

## Check this with:

select tablespace_name,encrypted from dba_tablespaces order by 1;

## Sqlplus should report something like this:

SQL> select tablespace_name,encrypted from dba_tablespaces order by 1;

TABLESPACE_NAME                ENC

—————————— —

ENC                            YES

Now you are all set to create all required application tablespaces with these extra parameters  encryption using ‘AES256’ default storage(encrypt).

Automatically open the wallet

Note:   Basically a wallet needs to be open before you can access the database. This however can and should be altered: When opening your database this should automatically open the wallet:

  1. In sqlplus check:

SQL> select * from v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

OPEN                                           PASSWORD                    SINGLE    NO

                 0

  • Give  this command:

SQL> administer key management create auto_login keystore from keystore ‘/app/oracle/admin/WALLET/MYDB/’ identified by “mypwd19!”;

## sqlplus will report:

keystore altered.

## On OS you will see :

oracle@Mysrv3dr:/app/oracle/admin/WALLET/MYDB [MYDB1]# ls -lisa

total 48

74  4 drwxr-xr-x. 2 oracle dba 4096 Sep 13 06:37 .

 2  4 drwxrwxr-x. 5 oracle dba 4096 Sep 13 05:44 ..

82 12 -rw——-. 1 oracle dba 5304 Sep 13 06:37 cwallet.sso

78  4 -rw——-. 1 oracle dba 2555 Sep 13 05:55 ewallet_2019091303553484.p12

80  4 -rw——-. 1 oracle dba 3803 Sep 13 05:57 ewallet_2019091303572233.p12

81  8 -rw——-. 1 oracle dba 5067 Sep 13 06:01 ewallet_2019091304014993.p12

77 12 -rw——-. 1 oracle dba 5259 Sep 13 06:01 ewallet.p12

  • And in Sqlplus  too you  will see  a change.

SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE

——————–

WRL_PARAMETER

——————————————————————————–

STATUS                                       WALLET_TYPE               WALLET_OR FULLY_BAC

—————————— ——————– ——— ———

    CON_ID

———-

FILE

/app/oracle/admin/WALLET/MYDB1/

OPEN                                           AUTOLOGIN                  SINGLE    NO

                 0

Appendix 1 Create an acfs file system:

Idea was to setup acfs via asmca. This however did not work in first implementation due to error because ora.proxy_advm is offline on some nodes.

It should be:

crsctl stat res ora.proxy_advm -t

——————————————————————————–

Name           Target  State        Server                   State details      

——————————————————————————–

Local Resources

——————————————————————————–

ora.proxy_advm

               ONLINE  ONLINE       Mysrv3dr                 STABLE

               ONLINE  ONLINE       Mysrv4dr                 STABLE

If the status is offline check if the acfsmodule are loaded: lsmod | grep acfs

oracleacfs           4840664  2

oracleoks             663240  2 oracleacfs,oracleadvm

if they are not loaded on the local node: log in as root and perform

$GRID_HOME/bin/csrctl stop crs

$GRID_HOME/bin/acfstool install

$GRID_HOME/bin/crsctl start crs

Modules should be loaded now. Volume can be created in asmca (high protection, TDE_VOL1, 1GB)

The volume will show up under /dev/asm (/dev/asm/tde_vol1-301 in this case).

Mkdir  -p /app/oracle/admin/wallet

Cd /app/oracle

Chown –R oracle:dba admin

/sbin/mkfs -t acfs /dev/asm/tde_vol1-302

Register in clusterware:

/sbin/acfsutil registry -a /dev/asm/tde_vol1-302 /app/oracle/admin/WALLET

Leave a comment