Tuesday, May 18, 2021

TDE queries to view your configuration

This post contains some of the scripts I have been using on my TDE encrypted database to see the big picture of what is being encrypted by what key.

1) Wallet information

The first script I put together will list the status of wallets for all tenants on all nodes. This will give you the wallet location, type of wallet, united, etc.

set linesize 170;
set pagesize 100
column wrl_parameter format a50
column wrl_type heading 'Type' format a10
column status heading 'Status' format a30
column fully_backed_up heading 'Backed Up' format a10
column pdb_name heading 'PDB Name' format a10

break on inst_id skip 1
 select inst_id,b.name pdb_name,wrl_type,
wrl_parameter,
status,wallet_type,
keystore_mode,
fully_backed_up
from gv$encryption_wallet a,v$containers b
where a.con_id = b.con_id(+)
order by inst_id,b.name,wrl_type;

Below is the output of this script for my single node, local wallet database.

  INST_ID PDB Name   Type	 WRL_PARAMETER					    Status			   WALLET_TYPE		KEYSTORE Backed Up
---------- ---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
	 1 CDB$ROOT   FILE	 /home/oracle/app/oracle/admin/tdecdb/wallet/tde/   OPEN			   AUTOLOGIN		NONE	 NO
	   PDB$SEED   FILE							    OPEN			   AUTOLOGIN		UNITED	 NO
	   PDBTDE1    FILE							    OPEN			   AUTOLOGIN		UNITED	 NO
	   PDBTDE2    FILE							    OPEN			   AUTOLOGIN		UNITED	 NO
	   PDBTDE3    FILE							    OPEN			   AUTOLOGIN		UNITED	 NO

Below is a the output from a 4 node cluster with OKV configured.


INST_ID PDB Name   Type       WRL_PARAMETER                                 Status               WALLET_TYPE      KEYSTORE Backed Up
------ ---------- ---------- ------------------------------------         --------------      ----------------    ------------- -------------------- -------- ----------
     1 CDB$ROOT   FILE       /u02/app/oracle/admin/jckey/wallet/tde/      OPEN_NO_MASTER_KEY  AUTOLOGIN            NONE     UNDEFINED
       CDB$ROOT   OKV                                                     OPEN                OKV                  NONE     UNDEFINED
       JCKPDB     FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       JCKPDB     OKV                                                     OPEN                OKV                  UNITED   UNDEFINED
       PDB$SEED   FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       PDB$SEED   OKV                                                     OPEN                OKV                  UNITED   UNDEFINED

     2 CDB$ROOT   FILE       /u02/app/oracle/admin/jckey/wallet/tde/      OPEN_NO_MASTER_KEY  AUTOLOGIN            NONE     UNDEFINED
       CDB$ROOT   OKV                                                     OPEN                OKV                  NONE     UNDEFINED
       JCKPDB     FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       JCKPDB     OKV                                                     OPEN                OKV                  UNITED   UNDEFINED
       PDB$SEED   FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       PDB$SEED   OKV                                                     OPEN                OKV                  UNITED   UNDEFINED

     3 CDB$ROOT   FILE       /u02/app/oracle/admin/jckey/wallet/tde/      OPEN_NO_MASTER_KEY  AUTOLOGIN            NONE     UNDEFINED
       CDB$ROOT   OKV                                                     OPEN                OKV                  NONE     UNDEFINED
       JCKPDB     FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       JCKPDB     OKV                                                     OPEN                OKV                  UNITED   UNDEFINED
       PDB$SEED   FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       PDB$SEED   OKV                                                     OPEN                OKV                  UNITED   UNDEFINED

     4 CDB$ROOT   FILE       /u02/app/oracle/admin/jckey/wallet/tde/      OPEN_NO_MASTER_KEY  AUTOLOGIN            NONE     UNDEFINED
       CDB$ROOT   OKV                                                     OPEN                OKV                  NONE     UNDEFINED
       JCKPDB     FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       JCKPDB     OKV                                                     OPEN                OKV                  UNITED   UNDEFINED
       PDB$SEED   FILE                                                    OPEN_NO_MASTER_KEY  AUTOLOGIN            UNITED   UNDEFINED
       PDB$SEED   OKV                                                     OPEN                OKV                  UNITED   UNDEFINED

2) Tablespace information

This script will list the tablespaces, if the tablespace is encrypted, and what the key is.

set linesize 170
set pagesize 50
column pdb_name heading "PDB Name" format a10
column tablespace_name heading "Tablespace Name" format a15
column masterkeyid_base64 heading "Master Key ID" format a25
column encrypted heading "Enc." format a5
column mkid heading "Key ID" format a35
column encrypted_key heading "tablespace Encryt key (trunc)" format a30


break on pdb_name skip 1

select pdb_name, 
       tablespace_name,encrypted,
       utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
,mkid,encrypted_key
 FROM (select t.name tablespace_name,
              z.name pdb_name,
              t.con_id, encrypted,
              RAWTOHEX(x.mkid) mkeyid,
               mkid,
             substr(encryptedkey,1,30) encrypted_key
        from v$tablespace t, 
             x$kcbtek x,
             v$containers z,
             cdb_tablespaces q 
        where t.ts#=x.ts# 
          and t.con_id=x.con_id 
          and t.con_id=z.con_id
          and t.con_id = q.con_id
          and t.name = q.tablespace_name)
order by pdb_name,encrypted,tablespace_name;

Below is the output from my database.

PDB Name   Tablespace Name Enc.          Master Key ID              Key ID                             tablespace Encryt key (trunc)
---------- --------------- -----         ------------------------- ----------------------------------- ------------------------------
CDB$ROOT   SYSAUX	       NO	 AQbOELhZAk9Dv8A2mADBKQQ=  06CE10B859024F43BFC0369800C12904    9C21DCFF8CB7DCC6E038239DD07D3D
	   SYSTEM	       NO	 AQbOELhZAk9Dv8A2mADBKQQ=  06CE10B859024F43BFC0369800C12904    9C21DCFF8CB7DCC6E038239DD07D3D
	   TEMP 	       NO	 AQbOELhZAk9Dv8A2mADBKQQ=  06CE10B859024F43BFC0369800C12904    9C21DCFF8CB7DCC6E038239DD07D3D
	   UNDOTBS1	       NO	 AQbOELhZAk9Dv8A2mADBKQQ=  06CE10B859024F43BFC0369800C12904    9C21DCFF8CB7DCC6E038239DD07D3D
	   USERS	       YES	 AQbOELhZAk9Dv8A2mADBKQQ=  06CE10B859024F43BFC0369800C12904    9C21DCFF8CB7DCC6E038239DD07D3D

PDBTDE1    SYSAUX	       NO	 AYQysCoXXk+Nv/Q//9sUAV4=  8432B02A175E4F8DBFF43FFFDB14015E    4D7007D0FFFCB3F2702233BDD2702A
    	   SYSTEM	       NO	 AYQysCoXXk+Nv/Q//9sUAV4=  8432B02A175E4F8DBFF43FFFDB14015E    4D7007D0FFFCB3F2702233BDD2702A
    	   TEMP 	       NO	 AYQysCoXXk+Nv/Q//9sUAV4=  8432B02A175E4F8DBFF43FFFDB14015E    4D7007D0FFFCB3F2702233BDD2702A
    	   UNDOTBS1	       NO	 AYQysCoXXk+Nv/Q//9sUAV4=  8432B02A175E4F8DBFF43FFFDB14015E    4D7007D0FFFCB3F2702233BDD2702A
    	   USERS	       YES	 AYQysCoXXk+Nv/Q//9sUAV4=  8432B02A175E4F8DBFF43FFFDB14015E    4D7007D0FFFCB3F2702233BDD2702A

PDBTDE2    SYSAUX	       NO	 AegHs2QPk09xv0HVO3B1alQ=  E807B3640F934F71BF41D53B70756A54    C3F9A04600AFE07F023589C0DE0ED8
    	   SYSTEM	       NO	 AegHs2QPk09xv0HVO3B1alQ=  E807B3640F934F71BF41D53B70756A54    C3F9A04600AFE07F023589C0DE0ED8
    	   TEMP 	       NO	 AegHs2QPk09xv0HVO3B1alQ=  E807B3640F934F71BF41D53B70756A54    C3F9A04600AFE07F023589C0DE0ED8
    	   UNDOTBS1	       NO	 AegHs2QPk09xv0HVO3B1alQ=  E807B3640F934F71BF41D53B70756A54    C3F9A04600AFE07F023589C0DE0ED8
    	   USERS	       YES	 AegHs2QPk09xv0HVO3B1alQ=  E807B3640F934F71BF41D53B70756A54    C3F9A04600AFE07F023589C0DE0ED8

PDBTDE3    SYSAUX	       NO	 AW5TJ43d8E+ZvxD8A1YhdcM=  6E53278DDDF04F99BF10FC03562175C3    6911A4106D914681528706E03202E6
	   SYSTEM	       NO	 AW5TJ43d8E+ZvxD8A1YhdcM=  6E53278DDDF04F99BF10FC03562175C3    6911A4106D914681528706E03202E6
    	   TEMP 	       NO	 AW5TJ43d8E+ZvxD8A1YhdcM=  6E53278DDDF04F99BF10FC03562175C3    6911A4106D914681528706E03202E6
    	   UNDOTBS1	       NO	 AW5TJ43d8E+ZvxD8A1YhdcM=  6E53278DDDF04F99BF10FC03562175C3    6911A4106D914681528706E03202E6
    	   USERS	       YES	 AW5TJ43d8E+ZvxD8A1YhdcM=  6E53278DDDF04F99BF10FC03562175C3    6911A4106D914681528706E03202E6

3) Wallet Contents

Now let's take a look at what's in my wallet.

set linesize 170;


set linesize 170
set pagesize 50
column pdb_name heading "PDB Name" format a15
column tag heading "Tag" format a20
column Creation_time heading "Key Creation Time" format a18
column key_id heading "Master Key ID" format a55
column activation_time heading "Key Act. Time" format a18
column origin heading "Origin" format a10
column mkid heading "Key ID" format a35

select substr(key_id,1,999) key_id,
tag,
       decode(b.name,null,'CDB',b.name) pdb_name,
keystore_type,origin,
to_char(a.creation_time,'mm/dd/yyyy hh24:mi') creation_time,
to_char(activation_time,'mm/dd/yyyy hh24:mi') activation_time
from v$encryption_keys a,v$containers b
   where a.con_id=b.con_id(+)
order by creation_time,pdb_name;

Below you can see the master key ID for each CDB/PDB and information about when it was created.

Master Key ID                                           Tag                  PDB Name        KEYSTORE_TYPE     Origin     Key Creation Time  Key Act. Time
------------------------------------------------------- -------------------- --------------- ----------------- ---------- ------------------ ------------------
ASd1jY/loU8Bv6HuSfZZFqAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	cdbroot_first_key    CDB$ROOT	     SOFTWARE KEYSTORE LOCAL	  06/28/2021 17:46   06/28/2021 17:46
AQbOELhZAk9Dv8A2mADBKQQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	cdbroot_second_key		     SOFTWARE KEYSTORE LOCAL	  06/28/2021 18:46   06/28/2021 18:46

AfhjvV/z/U9ev5bICBLYV1MAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	pdbtde1_firstkey     PDBTDE1	     SOFTWARE KEYSTORE LOCAL	  06/28/2021 17:53   06/28/2021 17:53
AYQysCoXXk+Nv/Q//9sUAV4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA	pdbtde1_second_key		     SOFTWARE KEYSTORE LOCAL	  06/28/2021 18:50   06/28/2021 18:50

AVXCNjl3f0+Av+/osXobX2sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	pdbtde2_firstkey     PDBTDE2	     SOFTWARE KEYSTORE LOCAL	  06/28/2021 17:54   06/28/2021 17:54
AegHs2QPk09xv0HVO3B1alQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	pdbtde2_second_key		     SOFTWARE KEYSTORE LOCAL	  06/28/2021 18:50   06/28/2021 18:50

Ab1/+jaPck+Ev6rhmBKtxXEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	pdbtde3_firstkey     PDBTDE3	     SOFTWARE KEYSTORE LOCAL	  06/28/2021 17:54   06/28/2021 17:54
AW5TJ43d8E+ZvxD8A1YhdcMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	pdbtde1_second_key		     SOFTWARE KEYSTORE LOCAL	  06/28/2021 18:50   06/28/2021 18:50

NOTE: I rotated my master key, and you can see both keys.. Adding a tag to the key helps identify the key also.

4) Control file Contents

This query looks at the x$jcbdbk table to determine the master key(s) currently in use.

set linesize 170;


set linesize 170
set pagesize 50
column pdb_name heading "PDB Name" format a15
column masterkeyid_base64 heading "Master Key ID" format a25
column mkid heading "Key ID" format a35
break

select name pdb_name,
        mkid, 
        utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64 
      FROM (select con_id,
             mkid, 
             RAWTOHEX(mkid) mkeyid from x$kcbdbk) a,v$containers b
      where a.con_id=b.con_id;

PDB Name        Key ID                              Master Key ID
--------------- ----------------------------------- -------------------------
CDB$ROOT        06CE10B859024F43BFC0369800C12904    AQbOELhZAk9Dv8A2mADBKQQ=

PDB$SEED        00000000000000000000000000000000    AQAAAAAAAAAAAAAAAAAAAAA=

PDBTDE1         8432B02A175E4F8DBFF43FFFDB14015E    AYQysCoXXk+Nv/Q//9sUAV4=

PDBTDE2         E807B3640F934F71BF41D53B70756A54    AegHs2QPk09xv0HVO3B1alQ=

PDBTDE3         6E53278DDDF04F99BF10FC03562175C3    AW5TJ43d8E+ZvxD8A1YhdcM=

Conclusion :

By looking at the queries above you should have a better of idea of how the Master encryption key ties to the tablespace encryption.

You can also see what happens when you rotate the master key, and how it affects the tablespaces.

Monday, May 10, 2021

Configuring ExaCC backups of an Oracle Database

This post covers how to configure your backups of an ExaCC database beyond the web interface.

First off the documentation can be found below, along with using the "--help" option at the command line with "bkup_api"

Configuration - https://docs.oracle.com/en/cloud/cloud-at-customer/exadata-cloud-at-customer/exacc/customize-backup-configuration-using-bkup_api.html

Backup execution - https://docs.oracle.com/en/cloud/cloud-at-customer/exadata-cloud-at-customer/exacc/create-demand-backup.html#GUID-2370EA04-3141-4D02-B328-5EE9A10F66F2

Step #1 - Configure backup settings in ExaCC

The next step is to configure my database to be backed up using the tooling. This is pretty straightforward. I click on the "edit backup" button and fill in the information for my database and save it. In my case I am using ZFS, and the need to make sure that I change my container to the container where the ZFS is configured.

NOTE : The backup strategy is a Weekly L0 (full) backup every Sunday, and a daily L1 (differential incremental backup) on all other days. The time the backup is scheduled can be found in either the backup settings, or by looking at the crontab file.

Then I just wait until I see complete. If I click on the work requests, I can see the progress until it's finished.

Step #2 - Update the settings to use my RMAN catalog.

First I need to get what the current settings are for my database (dbsg2) and save them in a config file so I can update them.

I log into the first node, and su to root.

Once there I execute "get config --all" and save all the settings to a file that I can update.

NOTE : I an creating a new file under the bakup_api/cfg directory to make it easy to find.

$ sudo su -
Last login: Thu May  6 11:43:46 PDT 2021 on pts/0
[root@ecc ~]## /var/opt/oracle/bkup_api/bkup_api get config --all --file=/var/opt/oracle/bkup_api/cfg/dbsg2.cfg --dbname dbsg2
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : get_config
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_92303612_20210506125612.006275.log
File /var/opt/oracle/bkup_api/cfg/dbsg2.cfg created

Now I am going to edit it and make some changes.

I changed to RMAN catalog settings to use my catalog.

NOTE: The entry has to be the connect string, not a tnsnames.ora entry.

#### This section is applicable when using a rman catalog ####
# Enables RMAN catalog. Can be set to yes or no.
bkup_use_rcat=yes

## Below parameters are required if rman catalog is enabled
# RMAN catalog user
bkup_rcat_user=rco


# RMAN catalog password
#bkup_rcat_passwd=RMan19c#_

# RMAN catalog conn string
bkup_rcat_conn=ecc-scan.bgrenn.com:1521:rmanpdb.bgrenn.com

Now I am going to commit (set) the changes using the "set config" command

# /var/opt/oracle/bkup_api/bkup_api set config --file=/var/opt/oracle/bkup_api/cfg/dbsg2.cfg --dbname dbsg2 
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : set_config
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_b800281f_20210506130824.084259.log
cfgfile : /var/opt/oracle/bkup_api/cfg/dbsg2.cfg
Using configuration file: /var/opt/oracle/bkup_api/cfg/dbsg2.cfg
API::Parameters validated.
UUID d0845ea0aea611eb98fb52540068a695 for this set_config(configure-backup)
** process started with PID: 86143
** see log file for monitor progress
-------------------------------------

And after a few minutes, I am going to check and make sure it was successful by using the configure_status command


/var/opt/oracle/bkup_api/bkup_api configure_status --dbname dbsg2
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : configure_status
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_fa81558e_20210507060019.504831.log
* Last registered operation: 2021-05-07 12:58:41 UTC 
* Configure backup status: finished
**************************************************
* API History: API steps
  API:: NEW PROCESS 120531
*
* RETURN CODE:0
##################################################

Everything looks good ! It removed my configuration file (which is good because it had the password in it).

I found that 2 things happened as part of adding an RMAN catalog

The password for the RMAN catalog user is now stored in the wallet file.
There is an entry in my tnsnames file on all nodes for "CATALOG" which points to the rman catalog.

NOTE: When part of this process is to register the database with the RMAN catalog. You do not have to manually register the database in the catalog.

Step #3 - Take a manual backup

Now logged in as OPC, and becoming Root, and can run a special backup using bkup_api

# /var/opt/oracle/bkup_api/bkup_api bkup_start --dbname=dbsg2
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : bkup_start
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_9458c30f_20210510084341.430481.log
UUID 7f6622f8b1a611eb865552540068a695 for this backup
** process started with PID: 336757
** see log file for monitor progress
-------------------------------------

I can see the status while it's running

/var/opt/oracle/bkup_api/bkup_api bkup_status --dbname=dbsg2
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : bkup_status
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_46545e6f_20210510084812.014419.log
(' Warning: unable to get current configuration of:', 'catalog')
* Current backup settings:
* Last registered Bkup: 05-10 15:44 UTC API::336757:: Starting dbaas backup process
* Bkup state: running
**************************************************
* API History: API steps
  API:: NEW PROCESS 336757
  API:: Starting dbaas backup process
*
* RETURN CODE:0
##################################################

And I waited a few minutes, and I can see it was successful.

# /var/opt/oracle/bkup_api/bkup_api bkup_status --dbname=dbsg2
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : bkup_status
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_8acd03e3_20210510085129.207757.log
(' Warning: unable to get current configuration of:', 'catalog')
* Current backup settings:
* Last registered Bkup: 05-10 15:44 UTC API::336757:: Starting dbaas backup process
* Bkup state: running
**************************************************
* API History: API steps
  API:: NEW PROCESS 336757
  API:: Starting dbaas backup process
*************************************************
* Backup steps
 -> 2021-05-10 08:44:20.651787 - API:: invoked with args : -dbname=dbsg2 -uuid=7f6622f8b1a611eb865552540068a695 -level1 
 -> 2021-05-10 08:44:23.458698 - API:: Wallet is in open AUTOLOGIN state
 -> 2021-05-10 08:44:24.204793 - API:: Oracle database state is up and running
 -> 2021-05-10 08:44:25.686134 - API:: CATALOG SETTINGS 
 -> 2021-05-10 08:45:19.767284 - API:: DB instance: dbsg2
 -> 2021-05-10 08:45:19.767424 - API:: Validating the backup repository ...... 
 -> 2021-05-10 08:46:38.263401 - API::      All backup pieces are ok
 -> 2021-05-10 08:46:38.263584 - API:: Validating the TDE wallet ...... 
 -> 2021-05-10 08:46:41.842706 - API:: TDE check successful.
 -> 2021-05-10 08:46:42.446560 - API:: Performing incremental backup to shared storage
 -> 2021-05-10 08:46:42.448228 - API:: Executing rman instructions
 -> 2021-05-10 08:49:21.161884 - API:: ....... OK
 -> 2021-05-10 08:49:21.162089 - API:: Incremental backup to shared storage is Completed
 -> 2021-05-10 08:49:21.163822 - API:: Starting backup of config files
 -> 2021-05-10 08:49:21.699197 - API:: Determining the oracle database id
 -> 2021-05-10 08:49:21.726308 - API::  DBID: 2005517379
 -> 2021-05-10 08:49:22.040891 - API:: Creating directories to store config files
 -> 2021-05-10 08:49:22.085476 - API:: Enabling RAC exclusions for config files.
 -> 2021-05-10 08:49:22.114211 - API:: Compressing config files into tar files
 -> 2021-05-10 08:49:22.173842 - API:: Uploading config files to NFS location
 -> 2021-05-10 08:49:22.222493 - API:: Removing temporary location /var/opt/oracle/log/dbsg2/obkup/7f6622f8b1a611eb865552540068a695.
 -> 2021-05-10 08:49:22.224071 - API:: Config files backup ended successfully
 -> 2021-05-10 08:49:26.052494 - API:: All requested tasks are completed
*
* RETURN CODE:0
##################################################

Step #4 - Check my periodic backups

Now it's been a few days (I started on Thursday and it's now Monday).

I am going to check on the incremental backups, and the archive log backups.

There are 2 ways I can do this.

Using the bkup_api command to list the backups that have run.

# /var/opt/oracle/bkup_api/bkup_api list --dbname=dbsg2
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : list
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_eddcd4e1_20210510064145.497707.log
-> Listing all backups
  Backup Tag             Completion Date (UTC)            Type          keep    
----------------------   -----------------------      -----------    --------
   TAG20210506T123203     05/06/2021 19:32:03       full        False
   TAG20210506T131438     05/06/2021 20:14:38       incremental        False
   TAG20210507T012240     05/07/2021 08:22:40       incremental        False
   TAG20210508T012315     05/08/2021 08:23:15       incremental        False
   TAG20210509T012438     05/09/2021 08:24:38       full        False
   TAG20210510T012322     05/10/2021 08:23:22       incremental        False

Using the RMAN catalog

Backup Type         Encrypted Tag                                Backup Piece                                                 Backup Time           Day Of Week
-------------------- --------- --------------------------------- ------------------------------------------------------------ -------------------- --------------------
Full L0              YES       DBAAS_FULL_BACKUP20210506122626     /backup/dbaas_bkup_DBSG2_2005517379_0dvu5rp2_13_1          05/06/21 12:29:32    THURSDAY
Differential L1      YES       DBAAS_INCR_BACKUP20210506131110     /backup/dbaas_bkup_DBSG2_2005517379_2avu5ud1_74_1          05/06/21 13:14:18    THURSDAY
Differential L1      YES       DBAAS_INCR_BACKUP20210507011926     /backup/dbaas_bkup_DBSG2_2005517379_72vu792b_226_1         05/07/21 01:22:27    FRIDAY
Differential L1      YES       DBAAS_INCR_BACKUP20210508011939     /backup/dbaas_bkup_DBSG2_2005517379_lbvu9tf3_683_1         05/08/21 01:22:51    SATURDAY
Full L0              YES       DBAAS_FULL_BACKUP20210509011940     /backup/dbaas_bkup_DBSG2_2005517379_u3vuchr8_963_1         05/09/21 01:22:59    SUNDAY
Differential L1      YES       DBAAS_INCR_BACKUP20210510011940     /backup/dbaas_bkup_DBSG2_2005517379_6rvuf672_1243_1        05/10/21 01:22:49    MONDAY

NOTE: I can see that a periodic L1 (differential) is executed at 1:22 AM, every day but Sunday where a Full backup is executed.

Now to look at archive log backups -- I am going to show a subset.

Again I can use the bkup_api "list_jobs" command and see all the backup jobs that have been run (which include archive logs).

# /var/opt/oracle/bkup_api/bkup_api list_jobs --dbname dbsg2 | more
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : list_jobs
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_b2532724_20210510070545.552300.log
UUID                             | DATE                | STATUS  | TAG                 | ACTION              
e7ad1ef6aea011eb9c8252540068a695 | 2021-05-06 19:26:23 | success | TAG20210506T123203  | create-backup-full  
03616d68aea211eba5aa52540068a695 | 2021-05-06 19:34:12 | success | TAG20210506T123516  | archivelog-backup   
33fae162aea611eba0ed52540068a695 | 2021-05-06 20:04:12 | success | TAG20210506T130518  | archivelog-backup   
267c21daaea711eb9d3852540068a695 | 2021-05-06 20:11:07 | success | TAG20210506T131438  | create-backup-incremental
650fd222aeaa11ebb58652540068a695 | 2021-05-06 20:34:12 | success | TAG20210506T133516  | archivelog-backup   
961831e4aeae11ebb0d452540068a695 | 2021-05-06 21:04:11 | success | TAG20210506T140517  | archivelog-backup   
c6919f28aeb211eb957e52540068a695 | 2021-05-06 21:34:12 | success | TAG20210506T143518  | archivelog-backup   
f7ce0d0caeb611eb97c552540068a695 | 2021-05-06 22:04:12 | success | TAG20210506T150522  | archivelog-backup   
286e8ea6aebb11eb864c52540068a695 | 2021-05-06 22:34:11 | success | TAG20210506T153516  | archivelog-backup   
598f77eeaebf11eb92c052540068a695 | 2021-05-06 23:04:11 | success | TAG20210506T160518  | archivelog-backup   
89f4919aaec311eb9a9452540068a695 | 2021-05-06 23:34:11 | success | TAG20210506T163516  | archivelog-backup   
bb5ba95eaec711ebb1ed52540068a695 | 2021-05-07 00:04:11 | success | TAG20210506T170518  | archivelog-backup

Step #5 - On demand backups

Now that I have my database configured, I am going to demonstrate some of the options you can add to your backup.

I am going to create a keep backup and give it a tag using bkup_start

$ /var/opt/oracle/bkup_api/bkup_api bkup_start --dbname=dbsg2 --keep --tag=Maymonthlybackup
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : bkup_start
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_7d923417_20210507113940.052080.log
UUID 958a58beaf6311eba98a52540068a695 for this backup
** process started with PID: 262102
** see log file for monitor progress
-------------------------------------

Now to list it.

$ /var/opt/oracle/bkup_api/bkup_api list --dbname dbsg2 --keep
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : list
-> logfile: /var/opt/oracle/log/dbsg2/bkup_api_log/bkup_api_19714a18_20210507114254.007083.log
-> Listing all backups
  Backup Tag                           Completion Date (UTC)      Type          keep    
----------------------                 -----------------------   -----------    --------
   Maymonthlybackup20210507T113125     05/07/2021 18:31:25       keep-forever   True

Step #6 - Restore my database

The last step I'm going to do in my database is to restore it to a previous point in time.

Below is what you see in the console.

NOTE - If you chose a specific time it will be in UTC time.

I pick a time to restore to, and click on the 'Restore Database' option. I can follow the process by looking at 'Workload Requests'.

Step #7 - Validating backups

A great feature of the command tool is the ability to validate backups that have been taken. This is easy to do with the 'bkup_api reval_start' command.

I started my validate for my database dbbsg and I saved the uuid to monitor it.

# /var/opt/oracle/bkup_api/bkup_api reval_start --dbname=dbbsg
DBaaS Backup API V1.5 @2021 Multi-Oracle home
-> Action : reval_start
-> logfile: /var/opt/oracle/log/dbbsg/bkup_api_log/bkup_api_d0647aa8_20210511032638.300613.log
UUID 5f204c4cb24311eb887252540068a695 for restore validation
** process started with PID: 15281
** Backup Request uuid     : 5f204c4cb24311eb887252540068a695

Now to monitor it using the uuid until it's done, and I can see it completed successfully.

# /var/opt/oracle/bkup_api/bkup_api --uuid=5f204c4cb24311eb887252540068a695 --dbname=dbbsg
DBaaS Backup API V1.5 @2021 Multi-Oracle home
@ STARTING CHECK STATUS 5f204c4cb24311eb887252540068a695
[ REQUEST TICKET ]
[UUID    ->  5f204c4cb24311eb887252540068a695 
[DBNAME  ->  dbbsg 
[STATE   ->  success 
[ACTION  ->  start-restore-validate 
[STARTED ->  2021-05-11 10:26:39 UTC 
[ENDED   ->  2021-05-11 10:28:00 UTC 
[PID     ->  15281 
[TAG     ->  None 
[PCT     ->  0.0 
[LOG     ->  2021-05-11 03:26:39.780830 - API:: invoked with args : -dbname=dbbsg -reval=default  
[LOG     ->  2021-05-11 03:26:42.324669 - API:: Wallet is in open AUTOLOGIN state 
[LOG     ->  2021-05-11 03:26:42.996885 - API:: Oracle database state is up and running 
[LOG     ->  2021-05-11 03:28:00.857565 - API:: ....... OK 
[LOG     ->  2021-05-11 03:28:00.857645 - API:: Restore Validation is Completed 
[ END TICKET ]

Step #8 - Restoring/listing/backups with API

There are many options to restoring with the API for both the "database" which consists of the CDB and all PDBs, or just a specific PDB.

Below are some of the commands that help with this.

NOTE: All commands are executed using "bkup_api" from /var/opt/oracle/bkup_api as "oracle"

Command	Options	Description
bkup_start		Start new special backup now
bkup_start	--keep	Create keep backup
bkup_start	--level0	Perform a new FULL level 0 backup
bkup_start	--level1	Perform a new level1 incremental backup
bkup_start	--cron	Creates an incremntal backup through Cron
bkup_chkcfg		Verifies that backups have been configured
bkup_status		Shows the status of the most recent backup
list		Shows the list of the most recent backups
reval_start		Starts a restore validation of datafiles
archreval_start		Starts a revalidation of archive logs
recover_start	--latest	Recover from latest backup
recover_start	--scn	Recover to SCN #
recover_start	--b	Recover using a specific backup tag and defuzzy to archivelog following
recover_start	-t	Recover to time. Specify --nonutc to use a non-UTC timestamp
recover_status		Show status of most recent recover of this database

With recovery you can also just recover a single PDB

--pdb={pdbname} - Recovery just a single PDB

You can also specify if the config files should be restored

--cfgfiles - store the configuration files (controlfiles, spfiles etc) along with database files.

Step #9 - Configuration changes

You can execute the "bkup_api get config --dbname={dbname}" to create a file containing the current configuration. In that file you can see some of the other changes you can be.

Below is what I see it using the version at the time of writing this.

Config Parameter	Settings	Description
bkup_cron_entry	yes/no	Enable/Disable automatic backups
bkup_archlog_cron_entry	yes/no	Enable automatic archive log cleanup when not using tooling
bkup_cfg_files	yes/no	Enable backup of Config files
bkup_daily_time	hh24:mi	Time to execute daily backup
bkup_archlog_frequency	15,20,30…	How many minutes apart to execute archive log backups
bkup_disk	yes/no	Backups to the FRA
bkup_disk_recovery_window	1-14	Recover window of FRA
bkup_oss_xxx		Backup settings when backing up to Object Store in Public Cloud
bkup_zdlra_xx		Backup settings when backing up to a ZDLRA
bkup_nfs_xxx		Backup settings when backing up to NFS
bkup_set_section_size	yes/no	Set to yes to over ride the default setting
bkup_section_size		Value for Over riding the default setting for section size
bkup_channels_node	xx	Number of channels to be used by RMAN
bkup_use_rcat	yes/no	If you are using an RMAN catalog
bkup_rcat_xxx		RMAN catalog settings

Step #10 - Scheduled backups

Backups are scheduled in the crontab on the first node of a cluster. You can view schedule by executing "sudo su - " to become root, and look at the /etc/crontab file.

Below is what is there for my database (dbsg2)

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

15 * * * * oracle /var/opt/oracle/misc/backup_db_wallets.pl
15 * * * * oracle /var/opt/oracle/dbaascli/dbaascli tde backup --alldb
19 1 * * * oracle /var/opt/oracle/bkup_api/bkup_api bkup_start --cron --dbname=dbsg2
4,34 * * * * oracle /var/opt/oracle/bkup_api/bkup_api bkup_archlogs --cron --dbname=dbsg2

The jobs that are scheduled to execute are.

1. backup_db_wallets.pl - Every 15 minutes this script executes from the Crontab. This script goes through the list of databases (regardless if database backups have been turned on) and it makes a copy of the SEPS wallet file in the current wallet location adding the current date/time. The old copy is removed and only one back exists.

The following 2 settings are in my configuration file (/var/opt/oracle/creg/dbsg2.ini) are used as the source location of the wallet, and the location for the backup

wallet_loc=/var/opt/oracle/dbaas_acfs/dbsg2/db_wallet

wallet_loc_bak=/u02/app/oracle/admin/dbsg2/db_wallet

NOTE: This wallet is used for storing user credentials and is an autologin wallet.

I can see the credentials stored. In my case it is both the "sys" password, and the password for "rco". If I was using OSS (object store) my login credential would be stored in this wallet, and if I backed up to ZDLRA, this wallet would contain my connection to the ZDLRA(s) I was backing up to.

2: CATALOG rco

1: dbsg2 sys

2. /var/opt/oracle/dbaascli/dbaascli tde backup --alldb - Every 15 minutes this script executes from the Crontab. This script goes through the list of databases (regardless if database backups have been turned on) and it makes a copy of the TDE wallet file in the $ORACLE_BASE directory.

The location is $ORACLE_BASE/{db_name}/tde_wallet/tde/

The output from this script is in /var/opt/oracle/log/misc/backup

3. /var/opt/oracle/bkup_api/bkup_api bkup_start --cron --dbname={mydb}- Every at 1:19 AM, This time is determined in the bkup_api configuration using the parameter "bkup_daily_time". This is the same API that is called to perform an on-demand backup from the command line, but with a '--cron' parameter also.

4. /var/opt/oracle/bkup_api/bkup_api bkup_archlogs --dbname={mydb} Every 30 minutes this script is executed based on the 'bkup_archlog_frequency' bkup_api configuration setting. This script will backup my archive logs to the backup location.

Friday, April 23, 2021

Enrolling my ExaCC RAC database using REST APIs

This post will continue the process of automating the enrollment of my RAC database using the OKV REST API, and some automation scripts. the steps to create the scripts are in my previous post.

NOTE: These steps are for ExaCC specific. If you want to learn about configuring OKV with Autonomous Database (ADB) when using ExaCC, the product manager, Peter Wahl has a great blog post on this topic. He also has videos as part of the "Ask Tom" series if you want to learn more about OKV 21c, or just OKV in general.

The first step is to download the zip file I created in the previous post. I downloaded it onto the first DB host in my RAC cluster. I unzipped it into /home/oracle/okv.

Below is what I am starting with.

.
 |-lib
 | |-okvrestcli.jar
 |-bin
 |-conf
 | |-okvrestcli_logging.properties
 | |-okvrestcli.ini
 | |-ewallet.p12.lck
 | |-ewallet.p12
 | |-cwallet.sso.lck
 | |-cwallet.sso
 | |-okvclient.ora
 |-setenv.sh
 |-run-me.sh

STEP #1 - Set the environment

First I am going to set my environment to the database instance I want to configure (jckey1), and then I am going to source the environment for my OKV install.

[oracle@exacc1]$ cd /home/oracle/okv
[oracle@exacc1]$ . oraenv
ORACLE_SID = [jckey1] ? jckey1
The Oracle base remains unchanged with value /u02/app/oracle
[oracle@exacc1]$ . ./setenv.sh
 
 
create environment variables OKV_RESTCLI_HOME and OKC_RESTCLI_CONFIG  
 
$OKV_RESTCLI_HOME    :  /home/oracle/okv 
$OKV_RESTCLI_CONFIG  :  /home/oracle/okv/conf/okvrestcli.ini 
 
Adding $OKV_RESTCLI_BIN to the $PATH

STEP #2 - Execute the enrollment creation script

The next step is to execute the run-me.sh that I created in the previous post. This will create the enrollment script. At the end of the output you will see the script it creates (okv-ep.sh).

NOTE: It will default to my DBNAME for the wallet name.

[oracle@exacc1]$ ./run-me.sh
 
executing script with $OKV_RESTCLI_HOME=/home/oracle/okv
 
 
DB Name is identified as jckey and ORACLE_SID is set to jckey1 setting
 
Press enter to keep this default [jckey], or enter the DB Name
DB Name [enter for Default]    : 
 
Using DB Name : jckey
 
#!/bin/bash
mkdir -pv /u02/app/oracle/admin/jckey/wallet
mkdir -pv /u02/app/oracle/admin/jckey/wallet/okv
 okv manage-access wallet create --wallet JCKEY --description "wallet for database JCKEY" --unique FALSE
okv admin endpoint create --endpoint JCKEY1_on_exacc1 --description "exacc11, 10.136.106.36" --type ORACLE_DB --platform L
INUX64 --unique FALSE
okv manage-access wallet set-default --wallet JCKEY --endpoint JCKEY1_on_exacc1
expect << _EOF
    set timeout 120
    spawn okv admin endpoint provision --endpoint JCKEY1_on_exacc1 --location /u02/app/oracle/admin/jckey/wallet/okv --auto
-login FALSE
    expect "Enter Oracle Key Vault endpoint password: "
    send "change-on-install\r"
    expect eof
_EOF

STEP #2 - Execute the enrollment script

[oracle@exacc1]$ ./okv-ep.sh
{
  "result" : "Success"
}
{
  "result" : "Success"
}
{
  "result" : "Success"
}
spawn okv admin endpoint provision --endpoint JCKEY1_on_exacc1 --location /u02/app/oracle/admin/jckey/wallet/okv --auto-login FALSE
Enter Oracle Key Vault endpoint password: 
{
  "result" : "Success",
  "value" : {
    "javaHome" : "/u02/app/oracle/product/19.0.0.0/dbhome_8/jdk"
  }
}

STEP #3 - We can verify what the enrollment script did

I am first going to look under $ORACLE_BASE/admin/$DBNAME/wallet where it placed the okv client.

[oracle@exacc1]$ pwd
/u02/app/oracle/admin/jckey/wallet
[oracle@exacc1]$ find . | sed -e "s/[^-][^\/]*\// |/g" -e "s/|\([^ ]\)/|-\1/"
.
  |-okv
 | |-bin
 | | |-okveps.x64
 | | |-okvutil
 | | |-root.sh
 | |-ssl
 | | |-ewallet.p12
 | |-csdk
 | | |-lib
 | | | |-liborasdk.so
 | |-jlib
 | | |-okvutil.jar
 | |-conf
 | | |-okvclient.ora
 | | |-logging.properties
 | | |-okvclient.lck
 | |-lib
 | | |-liborapkcs.so
 | |-log
 | | |-okvutil.deploy.log

Now I am going to verify in OKV and I can see the wallet got created for my database.

And I am going to look at the endpoint, and verify the default wallet is set.

STEP #4 Execute root.sh (only if this is the first install on this host).

I execute the root.sh script in the /bin directory as root.

[root@exacc1]# ./root.sh
Creating directory: /opt/oracle/extapi/64/hsm/oracle/1.0.0/
Copying PKCS library to /opt/oracle/extapi/64/hsm/oracle/1.0.0/
Setting PKCS library file permissions
Installation successful.

STEP #5 - Verify we can contact the OKV server

The next step is to execute the okvutil list command to verify we can contact the OKV host, and that the default wallet is configured.

[oracle@exacc1]$ ./okvutil list
Enter Oracle Key Vault endpoint password: 
Unique ID                               Type            Identifier
9E8BD892-D799-44B7-8289-94447E7ACC54    Template    Default template for JCKEY1_ON_ECC5C2N1

STEP #6 - change the OKV endpoint password

[oracle@exacc1]$ /u02/app/oracle/admin/jckey/wallet/okv/bin/okvutil changepwd -t wallet -l /u02/app/oracle/admin/jckey/wallet/okv/ssl/
Enter wallet password: change-on-install
Enter new wallet password: {my new password}
Confirm new wallet password:  {my new password}
Wallet password changed successfully

STEP #7 Install the client and change the password on all nodes.

I followed the steps above on the other 3 nodes to install the client and change the password.

STEP #8 Upload the keys from the wallet file.

I uploaded the keys from the shared wallet files on ACFS.

[oracle@exacc1]$ /u02/app/oracle/admin/jckey/wallet/okv/bin/okvutil upload -t wallet -l /var/opt/oracle/dbaas_acfs/jckey/wallet_root/tde -v 2 -g JCKEY
okvutil version 21.1.0.0.0
Endpoint type: Oracle Database
Configuration file: /u02/app/oracle/admin/jckey/wallet/okv/conf/okvclient.ora
Server: 10.136.102.243:5696 
Standby Servers: 
Uploading from /acfs01/dbaas_acfs/jckey/wallet_root/tde
Enter source wallet password: 
Enter Oracle Key Vault endpoint password: 
ORACLE.SECURITY.DB.ENCRYPTION.Ab8Sv6Ezs08fv9Sy7/zZB8oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.Ab8Sv6Ezs08fv9Sy7/zZB8oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.ATQdCFHhVk9Yv7er6uZtDf8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.ATQdCFHhVk9Yv7er6uZtDf8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.BFF45EC14E46013BE053246A880A5564
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Uploaded 2 TDE keys
Uploaded 0 SEPS entries
Uploaded 0 other secrets
Uploaded 4 opaque objects

Uploading private persona
Uploading certificate request
Uploading trust points

Uploaded 1 private keys
Uploaded 1 certificate requests
Uploaded 0 user certificates
Uploaded 0 trust points

Upload succeeded

STEP #9 Copy current wallet, and add OKV credentials.

Now you copy the current wallet file (from the ACFS location) to the tde directory (new OKV install) next to the OKV install.

In my case since my OKV client is installed in $ORACLE_BASE/admin/jckey/wallet (which will be the WALLET_ROOT), the tde directory will be the file location for wallets.

I am also adding my password credentials to the local wallet.

NOTE: "OKV_PASSWORD" is used to open the wallet. "HSM_PASSWORD" is used to access the OKV server(s).

mkdir /u02/app/oracle/admin/jckey/wallet/tde_seps
mkdir /u02/app/oracle/admin/jckey/wallet/tde
cp /var/opt/oracle/dbaas_acfs/jckey/wallet_root/tde/* /u02/app/oracle/admin/jckey/wallet/tde/.
ADMINISTER KEY MANAGEMENT ADD SECRET 'Welcome1+' FOR CLIENT 'OKV_PASSWORD' TO LOCAL AUTO_LOGIN KEYSTORE '/u02/app/oracle/admin/jckey/wallet/tde_seps';
ADMINISTER KEY MANAGEMENT ADD SECRET 'Welcome1+' FOR CLIENT 'HSM_PASSWORD' TO AUTO_LOGIN KEYSTORE '/u02/app/oracle/admin/jckey/wallet/tde';

STEP # 10 Change the WALLET_ROOT

Since WALLET_ROOT can only be changed with a restart, I am going to shut down all instances in the cluster and perform the next few steps on the first node only.

SQL> alter system set WALLET_ROOT='/u02/app/oracle/admin/jckey/wallet' scope=spfile;

System altered.

SQL> shutdown immediate
startup mount;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> 
alter system set tde_configuration='KEYSTORE_CONFIGURATION=OKV|FILE' scope=both;

select b.name pdb_name,wrl_type,
wrl_parameter,
status,wallet_type,
keystore_mode,
fully_backed_up
from v$encryption_wallet a,v$containers b
where a.con_id = b.con_id(+);SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7  

PDB Name   Type       WRL_PARAMETER					 Status 			WALLET_TYPE	     KEYSTORE Backed Up
---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
CDB$ROOT   FILE       /u02/app/oracle/admin/jckey/wallet/tde/		 OPEN				AUTOLOGIN	     NONE     YES
CDB$ROOT   OKV								 OPEN_NO_MASTER_KEY		OKV		     NONE     UNDEFINED
PDB$SEED   FILE 							 OPEN				AUTOLOGIN	     UNITED   YES
PDB$SEED   OKV								 OPEN_NO_MASTER_KEY		OKV		     UNITED   UNDEFINED
JCKPDB	   FILE 							 OPEN				AUTOLOGIN	     UNITED   YES
JCKPDB	   OKV								 OPEN_NO_MASTER_KEY		OKV		     UNITED   UNDEFINED

SQL> shutdown immediate
startup ;

STEP # 11 Combine the local wallet File and OKV.

Next I need to migrate the keys using the local wallet. Note this will rekey the database.

ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "-okv key" MIGRATE USING "-local wallet key-" WITH BACKUP;

STEP # 12 restart the instance and make sure the wallet open.


PDB Name   Type       WRL_PARAMETER                              Status              WALLET_TYPE     KEYSTORE Backed Up
---------- ---------- -------------------------------            ------------------- --------------- --------- ----------
CDB$ROOT   FILE       /u02/app/oracle/admin/jckey/wallet/tde/    OPEN                AUTOLOGIN       NONE     YES
CDB$ROOT   OKV                                                   OPEN                OKV             NONE     UNDEFINED
PDB$SEED   FILE                                                  OPEN                AUTOLOGIN       UNITED   YES
PDB$SEED   OKV                                                   OPEN                OKV             UNITED   UNDEFINED
JCKPDB     FILE                                                  OPEN                AUTOLOGIN       UNITED   YES
JCKPDB     OKV                                                   OPEN                OKV             UNITED   UNDEFINED

STEP # 13 rebuild the local wallet with the password

I deleted the original wallet files from the "tde" and "tde_seps" directories and recreated them using the exact same steps from step #9. The only addition is that I needed to create the wallet first


ADMINISTER KEY MANAGEMENT ADD SECRET 'Welcome1+' FOR CLIENT 'OKV_PASSWORD' TO LOCAL AUTO_LOGIN KEYSTORE '/u02/app/oracle/admin/jckey/wallet/tde_seps';
ADMINISTER KEY MANAGEMENT ADD SECRET 'Welcome1+' FOR CLIENT 'HSM_PASSWORD' TO AUTO_LOGIN KEYSTORE '/u02/app/oracle/admin/jckey/wallet/tde';

I then pushed executed the same commands to create the wallets on all the nodes in the clusters in the same location .

STEP # 14 - Bounce the database.

I bounced the database and made sure the wallet was open on all 4 nodes. Done.

INST_ID    PDB Name Type  WRL_PARAMETER                           Status               WALLET_TYPE   KEYSTORE Backed Up
-------- ---------- ----- ----------------------------------------  ------------------ -------------- -------- ---------
1        CDB$ROOT   OKV                                             OPEN               OKV            NONE     UNDEFINED
2        CDB$ROOT   OKV                                             OPEN               OKV            NONE     UNDEFINED
3        CDB$ROOT   OKV                                             OPEN               OKV            NONE     UNDEFINED
4        CDB$ROOT   OKV                                             OPEN               OKV            NONE     UNDEFINED
1        PDB$SEED   OKV                                             OPEN               OKV            UNITED   UNDEFINED
2        PDB$SEED   OKV                                             OPEN               OKV            UNITED   UNDEFINED
3        PDB$SEED   OKV                                             OPEN               OKV            UNITED   UNDEFINED
4        PDB$SEED   OKV                                             OPEN               OKV            UNITED   UNDEFINED
1        JCKPDB     OKV                                             OPEN               OKV            UNITED   UNDEFINED
2        JCKPDB     OKV                                             OPEN               OKV            UNITED   UNDEFINED
3        JCKPDB     OKV                                             OPEN               OKV            UNITED   UNDEFINED
4        JCKPDB     OKV                                             OPEN               OKV            UNITED   UNDEFINED
1        PDB$SEED   FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
1        CDB$ROOT   FILE  /u02/app/oracle/admin/jckey/wallet/tde/   OPEN_NO_MASTER_KEY AUTOLOGIN      NONE     UNDEFINED
2        CDB$ROOT   FILE  /u02/app/oracle/admin/jckey/wallet/tde/   OPEN_NO_MASTER_KEY AUTOLOGIN      NONE     UNDEFINED
3        CDB$ROOT   FILE  /u02/app/oracle/admin/jckey/wallet/tde/   OPEN_NO_MASTER_KEY AUTOLOGIN      NONE     UNDEFINED
4        CDB$ROOT   FILE  /u02/app/oracle/admin/jckey/wallet/tde/   OPEN_NO_MASTER_KEY AUTOLOGIN      NONE     UNDEFINED
1        PDB$SEED   FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
2        PDB$SEED   FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
3        PDB$SEED   FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
4        PDB$SEED   FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
1        JCKPDB     FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
2        JCKPDB     FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
3        JCKPDB     FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED
4        JCKPDB     FILE                                            OPEN_NO_MASTER_KEY AUTOLOGIN      UNITED   UNDEFINED

That's all there is to it. I now have my ExaCC database configuring to use OKV as the key store, and autologin into the wallet on all instances !

Thursday, April 22, 2021

Configuring OKV automation using REST APIs

This post will go through the process of creating a few simple scripts to automate OKV installation using the REST API capability of OKV.

NOTE: This information was provided by the product manager Peter Wahl who has a great blog on the features of OKV and some great "Ask Tom" sessions on OKV (and other security pieces),you can watch and learn more.

Step #1 Configure RESTful Services and download client tool

First you need to configure the OKV server for RESTful Services. The instructions can be found here. This is done by navigating to the System tab and clicking on RESTful Services.

It is recommended that you only enable the RESTful services when you are actively onboarding new endpoints.

This bring up the window below.

There are three things you want to do from this window.

Click on the "Enable" box to enable RESTful services
Download the okvrestcliepackage.zip which are the client utilities.
Save this setting to enable RESTful services.

Now that we have this file, we need to download it our client and start creating the scripts to automate this process.

I downloaded the zip file to my DB host to configure it. I unzipped it in /home/oracle/okv/rest

NOTE: you can also download it directly from the OKV hosts by using any of the commands below.

wget --no-check-certificate https://ip_address:5695/okvrestclipackage.zip  
curl -k https://ip_address:5695/okvrestclipackage.zip -o okvrestclipackage.zip 
curl -O -k https://ip_address:5695/okvrestclipackage.zip

Step #2 Create a user for the restapi steps

I create a new user in OKV called "restapi" and you can see the permissions below. Before using the new user, you must login and change the password because the password is expired by default when creating a new user.

Step #3 unzip and configure the client tool

I unzipped the client tool into my home directory on a DB server so I can put together the automation scripts. In my case I unzipped it into /home/oracle/okv/rest. This creates 3 sub directories. I am going to format the output using this command.

find . | sed -e "s/[^-][^\/]*\// |/g" -e "s/|\([^ ]\)/|-\1/"

Below is what the output looks like

.
 |-lib
 | |-okvrestcli.jar
 |-bin
 | |-okv.bat
 | |-okv
 |-conf
 | |-okvrestcli.ini
 | |-okvrestcli_logging.properties

Step #4 - Set the environment for the CLI

In order to configure OKV, I am going to need some variables set in my environment. I can do this manually, but in my case I decided to create a "setenv.sh" script that will set the variables and add the OKV script to my path to be executed. I also included the ability to pass a parameter (ORACLE_SID) so that you can use the script in a loop across multiple instances on the same host.

The 3 main variables I will be using are

OKV_RESTCLI_HOME - Location of the scripts that I am going to be installing. If I source the setenv.sh script, it will set the home to this location.

OKV_RESTCLI_CONFIG - Name of the configuration file that contains the rest CLI configuration.

OKV_HOME - Location to install OKV for the current instance. This location is $ORACLE_BASE/admin/${DB_UNIQUE_NAME}/okv_home. This follows the standard for ExaCC.

NOTE: If this is a NEW database

If you want to use these steps to configure OKV on a new database, you need to perform the following steps prior to executing "/ ./setenv.sh".

Add the new $ORACLE_SIDE for the host in the "/etc/oratab" file.
Create the directory "mkdir $ORACLE_BASE/admin/{DB Unique Name}"
Create the directory "mkdir -p $ORACLE_BASE/diag/rdbms/${DB Unique Name}/${ORACLE_SID}
Use ". oraenv" to set the environment to this $ORACLE_SID

 ###Ensure that the setenv file is sourced from the current directory to properly set variables


if [ "${BASH_SOURCE[0]}" != "${0}" ]
  then echo " "
  else echo " "
    echo "the setenv.sh file must be sourced with \". ./setenv.sh \""
   return
fi


FILE=`pwd`/setenv.sh
if [  -f "$FILE" ]
  then echo " "
  else echo " "
    echo "source this from RESTful install directory"
    return
fi



#############################################################################################
#
#  Check to see if a parameter is passed  Or ORACLE_SID is set. Otherwise exit
#
#############################################################################################



if [ $# -eq 0 ]
  then
      echo " "
      if [ -z ${ORACLE_SID} ]
         then
           echo "No \$ORACLE_SID set and nothing passed.  Exiting "
           echo " "
           return
      fi

    echo "No \$ORACLE_SID provided. Trying current \$ORACLE_SID value of : $ORACLE_SID"
    echo " "
  else

    echo "\$ORACLE_SID provided. Setting \$ORACLE_SID to  : $1"
    export ORACLE_SID=$1
    echo " "
fi



#############################################################################################
#
#  make sure SID is in the /etc/oratab so we can determine the
#
#############################################################################################


sid_found='false'
for sids in `cat /etc/oratab |  cut -d ":" -f 1`
  do
   if [ "$sids" = "${ORACLE_SID}" ]
      then sid_found='true'
   fi
  done

if [ ${sid_found} = "false" ]
  then
    echo "\$ORACLE_SID : ${ORACLE_SID} not found in /etc/oratab file. Exiting"
    return
fi


#############################################################################################
#
#  make sure SID  has exists under $ORACLE_BASE/admin to install software
#
#############################################################################################


 db_dir=`find ${ORACLE_BASE}/diag/rdbms -maxdepth 2 -name ${ORACLE_SID}`
 delimeter_cut=`echo $db_dir | sed -e 's/\(.\)/\1\n/g' | grep \/ | wc -l`
 DB_UNIQUE_NAME=`echo $db_dir | cut -d "/" -f $delimeter_cut `

 if [ -z ${DB_UNIQUE_NAME} ]
       then
        echo $db_dir
         echo "Invalid \$ORACLE_SID. Exiting script"
         echo " "
         echo " "
         return
 fi



#############################################################################################
#
#   set the environment
#
#############################################################################################

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES



#############################################################################################
#
#  set the variables
#
#############################################################################################

 echo "create environment variables OKV_HOME, OKV_RESTCLI_HOME and OKC_RESTCLI_CONFIG  "


## set variables
export OKV_HOME=$ORACLE_BASE/admin/$DB_UNIQUE_NAME/okv_home
export OKV_RESTCLI_HOME=`pwd`
export OKV_RESTCLI_BIN=$OKV_RESTCLI_HOME/bin
export OKV_RESTCLI_CONFIG=$OKV_RESTCLI_HOME/conf/okvrestcli.ini

##add the OKV script to the path

     echo " "
     echo "\$OKV_HOME            :  $OKV_HOME "
     echo "\$OKV_RESTCLI_HOME    :  $OKV_RESTCLI_HOME "
     echo "\$OKV_RESTCLI_CONFIG  :  $OKV_RESTCLI_CONFIG "
     echo " "
     echo "Adding \$OKV_RESTCLI_BIN to the \$PATH  "
     echo " "
 if [[ "$PATH" =~ (^|:)"${OKV_RESTCLI_BIN}"(:|$) ]]
 then   echo " "
 else PATH=${OKV_RESTCLI_BIN}:$PATH
 fi


string=`which dcli`
echo $string
if  [[ $string == *"no dcli in"* ]]
  then export RAC_DATABASE="N"
  else export RAC_DATABASE="Y"
fi

## Set JAVA_HOME to the location of Java if it isn't set already

if [ -z ${JAVA_HOME+x} ]
  then export JAVA_HOME=$ORACLE_HOME/jdk
       echo "Setting  \$JAVA_HOME to \$ORACLE_HOME/jdk  : $JAVA_HOME"
  else
       echo "\$JAVA_HOME already set to   : $JAVA_HOME"
fi

     echo " "

Step #5 - Set initialization parameters in okvrestcli.ini file

Next, I am going to configure the initialization parameters. These are found in the okvrestcli.ini file.

You can see that the file contains a "[Default]" profile and a few other example profiles. We will start with the default profile. In this we are going to set a few of the properties.

LOG_PROPERTY - Location of the logging properties. Default location is ./conf directory.

SERVER - IP address (or DNS) of one or more OKV hosts

OKV_CLIENT_CONFIG - location of the config file. Default location is ./conf directory

USER - OKV user that has authority to administer endpoints an wallets. In this case it will be the restapi user that I created.

PASSWORD - Password for the user, or location of wallet containing the password. I am NOT going to use this as I am going to use a wallet file.

CLIENT_WALLET - I am going to use a wallet to store the password, and this is the location of the wallet file. I will be creating the autologin wallet later.

Below is what my "[Default]" configuration file looks like after my changes which is located at $OKV_RESTCLI_HOME/conf/okvrestcli.ini . I am going to use the environmental variables I set in the setenv.sh script.

NOTE: I am choosing to store my password in wallet rather than clear text in the .ini file.

You need to change the server to either the server name or the IP address.

[Default]
log_property=$OKV_RESTCLI_HOME/conf/okvrestcli_logging.properties
server=10.0.0.150
okv_client_config=$OKV_RESTCLI_HOME/conf/okvclient.ora
user=restapi
client_wallet=$OKV_RESTCLI_HOME/conf

Step #6 Create the wallet to save the password encrypted

Since I chose to put my password in a wallet, I now need to create that wallet. Using the instructions in the document (linked to at the beginning of this blog), I execute the command from the directory I installed into (/home/oracle/okv/rest)

cd /home/oracle/okv/rest
. ./setenv.sh

#####  Output from script is below #########
# create environment variables OKV_RESTCLI_HOME and OKC_RESTCLI_CONFIG
#
#   $OKV_RESTCLI_HOME    :  /home/oracle/okv/rest
#  $OKV_RESTCLI_CONFIG  :  /home/oracle/okv/rest/conf/okvrestcli.ini
#
#   Adding $OKV_RESTCLI_BIN to the $PATH</i>#



okv admin client-wallet add --client-wallet $OKV_RESTCLI_HOME/conf --wallet-user restapi
#Password: {my password}
#{
#  "result" : "Success"
#}

Step #7 Create and execute the run-me.sh script

The last step is to create the script that will be executed on the host to create the provision script. In my script, I took the default and did some checking. This script will

Ensure the variable OKV_RESTCLI_HOME is set before it can be executed.
Determine the DB_UNIQUE_NAME from the $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID directory. Solving for the * should give us the DB_UNIQUE_NAME
While executing, it tells you what it believes the DB_UNIQUE_NAME is, and gives you a chance to change it if incorrect.
It will validate if the endpoint group exists by accessing OKV. If the endpoint group already exists, it does not try to create it again. If it doesn't exist it will create the endpoint group.
It will validate if the wallet exists by accessing OKV. If the wallet already exists, it does not try to create it again. If it doesn't exist it will create the wallet .
t will add the endpoint, add this endpoint to the endpoint group, and assign the wallet to this endpoint.
It will install the client software in $ORACLE_BASE/admin/$DB_UNIQUE_NAME/okv_home/okv

Below is the script I am using.

#!/bin/bash


###########################################################################################
#
#  Make sure the setenv script was executed to set the OKV_RESTCLI_HOME variable
#
###########################################################################################



if [ -v OKV_RESTCLI_HOME ]
 then echo " "
        echo "executing script with \$OKV_RESTCLI_HOME=$OKV_RESTCLI_HOME"
        echo " "
        echo " "
 else  echo " "
        echo "\$OKV_RESTCLI_HOME not set"
        echo " "
        echo "source the setenv.sh script in the RESTful Services install directory and try again."
        echo " "
        exit 0
fi


###########################################################################################
#
#  Determine the DBNAME Unique Name for this SID and have user validate
#
###########################################################################################

pushd $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID > /dev/null
cd ..
export DB_UNIQUE_NAME=`printf '%s\n' "${PWD##*/}"`
popd > /dev/null


echo "DB Unique Name is identified as $DB_UNIQUE_NAME and ORACLE_SID is set to $ORACLE_SID setting"
echo " "
echo "****************************************************************************************** "
echo " "
echo "DB Name is identified as $DB_UNIQUE_NAME ---  NOTE THIS MAY NOT be the correct DB_NAME --- "
echo "      ---  NOTE THIS MAY NOT be the correct DB_NAME if there is a primary and standby  --- "
echo " "
echo "****************************************************************************************** "
echo " "
echo " "
echo "Press enter to keep this default [$DB_UNIQUE_NAME], or enter the DB Name"
read -p 'DB Name [enter for Default]    : '  newdbname

  
if [ -z "$newdbname" ]
  then export DBNAME=`echo $DB_UNIQUE_NAME`
  else export DBNAME=`echo $newdbname`
fi

echo " "
echo "Using DB Name : $DBNAME"

echo "#!/bin/bash" >  $OKV_RESTCLI_HOME/okv-install-${ORACLE_SID}.sh

###########################################################################################
#
#  check to see if wallet already exists
#  create it if it doesn't exist
#
###########################################################################################



export wallet_exists=`okv manage-access wallet check-status --wallet ${DBNAME^^} | grep Success | wc -l`
if [ $wallet_exists -ne 0 ]
  then  echo " Wallet ${DBNAME^^} already exists skipping wallet creation. assuming this is NOT the first node for the database"
  else
cat >> $OKV_RESTCLI_HOME/okv-install-${ORACLE_SID}.sh << EOF2
 okv manage-access wallet create --wallet ${DBNAME^^} --description "wallet for database ${DBNAME^^}"
EOF2
fi


###########################################################################################
#
#  check to see if endpoint Group already exists
#  create endpoint group if it doesn't exist
#
###########################################################################################


export group_exists=`okv manage-access endpoint-group check-status --endpoint-group ${DBNAME^^} | grep Success | wc -l`
if [ $group_exists -ne 0 ]
  then  echo " endpoint group ${DBNAME^^} already exists skipping group creation. assuming this is NOT the first node for the Database"
  else
cat >> $OKV_RESTCLI_HOME/okv-install-$ORACLE_SID.sh << EOF2
 okv manage-access endpoint-group create --endpoint-group ${DBNAME^^} --description "endpoint group for database ${DBNAME^^}"
EOF2
fi


export EP_NAME=`echo ${ORACLE_SID^^}_on_${HOSTNAME/.*} | sed 's/\-/\_/g'`


###########################################################################################
#
#  add commands to create/remove directories
#
###########################################################################################

cat >> $OKV_RESTCLI_HOME/okv-install-$ORACLE_SID.sh << EOF1
mkdir -pv ${OKV_HOME}
mkdir -pv ${OKV_HOME}/okv
mkdir -pv ${OKV_HOME}/tde
mkdir -pv ${OKV_HOME}/tde_seps
EOF1

cat > $OKV_RESTCLI_HOME/okv-remove-$ORACLE_SID.sh << EOF1
#!/bin/bash
rm -r ${OKV_HOME}
rm -r ${ORACLE_BASE}/okv/$ORACLE_SID
EOF1



###########################################################################################
#
#  commands to provision instance on Node
#
###########################################################################################

cat >> $OKV_RESTCLI_HOME/okv-install-${ORACLE_SID}.sh << EOF3
okv admin endpoint create --endpoint ${EP_NAME} --description "$HOSTNAME, $(hostname -i)" --type ORACLE_DB --platform LINUX64
okv manage-access endpoint-group add-endpoint --endpoint-group  ${DBNAME^^}   --endpoint ${EP_NAME}
okv manage-access wallet set-default --wallet ${DBNAME^^} --endpoint ${EP_NAME}
okv manage-access wallet add-access --wallet ${DBNAME^^} --endpoint-group  ${DBNAME^^} --access "RM_MW"
expect << _EOF
    set timeout 120
    spawn okv admin endpoint provision --endpoint ${EP_NAME} --location $OKV_HOME/okv --auto-login FALSE
    expect "Enter Oracle Key Vault endpoint password: "
    send "change-on-install\r"
    expect eof
_EOF
EOF3
chmod +x $OKV_RESTCLI_HOME/okv-install-$ORACLE_SID.sh
more $OKV_RESTCLI_HOME/okv-install-$ORACLE_SID.sh

Step #8 Execute okv-install-${ORACLE_SID}.sh to validate that enrollment works

[oracle@pmdexa1dbadm01vm01 okv]$ ./okv-ep.sh
{
  "result" : "Success"
}
{
  "result" : "Success"
}
{
  "result" : "Success"
}
spawn okv admin endpoint provision --endpoint DEMO31_on_pmdexa1dbadm01vm01 --location /u01/app/oracle/admin/demo31/wallet/okv --auto-login FALSE
Enter Oracle Key Vault endpoint password:
{
  "result" : "Success"
}

Once I ran it I removed the files that were created for the install.

Step #9 Zip it all up and place it in a location to be downloaded

Below is the scripts that will be part of the zip file.

.
 |-wallet
 | |-ewallet.p12.lck
 | |-ewallet.p12
 | |-cwallet.sso.lck
 | |-cwallet.sso
 |-conf
 | |-okvrestcli_logging.properties
 | |-okvrestcli.ini
 |-setenv.sh
 |-runme.sh
 |-lib
 | |-okvrestcli.jar
 |-bin
 | |-okv
 | |-okv.bat

Now I am ready to download this zip file to my Database Host and enroll a database.

NOTE: To change the script to work on another OKV all host I only had to make 3 changes.

Update the okvrestcli.ini file with OKV host IP
Update the okvrestcli.ini file with the the user
recreate the wallet file that contains the password for the OKV user

Step #10 Run root.sh if this is the first on a host

When a new endpoint is added, a script is downloaded into the $OKV_HOME/okv/bin/ directory called root.sh.. This script will copy the PKCS library (liborapkcs.so) in central location on the host as root. This file is needed for the database to access OKV. The location of this file is different on different OS systems.

NOTE: If you are already using a key management software on this host for Oracle databases, running root.sh could overwrite the library used.