Tuesday, December 19, 2023

ZFSSA can be used to share data from your Oracle Database

Data Sharing has become a big topic recently, and Oracle Cloud has added some new services to allow you to share data from an Autonomous Database. But how do you do this with your on-premises database ? In this post I show you how to use ZFS as your data sharing platform.

Data Sharing

Being able to securely share data between applications is a critical feature in todays world. The Oracle Database is often used to consolidate and summarize collected data, but is not always the platform for doing analysis. The Oracle Database does have the capability to analyze data, but tools such as Jupyter Notebooks, Tableu, Power Bi, etc are typically the favorites of Data Scientists and data analysts.

The challenge is how to give access to specific pieces of data in the database without providing access to the database itself. The most common solution is to use object storage and pre-authenticated URLs. Data is extracted from the database based on the user and stored in object storage in a sharable format (JSON for example). With this paradigm, and pictured above, you can create multiple datasets that contain a subset of the data specific to the users needs and authorization. The second part is the use of a pre-authenticated URL. This is a dynamically created URL that allows access to the object without authentication. Because it contains a long string of random characters, and is only valid for a specified amount of time, it can be securely shared with the user.

My Environment

For this post, I started with an environment I had previously configured to use DBMS_CLOUD. My database is a 19.20 database. In that database I used the steps specified in the MOS note and my blog (information can be found here) to use DBMS_CLOUD.

My ZFSSA environment is using 8.8.63, and I did all of my testing in OCI using compute instances.

For preparation to test I had

Installed DBMS_CLOUD packages into my database using MOS note #2748362.1
Downloaded the certificate for my ZFS appliance using my blog post and added them to wallet.
Added the DNS/IP addresses to the DBMS_CLOUD_STORE table in the CDB.
Created a user in my PDB with authority to use DBMS_CLOUD
Created a user on ZFS to use for my object storage authentication (Oracle).
Configured the HTTP service for OCI
Added my public RSA key from my key par to the OCI service for authentication.
Created a bucket

NOTE: In order to complete the full test, there were 2 other items I needed to do.

1) Update the ACL to also access port 80. The DBMS_CLOUD configuration sets ACLs to access websites using port 443. During my testing I used port 80 (http vs https).

2) I granted execute on DBMS_CRYPTO to my database user for the testing.

Step #1 Create Object

The first step was to create an object from a query in the database. This simulated pulling a subset of data (based on the user) and writing it to a object so that it could be shared. To create the object I used the DBMS_CLOUD.EXPORT_DATA package. Below is the statement I executed.

BEGIN
 DBMS_CLOUD.EXPORT_DATA(
       credential_name => 'ZFS_OCI2',  
    file_uri_list =>'https://zfs-s3.zfsadmin.vcn.oraclevcn.com/n/zfs_oci/b/db_back/o/shareddata/customer_sales.json',
    format => '{"type" : "JSON" }',
   query => 'SELECT OBJECT_NAME,OBJECT_TYPE,STATUS FROM user_objects');
END;
/

In this example:

CREDENTIAL_NAME - Refers to my authentication credentials I had previously created in my database.
FILE_URI_LIST - The name and location of the object I want to create on the ZFS object storage.
FORMAT - The output is written in JSON format
QUERY - This is the query you want to execute and store the results in the object storage.

As you can see, it would be easy to create multiple objects that contain specific data by customizing the query, and naming the object appropriately.

In order to get the proper name of the object I then selected the list of objects from object storage.

set pagesize 0
SET UNDERLINE =
col object_name format  a25
col created format  a20
select object_name,to_char(created,'MM/DD/YY hh24:mi:ss') created,bytes/1024  bytes_KB 
       from dbms_cloud.list_objects('ZFS_OCI2', 'https://zfs-s3.zfsadmin.vcn.oraclevcn.com/n/zfs_oci/b/db_back/o/shareddata/');


 customer_sales.json_1_1_1.json 12/19/23 01:19:51    3.17382813

From the output, I can see that my JSON file was named 'customer_sales.json_1_1_1.json'.

Step #2 Create Pre-authenticated URL

The Package I ran to do this is below. I am going to break down the pieces into multiple sections. Below is the full code.



DECLARE

   tenancy_ocid           VARCHAR (100)  ;
   user_ocid               VARCHAR (100)  ;
   fingerprint             VARCHAR (100)  ;
   rest_api                VARCHAR (100)  ;
   host                    VARCHAR (100)  ;
   
   body                    VARCHAR (2000) ;

   URL                     varchar (20);
   Bucket                  varchar (20);
   compartment             varchar (20);
   object_name             varchar (200);


   content_sha256          VARCHAR (2000) ;
   content_sha256_header   VARCHAR (2000) ;
   content_length          VARCHAR (2000) ;
   content_length_header   VARCHAR (200)  ;

   headers                 VARCHAR (200)  ;

  
   content_type_header     VARCHAR (200)  ;


   vardate                 VARCHAR (200)  ;
   date_header             VARCHAR (200)  ;
   host_header             VARCHAR (200)  ;
   request_target          VARCHAR (200)  ;
   signing_string          VARCHAR (2000)  ;

   ip_str                  VARCHAR (2000) ;
   post_request            VARCHAR (2000) ;


  pubkey                   VARCHAR (2000) ;
  
  prvkey                   VARCHAR (2000) ; 
  sign_raw                 RAW (2000);
  signature                varchar (2000);
  signature_final          varchar (2000);
  authorization_header     varchar (2000);
  position64               integer :=1;
  returnval                BOOLEAN := false;
  sType                    PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA256_RSA;
  kType                    PLS_INTEGER := DBMS_CRYPTO.KEY_TYPE_RSA;

  req                      utl_http.req;
  res                      utl_http.resp;
  buffer                   varchar2(4000); 
  json_obj                 json_object_t;
  url_string               varchar2(400);


BEGIN

/* ##########################################################################################
   #                                                                                        #
   #                          Set Variables                                                 #
   #                                                                                        #
   ########################################################################################## */

/* # The tenancy. */
  tenancy_ocid := 'ocid1.tenancy.oc1..nobody';
  user_ocid    := 'ocid1.user.oc1..oracle';

/* # The fingerprint. */
   fingerprint  := '6d:f9:57:d5:ff:b1:c0:98:81:90:1e:6e:08:0f:d0:69';


/* # The URL */
   URL := '/oci';


/* # The bucket */
   bucket := 'db_back';


/* # The compartment/share on ZFS */
   compartment := 'zfs_oci';
    

/* # The REST api you want to call, with any required paramters. */
    rest_api    := URL || '/n/' || compartment || '/b/' || bucket || '/p?compartmentId=' || compartment ;



/* # The host you want to make the call against */
   host            := 'zfs-s3.zfsadmin.vcn.oraclevcn.com';


/* # Get the private key to sign the request */
prvkey    := 'MIIEowIBAAKCAQEAzdfuSSwga9ziJTQK6RKB86VkBRnsIUrNGKQVKAkGaG5V+NjjJXvZt3a7p70NykHD4ZKO5ShbTBxYMCHc6uIjAJe/5moGog267K4q1Wa1WJIcL0/H5t/MWKMjToQ+qIz2K21zsOCAgf9YBY3XoQsK3RvPcCO2zbp0g0PZ5cfOUxNGQP7o2aPltymx6OrzMMXWfr7vgnMWeif86AAgdh4yfyQmNzjZFjwz1RDwYQYA50iLGSEVf0UIxDqxe8qCIMzPe5J8Drhw10NKB95ZSj++GKZ6wpv1ScClzuxS2kZYHqWfIYpAZal/Xmpr5o6GVyMyeAYxZ3TnCsXH7PhrnIbb9QIDAQABAoIBADKIlTYfgCIayuXpPdh0M7T14HUkJQUTUQCAn7UBajJPDAoYbMMyAYP5wR3v2qfRt130VeJKIwevWbMPTSXYuFEX8AOH+fLkvgQGyfszyMdir0qqImA6hvcnra5D5wIIARMuPp2or8O6RQiwBsTBajxX7HgSPHmLEbl7+c1gXPWlr4f1Qu7Fzas3plNjdkTRb+Dewr6bE1k/EVhEGm/LIHZn6bObx2pUZ2RHSVWuyZjqgLN7mcmC0z9tMumvdAVbi6SDNsp6wfk21kkywo5eKe2nYjqm4ezWCYX7ZLsjJg0jIzjO0Y7u1x/8lyvsgVuwU74SY84hOgJYEQy7MG9HZ+ECgYEA+1Qm6XT1H/YX9O+bZ3eEZH6AgTAq9PIgqWV8+1InvH4u6HT5OMccxc2RsxxCUXarv3jGJAme43jLaCeP/CkjBhcVpRQT1PLv4k8Sf4ABO3GLEP2qdM7sk/dJbFbDplO1sS0ZZh8AKcntLzN99trvZ+E9BvJXmBroyjstoJSUg20CgYEA0ata94Q9SOFleMK82Oflm+gs7zbuyebSmh6lBn8gal0szW5nOsKce48ht71nRFQU4rLCkglcjxH8s8+BSVDaoo05LeR8SDKpPV1qdd0itVFe+agHyOGuTlZnjh3u+B3xyRKn9tdSiUNopSZ1wMqy3p1/k8BgFjAAUvu6o+nD3akCgYATZaFDL1Wjy9h5fOyfhafgIatFTTRjhzRiHoUQ2Hxcndh89FovuZ5M3dq62hl8NA55TpbsPRnYMIByX2tLaNldJPb0sKH/M2i0LKoe0QBHWDIFc65uKXI00UcHZ4LeGWPScsqdCRN4Ji96Dx1PqgIEIGwXVyjPgnQo93xEAnOb2QKBgDjSaJ/FzdoPg7F7jTR3fEmJXYwi5gn/rTmpbm3ZTA6nW7n83qsVDNWLj8a85NymsNZTPdnYAaagaEzOwqn86JO/FCz0zB3A5Q7FX1G78WkUOVoNiLaPOu7uCbiBMj851kn47V4/MpYSl6ySRL1P8bPVGTsQmrnInn3ezNEZTESpAoGBAPRTl9d3l8LtlVD10qDyunMyvfeLXvBMSq4Nje7EfTT5wGwRUIFabvM/KSM0NMW1eaEKO2PxeTpcvVarJykbLh3caSra/ptJ0/Qm2F595iIuwNeAt3pMkpr+gVtp21re+vFw6JUw/3SUirXvPWPUogN+6h0/wlIcWQr7CCerY452'; 


  
/* # Set the current date/time to the correct format */

select to_char (systimestamp at time zone 'GMT','Dy, dd Mon yyyy hh24:mi:ss') || ' GMT' into vardate from dual;



/* ##########################################################################################
   #                                                                                        #
   #                          Set rest API URL call                                         #
   #                                                                                        #
   ########################################################################################## */


/* # The REST api you want to call, with any required paramters. */
rest_api    := URL || '/n/' || compartment || '/b/' || bucket || '/p?compartmentId=' || compartment ;


/* ##########################################################################################
   #                                                                                        #
   #                          Set The Post Request                                           #
   #                                                                                        #
   ########################################################################################## */


post_request    :=  'POST http://' || host || rest_api || ' Date: ' || vardate;


/* ##########################################################################################
   #                                                                                        #
   #                          Set The body as JSON                                          #
   #                                                                                        #
   ########################################################################################## */



/* # the json file containing the data you want to POST to the rest endpoint */


object_name := 'shareddata/customer_sales.json_1_1_1.json';

body    := '{'                                             || chr(10) || 
  	   ' "accessType": "ObjectRead",'         || chr(10) || 
  	   ' "bucketListingAction": "Deny",'       || chr(10) || 
           ' "bucketName": "' || bucket || '",'           || chr(10) || 
           ' "name": "mytestforjsonx1",'                   || chr(10) || 
           ' "namespace": "' || compartment || '",'       || chr(10) || 
           ' "namespaceName": "' || compartment || '",'   || chr(10) || 
           ' "objectName": "' || object_name || '",'      || chr(10) || 
           ' "timeExpires": "2024-11-01T00:00:00+00:00"'  || chr(10) || 
           '}';




select  utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_crypto.hash (utl_i18n.string_to_raw (body || chr(10), 'AL32UTF8'),dbms_crypto.hash_sh256))) into content_sha256 from dual;




/* ##########################################################################################
   #                                                                                        #
   #                          Set The signing string                                        #
   #                                                                                        #
   ########################################################################################## */




content_sha256_header  := 'x-content-sha256: ' || content_sha256 ;
content_type_header    := 'content-type: application/json';
content_length         := length(body);
content_length_header  := 'content-length: ' || content_length ;
date_header            := 'date: ' || vardate;
host_header            := 'host: ' || host;

headers                := '(request-target) date host x-content-sha256 content-type content-length';

request_target :=   '(request-target): post ' || rest_api ; 

signing_string          := request_target || chr(10) || date_header || chr(10) || host_header;

signing_string          :=  signing_string || chr(10) || content_sha256_header || chr(10) || content_type_header || chr(10) || content_length_header;



sign_raw := DBMS_CRYPTO.SIGN
  (
   src        => UTL_I18N.STRING_TO_RAW(signing_string,'AL32UTF8'),
   prv_key    => UTL_I18N.STRING_TO_RAW( prvkey, 'AL32UTF8'),
   pubkey_alg => kType,
   sign_alg   => sType
  );





/* ##########################################################################################
   #                                                                                        #
   #         encode the signing string in Base64 and build the final signature              #
   #                                                                                        #
   ########################################################################################## */




select utl_raw.cast_to_varchar2(utl_encode.base64_encode(sign_raw)) into signature from dual;


  signature_final := '';

  while position64 < length(signature) loop
    if position64 + 63 > length(signature) THEN
       signature_final := signature_final || substr(signature,position64,length(signature) - (position64-1));

     else
       signature_final := signature_final || substr(signature,position64,64);
    end if;

    position64 :=position64 + 66;
  end loop;






/* ##########################################################################################
   #                                                                                        #
   #         Create the authorization header string including the signature string          #
   #                                                                                        #
   ########################################################################################## */



authorization_header                := 'Signature version="1",keyId="' || tenancy_ocid || '/' || user_ocid || '/' || fingerprint || '",algorithm="rsa-sha256",headers="(request-target) date host x-content-sha256  content-type content-length",signature="' || signature_final || '"';





/* ##########################################################################################
   #                                                                                        #
   #         Send the post request to create the pre-authenticated URL                      #
   #                                                                                        #
   ########################################################################################## */

 req := utl_http.begin_request('http://' || host || rest_api, 'POST',' HTTP/1.1'); 

         utl_http.set_header(req, 'date', vardate); 
         utl_http.set_header(req, 'x-content-sha256', content_sha256);
         utl_http.set_header(req, 'Content-Type', 'application/json'); 
         utl_http.set_header(req, 'Content-Length', content_length); 
         utl_http.set_header(req, 'Authorization', authorization_header); 

         utl_http.write_text(req, body); 


/* ##########################################################################################
   #                                                                                        #
   #         Read through the response and display the resulting URL                        #
   #                                                                                        #
   ########################################################################################## */

  res := utl_http.get_response(req);

 begin
    loop
      utl_http.read_line(res, buffer);

          dbms_output.put_line(' '); 
          json_obj  := json_object_t.parse (buffer);
          url_string := json_obj.get('accessUri').to_string;
          dbms_output.put_line('URI to use   :   http://' || host || substr(url_string,2,length(url_string)-2));
          dbms_output.put_line(' '); 

    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;

END;

/

Step #2a Declare variables

The first part of the pl/sql package declares the variables that will be used in the package. Most of the variables are normal VARCHAR variables, but there a re a few other variable types that are specific to the packages used to encrypt and send the URL request.

sType,kType - These are constants used to sign the URL request with RSA 256 encryption
utl_http.req,utl_http.resp - These are request and response types used when accessing the object storage
json_obj - This type is used to extract the url from the resulting JSON code returned from the object storage call.

Step #2b Set variables

In this section of code I set the authentication information along with the host, and the private key part of my RSA public/private key pair.

I also set a variable with the current date time, in the correct GMT format.

NOTE: This date time stamp is compared with the date time on the ZFSSA. It must be within 5 minutes of the ZFSSA date/time or the request will be rejected.

Step #2c Set JSON body

In this section of code, I build the actual request for the pre-authenticated URL. The parameters for this are...

accessType - I set this to "ObjectRead" which allows me to create a URL that points to a specific object. Other options are Write, and ReadWrite.
bucketListingAction - I set this to "Deny", This disallows the listing of objects.
bucketName - Name of the bucket
name - A name you give the request so that it can be identified later
namespace/namepaceName - This is the ZFS share
objectName - This is the object name on the share that I want the request to refer to.
timeExpires - This is when the request expires.

NOTE: I did not spend the time to do a lot of customization to this section. You could easily make the object name a parameter that is passed to the package along with the bucketname etc. You could also dynamically set the expiration time based on sysdate. For example you could have the request only be valid for 24 hours by dynamically setting the timeExpires.

The last step in this section of code is to create a sha256 digest of the JSON "body" that I am sending with the request. I created it using the dbms_crypto.hash.

Step #2d Create the signing string

This section builds the signing string that is encrypted. This string is set in a very specific format. The string that is build contains.

(request-target): post /oci/n/{share name}/b/{bucket name}/p?compartment={compartment}

date: {date in GMT}

host: {ZFS host}

x-content-sha256: {sha256 digest of the JSON body parameters}

content-type: application/json

content-length: {length of the JSON body parameters}

NOTE: This signing string has to be created with the line feeds.

The final step in this section is sign the signing string with the private key.

In order to sign the string the DBMS_CRYPTO.SIGN package is used.

Step #2e Build the signature from the signing string

This section takes the signed string that was built in the prior step and encodes the string in Base 64. This section uses the utl_encode.base64_encode package to sign the raw string and it is then converted to a varchar.

Note: The resulting base64 encoded string is broken into 64 character sections. After creating the encoded string, I loop through the string, and combine the 64 character sections into a single string.

This took the most time to figure out.

Step #2f Create the authorization header

This section dynamically builds the authorization header that is sent with the call. This section includes the authentication parameters (tenancy OCID, User OCID, fingerprint), the headers (these must be in the order they are sent), and the signature that was created in the last 2 steps.

Step #2g Send a post request and header information

The next section sends the post call to the ZFS object storage followed by each piece of header information. After header parameters are sent, then the JSON body is sent using the utl_http.write_text call.

Step #2h Loop through the response

This section gets the response from the POST call, and loops through the response. I am using the json_object_t.parse call to create a JSON type, and then use the json_obj.get to retrieve the unique request URI that is created.

Finally I display the resulting URI that can be used to retrieve the object itself.

Documentation

There were a few documents that I found very useful to give me the correct calls in order to build this package.

Signing request documentation : This document gave detail on the parameters needed to send get or post requests to object storage. This document was very helpful to ensure that I had created the signature properly.

Http message signature format : This document gives detail on the signature itself and the format.

OCI rest call walk through : This post was the most helpful as it gave an example of a GET call and a PUT call. I was first able to create a GET call using this post, and then I built on it to create a GET call.

Monday, December 4, 2023

File Retention on ZFS now supports expired file deletion/holds and changing permissions

The latest release of ZFS (8.8.63) contains 2 new features associated with File Retention lock.

File retention (deletion or hold) after file expiration
Allow permission changes on retained files.

File retention on expiry policy

This new setting for projects/shares defaults to "off" which is the normal behavior of unlocking files, but leaving them on the filesystem. In order to delete files you need to wait until the retention period expires, and then you can delete the file.

There are 2 new settings you can use to work with locked files to change this behavior.

Delete

When set to "Delete", files will be immediately deleted when their retention lock expires. This can be very useful if you want files to be automatically cleaned up at the end of their retention without having to create a deletion process.

There are a few items to be aware of the automatic deletion process.

DO NOT use this with an RMAN retention window. Customers typically use a weekly full/daily incremental backup strategy with RMAN. With this strategy, a weeks worth of backups (all dependent on the oldest full backup) are deleted together. Deleting backups as soon as they expire would delete backups too soon. Even with archival backups I recommend letting RMAN perform the deletions, otherwise you risk having a file deleted too early.
Be careful changing this setting on an existing share. This setting takes effect immediately and will affect ALL files that have a retention lock. Any files that were locked, but their retention lock expired will be deleted when this setting is applied.

Hold

When set to "Hold", any files that have, or have had a retention lock will be affected. This setting immediately prevents the deletion of all retention locked files until the hold is removed regardless of when the lock is set to expire. Keep in mind that while a hold is in place, the files still have a retention lock with an expiration date.

Removing the hold: When you remove the hold, the normal expiration date takes effect. If you remove the hold by changing the expiry policy to "Delete", ALL files that have an expired retention will be immediately deleted. If you change the expire policy to "Off", the files remain, and you must manually delete them.

NOTE: Be very careful when changing the Expiry Policy. The new setting immediately affects existing files, not just new files going forward, unlike the other file retention settings.

Allow permission changes on retained files

What happens normally : When a file has a retention lock set, you are protecting this file from both being deleting AND from being updated. Because you were not allowed to update the file permissions, you were not able to change the settings from the default of -r--r-----+ while the file was locked.

This could be an issue depending on what type of file you are protecting. There are some cases where you want to make this file either

An executable file, not just a read only file.
readable by any user.

When you attempted to make this change to a locked file the update would fail with a "Operation not permitted".

-r--r-----+ 1 oracle oinstall 792 Dec  4  2023 testfile
[oracle@ssh-server rmanbackups]$ ls -al testfile
-r--r-----+ 1 oracle oinstall 792 Dec  4 20:26 testfile
[oracle@ssh-server rmanbackups]$ chmod 550 testfile
chmod: changing permissions of 'testfile': Operation not permitted
[oracle@ssh-server rmanbackups]$ chmod 444 testfile
chmod: changing permissions of 'testfile': Operation not permitted
[oracle@ssh-server rmanbackups]$

What this setting does: When you check the setting for "Allow permission changes on retained files" you are IMMEDIATELY able to change the permissions on files that are locked. The files are still protected from making them writable, but you can adjust both the "r" - read and "x" execute settings for all users.

NOTE: this setting does take affect immediately and will affect all currently locked files regardless of when they were created.

Wednesday, November 22, 2023

Oracle Database Backup Cloud Service Primer

One topic that has been coming a lot as customers look at options for offsite protected backups, is the use of the Oracle Database Backup Cloud Service. This service can be used either directly from the database itself leveraging an RMAN tape library, or by performing a copy-to-cloud from the ZDLRA. In this post I will try to consolidate all the information I can find on this topic to get you started.

Overview

The best place to start is by downloading, and reading through this technical brief.

This document walks you through what the service is and how to implement it. Before you go forward with the Backup Cloud Service I suggest you download the install package and go through how to install it.

The key points I saw in this document are

RMAN encryption is mandatory - In this brief you will see that the backups being sent to OCI MUST be encrypted, and the brief explains how to create an encrypted backup. Included in the Backup Cloud Service is the use of encryption and compression (beyond basic compression) without requiring the ASO, or ACO license.
How to install the client files - The brief explains the parameters that are needed to install the client files, and what the client files are that get installed. I will go into more detail later on explaining additional features that have been added recently.
Config file settings including host - The document explains the contents of the configuration file used by the Backup Cloud Service library. It also explains how to determine the name of the host (OCI endpoint) based on the region you are sending the backups to.
Channel configuration example - There is an example channel configuration to show you how to connect to the service.
Best practices - The document includes sample scripts and best practices to use when using the Backup Cloud Service.
Lifecycle policies and storage tiers - This is an important feature of using the Backup Cloud Service, especially for long term archival backups. You most likely want have backups automatically moved to low cost archival storage after uploading to OCI.

NOTE: When using lifecycle polies to manage the storage tiers it is best to set the "-enableArchiving" and "-archiveAfterBackup" parameters when installing the backup module for a new bucket. There are small metadata files that MUST remain in standard storage, and the installation module creates a lifecycle rule with the bucket that properly archives backup pieces, leaving the metadata in standard storage.

Download

The version of the library on OTN (at the time I am writing this) is NOT the current release of the library, and that version does not support retention lock of objects.

Please download the library from this location.

Documentation on the newer features can be found here, using retention lock can be found here, and there is a oci_readme.txt file that contains all the parameters available.

Updates

There were a few updates since the tech brief was written, and I will summarize the important ones here. I also spoke the PM who is working on an updated brief that will contain this new information.

newRSAKeyPair - The installer is now able to generate the key pair for you making it much easier to generate new key pair. In order to have the installer ONLY create a new key pair pair, just pass the installer the "walletDir" parameter. The installer will generate both a public and private key, and place them in the walletDir (see below).

 /u01/app/oracle/product/19c/dbhome_1/jdk/bin/java -jar oci_install.jar -newRSAKeyPair -walletDir /home/oracle/oci/wallet 
Oracle Database Cloud Backup Module Install Tool, build 19.18.0.0.0DBBKPCSBP_2023-09-21
OCI API signing keys are created:
  PRIVATE KEY --> /home/oracle/oci/wallet/oci_pvt
  PUBLIC  KEY --> /home/oracle/oci/wallet/oci_pub
Please upload the public key in the OCI console.

Once you generate the public/private key, you can upload the public key to the OCI console. This will show you the fingerprint, and you can execute the installer using the private key file.

"immutable-bucket" and "temp-metadata-bucket" - The biggest addition to library is the ability to support the use of retention rules on buckets containing backups. The uploading of backups is monitored by using a "heartbeat" file, and this file is deleted when the upload is successful. Because all objects in a bucket are locked, the "heartbeat" object must be managed from a second bucket without retention rules. This is the temp-metadata-bucket. When using retention rules you MUST have both buckets set in the config file.

NOTE:

I ran into 2 issues when executing this script.

1) When trying to execute the jar file, I used the default java version in my OCI tenancy that is located in "/user/bin". The installer received a java error

"java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter"

In order to properly execute the installer, I used the java executable located in $ORACLE_HOME/jdk/bin

2) When executing the jar file with my own RSA key that I had been previously used with OCI object storage, I received a java error.

Exception in thread "main" java.lang.RuntimeException: Could not produce a private key
at oracle.backup.util.FileDownload.encode(FileDownload.java:823)
at oracle.backup.util.FileDownload.addBmcAuthHeader(FileDownload.java:647)
at oracle.backup.util.FileDownload.addHttpAuthHeader(FileDownload.java:169)
at oracle.backup.util.FileDownload.addHttpAuthHeader(FileDownload.java:151)
at oracle.backup.opc.install.BmcConfig.initBmcConnection(BmcConfig.java:437)
at oracle.backup.opc.install.BmcConfig.initBmcConnection(BmcConfig.java:428)
at oracle.backup.opc.install.BmcConfig.testConnection(BmcConfig.java:393)
at oracle.backup.opc.install.BmcConfig.doBmcConfig(BmcConfig.java:250)
at oracle.backup.opc.install.BmcConfig.main(BmcConfig.java:242)

Caused by: java.security.spec.InvalidKeySpecException: java.security.InvalidKeyException: IOException : algid parse error, not a sequence

I found that this was caused by the PKCS format. I was using a PKCS1 key, and the java installer was looking for a PKCS8 key. The header in my private key file contained "BEGIN RSA PRIVATE KEY".

In order to convert my private PKCS1 key "oci_api_key.pem" to a PKCS8 key "pkcs8.key" I ran.

openssl pkcs8 -topk8 -inform PEM -outform PEM -nocrypt -in oci_api_key.pem -out pkcs8.key

Executing the install

The next step is to execute the install. For my install I also wanted configure a lifecycle rule that would archive backups after 14 days. In order to implement this, I had the script create a new bucket "bsgtest". Below is parameters I used (note I used "..." to obfuscate the OCIDs).

$ORACLE_HOME/jdk/bin/java -jar oci_install.jar -pvtKeyFile /home/oracle/oci/wallet/pkcs8.key -pubFingerPrint .... -tOCID  ocid1.tenancy.oc1... -host https://objectstorage.us-ashburn-1.oraclecloud.com -uOCID ocid1.user.oc1.... -bucket bsgtest -cOCID ocid1.compartment.oc1... -walletDir /home/oracle/oci/wallet -libDir /home/oracle/oci/lib -configFile /home/oracle/oci/config/backupconfig.ora -enableArchiving TRUE -archiveAfterBackup "14 days"

This created a new bucket "bsgtest" containing a lifecycle rule.

I then added a 14 day retention rule to this bucket, and created a second bucket "bsgtest_meta" for the temporary metadata. If you want to make this rule permanent you enable retention rule lock which I highlighted on the screenshot below.

I then updated the config file to use the metadata bucket because I set a retention rule on the main bucket. Note that there is also a parameter that determines how long archival objects are cached in standard storage before they are returned to archival storage.

OPC_CONTAINER=bsgtest
OPC_TEMP_CONTAINER=bsgtest_meta
OPC_AUTH_SCHEME=BMC
retainAfterRestore=48 HOURS

Testing

Once you execute the installer you will be able to begin backing up to OCI object storage. Don't forget that you need to:

Change the default device type to SBT_TAPE
Change the compression algorithm. I recommend "medium" compression.
Configure encryption for database ON.
Configure the device type SBT_TAPE to send COMPRESSED BACKUPSET to optimize throughput and storage in OCI.
Create a default channel configuration for SBT_TAPE (or allocate channels manually) that use the library that was downloaded, and point to the configuration file for the database.
If you do not use ACO and don't have a wallet , manually set an encryption password in your session.

I recommend sending a "small" backup piece first to ensure that everything is properly configured. My favorite command is

RMAN>backup incremental level 0 datafile 1;

Datafile 1 is always the system tablespace.

Below is what my configuration looks like for RMAN specifically for what I changed to use the Backup Cloud Service.

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/home/oracle/oci/config/backupconfig.ora)';
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256'; # default
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

Network Performance

One of the big areas that comes up with using the Backup Cloud Service, is understanding the network capabilities.

The best place to start is with this MOS note

How to measure network performance from RMAN for ZDLRA or Cloud Backups (Doc ID 2371860.1)

It is a short note, but it shows you how to determine the throughput you can expect from a single stream/channel of data.

Below is an example of how to use this based on my configuration. I used it to test sending 1000M (1GB) of data. From the results I can see that a single channel can send and receive 1GB in about 15 seconds. This works out to about 240 GB/hr.

RMAN> run {
2> allocate channel foo device type sbt  PARMS  'SBT_LIBRARY=/home/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/home/oracle/oci/config/backupconfig.ora)';
3>  send channel foo 'NETTEST 1000M';
4> }

allocated channel: foo
channel foo: SID=431 device type=SBT_TAPE
channel foo: Oracle Database Backup Service Library VER=19.0.0.1

released channel: foo
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of send command at 11/22/2023 14:12:04
ORA-19559: error sending device command: NETTEST 1000M
ORA-19557: device error, device type: SBT_TAPE, device name:
ORA-27194: skgfdvcmd: sbtcommand returned error
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
   KBHS-00402: NETTEST sucessfully completed
KBHS-00401: NETTEST RESTORE: 1048576000 bytes received in 15068283 microseconds
KBHS-00400: NETTEST BACKUP: 1048576000 bytes sent

Executing Backups

Now to put it all together I am going to execute a backup of datafile 1. My database is encrypted, so I am going to set a password along with the encryption key.

 set encryption on identified by oracle;

executing command: SET encryption

RMAN>  backup incremental level 0 datafile 1;

Starting backup at 22-NOV-23
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=404 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=494 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=599 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Oracle Database Backup Service Library VER=19.0.0.1
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=691 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Oracle Database Backup Service Library VER=19.0.0.1
channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ACMEDBP/system01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 22-NOV-23
channel ORA_SBT_TAPE_1: finished piece 1 at 22-NOV-23
piece handle=8t2c4fmi_1309_1_1 tag=TAG20231122T150554 comment=API Version 2.0,MMS Version 19.0.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-NOV-23

Starting Control File and SPFILE Autobackup at 22-NOV-23
piece handle=c-1654679317-20231122-01 comment=API Version 2.0,MMS Version 19.0.0.1
Finished Control File and SPFILE Autobackup at 22-NOV-23

Restoring

Restoring is very easy as long as you have the entries in your controlfile. If you don't then there is a

script included in the installation that can catalog the backup pieces and I go through that process here.

This also allows you to display what's in the bucket.

Buckets 1 vs many

If you look at what created when executing backup you will see that there is a set format for the backup pieces. Below are the 2 backup pieces that I created

8t2c4fmi_1209_1_1 - This is the backup of datafile 1 for my database ACMEDBP
c-16546791317-20231122-01 - This is the controlfile backup for this database

Notice that the DB name is not in the name of the backup pieces, or in the visible nesting.

If you think about a medium sized database (let's say 100 datafiles), that has 2 weeks of backups (14 days), you would have 1,400 different backup pieces for the datafiles within the "sbt_catalog" directory.

My recommendation is to group small databases together in the same bucket (keeping the amount of backup pieces to a manageable level).

For large database (1,000+ datafiles), you can see where a 30 day retention could become 30,000+ backup pieces.

Having a large number of objects within a bucket increases the time to report the available backup pieces. There is no way to determine which database the object is a member of without looking at the metadata.

Keep this in mind when considering how many buckets to create.

Monday, October 23, 2023

Oracle Recovery Service now offers retention lock

Oracle DB Recovery Service recently added a new feature to protect backups from being prematurely deleted, even by a tenancy administrator. This new feature adds a retention lock to the Backup Retention Period at the policy level. The image below shows the new settings that you see within the protection policy.

Enabling retention lock

The recovery service comes with some default policies that appear as "oracle defined" policy types

Name Backup retention period

Platinum 46 days

Gold 65 days

Silver 35 days

Bronze 14 days

These policies can't' be changed, and they do not enable retention lock.

In order to implement a retention lock you need to create a new protection policy or update an existing user defined protection policy.

Step #1 Set/Adjust "Backup retention period"

If you are creating a new "user defined" protection policy, you need to set the backup retention to a number of days between 14 and 95. You should also take this opportunity to adjust the backup retention of an existing policy, if appropriate, before it is locked.

NOTE: Once a retention lock on the protection policy is activated (discussed in step #3), the backup retention period cannot be decreased, it can only be increased.

Step #2 Click on "enable retention lock"

This step is pretty straightforward. But the most important item to know is that the retention lock is not immediately in effect. Much like the "retention lock" that is set on object storage, there is a minimum period of at least 14 days before the lock is "active".

Note: Once the grace period has expired for the policy (explained later in this blog post) the "retention lock" is permanent and cannot be removed.

Step #3 Set "Scheduled lock time"

As I said in the previous step, the lock isn't immediately active. In this step you set the future date/time that the lock time becomes active, and this Date/Time must be at least 14 days in the future. This provides a grace period that delays when the lock on the policy becomes active. You have up until the lock activation date/time to adjust the scheduled lock time further into the future if it becomes necessary to further day lock activation.

Grace Period

I wanted to make sure I explain what happens with this grace period so that you can plan accordingly.

If you change an existing "user defined" policy to enable the retention lock, any databases that are a member of this policy will not have locked backups until the scheduled lock date/time activates the lock.

If you add databases to a protection policy that has a retention lock enabled, the backups will not be locked until whichever time is farther in the future.

Scheduled lock time for the policy if the retention lock has not yet activated.
14 days after the database is added to the protection policy.

Databases can be removed from a retention locked protection policy during this grace period.

If the policy itself is still within it's grace period from activating, the backup retention period can be adjusted down for the protection policy.

NOTE: This 14 day grace period allows you to review the estimated space needed. On the protected database summary page, for each database, you can see the "projected space for policy" in the Space Usage section. This value can be used to estimate the "locked backup" utilization.

What happens with a retention lock ?

Once the grace period expires the backups for the protected database are time locked and can't be prematurely deleted.

The backups are protected by the following rules.

1. The database cannot be moved to another policy. No user within the tenancy, including an administrator can remove a database from it's retention enabled policy. If it becomes necessary to move a database to another policy , an SR needs to raised, and security policies are followed to ensure that this is an approved change.

2. There is always a 14 day grace period in which changes can be made before the backups become locked. This is your window to verify the backup storage usage required before the lock activates.

3. Even if you check the "72 hour termination option" on the database, backups are locked throughout the retention window.

Comments:

This is a great new feature that protects backups from being deleted by anyone in the tenancy, including tenancy administrators. This provides an extra layer of security from an attack with compromised credentials. Because the lock is permanent, always use the 14 day grace period to ensure the usage and duration is appropriate for you database.

Wednesday, October 4, 2023

Cyber Vault Characteristics

One topic that has been coming up over and over this year is Cyber Vault. In this post I am going to through the characteristics I commonly see when a customer build a Cyber Vault. The image below gives you a good idea of what is involved.

Characteristics of a Cyber Vault

NTP and DNS services.: Because a Cyber Vault is often isolated from the rest of the datacenter it is critical to have NTP service. Proper time management is critical to ensuring backups are kept for the proper retention. DNS isn't critical, but it is definitely very helpful in configuring infrastructure. In many cases "/etc/hosts" can get around this, but is a pain to maintain.

Firewalls: Configuring firewalls, and isolated networks is critical to ensure the Cyber Vault is isolated. The vault is often physically in the same datacenter, with network isolation providing the protection. Be sure to understand what ports, networks, and traffic direction is utilized on all infrastructure so you can proper set firewall rules.

Air Gap: Creating an Air-Gap has become the standard to protect backups in the Cyber Vault. The Air Gap is often open for only a few hours a day at random times to ensure that the opening isn't predictable. To limit the exposure time, it is critical to maximize the networking into the vault, and minimize the amount of data necessary to transfer.

NOTE: Not all customers choose to have an Air Gap. Having an Air Gap that is closed for long periods of times ensures there is less chance of intrusions, BUT it guarantees long periods of data loss when a restoration is performed. This is most critical to decide with databases that are always changing.

Break-the-glass: There needs to be control on who gets access into the vault, and an approval process to ensure that all access is planned and controlled.

Backup validation: There needs to be a validation process in a vault to ensure that the backups are untouched. When the backups contain executables, this is typically scanning for ransomware signatures. When backups are Oracle Backups, performing "Restore Database Validate" is the gold standard for validating backups.

Clean Room: A clean room is an environment where backups can tested, This can be a small environment (a server or 2) or it can be large enough to restore and run the whole application.

Monitoring and reporting infrastructure : For Oracle this OEM (Cloud Control). It is critical that any issues are alerted and reported outside the vault.

Audit Reports: Audit reports are critical to ensuring that the backups in the Cyber Vault are secured. Audit reports will capture any changes to the environment, and any issues with the backups themselves.

BONUS: One thing that customers don't often think about is encryption keys. Implementing TDE on Oracle Databases is an important part of protecting your data from exfiltration. But you should also ensure that you have a secure backup of you encryption keys in the Vault.

OKV (Oracle Key Vault) is the best way of managing the keys for Oracle databases.

Friday, September 29, 2023

DG per PDB with 23c on DBCS (Oracle Base Database)

At Oracle Cloud World (a few weeks ago as of writing this) 23c was announced as being available with DBCS.

I've been wanting to test out DGPDB, which is Data Guard per PDB. This new feature was introduced with 21c and is covered in detail in this blog here .

Since I cover a lot of topics around backup, recovery, and availability this feature intrigued me.

NOTE: DGPDB is NOT supported with the tooling on DBCS and by testing it in your environment as I am going to do here your databases will not be supported.

First I am going to start with the documentation which can be found here.

My Environment :

I created 2 database services in ExaCS, both of which are 23c databases that are TDE encrypted (since they are in DBCS), and I am using a locally managed key (local wallet file).

CDB Name	DB Unique Name	PDB name
cdb1	cdb1_c83_iad	pdb1
cdb2	cdb2_cdx_iad	cdb2_pdb2

Step # 1 - Prepare the 2 CDBs

The first step is to prepare the 2 CDBs and follow the steps in the documentation

On both databases execute

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE FLASHBACK ON;

On the source database execute

alter system set dg_broker_start=true scope=both;
alter system set log_archive_dest_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb1_c83_iad";
alter system set standby_file_management=auto scope=both; ;

On the destination database execute

alter system set dg_broker_start=true scope=both;
alter system set log_archive_dest_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb2_cdx_iad";
alter system set standby_file_management=auto scope=both;

Step # 2 - Configure tnsname.oras and SEPS wallets

First I am going to ensure that both tnsnames.ora files contains entries for both CDBs.

CDB1_C83_IAD
CDB2_CDX_IAD

Next I create a SEPS wallet on both nodes, and add entries for both databases

mkdir -p $ORACLE_HOME/dbs/wallets/dgpdb
mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -createALO
mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -createCredential cdb1_c83_iad sys {password}
mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -createCredential cdb2_cdx_iad sys {password}
mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -listCredential

Then I update the sqlnet.ora to use this wallet file.

WALLET_LOCATION =
    (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
        (DIRECTORY = /u01/app/oracle/product/23.0.0.0/dbhome_1/dbs/wallets/dgpdb)
    )
)
SQLNET.WALLET_OVERRIDE = TRUE

And finally I test the connection

sqlplus /@cdb1_c83_iad as sysdba
sqlplus /@cdb2_cdx_iad as sysdba

Step # 3 - Create source and target configuration

Configure the source


dgmgrl /@cdb1_c83_iad <<EOF

CREATE CONFIGURATION 'cdb1_c83_iad' AS CONNECT IDENTIFIER IS cdb1_c83_iad; 
SHOW CONFIGURATION; 

EOF

Configure the target

dgmgrl /@cdb2_cdx_iad <<EOF

CREATE CONFIGURATION 'cdb2_cdx_iad' AS CONNECT IDENTIFIER IS cdb2_cdx_iad; 
SHOW CONFIGURATION; 

EOF

Step # 4 - Establish the connection between configurations

I ran the step in the documentation to configure the connection.


dgmgrl /@cdb1_c83_iad <<EOF

ADD CONFIGURATION 'cdb2_cdx_iad' CONNECT IDENTIFIER IS cdb2_cdx_iad; 
SHOW CONFIGURATION; 
ENABLE CONFIGURATION ALL; 
SHOW CONFIGURATION; 
EOF

And I validated that the output looked correct.

Configuration - cdb1_c83_iad

Protection Mode: MaxPerformance
Members:
cdb1_c83_iad - Primary database
cdb2_cdx_iad - Primary database in cdb2_cdx_iad configuration

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 1 second ago)

Step # 5 - Prepare the databases for DGPDB

The first step was to ensure that the PDBS are open in both database, which they are.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL>

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB2_PDB1                      READ WRITE NO
SQL>

Then prepare the databases for DGPDB

 dgmgrl /@cdb1_c83_iad
DGMGRL for Linux: Release 23.0.0.0.0 - Production on Thu Sep 28 17:40:38 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "cdb1_c83_iad"
Connected as SYSDBA.
DGMGRL> EDIT CONFIGURATION PREPARE DGPDB;
Enter password for DGPDB_INT account at cdb1_c83_iad:
Enter password for DGPDB_INT account at cdb2_cdx_iad:

Prepared Data Guard for Pluggable Database at cdb2_cdx_iad.

Prepared Data Guard for Pluggable Database at cdb1_c83_iad.
DGMGRL>

Step # 6 - Restart the listeners and databases with the wallet location

On both the source and destination, I performed an "su - grid" and restarted the listeners.

I also restarted both databases. Until I restarted the databases I kept getting a

"Error: ORA-12578: A wallet file was not found or failed to open."

error message.

Step # 7 - Add the pluggable database to the destination

The next step is to add the pluggable database to the destination in dgmgrl.

DGMGRL> add pluggable database pdb1_stby at cdb2_cdx_iad source is pdb1 at cdb1_c83_iad  pdbfilenameconvert is "'+DATA/CDB1_C83_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/','+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/'"  ;
ORA-46697: Keystore password required.
Help: https://docs.oracle.com/error-help/db/ora-46697/

At this point by following the documentation, adding the pluggable database failed with "ORA-46697: Keystore password required."

After some digging I found that I need to pass the wallet password (which is my sys password), and that the phrase needs to be in single quotes.

DGMGRL> add pluggable database pdb1_stby at cdb2_cdx_iad source is pdb1 at cdb1_c83_iad  pdbfilenameconvert is "'+DATA/CDB1_C83_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/','+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/'"  'keystore  IDENTIFIED BY "W3lCom3#123#123"';

Pluggable Database "PDB1_STBY" added

Step # 8 - List datafiles in the source PDB

     FILE#  NAME
----------  ------------------------------------------------------------------------------------------------------------------------
         8  +DATA/CDB1_C83_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/system.273.1148746431
         9  +DATA/CDB1_C83_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/sysaux.270.1148746437
        10  +DATA/CDB1_C83_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/undotbs1.271.1148746445
        12  +DATA/CDB1_C83_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/users.274.1148746543

Step # 9 - Copy the datafiles to the destination

First I logged onto the primary database and closed it.

alter pluggable database pdb1 close;

I decided to use DBMS_FILE_TRANSFER.GET_FILE

Step 9a - create directory on source pointing to datafiles


SQL*Plus: Release 23.0.0.0.0 - Production on Fri Sep 29 11:33:11 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> create directory SOURCE_DUMP as '+DATA/CDB1_C83_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/';

Directory created.

SQL> grant read,write on directory SOURCE_DUMP to public;

Grant succeeded.

SQL>

Step 9b - Log onto ASM and precreate directory

ASMCMD> mkdir 
+data/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/datafile

Step 9c - create directory on target pointing to datafiles

[oracle@cdb1 admin]$ sqlplus /@cdb2_cdx_iad as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri Sep 29 11:33:44 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> create directory TARGET_DUMP as '+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/';

grant read,write on directory TARGET_DUMP to public;



Directory created.

SQL> SQL>
Grant succeeded.

Step 9d - create database link on target pointing to source database

SQL> SQL> SQL> create public database link SOURCEDB connect to system identified by {password} using 'CDB1_C83_IAD';

Database link created.


SQL> select sysdate from dual@SOURCEDB;
select sysdate from dual@SOURCEDB
                         *
ERROR at line 1:
ORA-02085: database link SOURCEDB.ZFSADMIN.VCN.ORACLEVCN.COM connects to
CDB1.ZFSADMIN.VCN.ORACLEVCN.COM
Help: https://docs.oracle.com/error-help/db/ora-02085/


SQL> show parameter global

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_global_dblinks                 boolean     FALSE
global_names                         boolean     TRUE
global_txn_processes                 integer     1
SQL> alter system set global_names=false;

System altered.

SQL> select sysdate from dual@SOURCEDB;

SYSDATE
---------
29-SEP-23

SQL>

Step 9e - Get the datafiles from the source

SQL> set timing on
BEGIN
dbms_file_transfer.get_file('SOURCE_DUMP',
'system.273.1148746431',
'SOURCEDB',
'TARGET_DUMP',
'SYSTEM.DBF');
END;
/SQL>   2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.21

Step # 10 - Rename datafiles in the destination.

List the placeholder datafiles in destination database

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB2_PDB1                      READ WRITE NO
         4 PDB1_STBY                      MOUNTED
SQL> alter session set container=pdb1_stby;

Session altered.

Elapsed: 00:00:00.02
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295
+DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295
+DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295
+DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295

Elapsed: 00:00:00.02
SQL>

Match the file number in the destination files to the source datafiles and rename them.

SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295' to '+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/SYSTEM.dbf';

Database altered.

Elapsed: 00:00:00.04
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295' to '+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/SYSAUX.dbf';

Database altered.

Elapsed: 00:00:00.02

SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295' to '+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/UNDOTBS1.dbf';

Database altered.

Elapsed: 00:00:00.05
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295' to '+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/USERS.dbf';

Database altered.

Elapsed: 00:00:00.03
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/system.dbf
+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/sysaux.dbf
+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/undotbs1.dbf
+DATA/CDB2_CDX_IAD/066E9679DE2850DCE063A104000ABBA7/DATAFILE/users.dbf

Elapsed: 00:00:00.02
SQL>

Step # 11 - Bring over the TDE keys

Export the encryption keys from the source database

SQL> ADMINISTER KEY MANAGEMENT
  EXPORT ENCRYPTION KEYS WITH SECRET "export_secret"
  TO '/tmp/mykey'
  FORCE KEYSTORE
  IDENTIFIED BY "{password}";  2    3    4    5

keystore altered.

Import them into the target database

ADMINISTER KEY MANAGEMENT
  IMPORT KEYS WITH SECRET "export_secret"
  FROM '/tmp/mykeypdb1'
  force keystore
  IDENTIFIED BY "W3lCom3#123#123"
  WITH BACKUP;

SQL> SQL>   2    3    4    5    6
keystore altered.

SQL> SQL>

List the encryption keys on the source database

PDB Name        Key ID                              Master Key ID
--------------- ----------------------------------- -------------------------
CDB$ROOT        35B424EFDB6D4F19BFF31B5D3DE0BFFA    ATW0JO/bbU8Zv/MbXT3gv/o=
PDB$SEED        00000000000000000000000000000000    AQAAAAAAAAAAAAAAAAAAAAA=
PDB1            1AAD19EB6F364F3BBF6FE226A25AF1E5    ARqtGetvNk87v2/iJqJa8eU=

List the encryption keys on the destination database

Master Key ID                                           Tag                  PDB Name        KEYSTORE_TYPE     Origin     Key Creation Time  Key Act. Time
------------------------------------------------------- -------------------- --------------- ----------------- ---------- ------------------ ------------------
AZ5CKuJLrU/Nv5M5pXQCFcEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                         CDB$ROOT        SOFTWARE KEYSTORE LOCAL      09/28/2023 09:09   09/28/2023 09:09
ATW0JO/bbU8Zv/MbXT3gv/oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                         CDB$ROOT        SOFTWARE KEYSTORE LOCAL      09/28/2023 09:10   09/28/2023 09:10
AVAymCcev0/Pv94Tiqw/P50AAAAAAAAAAAAAAAAAAAAAAAAAAAAA                         CDB2_PDB1       SOFTWARE KEYSTORE LOCAL      09/28/2023 09:12   09/28/2023 09:12
ARqtGetvNk87v2/iJqJa8eUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                         CDB2_PDB1       SOFTWARE KEYSTORE LOCAL      09/28/2023 09:13   09/28/2023 09:13

Step # 12 - Add the standby redo logs to the PDB and validate

I created standby redo logs in the PDB on Target database

select 'ALTER DATABASE ADD STANDBY LOGFILE thread ' ||a.thread# || ' group 1' ||a.group# || ' (''' ||substr(b.member,1,35) || 'standby_pdb1_1' || a.group# ||  ''') size ' || a.bytes ||';'
  from v$log a, v$logfile b
  where a.group#=b.group# ;SQL>   2    3

'ALTERDATABASEADDSTANDBYLOGFILETHREAD'||A.THREAD#||'GROUP1'||A.GROUP#||'('''||SUBSTR(B.MEMBER,1,35)||'STANDBY_PDB1_1'||A.GROUP#||'
----------------------------------------------------------------------------------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 13 ('+RECO/CDB2_CDX_IAD/ONLINELOG/group_standby_pdb1_13') size 1073741824;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 12 ('+RECO/CDB2_CDX_IAD/ONLINELOG/group_standby_pdb1_12') size 1073741824;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 11 ('+RECO/CDB2_CDX_IAD/ONLINELOG/group_standby_pdb1_11') size 1073741824;

SQL> alter session set container=PDB1_STBY;

Session altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 4 ('+RECO/CDB2_CDX_IAD/ONLINELOG/group_standby_pdb1_13') size 1073741824;

Database altered.


SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 ('+RECO/CDB2_CDX_IAD/ONLINELOG/group_standby_pdb1_12') size 1073741824;

Database altered.

SQL>  ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6  ('+RECO/CDB2_CDX_IAD/ONLINELOG/group_standby_pdb1_11') size 1073741824;

Database altered.

SQL>

And then I validated the configuration

DGMGRL> VALIDATE PLUGGABLE DATABASE PDB1_STBY AT cdb2_cdx_iad;

  Ready for Switchover:    NO

  Data Guard Role:         Physical Standby
  Apply State:             Not Running
  Standby Redo Log Files:  3
  Source:                  PDB1 (con_id 3) at cdb1_c83_iad

Step # 13 - change the state of standby to APPLY-ON

I changed the state of the standby and checked the configuration.

DGMGRL> VALIDATE PLUGGABLE DATABASE PDB1_STBY AT cdb2_cdx_iad;

  Ready for Switchover:    NO

  Data Guard Role:         Physical Standby
  Apply State:             Not Running
  Standby Redo Log Files:  3
  Source:                  PDB1 (con_id 3) at cdb1_c83_iad


DGMGRL>
EDIT PLUGGABLE DATABASE PDB1_STBY AT cdb2_cdx_iad SET STATE='APPLY-ON';DGMGRL>
Succeeded.
DGMGRL> SHOW CONFIGURATION;

Configuration - cdb2_cdx_iad

  Protection Mode: MaxPerformance
  Members:
  cdb2_cdx_iad - Primary database
    Warning: ORA-16910: inconsistency detected for one or more pluggable databases

  cdb1_c83_iad - Primary database in cdb1_c83_iad configuration

Data Guard for PDB:   Enabled in TARGET role

Configuration Status:
SUCCESS   (status updated 37 seconds ago)

DGMGRL> SHOW PLUGGABLE DATABASE pdb1 AT cdb1_c83_iad;

Pluggable database - PDB1 at cdb1_c83_iad

  Data Guard Role:     Primary
  Con_ID:              3
  Active Target:       con_id 4 at cdb2_cdx_iad

Pluggable Database Status:
SUCCESS

DGMGRL> SHOW PLUGGABLE DATABASE pdb1_stby AT cdb2_cdx_iad ;

Pluggable database - PDB1_STBY at cdb2_cdx_iad

  Data Guard Role:     Physical Standby
  Con_ID:              4
  Source:              con_id 3 at cdb1_c83_iad
  Transport Lag:       20 hours 26 minutes 24 seconds (computed 9 seconds ago)
  Apply Lag:           (unknown)
  Intended State:      APPLY-ON
  Apply State:         Running
  Apply Instance:      cdb2
  Average Apply Rate:  (unknown)
  Real Time Query:     OFF

Pluggable Database Status:
SUCCESS

DGMGRL> VALIDATE PLUGGABLE DATABASE PDB1_STBY AT cdb2_cdx_iad;

  Ready for Switchover:    NO

  Data Guard Role:         Physical Standby
  Apply State:             Waiting for Redo Data
  Standby Redo Log Files:  3
  Source:                  PDB1 (con_id 3) at cdb1_c83_iad

DGMGRL>

Step # 14 - Perform a log switch at both databases

 sqlplus /@cdb1_c83_iad as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri Sep 29 15:57:52 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> exit
Disconnected from Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09
[oracle@cdb2 admin]$ sqlplus /@cdb2_cdx_iad as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Fri Sep 29 15:58:11 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL>

Step # 15 - Validate standby is applying redo.

DGMGRL> SHOW PLUGGABLE DATABASE pdb1_stby AT cdb2_cdx_iad ;

Pluggable database - PDB1_STBY at cdb2_cdx_iad

  Data Guard Role:     Physical Standby
  Con_ID:              4
  Source:              con_id 3 at cdb1_c83_iad
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           4 seconds (computed 1 second ago)
  Intended State:      APPLY-ON
  Apply State:         Running
  Apply Instance:      cdb2
  Average Apply Rate:  489 KByte/s
  Real Time Query:     OFF

Pluggable Database Status:
SUCCESS

SUMMARY :

This is an exciting new feature, and I am looking forward to see it expand.

Since a target database, can only have one source, I see this being useful to "pair" CDBs.

Instead of having 2 Primary CDBs in DC1 (for example) and 2 standby CDBs in DC2 you would have 2 Primary CDBs in in each Data Center, and you would be able to switch over any PDBs individually.

Since this is a paradigm change for RMAN, I am going to be following how this affects RMAN, the RMAN catalogs, and the ZDLRA/Recovery Service in it's support.

Finally :

It is all working, and for fun I am going to look at the destination settings on the source Database.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb1_c83_iad
log_archive_dest_2                   string      service="cdb2_cdx_iad", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="cdb2_cdx_iad" net_timeout=30, valid_for=(online_logs)

Today I did have time to perform a switchover and a second switchover to bring it back.

Because my database is encrypted, I did import the keys from CDB2 into CDB1. I am thinking when you implement this with TDE, you do need to keep the keys from both CDBs syncronized.

In OKV, I'm thinking you would want to possibly even use a single wallet. At the very least both primary databases would need access to both wallets.

OBSERVATIONS:

I will continue to update this post as I find useful observations.

Observation 1 :

I looked at the archive log using v$archived_logs view in the target (CDB2).

In it I only 6 archive logs were created.

SQL> select thread#,sequence#,dest_id,blocks,status from v$ARCHIVED_LOG;

   THREAD#  SEQUENCE#    DEST_ID     BLOCKS S
---------- ---------- ---------- ---------- -
         1          2          1     230208 A
         1          3          1     896391 A
         1          4          1          7 A
         1          5          1      35474 A
         1          6          1        387 A

I added a couple more PDBS and made changes to a second PDB (that didn't have a standby) and the original PDB (that had a standby).

I then created a workload in both PDBS and looked at the archive logs on the source.

   THREAD#  SEQUENCE#    DEST_ID     BLOCKS S
---------- ---------- ---------- ---------- -
         1          3          2       4894 A
         1          4          2     567681 A
         1          5          2         14 A
         1          6          2     871136 A
         1          7          2    1756544 A
         1          8          2    1847012 A
         1          9          2    1931501 A
         1         10          2    1901263 A
         1         11          2    1931491 A
         1         12          2    1931837 A
         1         13          2    1465002 A
         1         14          2    1955017 A
         1         15          2    1936946 A
         1         16          2    1685507 A
         1         17          2    1931499 A
         1         18          2    1808038 A
         1         19          2    1874455 A
         1         20          2    1910501 A
         1         21          2    2038138 A
         1         22          2    1812968 A
         1         23          2    1935941 A
         1         24          2    1939459 A
         1         25          2    2030413 A
         1         26          2    1934927 A

24 rows selected.

Then I looked at ASM on the target and I could ALL the archive logs on the target.

ASMCMD> ls
thread_1_seq_10.282.1148837541
thread_1_seq_11.283.1148837541
thread_1_seq_12.284.1148837557
thread_1_seq_13.285.1148837667
thread_1_seq_14.287.1148837943
thread_1_seq_15.288.1148837963
thread_1_seq_16.289.1148837979
thread_1_seq_17.290.1148837979
thread_1_seq_18.291.1148838023
thread_1_seq_19.292.1148838023
thread_1_seq_20.293.1148838101
thread_1_seq_21.294.1148838119
thread_1_seq_22.295.1148838137
thread_1_seq_23.296.1148838137
thread_1_seq_24.297.1148838183
thread_1_seq_25.298.1148838213
thread_1_seq_26.299.1148838237
thread_1_seq_3.275.1148831895
thread_1_seq_4.276.1148831899
thread_1_seq_5.274.1148831883
thread_1_seq_5.278.1148837115
thread_1_seq_6.277.1148836711
thread_1_seq_6.286.1148837681
thread_1_seq_7.279.1148837481
thread_1_seq_8.280.1148837499
thread_1_seq_9.281.1148837517

Observation 2 :

RMAN does not support this feature at this point.

What I have discovered is that

Within the CDB, V$ARCHIVED_LOG does not recognize archive logs from the second primary needed to recovery the standby
Within the CDB, V$DATAFILE does not recognize the datafiles for the “standby database”
Within the standby PDB, V$DATAFILE recognizes the datafiles for that standby database, and only the standby datafiles
Within the standby PDB V$ARCHIVED_LOG only sees this archive logs for the standby database, and not the local archive logs produced
When executing RMAN for the CDB, only the locally produced archive logs are backed up
When executing RMAN for the CDB, only the primary datafiles are backed up.
When executing RMAN for the standby PDB, no datafiles are backed up. “pluggable database PDB1_STBY does not have any data files”
When executing RMAN for the standby PDB, only the primary database’s locally produced archive logs are backed up.

SUMMARY – with DGPDB

Within the PDB, the PDB can see the datafiles, and archive logs needed to recover with V$DATAFILE and V$ARCHIVED_LOG

RMAN does not currently see the standby datafiles, or it’s archive logs to back them up.