Thursday, August 5, 2021

Adding immutability to buckets in the Oracle Cloud Object Store

I am going to demonstrate a new feature of the object store that you might not have known about. The feature is "Retention Lock" and is used to protect the objects in a bucket.

Let me first start with a few links to get you started and then I will demonstrate how to use this feature.

In order to add a retention lock to a bucket you create a rule for the individual bucket.

Below is a screen shot of where you will find the retention rules, and the "Create Rule" button. Also note that I highlighted the "Object Versioning" attribute of the bucket.

NOTE: You cannot add a retention lock to a bucket that has "Object Versioning" enabled. You can also not disable "Object Versioning" once enabled. You MUST suspend "Object Versioning" before adding any retention rules to your bucket.

There are 3 types of retention locks and below I will describe them and show you how to implement them. They are listed from least restrictive to most restrictive.

DATA GOVERNANCE

Data Governance is a time based lock based on the modified time of EACH OBJECT in the bucket.

The Retention can be set in "days" or "years".

Below is what the settings look like for data governance. You choose "Time-Bound" for the rule type and ensure that you do not "enable retention rule lock".

With Data Governance you can both increase and decrease the duration of the retention lock.

Below you can see after the lock was created, the rule is not locked.

REGULATORY COMPLIANCE

Regulatory Compliance is similar to Data Governance with the exception that the duration can only be increased.

The retention lock of the individual objects, just like Data Governance is based on when the individual object was last modified.

Another key difference is that when you "enable retention rule lock", you also set when this rule is locked. The default is 14 days, and cannot be set less than 14 days.

The delay of 14 days is a "cooling off period" that gives you 14 days to test before the rule takes effect. This is because once the cooling off period ends, the retention time cannot be shortened.

Below is the screen shot of creating a retention rule for regulatory compliance and note that the retention rule lock MUST be enabled to ensure the duration is not shortened.

It also asked me to confirm the "lock date" before the rule is created.

Below are the rules that are set after both of these steps.

.NOTE: I now have 2 rules. I have the original rule that will lock the objects for 30 days (this can be changed as needed). I also have a Regulatory Compliance rule that will lock the objects for 1 day. The Regulatory Compliance rule not take effect for 14 days from today.

LEGAL HOLD

The final type of retention is a legal hold. A legal hold will put a retention lock on the WHOLE bucket. All objects in the bucket are locked and cannot be modified/deleted until the hold is removed. There is no ending time period for a legal hold.

Below is how you create a legal hold.

SUMMARY

You can create the 3 types of retention locks, and you can even layer them. Below you can see that I have 3 locks. The Legal Hold rule will lock everything, but that can be removed leaving the 2 remaining rules. I can remove the Data Governance rule, but the Regulatory Compliance rule is the most restrictive. Once the 14 day (or whatever you set) has passed this rule cannot be changed.

Now when I go to delete an object that is protected by a retention rule I get an error. Below is example of what you will see.

Wednesday, July 28, 2021

A New ZDLRA feature can help you migrate to a new ZDLRA

A new feature was included in the 19.2.1.1.2 ZDLRA software release to help you migrate your backup strategy when moving to a new ZDLRA.

*****************************************************************************

Update March, 2025 ----> Release 23.1 of ZDLRA software

A new feature was added to this release "Rack Migration assistant".

What this means is that a new parameter is available with ADD_REPLICATION_SERVER called copyall_backups. This will replicate all existing backups making it much easier to just migrate to the new ZDLRA.

Parameter	Description
copyall_backups	When adding a replication server to a protection policy with copyall_backups set to TRUE, all backups for each of the databases are chosen for initial replication to the downstream Recovery Appliance. If set to FALSE the most recent level 0 including archivelogs and control files are replicated.

*****************************************************************************

This feature allows you to continue to access your older backups during the cut-over period directly from the new ZDLRA. You point your database restore to the the new ZDLRA and it will automagically access the older backups if necessary. Once the cutover period has passed, the old ZDLRA can be retired.

I am going to walk through the steps.

1. Configure new ZDLRA

Add the new ZDLRA to OEM - The first step is to ensure that the new ZDLRA has been registered within your OEM environment. This will allow it to be managed, and of course monitored.

Add a replication VPC user to the new ZDLRA. This will be used to connect from the old ZDLRA.
Add the VPC users on the new ZDLRA that match the old ZDLRA

Configure policies on new ZDLRA to match old ZDLRA.

This can done by dynamically executing DBMS_RA.CREATE_PROTECTION_POLICY.

Current protection policy information can be read from the RA_PROTECTION_POLICY view.

Add databases to proper protection policies on new ZDLRA.

This can be done by dynamically executing DBMS_RA.ADD_DB.

Current database information can be read from the RA_DATABASE view.

Grant the replication VPC user access to all databases for replication.

This can be done by dynamically executing DBMS_RA.GRANT_DB_ACCESS

The current list of databases can be read from the RA_DATABASE view.

Grant the VPC users access to the database for backups/restores

This can be by dynamically executing DBMS_RA.GRANT_DB_ACCESS

The current list of grants can be read from the RA_DB_ACCESS view

Create a replication server on the old ZDLRA that points to the new ZDLRA
Add the protection policies on the old ZDLRA to the replication server created previously..

NOTE: When these steps are completed, the old ZDLRA will replicate the most recent L0 to the new ZDLRA, and will then replicate all new incremental backups and archive logs.

2. Switch to new ZDLRA for backups

Update the wallet on all clients to include the VPC user/Scan listener of the new ZDLRA.

Update the real-time redo configuration (if using real-time redo) to point to the new ZDLRA.

Update backup jobs to open channels to the new ZDLRA

Remove the VPC replication user from the new ZDLRA

Drop the replication server on the old ZDLRA

NOTE: The backups will begin with an incremental backup based on the contents of the new ZDLRA and will properly create a "virtual full". Archive logs will automatically pick up with the sequence number following the last log replicated from the old ZDLRA.

3 . Configure "Read-Only Mode" replication to old ZDLRA

Add a replication VPC user on the old ZDLRA. This will be used to connect from the new ZDLRA.

Create a replication server from new ZDLRA to the old ZDLRA

Grant the replication VPC user on the old ZDLRA access to all databases for replication.

This can be done by dynamically executing DBMS_RA.GRANT_DB_ACCESS

The current list of databases can be read from the RA_DATABASE view.

Add a replication server for each policy that includes the "Read-Only" flag set to "YES".

NOTE: this will allow the new ZDLRA to pull backups from the old ZDLRA that only exist on the old ZDLRA.

4 . Retire old ZDLRA after cutover period

Remove replication server from new ZDLRA that points to old ZDLRA

NOTE: The old ZDLRA can now be decommissioned.

That's all there is to it. This will allow you to restore from the new ZDLRA, and not have to keep track of which backups are on which appliance during the cutover window !

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.

Bryan's Oracle Blog