Automatically clean up expired backups

After a backup is successfully performed, you can set cleanup policies for tenants based on your business requirements. Tenants with cleanup policies will trigger an automatic cleanup process every hour to ensure that expired backups and archive data are promptly removed.

Considerations

• Log archive data can be cleared only after data backups are performed. Before you clean up log archive data, make sure that data backup files exist. If no data backup files exist, log archive data cannot be cleaned up.

• Automatic cleanup is supported only for the backup and archive data stored in the paths currently specified by the DATA_BACKUP_DEST and LOG_ARCHIVE_DEST parameters. Data in the backup paths and archive paths that are no longer in use will not be cleaned up. If the backup destination or archive destination has been changed, the data in the original backup path or archive path can be cleaned up only manually.

  For more information about manually cleaning up backup or archive data, see Manually clean up specified backup data and Clear the backup_dest or archivelog_dest.

• Automatic cleanup retains at least one valid backup copy. If only one valid backup copy exists, this copy will not be cleaned up.

• Different backup media have varying results for automatic backup cleaning:

  • For backup media such as NFS or object storage compatible with the S3 protocol (OBS, GCS, and so on), the system directly deletes the backup files that meet the cleanup requirements during backup data cleanup.
  • For scenarios where the backup destination is S3/COS (accessed by using the S3 protocol), the method of cleaning backup files depends on the value of the delete_mode parameter in data_backup_dest and log_archive_dest. For more information about this parameter, see SET LOG_ARCHIVE_DEST and SET DATA_BACKUP_DEST.

• In particular, for paths where the enable_worm=true parameter is configured in the path attributes, during automatic cleanup of backup or archive data, the system selects the backup or archive data that meets the conditions based on the time specified by RECOVERY_WINDOW. However, whether the cleanup is successful depends on the cleanup mode corresponding to the path (the value of the delete_mode parameter) and the retention policy of the corresponding bucket.

  Notice

  The enable_worm=true parameter is applicable in scenarios where a compliance retention policy (WORM) is enabled.

  For example, if a backup set is outside the time specified by RECOVERY_WINDOW but still within the WORM (Write Once Read Many) retention period, then:

  • If the cleanup mode of the backup set is delete, OceanBase Database will fail to clean up the backup set. Cleanup can occur only after the retention period of the object expires.

  • If the cleanup mode of the backup set is tagging, OceanBase Database will tag the backup set, and the object storage will delete it after its lifecycle ends and the WORM retention period has passed.

Set a cleanup policy for a specified user tenant in the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

2. Set a cleanup policy to enable the automatic cleanup feature of the tenant.

   The statement is as follows:

   ALTER SYSTEM ADD DELETE BACKUP POLICY [=] policy_name RECOVERY_WINDOW [=] recovery_window TENANT [=] tenant_name;
   

   where:

   • policy_name: specifies the name of the cleanup policy. Supported values:

     • default: automatic cleanup applies only to the backup and archive data stored in the paths currently specified by the DATA_BACKUP_DEST and LOG_ARCHIVE_DEST parameters of the tenant.
     • log_only: automatic cleanup applies only to the logs in the archive path currently specified by the LOG_ARCHIVE_DEST parameter of the tenant. This mode is applicable when only log archiving is enabled and no data backup is performed. When the cleanup policy is set to log_only, the current value of DATA_BACKUP_DEST for the tenant must be empty, which means that no data backup destination is configured. Accordingly, if a log_only cleanup policy has been set, you can no longer configure a backup destination by setting the DATA_BACKUP_DEST parameter.

   • The recovery_window parameter specifies the time window during which backup data can be restored. For more information about this parameter, see Introduction to the recovery_window parameter.

   • The TENANT parameter specifies the tenant for which the cleanup policy takes effect. You must specify the name of a user tenant, and only one tenant name is supported.

   Notice

   Only tenant-level cleanup policies are supported. Cluster-level cleanup policies are not supported. Each statement can set a cleanup policy for only one tenant.

   Examples:

   • The system tenant specifies a cleanup policy for the mysql001 tenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM ADD DELETE BACKUP POLICY 'default' RECOVERY_WINDOW '7d' TENANT mysql001;
     

   • The system tenant specifies a cleanup policy for the oracle001 tenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM ADD DELETE BACKUP POLICY 'log_only' RECOVERY_WINDOW '7d' TENANT oracle001;
     

   Note

   The automatic cleanup task is triggered by the background system every hour. Therefore, after a cleanup policy is set, you may need to wait for some time (no more than one hour) before the corresponding cleanup task can be queried.

3. View the configured cleanup policies.

   After the cleanup policies are configured, you can query the oceanbase.CDB_OB_BACKUP_DELETE_POLICY view to view the cleanup policies of all tenants.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, POLICY_NAME, RECOVERY_WINDOW FROM oceanbase.CDB_OB_BACKUP_DELETE_POLICY;
   

   The query result is as follows:

   +-----------+-------------+-----------------+
   | TENANT_ID | POLICY_NAME | RECOVERY_WINDOW |
   +-----------+-------------+-----------------+
   |      1002 | default     | 7d              |
   |      1004 | log_only    | 7d              |
   +-----------+-------------+-----------------+
   2 rows in set
   

Set a cleanup policy in a user tenant

1. Log in to the database as the tenant administrator of the user tenant.

   Note

   The administrator of a MySQL tenant is the root user. The administrator of an Oracle tenant is the SYS user.

2. Set a cleanup policy to enable the automatic cleanup feature of the tenant.

   The statement is as follows:

   ALTER SYSTEM ADD DELETE BACKUP POLICY [=] policy_name RECOVERY_WINDOW [=] recovery_window;
   

   where:

   • policy_name: specifies the name of the cleanup policy. Supported values:

     • default: automatic cleanup applies only to the backup and archive data stored in the paths currently specified by the DATA_BACKUP_DEST and LOG_ARCHIVE_DEST parameters of the tenant.
     • log_only: automatic cleanup applies only to the logs in the archive path currently specified by the LOG_ARCHIVE_DEST parameter of the tenant. This mode is applicable when only log archiving is enabled and no data backup is performed. When the cleanup policy is set to log_only, the current value of DATA_BACKUP_DEST for the tenant must be empty, which means that no data backup destination is configured. Accordingly, if a log_only cleanup policy has been set, you can no longer set the DATA_BACKUP_DEST parameter.

   • recovery_window: specifies the time window during which backup data can be restored. For more information about this parameter, see Introduction to the recovery_window parameter.

   Examples:

   MySQL-compatible mode

   Oracle-compatible mode

   In MySQL-compatible mode, set the cleanup policy of the current tenant to default and the time window during which backups can be restored to 7 days.

   obclient(root@mysql001)[(none)]> ALTER SYSTEM ADD DELETE BACKUP POLICY 'default' RECOVERY_WINDOW '7d';
   

   In Oracle-compatible mode, set the cleanup policy of the current tenant to log_only and the time window during which backups can be restored to 7 days.

   obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ADD DELETE BACKUP POLICY 'log_only' RECOVERY_WINDOW '7d';
   

   Note

   The automatic cleanup task is triggered by the background system every hour. Therefore, after a cleanup policy is set, you may need to wait for some time (no more than one hour) before the corresponding cleanup task can be queried.

3. View the configured cleanup policies.

   After the cleanup policies are configured, you can query the oceanbase.DBA_OB_BACKUP_DELETE_POLICY view (in MySQL-compatible mode) or the sys.DBA_OB_BACKUP_DELETE_POLICY view (in Oracle-compatible mode) to view the cleanup policies of the current tenant.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to query the cleanup policies in MySQL-compatible mode:

   obclient(root@mysql001)[oceanbase]> SELECT POLICY_NAME, RECOVERY_WINDOW FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY;
   

   The query result is as follows:

   +-------------+-----------------+
   | POLICY_NAME | RECOVERY_WINDOW |
   +-------------+-----------------+
   | default     | 7d              |
   +-------------+-----------------+
   1 row in set
   

   The following example shows how to query the cleanup policies in Oracle-compatible mode:

   obclient(SYS@oracle001)[SYS]> SELECT POLICY_NAME, RECOVERY_WINDOW FROM SYS.DBA_OB_BACKUP_DELETE_POLICY;
   

   The query result is as follows:

   +-------------+-----------------+
   | POLICY_NAME | RECOVERY_WINDOW |
   +-------------+-----------------+
   | log_only    | 7d              |
   +-------------+-----------------+
   1 row in set
   

What to do next

• After a cleanup policy is configured, if you need to change the cleanup policy, you must delete the current cleanup policy first and then configure a new one. For more information about how to delete a cleanup policy, see Drop the cleanup strategy.

• After a cleanup policy is configured, if you want to perform an expired backup cleanup as soon as possible, you can manually trigger an expired backup cleanup task. For more information about how to manually clean up expired backups, see Manually trigger the cleanup of expired backups.

References

• View the progress of backup cleanup

• View the results of backup cleanup

• Cancel a cleanup task