I have been asked more than once whether the Oracle Wallet supports GoldenGate trail encryption. Although GoldenGate has supported encryption with the ENCKEYS file for years, Oracle GoldenGate 12c now also supports encryption using the Oracle Wallet. This helps improve security and makes it easier to administer.

Two types of wallets can be configured in Oracle GoldenGate 12c:

• The wallet that holds the master keys, used with trail or TCP/IP encryption and decryption, stored in the new 12c dirwlt/cwallet.sso file.  

• The wallet that holds the Oracle Database user IDs and passwords stored in the ‘credential store’ stored in the new 12c dircrd/cwallet.sso file.

 

A wallet can be created using a ‘create wallet’  command.  Adding a master key to an existing wallet is easy using ‘open wallet’ and ‘add masterkey’ commands.

 

GGSCI (EDLVC3R27P0) 42> open wallet

Opened wallet at location 'dirwlt'.

GGSCI (EDLVC3R27P0) 43> add masterkey

Master key 'OGG_DEFAULT_MASTERKEY' added to wallet at location 'dirwlt'.

 

Existing GUI Wallet utilities that come with other products such as the Oracle Database “Oracle Wallet Manager” do not work on this version of the wallet. The default Oracle Wallet can be changed.

 

GGSCI (EDLVC3R27P0) 44> sh ls -ltr ./dirwlt/*

-rw-r----- 1 oracle oinstall 685 May 30 05:24 ./dirwlt/cwallet.sso

GGSCI (EDLVC3R27P0) 45> info masterkey

Masterkey Name:                 OGG_DEFAULT_MASTERKEY

Creation Date:                  Fri May 30 05:24:04 2014

Version:        Creation Date:                  Status:

1               Fri May 30 05:24:04 2014        Current

 

The second wallet file is used for the credential used to connect to a database, without exposing the user id or password. Once it is configured, this file can be copied so that credentials are available to connect to the source or target database.

 

GGSCI (EDLVC3R27P0) 48> sh cp ./dircrd/cwallet.sso $GG_EURO_HOME/dircrd

GGSCI (EDLVC3R27P0) 49> sh ls -ltr ./dircrd/*

-rw-r----- 1 oracle oinstall 709 May 28 05:39 ./dircrd/cwallet.sso

 

The encryption wallet file can also be copied to the target machine so the replicat has access to the master key to decrypt records that are encrypted in the trail. Similar to the old ENCKEYS file, the master keys wallet created on the source host must either be stored in a centrally available disk or copied to all GoldenGate target hosts. The wallet is in a platform-independent format, although it is not certified for the iSeries, z/OS, and NonStop platforms.

 

GGSCI (EDLVC3R27P0) 50> sh cp ./dirwlt/cwallet.sso $GG_EURO_HOME/dirwlt

 

The new 12c UserIdAlias parameter is used to locate the credential in the wallet so the source user id and password does not need to be stored as a parameter as long as it is in the wallet.

 

GGSCI (EDLVC3R27P0) 52> view param extwest

extract extwest

exttrail ./dirdat/ew

useridalias gguamer

table west.*;

The EncryptTrail parameter is used to encrypt the trail using the Advanced Encryption Standard and can be used with a primary extract or pump extract.

GGSCI (EDLVC3R27P0) 54> view param pwest

extract pwest

encrypttrail AES256

rmthost easthost, mgrport 15001

rmttrail ./dirdat/pe

passthru

table west.*;

 

Once the extracts are running, records can be encrypted using the wallet.

 

GGSCI (EDLVC3R27P0) 60> info extract *west

EXTRACT    EXTWEST   Last Started 2014-05-30 05:26   Status RUNNING

Checkpoint Lag       00:00:17 (updated 00:00:01 ago)

Process ID           24982

Log Read Checkpoint  Oracle Integrated Redo Logs

                     2014-05-30 05:25:53

                     SCN 0.0 (0)

EXTRACT    PWEST     Last Started 2014-05-30 05:26   Status RUNNING

Checkpoint Lag       24:02:32 (updated 00:00:05 ago)

Process ID           24983

Log Read Checkpoint  File ./dirdat/ew000004

                     2014-05-29 05:23:34.748949  RBA 1483

 

The ‘info masterkey’ command is used to confirm the wallet contains the key after copying it to the target machine. The key is needed to decrypt the data in the trail before the replicat applies the changes to the target database.

 

GGSCI (EDLVC3R27P0) 41> open wallet

Opened wallet at location 'dirwlt'.

GGSCI (EDLVC3R27P0) 42> info masterkey

Masterkey Name:                 OGG_DEFAULT_MASTERKEY

Creation Date:                  Fri May 30 05:24:04 2014

Version:        Creation Date:                  Status:

1               Fri May 30 05:24:04 2014        Current

 

Once the replicat is running, records can be decrypted using the wallet.

 

GGSCI (EDLVC3R27P0) 44> info reast

REPLICAT   REAST     Last Started 2014-05-30 05:28   Status RUNNING

INTEGRATED

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Process ID           25057

Log Read Checkpoint  File ./dirdat/pe000004

                     2014-05-30 05:28:16.000000  RBA 1546

 

There is no need for the DecryptTrail parameter when using the Oracle Wallet, unlike when using the ENCKEYS file.

 

GGSCI (EDLVC3R27P0) 45> view params reast

replicat reast

assumetargetdefs

discardfile ./dirrpt/reast.dsc, purge

useridalias ggueuro

map west.*, target east.*;

 

Once a record is inserted into the source table and committed, the encryption can be verified using logdump and then querying the target table.

 

AMER_SQL>insert into west.branch values (50, 80071);

1 row created.

 

AMER_SQL>commit;

Commit complete.

 

The following encrypted record can be found using logdump.

Logdump 40 >n

2014/05/30 05:28:30.001.154 Insert               Len    28 RBA 1546

Name: WEST.BRANCH

After  Image:                                             Partition 4   G  s  

 0a3e 1ba3 d924 5c02 eade db3f 61a9 164d 8b53 4331 | .>...$\....?a..M.SC1 

 554f e65a 5185 0257                               | UO.ZQ..W 

Bad compressed block, found length of  7075 (x1ba3), RBA 1546

  GGS tokens:

TokenID x52 'R' ORAROWID         Info x00  Length   20

 4141 4157 7649 4141 4741 4141 4144 7541 4170 0001 | AAAWvIAAGAAAADuAAp.. 

TokenID x4c 'L' LOGCSN           Info x00  Length    7

 3231 3632 3934 33                                 | 2162943 

TokenID x36 '6' TRANID           Info x00  Length   10

 3130 2e31 372e 3135 3031                          | 10.17.1501 

The replicat automatically decrypted this record from the trail and then inserted the row to the target table using the wallet. This select verifies the row was inserted into the target database and the data is not encrypted.

EURO_SQL>select * from branch where branch_number=50;

BRANCH_NUMBER                  BRANCH_ZIP

-------------                                   ----------

   50                                              80071

 

Book a seat in an upcoming Oracle GoldenGate 12c: Fundamentals for Oracle course now to learn more about GoldenGate 12c new features including how to use GoldenGate with the Oracle wallet, credentials, integrated extracts, integrated replicats, the Oracle Universal Installer, and other new features.

Looking for another course? View all Oracle GoldenGate training.

 

Randy Richeson joined Oracle University as a Senior Principal Instructor in March 2005. He is an Oracle Certified Professional (10g-12c) and a GoldenGate Certified Implementation Specialist (10-11g). He has taught GoldenGate since 2010 and also has experience teaching other technical curriculums including GoldenGate Monitor, Veridata, JD Edwards, PeopleSoft, and the Oracle Application Server.