Introduction
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:
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 :
oracle@mysrvr:/app/oracle/admin/WALLET/MYDB1 [MYDB1]# ls -lisa
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
- SQL> select * from v$encryption_wallet;
- WRL_TYPE
- ——————–
- WRL_PARAMETER
- ——————————————————————————–
- STATUS WALLET_TYPE WALLET_OR FULLY_BAC
- —————————— ——————– ——— ———
- CON_ID
- ———-
- FILE
- /app/oracle/admin/WALLET/MYDB1/
- OPEN UNKNOWN SINGLE NO
- 0
Goldengate part, when TDE is involved:
- Run GGSCI.
- 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.
- 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
Mathijs Bruggink 