Implementing TDE in RAC with Standby database configuration
============= Start of Step One, Steps for Primary database ====================
1. Add the keystore location in sqlnet.ora
on Node 1 of Primary RAC.
cp /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora.backup
vi /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA/$ORACLE_UNQNAME/WALLET)
)
)
Repeat the above in all the nodes of the cluster
2) Create a keystore in Primary database as below.
If its a container database, connect to the root and create the keystore as below.
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/BASICPR/WALLET/' identified by Welcome1;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------------- ------------------------- ------------------------------ -------------------- --------- --------- --------ASM +DATA/basicpr/WALLET/ CLOSED UNKNOWN SINGLE UNDEFINED 1
SQL>
3. Open the keystore In primary database.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1 ;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------------- ------------------------- ------------------------------ -------------------- --------- --------- --------ASM +DATA/basicpr/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 1
4. To avoid opening the Keystore manually everytime, we can enable auto-login so that you dont have enter the password when we we bounce the database.
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/BASICPR/WALLET' identified by Welcome1;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------------- ------------------------- ------------------------------ -------------------- --------- --------- --------ASM +DATA/basicpr/WALLET/ OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 1
5. Create the master key for the primary database.
Current Setting
SQL> ;select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
----------------- ------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA/basicpr/WALLET/ OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE NO 1
At this step you can observe that the status column value is OPEN_NO_MASTER_KEY when you query V$ENCRYPTION_WALLET. This is because the master key is not yet created. Below is the command to create a master key.
Now you can create the Master key using " ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome1 WITH BACKUP";
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome1 WITH BACKUP;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------------- ------------------------- ------------------------------ -------------------- --------- --------- --------
ASM +DATA/basicpr/WALLET/ OPEN AUTOLOGIN SINGLE NO 1
This completes the TDE setup on Primary .
============= END of Step One, Steps for PRIMARY database ====================
============= Start of Step One, Steps for STANDBY database ====================
Now For Standby database
1. . Add the keystore location in sqlnet.ora
on Node 1 of Standby RAC.
cp /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora.backup
vi /u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA/$ORACLE_UNQNAME/WALLET)
)
)
Repeat the above in all the nodes of the cluster
2. On Primary database, Create a temporary keystore in Filesystem and merge the existing ASM keystore into this filesystem keystore so that we can copy the key from filesystem kestore to standby database.
mkdir -p /u01/software/basicpr_mig/wallet_bakp/
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/software/basicpr_mig/wallet_bakp/' identified by Welcome1;
keystore altered.
Merge both keystore in ASM and created in file system(above) .
SQL> ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '+DATA/basicpr/WALLET/' identified by Welcome1 INTO EXISTING KEYSTORE '/u01/software/basicpr_mig/wallet_bakp/' IDENTIFIED BY Welcome1 WITH BACKUP;
keystore altered.
3) On Standby databae create below directores and Copy the files from Primary to this directory on stamdby.
On Standby
[oracle@virtual-dr-19crac1 dbs]$ mkdir -p /u01/software/basicpr_mig/wallet_bakp/
[oracle@virtual-dr-19crac1 dbs]$ cd /u01/software/basicpr_mig/wallet_bakp/
[oracle@virtual-dr-19crac1 wallet_bakp]$ pwd
On Primary
[oracle@chopvm-19crac1 dbs]$ cd /u01/software/basicpr_mig/wallet_bakp/
[oracle@chopvm-19crac1 wallet_bakp]$ pwd
/u01/software/basicpr_mig/wallet_bakp
[oracle@chopvm-19crac1 wallet_bakp]$ ls -ltr
total 8
-rw------- 1 oracle oinstall 2555 Mar 7 10:39 ewallet_2021030716392070.p12
-rw------- 1 oracle oinstall 3995 Mar 7 10:39 ewallet.p12
[oracle@chopvm-19crac1 wallet_bakp]$ scp * oracle@virtual-dr-19crac1:/u01/software/basicpr_mig/wallet_bakp/
oracle@virtual-dr-19crac1's password:
ewallet_2021030716392070.p12 100% 2555 3.2MB/s 00:00
ewallet.p12 100% 3995 7.1MB/s 00:00
4) On standby database server, we should now copy the contents from filesystem to asm directory.
Now we are going to move the keystore to ASM on standby server
4a) On Standby database, now create a keystore in ASM.
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/basicdr/WALLET/' IDENTIFIED BY Welcome1;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------- ------------------------- ------------------------------ -------------------- --------- -------- ------ASM +DATA/basicdr/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE NONE UNDEFINED 0
4b) Merge both keystore in Filesystem where we copied the keys with the ASM keystore
SQL> ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/u01/software/basicpr_mig/wallet_bakp/' identified by Welcome1 INTO EXISTING KEYSTORE '+DATA/basicdr/WALLET/' identified by Welcome1 WITH BACKUP;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------- ------------------------- ------------------------------ -------------------- --------- -------- --------- ASM +DATA/basicdr/WALLET/ CLOSED UNKNOWN SINGLE NONE UNDEFINED 0
4c) Open the Keystore in the standby database.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1;
keystore altered.
4d) You can now start the recovery and see if the standby database is able to catch up with primary.
SQL> ALTER DATABASE RECOVER managed standby database using current logfile disconnect;
4e) To avoid opening the Keystore manually everytime, we can enable auto-login so that you dont have enter the password when we we bounce the database.
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/basicdr/WALLET/' identified by Welcome1;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
-------- ------------------------- ------------------------------ -------------------- --------- -------- --------- ---------ASM +DATA/basicdr/WALLET/ OPEN PASSWORD SINGLE NONE NO 0
4f) Bounce the database so that keystore autologin can
[oracle@virtual-dr-19crac1 wallet_bakp]$ srvctl stop database -d basicdr
[oracle@virtual-dr-19crac1 wallet_bakp]$ srvctl start database -d basicdr
[oracle@virtual-dr-19crac1 wallet_bakp]$ sqlplus / as sysdba
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------- ------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM +DATA/basicdr/WALLET/ OPEN AUTOLOGIN SINGLE NONE NO 0
4g) Finally start the recovery process again and check to see if the MRP proccess is applying the redo data.
ALTER DATABASE RECOVER managed standby database using current logfile disconnect;
select inst_id,process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process='MRP0';
select a.thread#, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived,max(a.sequence#) applied, (select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max(a.sequence#)gap from v$archived_log a where a.applied='YES' group by a.thread# order by thread#;
============= END of Step One, Steps for STANDBY database ====================
Test Case: In PRIMARY database, Lets create a ENCRYPTED tablespaces and create a table and insert a row and switch the logfile and then on STANDBY database, check to see if the table comes by and if we can see the data in that encrypted table.
SQL> CREATE TABLESPACE ENCRYPTED_TS DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON maxsize 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> select tablespace_name,encrypted from dba_tablespaces;
TABLESPACE_NAME ENCRYPTED
-------------------------- ----------------------- ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
UNDOTBS2 NO
TBS_NAME YES
7 rows selected.
SQL> create table encrypt (name varchar2(100)) tablespace encrypted_ts;
Table created.
SQL> insert into encrypt values ('This tablepace is Encrypted');
1 row created.
SQL> commit;
Commit complete.
Switch couple of logfiles.
SQL> alter system switch all logfiles;
System altered.
SQL> alter system switch all logfiles;
System altered.
On now on standby check if the table is replicated and if you can access it.
SQ> select * from encrypt
NAME
-------------------------------------------This tablespaces is Encrypted
Hope this doc helps you.
Thanks
Sam!
======================================================================
No comments:
Post a Comment