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 do 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.
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:
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.
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.
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.
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
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.
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 many 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.
Note
Do not set the backup window start time too near to the maintenance window start time. The automatic backup cannot start if a conflicting operation such as maintenance or upgrade is currently running. It has to wait until the conflicting operation completes. If the conflicting operation does not complete within two hours of the backup window start time, the automatic backup will be skipped and it will try again in the next backup window in the following day.
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:
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.
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 cluster1
Standalone DB system
High availability DB system
Supported - Only InnoDB data is restored.
High availability DB system with HeatWave cluster1
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.
1 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.