By Mouhamadou Diaw

Setting up a DatagGard environment for a database with Transparent Data Encryption requires some tasks concerning the encryption keys. Otherwise the steps are the same than for an environment without TDE.
In this blog we will present the tasks we have to do on both primary and standby servers for the keys. We will not describe the procedure to build the standby database. We will just talk about tasks for the wallet and we will verify that data for encrypted tables are being replicated.
We are using oracle 12.2 and a non-container database.
Tasks on primary side
First on the primary server we have to configure the keystore location. This will be done by updating the sqlnet.ora with the directory whch will contain the keys.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[oracle@primaserver ~]$mkdir /u01/app/wallet
[oracle@primaserver admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
# For TDE
ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=file)
   (METHOD_DATA=
    (DIRECTORY=/u01/app/wallet)))
[oracle@primaserver admin]$

After on the primary we have to create the keystore.

1
2
3
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/wallet' identified by root ;
keystore altered.

Next we have to open the keystore before creating the master key

1
2
3
SQL> ADMINISTER KEY MANAGEMENT set KEYSTORE open   identified by root ;
keystore altered.

And then we can create the master key.

1
2
3
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY root WITH BACKUP;
keystore altered.

The wallet should be open before we can access to encrypted objects. So every time the database starts up, we have to manually open the wallet. To avoid this we can just create an auto_login wallet which will automatically opened at each database startup.

1
2
3
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/wallet' identified by root;
keystore altered.

Tasks on standby side
On the standby side we just have to copy files in the wallet and to update the sqlnet.ora file.

1
2
3
4
5
6
7
8
9
[oracle@primaserver wallet]$ pwd
/u01/app/wallet
[oracle@primaserver wallet]$ ls
cwallet.sso  ewallet_2018062707462646.p12  ewallet.p12
[oracle@primaserver wallet]$ scp * standserver1:$PWD
oracle@standserver1's password:
cwallet.sso                                   100% 3891     3.8KB/s   00:00
ewallet_2018062707462646.p12                  100% 2400     2.3KB/s   00:00
ewallet.p12                                  

And that’s all. We can now configure our standby database. Below our configuration

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DGMGRL> show configuration;
Configuration - DGTDE
  Protection Mode: MaxPerformance
  Members:
  DGTDE_SITE1 - Primary database
    DGTDE_SITE2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 1 second ago)
DGMGRL>

Now let’s verify that encrypted data are being replicated. We have a table with an encrypted column

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> show user
USER is "SCOTT"
SQL> desc TEST_COL_ENC
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 DESIGNATION                                        VARCHAR2(30) ENCRYPT
SQL> select * from TEST_COL_ENC;
        ID DESIGNATION
---------- ------------------------------
         1 toto
         2 tito
         3 tata
SQL>

And let’s insert some data form the primary

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> insert into TEST_COL_ENC values (4,'titi');
1 row created.
SQL> insert into TEST_COL_ENC values (5,'teti');
1 row created.
SQL> commit;
Commit complete.
SQL>

From the standby let’s query the table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> select db_unique_name,open_mode from v$database;
DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
DGTDE_SITE2                    READ ONLY WITH APPLY
SQL> select * from scott.TEST_COL_ENC;
        ID DESIGNATION
---------- ------------------------------
         4 titi
         5 teti
         1 toto
         2 tito
         3 tata
SQL>

To finish we will remind following notes about DataGuard and TDE (Oracle Documentation)

The database encryption wallet on a physical standby database must be replaced with a fresh copy of the database encryption wallet from the primary database whenever the TDE master encryption key is reset on the primary database.

For online tablespaces and databases, as of Oracle Database 12c Release 2 (12.2.0.1), you can encrypt, decrypt, and re-key both new and existing tablespaces, and existing databases within an Oracle Data Guard environment. This tasks will be automatically performed on the standby once done on the primary. Note that these online tasks cannot be done directly on the standby side.

In an offline conversion, the encryption or decryption must be performed manually on both the primary and standby. An offline conversion affects the data files on the particular primary or standby database only. Both the primary and physical standby should be kept at the same state. You can minimize downtime by encrypting (or decrypting) the tablespaces on the standby first, switching over to the primary, and then encrypting (or decrypting) the tablespaces on the primary.

Post Views: 2,603
Thumbnail [60x60]
by
Oracle Team