Automatically clean up expired backup data

You can set a backup cleanup strategy for a tenant as needed. After you set a cleanup strategy for a tenant, automatic cleanup will be triggered for the tenant every hour so that expired backup data can be promptly cleaned up.

In the current version of OceanBase Database, expired backups can only be automatically cleaned up and cannot be manually cleaned up.

Considerations

• The cleanup of the archive log data depends on data backup. Before you clean up archive log data, confirm that a data backup file exists. If no data backup file exists, the archive log data cannot be cleaned up.

• Automatic cleanup applies only to the data at the backup destinations specified by data_backup_dest and log_archive_dest. If the backup destination changes, the expired backup data cannot be cleaned up in OceanBase Database.

• At least one copy of valid backup data is retained after automatic cleanup. If only one copy of valid backup data exists, this data copy will never be cleaned up.

• When you clean up backup data from Network File System (NFS) or object storage services that are compatible with the Amazon Simple Storage Service (S3) protocol, such as Huawei Object Storage Service (OBS) and Google Cloud Storage (GCS), the system directly deletes backup files that meet the requirements. When you clean up backup data from Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), or S3, the backup file cleanup mode is determined by the delete_mode parameter in data_backup_dest and log_archive_dest. For more information, see Preparations (for log archiving) and Preparations (for data backup).

Set a cleanup strategy

Set a cleanup strategy for a user tenant

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

2. Set a cleanup strategy. This is equivalent to enabling automatic cleanup for the tenant.

   The SQL syntax is as follows:

   obclient> ALTER SYSTEM ADD DELETE BACKUP POLICY policy_name RECOVERY_WINDOW recovery_window;
   

   where:

   • POLICY specifies the name of the cleanup strategy. It can be set to default only, which indicates that automatic cleanup applies only to the data in the backup destinations specified by data_backup_dest and log_archive_dest.

   • RECOVERY_WINDOW specifies the time window for restoring backup data. For more information about this parameter, see recovery_window.

   Here is an example:

   obclient> ALTER SYSTEM ADD DELETE BACKUP POLICY 'default' RECOVERY_WINDOW '7d';
   

Set a cleanup strategy in the sys tenant

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

2. Set a cleanup strategy. This is equivalent to enabling automatic cleanup for the tenant.

   The SQL syntax is as follows:

   obclient> ALTER SYSTEM ADD DELETE BACKUP POLICY policy_name RECOVERY_WINDOW recovery_window TENANT tenant_name;
   

   where:

   • POLICY specifies the name of the cleanup strategy. It can be set to default only, which indicates that automatic cleanup applies only to the data in the backup destinations specified by data_backup_dest and log_archive_dest.

   • RECOVERY_WINDOW specifies the time window for restoring backup data. For more information about this parameter, see recovery_window.

   • TENANT specifies the tenant on which the cleanup strategy takes effect. You can specify the name of only one user tenant.

To set a cleanup strategy for the MySQL tenant from the sys tenant, execute the following statement:

obclient> ALTER SYSTEM ADD DELETE BACKUP POLICY 'default' RECOVERY_WINDOW '7d' TENANT MySQL;

Query configured cleanup strategies

You can query views for the configured cleanup strategies of all tenants from the sys tenant or for the configured cleanup strategies of a user tenant.

1. Log in to the database as an administrator of the sys tenant or a user tenant.

2. Query the configured cleanup strategies.

   • Query the oceanbase.CDB_OB_BACKUP_DELETE_POLICY view for the cleanup strategies of all tenants from the sys tenant.

     Here is an example:

     obclient> SELECT * FROM oceanbase.CDB_OB_BACKUP_DELETE_POLICY;
     +-----------+-------------+-----------------+
     | TENANT_ID | POLICY_NAME | RECOVERY_WINDOW |
     +-----------+-------------+-----------------+
     |      1002 | default     | 7d              |
     +-----------+-------------+-----------------+
     1 row in set
     

   • Query the oceanbase.DBA_OB_BACKUP_DELETE_POLICY or sys.DBA_OB_BACKUP_DELETE_POLICY view for the cleanup strategies of the current user tenant.

     • MySQL tenant

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

     • Oracle tenant

       obclient [SYS]> SELECT * FROM sys.DBA_OB_BACKUP_DELETE_POLICY;
       +-------------+-----------------+
       | POLICY_NAME | RECOVERY_WINDOW |
       +-------------+-----------------+
       | default     | 7d              |
       +-------------+-----------------+
       1 row in set