LATEST TOPICS

SharePlex: Integrate TDE with SharePlex replication

Transparent Data Encryption (TDE) is a Oracle database feature which was first introduced with Oracle 10g Release 2 as a OUT-OF-PLACE method to encrypt data at the storage level. By default, SharePlex is not capable of replicating data which are encrypted with TDE and we need to perform few additional configuration to be able to replicate the data encrypted by TDE.

In this post, I will cover the configuration steps required to enable SharePlex replicate the data encrypted by TDE. Today’s discussion assumes that you already have configured TDE at the database level. I would be using Oracle 12c database and SharePlex release 8.6.2 for the purpose of this discussion. If you are interested in learning about TDE in Oracle 12c, you can refer my article here.

TDE support was first introduced with SharePlex release 7.6.0. Over the time, there were several improvements made to this SharePlex feature and the way we configure it. When this feature was first introduced in SharePlex, we had to create separate shared key (using mkstore utility) to allow SharePlex access the encrypted data. However, in SharePlex release 8.5; this requirement has changed and we are no longer required to create a separate shared key for SharePlex. SharePlex is able to leverage the TDE master key to read the encrypted data in a decrypted mode.

We use the SharePlex’s ora_setup tool for enabling TDE support. While running ora_setup, it will prompt for the following information related to TDE configuration

##---
##--- ora_setup prompts related to TDE configuration ---##
##---
Do you want to enable replication of tables with TDE? [n] : 

Enter the fully qualified pathname of the TDE wallet file [] : 

The first question is to confirm, if we want to enable SharePlex replication of tables with TDE. If we answer as yes [y], then we need to provide the full pathname of the TDE wallet file (which was created as part of TDE configuration) as the next input.

Let’s quickly go through a demonstration to check how it works. In the following demonstration, I am enabling SharePlex’s TDE support for source database cdb1_pdb_1 which is a pluggable database and will replicate the data to target pluggable database cdb2_pdb_1. My TDE wallet file is /data/oracle/orpcdb1/wallet/ewallet.p12, which I will pass during ora_setup.

##---
##--- running ora_setup to enable TDE replication ---##
##---
[oracle@labserver1 bin]$ ./ora_setup


Welcome to the Oracle SharePlex setup process for port 2015.
This process creates tables and user accounts needed to run
Oracle SharePlex replication.

Will the SharePlex install be using a BEQUEATH connection? (Entering 'n' implies a SQL*net connection)  [y] : n
Please note the following:
** In response to prompts, a carriage return will choose the default
   given in brackets.  If there is no default, a reply must be entered.

** To exit the program while the program is waiting for input, use the
   CTRL-C key sequence.
   This sequences can be entered by holding down the CONTROL key and
   pressing the C key.


Enter the TNS alias for which SharePlex should be installed [orpcdb1] : cdb1_pdb_1

Verifying TNS supplied ... done.

In order to create the SharePlex tables and user account, we must
connect to the database as a DBA user

Enter a DBA user for cdb1_pdb_1  : system
NOTE: Appending the tns alias to the password via @ sign is not needed.

Enter password for the DBA account, which will not echo :

connecting--This may take a few seconds.

validating user name and password. . . This may take a few seconds.
SharePlex objects will need to be created under a special
account.  You can pick an existing user or create a new one.

Current SharePlex user is : SPLEX

Warning: Changing SharePlex user requires
reactivating the current configuration.
Would you like to create a new SharePlex user ? [y] : y
NOTE: Appending the tns alias to the password via @ sign is not needed.
Enter username for new user [splex/splex] : splex

Enter password for new user :

Re-enter password for new user :

Warning:  This user is now being granted unlimited tablespace.
This privilege will remain in effect until it is explicitly changed.

Do you want to enable replication of tables with TDE? [n] : y       ## Answer as 'y' to enable TDE replication

Enter the fully qualified pathname of the TDE wallet file [] : /data/oracle/orpcdb1/wallet/ewallet.p12  ## Provide full path of the TDE wallet file

Granting SELECT privilege on SYS.ENC$ to user splex ...OK.

Setup will now install SharePlex objects.

These are the existing tablespaces.

SYSTEM SYSAUX TEMP USERS EXAMPLE_TS MYAPP_TS SPLEX_TS MYAPP_TS_ENC


Enter the default tablespace for use by SharePlex [USERS] : SPLEX_TS

Enter the temporary tablespace for use by SharePlex [TEMP] :
Enter the index tablespace for use by SharePlex [ ] : SPLEX_TS

Creating SharePlex objects [Installation type: Fresh]. . .

Creating SharePlex Oracle-timezone-region map . . . Done.

Creating Conflict Resolution Package . . . Done.

Creating SharePlex Dataequator package . . .

Loading Compare Package from "/app/shareplex/8.6.2/.app-modules/../util/sp_deq_pkg.plb"...Done.

Spatial datatype was not installed - skip compare varray package

Will the current setup for sid: [cdb1_pdb_1] be used as source (including cases as source for failover or master-master setups)?  [y] :

Setup of SharePlex objects successful . . .

Setup completed successfully

Once we run the ora_setup, SharePlex is now ready to replicate the TDE encrypted data. Let’s start our SharePlex instance and examine, if it is able to replicate the encrypted data.

##---
##--- Starting SharePlex instance after enabling TDE replication ---##
##---
[oracle@labserver1 ~]$ $SP_HOME/bin/sp_cop -u${SP_COP_TPORT} &
[1] 6988
[oracle@labserver1 ~]$

*******************************************************
* SharePlex for Oracle Startup
* Copyright 2014 Dell, Inc.
* ALL RIGHTS RESERVED.
* Protected by U.S. Patents: 7,461,103 and 7,065,538
* Version: 8.6.2.43-m64-oracle120
* VarDir : /app/shareplex/var/cdb1_2015
* Port   : 2015
*******************************************************

  ***  To enable TDE replication, run sp_wallet and provide the wallet password ***

Although SharePlex instance is started, there is a important message (*** To enable TDE replication, run sp_wallet and provide the wallet password ***) displayed during the SharePlex instance startup. This means that we need to run another SharePlex tool namely sp_wallet once we startup a SharePlex instance which is configured for TDE replication.

If we look into the SharePlex event_log file, we could see following warning message being logged in the event_log.

  
##---
##--- warning in event_log related to sp_wallet ---##
##---
Warning  2016-06-04 13:07:45.799789 6187 3646334800 Run sp_wallet - the wallet password is required to replicate encrypted data
Info     2016-06-04 13:07:45.800012 6187 3646334800 SharePlex was started on cpu 20095 using port 2015 version 8.6.2.43-m64-oracle120
Info     2016-06-04 13:07:45.800693 6188 3646334800 Capture launched, pid = 6188  (capturing from cdb1_pdb_1)

Before we run the sp_wallet tool to enable SharePlex reading encrypted data, let’s find out what happens if we don’t run the sp_wallet tool. I have the following encrypted tables participating in SharePlex replication.

##---
##--- tables participating in SharePlex replication ---##
##---
sp_ctrl (labserver1:2015)> show config

Tables Replicating with Key:

  "MYAPP"."T_MYAPP_USERS"   KEY: USER_ID
  "MYAPP"."T_MYAPP_PRODUCTS"   KEY: PROD_ID
  "MYAPP"."T_MYAPP_ORDERS"   KEY: ORDER_ID

File Name  :cdb1_pdb1_sp.conf
Datasource :cdb1_pdb_1
Activated  :30-May-16 16:18:09
Actid      :6

Total Objects                 :3
Total Objects Replicating     :3
Total Objects Not Replicating :0

View config summary in /app/shareplex/var/cdb1_2015/log/cdb1_pdb_1_config_log

Let’s insert few records in the source encrypted table T_MYAPP_USERS and examine if it gets replicated to target database cdb2_pdb_1.

---//
---// insert few records in source encrypted table //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

SQL> select * from T_MYAPP_USERS;

   USER_ID USER_NAME       JOIN_DATE
---------- --------------- ----------------------------------
         1 Alex            02-APR-16 11.43.38.729821 AM


SQL> insert into MYAPP.T_MYAPP_USERS values (&id,&name,sysdate);
Enter value for id: 2
Enter value for name: 'Abbas'
old   1: insert into MYAPP.T_MYAPP_USERS values (&id,&name,sysdate)
new   1: insert into MYAPP.T_MYAPP_USERS values (2,'Abbas',sysdate)

1 row created.

SQL> /
Enter value for id: 3
Enter value for name: 'Fazal'
old   1: insert into MYAPP.T_MYAPP_USERS values (&id,&name,sysdate)
new   1: insert into MYAPP.T_MYAPP_USERS values (3,'Fazal',sysdate)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from T_MYAPP_USERS;

   USER_ID USER_NAME       JOIN_DATE
---------- --------------- ------------------------------------
         1 Alex            02-APR-16 11.43.38.729821 AM
         2 Abbas           04-JUN-16 01.10.17.000000 PM
         3 Fazal           04-JUN-16 01.10.24.000000 PM
         

We have inserted couple of records in the source encrypted table T_MYAPP_USERS. Let’s check, if these records are replicated to the target database cdb2_pdb_1.

---//
---// validate if records are replicated to target //---
---//         
SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB_1
SQL>  select * from T_MYAPP_USERS;

   USER_ID USER_NAME       JOIN_DATE
---------- --------------- ---------------------------------------------------------------------------
         1 Alex            02-APR-16 11.43.38.729821 AM

As expected, the records from the encrypted source table (T_MYAPP_USERS) are not replicated to target database cdb2_pdb_1. Let’s check the SharePlex state on source system to validate if it was able to capture/read the encrypted data.

##---
##--- capture status on source system ---##
##---
sp_ctrl (labserver1:2015)> show

Process    Source                               Target                 State                   PID
---------- ------------------------------------ ---------------------- -------------------- ------
Capture    o.cdb1_pdb_1                                                Running                6188
Export     labserver1                           labserver2             Running                6190
Read       o.cdb1_pdb_1                                                Running                6189

In the source system, the Capture process is running fine and didn’t through any errors. There are even no errors reported in the source system event_log file. Let’s check the detailed status of the Capture process to see if we can find any relevant information.

##---
##--- detailed Capture status from source system ---##
##---
sp_ctrl (labserver1:2015)> show capture detail

Host: labserver1.oraclebuffer.com
                           Operations
Source     Status            Captured Since
---------- --------------- ---------- ------------------
o.cdb1_pdb Running                  0 04-Jun-16 13:07:45

   Oracle current redo log          : 754
   Capture current redo log         : 0
   Capture log offset               : 0
   Last redo record processed:
        None

   Capture state                    : Waiting for wallet to be loaded
   Activation id                    : 0
   Error count                      : 0
   Operations captured              : 0
   Transactions captured            : 0

   Concurrent sessions              : 0
   HWM concurrent sessions          : 0
   Checkpoints performed            : 0
   Total operations processed       : 0
   Total transactions completed     : 0
   Total Kbytes read                : 0

   Redo records in progress         : 0
   Redo records processed           : 0
   Redo records ignored             : 0
   Redo records - last HRID         : N/A

Notice the Capture state, it is showing as Waiting for wallet to be loaded. Further, we can see the Capture current redo log is showing as zero (0) which means, Capture process is not able to extract the redo data as it doesn’t have the TDE master key to decrypt the encrypted data.

Let’s run the sp_wallet tool for source SharePlex instance to allow SharePlex read the encrypted data in decrypt mode. We need to provide the Oracle TDE encryption master key, when prompted by the sp_wallet tool.

  
##---
##--- run sp_wallet for source SharePlex instance ---##
##---
[oracle@labserver1 ~]$ $SP_HOME/bin/sp_wallet

wallet password:

Wallet loaded into SharePlex

[oracle@labserver1 ~]$

Notice the message (Wallet loaded into SharePlex) returned by the sp_wallet tool. It is indicating that the TDE wallet information is now loaded in to SharePlex, which means SharePlex now has the TDE master key and will be able to read the encrypted data in a decrypted mode. If we look into the event_log file of the source system, we can observe the following information being logged in the event_log file after executing the sp_wallet tool.

##---
##--- execution of sp_wallet enabled TDE replication in SharePlex ---##
##---
Info     2016-06-04 13:16:55.900667 6392 2832541520 SharePlex enabled for TDE replication
Notice   2016-06-04 13:16:55.901519 6188 4050536272 Capture: TDE support is enabled  (capturing from cdb1_pdb_1) [module oct]

Now, if we look into the Capture status, we could see Capture is no longer waiting for the wallet to be loaded and is capturing the redo data as shown below.

  
##---
##--- Capture no longer waiting for wallet to be loaded ---##
##---

sp_ctrl (labserver1:2015)> show

Process    Source                               Target                 State                   PID
---------- ------------------------------------ ---------------------- -------------------- ------
Capture    o.cdb1_pdb_1                                                Running                6188
Export     labserver1                           labserver2             Running                6190
Read       o.cdb1_pdb_1                                                Running                6189

sp_ctrl (labserver1:2015)> show capture detail

Host: labserver1.oraclebuffer.com
                           Operations
Source     Status            Captured Since
---------- --------------- ---------- ------------------
o.cdb1_pdb Running                  3 04-Jun-16 13:07:45

   Oracle current redo log          : 754
   Capture current redo log         : 754
   Capture log offset               : 47793276
   Last redo record processed:
        Operation on SHAREPLEX internal table at 06/04/16 13:18:51

   Capture state                    : Processing
   Activation id                    : 6
   Error count                      : 0
   Operations captured              : 3
   Transactions captured            : 1

   Concurrent sessions              : 0
   HWM concurrent sessions          : 1
   Checkpoints performed            : 0
   Total operations processed       : 126
   Total transactions completed     : 124
   Total Kbytes read                : 46673

   Redo records in progress         : 0
   Redo records processed           : 2481
   Redo records ignored             : 2355
   Redo records - last HRID         : AAAVLkAA7AAAACFAAB


Notice the Capture current redo log which is pointing to Oracle log sequence# 754. This indicates that SharePlex is processing (mining) the latest Oracle redo stream. We can also notice that the Capture state is changed from Waiting for wallet to be loaded to Processing, which means Capture process is no longer looking for the TDE key and is able to process the redo stream.

Let’s validate if we can now see the records (that we had inserted in source encrypted table) in the target table.

##---
##--- validate if the encrypted records are replicated to target ---##
##---
SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB_1

SQL> select * from T_MYAPP_USERS;

   USER_ID USER_NAME       JOIN_DATE
---------- --------------- ---------------------------------------------------------------------------
         1 Alex            02-APR-16 11.43.38.729821 AM
         2 Abbas           04-JUN-16 01.10.17.000000 PM
         3 Fazal           04-JUN-16 01.10.24.000000 PM

As expected, the encrypted records are now replicated to target database. The replication setup is confirmed to be working with support for TDE replication.

One important point to mention here is that every time we start (restart) a SharePlex instance which is configured to support TDE replication, we need to run the sp_wallet tool for loading the TDE wallet information into SharePlex, which will in turn let SharePlex read the encrypted data in a decrypted mode. We will get a message (*** To enable TDE replication, run sp_wallet and provide the wallet password ***) to do the same at the time of SharePlex instance startup.

This seems to be a manual operation and at times we might forget (human tendency) to run the sp_wallet tool and potentially cause the replication to not work for TDE encrypted data. Dell had answered this concern with SharePlex release 8.6.3, where we can configure the wallet to be automatically loaded for SharePlex during instance startup. To facilitate the auto load of wallet, Dell has added a new clause in the sp_wallet tool called –auto-open, which lets the wallet to be loaded automatically for SharePlex.

Following is the sp_wallet command for enabling auto load of TDE wallet information.

##---
##--- sp_wallet command for auto load of wallet information ---##
##---
./sp_wallet --auto-open

If at any point, we wish to revert back to the manual method of loading wallet information, we can do that using the following sp_wallet command.

##---
##--- sp_wallet command for disabling auto load of wallet information ---##
##---
./sp_wallet --no-auto-open

In this post, we have covered the steps required to configure SharePlex to support Oracle’s transparent data encryption feature. For full information on the Oracle features that SharePlex supports, please refer the release specific notes.

Clef two-factor authentication
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed