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.

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, MySQL HeatWave Service takes an initial full backup (Backup type: Full, Creation Type: automatic). Later on, the backups are incremental backups.

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. If you delete the DB system, it disables point-in-time recovery.

The retention period of binary logs is same as the retention period of automatic backups.

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 MySQL HeatWave, click DB systems.
Choose your compartment from the List scope.
Do one of the following:
- Choose the Restore to a new DB system from the Actions menu on the same line as your DB system.
- Click the name of the DB system to open the DB system details page. Click More actions and then click Restore to 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: Choose this option to enable you to select a fault domain. If you do not choose a fault domain, Oracle chooses one for you. See Fault Domains.
Configure hardware:
- Select a shape: Click Change shape. In the Browse all shapes panel, do the following:
  1. Select the checkbox to choose the relevant shape. See Supported Shapes.
  2. Click Select a shape. If you want to use a shape which is smaller than the shape used by the parent DB System, ensure the selected shape defines resources appropriate to your DB System's resource requirements.
- Data storage size (GB): Specify the amount of block storage, in GB, to allocate to the new DB system. The value should be equal to or greater than the storage size of the existing DB system, and smaller than 131,072. The data storage size is independent of the shape you select.
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.
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

MySQL 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 High availability DB system with HeatWave	Supported
Standalone DB system with HeatWave¹	Standalone DB system High availability DB system	Supported - Only InnoDB data is restored.
Standalone DB system with HeatWave¹	Standalone DB system with HeatWave High availability DB system with HeatWave	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 High availability DB system with HeatWave	Supported
High availability DB system with HeatWave¹	Standalone DB system High availability DB system	Supported - Only InnoDB data is restored.
High availability DB system with HeatWave¹	Standalone DB system with HeatWave High availability DB system with HeatWave	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.

Oracle Cloud Infrastructure Documentation

Using the Console

Using the CLI

Supported Point-In-Time Recovery Scenarios

Limitations