Showing posts with label oracle keyvault. Show all posts

Saturday, December 28, 2024

Configure SNMP based remote monitoring on Oracle Key Vault appliance 21.9

Objective: Oracle Key Vault SNMP Monitoring Setup Routine

Reference: https://docs.oracle.com/en/database/oracle/key-vault/21.9/okvag/monitoring.html#GUID-95FA7828-A008-4CD4-89F9-20F8C01CE1E1

YouTube Video:

Monitoring OKV is important, even if your in multimaster with geographically distributed 16 node cluster etc..

SNMP allows OKV to be monitored by a remote monitrong server without any additional tools or software installed in the OKV appliance

OKV uses snmp version 3 for the monitoring configuration

version 3 bring itself with encryption for the authentication and communication with monitoring server, so no intruder knows what is beinng exchanged

Setup needs or prereqs:

Remote monitoring server: Vagrant box with grafana and prometheus

Mandatory appliance: OKV

Step 1) Goto OKV console and login using sysadmin credentials

Step 2) Goto system tab -> settings -> snmp

Step 3) Enter the details requested

You have 2 choices, configure snmp for the local node or for the cluster

snmp access is granted to all the IPs or restricted only to list of IPs [we say all]

Enter snmp user

Enter snmp password

Click save

Step 4) Perform snmpwalk or snmpget from the remote monitoring server

a. Ensure you login to the remote monitoring host

b. Ensure UCD-SNMP-MIB is installed on the remote host

root@ubuntu-bionic:~# ls -altr /usr/share/snmp/mibs

total 320

-rw-r--r-- 1 root root 49561 Dec 8 2014 UCD-SNMP-MIB.txt

Other directories to inspect:

/root/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

c. Query the object ID for an Oracle Key Vault-supported SNMP Management Information Base (MIB) variable.

For example, suppose you wanted to track the number of processes running for the SNMP host.

You can use a third-party SNMP client utility to query the status of the KMIP MIB whose object ID is 1.3.6.1.4.1.2021.2, as follows:

third_party_snmp_client_command -v 3 OKV_IP_address -u SNMP_user -a SHA -A SNMP_password -x AES -X SNMP_password -l authPriv iso.3.6.1.4.1.2021.2.1.2

Actual:

snmpwalk -v 3 192.168.194.122 -u snmpuser -a SHA -A "User_Snmp_54321" -x AES -X "User_Snmp_54321" -l authPriv iso.3.6.1.4.1.2021.2.1.2

Result:

root@ubuntu-bionic:~# snmpwalk -v 3 192.168.194.122 -u snmpuser -a SHA -A "User_Snmp_54321" -x AES -X "User_Snmp_54321" -l authPriv 1.3.6.1.2.1.2.2.1.7

snmpwalk: Authentication failure (incorrect password, community or key)

Configure SMTP mail alerts on Oracle Key Vault appliance 21.9

Objective: Oracle Key Vault SMTP configuration routine

YouTube Video:

Step 1) Go to https://myaccount.google.com/apppasswords

and then generate a new app password. No need to store this at all as per gmail. In case needed you can regenerate.

This is a OTP kind of password, to let google know the incoming connection was kicked off by you.

Step 2) Login to OKV console as sysadmin

Goto System -> Settings -> Email

Step 3) Enter the requested details

here for example I used gmail to cofigure mail setup.

SMTP server: smtp.gmail.com

SMTP port: 25, 465, 587 [we will use ssl, so 465]

Name: OKVNode1

From Address: oracledbaplanner@gmail.com

Require Secure Connection: select

Require Credentials: Select

Enter the username as gmail: oracledbaplanner@gmail.com

Enter the password you generated in step 1: .....wbzkukyobqziu

Send Test email: Select TEST

Step 4) Now go to reports and see if the email was successfully sent

Goto Reports -> System Report -> Notification reports -> you should see the test message result

Tuesday, September 24, 2024

OKV Implementation: Reverse migrate from OKV to TDE File (rollback testing)

Reverse migrate from OKV to TDE File

YouTube Video:

1. note down current config

set lines 1200 pages 30000 colsep , time on timing on trim on trims on long 20000

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

col KEY_ID for a40

col TAG for a40

col CREATOR for a40

col USER for a20

col CREATOR_DBNAME for a10

col CREATOR_INSTANCE_NAME for a10

col CREATOR_PDBNAME for a20

col CREATOR_PDBGUID for a40

col ACTIVATING_DBNAME for a10

col ACTIVATING_INSTANCE_NAME for a10

col ACTIVATING_PDBNAME for a20

col ACTIVATING_PDBGUID for a40

col wrl_parameter for a70

select * from v$encryption_wallet;

select * from v$encryption_keys order by creation_time,ACTIVATION_TIME;

select tablespace_name,encrypted from dba_tablespaces;

actual output:

17:03:30 SQL> select * from v$encryption_wallet;

WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,KEYSTORE,FULLY_BAC, CON_ID

--------------------,----------------------------------------------------------------------,------------------------------,--------------------,---------,--------,---------,----------

FILE ,/u01/app/oracle/admin/GGTGT04T/wallet/tde/ ,OPEN_NO_MASTER_KEY ,LOCAL_AUTOLOGIN ,SECONDARY,NONE ,UNDEFINED, 0

OKV , ,OPEN ,OKV ,PRIMARY ,NONE ,UNDEFINED, 0

Elapsed: 00:00:00.01

17:03:34 SQL> select * from v$encryption_keys order by creation_time,ACTIVATION_TIME;

KEY_ID ,TAG ,CREATION_TIME ,ACTIVATION_TIME ,CREATOR ,CREATOR_ID,USER , USER_ID,KEY_USE ,KEYSTORE_TYPE ,ORIGIN ,BACKED_UP,CREATOR_DB,CREATOR_DBID,CREATOR_IN,CREATOR_INSTANCE_NUMBER,CREATOR_INSTANCE_SERIAL,CREATOR_PDBNAME ,CREATOR_PDBID,CREATOR_PDBUID,CREATOR_PDBGUID ,ACTIVATING,ACTIVATING_DBID,ACTIVATING,ACTIVATING_INSTANCE_NUMBER,ACTIVATING_INSTANCE_SERIAL,ACTIVATING_PDBNAME ,ACTIVATING_PDBID,ACTIVATING_PDBUID,ACTIVATING_PDBGUID , CON_ID

----------------------------------------,----------------------------------------,---------------------------------------------------------------------------,---------------------------------------------------------------------------,----------------------------------------,----------,--------------------,----------,----------,-----------------,-----------------------------------------,---------,----------,------------,----------,-----------------------,-----------------------,--------------------,-------------,--------------,----------------------------------------,----------,---------------,----------,--------------------------,--------------------------,--------------------,----------------,-----------------,----------------------------------------,----------

0624C275A122E04FA4BFAAF486B4B27B5E , ,26-AUG-23 12.21.51.228487 PM +01:00 ,26-AUG-23 12.21.51.228490 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,NO ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0

06B658AFFFACFC4F3CBFE0133497195A3C , ,06-SEP-24 09.51.29.763257 PM +01:00 ,06-SEP-24 09.51.29.763260 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,NO ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0

Elapsed: 00:00:46.57

17:04:31 SQL> 17:04:31 SQL> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME ,ENC

------------------------------,---

SYSTEM ,NO

SYSAUX ,NO

UNDOTBS1 ,NO

TEMP ,NO

USERS ,NO

ENCRYPT_TS1 ,YES

GG_DATA_TGT ,NO

7 rows selected.

Elapsed: 00:00:00.04

17:05:20 SQL> select count(1) from encvault_test.encrypt_tab1;

COUNT(1)

----------

Elapsed: 00:00:00.07

17:05:30 SQL> select owner,table_name,tablespace_name from dba_tables where owner='ENCVAULT_TEST' and table_name='ENCRYPT_TAB1';

OWNER ,TABLE_NAME ,TABLESPACE_NAME

--------------------------------------------------------------------------------------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------,------------------------------

ENCVAULT_TEST ,ENCRYPT_TAB1 ,ENCRYPT_TS1

Elapsed: 00:00:00.08

17:06:07 SQL> sho parameter wallet_root

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

wallet_root ,string ,/u01/app/oracle/admin/GGTGT04T

, ,/wallet

17:06:33 SQL> sho parameter tde_Conf

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

tde_configuration ,string ,KEYSTORE_CONFIGURATION=OKV|FIL

, ,E

17:06:37 SQL>

2. Validate data access

select count(1) from encvault_test.encrypt_tab1;

3. Check the WALLET_ROOT and ensure note down its content (may be backup the $WALLET_ROOT/tde)

actual output:

[oracle@vcentos79-oracle-ggtgt tde]$ ls -altr /u01/app/oracle/admin/GGTGT04T/wallet

total 12

-rw-------. 1 oracle oinstall 3891 Aug 26 2023 cwallet.sso

-rw-------. 1 oracle oinstall 3848 Aug 26 2023 ewallet.p12

drwxr-xr-x. 5 oracle oinstall 51 Aug 26 2023 ..

lrwxrwxrwx. 1 oracle oinstall 19 Sep 6 21:50 okv -> /u01/app/oracle/okv

drwxr-xr-x. 2 oracle oinstall 4096 Sep 6 21:51 tde_2_Remove

drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 tde_seps

drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 tde

drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 .

[oracle@vcentos79-oracle-ggtgt tde]$ ls -altr /u01/app/oracle/admin/GGTGT04T/wallet/tde

total 4

drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 .

-rw-------. 1 oracle oinstall 3925 Sep 18 15:00 cwallet.sso

drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 ..

[oracle@vcentos79-oracle-ggtgt tde]$ ls -altr /u01/app/oracle/admin/GGTGT04T/wallet/tde_seps/

total 4

drwxr-xr-x. 2 oracle oinstall 25 Sep 18 15:00 .

-rw-------. 1 oracle oinstall 3925 Sep 18 15:00 cwallet.sso

drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 ..

[oracle@vcentos79-oracle-ggtgt tde]$ exit

exit

17:07:05 SQL>

4. Ensure wallet_root and tde_configuration parameters are proper

5. As sysdba, perform the reverse migration command

ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY

IDENTIFIED BY TDE_wallet_password

REVERSE MIGRATE USING "external_key_manager_password" WITH BACKUP;

Notes:

ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY

IDENTIFIED BY oracle

REVERSE MIGRATE USING "null" WITH BACKUP;

Error:

ORA-46624 - so we need to copy the ewallet.p12 from old tde directory to here.

ORA-28416 - we need to alter the tdE_configuration from 'OKV|FILE' to 'FILE|OKV'

ORA-46631 - error insists we should have "WITH BACKUP" keyword in the command

Actual output:

17:12:52 SQL> show parameter tde_co

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

tde_configuration ,string ,KEYSTORE_CONFIGURATION=OKV|FIL

, ,E

17:13:02 SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE|OKV';

System altered.

Elapsed: 00:00:00.02

17:13:46 SQL> show parameter tde_co

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

tde_configuration ,string ,KEYSTORE_CONFIGURATION=FILE|OK

, ,V

[oracle@vcentos79-oracle-ggtgt tde]$ cp ../tde_2_Remove/ewallet.p12 .

[oracle@vcentos79-oracle-ggtgt tde]$ ls -altr

total 8

-rw-------. 1 oracle oinstall 3925 Sep 18 15:00 cwallet.sso

drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 ..

-rw-------. 1 oracle oinstall 3995 Sep 18 17:15 ewallet.p12

drwxr-xr-x. 2 oracle oinstall 44 Sep 18 17:15 .

[oracle@vcentos79-oracle-ggtgt tde]$

17:15:30 SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY

IDENTIFIED BY oracle

REVERSE MIGRATE USING "null" WITH BACKUP;17:15:36 2 17:15:36 3

keystore altered.

Elapsed: 00:00:01.56

17:15:39 SQL>

[oracle@vcentos79-oracle-ggtgt tde]$ ls -altr

total 20

drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 ..

-rw-------. 1 oracle oinstall 3995 Sep 18 17:15 ewallet_2024091816153779.p12

drwxr-xr-x. 2 oracle oinstall 80 Sep 18 17:15 .

-rw-------. 1 oracle oinstall 5259 Sep 18 17:15 ewallet.p12 <<< file grew in size

-rw-------. 1 oracle oinstall 5304 Sep 18 17:15 cwallet.sso

[oracle@vcentos79-oracle-ggtgt tde]$

6. note down current config

set lines 1200 pages 30000 colsep , time on timing on trim on trims on long 20000

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

col KEY_ID for a40

col TAG for a40

col CREATOR for a40

col USER for a20

col CREATOR_DBNAME for a10

col CREATOR_INSTANCE_NAME for a10

col CREATOR_PDBNAME for a20

col CREATOR_PDBGUID for a40

col ACTIVATING_DBNAME for a10

col ACTIVATING_INSTANCE_NAME for a10

col ACTIVATING_PDBNAME for a20

col ACTIVATING_PDBGUID for a40

col wrl_parameter for a70

select * from v$encryption_wallet;

select * from v$encryption_keys order by creation_time,ACTIVATION_TIME;

select tablespace_name,encrypted from dba_tablespaces;

Actual output:

17:19:22 SQL> select * from v$encryption_wallet;

WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,KEYSTORE,FULLY_BAC, CON_ID

--------------------,----------------------------------------------------------------------,------------------------------,--------------------,---------,--------,---------,----------

FILE ,/u01/app/oracle/admin/GGTGT04T/wallet/tde/ ,OPEN ,PASSWORD ,PRIMARY ,NONE ,NO , 0

OKV , ,CLOSED ,UNKNOWN ,SECONDARY,NONE ,UNDEFINED, 0

Elapsed: 00:00:00.00

17:19:39 SQL> select * from v$encryption_keys order by creation_time,ACTIVATION_TIME;

ASTCdaEi4E+kv6r0hrSye14AAAAAAAAAAAAAAAAA, ,26-AUG-23 12.21.51.228487 PM +01:00 ,26-AUG-23 12.21.51.228490 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,YES ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGSRC04T , 4198404018,GGSRC04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0

AAAAAAAAAAAA , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

AeTxS3+/p094v+Pov24n5BkAAAAAAAAAAAAAAAAA, ,18-SEP-24 05.15.37.795789 PM +01:00 ,18-SEP-24 05.15.37.795795 PM +01:00 ,SYS , 0,SYS , 0,TDE ,SOFTWARE KEYSTORE,LOCAL ,NO ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 ,GGTGT04T , 154373397,GGTGT04T , 1, 4294967295, , 0, 0,00000000000000000000000000000000 , 0

AAAAAAAAAAAA , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Elapsed: 00:00:00.00

17:19:56 SQL> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME ,ENC

------------------------------,---

SYSTEM ,NO

SYSAUX ,NO

UNDOTBS1 ,NO

TEMP ,NO

USERS ,NO

ENCRYPT_TS1 ,YES

GG_DATA_TGT ,NO

7 rows selected.

Elapsed: 00:00:00.00

7. Validate data access

select count(1) from encvault_test.encrypt_tab1;

17:21:33 SQL> select count(1) from encvault_test.encrypt_tab1;

COUNT(1)

----------

Elapsed: 00:00:00.00

17:22:03 SQL>

8. To create auto_login wallet:

command:

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/GGTGT04T/wallet/tde' IDENTIFIED BY "oracle";

actual output:

17:29:07 SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/GGTGT04T/wallet/tde' IDENTIFIED BY "oracle";

keystore altered.

Elapsed: 00:00:00.19

17:29:32 SQL>

[oracle@vcentos79-oracle-ggtgt tde]$ ls -altr

total 28

drwxr-xr-x. 5 oracle oinstall 102 Sep 18 15:16 ..

-rw-------. 1 oracle oinstall 3995 Sep 18 17:15 ewallet_2024091816153779.p12

-rw-------. 1 oracle oinstall 5259 Sep 18 17:15 ewallet.p12

-rw-------. 1 oracle oinstall 5304 Sep 18 17:15 cwallet.sso_old

-rw-------. 1 oracle oinstall 5304 Sep 18 17:29 cwallet.sso <<< file updated

drwxr-xr-x. 2 oracle oinstall 103 Sep 18 17:29 .

[oracle@vcentos79-oracle-ggtgt tde]$

17:31:08 SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

17:31:38 SQL> 17:31:38 SQL>

17:31:39 SQL> startup

ORACLE instance started.

Total System Global Area,3221222464,bytes

Fixed Size , 8901696,bytes

Variable Size ,1107296256,bytes

Database Buffers ,2097152000,bytes

Redo Buffers , 7872512,bytes

Database mounted.

Database opened.

17:31:52 SQL> select * from v$encryption_wallet;

WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,KEYSTORE,FULLY_BAC, CON_ID

--------------------,----------------------------------------------------------------------,------------------------------,--------------------,---------,--------,---------,----------

FILE ,/u01/app/oracle/admin/GGTGT04T/wallet/tde/ ,OPEN ,AUTOLOGIN ,PRIMARY ,NONE ,NO , 0

OKV , ,CLOSED ,UNKNOWN ,SECONDARY,NONE ,UNDEFINED, 0

Elapsed: 00:00:00.09

17:31:59 SQL> select * from v$encryption_keys order by creation_time,ACTIVATION_TIME;

AAAAAAAAAAAA , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Elapsed: 00:00:00.03

17:32:18 SQL> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME ,ENC

------------------------------,---

SYSTEM ,NO

SYSAUX ,NO

UNDOTBS1 ,NO

TEMP ,NO

USERS ,NO

ENCRYPT_TS1 ,YES

GG_DATA_TGT ,NO

7 rows selected.

Elapsed: 00:00:00.02

17:32:27 SQL> select count(1) from encvault_test.encrypt_tab1;

COUNT(1)

----------

Elapsed: 00:00:00.01

17:32:33 SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@vcentos79-oracle-ggtgt tde]$

Let us learn Oracle Database Administration skills

Saturday, December 28, 2024

Configure SNMP based remote monitoring on Oracle Key Vault appliance 21.9

Objective: Oracle Key Vault SNMP Monitoring Setup Routine

YouTube Video:

Setup needs or prereqs:

Step 1) Goto OKV console and login using sysadmin credentials

Step 2) Goto system tab -> settings -> snmp

Step 3) Enter the details requested

Step 4) Perform snmpwalk or snmpget from the remote monitoring server

Configure SMTP mail alerts on Oracle Key Vault appliance 21.9

Objective: Oracle Key Vault SMTP configuration routine

Tuesday, September 24, 2024

OKV Implementation: Reverse migrate from OKV to TDE File (rollback testing)

1. note down current config

actual output:

2. Validate data access

3. Check the WALLET_ROOT and ensure note down its content (may be backup the $WALLET_ROOT/tde)

actual output:

4. Ensure wallet_root and tde_configuration parameters are proper

5. As sysdba, perform the reverse migration command

Notes:

Actual output:

6. note down current config

Actual output:

7. Validate data access

8. To create auto_login wallet:

actual output:

OKV platform certificate rotation - pitfall , awareness!!!!