Cataloging backups and recovering an Oracle Database from the OCI object store

   This is the fourth and final post of a multi-part blog series walking through how to copy your TDE encrypted on premise Oracle Database to an OCI instance in the oracle cloud. This blog post will focus on how to restore your database from the object store, when the backup pieces are not available from your controlfile. 

There a few reasons why this might be the case.

• The backups were written to the ZDLRA directly.
• You are using an RMAN catalog, and they have aged off the controlfile.
• They are "keep" backups which will be stored in the RMAN catalog.
• You had to rebuild the controlfile, and lost history of backups.

Whatever the reason, there is way to find out what backups are in the Object for your database, and you will be able to catalog them.

NOTE: You can use this same script to delete old backups directly if you've lost your catalog entries.

When you download the Oracle Cloud Backup installation zip file, and execute the "oci_install.jar" command to download the library you will find 5 extra files in the /lib directory with the "libopc.so" file that is used by the RMAN channel. The 2 we are going to use are 

• odbsrmt.py             --> python script to manage the contents of the object store bucket
• python_readme.txt --> Documentation for how to use the above python script.

Step #1 Execute odbsrmt.py to get a listing of your backup pieces.

NOTE: The python script uses python 2.x and will not work with python 3.x.  Python 3.x is typically the default version in your path, and you might have to find the 2.x version on your system. For my system this means executing "python2" rather than "python"

If I execute the script without any parameters, I can see what parameters are expected.

[oracle@oracle-19c-test-tde lib]$ python2 odbsrmt.py
usage: odbsrmt.py [-h] --mode
                  {report,rman-listfile,garbage-collection,delete,recall}
                  [--ocitype {classic,swift,bmc,archive}]
                  [--credential CREDENTIAL] [--token TOKEN] --host HOST
                  [--base BASE] [--forcename FORCENAME]
                  [--format {text,xml,json}] [--dbid DBID]
                  [--container CONTAINER] [--dir DIR] [--prefix PREFIX]
                  [--untildate UNTILDATE] [--exclude_deferred]
                  [--thread THREAD] [--proxyhost PROXYHOST]
                  [--proxyport PROXYPORT] [--tocid TOCID] [--uocid UOCID]
                  [--pubfingerprint PUBFINGERPRINT] [--pvtkeyfile PVTKEYFILE]
                  [--skip_check_status] [--debug]
odbsrmt.py: error: argument --mode is required

Now let's go through the most common parameters I am going to use to report on my backups

And now to execute the command to see some of the report.

python2  odbsrmt.py --mode report --ocitype bmc  --host https://objectstorage.us-ashburn-1.oraclecloud.com --dir /home/oracle/ocicloud/report --base mydbreport --pvtkeyfile  /home/oracle/ocicloud/myprivatekey.ppk --pubfingerprint 6d:f9:57:d5:ff:b1:c0:98:81:90:1e:6e:08:0f:d0:69 --tocid ocid1.tenancy.oc1..aaaaaaaanz4trskw6jm57cz2fztoasatto3i6z4h33gzfb3pmei5vvnoq --uocid ocid1.user.oc1..aaaaaaaae2mlwyke4gvd7kzxv5zxgg3k2dlcwvubv7vjy6jvbgsaouxq --container migest_backups  --dbid 301925655

And this will give me the following output in my report file.

FileName
Container                Dbname         Dbid        FileSize          LastModified                BackupType                  Incremental  Compressed   Encrypted
220h9q5f_66_1_1
migest_backups           OCITEST        301925655   72876032          2021-12-21 19:37:33         ArchivedLog                 false        true         true
230h9q5g_67_1_1
migest_backups           OCITEST        301925655   75759616          2021-12-21 19:37:32         ArchivedLog                 false        true         true
240h9q5g_68_1_1
migest_backups           OCITEST        301925655   54263808          2021-12-21 19:37:12         ArchivedLog                 false        true         true
250h9q5g_69_1_1
migest_backups           OCITEST        301925655   48496640          2021-12-21 19:36:58         ArchivedLog                 false        true         true
260h9q9n_70_1_1
migest_backups           OCITEST        301925655   159645696         2021-12-21 19:42:46         Datafile                    true         true         true
270h9q9n_71_1_1
migest_backups           OCITEST        301925655   408682496         2021-12-21 19:47:04         Datafile                    true         true         true
280h9q9n_72_1_1
migest_backups           OCITEST        301925655   524288            2021-12-21 19:37:46         Datafile                    true         true         true
290h9q9n_73_1_1
migest_backups           OCITEST        301925655   56885248          2021-12-21 19:39:37         Datafile                    true         true         true
2a0h9q9v_74_1_1
migest_backups           OCITEST        301925655   235667456         2021-12-21 19:45:05         Datafile                    true         true         true
2b0h9qdi_75_1_1
migest_backups           OCITEST        301925655   233832448         2021-12-21 19:46:18         Datafile                    true         true         true
2c0h9qjb_76_1_1
migest_backups           OCITEST        301925655   52166656          2021-12-21 19:44:31         Datafile                    true         true         true
2d0h9qmk_77_1_1
migest_backups           OCITEST        301925655   1572864           2021-12-21 19:44:43         Datafile                    true         true         true
2e0h9qn3_78_1_1
migest_backups           OCITEST        301925655   34865152          2021-12-21 19:45:41         Datafile                    true         true         true
2f0h9qns_79_1_1
migest_backups           OCITEST        301925655   524288            2021-12-21 19:45:20         Datafile                    true         true         true
2g0h9qrg_80_1_1
migest_backups           OCITEST        301925655   262144            2021-12-21 19:47:14         ArchivedLog                 false        true         true
c-301925655-20211221-00
migest_backups           OCITEST        301925655   524288            2021-12-21 19:47:22         ControlFile SPFILE          false        true         true
Total Storage: 1.34 GB

You can see that this report contains  the backup pieces I need. 

I am going to use the script (below) and pass it the report name to create the commands to catalog the backup pieces.

set linesize 170; #!/bin/bash if [ -f "$1" ] then echo " " echo "report file used for catalog scripts :" $1 echo " " echo " " else echo " " echo "no report file given" echo "you must enter the name of the report file as the first parameter " echo " " exit 0 fi input="$1" while IFS= read -r line do if [[ "$line" != *"true"* ]]; then if [[ "$line" != *"false"* ]]; then if [[ "$line" != *"FileName"* ]]; then if [[ "$line" != *"Container"* ]]; then if [[ "$line" != *"Total"* ]]; then printf '%s\n' "catalog device type 'sbt_tape' backuppiece '${line%"${line##*[![:space:]]}"}';" fi fi fi fi fi done < "$input"

And when I execute the above script passing my report file, it produces my commands to catalog the backup pieces.

report file used for catalog scripts   : mydbreport4701.lst


catalog device type 'sbt_tape' backuppiece '220h9q5f_66_1_1';
catalog device type 'sbt_tape' backuppiece '230h9q5g_67_1_1';
catalog device type 'sbt_tape' backuppiece '240h9q5g_68_1_1';
catalog device type 'sbt_tape' backuppiece '250h9q5g_69_1_1';
catalog device type 'sbt_tape' backuppiece '260h9q9n_70_1_1';
catalog device type 'sbt_tape' backuppiece '270h9q9n_71_1_1';
catalog device type 'sbt_tape' backuppiece '280h9q9n_72_1_1';
catalog device type 'sbt_tape' backuppiece '290h9q9n_73_1_1';
catalog device type 'sbt_tape' backuppiece '2a0h9q9v_74_1_1';
catalog device type 'sbt_tape' backuppiece '2b0h9qdi_75_1_1';
catalog device type 'sbt_tape' backuppiece '2c0h9qjb_76_1_1';
catalog device type 'sbt_tape' backuppiece '2d0h9qmk_77_1_1';
catalog device type 'sbt_tape' backuppiece '2e0h9qn3_78_1_1';
catalog device type 'sbt_tape' backuppiece '2f0h9qns_79_1_1';
catalog device type 'sbt_tape' backuppiece '2g0h9qrg_80_1_1';
catalog device type 'sbt_tape' backuppiece 'c-301925655-20211221-00';

Now in RMAN I can execute these commands to catalog the backup pieces from the OCI bucket.

Note : By using "untildate" you  can control the dates that will be reported on.

Restoring OKV in the Oracle Cloud to manage your encrypted databases

  This is the third of a multi-part blog series walking through how to copy your TDE encrypted on premise Oracle Database to an OCI instance in the oracle cloud. This blog post will focus on how to restore OKV (Oracle Key Vault) into an instance in OCI to manage your encryption keys, and support restoring an encrypted database.

The first part of this series went through how to migrate a database from on premise to an instance in the cloud using the Oracle Database Cloud Backup Module. You can find it here.

The second part of this series went through how to backup OKV to an immutable OCI bucket leveraging ZFSSA. You can find it here.

I will add to this by restoring from my OKV backup into the Oracle Cloud (OCI), and then restoring my database.

I am going to skip over how to migrate  my database to using OKV. If you are starting at the beginning (small database no encryption), the steps to get to this next blog post are.

1. Create a new database for testing.
2. Implement Advanced Security (TDE) which is covered in my post here.
3. Migrating from a local wallet to OKV which is covered in my post here.
4. Backup your database to an OCI bucket encrypted, and compressed.

At this point my database (ocitest),  is using my OKV environment on premise, and I have a backup of both my database, and OKV in Object Storage in the Oracle Cloud.

Create a ZFS Image in OCI to restore OKV from Object Store.

Log into OCI (you can do this with the 30 day trial), and create a new instance using the ZFS image. Below you can see that you can find this image under "Oracle images".

Select this image, upload your public key, and create the new instance.

There are a couple of great step-by-step guides to help you get started with the ZFS image in OCI.

• Overview on the steps to create a usable ZFS image
• ZFS on OCI quick Start Guide with detailed steps

I am not going to go through the process, as those 2 documents are extremely thorough, and will give you the detail needed to configure ZFS with attached storage within OCI.

Create an OKV Image in OCI to restore OKV from Object Store.

The next step to restore OKV is to create an OKV image in OCI.  At this point it is CRITICAL to create an image that is the same version of the source OKV backup.  As of writing this post, I am on 21.2, and I will create a 21.2 instance in OCI.

Again there is great documentation on how to do go through this process.  You need to create a "SYSADMIN" user. Since the users within OKV will get replaced during the install, this user will only be used temporarily.  Below are the links to start with.

• Start page for OCI marketplace
• Youtube video walking through deploying OKC in OCI

NOTE:

• Always deploy the same version in OCI as the backup you are restoring from.
• The command when first logging into the image to configure it may be different from the video, but the login screen will give you clear instructions.

Configure ZFS as a backup location for OKV

At this point if you follow my last blog post found here, you go through the same series of steps in OCI to configure OKV to use ZFS as a backup location that had been done to configure the original backups.

• Create the user on the ZFS image to own the backups
• Log into OKV and save the "public key" for Authentication.
• Configure SFTP on the ZFS image, and add the "Public Key" for the new user.
• Configure the OCI Object Store on the ZFS image as a "cloud target" pointing to the same bucket you had written to.
• Create a new project on the ZFS image with the OKV backup owner as the owner of the project.
• Configure protocols on the new project to ensure that "SFTP" is read/write.

The steps left NOT completed are

• Creating a share within the project
• Creating a backup location within OKV.

Restore the share to the ZFS image in OCI

Now we are ready to restore the backup from the OCI bucket to a share on the ZFS image.

On the ZFS, navigate to "SERVICES" => "Cloud", and within "Cloud" click on the "Backups" tab. Within that tab you will see the ZFS backups that have been sent to the target.

Find the backup that you want, and click on the circular arrow to restore that backup.

This will bring up a popup window where you will choose where to restore the backup to.  Chose the project that you previously created (with the OKV backup user, and "SFTP" protocol enabled"). Give the share a name, and click on "APPLY".

Then once you click on "APPLY" you will see a status popup telling you when it is completed.

When it completes the restore, take note of the share name, and you can configure OKV to restore from this share.

Restore the OKV backup in OCI

Now return to the OKV image in OCI, and navigate to "System" => "Backup and Restore" and create a new backup location, like we had done to create the original backup.

This time enter information for the ZFS image in OCI, and include the destination as "/export/{restored share name}".

Once this is configured click on the "Restore" button, and it will bring up a list of backups that are available to restore from the ZFS share.

Choose the backup you want to use (the backup time will help narrow it down). Click on "Restore" and it will bring up a popup window to enter the "Recovery Passphrase". Enter the passphrase set when OKV was originally installed in your data center, and click on "Restore".

NOTE: The backup is encrypted using the "Recovery Passphrase", and it is critical that you have the original passphrase available to complete this step.

When the restore starts, you will see a message, and OKV will not be available until the restore process completes.

Re-enroll your database  in OCI

Once OKV is restored, the users you created within OKV will be restored. The only items that will be saved are

• root
• support
• "recovery passphrase"

Within OCI where you are restoring your database, you will configure the database environment to start the restore process.  I started by creating a pfile, and some of the directories needed.

audit_file_dest='/u01/app/oracle/admin/ocitest/adump'
audit_trail='db'
compatible='19.0.0'
control_files='/u01/app/oracle/oradata/OCITEST/controlfile/o1_mf_jo6q53rf_.ctl''
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_name='ocitest'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=32212254720
diagnostic_dest='/u01/app/oracle'
enable_pluggable_database=true
pga_aggregate_target=1547m
processes=300
sga_target=4638m
tde_configuration='KEYSTORE_CONFIGURATION=OKV|FILE'
undo_tablespace='UNDOTBS1'
wallet_root='/u01/app/wallets/ocitest'

NOTE: Since you need OKV to decrypt the RMAN backup of the controlfile, you need to ensure the pfile contains the "WALLET_ROOT" and "TDE_COFIGURATION". 

Within OKV I re-enrolled the endpoint for my database, and I downloaded and installed the "okvclient.jar" in  the "WALLET_ROOT"/okv location.

Now to restore my database, I can use a script, like the script below to

• Startup nomount
• Open the wallet pointing to my keys in OKV
• Set the DBID
• Allocate the channel
• Restore the controlfile
• Mount the database.

sqlplus / as sysdba
SQL> startup nomount;
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "0KV2021!";
SQL> exit


rman target /
RMAN> set dbid=301925655;
RMAN> run {
RMAN> allocate CHANNEL c1 TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/ociconfig/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/ociconfig/config/ocitestbackup.ora)';
RMAN> restore controlfile from autobackup ;
RMAN> release channel c1;
RMAN> }
RMAN> alter database mount;

Once mounted, I can follow the normal steps to restore my database, and my encryption keys are available.  The backup information for my OCI bucket is in my controlfile.

Backing up Oracle Key Vault from your datacenter to OCI

  This is the second of a multi-part blog series walking through how to copy your TDE encrypted on premise Oracle Database to an OCI VM in the oracle cloud. This blog post will focus on how to leverage OKV (Oracle Key Vault) to help with storing, backing up, and migrating encryption keys. In this post I will walk through backing up OKV to both a local ZFS, and an OCI bucket.

The first part of this series went through how to migrate a database from on premise to the OCI cloud using the Oracle Database Cloud Backup Module. You can find it here.

I will add to this first by including how to migrate my OKV (Oracle Key Vault) environment to OCI to allow me to restore my encrypted database in OKV.

I am going to skip over how to migrate migrate my database to using OKV. If you are starting at the beginning (small database no encryption), the steps to get to this next blog post are.

1. Create a new database for testing.
2. Implement Advanced Security (TDE) which is covered in my post here.
3. Migrating from a local wallet to OKV which is covered in my post here.

At this point my database (ocitest),  is using my OKV environment on premise, and I am ready to backup and restore my OKV host along with my database.

Backup the database to an OCI bucket

First I am going to back my database up to an OCI bucket.

I am going to set my channels and perform a level 0 backup with archive logs.

NOTE: It is encrypted using the encryption key from OKV, rather than a password.

### Default device is tape
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';


### Ensure autobackups are sent to my bucket
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default


### Backup set is a compressed backupset (this is included for free with the backup module)
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

### Channel configuration
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/ocicloud/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/ocicloud/config/migtestbackup.ora)';

### Encryption is on
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

### Compression is medium
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

Configure ZFSSA as a destination

Step # 1 Add a dedicated user on the ZFSSA to own the backups.

Log onto the ZFSSA console and go to "Configuration" => "Users". Add a new user to be the owner of the OKV backups on the ZFSSA.  Click add when completed.

Step # 2 Retrieve the public SSH key from OKV.

Log onto the OKV console and go to "system" => "Settings" ==> "Backup and Restore". Click on "Backup and Restore" and then "Manage Backup destinations".  Once there click on "Create" to add a new backup destination.

On the screen below you want capture the "Public Key", which is the strings AFTER the "ssh-rsa".  You can save this in notepad, or some sort of scratch pad without the beginning line.

Step # 3 Add the user to the ZFSSA with the public key

Now go back to the ZFFSA console, and log into the configuration for SFTP. This can be under "SERVICES" => "SFTP". Click on "SFTP", and you will see the screen in the background below.  Click on the "+" to the left of "Keys".  On the window that pops up you will enter the "Public Key" characters you previously saved, and the "user" that you created as the owner of the OKV backups. Once you complete this click on "ADD" to add the OKV Public Key to the ZFSSA.

Step # 4 Add a new Project/Share to hold the OKV backups

Add a new project to hold the backups.  With the project, navigate to the "General" tab, and go to to bottom of the window and change the "Default Settings".  For this project, the "user" should be the user you created on the ZFS. This ensures that the OKV backups are separate from other backups on the ZFS, and are only accessible by the new users.

Then set the protocol for the project to be SFTP as the only read/write protocol on the "Protocols" tab.

Navigate to the "Snapshots" tab and we will now create 3 immutable snapshots taken every day.

Ensure you click on "Enable retention policy for Scheduled Snapshots"

Under the Snapshots section, click on the “Schedules” tab and click on the “+” next to it.

• Change the desired frequency of the snapshot to daily for a daily backup that matches the OKV backup.

• Change the “scheduled time” to a time of day following the daily backup.

• Decide how many backups in total you wish to keep. This is the “KEEP AT MOST”.

• Change the “RETENTION” to “Locked” with the drop down to ensure the backups will be immutable:

• Decide how many backups you wish to keep as immutable. This is the “RETENTION”.

Click on “Apply”.

And then add a new share to the project to backups.

Step # 5 Add the ZFSSA as a destination.

Go back to OKV and navigate back to the “Create Backup Destination” under “System”.

On the “create Backup Destination” page 

• give the “Destination Name” the name you want to use for the ZFS.  

• Change “Transfer Method” to “sftp” using the radio button.

• Enter the “Hostname” for the ZFS. This can be either the IP or the DNS name.

• Under the “Port” ensure the port matches the ZFS port used for “SFTP”, which defaults to 218.

• Enter the “Destination Path” which is “/export/” followed by the share name given in step 021.

• Enter the “User Name” which is the user created in step 006 and the user that owns the share from step 021.

Click on “Save”

Backup OKV to  ZFSSA 

With the “Backup” screen

• Give your backup a descriptive name

• Leave the start time (or change it to the time to run the backup).

• Choose the destination entered in step 022

• Change the dial to “PERIODIC” to schedule a regular backup

• Chose the frequency for the backup

Click on Schedule.

Once the first backup completes you will see it on this "Backup and Restore" window.

Backup   ZFSSA to OCI

 

Now that we have our backup sent to the ZFSSA, we need to configure the ZFSSA to send the backup to an OCI bucket.  Navigate to "SERVICES" => "Cloud" on the ZFSSA, and click on the "+" sign to the left of "Targets" top add a new cloud target.  On the window that pops up, enter the authentication information for your cloud bucket in OCI (It should be set as immutable). 

In the “Add Cloud Target” window enter.

• Name of the cloud target, if you are setting up multiple targets to different buckets having the bucket name is most descriptive

• The location is https://objectstorage.{cloud location for your tenancy and bucket}.oraclecloud.com

• Bucket name from the previous step.

• “User” which is the user OCID from the previous steps

• “Tenancy” which is the Tenancy OCID from the previous steps

• “Private key” associated with the public key assigned with OCI.

• Any proxy information and bandwidth information if needed.

Click on “ADD”.

Navigate to your project, and go to the "Snapshots" tab. You should see the snapshots that have been created and click on the Symbol under clones that looks like a globe.

Once there, choose the target you previously created, and send the backup as "tar" format. and click on "APPLY", this will send a copy of your OKV backup (which is encrypted) to your bucket in OCI as an offsite backup.

Backing up your database to a bucket in OCI and restoring it in OCI

 This is the first of a multi-part blog series walking through how to copy your TDE encrypted on premise Oracle Database to an OCI VM in the oracle cloud using the Oracle Database Backup Cloud Service. 

I am going to start with a simple test case of a small database which doesn't contain any TDE encryption or wallet, and back it up to an OCI bucket.

As far as where to start, below are some documentation links that will help.

• Cloud Backup Documentation
• Download for Cloud Database Backup Service
• Immutable buckets in OCI

NOTE: You will be doing downloading, and installing the library files on both the source database and the destination database.

Install Database backup module

The first thing I am going to do is unzip the Cloud Backup Module (opc_installer.zip).  This can downloaded using the link above, but it can also be found within the $ORACLE_HOME/lib  directory.  As always, I would recommend downloading the current copy to be sure it is the latest release.   Once unzipped you will find the module contains a directory "opc_installer".  Within "opc_installer" there are 2 subdirectories with a ".jar" file to install the library, and a readme file.

    oci_installer/                                                  ---> OCI (Oracle Cloud Native) library install
                       oci_install.jar

                       oci_readme.txt

    opc_installer/                                                  ---> OPC (Oracle Cloud Gen 1/swift) library install

                       opc_install.jar

                       opc_readme.txt

I am going to use "oci_install.jar" file and access the bucket using the Oracle Cloud Native API.

If I look in the "readme" file, I can see that I install the library using the following parameters.

I am going to install my files within a new directory for my Database host.

/home/oracle/ocicloud/

                                    config/

                                    lib/
                                    wallet/

To install and configure my library I am going to execute

java -jar oci_install.jar
         -host https://objectstorage.us-ashburn-1.oraclecloud.com  
        -pvtkeyFile  /home/oracle/ocicloud/myprivatekey.ppk 
        -pubFingerPrint 6d:f9:57:d5:ff:b1:c0:98:81:90:1e:6e:08:0f:d0:69 
        -tOCID ocid1.tenancy.oc1..aaaxxxnoq 
        -uOCID ocid1.user.oc1..aaaaaaaae2mlwyke4gvxxsaouxq 
        -bucket migest_backups  
        -walletDir /home/oracle/ocicloud/wallet 
        -configFile /home/oracle/ocicloud/config/migtestbackup.ora 
         -cOCID ocid1.compartment.oc1..aaaaaaaanqbquh2bwju4igzd3tyq 
        -libDir /home/oracle/ocicloud/lib  

Oracle Database Cloud Backup Module Install Tool, build MAIN_2021-08-31

Oracle Database Cloud Backup Module credentials are valid.

Backups would be sent to bucket migest_backups.

Oracle Database Cloud Backup Module wallet created in directory /home/oracle/ocicloud/wallet.

Oracle Database Cloud Backup Module initialization file /home/oracle/ocicloud/config/migtestbackup.ora created.

Downloading Oracle Database Cloud Backup Module Software Library from Oracle Cloud Infrastructure.

Download complete.

Now that it is successfully installed we can go to configuring the module.

Configure Database backup module

Running the command below, lets see what is in my directory now.

find . | sed -e "s/[^-][^\/]*\// |/g" -e "s/|\([^ ]\)/|-\1/"

.
 |-lib
 | |-bulkimport.pl
 | |-libopc.so
 | |-metadata.xml
 | |-odbsrmt.py
 | |-perl_readme.txt
 | |-python_readme.txt
 |-config
 | |-migtestbackup.ora
 |-wallet
 | |-cwallet.sso.lck
 | |-cwallet.sso
 |-oci_install.jar
 |-myprivatekey.ppk

Looking at the configuration file created you can see the information used to connect to the bucket in the OCI Object store.

OPC_HOST=https://objectstorage.us-ashburn-1.oraclecloud.com/n/id20avsofo
OPC_WALLET='LOCATION=file:/home/oracle/ocicloud/wallet CREDENTIAL_ALIAS=alias_oci'
OPC_CONTAINER=migest_backups
OPC_COMPARTMENT_ID=ocid1.compartment.oc1..aaaaaaaanqbquh2bwju4igabu5g7xuiijael2fwcpqyvzzb4ykd3tyq
OPC_AUTH_SCHEME=BMC

Now we can create the channel configuration to send backups to the oci bucket.

The create channel would be executed like the command below filling in the library and configuration file

CONFIGURE CHANNEL DEVICE TYPE 

        'SBT_TAPE' PARMS 

         'SBT_LIBRARY={library name and location},

                SBT_PARMS=(OPC_PFILE=/{configuration file})';

Below are the commands I am going to execute in RMAN to configure my channel and settings to backup my database.

## Default device type is tape
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; 

## Backup using the library and config file we just installed
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/ocicloud/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/ocicloud/config/migtestbackup.ora)';

## Backup with 4 channels to a compressed backupset
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

## Use medium compression since this is included in the license for the module.
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

## Encrypt the backup being sent, this is mandatory for writing to the cloud.
CONFIGURE ENCRYPTION FOR DATABASE ON;

Backup Database to an OCI bucket

Set a password to encrypt the backup (it must be encrypted to send to a bucket) and perform a full backup.

set encryption identified by oracle only;
backup incremental level 0 database plus archivelog not backed up;

This will send the backup to the object store

Configure Database backup module in OCI.

I am going to go through the same series of steps to install the Oracle Database Cloud backup Module in my OCI instance. 

Oracle Database Cloud Backup Module Install Tool, build MAIN_2021-08-31
Oracle Database Cloud Backup Module credentials are valid.
Backups would be sent to bucket migest_backups.
Oracle Database Cloud Backup Module wallet created in directory /home/oracle/ocicloud/wallet.
Oracle Database Cloud Backup Module initialization file /home/oracle/ocicloud/config/migtestbackup.ora created.
Downloading Oracle Database Cloud Backup Module Software Library from Oracle Cloud Infrastructure.
Download complete.

Configure pfile for database in OCI.

I now need to configure my database pfile in OCI. I just need a few basic things 

audit_file_dest='/u01/app/oracle/admin/migtest/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/MIGTEST/controlfile/controlfile1.ctl','/u01/app/oracle/oradata/MIGTEST/controlfile/controlfile2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='migtest'
*.processes=300
*.sga_target=4638m

Restore pfile and controlfile for database in OCI

There a few steps to get ready to restore the spile and controlfile

• I add my database to the "/etc/oratab" to ensure I can connect to it, and ". oraenv" to set the environment.
• I now start up the database nomount
• I go back to the original database to retrieve the dbid.

Now I am ready to restore the spfile (note that I am setting the password to decrypt the backups).

In RMAN I restore the spfile

set decryption identified by oracle;

 run {
 allocate CHANNEL c1 TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/ocicloud/ib/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/ocicloud/config/migtestbackup.or)';
 restore spfile from autobackup ;
 release channel c1;
 }

rman target /
RMAN> set decryption identified by oracle;

executing command: SET decryption
 run {
 allocate CHANNEL c1 TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/ocicloud/ib/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/ocicloud/config/migtestbackup.or)';
 restore spfile from autobackup ;
 release channel c1;
 }


RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=20 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=21.0.0.1

Starting restore at 20-DEC-21

channel c1: looking for AUTOBACKUP on day: 20211220
channel c1: AUTOBACKUP found: c-286701374-20211220-00
channel c1: restoring spfile from AUTOBACKUP c-286701374-20211220-00
channel c1: SPFILE restore from AUTOBACKUP complete
Finished restore at 20-DEC-21

Then I restore the controlfile.

set decryption identified by oracle;

 run {
 allocate CHANNEL c1 TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/ocicloud/ib/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/ocicloud/config/migtestbackup.or)';
 restore controlfile from autobackup ;
 release channel c1;
 }

RMAN>
executing command: SET decryption
using target database control file instead of recovery catalog


RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=17 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=21.0.0.1

Starting restore at 20-DEC-21

channel c1: looking for AUTOBACKUP on day: 20211220
channel c1: AUTOBACKUP found: c-286701374-20211220-00
channel c1: restoring control file from AUTOBACKUP c-286701374-20211220-00
channel c1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/MIGTEST/controlfile/controlfile1.ctl
output file name=/u01/app/oracle/oradata/MIGTEST/controlfile/controlfile2.ctl
Finished restore at 20-DEC-21

released channel: c1

Now I can mount the database

Restore the datafile for the database in OCI

Since the location in OCI is different.

My on-premise database  "/home/oracle/app/oracle/oradata/"

My OCI database  "/u01/app/oracle/oradata/"

I am going to create a script to set newname from my datafiles to restore to.

set linesize 160
set pagesize 0

SELECT REPLACE(file_name,'/home/oracle/app/oracle/oradata/','/u01/app/oracle/oradata/') "Changes"
     FROM (select
           'set newname for datafile ' || file# || ' to ' || '''' || name || '''' || ';' file_name
           from v$datafile
           )
;

Which will create the script that sets "new name for my datafiles"

I just need to execute in RMAN within a run block.

run {

set newname ....

  }

Now I configure the channels just like I did in the for my on premise (unless they are are already set from the controlfile).

## Default device type is tape
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; 

## Backup using the library and config file we just installed
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/home/oracle/ocicloud/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/home/oracle/ocicloud/config/migtestbackup.ora)';

## Backup with 4 channels to a compressed backupset
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

## Use medium compression since this is included in the license for the module.
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

## Encrypt the backup being sent, this is mandatory for writing to the cloud.
CONFIGURE ENCRYPTION FOR DATABASE ON;

Now we can restore and recover the database and switch to the new copy of the datafiles.

run {
  restore database; 
  recovery database;
  switch datafile all;
    }

And finally (if we want to start it up) open it resetlogs.

RMAN> alter database open resetlogs;

Statement processed

RMAN>

That's all there is to it.

ZFSSA now offers immutable snapshots

 The latest ZFSSA software release (as of this post) is OS8.8.39. 

 This release contains the ability to make both scheduled snapshots and manual snapshots immutable, and I will go through how this works in this post.

New Authorizations

By default non-root users are not authorized to create scheduled locked snapshots, or manual locked snapshots and you will see the message below.

There are 3 new authorizations added to support Snapshot immutability.  The authorizations are

• releaseSnapRetention - This allows the role to release a snapshot from it's retention hold
• scheduleLockedSnap - This allows the role to schedule a locked snapshot
• retainSnap                   - This allows the role to create a manual locked snapshot

In order to show how this works I created a new role "Security_Admin" and granted this role the new authorizations.

You can see that the "Security_Admin" role has releaseSnapRetention, scheduleLockedSnap and retainSnap authorizations which reside under the "Projects and shares" scope.

I then added the new role "Security_Admin" to my administration user.  This limits who has the authority to create and change the status on the immutable snapshots.

Create a Manual Locked Snapshot (BUI) 

First I am going to create a manual locked snapshot.  Below is the window that appears when I click on the "+" to create the snapshot.

Notice below the name there is a new field "Retention policy". This can be either

• Off           - There is no retention on this snapshot (normal)
• Unlocked - There is a locked retention on this snapshot 

I am going to create my manual snapshot with an "unlocked" retention policy

Change the retention setting of a Snapshot (BUI) 

Once I create the manual snapshot, I can see that it has an "unlocked" retention when I click on the edit button.  Here I can update the snapshot and turn the retention policy to "Off" to unlock the snapshot when I am ready to delete it. I can also change the status of a snapshot without a retention to have a retention policy.

Create a Manual Locked Snapshot (CLI) 

1) Navigate to the share or project you want to snapshot.

zfssim:shares NFSbackups> select NFS_immutable
zfssim:shares NFSbackups/NFS_immutable>

 

 2) Enter snapshots

zfssim:shares NFSbackups> snapshots
zfssim:shares NFSbackups/NFS_immutable snapshots>

3) Use the snapshot command followed by a "-r" to set the retention lock, and set the new snapshot name

zfssim:shares NFSbackups/NFS_immutable snapshots> snapshot -r Save_until_Jan_1_2022
zfssim:shares NFSbackups/NFS_immutable snapshots>

4) You can use the list command to see the snapshot, and then select the snapshot

zfssim:shares objectstore> select rmanbackups
zfssim:shares objectstore/rmanbackups>

5) The "show" command will display the settings for the snapshot, and you will see that has a retentionpolicy of "unlocked"

zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022> show
Properties:
                      creation = Tue Nov 16 2021 20:35:25 GMT+0000 (UTC)
                     numclones = 0
                        isauto = false
               retentionpolicy = unlocked
                          pool = generalpool1
                canonical_name = generalpool1/local/NFSbackups/NFS_immutable@Save_until_Jan_1_2022
                    shadowsnap = false
                  space_unique = 0
                    space_data = 31K

Change the retention setting of a Snapshot (CLI) 

Continuing from the previous set of commands, with the "show" I can see the status of the retention lock.

Using the "set retentionpolicy={off | unlocked}" you can change the status of a snapshot.

Below is the example when I turned the retention policy to off for the snapshot I took in the prior example.

zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022> get retentionpolicy
               retentionpolicy = unlocked
zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022> set retentionpolicy=off
               retentionpolicy = off (uncommitted)
zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022> get retentionpolicy
               retentionpolicy = off (uncommitted)
zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022> commit
zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022> show
Properties:
                      creation = Tue Nov 16 2021 20:35:25 GMT+0000 (UTC)
                     numclones = 0
                        isauto = false
               retentionpolicy = off
                          pool = generalpool1
                canonical_name = generalpool1/local/NFSbackups/NFS_immutable@Save_until_Jan_1_2022
                    shadowsnap = false
                  space_unique = 0
                    space_data = 31K

Children:
                          backups => Configure Cloud Backups
                          targets => List snapshot parents per target

zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022>

Deleting a Manual Locked Snapshot

BUI 

When you delete a manual snapshot that has a retention policy, you will receive an error screen once you click through the "are you sure" message. Below is the message that will appear if the snapshot still has a retention lock.

In order to allow the snapshot to be deleted, you need to edit the snapshot, and set the retention to "Off".  Once you remove the retention lock the snapshot can be deleted.

CLI

You will receive an error when trying to delete the snapshot. You must release the lock (or in the case of a schedule snapshot) wait for it to roll off.

zfssim:shares NFSbackups/NFS_immutable@Save_until_Jan_1_2022> destroy
This will destroy all data in "Save_until_Jan_1_2022"! Are you sure? (Y/N) y
error: The action could not be completed because the target 'NFSbackups/NFS_immutable@Save_until_Jan_1_2022' is in use. It cannot be modified while it, or its children, are actively changing. Make sure no other users are editing the
       share configuration and try again. If this problem persists, contact your service provider.

Enable Scheduled Locked Snapshots (BUI)

The next step is to enable scheduled locked snapshots. You will notice (highlighted below) that there is a new option to enable the retention policy for locked scheduled snapshots under the project and share.

shares --> share/project --> snapshots

Create Scheduled Locked Snapshots (BUI)

To create a schedule snapshot that is locked, you will noticed there are  addition fields on the scheduling popup.  You have the ability to schedule a snapshot with retention either "Off" or "Locked".  When schedule with "Locked" you must also decide on how many of the "kept" snapshots will be locked. Below I am scheduling snapshots every half hour.  5 snapshots will be kept, and the most recent 3 snapshots will be locked (since I chose locked).

Viewing retention status of scheduled snapshots (BUI)

Using the schedule from above (5 snapshots, 3 of which are locked), below is what I am seeing after it has been executing for awhile.  I chose one of the 3 most recent snapshots and I can see that it has a status of "locked" and I am unable to change that status.

Deleting locked scheduled snapshots (BUI) - Not allowed

Now I am going to try to delete the schedule that contains locked snapshots.  I click on the delete button and hit apply. I get a message saying the snaps will be converted to manual snapshots.

I click on "CONVERT" but it won't let me convert them to manual snapshots.

If I try to change the schedule to have the snapshots no longer be "Locked", I get the same message.

Enable Scheduled Locked Snapshots (CLI)

I navigated through the CLI and got to the share that I wanted to create a scheduled, locked snapshot for.  I first need to make sure the property "snap" is set. In my case it wasn't so I set the value and committed the change.

zfssim:shares NFSbackups/nfstest> get snapret_enabled
               snapret_enabled = false (inherited)
zfssim:shares NFSbackups/nfstest> set snapret_enabled=true
               snapret_enabled = true (uncommitted)
zfssim:shares NFSbackups/nfstest> commit
zfssim:shares NFSbackups/nfstest> get snapret_enabled
               snapret_enabled = true
zfssim:shares NFSbackups/nfstest>

Create Schedule Locked Snapshots (CLI)

Navigate to the share --> snapshots --> automatic

Once there create a new snapshot, and set the properties for the snapshot.

In order to make this a locked snapshot, you need to set the property "retentionpolicy" to "locked".

Below is the steps I followed to create a daily snapshot, kept for 35 days, and immutable for 30 days.

 zfssim:shares NFSbackups/nfstest>
zfssim:shares NFSbackups/nfstest> snapshots
zfssim:shares NFSbackups/nfstest snapshots> automatic
zfssim:shares NFSbackups/nfstest snapshots automatic> create
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> show
Properties:
                     frequency = (unset)
                           day = (unset)
                          hour = (unset)
                        minute = (unset)
                          keep = 0
                 retentionhold = 0
               retentionpolicy = off

zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> set frequency=day
                     frequency = day (uncommitted)
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> set hour=06
                          hour = 06 (uncommitted)
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> set minute=00
                        minute = 00 (uncommitted)
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> set keep=35
                          keep = 35 (uncommitted)
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> set retentionhold=30
                 retentionhold = 30 (uncommitted)
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> set retentionpolicy=locked
               retentionpolicy = locked (uncommitted)
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> show
Properties:
                     frequency = day (uncommitted)
                           day = (unset)
                          hour = 06 (uncommitted)
                        minute = 00 (uncommitted)
                          keep = 35 (uncommitted)
                 retentionhold = 30 (uncommitted)
               retentionpolicy = locked (uncommitted)

zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)>
zfssim:shares NFSbackups/nfstest snapshots automatic-000 (uncommitted)> commit
zfssim:shares NFSbackups/nfstest snapshots automatic> show
Properties:
                       convert = false

Automatics:

NAME           FREQUENCY      DAY             HH:MM KEEP
automatic-000  day            -               06:00   35


 

Viewing retention status of scheduled snapshots (CLI)

Below I listed out the snapshots that were automatically created. I can see that the snapshot chose has a "retentionpolicy" of "locked" and this lock will be removed according to the schedule.

zfssim:shares NFSbackups/zfsshare> snapshots
zfssim:shares NFSbackups/zfsshare snapshots> list
.auto-Bihourly_snapshots-20211116T193000UTC
.auto-Bihourly_snapshots-20211116T200000UTC
.auto-Bihourly_snapshots-20211116T203000UTC
.auto-Bihourly_snapshots-20211116T210000UTC
.auto-Bihourly_snapshots-20211116T213000UTC
zfssim:shares NFSbackups/zfsshare snapshots> select .auto-Bihourly_snapshots-20211116T203000UTC
zfssim:shares NFSbackups/zfsshare@.auto-Bihourly_snapshots-20211116T203000UTC> show
Properties:
                      creation = Tue Nov 16 2021 20:30:00 GMT+0000 (UTC)
                     numclones = 0
                        isauto = true
               retentionpolicy = locked
                          pool = generalpool1
                canonical_name = generalpool1/local/NFSbackups/zfsshare@.auto-Bihourly_snapshots-20211116T203000UTC
                    shadowsnap = false
                  space_unique = 0
                    space_data = 1.22G

Children:
                          backups => Configure Cloud Backups
                          targets => List snapshot parents per target

zfssim:shares NFSbackups/zfsshare@.auto-Bihourly_snapshots-20211116T203000UTC>

BONUS : 

In the audit logs you can see the changes occur, and who made them.. I highlighted where I changed status of one of the shares from Unlocked to OFF and from Off to Unlocked.