Automatically clean up expired backups

After a backup is successfully performed, you can set retention policies for tenants based on your business requirements. Tenants with retention 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. If no data backup files exist, the system does not automatically clean up log archive data.

• Automatic cleanup is supported only for the backup and archive destinations specified by using the DATA_BACKUP_DEST and LOG_ARCHIVE_DEST parameters. If the backup or archive destination is changed, the data in the original backup or archive destination cannot be automatically cleared by using OceanBase Database. You need to manually clean up data in the original backup or archive destination.

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

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

• For backup media like NFS or object storage compatible with the S3 protocol (such as OBS, GCS, etc.), the system will directly delete backup files that meet the requirements during data cleanup.

• For scenarios where the backup destination is S3/COS (accessed via 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 detailed information on this parameter, refer to SET LOG_ARCHIVE_DEST and SET DATA_BACKUP_DEST.

• Specifically, for paths where the enable_worm=true parameter is configured, the system will select backup sets that meet the conditions for cleanup 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. Starting from version V4.3.5 BP2, OceanBase Database supports using Alibaba Cloud OSS buckets configured with a WORM policy as backup or archive paths.

  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 only occur after the retention period of the object expires.

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

Set a cleanup policy

Set a cleanup policy 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 for the specified user 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 backups and archive logs stored under the paths currently specified by the DATA_BACKUP_DEST and LOG_ARCHIVE_DEST parameters of the tenant.

     • log_only: automatic cleanup applies only to archive logs stored under the paths currently specified by the LOG_ARCHIVE_DEST parameter. This mode is applicable when only log archiving is enabled and no data backup is configured. When policy_name is set to log_only, the current value of DATA_BACKUP_DEST for the tenant must be empty. If a log_only policy has been set, you cannot configure a data backup destination by setting DATA_BACKUP_DEST.

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

   • tenant_name: specifies the user tenant for which the cleanup policy takes effect. You can specify only one tenant name in each statement.

Examples:

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

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

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-compatible tenant is the root user. The administrator of an Oracle-compatible tenant is the SYS user.

2. Set a cleanup policy to enable the automatic cleanup feature for 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 backups and archive logs stored under the paths currently specified by the DATA_BACKUP_DEST and LOG_ARCHIVE_DEST parameters of the tenant.

     • log_only: automatic cleanup applies only to archive logs stored under the paths currently specified by the LOG_ARCHIVE_DEST parameter. This mode is applicable when only log archiving is enabled and no data backup is configured. When policy_name is set to log_only, the current value of DATA_BACKUP_DEST for the tenant must be empty. If a log_only policy has been set, you cannot configure a data backup destination by setting DATA_BACKUP_DEST.

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

   Examples:

   MySQL-compatible mode

   Oracle-compatible mode

   In MySQL-compatible mode, set the cleanup policy of the current tenant to default and the recovery window 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 recovery window 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 every hour by background threads. After you set a cleanup policy, it may take up to one hour before the corresponding cleanup task can be queried.

Query the cleanup policies

Both system tenants and user tenants can query views to obtain the cleanup policies that have been set.

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

2. Query the cleanup policies set for the tenant.

   • In the sys tenant, query the oceanbase.CDB_OB_BACKUP_DELETE_POLICY view to obtain the cleanup policies set for all tenants.

     Example:

     obclient(root@sys)[oceanbase]> SELECT TENANT_ID, POLICY_NAME, RECOVERY_WINDOW FROM oceanbase.CDB_OB_BACKUP_DELETE_POLICY;
     +-----------+-------------+-----------------+
     | TENANT_ID | POLICY_NAME | RECOVERY_WINDOW |
     +-----------+-------------+-----------------+
     |      1002 | default     | 7d              |
     |      1004 | log_only    | 7d              |
     +-----------+-------------+-----------------+
     2 rows in set
     

   • In a user tenant, query the cleanup policies set for the current tenant.

     MySQL-compatible mode

     Oracle-compatible mode

     obclient(root@mysql001)[oceanbase]> SELECT POLICY_NAME, RECOVERY_WINDOW FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY;
     +-------------+-----------------+
     | POLICY_NAME | RECOVERY_WINDOW |
     +-------------+-----------------+
     | default     | 7d              |
     +-------------+-----------------+
     1 row in set
     

     obclient(SYS@oracle001)[SYS]> SELECT POLICY_NAME, RECOVERY_WINDOW FROM SYS.DBA_OB_BACKUP_DELETE_POLICY;
     +-------------+-----------------+
     | 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 policy, delete the current cleanup policy first and then configure a new policy.

• 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.

References

• View backup cleanup progress

• View backup cleanup results