Point-In-Time Recovery

You can restore data from a DB system to a new DB system at the latest available point-in-time or a specific point-in-time.

Note

Point-in-time recovery is not supported on Always Free DB system.

To restore from a point-in-time, enable automatic backups, and then enable point-in-time recovery on the backup plan of the DB system. See Editing a Backup Plan. Point-in-time recovery provides a Recovery Point Objective (RPO) of approximately five minutes for an active DB system, while the daily backup provides you a RPO of 24 hours. When you stop a DB system, that is, when the DB system is inactive, the latest recovery point is not updated. When you restart the DB system, that is, the DB system is active, the latest recovery point is updated.

When you enable point-in-time recovery:

HeatWave Service takes an initial full backup (Backup type: Full, Creation Type: automatic). Subsequent backups are incremental backups.
The binary log file is rotated and backed up every five minutes. It will also purge any binary log files older than the binlog_expire_seconds setting.
The binlog_expire_auto_purge variable is set to OFF to ensure that the DB system does not purge any binary log files before they have been backed up.

Automatic backups are deleted after the retention period. When an automatic backup is deleted, all backed-up binary log files that are older than the oldest automatic backup of the DB system are deleted too.

When a DB system is deleted, automatic backups are retained if the DB system has been configured with retain automatic backups enabled. See Advanced Option: Deletion Plan. Starting from Sept 25, 2024, deleting a DB system that retains automatic backups also retains the backed-up binary log files. This allows point-in-time recovery to be performed on a deleted DB system if the following conditions are satisfied:

Automatic backup and point-in-time recovery are enabled.
Retain automatic backups is enabled.
It is still within the retention period of the automatic backup.
The DB system is deleted on or after Sept 25, 2024.

Enabling or disabling point-in-time recovery does not impact the DB system. Also, DB system operations such as stop, start, restart, or upgrade does not impact the point-in-time recovery operation. When you create a new DB system from an existing DB system at a point-in-time, it retains the administrator credentials of the original DB system.

You can restore to any specific point-in-time within the earliest and the latest time window. The earliest and the latest time window is displayed in the Console under the Select a specific point-in-time option. The earliest available time depends on the backup retention period. For example, if you set the backup retention period to 12 days, the earliest available time is 12 days.

You cannot create a DB system that has the same IP address as a running DB system. You can let Oracle select an unused IP address from the subnet or define a new IP address for the new DB system using the IP address field on the Connections tab of the Advanced options. See Advanced Option: Connections.

Using the Console

Use the Console to restore from a DB system at a point-in-time to a new DB system.

Note

If you encounter OutOfHostCapacity work request error while creating a DB system from a backup, see Resolving OutOfHostCapacity Error.

Open the navigation menu and select Databases. Under HeatWave MySQL, click Backups.
Select your compartment from the List scope.
Do one of the following:
- On the same line as your backup, select the Restore to new DB system from the Actions menu .
- Click the name of the backup to open the Backup details page and click Restore to new DB system.
- Click the name of the DB system present on the same line as your backup to open the DB system details page. Click More actions and then click Restore to a new DB system.
In the Restore to a new DB system panel, select Restore from DB system a point-in-time.
Select one of the following options:
- Use the latest available point-in-time: Restores the DB system to a new DB system using the latest available point-in-time.
- Select a specific point-in-time: Restores the DB system to a new DB system using the specified date and time. Select the date and time to restore the DB system.
Provide DB system information:
- Create in compartment: If you want to launch the DB system in a compartment other than the current compartment, select the required compartment from the list. If you do not select a different compartment, the current compartment is used.
- Name: (Optional) Specify a user-friendly display name for the DB system. The name does not need to be unique. An Oracle Cloud Identifier (OCID) uniquely identifies the DB system.
- Description: (Optional) Specify a user-friendly description of the DB system and its purpose.
The type of DB system: Standalone or High availability.
Configure networking:
- Virtual cloud network: Select the VCN in which to launch the DB system.
- Subnet: Select the required subnet of the selected VCN.
Configure placement:
- Availability domain: You can change the availability domain, but not the region.
- Choose a fault domain: Select this option to enable you to choose a fault domain. If you do not choose a fault domain, Oracle chooses one for you. See Fault Domains.
Configure hardware:
- Enable HeatWave cluster: The default depends to the state of the DB system during the backup. You can change this option based on your requirements.
  Note
  
  When restoring a DB system combined with HeatWave cluster, you cannot upgrade to a newer version. If needed, you can upgrade the DB system and HeatWave cluster after the restore has completed.
- Select a shape: Click Change shape. In the Browse all shapes panel, do the following:
  1. Select the compute model.
    - ECPU: Based on an architecture-agnostic of ECPU with an abstracted measure of CPU cores, memory, and other resources.
    - OCPU: Based on physical core of a processor with hyper-threading enabled. Filter the shapes by selecting the machine type:
      - All types: Displays all available shapes.
      - Virtual machine: Displays the Virtual Machine-based shapes only.
      - Bare metal: Displays the Bare Metal-based shapes only.
      Note
      
      All OCPU shapes are deprecated and are not available to new users created after Sept 5, 2024; existing users can still use them until Sept 5, 2025.
  2. Select the checkbox to choose a shape. See Supported Shapes.
  3. Click Select a shape.
  Note
  
  The shape is not changeable when restoring an Always Free backup to an Always Free DB system.
- HeatWave cluster configuration: This option is shown when Enable HeatWave cluster is selected. If you want to change the default configuration, click Configure HeatWave cluster and do the following in the Configure HeatWave cluster panel:
  Note
  
  The shape and number of nodes are not changeable when restoring an Always Free backup to an Always Free DB system.
  1. Shape details: The shape for the HeatWave nodes. If you want to change the shape, click Change shape to select a shape for the HeatWave nodes. See Supported Shapes.
  2. Node: Specify the number of HeatWave nodes to create. The minimum is 1 node and the maximum depends to the following:
    - HeatWave.Free shape: 1 node
    - HeatWave.32GB shape: 16 nodes
    - HeatWave.512GB or MySQL.HeatWave.VM.Standard shape: 64 nodes
    - HeatWave.512GB or MySQL.HeatWave.VM.Standard shape with Lakehouse enabled: 512 nodes
  3. HeatWave Lakehouse: Enable query processing on the data residing in Object Storage. Prior to MySQL 8.4.0-u3, HeatWave Lakehouse is supported on the MySQL.HeatWave.VM.Standard and HeatWave.512GB shapes only. See HeatWave Lakehouse.
    Note
    
    Prior to MySQL 8.3.0-u2, you must disable point-in-time recovery, high availability, read replicas, and outbound replication in order to enable HeatWave Lakehouse.
  4. Click Save changes.
- Storage size: Configure the storage size of the DB system. The data storage size is independent of the shape you select.
  - Initial data storage size (GB): Specify the initial data storage size, in GB, to allocate to the DB system. The value should be greater than 50 and less than 131072.
    The initial storage value has an impact on your ability to increase the storage size later and the performance of the increased storage. See DB System Storage. Also, if you are importing data, ensure you specify sufficient data storage.
  - Automatic storage expansion: Enable automatic data storage expansion when space usage grows near to the database instance Health Monitor read-only limits for any instance in the DB system.
    Note
    
    Automatic storage expansion is enabled by default when high availability is enabled, and it cannot be disabled.
  - Maximum data storage size (GB): Specify the maximum data storage size, in GB, to which the storage size can be expanded. The value must be greater than the initial data storage size.
    Note
    
    Whenever the storage size is expanded, the billing for storage usage will increase accordingly. Use the maximum storage size to prevent cost overrun.
Configure backup plan:
- Enable automatic backups: Select the option to enable automatic backups. If you do not select automatic backups, manage your backups manually. It is recommended to enable automatic backups.
- Backup retention period: Specify how may days you want to retain the backup. By default, the DB system retains the backup for seven days.
- Enable point-in-time recovery: Select the option to enable you to restore a DB system at a point-in-time to a new DB system.
- Select backup window: Select the option to enable you to select the backup window start time.
- Window start time: Specify the time (in UTC timezone) when you want to start the backup of the DB system. The backup starts within 30 minutes following the window start time.
- Show backup windows per region: Click to see the default window start time of the region.
Provide contacts for operational notifications and announcements: (Optional) Enter one or more contact emails to receive operational notices and announcements as well as unplanned maintenance notifications. Click Add contact to add more contacts. You can add up to a maximum of 10 contacts. Duplicate and invalid email addresses are not allowed.
Show advanced options: Click to open a group of tabs that enable you to further configure the DB system. See Advanced Options.
Click Restore.

Using the CLI

Use the command-line interface to restore from a DB system to a new DB system at a point-in-time .

This task requires the following:

A DB system Oracle Cloud Identifier (OCID).
A properly configured CLI installation and the requisite SSH keys. See Command Line Interface.

Open a command prompt and run the following command:
```
oci mysql db-system create 
          --compartment-id=<CompartmentOCID> 
          --configuration-id=<ConfigurationOCID>
          --subnet-id=<SubnetOCID> 
          --shape-name=<ShapeName> 
          --display-name=<DisplayName>
          --availability-domain=<AvailabilityDomain> 
          --source='{"dbSystemId": "<DBSystemOCID>", 
                     "recoveryPoint": "2022-06-30T06:55:50.000Z", 
                     "sourceType": "PITR"}'
```
- compartment-id: Specify the OCID of the compartment in which you want to create the DB system.
- configuration-id: (Optional) Specify the OCID of the configuration that you want to apply to the DB system. If you do not define a configuration ID, the DB system uses the default configuration associated with the selected shape. If you define a custom configuration, use the same shape as you define in the request.
- subnet-id: Specify the OCID of the VCN subnet to which the DB system is attached.
- shape-name: Specify the name of the shape. For example, MySQL.VM.Standard.E3.1.8GB.
- display-name: (Optional) Specify the display name of the DB system. If you do not define a display name, Oracle generates one for you in the format mysqldbsystemYYYYMMDDHHMMSS.
- availability-domain: Specify the name of the availability domain in which you want to create the DB system.
- source: (Optional) Specify the details of point-in-time recovery.
  - dbSystemId: Specify the OCID of the DB system from which you want to restore data.
  - recoveryPoint: Specify the point-in-time of the source DB system at which you want to restore data to the new DB system in the format, YYYY-MM-DDThh:mm:ss.SSSZ. The SSS value is ignored, that is, 2020-08-07T15:02:00.832Z is treated as 2020-08-07T15:02:00.000Z
    If you do not specify a time, then the new DB system is restored to the latest recovery point available for the source DB system.
  - sourceType: Specify PITR for point-in-time recovery.

Supported Point-In-Time Recovery Scenarios

HeatWave Service supports point-in-time recovery of a standalone or high availability DB system to a new DB system.

When you restore from standalone DB system to a high availability DB system, ensure the following:

Primary keys exist on every table in the DB system.
MySQL version is 8.0.24 or higher. You cannot enable high availability on earlier versions of MySQL.

Table 9-1 Supported Point-In-Time Recovery Scenarios

Source	Target	Support
Standalone DB system	Standalone DB system High availability DB system Standalone DB system with HeatWave cluster High availability DB system with HeatWave cluster	Supported
Standalone DB system with HeatWave cluster¹	Standalone DB system High availability DB system	Supported - Only InnoDB data is restored.
Standalone DB system with HeatWave cluster¹	Standalone DB system with HeatWave cluster High availability DB system with HeatWave cluster	Supported - Only InnoDB data is restored. If you add a HeatWave cluster to the standalone or high availability DB system later, data is not automatically loaded in the HeatWave cluster, and you need to load it manually.
High availability DB system	Standalone DB system High availability DB system Standalone DB system with HeatWave cluster High availability DB system with HeatWave cluster	Supported
High availability DB system with HeatWave cluster¹	Standalone DB system High availability DB system	Supported - Only InnoDB data is restored.
High availability DB system with HeatWave cluster¹	Standalone DB system with HeatWave cluster High availability DB system with HeatWave cluster	Supported - Only InnoDB data is restored. If you add a HeatWave cluster to the standalone or high availability DB system later, data is not automatically loaded in the HeatWave cluster, and you need to load it manually.

¹ Prior to MySQL 8.3.0-u2, you cannot enable point-in-time recovery on a DB system with HeatWave Lakehouse enabled.

Limitations

There are certain limitations of restoring from a DB system at a point-in-time.

Prior to MySQL 8.3.0-u2, you cannot enable point-in-time recovery on a DB system with HeatWave Lakehouse enabled.
An automatic backup failure can impact the time taken by the point-in-time recovery operation. For example, if Backup retention period is set to two days, and Monday backup was successful while Tuesday backup has failed, then point-in-time recovery to any time on Tuesday requires roll forward from Monday's backup. Hence, the point-in-time recovery operation requires more time to restore.
You cannot select a value (in the Select a specific point-in-time field) earlier than the oldest successful automatic backup after point-in-time recovery was enabled. For example, if Backup retention period is set to three days and Monday, Tuesday backup have failed, and Wednesday backup has succeeded, then the oldest point-in-time recovery is Wednesday.
You cannot disable point-in-time recovery on a DB system while point-in-time recovery is going on.
Point-in-time recovery is not supported on an Always Free DB system.

Oracle Cloud Infrastructure Documentation

Using the Console

Using the CLI

Supported Point-In-Time Recovery Scenarios

Limitations