Oracle Cloud Infrastructure Documentation

Restoring With Point In Time Restore Feature

You can restore data from a DB system to a new DB system using the point in time restore feature. You can either select the latest available point in time or a specific point in time.

To restore from a point in time, enable the feature on the backup plan of the DB system. See Editing a Backup Plan. The point in time restore feature provides a Recovery Point Objective (RPO) of approximately five minutes while the daily backup provides you a RPO of 24 hours. 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. Enabling or disabling of the point in time feature does not impact the DB system. Also, DB system operations such as stop, start, restart, or upgrade does not impact the point in time restore operation. The retention period of binary logs is same as the retention period of automatic backups.

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 Networking tab of the Advanced Options. See Advanced Option: Networking. If you delete the DB system, it disables the point in time restore feature.

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.

Using the Console

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

  1. Open the navigation menu and select Databases. Under MySQL, click DB Systems.
  2. Choose your compartment from the List Scope.
  3. 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.
  4. In the Restore to a new DB system panel, select Restore from DB system a point in time.
  5. 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.
  6. 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.
  7. The type of DB System: You cannot change the type of the DB system when restoring from a backup. For example, if the original DB system was Standalone, your new DB system will also be Standalone.
  8. Configure networking:
    • Virtual Cloud Network: Select the VCN in which to launch the DB system.
    • Subnet: Select the required subnet of the selected VCN.
  9. 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.
  10. 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 DB system. The value should be greater than 50 and less than 65536.
      Note

      The storage value has an impact on your ability to update storage later. See Increasing the Storage of a DB System. Also, if you are importing data, ensure you specify sufficient data storage.
  11. 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 restore: 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 your DB system. Your backup starts within 30 minutes following the window start time.
    • Show backup windows per region: Click to see the default window start time of your region.
  12. Show Advanced Options: Click to open a group of tabs that enable you to further configure your DB system. See Advanced Options.
  13. 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.
  1. 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 the point in time restore feature.
      • 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 restore feature.

Limitations

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

  • Point in time restore does not support high availability DB systems.
  • You cannot restore from a non high availability DB system to a high availability DB system.
  • You cannot disable or delete automatic backups on a DB system that has point in time restore feature enabled.
  • An automatic backup failure can impact the time taken by the point in time restore 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 restore to any time on Tuesday requires roll forward from Monday's backup. Hence, the point in time restore 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 the point in time restore feature 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 restore is Wednesday.
  • You cannot disable the point in time restore feature on a DB system while point in time restore operation is going on.

Billing

When you enable point in time restore feature, the binary logs are backed up, and billing depends on the storage size of the binary logs.

If you disable point in time restore feature on a DB system, or if you delete the DB system, your billing stops.