Autonomous Recovery Service Prechecks

 If you are configuring backups to utilize the Autonomous Recovery Service, there are some prerequisites that you need to be aware of.  If your Oracle Database was originally created in OCI and has always been OCI, those prerequisites are already configured for your database.  But, if you migrated a database to an OCI service, you might not realize that these items are required.

Prerequisites for Autonomous Recovery Service

1) WALLET_ROOT must be configured in the SPFILE.

WALLET_ROOT is a new parameter that was added in 19c, and its purpose is to replace the SQLNET.ENCRYTPION_WALLET_LOCATION in the sqlnet.ora file. Configuring the encryption wallet location in the sqlnet.ora file is depreciated.

WALLET_ROOT points to the directory path on the DB node(s) where the encryption wallet is stored for this database, and possibly the OKV endpoint client if you are using OKV to manage your encryption keys.

WALLET_ROOT allows each database to have it's own configuration location specific to each database.

There is a second parameter that goes with WALLET_ROOT that tells the database what kind of wallet is used (file, HSM or OKV), and that parameter is tde_configuration.

Running the script below should return the WALLET_ROOT location, and the tde_configuration information.

Checking the WALLET_ROOT and tde_configuration

set linesize 100 column value heading "Value" format a60 column name heading "Parameter" format a20 select name, value from v$parameter where upper(name) in ('WALLET_ROOT','TDE_CONFIGURATION');

Below you can see that both of these parameters are configured and I am using a wallet file.

Parameter            Value
-------------------- ------------------------------------------------------------
wallet_root          /var/opt/oracle/dbaas_acfs/bgrenndb/wallet_root
tde_configuration    keystore_configuration=FILE

If you need to migrate to WALLET_ROOT use the tooling .

dbaascli tde enableWalletRoot - enable wallet_root spfile parameter for existing database.

           Usage: dbaascli tde enableWalletRoot --dbname <value> [--dbRestart <value>] [--executePrereqs] [--resume [--sessionID <value>]]
                     Where:
                          --dbname - Oracle database name.
                          [--dbRestart - database restart option. Valid values : full|rolling ]
                          [ --executePrereqs - run the prerequisite checks and report the results. ]
                          [--resume - to resume the previous operation]
                          [--sessionID - to resume a specific session id.]

2) Encryption keys must be configured and available

In order to leverage the Autonomous Recovery Service, you must have an encryption key set and available for the CDB and each PDB.  If you migrated a non-TDE database (or plugged in a nonTDE PDB) to OCI you might not have configured encryption for one ore more PDBs.  The next step is to ensure that you have an encryption key set, and the wallet is open.  The query below should return "OPEN" for each CDB/PDB showing that the encryption key is available.

set linesize 170; set pagesize 100 column wrl_parameter format a60 column status heading 'Status' format a15 column pdb_name heading 'PDB Name' format a10 break on inst_id skip 1 select inst_id,b.name pdb_name,wrl_type, wrl_parameter, status from gv$encryption_wallet a,v$containers b where a.con_id = b.con_id(+) order by inst_id,b.name,wrl_type;

Below is the output from the query showing that the wallet is open for the CDB and the PDBs. 

   INST_ID PDB Name   Type       WRL_PARAMETER                                                Status
---------- ---------- ---------- ------------------------------------------------------------ ---------------
         1 BGRENNPDB1 FILE                                                                    OPEN
           CDB$ROOT   FILE       /var/opt/oracle/dbaas_acfs/bgrenndb/wallet_root/tde/         OPEN
           PDB$SEED   FILE                                                                    OPEN

         2 BGRENNPDB1 FILE                                                                    OPEN
           CDB$ROOT   FILE       /var/opt/oracle/dbaas_acfs/bgrenndb/wallet_root/tde/         OPEN
           PDB$SEED   FILE                                                                    OPEN

3) All tablespaces are TDE encrypted

TDE encryption is mandatory in OCI, and the Autonomous Recovery Service cannot be used if all of your tablespaces are not encrypted.  Below is a query to run that will tell you if your tablespaces are all encrypted.

column tablespaces heading "Encrypted tablespace information" format a60 select tablespaces from ( select 'Number of encrypted tablespaces : ' || count(1) tablespaces,1 ordercolumn from cdb_tablespaces where encrypted='YES' Union select 'Number of unencrypted tablespaces : ' || count(1) tablespaces,2 ordercolumn from cdb_tablespaces where encrypted='NO' Union select ' ' || '----' tablespaces,3 ordercolumn from dual Union select 'Total Number of tablespaces : ' || count(1) tablespaces,4 ordercolumn from cdb_tablespaces order by ordercolumn );

In my case I can see that all of the tablespaces are encrypted

Encrypted tablespace information
------------------------------------------------------------
Number of encrypted tablespaces   :      12
Number of unencrypted tablespaces :      0
                                         ----
Total Number of tablespaces       :      12

To find any tablespaces that are not encrypted you can run the query below.

set linesize 170 set pagesize 50 column pdb_name heading "PDB Name" format a10 column tablespace_name heading "Tablespace Name" format a30 column encrypted heading "Enc." format a5 break on pdb_name skip 1 select pdb_name, tablespace_name,encrypted FROM (select q.tablespace_name tablespace_name, z.name pdb_name, encrypted from v$containers z, cdb_tablespaces q where q.con_id = z.con_id and encrypted = 'NO' ) order by pdb_name,encrypted,tablespace_name;

4) Turn Off Any Manual Operational Backups.

In some cases, OCI users perform manual operational backups. These backups are run outside the standard tooling and support point-in-time recovery (non-KEEP backups).

Performing incremental backups to multiple locations can cause integrity issues with recovery.

The original backups can be kept to support the original retention window, and ensure that you have operational backups for a point-in-time prior to onboarding to the Recovery Service.  

Choose an appropriate cutover time, and switch to the Recovery Service, and slowly remove older backups as they expire until they are all completely removed.

RMAN create standby database - Restore or Duplicate ?

RMAN create standby database - Are you like me and use "restore database" for large databases, or like most people (based on my Linkedin poll) and use "duplicate for standby"? 

The table below shows you the 3 main differences between the 2 methods.

This post started with a discussion within my team around which method you use. I, being of the "restore database" camp, didn't realize how commonly used "duplicate for standby" is. 

I have also dug through the documentation, and there is no common method that is mentioned. Even the 21c documentation for creating a standby database doesn't mention using the duplicate command.

I also was pointed to a MOS note that goes through creating a standby directly from the primary across multiple nodes, and with encryption.  Creating a Physical Standby database using RMAN restore database from service (Doc ID 2283978.1)

Well in this post, I will explain why "restore database" has been my preference. 

NOTE : If you are creating a standby database that is encrypted and the source database is not (creating a standby database in OCI for example) then I have instructions at the end of this post for how to use "Restore Database" to create a hybrid standby database.

Duplicate database for standby

From the poll I ran, this is the most common way to create a standby database.  It is probably the simplest way also because a lot of the configuration of the standby database is done automatically as part of the automated process.

Below is the simplified steps to perform this process.

PRE work

1. Create simple initfile on the standby host.  The real SPFILE will be brought over as part of the duplication process.  This may contain location parameters for datafiles and redo logs if different from the primary.
2. Create directories on the standby host.  This includes the audit directory, and possibly the database file directories if they are different from the host.
3. Startup nomount.

Duplicate 

The duplicate process automatically performs these major steps using the standby as an auxiliary instance.

1.  Create an SPFILE. The process creates an SPFILE for the standby and sets parameters for the standby.
2. Shutdown/Startup standby database. This will use the newly created SPFILE during the rest of the processing
3. Restore backup controlfile for standby database. The controlfile for the standby database is put in place, and the spfile is updated to it's location
4. Mount controlfile . Mount the controlfile that was restored
5. Restore database . Restore the datafiles files for the CDB and PDBs to their new location on the standby
6. Switch datafile . Uses the new location of the datafiles that were restored.
7. Create standby redo logs.
8. Set parameters for standby database. The parameters necessary to communicate with the primary database are set.
9. Put standby in recover mode . By this time, you should have set the primary database to communicate with the standby database.

NOTES

If you noticed above, I highlighted the second step which forces a shutdown/startup of the standby database. Because of this step, it is not possible to use this method and restore across nodes in a RAC database.  This can cause the duplicate operation to take much longer for larger databases.

Then in step #5 you can see that the "Restore Database" is automatic in the processing and it is not possible to perform a "restore as encrypted" if you are migrating to OCI from a non-TDE database.  The duplicate process does support "restore as encrypted", but only for creating a new Database, not a standby database.

Restore Database

This is the method that I've always used.  There is no automation, but it gives you much more control over the steps.  

PRE work

1. Restore copy of prod SPFILE to standby host.  For this process, it doesn't matter if it is an intifile or spfile.  In this file you set all the parameters that are needed for the standby database to communicate with the primary and store datafiles/logfiles in the correct location.
2. Create directories on the standby host.  This includes the audit directory, and possibly the database file directories if they are different from the host.
3. Startup nomount.
4. Create copy of primary controlfile for standby. This will be used for the standby database, and should contain the backup catalog  of the primary database, and the RMAN settings including the  channel definitions.
5. Copy standby controlfile to standby host. The controlfile is copied to the standby host, and may be put in ASM at this point. Ensure the spfile points to the controlfile (and/or srvctl).
6. Alter database mount.  Mount the controlfile. 
7. Start up ALL nodes in the RAC cluster in mount mode.  This will allow you to restore the database across ALL nodes in the RAC cluster, and include all the networking from these nodes.  For a large database hosted on multiple DB nodes this can make a HUGE difference when restoring the database.
8. Create (or copy) TDE wallet.  If the standby database is going to be TDE, then include the wallet if the primary is TDE, or create a new wallet and key if the standby database is going to be TDE.

Restore Database 

The restore process is a manual process

1.  RMAN Connect to database (and possibly RMAN catalog). Connect to the database and make sure you have access to the backups. For ZDLRA this may mean connecting to the RMAN catalog.
2. Restore Database (as encrypted). This will restore the database to the new location.  With Restore Database, the database can be encrypted during the restore operation.  With 19c it is supported to have the standby database be encrypted without the primary database being encrypted (Hybrid dataguard).
3. Switch datafile . Uses the new location of the datafiles that were restored.
4. Recover database. This will use the archive logs that are cataloged to bring the standby database forward
5. Create standby redo logs.
6. Set parameters for standby database. The parameters necessary to communicate with the primary database are set.
7. Put standby in recover mode . By this time, you should have set the primary database to communicate with the standby database.

NOTES

With the restore database, there are 2 sections I highlighted and these are the advantages that I love about using this method.

• RMAN is restoring across multiple nodes in a RAC cluster which can make the restore operation much faster.
• Restore as encrypted allows you take a database that may have TDE partially implemented, or not implemented and create a new standby database that is encrypted. With the duplicate method, TDE would have to be implemented separately.

If you are restoring a VERY large database (200 TB for example) that was not TDE from object storage to the Exadata Cloud Service, both of these advantages can make a HUGE difference when creating a standby database.

Comparison

The chart below compares the the differences between "Duplicate Database" and "Restore Database".

WARNING: When using a ZDLRA for backups, it is NOT recommended to use the "Restore Database" to clone a database as a new copy. Registering the restored copy can cause issues with the RMAN catalog because the "restore database" leaves entries in the RC_SITE table.

Data Guard Hybrid Cloud Configuration

The ability to create a hybrid cloud configuration was introduced in Version 19.16 and there is a great blog post from Glen Hawkins explaining this feature.

This feature allows you have your Primary database remain unencrypted (no ASO license), but still have the standby database in OCI be TDE encrypted.

In this section I want to talk about how you can use "Restore Database as Encrypted" to implement this configuration quickly.

If you want to implement this feature using "Duplicate for standby" you have to separately encrypt the datafiles once they are restored in OCI.  This can be done online, or offline, but it is still a time consuming task.

Prepare the primary and future standby databases

The first step is prepare the primary database and future standby database by creating a wallet file and setting encryption keys.  There is a great video put together by Peter Wahl (PM for TDE and OKV) that goes through a lot of the steps.

Below is a summary of the steps you need to perform.  You can follow along the steps in Peter's video and I will point out where in the video you will find each step.

• Create the directories on the primary (3:40) -  Directories are specified in the video and need to be created on all nodes in a RAC cluster.
• Create the directories on the standby database (4:18) -Directories are specified in the video and need to be created on all nodes in a RAC cluster.
• Set the wallet_root in the primary (4:25) - This is set in the SPFILE only
• Set tablespace_encryption to decrypt_only on primary (4:40) -  This is set in the SPFILE only
• Set the default algorithm to AES256 on primary (4:50) - This is set in the SPFILE only
• Set wallet_root on standby, tablespace_encryption to auto_enable, and default algorithm on standby --  This is set in the initfile that you create prior to performing the restore.  This step is different from the video because there is no standby at this point.
• Bounce the primary database (5:50) - This can be in a rolling manner.
• Create a password protected wallet on the primary (7:25) - This gets created in the default location specified from WALLET_ROOT
• Create an auto open wallet on the primary (7:30) - This will make it automatically open for the database.
• Set the encryption keys in the primary (7:40) - The keys will be created so that they can be used when restoring the datafiles on the standby database.
• Copy the wallets from the primary to the standby (7:55) - This provides the standby database with the keys to encrypt.

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.

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.

ZDLRA - Copy-to-cloud steps by step explained

 One of the best features of the ZDLRA is the ability to dynamically create a full Keep backup and send it to Cloud (ZFSSA or OCI) for archival storage.

Here is a great article by Oracle Product Manager Marco Calmasini that explains how to use this feature.

In this blog post, I will go through the RACLI steps that you execute, and explain what is happening with each step

The documentation I am started with is the 21.1 administrators Guide which can be here.  If you are on a more current release, then you can find the steps in chapter named "Archiving Backups to Cloud".

Deploying the OKV Client Software

To ensure that all the backup pieces are encrypted, you must use OKV (Oracle Key Vault) to manage the encryption keys that are being used by the ZDLRA.  Even if you are using TDE for the datafiles, the copy-to-cloud process encrypts ALL backup pieces including the backup of the controlfile, and spfile which aren't already encrypted.

I am not going to go through the detailed steps that are in the documentation to configure OKV, but I will just go through the high level processes.

The most important items to note on this sections are

• Both nodes of the ZDLRA are added as endpoints, and they should have a descriptive name that identifies them, and ties them together.
• A new endpoint group should be created with a descriptive name, and both nodes should be added to the new endpoint group.
• A new virtual wallet is created with a descriptive name, and this needs to both associated with the 2 endpoints, and be the default wallet for the endpoints.
• Both endpoints of the ZDLRA are enrolled through OKV and during the enrollment process a unique enrollment token file is created for each node. It is best to immediately rename the files to identify the endpoint it is associated with using the format <myhost>-okvclient.jar.
• Copy the enrollment token files to the /radump directory on the appropriate host.

NOTE: It is critical that you follow these directions exactly, and that each node has the appropriate enrollment token with the appropriate name before continuing.

#1 Add credential_wallet

racli add credential_wallet

Fri Jan 1 08:56:27 2018: Start: Add Credential Wallet

Enter New Keystore Password: <OKV_endpoint_password>

Confirm New Keystore Password:

Enter New Wallet Password: <ZDLRA_credential_wallet_password> 

Confirm New Wallet Password:

Re-Enter New Wallet Password:

Fri Jan 1 08:56:40 2018: End: Add Credential Wallet

The first step to configure the ZDLRA to talk to OKV is to have the ZDLRA create a password protected SEPS wallet file that contains the OKV password.

This step asks for 2 new passwords when executing

1. New Keystore Password - This password is the OKV endpoint password.  This password is used to communicate with OKV by the database, and can be used with okvutil to interact with OKV directly
2. New Wallet Password - This password is used to protect the wallet file itself that will contain the OKV keystore password.

This password file is shared across both nodes.

Update contents      -  "racli add credential"

Change password    - "racli alter credential_wallet"

#2 Add keystore

racli add keystore --type hsm --restart_db

RecoveryAppliance/log/racli.log

Fri Jan 1 08:57:03 2018: Start: Configure Wallets

Fri Jan 1 08:57:04 2018: End: Configure Wallets

Fri Jan 1 08:57:04 2018: Start: Stop Listeners, and Database

Fri Jan 1 08:59:26 2018: End: Stop Listeners, and Database

Fri Jan 1 08:59:26 2018: Start: Start Listeners, and Database

Fri Jan 1 09:02:16 2018: End: Start Listeners, and Database

The second step to configure the ZDLRA to talk to OKV is to have the ZDLRA database be configured to communicate with OKV. The Database on the ZDLRA will be configured to use the OKV wallet for encryption keys which requires a bounce of the database.  

Backout         - "racli remove keystore" 

Status            - "racli status keystore"

Update          - "racli alter keystore"

Disable          - "racli disable keystore"

Enable            - "racli enable keystore"

#3 Install okv_endpoint (OKV client software)

racli install okv_endpoint

23 20:14:40 2018: Start: Install OKV End Point [node01]

Wed August 23 20:14:43 2018: End: Install OKV End Point [node01]

Wed August 23 20:14:43 2018: Start: Install OKV End Point [node02]

Wed August 23 20:14:45 2018: End: Install OKV End Point [node02]

The third step to configure the ZDLRA to talk to OKV is to have the ZDLRA nodes (OKV endpoints) enrolled in OKV.  This step will install the OKV software on both nodes of the ZDLRA, and complete the enrollment of the 2 ZDLRA nodes with OKV.  The password that entered in step #1 for OKV is used during the enrollment process.

Status            - "racli status okv_endpoint"

NOTE: At the end of this step, the status command should return a status of online from both nodes.

Node: node02

Endpoint: Online

Node: node01

Endpoint: Online

#4 Open the Keystore

racli enable keystore

The fourth step to configure the ZDLRA to talk to OKV is to have the ZDLRA nodes open the encryption wallet in the database. This step will use the saved passwords from step #1 and open up the encryption wallet.

NOTE: This will need to be executed after any restarts of the database on the ZDLRA.

#5 Create a TDE master key for the ZDLRA in the Keystore

racli alter keystore --initialize_key

The final step to configure the ZDLRA to talk to OKV is to have the ZDLRA create the master encryption for the ZDLRA in the wallet.

Creating Cloud Objects for Copy-to-Cloud

These steps create the cloud objects necessary to send backups to a cloud location.

NOTE: If you are configuring multiple cloud locations, you may go through these steps for each location.

Configure public/private key credentials

Authentication with the object storage is done using an X.509 certificate.  The ZDLRA steps outlined in the documentation will generate a new pair of API signing keys and register the new set of keys.

You can also use any set of API keys that you previously generated by putting your private key in the shared location on the ZDLRA nodes..

In OCI each user can only have 3 sets of API keys, but the ZFSSA has no restrictions on the number of API signing keys that can be created.

Each "cloud_key" represents an API signing key pair, and each cloud_key contains 

1. pvt_key_path - Shared location on the ZDLRA where the private key is located
2. fingerprint      - fingerprint associated with the private key to identify which key to use.

You can use the same "cloud_key" to authenticate to multiple buckets, and even different cloud locations.

Documentation steps to create new key pair

#1 Add Cloud_key

racli add cloud_key --key_name=sample_key

Tue Jun 18 13:22:07 2019: Using log file /opt/oracle.RecoveryAppliance/log/racli.log

Tue Jun 18 13:22:07 2019: Start: Add Cloud Key sample_key

Tue Jun 18 13:22:08 2019: Start: Creating New Keys

Tue Jun 18 13:22:08 2019: Oracle Database Cloud Backup Module Install Tool, build 19.3.0.0.0DBBKPCSBP_2019-06-13

Tue Jun 18 13:22:08 2019: OCI API signing keys are created:

Tue Jun 18 13:22:08 2019:   PRIVATE KEY --> /raacfs/raadmin/cloud/key/sample_key/oci_pvt

Tue Jun 18 13:22:08 2019:   PUBLIC  KEY --> /raacfs/raadmin/cloud/key/sample_key/oci_pub

Tue Jun 18 13:22:08 2019: Please upload the public key in the OCI console.

Tue Jun 18 13:22:08 2019: End: Creating New Keys

Tue Jun 18 13:22:09 2019: End: Add Cloud Key sample_key

This step is used to generate a new set of API signing keys,

The output of this step is a shared set of files on the ZLDRA which are stored in:

/raacfs/raadmin/cloud/key/{key_name)/

In order to complete the cloud_key information, you need to add the public key to OCI, or to the ZFS and save the fingerprint that is associated with the public key. The fingerprint is used in the next step.

#2 racli alter cloud_key

racli alter cloud_key --key_name=sample_key --fingerprint=12:34:56:78:90:ab:cd:ef:12:34:56:78:90:ab:cd:ef

The fingerprint that is associated with the public key (from the previous step) is added to the ZDLRA cloud_key information so that it can be used for authentication.  

Both the private key, and the fingerprint are need to use the API signing key for credentials.

Using your own API signing key pair

#1 Add cloud_key

racli add cloud_key --key_name=KEY_NAME [--fingerprint=PUBFINGERPRINT --pvt_key_path=PVTKEYFILE]

You can add your own API signing keys to the ZDLRA by  using the "add cloud_key" command identifying both the private key file location (it is best to follow the format and location in the automated steps) and the fingerprint associated with the API signing keys.

It is assumed that the public key has already been added to OCI, or to the ZFSSA.

Status        - racli list cloud_key

Delete        - racli remove cloud_key

Update       - racli alter cloud_key

Documentation steps to create a new cloud_user 

This step is used to create the wallet entry on the ZDLRA that is used for authenticating to the object store.

This step combines the "cloud_key", which contains the API signing keys, the user login information and the compartment (on ZFSSA the compartment is the share ).

The cloud_user can be used for authentication with multiple buckets/locations that are identified as cloud_locations as long as they are within the same compartment (share on ZFSSA).

The format of the command to create a new cloud_user is below

racli add cloud_user 

--user_name=sample_user

--key_name=sample_key

--user_ocid=ocid1.user.oc1..abcedfghijklmnopqrstuvwxyz0124567901

--tenancy_ocid=ocid1.tenancy.oc1..abcedfghijklmnopqrstuvwxyz0124567902

--compartment_ocid=ocid1.compartment.oc1..abcedfghijklmnopqrstuvwxyz0124567903

The parameters for this command are

• user_name        - This is the username that is associated with the cloud_user to unique identify it.
• key_name         - This is name of the "cloud_key" identifying the API signing keys to be used.
• user_ocid          - This is the Username for authentication. In OCI this is the users OCID, in ZFS, this combines the ocid format with the username on the ZFSSA that owns the share.
• tenancy_ocid    - this is the tenancy OCID in OCI, on ZFSSA it is ignored
• compartment_ocid - this is the OCID, on ZFSSA it is the share

For more information on configuring the ZFSSA see

How to configure Zero Data Loss Recovery Appliance to use ZFS OCI Object Storage as a cloud repository (Doc ID 2761114.1)

List                - racli list  cloud_user

Delete            - racli remove  cloud_user

Update           - racli alter cloud_user

Documentation steps to create a new cloud_location 

This step is used to associate the cloud_user (used for authentication) with both the location and the bucket that is going to be used for backups.

racli add cloud_location

--cloud_user=<CLOUD_USER_NAME>

--host=https://<OPC_STORAGE_LOCATION>

--bucket=<OCI_BUCKET_NAME>

--proxy_port=<HOST_PORT>

--proxy_host=<PROXY_URL>

--proxy_id=<PROXY_ID>

--proxy_pass=<PROXY_PASS>

--streams=<NUM_STREAMS>

[--enable_archive=TRUE]

--archive_after_backup=<number>:[YEARS | DAYS]

[--retain_after_restore=<number_hours>:HOURS]

--import_all_trustcert=<X509_CERT_PATH>

--immutable
--temp_metadata_bucket=<metadata_bucket>  

 

I am going to go through the key items that need to be entered here.  I am going to skip over the PROXY information and certificate.

• cloud_user - This is the object store authentication information that was created in the previous steps.
• host - This the URL for the object storage location. On ZFS the namespace in the URL is the "share"
• bucket - This is the bucket where the backups will be sent. The bucket will be created if it doesn't exists. 
• streams - The maximum number of channels to use when sending backups to the cloud
• enable_archive - Not used with ZFS. With OCI the default TRUE allows you to set an archival strategy, FALSE will automatically put backups in archival storage.
• archive_after_restore - Not used with ZFS. Automatically configures an archival strategy in OCI
• retain_after_restore - Not used with ZFS. Sets the period of time that backups will remain in standard storage before returning to archival storage.
• immutable - This allows you to set retention rules on the bucket by using the <metadata_bucket> for temporary files that need to be deleted after the backup. When using immutable you must also have a temp_metadata_bucket
• temp_metadata_bucket - This is used with immutable to configure backups to go to 2 buckets, and this bucket will only contain a temporary object that gets deleted after the backup completes.

This command will create multiple attribute sets (between 1 and the number of streams) for the cloud_location that can be used for sending archival backups to the cloud with different numbers of channels.

The format of <copy_cloud_name> is a combination of  <bucket name> and <cloud_user>.

The format of the attributes used for the copy jobs is <Cloud_location_name>_<stream number>

Update          - racli alter cloud_location

Disable          - racli disable cloud_location  - This will pause all backups going to this location

Enable           - racli enable cloud_location  - This unpauses all backups going to this location

List                - racli list  cloud_location

Delete            - racli remove cloud_location

NOTE: There are quite a few items to note in this section.

• When configuring backups to go to ZFSSA use the documentation previously mentioned to ensure the parameters are correct.
• When executing this step with ZFSSA, make sure that the default OCI location on the ZFSSA is set to the share that you are currently configuring. If you are using multiple shares for buckets, then you will have to change the ZFSSA settings as you add cloud locations.
• When using OCI for archival ensure that you configure the archival rules using this command. This ensures that the metadata objects, which can't be archived are excluded as part of the lifecycle management rules created during this step.

Create the job template using the documentation.

Why DBCS (Oracle Base Database Service) in OCI can make a DBA's life much easier (even with BYOL)

DBCS (now named Oracle Base Database service, but I will call it DBCS throughout this post) in OCI  can help make a DBA's life easier.  When I was testing the new Autonomous Recovery Service for Oracle Database in OCI, I created a LOT of different DBCS systems to test backup and recovery.  Along the way I learned a lot about the workings of DBCS, and I came to appreciate how it makes sense, even if you are a BYOL (bring your own license) customer.

I'm more of a an "old school" DBA, preferring command line, and scripting processes myself.  I am typically not a fan of automation.  When using DBCS I was surprised by all the things it would do for me that I would have to do manually.

Install oracle software and create a database

Having installed oracle software hundreds of times, and having created test databases, I didn't think I would care much about automation that did this for me.

Central Software image management

What I found in OCI, is that you can create your own software images that can be used to ensure each new database environment is consistent.  OCI gives you ability to create your own set of release images (which can include patches).  This ensures each time I create a new DBCS environment, and choose my custom image, it's running the same version in all environments. No more installing base release, then patches, and then then any possible one-off patches.  This makes the installation of the database software much, much easier, and ensures consistency.

Easy Database creation

Recently I've gotten familiar with performing a silent database creation, as using dbca isn't always easy to configure.  The tooling provided by DBCS will not only create a database for you, but will also configure TDE encryption (with a local wallet, or using OCI vault).  It can even create a RAC database across 2 nodes.  And don't forget, it can create the standby for me also.

Configure ASM storage

Now this is the most interesting piece I found when using DBCS.  Not only does the DBCS service create a disk group, but it automatically stripes multiple block volumes together maximizing performance.  This is a HUGE help in ensuring I am getting the best performance.

When I was going through what the configuration did, I tried to build tables showing how the different storage sizes translate to the storage configurations.

There were 2 configurations and DB data storage sizes, one for Flex, and one for Standard shapes.

Flex

First I looked at flex, and regardless of the performance level these were the sizes.

Then within Flex, I looked at the "Balanced performance" configuration.

Balanced Performance configuration

You can see that as the DB storage available goes up, the number of disks goes up also allowing for a higher  possible IOPS than you would get from a single Block Storage device.

Below is the chart for "High Performance"

High Performance configuration

You can see that the IOPS is even higher, and it is using even more disks to get that performance.

Standard

Next looked at standard shapes, and regardless of the performance level these were the sizes. Note that with Standard shapes, there were many more options for configurations.

Balanced Performance configuration

High Performance configuration

Benefits of DBCS

I also went through what some of the other benefits of DBCS are, and below is the list I came up with.

• When using the DBCS service,  the storage cost is based on the Block Storage cost. This is the same cost as you would pay in an IaaS service.  Having the storage striped and configured for maximum IOPS makes this a huge plus.

• DBCS allows you purchase licenses if you don't have enough licenses to use the BYOL option.

• The DBCS service price is based on OCPU and is the same regardless of the shape. Memory is included in the OCPU cost.

• DBCS automatically configures RAC if you choose it.

• DBCS provides tooling that automatically configures backups, can apply patches, and rotate encryption keys.

• DBCS allows you to automate the cloning of your database, and automate any restores.

• DBCS includes TDE, and relieves you of having to own the ASO license.  

Conclusion:

DBCS offers a lot more than you realize. Take a deep dive into what it can do for you to save time as DBA and you also might realize that sometimes tooling along with automation has it's benefits.