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
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 (GRIDand 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 forORACLEGGin 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
No comments:
Post a Comment