When replicating sensitive data with Oracle GoldenGate, it's important to encrypt the trail files [2]. In this blog, I will discuss how to encrypt the trail data and manage the encryption keys using the two available methods supported by Oracle GoldenGate: the Master Key and Wallet Method and the the Encryption Files (ENCKEYS) Method.
​1. Encrypting Data with the Master Key and Wallet Method
Let's begin with the recommended approach. Starting from Oracle GoldenGate 12.1.2, Oracle Wallet is integrated into Oracle GoldenGate to manage encryption keys [1] . The master key and wallet method then also became the recommended approach to encrypting ​trail files.
What is Oracle Wallet?
Oracle Wallet is a password-protected container used to store authentication and signing credentials, including private keys, certificates, and trusted certificates needed by SSL. [3] You can use Oracle Wallet Manager to create wallets, generating certificate requests, open wallets to access the PKI-based (Public Key Infrastructure) services and upload wallets to LDAP directory etc.
Best Practices: Encrypting Oracle GoldenGate Trail Files
Oracle GoldenGate encryption with the master key and wallet is the best practice to encrypt Oracle GoldenGate trail files.
The master key and wallet encryption process includes the following steps:
- Users have to create a master-key wallet and add a master key to the wallet.
- Oracle GoldenGate automatically generates a new encryption key and use it to encrypt every new trail file. The encryption key is included in the trail header and is encrypted using the master key.
- Oracle GoldenGate on the target will decrypt the encryption key with the shared master key, and then use the encryption key to decrypt the trail file.
The following is a step by step example of using the master key and wallet method.
​1.1. Create the Master Key
First, you need to create a wallet once, add the master keys to the wallet and then copy the cwallet.sso file to all the systems (at WALLETLOCATION defined by GLOBALS with default to be dirwlt folder) that run Oracle GoldenGate or put the cwallet.sso file to a shared storage accessible by all Oracle GoldenGate systems.
​1.1. Create the Master Key
First, you need to create a wallet once, add the master keys to the wallet and then copy the cwallet.sso file to all the systems (at WALLETLOCATION defined by GLOBALS with default to be dirwlt folder) that run Oracle GoldenGate or put the cwallet.sso file to a shared storage accessible by all Oracle GoldenGate systems.
Why do I got the "OGG-06114 Error retrieving masterkey" error?
Details:When I start the replicat, I got the following error on the replicat side:
Details:When I start the replicat, I got the following error on the replicat side:
2017-06-26 17:28:47 ERROR OGG-06114 Error retrieving masterkey OGG_DEFAULT_M ASTERKEY from wallet at location dirwlt.What's the problem?
Answer: Please make sure you copy the cwallet.sso file to the wallet folder.
GGSCI> create wallet Created wallet at location 'dirwlt'. Opened wallet at location 'dirwlt'. GGSCI> open wallet Opened wallet at location 'dirwlt'. GGSCI> add masterkey Master key 'OGG_DEFAULT_MASTERKEY' added to wallet at location 'dirwlt'. GGSCI> sh ls dirwlt cwallet.sso
The preceding example doesn't specify a master key name. GoldenGate will create the key under the default name, OGG_DEFAULT_MASTERKEY. You can create a master key with a name shown as follows:
GGSCI> add masterkey ggcs Master key 'ggcs' added to wallet at location 'dirwlt'. GGSCI> info masterkey ggcs Masterkey Name: ggcs Creation Date: Sat Dec 17 16:23:21 2016 Version: Creation Date: Status: 1 Sat Dec 17 16:23:21 2016 Current
The example creates a master key named ggcs. You tell Oracle GoldenGate to use the master key by configuring the the MASTERKEYNAME parameter. in GLOBALS file. ​By default, Oracle GoldenGate will pick up the latest version.
GLOBALS: MASTERKEYNAME [VERSION ] MASTERKEYNAME ggcs
1.2. Managing the Keys
You can manage the master keys in GGSCI. The following example shows how you can check the masterkey details.
You can manage the master keys in GGSCI. The following example shows how you can check the masterkey details.
GGSCI (ip-172-30-3-169.ec2.internal) 2> open wallet Opened wallet at location 'dirwlt'. GGSCI (ip-172-30-3-169.ec2.internal) 3> info masterkey Masterkey Name: OGG_DEFAULT_MASTERKEY Creation Date: Fri Dec 16 22:20:14 2016 Version: Creation Date: Status: 1 Fri Dec 16 22:20:14 2016 Current GGSCI (ip-172-30-3-169.ec2.internal) 4> info masterkey version 1 Masterkey Name: OGG_DEFAULT_MASTERKEY Creation Date: Fri Dec 16 22:20:14 2016 Version: 1 Renew Date: Fri Dec 16 22:20:14 2016 Status: Current Key Hash (SHA1): 0x44C06B01B64265A5502D4D7DE0AFBEE541EB96C7
You can also renew masterkeys to create a new encryption key with a different bit order.
GGSCI> renew masterkey ggcs Master key 'ggcs' renewed to version 2 in wallet at location 'dirwlt'. GGSCI> renew masterkey Master key 'OGG_DEFAULT_MASTERKEY' renewed to version 2 in wallet at location 'dirwlt'. GGSCI (ip-172-30-3-169.ec2.internal) 11> ifno masterkey ERROR: Invalid argument 'ifno'. GGSCI> info masterkey Masterkey Name: OGG_DEFAULT_MASTERKEY Creation Date: Fri Dec 16 22:20:14 2016 Version: Creation Date: Status: 1 Fri Dec 16 22:20:14 2016 Available 2 Sat Dec 17 16:25:58 2016 Current
1.3. Using the Encryption Key
You can use the ENCRYPTTRAIL parameter in Extract (including the Pump) parameter file to encrypt the trail files. If you don't specify the MASTERKEYNAME in the GLOBALS, Oracle GoldenGate will use the default master key named OGG_DEFAULT_MASTERKEY. If you don't specify the AES cipher name, AES128 is used.
You can use the ENCRYPTTRAIL parameter in Extract (including the Pump) parameter file to encrypt the trail files. If you don't specify the MASTERKEYNAME in the GLOBALS, Oracle GoldenGate will use the default master key named OGG_DEFAULT_MASTERKEY. If you don't specify the AES cipher name, AES128 is used.
ENCRYPTTRAIL AES192 ENCRYPTTRAIL
When extract directly write to the remote host, the encryption using REMOTEHOSTOPTIONS syntax is shown as follows:
RMTHOSTOPTIONS host, MGRPORT port, ENCRYPT {AES128 | AES192 | AES256 | BLOWFISH}
The decryption is mostly automatic, which means you don't need to use ​DECRYPTTRAIL unless your want to create a decrypted trail file in the pump. ​
When shall I use DECRYPTTRAIL parameter if the wallet-based encryption is used?
Answer: When using the wallet-based encryption, you only need to use the DECRYPTTRAIL parameter in PUMP, and only when the PUMP process is a NOPASSTHROGH, and you need to send decrypted trail files to the targets.
2. Encryption with the Encryption Files (ENCKEYS) Method
Before Oracle GoldenGate 12.1.2, or for database including DB2 for Series, DB2 for z/OS, and NonStop, you can use the encryption file method. You need to create encryption key files with a list of key names and the associated key content shown as follows:
encyptkey1 0x74E8701BD5DFB21F559ECB34594ED437 encyptkey2 0x4E62863FE5C8AA70DA9B4A3D80250C34 encyptkey3 0x9900ED62CC0FEB77D3841D52E28C957D
When you encrypt the trail file, you need to specify the key name along with the encryption algorithm used.
ENCRYPTTRAIL [Algorithm] KEYNAME [keyname] DECRYPTTRAIL [Algorithm] KEYNAME [keyname]
The encryption files (ENCKEYS) process includes the following steps:
- Users need to create the ENCKEYS and copy the file to all of the related Oracle GoldenGate systems.
- Oracle GoldenGate use the defined encryption key encrypt the trail files.
- Oracle GoldenGate on the target will decrypt the encryption key.
2.1 Generate the Encryption Key
Oracle GoldenGate provides the keygen utility to generated encryption keys. The following example creates a AES256 key and create a new ENCKEYS file.
Oracle GoldenGate provides the keygen utility to generated encryption keys. The following example creates a AES256 key and create a new ENCKEYS file.
>./keygen 256 1 >ENCKEYS
2.2 Store the Encryption Key in a ENCKEYS Lookup File
You can use any text editor to add the key names to the ENCKEYS file. The example defines the key name to be keyaes2561.
You can use any text editor to add the key names to the ENCKEYS file. The example defines the key name to be keyaes2561.
> more ENCKEYS 0x75EBF271E0588D443B8B3259200AB23BBF41E92EC5BAF83E6FE3B83153AA6844 > vi ENCKEYS > more ENCKEYS keyaes2561 0x75EBF271E0588D443B8B3259200AB23BBF41E92EC5BAF83E6FE3B83153AA6844
2.3 Copy ENCKEYS File to Every Oracle GoldenGate System
You have to copy the ENCKEYS file to every system where the encryption and decryption are performed.
2.4 Using the Encryption Key
​In the extract and replicat parameter files, you can use the ENCRYPTTRAIL and DECRYPTTRAIL parameters with the KEYNAME to configure the encryption and decryption. The following is an example extract parameter file:
You have to copy the ENCKEYS file to every system where the encryption and decryption are performed.
2.4 Using the Encryption Key
​In the extract and replicat parameter files, you can use the ENCRYPTTRAIL and DECRYPTTRAIL parameters with the KEYNAME to configure the encryption and decryption. The following is an example extract parameter file:
extract extgdrds useridalias awsuser encrypttrail AES256 keyname keyaes2561 EXTTRAIL ./dirdat/rd ddl include all ddloptions addtrandata, report TABLE awsuser.member;
The following is an example replicat parameter file:
replicat repgdrds
MACRO #exception_handler
BEGIN
, TARGET ggadm.exception_tbl
, COLMAP ( rep_name = "REPGDRDS"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
reperror(1413, exception)
useridalias ggadmin_tgt
DDLERROR 1918 IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 10
DDLERROR 1435 IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 10
dboptions integratedparams(parallelism 2)
decrypttrail aes256 keyname keyaes2561
discardfile ./dirrpt/repgdrds.dsc,append megabytes 50
ddl include all
map awsuser.member, TARGET pdb1.pmdemo.member;
What should I do if I got the "OGG-02268 Decryption key found in source trail after switching trail sequence." error?
Answer: The error message shows that the previous sequence trail had no trail file decryption key, but the new trail file has the trail file decryption key. You just need to restart the pump process to reset the table mapping.
When extract directly write to the remote host, the encryption using REMOTEHOSTOPTIONS syntax is shown as follows:
RMTHOSTOPTIONS host, MGRPORT port, ENCRYPT {AES128 | AES192 | AES256 | BLOWFISH} KEYNAME [keyname]
In most of the GoldenGate extracts, the data are decrypted when reading from database transaction logs. Therefore, encrypting them before delivering to the target databases helps protect your data.
Resources
- Oracle GoldenGate 12.1.2 Documentation: 11.2 Encrypting Data with the Master Key and Wallet Method
- Loren Penton, Masking Sensitive Data with Oracle GoldenGate, Oracle A-Team Blog
- Oracle Documentation, Database Advanced Security Administrator's Guide, 9 Using Oracle Wallet Manager
Created: 12/19/2016, Last Updated: 6/26/2017
RSS Feed