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.

Creating Archival Backups from ZDLRA using EM Cloud Control

The ability for the ZDLRA to create archive backups was added with release 21.1 and I wrote a blog post (here) on how to do this.  I recently noticed that the latest plugin for ZDLRA (13.5.1.0.0) allows you to dynamically schedule your archival jobs from EM Cloud Control.

In this blog post I will go through how to use this new feature.

First the release that I am using for this is

•  EM Cloud Control 13.5.0.19
• Zero Data Loss Recovery Appliance Plugin Release 13.5.1.0.0

Where to find the feature:

If you have the correct plugin, you will notice that there is a new choice in the "Recovery Appliance"  pull down menu provided by the plugin.

There is an entry for "Archival Backups" that appears just below "replication".  When you chose this option it will bring up a new window that you can use to prepare to create an archival backup.

Notice that there is nothing listed here.  I did create an archival backup earlier, but it isn't listed.

In order to create an archival backup, Click on the "Create Archival Backup" button and continue to one of the next sections.  You can either create a "one-time" archival backup, or schedule a recurring backup.  The default is to create a recurring scheduled backup

Create a recurring scheduled Backup:

Protected Databases

I am going to create a recurring scheduled backup for my database "testdb".   I can choose only one database.

Recovery Point Time

• This should be for every month.  I chose every month individually, and I ensure that I chose all 12 months.
• This should occur on the "last" day of the month
• The recovery point should be 11:00 PM based on the browser time (I can also chose the DB time, or UTC).
• I want to set the restore point prefix to be "MONTHLY_KEEP_BACKUP_". The job will affix the timestamp to the end of the the prefix

Retention Time

• Keep this backup for 3 years (I can also choose a time period based on months or weeks).

Properties

• Use the attribute set "TESTDB" that I created earlier.
• Leave the default format of the backup pieces, but I can change the format if I'd like to.
• I am setting not setting encryption algorithm on (I would need to for a copy-to-cloud job).
• I am not setting a compression algorithm on.

My screen for creating the recurring backup looks like the image below.

Once I complete everything I can click on OK, and it will submit my schedule to run.

Viewing recurring scheduled Backup  Procedures:

The recurring backups are not scheduled as jobs, they are scheduled as Procedures because they have a few steps to execute.

You can find these scheduled backups in EM Cloud Control under Enterprise --> Provisioning and Patching --> Procedure Activity.

At this point, I scheduled 2 jobs (actually procedures) , prior and you can see them in this section.

In order to see more detail on these 2 procedures I can select one of them and click on the "Reschedule" button at the top of the list of procedures.

I know the first procedure is for executing scheduled archival backups for TESTDB because the name of the procedure contains TESTDB followed by the timestamp.

Below is what it shows it when I choose to reschedule it.

You can see that during this test, I created a monthly schedule that creates a new backup at 7:00 AM PT on the 10th of the months listed.  During my test I did not include all months, and those that I included, I did not choose them in order.  

When I go back to the list of procedures, and drill into the procedure, I can see there there are just a couple of steps, and I can't see any detail as to what the steps do.

Viewing executed scheduled Backup Procedures:

In order to view any executed scheduled backup you would look in the same place as you do for schedule procedures.  Along with the 2 scheduled procedures I had above, I also had one of the actually execute and I see it in the list.

You can see that the first scheduled job had successfully executed, now let's take a look at the executed step and output.

If you click on the highlighted "Run" name, you can drill into the procedure and steps. Below is what I see for the step detail for this execution.

Below is what the output of the last step looks like.

You can see all of the attributes that were set when I created this procedure, and you can see the actual command that executed to create the archival backup.

Create a One-time only archival Backup:

Similar to creating an recurring backup, you go the "Create Archival Backup" section within the ZDRLA plugin.

Protected Databases

I am going to create a One-time archival backup for my database "testdb".   I can choose only one database.

Create Archival Backup For

Within this section there are 3 choices

Point-in-Time : Using a date picker choose the point in time you want to create the archival backup as of. 

SCN : Enter the SCN you want to use. The text tells the range of SCN numbers you can use.

Restore Point : Enter the restore point from the drop down menu.

Retention Time (same as recurring backups)

• Keep this backup for 3 years (I can also choose a time period based on months or weeks).

Properties (same as recurring backups)

• Use the attribute set "TESTDB" that I created earlier.
• Leave the default format of the backup pieces, but I can change the format if I'd like to.
• I am setting not setting encryption algorithm on (I would need to for a copy-to-cloud job).
• I am not setting a compression algorithm on.

Click "OK" after filling in all of the detail, and submit the job.

Viewing archival Backups:

In the window that you choose the "Create Archival Bucket" you can view existing backups.  In order to view the backups, you must first choose the "Protected Database" you want to view the backups for. Below is what you would see once a backup is initiated.

Summary:

You still might find it easier to create the archive log yourself using the PL/SQL package. This can be done either manually or through scripting.  The GUI gives you nice way to schedule individual database jobs, but for 100's, or 1000's of databases with varying requirements, scripting can be more flexible.

ZDLRA's space efficient encrypted backups with TDE explained

 In this post I will explain what typically happens  when RMAN either compresses, or encrypts backups and how the new space efficient encrypted backup feature of the ZDLRA solves these issues.

TDE - What does a TDE encrypted block look like ?

In the image above you can see that only the data is encrypted with TDE.  The header information (metadata) remains unencrypted.  The metadata is used by the database to determine the information about the block, and is used by the ZDLRA to create virtual full backups.

Normal backup of TDE encrypted datafiles

First let's go through what happens when TDE is utilized, and you perform a RMAN backup of the database.

In the image below, you can see that the blocks are written and are not changed in any way. 

NOTE: Because the blocks are encrypted, they cannot be compressed outside of the database.  

Compressed backup of TDE encrypted datafiles

Next let's go through what happens if you perform an RMAN backup of the database AND tell RMAN to create compressed backupsets.  As I said previously, the encrypted data will not compress., and because the data is TDE the backup must remain encrypted.

Below you can see that RMAN handles this with series of steps.  

RMAN will

1. Decrypt the data in the block using the tablespace encryption key.
2. Compress the data in block (it is unencrypted in memory).
3. Re-encrypt the whole block (including the headers) using a new encryption key generated by the RMAN job

You can see in the image below, after executing two RMAN backup jobs the blocks are encrypted with two different encryption keys. Each subsequent backup job will also have new encryption keys.

Compression or Deduplication

This leaves you with having to chose one or the other when performing RMAN backup jobs to a deduplication appliance.  If you execute a normal RMAN backup, there is no compression available, and if you utilize RMAN compression, it is not possible to dedupe the data. The ZDLRA, since it needs to read the header data, didn't support using RMAN compression.

How space efficient encrypted backups work with TDE

So how does the ZDLRA solve this problem to be able provide both compression and the creation of virtual full backups?

The flow is similar to using RMAN compression, BUT instead of using RMAN encryption, the ZDLRA library encrypts the blocks in a special format that leaves the header data unencrypted.  The ZDLRA library only encrypts the data contents of blocks.

1. Decrypt the data in the block using the tablespace encryption
2. Compress the data in block (it is unencrypted in memory).
3. Re-encrypt the data portion of the block (not the headers) using a new encryption key generated by the RMAN job

In the image below you can see the flow as the backup is migrating to utilizing this feature.  The newly backed up blocks are encrypted with a new encryption key with each RMAN backup, and the header is left clear for the ZDLRA to still create a virtual full backup.

This allows the ZDLRA to both compress the blocks AND provide space efficient virtual full backups

How space efficient encrypted backups work with non-TDE blocks

So how does the ZDLRA feature work with non-TDE data ?

The flow is similar to that of TDE data, but the data does not have to be unencrypted first.  The blocks are compressed using RMAN compression, and are then encrypted using the new ZDLRA library.

In the image below you can the flow as the backup is migrating to utilizing this feature.  The newly backed up blocks are encrypted with a new encryption key with each RMAN backup, and the header is left clear for the ZDLRA to still create a virtual full.

I hope this helps to show you how space efficient encrypted backups work, and how it is a much more efficient way to both protect you backups with encryption, and utilize compression.

NOTE: using space efficient encrypted backups does not require with the ACO or the ASO options.

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.

Creating dynamic KEEP archival backups from ZDLRA

 This post covers how to utilize the new package DBMS_RA.CREATE_ARCHIVAL_BACKUP to dynamically create KEEP archival backups from a ZDLRA.

When using this package to schedule KEEP backups, I recommend creating restore points with every incremental backup.  Read this blog post to find out why.

PROCEDURE CREATE_ARCHIVAL_BACKUP(
   db_unique_name IN VARCHAR2,
   from_tag IN VARCHAR2 DEFAULT NULL,
   compression_algorithm IN VARCHAR2 DEFAULT NULL,
   encryption_algorithm IN VARCHAR2 DEFAULT NULL,
   restore_point IN VARCHAR2 DEFAULT NULL,
   restore_until_scn      IN VARCHAR2 DEFAULT NULL,
   restore_until_time     IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   attribute_set_name     IN VARCHAR2,
   format                 IN VARCHAR2 DEFAULT NULL,
   autobackup_prefix      IN VARCHAR2 DEFAULT NULL,
   restore_tag            IN VARCHAR2 DEFAULT NULL,
   keep_until_time        IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   max_redo_to_apply      IN INTEGER DEFAULT 14                    --> Added in 21.1 June PSU
   comments IN VARCHAR2 DEFAULT NULL);

NOTE: This blog post was updated to include the MAX_REDO_TO_APPLY parameter which is not documented as of writing this post.

 The documentation can be found here.  

These archival KEEP backups can be sent to either

• TAPE - Using the copy-to-tape process you can send archival backups to physical tape, virtual tape, or any media manager that uses a "TAPE" backup type.
• CLOUD - Using the copy-to-cloud process you can send archival backups to an OCI object store bucket which can be either on a local ZFSSA (using the OCI API protocol), or to the Oracle Cloud directly.

NOTE: When sending backups to a cloud location, retention rules can be set on the bucket LOCKING the cloud backups to ensure that they are immutable.  This is integrated with the new compliance settings on the RA21.

How to use this package

1. Identify the Database

Because this is more of an on demand process, you have to execute the package for each database separately (rather than by using a protection policy), and identify for each database the point-in-time you want to use for recovery..

2. Set Archival Restore Point

Because the archival backup is dynamically created using existing backups the restore point works differently than if you create the KEEP backup on demand from the protected database. 

When you create a KEEP backup from the protected database, the backup contains 

• Full backup of all datafiles
• Backup of spfile and controlfile
• Backup of archive logs created during the backup starting with a log switch at the beginning of the backup.
• Final archive logs created by performing a log switch at the end of the backup.

 When you create an Archival backup from the ZDLRA , the backup contains

• Most current virtual full backup of each datafile prior to the point in time for recovery that you choose. 
• Backup of spfile and controlfile 
• Backup of the active archive logs generated when the oldest virtual full datafile backup started, up to the archive logs needed to recover until the point in time chosen for recovery.

As you can see a normal KEEP backup generated by the protected database is a a "self-contained" backup that can be recovered only to the point in time that the backup completed.  You can increase the recover point by adding additional KEEP archival log backups after the backup.

The dynamically created KEEP backup generated by the ZDLRA is also a "self-contained" backup that can be recovered to any point in time after the last datafile backup completed, but it also includes any point in time up to the restore point identified.  

Choices for a dynamic restore point 

 There are 3 options to choose a specific restore point. If you do not set one of these options, the KEEP backup will be created using the current restore point of the database.  

•  RESTORE_POINT - If you set a unique restore point in the database immediately following an incremental backup (or  at a later point in time), you can create a KEEP backup that will recover to that point-in-time.  When using this process, after creating the restore point you should ensure that you also perform a log switch, and a log sweep to backup the archive logs.  This restore point name is used as the default RESTORE_TAG, and should be unique.  The recommended name (because it is the default KEEP restore tag) is "<KEEP_BACKUP_><yyyyMMddHH24miSS>".  BUT- in order to better identify the restore point, I would use a shorter name that just contains the date (assuming you are only performing an single daily incremental backup), for example "KEEP_BACKUP_MMDDYY".  By using a restore point, you can better control the amount of archive logs necessarily to recover the database.

 

• Incremental forever backups ensure that the duration of the backup is much shorter than a typical full KEEP backup limiting the amount of archive logs necessary to have a recovery point.
• Setting a restore point immediately following the backup ensures that the recovery window following the last datafile backup piece is short also limiting the amount of archive logs necessary.

• RESTORE_UNTIL_SCN or RESTORE_UNTIL_TIME -  I am grouping these 2 choices together, because they are so similar.  Unlike using a restore point that is preset, using either of these options will create the KEEP archive backup with a recover point as the SCN number given or the UNTIL TIME given (using the databases timezone). 

FROM_TAG - The documentation states that only backups containing the FROM_TAG will be considered if a FROM_TAG is set. I am thinking this would make sense if you let the restore point default to the current time, and you want to choose which backup pieces to include.  I am not sure of the full use of this option however.

WARNING: This process only looks back 14 days for a full backup to start the KEEP backupset with.  If you do not have a full backup within the 14 day window this can be over ridden with the  MAX_REDO_TO_APPLY parameter in the package call. This was added in the 21.1 June PSU to allow customers to set a window farther than 14 days.

 RECOMMENDATIONS 

•  Because you can create up to 2048 RESTORE_POINTs in a database, and normal restore points are automatically dropped when necessary, I would recommend creating a restore point following each incremental backup with the format mentioned above, This will allow you to create a self-contained FULL KEEP backup from any incremental backup as needed. This can be used to easily create an end-of-month KEEP backup (for example).

 

• I would use the RESTORE_UNTIL options when it is necessary to create a KEEP backup as of a specific point-in-time regardless of when the backup completed. This would be used if the recovery point is critical.

WARNING

Before creating the archival backup, ensure you have the archive logs backed up that are needed to support the recover point, and ensure there is enough time for the incremental backups to virtualize.  You many need perform a log switch and execute an additional log sweep prior to scheduling the archival backup.

3. Set Archival Options

COMPRESSION_ALGORITHM -  The default is no compression, and if the backup piece is already compressed, it will not try to compress the backup again.  The documentation does a good job of going through the options, and why you would chose one or the other.  Keep in mind that if your database uses TDE for all the datafiles, there will be no gain with compression, and the extra resources required for compression may slow down the restore.  Also, the compression is performed by the ZDLRA (RMAN compression), but the de-compression is performed by the protected database during restore.

 ENCRYPTION_ALGORITHM - The default is no encryption, but it is important to understand that any copy-to-cloud processing MUST have encryption set.  It is also important to understand that the ZDLRA must be using OKV (Oracle Key Vault) to store the encryption keys when encryption is set. The list of algorithms can be found in the documentation.

 

4. Set Archival Location and Name

ATTRIBUTE_SET_NAME - This must be specified, and this identifies the backup location to send the archival backups.

FORMAT - By default the  backup pieces are given handles automatically generated by the ZDLRA, this setting allows you to change the default backup piece format using normal RMAN formatting options.

AUTOBACKUP_PREFIX - - By default the autobackup pieces will retain the original names, but  you can add a prefix to the original autobackup names. 

 

5. Set Restore TAG

 By default the RESTORE_TAG defaults to  "<KEEP_BACKUP_><yyyyMMddHH24miSS>". This can be overridden to give the backup a more meaningful tag. For example, the end-of-month backup could be tagged as "MONTHLY_12_2023", making it easier to automate finding specific KEEP backups.

 RECOMMENDATIONS 

I would set the Restore Tag to a set format that makes the KEEP backups easy to find. You can see the example above. 

6. Set KEEP_UNTIL time

The default KEEP_UNTIL time is "FOREVER". In most cases you want to set an end date for the backup, allowing the ZDLRA to automatically remove the backup when it expires.  This date-time is based on the timezone of the protected database. 

 SUMMARY 

 If using this functionality to dynamically create Archival KEEP backups...

• I would set a Restore Point in each database immediately following every incremental backup.  
• I would schedule this procedure to create the archival backup with a formatted restore tag to make the backup easy to find.
• If backing up to a CLOUD location, I would use retention rules to ensure the backups are immutable until they expire.

 

 

X

Backup Anywhere offers Expanded Replication for High Availability and More Flexibility

The previous release of the Zero Data Loss Recovery Appliance software (19.2.1.1.2) includes 3 new exciting features for replication. 

• Backup Anywhere - Providing the ability to change roles (upstream vs downstream).
• Read Only replication - Providing seamless migration to a different Recovery Appliance.
• Request Only Replication - Providing a High Availability option for backups.

Backup Anywhere

 Backup Anywhere provides even more options for HADR (High Available/Disaster Recovery) with the ability to redirect backups and redo to another Recovery Appliance. In addition, Backup Anywhere provides the ability to perform a role reversal, removing the concept of upstream/downstream.  As the name implies, when replicating between two or more Zero Data Loss Recovery Appliances you can switch the Recovery Appliance that is receiving backups from your protected databases. 

With Backup Anywhere you configure two Recovery Appliance as pairs and create replication servers that point to each other.  The metadata synchronization will ensure backups are replicated to its pair and ensures the Replication Appliance pairs stay in sync.

NOTE: In order to use Backup Anywhere you must use the new REPUSER naming convention of REPUSER_FROM_<source>_TO_<destination>.

For my example, the diagram below depicts a three Zero Data Loss Appliance architecture with the primary databases in New York sending backups to the Recovery Appliance in the New York Data Center,  The Recovery Appliance in the New York Data Center replicates backups to the Recovery Appliance in the London Data Center. And finally, the Recovery Appliance in the London Data Center replicates backups to the Recovery Appliance in Singapore.

New York --> London --> Singapore

But what happens If I want to change which Recovery Appliance I am sending my backups to? With Backup Anywhere I can change the Recovery Appliance receiving backups, and the flow of replicated backups will be taken care of automatically.  With Backup Anywhere the Recover Appliances will seamlessly change the direction of the replication stream based on which Recover Appliance is currently receiving the backups.  Backup Anywhere does this automatically and will still ensure backups on the three Zero Data Loss Appliances are synchronized and available

Singapore --> London --> New York.

 

Read Only Replication

This is my favorite new feature included in the latest Recovery Appliance release. Read Only allows you to easily migrate your backups to a new Recovery Appliance while leaving the older backups still available.

Replication normally synchronizes the upstream catalog with the downstream catalog AND ensures that backups are replicated to the downstream. With Read Only Replication, only the synchronization occurs.  The upstream Recovery Appliance (typically the new RA) knows about the backups on the downstream Recovery Appliance (the old RA).  If a restore is requested that is not on the upstream Recovery Appliance, the upstream will pull the backup from the downstream.

The most common use case is retiring older pieces of equipment, but Read Only Replication can be used for additional use cases.

• Migrating backups to a new datacenter
• Migrating backups for a subset of database from an overloaded Recovery Appliance to a new Recovery Appliance to balance the workload

 Replace older Recovery Appliance

In this example I want to replace the current Recovery Appliance (ZDLRAOLD) with a new Recovery Appliance (ZDLRANEW).  During this transition period I want ensure that backups are always available from the protected database.  This example will show the migration of backups from ZDLRAOLD to ZDLRANEW. I am keeping 30 days of backups for my databases and I am starting the migration on September 1.

Step #1 - September 1, configure replication from ZDLRAOLD to ZDLRANEW

Create a replication server from ZDLRAOLD to ZDLRANEW and add the policy(s) for the databases to the replication server.  This will replicate the most current level 0 backup (FULL)  onto ZDLRANEW for all databases without changing the backup location from the protected databases.

Once you have ensured that all databases have replicated a level 0 backup to ZDLRANEW you can remove the replication server from ZDLRAOLD which will stop the replication.

Step #2 - September 2, configure Read Only replication from ZDLRANEW to ZDLRAOLD

Create a replication server from ZDLRANEW to ZDLRAOLD. Add the policies all databases to the replication server and ensure that the read only flag is set when adding the policy.

 

PROCEDURE add_replication_server (

   replication_server_name IN VARCHAR2,

   protection_policy_name IN VARCHAR2

   skip_initial_replication IN BOOLEAN DEFAULT FALSE,

   read_only IN BOOLEAN DEFAULT FALSE,

   request_only IN BOOLEAN DEFAULT FALSE);

Note: The Read Only flag must be set when adding the policy to the replication server to ensure backups are NOT replicated from ZDLRANEW to ZDLRAOLD.

 




 

Step #3 - September 3, configure backups from the protected databases to backup to ZDLRANEW.

At this point ZDLRANEW should contain at least 1 full backup for all databases, and the incremental backups will begin on September 3rd.  ZDLRANEW will now contain backups from September 1 (when replication began) until the most current Level 0 virtualized backup taken.  ZDLRAOLD will contain backups from August 4 until September 2nd when protected database backups to ZDLRAOLD were moved to be sent to ZDLRANEW.

Step #4 - September 4+, ZDLRANEW contains all new backups and old backups age off ZDLRAOLD

Below is a snapshot of what the backups would look like 15 days later on September 15th.  Backups are aging off of ZDLRAOLD and ZDLRANEW now contains 15 days of backups.

 

Step #5 - September 15, Restore backups

To restore the protected database using a point in time you would connect the protected database to ZDLRANEW and ZDLRANEW would provide the correct virtual full backup regardless of its location.

1.       If the Full backup prior to the point-in-time is on ZDLRANEW it is restored directly from there.

2.     If the Full backup is NOT on ZDLRANEW, it will get pulled from ZDLRAOLD through ZDLRANEW back to the protected database

The location of the backups is transparent to the protected database, and ZDLRANEW manages where to restore the backup from.

Step #6 - September 30  Retire ZDLRAOLD

At this point the new Recovery Appliance ZDLRANEW contains 30 days of backups and the old Recovery Appliance ZDLRAOLD can be retired.

  

Request Only Mode

 

Request Only Mode is used when Data Guard is present and both the Primary database and the Data Guard database are backing up to a local Recovery Appliance. The two Recovery Appliances synchronize only  the metadata, no backup pieces are actively replicated. But, in the event of a prolonged outage of either Recovery Appliance, this features provides the ability to fill gaps by replicating backups from its paired Recovery Appliance. 

To implement this feature, replication servers are configured on both Recovery Appliances, and the policies are added to the replication server specifying REQUEST_ONLY=TRUE.

 

PROCEDURE add_replication_server (

   replication_server_name IN VARCHAR2,

   protection_policy_name IN VARCHAR2

   skip_initial_replication IN BOOLEAN DEFAULT FALSE,

   read_only IN BOOLEAN DEFAULT FALSE,

   request_only IN BOOLEAN DEFAULT FALSE);

Below is my environment that is configured and running in a normal mode. I have my primary database in San Francisco, and my standby database in New York.  Both databases, Primary and Standby are backing up to the local Recovery Appliance in their respective same data center.  Request Only Mode is configured between the two Recovery Appliances.

 

To demonstrate what happens when a failure occurs, I will assume that the Recovery Appliance in the SFO datacenter is down for a period of time.  In this scenario, backups can no longer be sent to the SFO Recovery Appliance, but Data Guard Redo Traffic still occurs to the standby database in New York, and the standby database in New York is still backing up locally to the Recovery Appliance in New York.

When the SFO appliance comes back on-line, it will synchronize the backup information with that on the NYC Recovery Appliance.  The SFO appliance will request datafile backups and any controlfile backups that are older than 48 hours, from NYC appliance.

NOTE: The assumption is that a new backup will occur locally over a faster LAN network and fill any gaps within the last 48 hours. The backups requested from its pair will be transferred over a slower WAN and fill any gaps older than 48 hours

If Real-Time redo is configured, the protected databases will immediately begin the archived log gap fetch process, and fill any gaps in archive logs on SFO appliance that are available on the protected databases. The SFO appliance will also check for new logs to be requested from NYC appliance once per hour over the next 6 hours. This gives time for local arch log gap fetch to run via LAN, which is faster than replicating logs via WAN from NYC.

HADR Bonus Feature: Since the SFO appliance recovery catalog is immediately synchronized with the NYC recovery catalog, backup pieces on the NYC Recovery Appliance are available for recovery.  With this capability you have full recovery protection as soon as the catalog synchronization completes.

 

 

 

This ensures that the SFO Recovery Appliance will be able to provide a short Recovery Point Object without waiting for the next backup job to occur.

All of this happens transparently and quickly returns the Recovery Appliance to the expected level of protection for the database backups.

 

For more details on implementing different replication modes, refer to the Administrator’s Guide.