Expert insights on Oracle Database, Zero Data Loss Recovery Appliance (ZDLRA), backup and recovery strategies, database security, and enterprise database solutions by Bryan Grenn.
When I wrote my last blog on listing the Long Term Backups created by Autonomous Recovery Service, I didn't go through the process of how to dynamically create a new backup.
Below is how you create a long term backup in the console, but most customers want to automate this process.
The oci cli command you would use to create a new backup is "oci db backup create".
In order to create an end-of-month backup with a restore point as of midnight what I recommend customers do is
Ensure you have a nightly backup that runs late at night (about 22:00) and will finish by midnight on a regular basis.
Schedule the automatic long term backup creation to occur about 00:45 on the next day. This ensures that a log sweep has occurred. For BaseDB you would wait until after the next hour. If you have enabled the zero data loss feature (real-time redo), you want to make sure that the ARCHIVE_LAG_TARGET is set to 30 minutes or less and forcing a periodic log switch.
This ensures you are creating a long term backup with minimal archive logs to defuzzy the backup.
Command inputs
The easiest way to determine the input for this command is to use the --generate-full-command-json-input option.
oci db backup create --generate-full-command-json-input
What is returned is the JSON example below showing you what parameters need to be filled in to create the backup.
databaseID : This is the OCID for the database that you want to create the long term backup for.
displayName: This is the name to identify the backup from a listing and would match the name I would put in the GUI.
Wait for state of command (Optional)
waitForState: Since the backup can take awhile to run, you can have the command wait to return until a specified state (or any one of a list of states) occurs. When creating a new backup the valid states would be
ACTIVE
CANCELED
CANCELING
CREATING
FAILED
maxWaitSeconds: How long to wait between state checks when waiting for a state to occur.
Example of wait
oci db backup create --waitForState CREATING --waitForState CANCELED
Would wait for the backup to start to be created or canceled before returning.
Retention (mandatory for long term backups and must be greater than 90 days and less than 10 years)
You would enter the number of days you want to keep backups for (retentionDays), or you would enter the number of years (retentionYears) , but not both.
Below is an example JSON file that would create a new long term backup named "bsgtest" and keep the backup for 100 days.
One of the unique features of the Autonomous Recovery Service (RCV) is the ability to create Long Term Backups by using existing backups that are currently stored in RCV.
NOTE:Long term backups, also known as "Keep" backups are self contained backups that provide the ability to restore to a small predetermined point-in-time window. These long term backups are often stored for months, or even years and are typically used for auditing purposes.
These backups are created dynamically outside of the database itself and the DB host is not used.
Because the DB host is bypassed, the normal backup listings on the DB host using the DBAASCLI tool do not see long term backups.
Viewing backups with OCI
All of the backups can be viewed in both the OCI Console and by using the OCI CLI tool.
In this blog, I will describe how you can use the OCI Cli tool to view all of the backups.
The command I am utilizing to display backups is
oci db backup list
Unfortunately, the output from this command is JSON objects which can be difficult to read if you want to produce a report.
In this blog, I show examples leveraging JMESPath queries via the --query flag.
The Foundation: Listing Database Backups
The baseline command to list backups for a specific database requires the --database-id (OCID). By default, we want to output this as a table, grab all records across pages using the --all flag, and project key fields like Shape and Type:
In this example, I am limiting the command to extract only daily backups (no long term backups), sort by the execution time, and return only the first record.
This command can be also be used in any scripting to ensure you are cloning from the most recent backup.
Executing this slice pattern evaluates down to a single, isolated record representing your absolute most recent backup:
+------------------+-------------------+-------------------------------------------------------------------------------------+-------------+--------+----------------------------------+-------------+-------------+
| Backup_Name | Database_size_GBs | OCID | Shape | Status | Time_Started | Type | Version |
+------------------+-------------------+-------------------------------------------------------------------------------------+-------------+--------+----------------------------------+-------------+-------------+
| Automatic Backup | 24.01171875 | ocid1.dbbackup.oc1.phx.anyhqljtbv6267iaufuy74ggl2bvf66czd75x3v4366qrwc............. | Exadata.X8M | ACTIVE | 2026-05-19T12:04:12.917000+00:00 | INCREMENTAL | 19.26.0.0.0 |
+------------------+-------------------+-------------------------------------------------------------------------------------+-------------+--------+----------------------------------+-------------+-------------+
Finding all of the long term backups
This command will display only backups that were creating with a future expiration date. This can be used to view all of the long term backups that were created for this database.
One of the questions I often get from customers is
"How do I automate the cloning of a production database backup to a non-prod copy? This is something we do often."
There are three different OCI commands to do what seems like the exact same thing. Specifically, when it comes to restoring a database from a backup, the OCI CLI gives us three primary paths.
The "secret sauce" to choosing the right command is understanding where the database is going and ensuring you have the right OCIDs for your target infrastructure. Let's break down the full parameter sets you need to keep your automation from failing.
These are all "oci db database " commands
The Restore Matrix: Choosing Your Command
Command
Infrastructure Target
Required Target ID
Primary Use Case
create-database-from-backup
Exadata / C@C
--db-home-id
Restoring into an existing Exadata Home.
create-from-backup
Base DB (VM/BM)
--db-system-id
Adding a DB to an existing DB System.
create --source DB_BACKUP
Base DB (VM/BM)
--compartment-id
Building a NEW DB System from a backup.
1. The Exadata Full Set: create-database-from-backup
This command uses a JSON object for the --database flag. This is where you define the identity of the clone within the Exadata rack along with the backup you want to use to create the new database.
This is for standard Virtual Machine shapes. You must provide the dbSystemId (the OCID of the VM) to tell OCI exactly where to deploy the restored data.
This is the "All-In-One" command. It creates the VM Cluster or DB System infrastructure from scratch. Because of this, it requires networking IDs (VCN/Subnet) and hardware shapes.
Exadata: You must have the --db-home-id of an existing home on the rack.
VM In-Place: You need the --db-system-id of the running VM instance.
Identity: Every command requires a dbName (8 chars max) and dbUniqueName. For automation, use the sidPrefix to prevent instance ID collisions.
Example from my tenancy
This example shows the command I am using in my tenancy to clone a database
{
oci db database create \
--config-file /home/opc/clone/config \ #--> My OCI authentication config file
--profile DEFAULT \ #--> Entry in the config file to use credentials for
--region us-phoenix-1 \ #--> Region I am connecting to execute (source region)
--source DB_BACKUP \ #--> Source for the new database is a DB_BACKUP
--db-home-id ocid1.dbhome.{...} \ #--> Target home OCID to create new DB
--vm-cluster-id ocid1.cloudvmcluster.{...} \ #--> Target VM OCID to create the new DB in
--admin-password "$_ADMIN_PW" \ #--> Target DB admin password when creating
--from-json file:///{file location}/xx.json #--> JSON input file
}
Example from my tenancy (cont)
This is the contents of the .json input file
{
"source": "DB_BACKUP", #--> Source is a DB_BACKUP
"dbHomeId": "ocid1.dbhome.{...}", #--> Target DB Home OCID
"database": {
"backupId": "ocid1.dbbackup.{...}", #--> Backup OCID to create database from
"dbName": "BGRENNC", #--> New DB name
"dbUniqueName": "bgrennc_clone", #--> New DB Unique name
"adminPassword": "dd", #--> New DB Admin password (new TDE password will be the same)
"backupTDEPassword": "dd", #--> Original TDE wallet password
"dbBackupConfig": {
"autoBackupEnabled": true, #--> Configure automatic backups
"recoveryWindowInDays": 30 #--> Set recovery window for new backups
},
"definedTags": {
"Oracle-Tags": {
"CostType": "Shared"
}
}
}
}
Mastering TDE & Key Management
One of the biggest hurdles in database cloning is handling the Transparent Data Encryption (TDE) layer. If your source backup was encrypted using a key from a different cloud provider or a local HSM, you must tell OCI how to decrypt it during the restore process.
1. Cross-Cloud & External Key Providers
When using the create-database-from-backup command (Exadata), you use the sourceEncryptionKeyLocationDetails parameter. This is a JSON object where you must specify the providerType and the corresponding Key OCID or ID from the source provider.
Provider Type
Parameter Required
Description
AWS
awsEncryptionKeyId
The ARN of the AWS KMS key used on the source.
AZURE
azureEncryptionKeyId
The Azure Key Vault key URI.
GCP
googleCloudProviderEncryptionKeyId
The fully qualified resource name of the GCP KMS key.
EXTERNAL
hsmPassword
Used for backups protected by an on-premises Hardware Security Module.
2. Native OCI Vault Integration
For native OCI restores, you have two choices: use the standard Oracle-managed keys (default) or use your own keys via OCI Vault (KMS). If you want to use your own keys, you must provide the kmsKeyId and, in some cases, the vaultId.
kmsKeyId: The OCID of the Master Encryption Key in the OCI Vault.
kmsKeyVersionId: (Optional) Use this if you need to pin the restore to a specific version of your key.
vaultId: Required by the create command to identify which Vault the key resides in.
Important Security Note: If you are restoring a database into a different compartment or tenancy than the source, your Dynamic Group for the target DB System must have READ and USE permissions for the Vault and Key. Without these IAM policies, the restore will fail immediately with a "Not Authorized" error.
By correctly mapping these key parameters, you ensure that your data remains encrypted and compliant throughout its entire lifecycle, even as it moves across cloud boundaries.
Automating with Infrastructure as Code (Terraform)
While the CLI is great for one-off tasks, most of my customers eventually want to bake these clones into their CI/CD pipelines. In Terraform, we use the oci_database_database resource. The "magic" happens in the source attribute and the database_details block.
resource"oci_database_database""cloned_db" {
# This maps to the --source flag in the CLIsource = "DB_BACKUP"database {
admin_password = var.database_admin_passworddb_name = "CLONEDB"db_unique_name = "CLONEDB_IAD"character_set = "AL32UTF8"ncharacter_set = "AL16UTF16"db_workload = "OLTP"# TDE Managementtde_wallet_password = var.source_tde_passwordkms_key_id = var.target_vault_key_ocid
}
# Target Infrastructure IDsdb_home_id = var.target_db_home_ociddatabase_id = var.source_database_backup_ocid# Best Practice: Ignore password changes after initial provisionlifecycle {
ignore_changes = [database[0].admin_password]
}
}
Terraform Pro-Tip: Always use the ignore_changes lifecycle hook for the admin_password. Once the database is restored, security policies often require a password rotation. Without this hook, Terraform will try to revert the password to the plain-text value in your .tfvars every time you run an update!
