It might sound like a movie trilogy to touch similar topic for the 3rd time, and frankly in my last two topics touched ground on these topics:

• TDE SETUP in a rac environment:
• When wallet is not open in TDE Environment (ORA-28365)

Well today the third article has to rise. Simply because there is always an interesting interactions requiring a follow up between Admins and teams using a solution. In such galaxy far far away the question would rise:what will happen if you armor your tablespaces with TDE and then you decide to use Goldengate when classic capture is being used as tool to extract data from that environment. (spoiler alert Goldengate (GG) will break).

On the various capture models in Goldengate check this url:

https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/process_mode.htm#GIORA209

For this blog really all credits needs to be given to a brother in arms, a close friend and a most seasoned dba (Mr. Roel Hendriks) who I had the honor to work with during Project(s). He did the research implemented this solotion first. Since then I was able to implement it on various platforms. For saga completion it is now my goal to complete the trilogy with this blog.

Additional required steps with TDE And GG in place

• Create package dbms_internal_clkm on database by running @?/rdbms/admin/prvtclkm.plb.
• Grant this package to database account which extract data from the database: “grant execute on dbms_internal_clkm to ggapp;”. This package enables database accounts to extract data via GoldenGate in a encrypted database.
• Find out the location of your wallet location via the view v$encryption_wallet or the sqlnet.ora file.

This will show something like :

total 48

74  4 drwxr-xr-x. 2 oracle dba 4096 Sep 13 06:37 .

 2  4 drwxrwxr-x. 5 oracle dba 4096 Sep 13 08:52 ..

82 12 -rw——-. 1 oracle dba 5304 Sep 13 06:37 cwallet.sso

78  4 -rw——-. 1 oracle dba 2555 Sep 13 05:55 ewallet_2019091303553484.p12

80  4 -rw——-. 1 oracle dba 3803 Sep 13 05:57 ewallet_2019091303572233.p12

81  8 -rw——-. 1 oracle dba 5067 Sep 13 06:01 ewallet_2019091304014993.p12

77 12 -rw——-. 1 oracle dba 5259 Sep 13 06:01 ewallet.p12

• Execute at wallet location (you have to supply the wallet password) :

(means in this case in:  /app/oracle/admin/WALLET/ MYDB1)

mkstore -wrl ./ -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

Oracle Secret Store Tool : Version 12.2.0.1.0

Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

 ## you have to enter a password ( twice):

— supplied password: hugo ( this password will be needed in GGSCI)

Enter your secret/Password:

Re-enter your secret/Password: 

Enter wallet password:

• Verify the result:

mkstore -wrl . -list

Oracle Secret Store Tool : Version 12.2.0.1.0

Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

• Enter wallet password:

## when wallet password is given you will see similar to below:

Oracle Secret Store entries:

ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

ORACLE.SECURITY.DB.ENCRYPTION.Ae/sVNN8L0+iv/uBOenqJsQZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

ORACLE.SECURITY.DB.ENCRYPTION.AUAh7+KLWk+6v92xdM/qIxcZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.ID.ENCRYPTION.

ORACLE.SECURITY.KB.ENCRYPTION.

ORACLE.SECURITY.KM.ENCRYPTION.Ae/sVNN8L0+iv/uBOenqJsQZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

ORACLE.SECURITY.KM.ENCRYPTION.AUAh7+KLWk+6v92xdM/qIxcZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

##

• On os you will now see similar to this:

oracle@mysrvr:/app/oracle/admin/WALLET/MYDB1 [MYDB1]# ls -lisa

total 48

74  4 drwxr-xr-x. 2 oracle dba 4096 Sep 19 11:16 .

 2  4 drwxrwxr-x. 5 oracle dba 4096 Sep 13 08:52 ..

82 12 -rw——-. 1 oracle dba 5453 Sep 19 11:17 cwallet.sso

83  0 -rw——-. 1 oracle dba    0 Sep 19 11:16 cwallet.sso.lck

78  4 -rw——-. 1 oracle dba 2555 Sep 13 05:55 ewallet_2019091303553484.p12

80  4 -rw——-. 1 oracle dba 3803 Sep 13 05:57 ewallet_2019091303572233.p12

81  8 -rw——-. 1 oracle dba 5067 Sep 13 06:01 ewallet_2019091304014993.p12

77 12 -rw——-. 1 oracle dba 5408 Sep 19 11:17 ewallet.p12

79  0 -rw——-. 1 oracle dba    0 Sep 19 11:16 ewallet.p12.lck

• Switch logfile.
• Stop / start the database.

• Be aware:

  The “wallet_type” in the v$encryption_wallet be ‘unknown’  because of using mkstore utility. This is expected behavior: https://docs.oracle.com/database/121/REFRN/GUID-054B6FBC-4263-4A91-AEBD-D6FB50C22107.htm#REFRN30488
7.168 V$ENCRYPTION_WALLET

Goldengate part, when TDE is involved: 

1. Run GGSCI.
2. Issue the ENCRYPT PASSWORD command to encrypt the shared secret so that it is obfuscated within the Extract parameter file. This is a security requirement.
3. ENCRYPT PASSWORD hugo {AES128 | AES192 | AES256} ENCRYPTKEY keyname

Where:

• hugo is the clear-text shared secret. This value is case-sensitive.
• {AES128 | AES192 | AES256} specifies Advanced Encryption Standard (AES) encryption. Specify one of the values, which represents the desired key length.
• keyname is the logical name of the encryption key in the ENCKEYS lookup file. Oracle GoldenGate uses this key to look up the actual key in the ENCKEYS file. To create a key and ENCKEYS file, see Administering Oracle GoldenGate for Windows and UNIX.

Example:

ENCRYPT PASSWORD hugo AES256 ENCRYPTKEY mykey1

• In the Extract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option. As input, supply the encrypted shared secret and the decryption key.

DBOPTIONS DECRYPTPASSWORD hugo {AES128 | AES192 | AES256} ENCRYPTKEY keyname

In our case the DBOPTIONS was:

DBOPTIONS DECRYPTPASSWORD “AADAAAAAAAAAAAMANAREQEREYDJFOBYALGZHQCLGUCCHQGBDZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZAAA

Happy reading,

Mathijs