A! Help: Creating Manual Backups on Autonomous Transaction Processing DB

Sunday, March 14, 2021

Creating Manual Backups on Autonomous Transaction Processing DB

Autonomous Transaction Processing (ATP) DB is not configured for manual backup right after provisioning.

The manual backups are written to an object storage bucket. Cretain configuration tasks must be done before hand so that ATP is able to identify and write the backup to the pre configured object storage bucket. The actual backup tasks is simple as clicking the "create manaul backup" button.
1. As the first step of the configuration identify the object storage name space for the tenancy. This could be done either with oci cli as

$ oci os ns get
{
  "data": "namespace is shown here"
}

or from the OCI console (administration -> tenancy details).

2. Get the ATP name not the display name of the ATP. This inforamtion is available on the ATP detail page.

3. Create an object storage bucket to hold the manual backups. The bucket name must follow a certain format. It must be prefixed with word "backup" and followed by an underscore (backup_). After the underscore specify the ATP name. In this case the ATP name is "testatp". As such the bucket name should be "backup_testatp".

If the bucket name doesn't follow this format then the ATP would be unable to identify that configuration needed for manaul backup has been done.

Update 28/09/2021
The naming convention is no longer needed. Any valid bucket name could be used for manual backup storage. Once all configuration steps are done ATP will identify the bucket designated for backups. Below is an example of such bucket name being detected by ATP as a location for manual backups.

4. Once the bucket is created, as the admin user set the default_bucket property for the database using the swift format. Do not include the bucket name in the end. The URL should end with the namespace identified in step 1.

ALTER DATABASE PROPERTY SET default_bucket='https://swiftobjectstorage.#region#.oraclecloud.com/v1/#NAMESPACE#';

Update 11/09/2021
In the new documentation the property has been renamed to default_backup_bucket. The new documentation states set the database property DEFAULT_BACKUP_BUCKET to identify the manual backup bucket in your Oracle Cloud Infrastructure Object Storage. Also the URI used with default backup bucket contain the bucket name. Considering the earlier shown bucket name, the URI for the default backup bucket is specified as

ALTER DATABASE PROPERTY SET default_backup_bucket
='https://swiftobjectstorage.#region#.oraclecloud.com/v1/#namespace#/atp_backups';

5. Generate an auth token for a OCI user that has access to the bucket created. (this step is similar to earlier post on import/export on ATP).

At minimum the user must belong to a group that as following permission on the compartment where the bucket resides

allow group #usergroup#  to read buckets in compartment #bucketCompartment#
allow group #usergroup#  to manage objects in compartment #bucketCompartment#

6. Create a crednetail using the auth token

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'backup_cred',
username => '#user#',
password => 'token generated above'
);
END;
/

7. Set the created credentail as the default credentail.

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL ='ADMIN.BACKUP_CRED';

8. Check the default properties for manual backup is set.

SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME IN ('DEFAULT_BUCKET','DEFAULT_CREDENTIAL');

PROPERTY_NAME        PROPERTY_VALUE
-------------------- ----------------------------------------------------------------------------------------------------
DEFAULT_BUCKET       https://swiftobjectstorage.#region#.oraclecloud.com/v1/#namespace#   
DEFAULT_CREDENTIAL   ADMIN.BACKUP_CRED

Another way to verify that all is working as it should is to upload a dummy file to the bucket created earlier and query the content of the bucket specifying the credentail. In this case the URL to bucket must be given.

select object_name, bytes from 
dbms_cloud.list_objects('BACKUP_CRED','https://swiftobjectstorage.#region#.oraclecloud.com/v1/#namespace#/backup_testatp/');

OBJECT_NAME                         BYTES
------------------------------ ----------
exports_options.txt                   428

9. When the configuration is done refresh the ATP details page. If ATP is able to detect the manaul configuration has been done the bucket name will be listed as the manual backup store.

Anything else listed under manual backup store, such as the namespace or "not configured" means manual backup configuration is not complete and any attempt to run a manual backup will fail.

10. Final step is to execute the manual backup. On the ATP details page select backup and click on create manual backup button and give the manual backup a name.

At times while the backup is in progress the manual backup store on ATP detail page goes back to "not configured". If this happens ignore it and wait until backup finishes. If all is correct inspite of this backup will successfully complete. If backup fails (even though at step 9 ATP discovered the bucket) then it means at run time ATP encountered an error and manual backup configuration is not correct (most likely a permission issue such as able to view the bucket but cannot write to it).

The work request will show the backup job progress.