A third recovery site: hybrid Data Guard and Autonomous Recovery Service

Until recently, most organizations I work with have been comfortable running their databases and applications across two regions. Sometimes that is an Active/Standby configuration, but more often I see some form of Active/Active. The big change I have seen recently is the addition of a third site, often described as a "cyber vault" or an IRE (Isolated Recovery Environment).

To provide that third site, many customers are looking at hosting a standby database in the cloud, either in OCI or through Oracle Database@ services in a multicloud environment. A practical way to limit traffic from the on-premises environment is to only send redo to the cloud database with Data Guard, then back up that cloud database to Autonomous Recovery Service. The backup workload stays on the cloud side.

Creating a cloud standby from your on-premises database is easier than you might think. In this post, I go through the standby build and the next step: configuring backups to Autonomous Recovery Service.

One thing to keep in mind is that a standby database and backups are only part of the picture. To use this as a cyber vault or IRE, you also need to control who can access it and which systems can connect to it. You need to protect the encryption keys and test that you can restore the database to a point before an attack or data corruption occurred. Plan for all of this as you build the standby and configure the backups..

The third-site pattern: send redo to a cloud standby, then back up the standby to Autonomous Recovery Service. The worked example below uses Oracle Exadata Database Service in OCI.

I recently went through the Oracle documentation for creating a hybrid Data Guard configuration. There is a lot of information to work through, especially when you are trying to figure out what needs to be ready before you start.

I am breaking this into four parts:

1. Get the prerequisites in place.
2. Run dbcactl to prepare the primary database.
3. Run dbaascli to create the standby database in OCI.
4. Configure backups from the cloud standby to Autonomous Recovery Service.

Once the preparation is done, the standby build comes down to two commands. I will go through their parameters and what dbcactl and  dbaascli are doing, then show where Recovery Service fits into the third-site design.

For this walkthrough, I am showing an on-premises Oracle RAC primary with a standby on Oracle Exadata Database Service in OCI. I have changed the database names and hostnames to use a generic sales example. My example build used a 19c Oracle Home and DBAAS CLI 26.3.1.0.0.

NOTE: The cloud VM cluster and Oracle Home need to exist before you start. configureStandby creates the database within that cluster. Refer to the full Oracle guide for platform requirements and configurations with additional standbys.

1) What needs to be ready before I start

First, let's go through the pieces that need to be in place before running either command.

• Download dbcactl. My Oracle Support document 3099785.1, linked from the hybrid guide, has the download and installation instructions. I am using /home/oracle/dbcacl/dbcactl in the example. The folder is named dbcacl, and the executable is dbcactl.
• Have the cloud Oracle Home ready. Match the primary release, RU, and one-off patches where possible. If you need a different RU, check the combinations allowed in the guide.
• Check connectivity in both directions. The primary needs to reach the cloud, and the cloud needs to reach the primary. Check DNS, SSH, and Oracle Net for all RAC nodes and SCAN endpoints. The network also needs enough bandwidth for the initial copy and ongoing redo.
• Existing standby database. If you already have an existing standby database you need to ensure that ALL databases can reach each other with DG broker. The existing standby needs to be able to resolve the scan listener on the new standby and connect. The new standby needs to be able to resolve the scan listener for the existing standby and connect.
• Have a shared ACFS directory on the primary if possible. This is where the network files and wallet configuration can be shared between instances. For this example I am using /acfs01/sales on an existing mounted ACFS filesystem.  If you do not have a shared ACFS directory you need to validate the connectivity on all DB nodes after executing dbcactl.
• Prepare the primary for Data Guard. This includes standby redo logs, logging, Flashback Database, Oracle Net encryption, and enough recovery-area space. Follow the MAA recommendations in the guide.

Make sure the encryption keys are there

One thing I want to call out is the TDE wallet. Even if the on-premises primary is not encrypted, it still needs a wallet with master encryption keys for this hybrid configuration. The cloud database needs those keys.

For this example I am using a file-based TDE keystore. Before starting, I would check that the wallet is usable and that the keys exist in the CDB root and each PDB other than the seed. The TDE with Data Guard documentation goes through the key handling.

For 19c at RU 19.16 or later, these are the settings I would look at:

Parameter	Example or policy	Purpose
WALLET_ROOT	/acfs01/sales/wallet	Root directory for wallet storage; the file-based TDE wallet is under tde. Use your existing keystore location.
TDE_CONFIGURATION	KEYSTORE_CONFIGURATION=FILE	Selects the file-based keystore used in this example.
TABLESPACE_ENCRYPTION	Choose for each site	AUTO_ENABLE is the cloud policy. DECRYPT_ONLY  is an on-premises option when encrypted tablespaces must be avoided. Do not apply it to the cloud standby.

NOTE: Setting the parameters does not create the wallet or set the master keys. Also, WALLET_ROOT is static, so changing it requires a restart.

If you still need to set up the wallet, follow the TDE configuration procedure. The TABLESPACE_ENCRYPTION reference explains the encryption policies. For earlier RUs, use the release-specific settings in the hybrid guide.

Here is a quick check I can run on the primary, connected through SQL*Plus as oracle:

SELECT name, db_unique_name, database_role, log_mode,
       force_logging, flashback_on
FROM v$database;

SHOW PARAMETER wallet_root
SHOW PARAMETER tde_configuration
SHOW PARAMETER tablespace_encryption

SELECT con_id, status, wallet_type, wrl_parameter
FROM v$encryption_wallet;

I want the primary in ARCHIVELOG mode, with force logging enabled and Flashback Database prepared before moving on. If archive logging still needs to be enabled, plan for the maintenance window that change may require.

I would also check V$DATABASE_KEY_INFO and V$ENCRYPTION_KEYS in the root and relevant PDBs. An open wallet is only part of the check; I need the master keys as well. If the wallet shows OPEN_NO_MASTER_KEY, that needs to be addressed before the build.

Use your existing key-management procedure and wallet location. There is no reason to replace an existing wallet just to match the path in my example.

Finally, have the primary SYS password, the TDE wallet password, and the password you will supply for AWR administration ready. These have different purposes and do not need to be the same.

Get Recovery Service ready as well

For the backup step, I also need the Recovery Service IAM policies, network connectivity, and a registered Recovery Service subnet. For 19c, the documented minimum is RU 19.18. Broker is mandatory for this backup destination in a Data Guard configuration with dbaascli 25.3.1.0.0 and later. I would also check regional availability, service limits, and the retention policy I need. The Exadata backup guide covers these prerequisites.

The names I am using in the example

Item	Generic value
Database name on both sites	sales
Primary database unique name	sales_primary
Standby database unique name	sales_stby
Primary SCAN	onprem-scan.example.com
Standby SCAN	cloud-scan.oraclevcn.com
Primary database service	sales_primary.example.com
SCAN listener port on both sites	1521
Existing cloud Oracle Home	/u02/app/oracle/product/19.0.0.0/dbhome_1

You can see that I am using sales as the database name on both sides. The unique names are different: sales_primary and sales_stby.

The other distinction to keep in mind is the SCAN name versus the database service. The SCAN gets me to the cluster, and the service identifies the database connection. Use the actual service registered with your primary listener when replacing sales_primary.example.com.

2) Run dbcactl on the primary

Figure 1. Run the preparation command on the first primary node. The standby SCAN already belongs to the provisioned cloud cluster.

Now I can prepare the primary. The diagram above shows the database and its SCAN listener, along with the cloud SCAN I will pass to the command.

I run this as oracle on the first primary node, with ORACLE_HOME and the local ORACLE_SID set for the source database. The shared directory /acfs01/sales also needs to be writable by oracle.

Here is the command with the example names:

/home/oracle/dbcacl/dbcactl \
  -silent \
  -oui_internal \
  -configureDatabase \
  -prepareForStandby \
  -dgTNSNamesoraFilePath /acfs01/sales \
  -sourceDB sales_primary \
  -gdbName sales.example.com \
  -standbyDBUniqueName sales_stby \
  -standbyScanName cloud-scan.oraclevcn.com \
  -standbyScanPort 1521 \
  -primaryScanPort 1521 \
  -standbyDBDomain oraclevcn.com \
  -blobFileLocation /tmp

The command prompts for the primary SYS password. In my run, I could see it creating the Data Guard services, updating tnsnames.ora and the include-file entry, and then preparing the standby bundle.

If you do not have a shared location for the the new tnsnames.ora file, you need to make sure ALL DB nodes have access to the entries in the new tnsnames.ora. This may involve manually adding the entries to your database.

What the dbcactl parameters mean

Parameter	Value in this example	Meaning
-silent	Flag	Runs without the graphical assistant; password prompts still appear.
-oui_internal	Flag	Workflow flag that allows for the use of prepareForStandby  (which is a hidden parameter)
-configureDatabase	Flag	Selects database configuration.
-prepareForStandby	Flag	Selects primary preparation for a standby.
-dgTNSNamesoraFilePath	/acfs01/sales	Directory for the Data Guard network configuration.  An "ifile" entry is created in the default tnsnames.ora file that points to a tnsnames.ora that is created in this directory which contains entries for both the primary database and the new standby database.
-sourceDB	sales_primary	Identifies the primary by its unique name.
-gdbName	sales.example.com	Global database name passed to the workflow.
-standbyDBUniqueName	sales_stby	Unique name planned for the standby.
-standbyScanName	cloud-scan.oraclevcn.com	SCAN hostname for the existing standby cluster.
-standbyScanPort	1521	Standby SCAN listener port.
-primaryScanPort	1521	Primary SCAN listener port.
-standbyDBDomain	oraclevcn.com	Standby database in OCI domain passed to the workflow.
-blobFileLocation	/tmp	Output directory for the generated bundle.

These are the options from my successful command, with the environment names changed. If you are using a different utility version, check its supported options, especially -gdbName and -pdbServiceDomain.

At the end, the command tells me where it wrote the .tar file. The filename will look like this, with a generated timestamp and ID:

Successfully created blob file:
/tmp/sales_<generated_timestamp>_<generated_id>.tar

This file contains the preparation material, including network files and the TDE wallet. I need to copy it securely to the first cloud node before running the second command.

For example, I can use scp:

scp /tmp/sales_<generated_timestamp>_<generated_id>.tar opc@cloud-node1.example.com:/tmp/

Replace the timestamp and ID with the actual filename from your output. For the next command, I am using /tmp/sales_standby.tar as a simpler name for the transferred file. You can keep the generated name instead; just use the same path in --standbyBlobFromPrimary.

NOTE: The bundle contains wallet material. Keep access to it restricted during the transfer and on the destination.

Oracle 19c: Cloning Encrypted PDBs and Moving TDE Keys

Oracle TDE key hierarchy. This walkthrough uses file-based wallets in Oracle Database 19c.

When cloning or refreshing an encrypted pluggable database (PDB) in Oracle Database 19c, it is easy to focus on the datafiles. Restore the backup, recover the database, and plug the PDB into the destination container database (CDB).

But don't forget the keys. With Transparent Data Encryption (TDE), the destination also needs access to the master encryption keys stored in the source wallet.

In this post I will walk through two ways to bring the keys with an encrypted PDB, and then show where those steps fit when the PDB is restored from backup. I am using Oracle Database 19c, local file-based TDE wallets, and united mode. In united mode the PDBs use the CDB's wallet, while keeping their own master encryption keys.

This builds on my posts about encrypting an Oracle PDB with TDE and Oracle wallet files for TDE and authentication.

NOTE: These are command examples to adapt and validate on your 19c Release Update. Database names, paths, passwords, and the recovery SCN are placeholders. This is a walkthrough of the key handling, not output from a completed lab.

For unplug/plug, I can bring the keys with ENCRYPT USING and DECRYPT USING, or export and import them separately. A refresh from RMAN backups also needs the historical keys during recovery into the auxiliary CDB.

In this post: Check the wallet · Unplug/plug with keys · Export and import keys · Refresh from RMAN backups · Rekey and verify · Merge wallets

TDE wallet keys: what needs to move with a PDB?

TDE has two levels of keys. The tablespace key encrypts the data, and the TDE master encryption key protects that key. The master key is stored in the keystore. Copying encrypted datafiles does not, by itself, give a different CDB access to that master key. Oracle's TDE overview

Figure 1. Move both the encrypted PDB datafiles and the required master keys into the destination environment.

For these examples I am using the following names.

Name	Purpose
PRODCDB / APPPDB	Source CDB and encrypted PDB
AUXCDB / APPPDB	Temporary CDB used for a restore from backup
TESTCDB / APP_REFRESH	Existing destination CDB and the new refresh PDB
SourceWalletPassword	Password for the source wallet
TargetWalletPassword	Password for the destination wallet
TransportSecret	Separate secret protecting keys during transport

The transport secret and the wallet passwords have different jobs. I do not need to make the source and destination wallet passwords match.

STEP #1 - Check the TDE wallet configuration

On each CDB, I first check the configuration and wallet status.

-- SQL*Plus, connected to the appropriate CDB root
SHOW CON_NAME
SHOW PARAMETER wallet_root
SHOW PARAMETER tde_configuration

SELECT con_id, status, wallet_type, keystore_mode,
       wrl_parameter
FROM   v$encryption_wallet
ORDER BY con_id;

The examples assume TDE_CONFIGURATION='KEYSTORE_CONFIGURATION=FILE', with the united wallet under WALLET_ROOT/tde. A PDB row should show UNITED; the root can show NONE. An OPEN wallet is a useful first check, but it does not prove that it contains every key needed for this refresh. Wallet status reference

For a password wallet that is closed, open it from the root. Use the password belonging to that CDB.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
  IDENTIFIED BY "SourceWalletPassword"
  CONTAINER = ALL;

If auto-login already has the wallet open, check its state rather than blindly repeating the open. The key-management examples below use FORCE KEYSTORE where supported to allow access to the password wallet. Have the ewallet.p12 password available even when normal database startup uses cwallet.sso. Configuring TDE

I also record the source PDB's keys before the move.

ALTER SESSION SET CONTAINER = APPPDB;

SELECT key_id, creation_time, activation_time
FROM   v$encryption_keys
ORDER BY activation_time;

This gives me key identifiers to compare later. I am recording metadata here, not displaying the secret key material. Encryption key view

Run the key operations as an appropriately privileged key administrator (SYSKM or ADMINISTER KEY MANAGEMENT), and the PDB operations with the necessary database privileges. The examples assume both are available to the administrative session.

METHOD #1 - Unplug and plug an encrypted PDB with its TDE keys

For a planned unplug/plug, I can protect the PDB's keys with a transport secret as part of the unplug operation.

1) Unplug on the source

-- PRODCDB root; source wallet must be open
ALTER SESSION SET CONTAINER = CDB$ROOT;

ALTER PLUGGABLE DATABASE APPPDB CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE APPPDB
  UNPLUG INTO '/secure_stage/apppdb.xml'
  ENCRYPT USING "TransportSecret";

ENCRYPT USING protects the transported keys. The XML file is a manifest; the PDB's datafiles still have to be made available on the destination host. A .pdb archive is an alternative packaging format. Unplugging PDBs

NOTE: Unplugging takes this source PDB out of service. For a refresh that must leave production running, use the backup/auxiliary workflow below and unplug the restored copy.

2) Check compatibility at the destination

For this example, the XML and datafiles are accessible at the paths recorded in the XML. If the files were staged at different paths, account for that with SOURCE_FILE_NAME_CONVERT; FILE_NAME_CONVERT controls the destination copies.

-- TESTCDB root
SET SERVEROUTPUT ON
DECLARE
  can_plug BOOLEAN;
BEGIN
  can_plug := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
    pdb_descr_file => '/secure_stage/apppdb.xml',
    pdb_name       => 'APP_REFRESH');
  IF can_plug THEN
    DBMS_OUTPUT.PUT_LINE('Compatible');
  ELSE
    RAISE_APPLICATION_ERROR(-20001,
      'Review PDB_PLUG_IN_VIOLATIONS before continuing');
  END IF;
END;
/

Resolve compatibility errors before creating the PDB, including applicable patch, component, and character-set issues. Plug compatibility check

3) Plug in the copy

The destination wallet must already exist and be open. APP_REFRESH must be a new PDB name in TESTCDB.

-- TESTCDB root; filesystem paths used for this example
CREATE PLUGGABLE DATABASE APP_REFRESH AS CLONE
  USING '/secure_stage/apppdb.xml'
  COPY
  FILE_NAME_CONVERT =
    ('/u02/oradata/PRODCDB/APPPDB/',
     '/u02/oradata/TESTCDB/APP_REFRESH/')
  KEYSTORE IDENTIFIED BY "TargetWalletPassword"
  DECRYPT USING "TransportSecret";

ALTER PLUGGABLE DATABASE APP_REFRESH OPEN;

Here, AS CLONE gives the plugged copy a new identity, and COPY creates its datafiles at the destination. DECRYPT USING supplies the transport secret, while KEYSTORE IDENTIFIED BY supplies the destination wallet password. Adapt file placement for OMF or ASM. CREATE PLUGGABLE DATABASE reference

Finish with the rekey and verification steps below. An initial restricted open is not the end of the process.

METHOD #2 - Export and import TDE keys for an Oracle 19c PDB

This is an alternative to Method #1. I use a separate export file when I want the key transfer to be an explicit step in the refresh process.

1) Export from inside the source PDB

-- PRODCDB, or AUXCDB after restoring the PDB
ALTER SESSION SET CONTAINER = APPPDB;

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS
  WITH SECRET "TransportSecret"
  TO '/secure_stage/apppdb_keys.exp'
  FORCE KEYSTORE
  IDENTIFIED BY "SourceWalletPassword";

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE APPPDB CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE APPPDB
  UNPLUG INTO '/secure_stage/apppdb.xml';

Export inside the PDB. Do not add a WITH IDENTIFIER IN filter: a PDB export carries its keys and the metadata identifying the active key. Protect the export file and deliver its secret separately. Exporting keys leaves the source keys in place. Key export syntax

2) Import into the destination root before plugging, when required

If SYSTEM, SYSAUX, UNDO, or TEMP is encrypted, first import into TESTCDB's root. Then import again inside the new PDB to associate the keys with it. For a PDB without those encrypted system tablespaces, the pre-plug root import can be skipped. Oracle's united-mode PDB procedure

-- TESTCDB root: conditional pre-plug import
ALTER SESSION SET CONTAINER = CDB$ROOT;

ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS
  WITH SECRET "TransportSecret"
  FROM '/secure_stage/apppdb_keys.exp'
  FORCE KEYSTORE
  IDENTIFIED BY "TargetWalletPassword"
  WITH BACKUP USING 'before_refresh_root_import';

3) Plug in, open the PDB wallet, and import inside the PDB

Run the compatibility check from Method #1 first. This manifest was created without ENCRYPT USING, so this branch does not use DECRYPT USING.

-- TESTCDB root
CREATE PLUGGABLE DATABASE APP_REFRESH AS CLONE
  USING '/secure_stage/apppdb.xml'
  COPY
  FILE_NAME_CONVERT =
    ('/u02/oradata/PRODCDB/APPPDB/',
     '/u02/oradata/TESTCDB/APP_REFRESH/')
  KEYSTORE IDENTIFIED BY "TargetWalletPassword";

ALTER SESSION SET CONTAINER = APP_REFRESH;

-- If the PDB's password wallet is not already open:
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
  FORCE KEYSTORE
  IDENTIFIED BY "TargetWalletPassword";

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE APP_REFRESH OPEN;

ALTER SESSION SET CONTAINER = APP_REFRESH;

ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS
  WITH SECRET "TransportSecret"
  FROM '/secure_stage/apppdb_keys.exp'
  FORCE KEYSTORE
  IDENTIFIED BY "TargetWalletPassword"
  WITH BACKUP USING 'before_refresh_pdb_import';

The PDB may initially open restricted. Complete the PDB import, rekey, and reopen before releasing it to the application. The root import and PDB import serve different purposes; do not treat the root import as completion of both steps.

Refresh an encrypted PDB from RMAN backups

I separate this into two handoffs: backup to auxiliary CDB, followed by auxiliary PDB to the existing destination CDB.

Figure 2. A backup refresh has two key handoffs: recovery into AUXCDB, then plug-in and key transfer into TESTCDB.

1) Make the historical keys available to the auxiliary

The auxiliary needs the keys required by the backup and recovery interval. Rotating the production master key does not eliminate the need for older keys. A wallet backup that predates a required rotation may be missing a key; a later wallet can contain the historical keys if they have been retained. TDE key history

For a newly prepared auxiliary, securely provision a copy of the source password wallet at its configured WALLET_ROOT/tde location. Use a wallet with the required CDB and PDB key history, not just a PDB key export. The duplicate also restores root and seed files. Keep the auxiliary's wallet separate from the live source and from TESTCDB's wallet. Preparing the auxiliary keystore

For example, the auxiliary initialization settings include:

db_name='AUXCDB'
db_unique_name='AUXCDB'
enable_pluggable_database=TRUE
wallet_root='/u01/app/oracle/admin/AUXCDB/wallet'
tde_configuration='KEYSTORE_CONFIGURATION=FILE'

This is only the relevant parameter excerpt. Prepare the auxiliary in NOMOUNT, with its own controlfile, datafile, redo, and recovery-area destinations, plus the password file and Oracle Net configuration required by your duplication method.

2) Restore the PDB into the temporary CDB

Here is a backup-based duplication example, with RMAN connected to PRODCDB as TARGET and AUXCDB as AUXILIARY. Both connections are to the root. The source backup pieces must be accessible to the auxiliary, including root, seed, PDB, controlfile, and the archived redo needed to reach the chosen SCN.

-- RMAN: connected to the two CDB roots
SET DECRYPTION WALLET OPEN
  IDENTIFIED BY 'SourceWalletPassword';

RUN {
  SET UNTIL SCN 123456789;
  SET NEWNAME FOR DATABASE TO '/u02/oradata/AUXCDB/%U';

  DUPLICATE TARGET DATABASE TO AUXCDB
    PLUGGABLE DATABASE APPPDB
    LOGFILE
      GROUP 1 ('/u02/oradata/AUXCDB/redo01.log') SIZE 200M,
      GROUP 2 ('/u02/oradata/AUXCDB/redo02.log') SIZE 200M,
      GROUP 3 ('/u02/oradata/AUXCDB/redo03.log') SIZE 200M;
}

Replace the SCN and file layout with values for your restore. This creates a new auxiliary CDB containing the selected PDB, not a PDB directly inside TESTCDB. Oracle 19c's DUPLICATE PLUGGABLE DATABASE ... TO existing_cdb form supports active duplication; it is not the backup-based command used here. RMAN DUPLICATE reference

SET DECRYPTION WALLET OPEN supplies the wallet password across auxiliary restarts during duplication. It does not transfer missing keys. If the backup pieces have a separate password-encryption layer, also supply the applicable backup password using SET DECRYPTION IDENTIFIED BY. That password does not replace the keys needed for the TDE-encrypted datafiles. RMAN SET reference

3) Transfer from AUXCDB into TESTCDB

After successful recovery and opening the restored PDB, use either Method #1 or Method #2, with AUXCDB as the source.

Create a fresh manifest from the restored PDB, and export its keys there if using Method #2. Update FILE_NAME_CONVERT to match the restored files' actual paths. In the RMAN example, %U generates filenames directly under /u02/oradata/AUXCDB/; inspect those filenames rather than assuming an APPPDB subdirectory exists.

I would create APP_REFRESH alongside the current test PDB, validate it, and then handle the application/service cutover. The destructive replacement of an existing PDB is a separate decision. This is a repeated refresh from backups, not a REFRESH MODE clone.

Rekey the cloned PDB, reopen, and verify

Once the imported keys are available, create and activate a destination master key inside APP_REFRESH.

-- TESTCDB, inside the new PDB only
ALTER SESSION SET CONTAINER = APP_REFRESH;

ADMINISTER KEY MANAGEMENT SET KEY
  FORCE KEYSTORE
  IDENTIFIED BY "TargetWalletPassword"
  WITH BACKUP USING 'app_refresh_rekey'
  CONTAINER = CURRENT;

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE APP_REFRESH CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE APP_REFRESH OPEN;

Use CONTAINER=CURRENT for this PDB; there is no reason for a refresh to rotate every PDB's key. Changing the wallet password is also a different operation from generating a new master key. Key-management operations

Rekeying gives the destination a new master key. It does not make historical backups independent of their original keys. Retain those keys for as long as the corresponding recovery requirements exist. TDE key architecture FAQ

Now I check the result.

-- TESTCDB root
SELECT name, open_mode, restricted
FROM   v$pdbs
WHERE  name = 'APP_REFRESH';

SELECT time, name, cause, type, message, status, action
FROM   pdb_plug_in_violations
WHERE  name = 'APP_REFRESH'
AND    status <> 'RESOLVED'
ORDER BY time;

ALTER SESSION SET CONTAINER = APP_REFRESH;

SELECT status, keystore_mode, fully_backed_up
FROM   v$encryption_wallet;

SELECT key_id, creation_time, activation_time
FROM   v$encryption_keys
ORDER BY activation_time;

SELECT tablespace_name, encrypted
FROM   dba_tablespaces
ORDER BY tablespace_name;

I want READ WRITE, RESTRICTED=NO, and all blocking plug-in violations resolved. I also read known application data from an encrypted tablespace and check the expected refresh timestamp or business totals. Opening the PDB alone is not my application validation. Completing a PDB plug-in

Finally, back up the updated destination wallet and the refreshed PDB. WITH BACKUP protects the wallet before a change; I also want a retained copy containing the newly created key.

-- TESTCDB root, after successful rekey
ALTER SESSION SET CONTAINER = CDB$ROOT;

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE
  USING 'after_app_refresh'
  FORCE KEYSTORE
  IDENTIFIED BY "TargetWalletPassword"
  TO '/secure_wallet_backups/TESTCDB';

Keep password-wallet backups and their passwords recoverable, with access controlled separately from the database backups. An auto-login wallet stored beside encrypted backups weakens that separation. RMAN encryption and wallet backup guidance

Migrate TDE wallet keys with MERGE KEYSTORE

For one PDB, I would normally use the PDB export/import above. A wallet merge has broader scope: it adds the source wallet's keys to another wallet.

Here is a merge example using staged wallet copies, outside the live wallet directories:

-- Optional wallet administration example; not a PDB refresh step
ADMINISTER KEY MANAGEMENT
  MERGE KEYSTORE '/secure_stage/source_wallet'
    IDENTIFIED BY "SourceWalletPassword"
  INTO EXISTING KEYSTORE '/secure_stage/target_wallet_copy'
    IDENTIFIED BY "TargetWalletPassword"
  WITH BACKUP USING 'before_wallet_merge';

This updates the staged destination password wallet and leaves the source unchanged. It does not switch the live database to that staged wallet. A planned wallet migration must also configure the final location, reopen the merged wallet, and update or recreate its auto-login companion as appropriate. Coordinate that change across all consumers of the wallet. Merging and relocating wallets

NOTE: Do not copy the source ewallet.p12 over the wallet of an existing destination CDB. That destination may need keys for other PDBs. Also, MOVE KEYS removes selected keys from the source keystore and is not a substitute for a clone's export/import. MIGRATE USING is for changing the keystore provider, such as migrating to Oracle Key Vault. Key-management SQL reference

The operational check I would add to every refresh job is simple: record which backup was restored, which keys were supplied, where the destination wallet was backed up, and whether encrypted application data was successfully read. Those four items make the next refresh, and the next recovery exercise, much easier to review.

Building a Cyber Vault ? Don't forget your keys

When building a cyber vault, one of the most important items to manage is encryption keys.  Encrypting your data is a fundamental pillar of ransomware protection, but encryption key management is often forgotten.  

Ensuring your Cyber Vault has a current copy of your encryption keys associated with your Oracle Databases is critically important for a successful restore and recover after an attack.

Starting with the Oracle Key Vault (OKV) 21.11 release, Oracle Key Vault includes a preview of the Key Transfer Across Oracle Key Vault Clusters Integration Accelerator. You can use this feature to transfer security objects from one OKV cluster to another.

You can find more detail on this feature here, and I will describe it's benefits in this post.

The diagram below shows a typical cyber vault architecture to protect Oracle Databases with the Zero Data Loss Recovery Appliance (ZDLRA) and OKV .

Transferring keys into a cyber vault with OKV

Encryption Key Architecture

Encryption key management is a critical piece of data protection, and it is important to properly manage your keys.  Good cyber protection begins with proper encryption key management.

Local Wallets

With Oracle databases, the default (and simplest) location for encryption keys is in a locally management wallet file.  The keys are often stored in an auto-login wallet, which is automatically opened by the database at startup making key management transparent and simple, but not very secure.

Why aren't wallets secure ?

• They are often auto-login (cwallet.sso) which allows the database to open the wallet without requiring a password. This wallet file gives full access to the encryption keys.
• The wallet file is stored with the database files.  A privileged administrator, such as a DBA has access to both the database and the keys to decrypt the data directly from the hosts.  They also have the ability to delete the wallet file.
• Often the wallet file is backed up with the database, which includes an auto-login wallet.  This allows anyone who has access to backups, to also be able to decrypt the data.
• Securely backing up the wallet file separate from the database is often forgotten, especially when ASM is used as the wallet location.  Not having the wallet file when restoring the database makes restoration and recovery impossible.

Steps you can take

• Create both a passworded wallet (ewallet.p12) and local auto-login wallet (cwallet.sso). With a local auto-login wallet, the wallet can only be opened on the host where the wallet was created.
• Backup the passworded wallet only (ewallet.p12).  The auto-login wallet can always be recreated from the passworded wallet.
• Properly store the password for you passworded encryption wallet. You will need the password to rotate your encryption keys, and create the auto-login wallet.

Oracle Key Vault (OKV)

 The best way to securely manage encryption keys is with OKV.

Why ?

• Keys are managed outside of the database and cannot be accessed locally outside of the database.
• Access to keys is granted to a specific database instance.
• OKV is clustered for High Availability, and the OKV cluster can be securely backed up.
• Key access is audited to allow for early detection of access.

Encryption Keys in a cyber vault architecture

Below is a diagram of a typical cyber vault architecture using ZDLRA.  Because the backups are encrypted, either because the databases are using TDE and/or they are creating RMAN encrypted backups sent to the ZDLRA, the keys need to be available in the vault also.

Not only is the cyber vault a separate, independent database and backup architecture, the vault also contains a separate, independent OKV cluster.

This isolates the cyber vault from any attack to the primary datacenter, including any attack that could compromise encryption key availability.

Encryption Keys in an advanced cyber vault architecture

Below is a diagram of an advanced cyber vault architecture using ZDLRA.  Not only are the backups replicated to a separate ZDLRA in the vault, they are internally replicated to an Isolated Recovery Environment (IRE).  In this architecture, the recover area is further isolated, and the OKV cluster is even further isolated from the primary datacenter. This provides the highest level of protection.

OKV Encryption Key Transfer

This blog post highlights the benefit of the newly released (21.11) OKV feature to allow for the secure transfer of encryption keys.

Periodic rotation of encryption keys is a required practice to protect encryption keys, and ensuring you have the current key available in a cyber vault is challenging.

OKV solves this challenge by providing the ability to transfer any new or changed keys between clusters.

Implementing OKV in a cyber Vault

When building a cyber vault, it is recommend to build an independent OKV cluster.  The OKV cluster in the vault is isolated from the primary datacenter and protected by an airgap.  The nodes in this cluster are  not be able to communicate with the OKV cluster outside of the vault.

The OKV cluster in the vault can be created using a full, secure, backup from the OKV cluster in the primary datacenter. The backup can be transferred into the vault, and then restored to the new, independent OKV cluster providing a current copy of the encryption keys.

Keeping OKV managed keys updated in a cyber Vault

The challenge, once creating an isolated OKV cluster has been keeping the encryption keys within the cluster current when new keys are created.  This was typically accomplished by transferring a full backup of OKV into the vault, and rebuilding the cluster using this backup.

OKV 21.11 provides the solution with secure Encryption Key Transfer.  Leveraging this feature you can securely transfer just the keys that have recently changed allowing you to manage independent OKV clusters that are synchronized on a regular basis.

The diagram below shows the flow of the secure Encryption Key Transfer package from the primary OKV cluster into the vault when the air-gap is opened.

This new OKV feature provides a much better way to securely manage encryption keys in a Cyber Vault.

Summary

As ransomware attacks increase, it is critical to protect a backup copy of your critical database in a cyber vault.  It is also critical to protect a copy of your encryption keys to ensure you can recovery those databases.  OKV provides the architecture for key management in both your primary datacenter and in a cyber vault.  The new secure key transfer feature within OKV allows you to synchronize keys across independent OKV clusters.

Listing Databases on an Oracle DB node

 In this blog post I am sharing a script that I wrote that will give you the list of databases running on a DB node.  The information  provided by the script is

• DB_UNIQUE_NAME
• ORACLE_SID
• DB_HOME

WHY

I have been working on a script to automatically configure OKV for all of the Oracle Databases running on a DB host.  In order to install OKV in a RAC cluster, I want to ensure the unique OKV software files are in the same location on every host when I set the WALLET_ROOT variable for my database.  The optimal location is to put the software under $ORACLE_BASE/admin/${DB_NAME} which should exist on single instance nodes, and RAC nodes.

Easy right?

I thought it would be easy to determine the name of all of the databases on a host so that I could make sure the install goes into $ORACLE_BASE/admin/{DB_NAME}/okv directory on each DB node.

The first item I realized is that the directory structure under $ORACLE_BASE/admin is actually the DB_UNIQUE_NAME rather than DB_NAME. This allows for 2 different instances of the same DB_NAME (primary and standby) to be running on the same DB node without any conflicts. 

Along with determining the DB_UNIQUE_NAME, I wanted to take the following items into account

• A RAC environment with, or without srvctl properly configured
• A non-RAC environment 
• Exclude directories that are under $ORACLE_BASE/admin that are not a DB_UNQUE_NAME running on the host.
• Don't match on ORACLE_SID.  The ORACLE_SID name on a DB node can be completely different from the DB_UNIQUE_NAME.

Answer:

After searching around Google and not finding a good answer I checked with my colleagues.  Still no good answer.. There were just suggestions like "srvctl config", which would only work on a RAC node where all databases are properly registered.  

The way I decided to this was to 

• Identify the possible DB_UNIQUE_NAME entries by looking in $ORACLE_BASE/admin
• Match the possible DB_UNIQUE_NAME with ORACLE_SIDs by looking in $ORACLE_BASE/diag/rdbms/${DB_UNIQUE_NAME} to find the ORACLE_SID name.  I would only include DB_UNIQUE_NAMEs that exist in this directory structure and have a subdirectory.
• Find the possible ORACLE_HOME by matching the ORACLE_SID to the /etc/oratab.  If there is no entry in /etc/oratab still include it.

Script:

Below is the script I came up with, and it displays a report of the database on the host.  This can be changed to store the output in a temporary file and read it into a script that loops through the databases.

#################################################################### ## Make sure that $ORACLE_BASE is set and exists before continuing #################################################################### if [ -z ${ORACLE_BASE+x} ] then echo "\$ORACLE_BASE must be set" exit 0 fi #################################################################### ## Make sure that $ORACLE_BASE/admin exists before continuing #################################################################### if [ -d ${ORACLE_BASE}/admin ] then echo "\$ORACLE_BASE is set to : ${ORACLE_BASE}" else echo "\$ORACLE_BASE is set to ${ORACLE_BASE} but ${ORACLE_BASE}/admin does not exist" exit 0 fi ######################################################################## ## Loop through $ORACLE_BASE/admin to find the list of DB_UNIQUE_NAMEs ######################################################################### for dbdir in $ORACLE_BASE/admin/* do DB_UNIQUE_NAME=${dbdir##*/} ## echo "possible DB_UNIQUE_NAME : ${DB_UNIQUE_NAME}" ######################################################################## ## Loop through $ORACLE_BASE/diag/rdbms/${DB_UNIQUE_NAME} to validate ######################################################################### for siddir in ${ORACLE_BASE}/diag/rdbms/${DB_UNIQUE_NAME}/* do db_sid=${siddir##*/} if [ -d ${siddir} ] then echo " " echo "DB_UNIQUE_NAME : ${DB_UNIQUE_NAME}" echo "ORACLE_SID : ${db_sid}" db_home=`cat /etc/oratab | grep ${db_sid} | cut -d ':' -f 2` if [ -z "${db_home}" ] then echo "ORACLE_HOME : ****** NOT IN /etc/oratab **** Cannot determine ORACLE_HOME *****" else echo "ORACLE_HOME : ${db_home}" fi echo " " fi done done

Output:

Below is the sample output from the script.. You can see that it doesn't require the DB to exist in the /etc/oratab file.

DB_UNIQUE_NAME : cdb1db1
ORACLE_SID     : cdb1db11
ORACLE_HOME    :  ******  NOT IN /etc/oratab **** Cannot determine ORACLE_HOME *****


DB_UNIQUE_NAME : daver
ORACLE_SID     : daver1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1


DB_UNIQUE_NAME : dbsgadat
ORACLE_SID     : dbsgadat1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1


DB_UNIQUE_NAME : dbsgprd
ORACLE_SID     : dbsgprd1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1

Finally:

If you are also trying to get a list of databases that are running on a DB node I hope this helps you.

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.