10 Backing Up and Restoring Autonomous Data Warehouse

This section describes backup and recovery tasks on Autonomous Data Warehouse.

About Backup and Recovery on Autonomous Data Warehouse

Autonomous Data Warehouse automatically backs up your database for you. The retention period for backups is 60 days. You can restore and recover your database to any point-in-time in this retention period.

Manual Backups

You do not have to do any manual backups for your database as Autonomous Data Warehouse backs up your database automatically. You can do manual backups using the cloud console; for example if you want to take a backup before a major change to make restore and recovery faster. The manual backups are put in your Cloud Object Storage bucket. When you initiate a point-in-time recovery Autonomous Data Warehouse decides which backup to use for faster recovery.

Recovery

You can initiate recovery for your Autonomous Data Warehouse database using the cloud console. Autonomous Data Warehouse automatically restores and recovers your database to the point-in-time you specify.

Listing Backups

The list of backups available for recovery is shown on the Autonomous Data Warehouse details page under Backups.

Restore and Recover your Autonomous Data Warehouse Database

The Autonomous Data Warehouse console Actions drop-down Restore operation allows you to initiate recovery for your database.

To restore and recover your database to a point in time, do the following:

  • Sign in to your Oracle Cloud Account at cloud.oracle.com.

  • From the Oracle Cloud Infrastructure left navigation list click Autonomous Data Warehouse.

  • On the Autonomous Databases page select an Autonomous Data Warehouse instance from the links under the Name column.

  1. On the details page, from the Actions drop-down list, select Restore to display the Restore prompt.
  2. In the Restore prompt, select Specify Timestamp or Select Backup to restore to a point in time or to restore from a specified backup.
  3. Click Restore.

    Note:

    Restoring Autonomous Data Warehouse puts the database in the unavailable state during the restore operation. You cannot connect to a database in that state. The only lifecycle management operation supported in unavailable state is terminate.

    The details page shows Lifecycle State: Restore In Progress...

  4. When the restore operation finishes your Autonomous Data Warehouse instance opens in read-only mode and the instance details page Lifecycle State shows Available Needs Attention.
  5. At this point you can connect to your Autonomous Data Warehouse instance and check your data to validate that the restore point you specified was correct:
    • If the restore point you specified was correct and you want to open your database in read-write mode click Stop and after the database stops, click Start to start the database. After stopping and starting, the database opens in read-write mode.

      Note:

      After the instance is opened in read-write mode some of your backups are invalidated.
    • After checking your data if you find that the restore date you specified was not the one you needed you can initiate another restore operation to another point in time.

Note 1:

When you restore your database and open it in read-write mode, all backups between the date the restore completes and the date you specified for the restore operation - the restore time - are invalidated. After the database is opened in read-write mode after a restore, you cannot initiate further restore operations to any point in time between the restore time and restore completion time. You can only initiate new restore operations to a point in time older than the restore time or more recent than the time when the actual restore succeeded.

For example, assume you initiate a restore operation on Oct 8, 2018, 2 pm and specify Oct 1, 2018, 2 pm as the point in time to restore to and the restore completes on Oct 8, 2018, 2:30 pm. If you open your database in read-write mode at this point, backups between Oct 8, 2018, 2:30 pm and Oct 1, 2018, 2 pm will be invalidated. You will not be able to restore to any date between Oct 1, 2018, 2 pm and Oct 8, 2018, 2:30 pm. If you initiate a restore to a point in time between these dates the restore will fail with an error.

Note 2:

The restore operation also restores the DATA_PUMP_DIR directory to the timestamp you specified for the restore; files that were created after that timestamp would be lost.

Manual Backups on Autonomous Data Warehouse

In addition to automatic backups Autonomous Data Warehouse also allows you take manual backups to your Oracle Cloud Infrastructure Object Storage.

Configure Manual Backups on Autonomous Data Warehouse

Follow these steps to define your Oracle Cloud Infrastructure Object Storage credentials and tenancy URL and to create the bucket for manual backups.

To perform manual backups you need to define your Oracle Cloud Infrastructure Object Storage credentials and your Oracle Cloud Infrastructure Object Storage tenancy URL; you also need to create a bucket to hold the backups. The manual backup configuration tasks are a one-time operation. After you define your credentials and your tenancy URL you can initiate manual backups without doing the same operations again unless the URL, the credentials, or bucket change.

For this task you need a client tool that is connected to Autonomous Data Warehouse to perform the DDL commands. For example, use SQL Developer or SQL*Plus. See Connect to Autonomous Data Warehouse Using Oracle Database Tools.

  1. Set the database default_bucket property to your Oracle Cloud Infrastructure Object Storage tenancy URL. The format of the tenancy URL is https://swiftobjectstorage.region.oraclecloud.com/v1/object_storage_namespace. Note that you need to do this using the ADMIN user.

    For example:

    ALTER DATABASE PROPERTY SET default_bucket='https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/adwc';
  2. On your Oracle Cloud Infrastructure Object Storage, create a bucket to hold the backups. The format of the bucket name is backup_databasename. Where databasename is lowercase.
    1. Open the Oracle Cloud Infrastructure service console from MyServices through the Services menu or the Services Dashboard tile.
    2. Select Object Storage from the menu.
    3. Select Object Storage from the submenu.
    4. Create a bucket in a compartment by clicking Create Bucket.

    Manual backups are only supported with buckets created in the standard storage tier, make sure you pick Standard as the storage tier when creating your bucket. For information on the Standard Object Storage Tier, see Overview of Object Storage.

    For example, if you provision an Autonomous Data Warehouse instance named ADWC1, the bucket name should be backup_adwc1. Following the same example, the URL of this bucket would be (the bucket name is lowercase):

    https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/adwc/backup_adwc1
  3. Create a credential for your Oracle Cloud Infrastructure Object Storage account using DBMS_CLOUD.CREATE_CREDENTIAL. See CREATE_CREDENTIAL Procedure.

    Note that you need to do this using the ADMIN user. For example:

    BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DEF_CRED_NAME',
        username => 'adwc_user@oracle.com', 
        password => 'password'
    );
    END;
    /
  4. Set the database property DEFAULT_CREDENTIAL to the credential you created in the previous step. For example:
    ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

To list the current value for the default bucket, use the following command:

SELECT PROPERTY_VALUE 
FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME='DEFAULT_BUCKET';

Perform Manual Backups on Autonomous Data Warehouse

In addition to automatic backups Autonomous Data Warehouse also allows you take manual backups to your Oracle Cloud Infrastructure Object Storage.

  • Sign in to your Oracle Cloud Account at cloud.oracle.com.

  • From the Oracle Cloud Infrastructure left navigation list click Autonomous Data Warehouse.

  • On the Autonomous Databases page select an Autonomous Data Warehouse instance from the links under the Name column.

  1. On the details page, under Backups, click Create Manual Backup.
  2. In the Create Manual Backup dialog enter a name in the Name field.
  3. In the Create Manual Backup dialog click Create.

Note 1:

Each manual backup creates a full backup on your Oracle Cloud Infrastructure Object Storage bucket and the backup can only be used by the Autonomous Data Warehouse instance when you initiate a point-in-time-recovery.

Note 2:

The retention period for manual backups is the same as automatic backups which is 60 days.

Note 3:

While backing up a database, the database is fully functional; however during the backup lifecycle management operations are not allowed. For example, stopping the database is not allowed during the backup.