Encrypting Data with the Master Key and Wallet Method

To use this method of data encryption, you create a master-key wallet and add a master key to the wallet.

=> Each time Oracle GoldenGate creates a trail file, it generates a new encryption key automatically. This encryption key encrypts the trail contents. The master key encrypts the encryption key. This process of encrypting encryption keys is known as key wrap.
=> To encrypt data across the network, Oracle GoldenGate generates a session key using a cryptographic function based on the master key.
=> Oracle GoldenGate uses an auto-login wallet (file extension .sso), meaning that it is an obfuscated container that does not require human intervention to supply the necessary passwords.

=> To encrypt trail data:
In the parameter file of the primary Extract group and the data pump, add an ENCRYPTTRAIL parameter statement before any parameter that specifies a trail or file that you want to be encrypted. Parameters that specify trails or files are EXTTRAIL, RMTTRAIL, EXTFILE, and RMTFILE.
=> To Decrypt trail data :
Use the DECRYPTTRAIL parameter for a data pump if you want trail data to be decrypted before it is written to the output trail. Otherwise, the data pump automatically decrypts it, if processing is required, and then reencrypts it before writing to the output trail. (Replicat decrypts the data automatically without any parameter input.)

=> To encrypt data across TCP/IP:
In the parameter file of the data pump (or the primary Extract, if no pump is being used), use the ENCRYPT option of the RMTHOSTOPTIONS parameter.

=> To Decrypt data across TCP/IP :
Use the DECRYPTTRAIL parameter for a data pump if you want trail data to be decrypted before it is written to the output trail. Otherwise, the data pump automatically decrypts it, if processing is required, and then reencrypts it before writing to the output trail. (Replicat decrypts the data automatically without any parameter input.)

So let's Begin with Practice

Step 1=> Create Wallet (Purse)

---Check contents of dirwlt

[oracle@HOST12C2020 gg_home]$ cd dirwlt/
[oracle@HOST12C2020 dirwlt]$ ls -lrt
total 0

GGSCI (HOST12C2020) 1> create wallet

Created wallet.

Opened wallet.

---Check contents of dirwlt and size of cwallet.sso

[oracle@HOST12C2020 gg_home]$ ls -lrt dirwlt/
total 4
-rw-r-----. 1 oracle oinstall 381 May 31 00:14 cwallet.sso
[oracle@HOST12C2020 gg_home]$

Step 2=> Open Wallet and Add Masterkey in it

GGSCI (HOST12C2020) 2> open wallet

Opened wallet.

GGSCI (HOST12C2020) 3> add masterkey
2020-05-30T18:45:03Z  INFO    OGG-06142  Created version 1 of master key 'OGG_DEFAULT_MASTERKEY' in Oracle Wallet.

GGSCI (HOST12C2020) 4> exit
[oracle@HOST12C2020 gg_home]$

--Check contents of dirwlt and size of cwallet.sso

[oracle@HOST12C2020 gg_home]$ ls -lrt dirwlt/
total 4
-rw-r-----. 1 oracle oinstall 763 May 31 00:15 cwallet.sso

GGSCI (HOST12C2020) 3> info masterkey
Masterkey Name: OGG_DEFAULT_MASTERKEY

Version         Creation Date                   Status
1               2020-05-31T00:15:03.000+05:30   Current

Step 3=> Scp cwallet.sso to other nodes (where replicat running)

[oracle@HOST12C2020 dirwlt]$ scp cwallet.sso oracle@HOST12C2021:/u01/app/oracle/gg_home/dirwlt/
oracle@host12c2021's password:
cwallet.sso                                                                                       100%  763     0.8KB/s   00:00
[oracle@HOST12C2020 dirwlt]$
[oracle@HOST12C2020 dirwlt]$ cd ..

--ON TARGET

[oracle@HOST12C2021 gg_home]$ ls -lrt dirwlt/
total 4
-rw-r-----. 1 oracle oinstall 763 May 31 00:17 cwallet.sso
[oracle@HOST12C2021 gg_home]$

Step 4=> Edit Extract on Source only as on target REPLICAT DO NOT NEED TO EDIT ANY PARAMETER FOR Decrypt

GGSCI (HOST12C2020) 1> view params EXT1

EXTRACT ext1
USERID ggsource@NCDB password AADAAAAAAAAAAAIARIHASGODKDZFIAZISDJDGBNIOHZADDHFAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AES128 ENCRYPTKEY ggkey
--Encrypt Trail using Wallet
ENCRYPTTRAIL AES256
EXTTRAIL /u01/app/oracle/gg_home/dirdat/tt
TRANLOGOPTIONS EXCLUDEUSER ggsource
TABLE dbsource.*;

GGSCI (HOST12C2020) 2>

Step 5=> Stop and START Extract AND REPLICAT

--ON Source
GGSCI (HOST12C2020) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP1      00:00:00      00:00:02
EXTRACT     RUNNING     EXT1        00:00:00      00:00:06

--ON TARGET

GGSCI (HOST12C2021) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:08

Step 6=> Check log to verify if Encryption is running on source and target

--ON Source

2020-05-31T00:37:19.730+0530  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, ext1.prm:  Rolling over remote file /u01/app/oracle/gg_home/dirdat/tt000000007.
2020-05-31T00:37:19.745+0530  INFO    OGG-05519  Oracle GoldenGate Capture for Oracle, ext1.prm:  Output trail file encryption: AES256.
2020-05-31T00:37:19.749+0530  INFO    OGG-01053  Oracle GoldenGate Capture for Oracle, ext1.prm:  Recovery completed for target file /u01/app/oracle/gg_home/dirdat/tt000000008, at RBA 1482.
2020-05-31T00:37:19.749+0530  INFO    OGG-01057  Oracle GoldenGate Capture for Oracle, ext1.prm:  Recovery completed for all targets.
2020-05-31T00:37:19.750+0530  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, ext1.prm:  Position of first record processed Sequence 11, RBA 9608208, SCN 0.2147084 (2147084), 2020 M05 31 00:36:18.
2020-05-31T00:37:19.907+0530  INFO    OGG-02232  Oracle GoldenGate Capture for Oracle, dpump1.prm:  Switching to next trail file /u01/app/oracle/gg_home/dirdat/tt000000008 at 2020-05-31 00:37:19.907807 due to EOF. with current RBA 1,482.
2020-05-31T00:37:22.160+0530  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2020-05-31T00:39:07.479+0530  INFO    OGG-06508  Oracle GoldenGate Capture for Oracle, ext1.prm:  Wildcard MAP (TABLE) resolved (entry dbsource.*): TABLE "DBSOURCE"."TEST".

2020-05-31T00:39:09.333+0530  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, ext1.prm:  Using the following key columns for source table DBSOURCE.TEST: ID.
2020-05-31T00:39:10.099+0530  INFO    OGG-05520  Oracle GoldenGate Capture for Oracle, dpump1.prm:  Input trail file encryption: AES256.
2020-05-31T00:39:10.099+0530  INFO    OGG-02262  Oracle GoldenGate Capture for Oracle, dpump1.prm:  Passthru wildcard MAP (TABLE) resolved (entry dbsource.*): TABLE "DBSOURCE"."TEST".
2020-05-31T00:39:10.107+0530  INFO    OGG-05519  Oracle GoldenGate Capture for Oracle, dpump1.prm:  Output trail file encryption: AES256.

--on target

2020-05-31T00:39:12.799+0530  INFO    OGG-05520  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Input trail file encryption: AES256.
2020-05-31T00:39:12.799+0530  INFO    OGG-03506  Oracle GoldenGate Delivery for Oracle, rep1.prm:  The source database character set, as determined from the trail file, is UTF-8.
2020-05-31T00:39:12.801+0530  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Wildcard MAP resolved (entry dbsource.*): MAP "DBSOURCE"."TEST", TARGET dbtarget."TEST".
2020-05-31T00:39:15.718+0530  INFO    OGG-02756  Oracle GoldenGate Delivery for Oracle, rep1.prm:  The definition for table DBSOURCE.TEST is obtained from the trail file.
2020-05-31T00:39:15.718+0530  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Using following columns in default map by name: ID, NAME.
2020-05-31T00:39:15.718+0530  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Using the following key columns for target table DBTARGET.TEST: ID.