Ref: [Doc ID: 1451327.1]
To support TDE on Oracle 10.2.0.5 or 11.2.0.2, download and apply
Oracle Patch 10395645 to the source database. Oracle 11.2.0.3
patchset includes this patch
Since my database is on 11.2.0.2 Two-Node RAC on LINUX so i'm applying below patch (see patch doc for how to apply)
$ opatch apply online
-connectString<SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_Node2>:<Password_Node2>:<Node2_Name>,<SID_NodeN>:<Username_NodeN>:<Password_NodeN>:<NodeN_Name><PATCH_TOP_DIR>/10395645
From NODE 1: (everything from NODE1 unless specified)
------------------------------------------------------------------------
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
DEV011
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE
---------- --------------------
WRL_PARAMETER
STATUS
----------------------------------------
------------------------------------
1 file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED
2 file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED
Status "CLOSED" because my database doesn't have any Wallet created yet
SQL> select * from v$encryption_wallet;
WRL_TYPE
-------------
WRL_PARAMETER
STATUS
-----------------------------------
---------------------------
file
/opt/oracle/app/admin/DEV01/wallet CLOSED
Put the below code in sqlnet.ora of GI and OH home location :
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/app/admin/DEV01/wallet/)))
NOTE: Create the directories on both nodes if they
doesn't exists on the server
ex: mkdir -p /opt/oracle/app/admin/DEV01/wallet
copy sqlnet.ora file to
other nodes into same locations (GRID
and RDBMS homes)
ex: scp sqlnet.ora oracle@oracledev012.domain.com:/opt/oracle/database/11.2.0.2/network/admin
scp sqlnet.ora oracle@oracledev012.domain.com:/opt/grid/11.2.0.2/network/admin
Now create a wallet password
using below sql command from one node:
SQL> alter system set
encryption key identified by "xxxxx";
System altered.
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE
---------- --------------------
WRL_PARAMETER
STATUS
-----------------------------------
------------------------------------
1 file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet OPEN
2 file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED
copy the wallet file from /opt/oracle/app/admin/DEV01/wallet/ from
node1 to other nodes
**** Now, BOUNCE the database ****
$ srvctl stop database -d DEV01
$ srvctl start database -d DEV01
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE
---------- --------------------
WRL_PARAMETER
STATUS
-----------------------------------
------------------------------------
1 file
/opt/oracle/app/admin/DEV01/wallet/ CLOSED
2 file
/opt/oracle/app/admin/DEV01/wallet/ CLOSED
Observe, Oracle wallet location has been
changed after database bounce
SQL> alter system set encryption wallet open identified by “xxxxx";
System altered.
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE
---------- --------------------
WRL_PARAMETER
STATUS
-----------------------------------
------------------------------------
1 file
/opt/oracle/app/admin/DEV01/wallet/ OPEN
2 file
/opt/oracle/app/admin/DEV01/wallet/ OPEN
From GG_HOME directory, run prvtclkm.plb,
and grant privilege to goldengate user
(missing this step
may cause extract error: PLS-00201:
identifier 'SYS.DBMS_INTERNAL_CLKM'
must be declared)
oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 18 20:59:07 2013
Copyright (c) 1982, 2010, Oracle.
All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Data Mining and Real Application Testing options
SQL>@prvtclkm.plb
Package created.
Library created.
Package body created.
SQL>grant execute on
sys.dbms_internal_clkm to gguser;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Data Mining and Real Application Testing options
oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ cd
/opt/oracle/app/admin/DEV01/wallet
From wallet location, execute
below:
oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA
Now, create an entry forORACLEGG
in the wallet (this will ask you to create
sharedsecret password)
oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -createEntry
ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights
reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:enter_sharedsecret_password
Re-enter your secret/Password:enter_sharedsecret_password
Enter wallet password:enter_wallet_password
Now verify whether OGG entry has been created or not
oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA
Now we’ll see the new ORACLEGG entry in the wallet (only in node1), so
to create the entry in other nodes copy the wallet to other nodes
Here I’m doing SCP the wallet file from node1 to other nodes
$ scp * oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet
After copying wallet file from node1 to node2 list and see for new
encryption for ORACLEGG entry from Node2 server
(from NODE2) oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet
INT$ mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA
NOTE:
- close wallet from all the instances.
- re-open the wallet from all the
instances.
Ex:
alter system set encryption wallet close identified by "xxxx";
(node1)
alter system set encryption wallet open identified by "xxxx";
(node1)
alter system switch logfile;
alter system set encryption wallet close identified by "xxxx";
(node2)
alter system set encryption wallet open identified by "xxxx";
(node2)
alter system switch logfile;
Connect to ggsci and encrypt the sharedsecret password
GGSCI (oracleDEV01.domain.com) 2> ENCRYPT PASSWORD sharedsecret
AES128 ENCRYPTKEY WALLETENCR
Encrypted password:
AADAAAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI
Algorithm used: AES128
Where WALLETENCR à
key mentioned in the ENCKEYS file
In the Extract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option
GGSCI (oracleDEV01.domain.com) 3> edit params EXT
DBOPTIONS DECRYPTPASSWORD AADAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI
AES128 &
ENCRYPTKEY WALLETENCR
Bounce the extract process to take this effect
