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.
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.
- 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 toOFF
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.
- 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.
When you restore a DB system to a point-in-time, it retains the administrator credentials of the original DB system at the point-in-time. It also retains the version of the DB system by default unless you choose a higher version to upgrade the restored DB system. If the version of the restored DB system has become unavailable, it will be upgraded in the next maintenance cycle.
- Using the Console
- Using the CLI
- Using the REST API, run the CreateDbSystem operation with the
source
attribute set to CreateDbSystemSourceFromPitrDetails. - Supported Point-In-Time Recovery Scenarios
- Limitations
Using the Console
Use the Console to restore from a DB system at a point-in-time to a new DB system.
- If you encounter
OutOfHostCapacity
work request error while creating a DB system from a backup, see Resolving OutOfHostCapacity Error.
Using the CLI
Use the command-line interface to restore from a DB system to a new DB system at a point-in-time .
- A DB system Oracle Cloud Identifier (OCID).
- A properly configured CLI installation and the requisite SSH keys. See Command Line Interface.
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 |
|
Supported |
Standalone DB system with HeatWave cluster1 |
|
Supported - Only InnoDB data is restored. |
Standalone DB system with HeatWave cluster1 |
|
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 |
|
Supported |
High availability DB system with HeatWave cluster1 |
|
Supported - Only InnoDB data is restored. |
High availability DB system with HeatWave cluster1 |
|
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.