â1. Encrypting Data with the Master Key and Wallet Method
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.
Oracle GoldenGate encryption with the master key and wallet is the best practice to encrypt Oracle GoldenGate trail files.
- 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.
â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.
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
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
GLOBALS: MASTERKEYNAME [VERSION ] MASTERKEYNAME ggcs
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
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
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
RMTHOSTOPTIONS host, MGRPORT port, ENCRYPT {AES128 | AES192 | AES256 | BLOWFISH}
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
encyptkey1 0x74E8701BD5DFB21F559ECB34594ED437 encyptkey2 0x4E62863FE5C8AA70DA9B4A3D80250C34 encyptkey3 0x9900ED62CC0FEB77D3841D52E28C957D
ENCRYPTTRAIL [Algorithm] KEYNAME [keyname] DECRYPTTRAIL [Algorithm] KEYNAME [keyname]
- 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.
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
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
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;
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;
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.
RMTHOSTOPTIONS host, MGRPORT port, ENCRYPT {AES128 | AES192 | AES256 | BLOWFISH} KEYNAME [keyname]
- 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