Manually clean up specified backup or archive data

If you need to manually clean up specific backup or archive data due to business requirements, you can refer to this topic.

Limitations

• When manually cleaning up specified backup or archive data, the user tenant being operated on must not have an automatic cleanup strategy configured. If an automatic cleanup strategy is configured, manual cleanup cannot be performed.

• For scenarios where only log archiving is enabled and no data backup path is configured, you cannot manually clean up specified log archive pieces. Users can only achieve the cleanup of log archive pieces by configuring the automatic cleanup strategy to log_only.

  For detailed operations on configuring the automatic cleanup strategy, see Automatically clean up expired backups.

Clean up a specified data backup set

Considerations

When cleaning up a specified data backup set, note the following considerations:

• Do not break the dependency relationships between data backups. If a data backup is dependent on associated incremental backups, the dependent data backup can only be deleted after all incremental backups that depend on it have been deleted.

• If you need to specify multiple BACKUP_SET_ID for cleanup, ensure all BACKUP_SET_ID are under the same backup path. Cleaning up data backups from multiple paths in the same command is not supported.

• For the currently in-use backup path, you must retain at least one latest full data backup that can be used for recovery. That is, you can delete other backup sets before the one to be cleaned up only after retaining a full backup available for recovery.

• If the data backup set to be deleted is not in the currently in-use backup path, ensure OceanBase Database has access permissions for the corresponding backup path.

Clean up a specified data backup set of a specified user tenant by the system tenant

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

   The connection example below is for reference only; please adjust it according to your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. View tenant information to obtain the tenant's TENANT_ID.

   This document uses the tenant mysql001 as an example. The query example is as follows:

   obclient(root@sys)[(none)]> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'mysql001';
   

   The query result is as follows:

   +-----------+-------------+-------------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE |
   +-----------+-------------+-------------+
   |      1002 | mysql001    | USER        |
   +-----------+-------------+-------------+
   1 row in set
   

   According to the query result, the tenant's TENANT_ID is 1002.

3. Obtain the backup_set_id of the backup set to be cleaned up.

   obclient(root@sys)[(none)]> SELECT TENANT_ID, BACKUP_SET_ID, BACKUP_TYPE, PREV_FULL_BACKUP_SET_ID, PREV_INC_BACKUP_SET_ID, STATUS, FILE_STATUS, START_REPLAY_SCN, START_REPLAY_SCN_DISPLAY, MIN_RESTORE_SCN, MIN_RESTORE_SCN_DISPLAY, PATH FROM oceanbase.CDB_OB_BACKUP_SET_FILES WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+---------------+-------------+-------------------------+------------------------+---------+-------------+---------------------+----------------------------+---------------------+-------------------------------+-------------------------------+
   | TENANT_ID | BACKUP_SET_ID | BACKUP_TYPE | PREV_FULL_BACKUP_SET_ID | PREV_INC_BACKUP_SET_ID | STATUS  | FILE_STATUS | START_REPLAY_SCN    | START_REPLAY_SCN_DISPLAY   | MIN_RESTORE_SCN     | MIN_RESTORE_SCN_DISPLAY       | PATH                          |
   +-----------+---------------+-------------+-------------------------+------------------------+---------+-------------+---------------------+----------------------------+---------------------+-------------------------------+-------------------------------+
   |      1002 |             1 | FULL        |                       0 |                      0 | SUCCESS | AVAILABLE   | 1757326402739265000 | 2025-09-08 18:13:22.739265 | 1757326563751672000 | 2025-09-08 18:16:03.751672000 |  file:///data/nfs/backup/data |
   |      1002 |             2 | INC         |                       1 |                      1 | SUCCESS | AVAILABLE   | 1757326402739265000 | 2025-09-08 18:13:22.739265 | 1757326695246191000 | 2025-09-08 18:18:15.246191000 |  file:///data/nfs/backup/data |
   |      1002 |             3 | FULL        |                       0 |                      0 | SUCCESS | AVAILABLE   | 1757326402739265000 | 2025-09-08 18:13:22.739265 | 1757326826319880000 | 2025-09-08 18:20:26.319880000 |  file:///data/nfs/backup/data |
   |      1002 |             4 | FULL        |                       0 |                      0 | SUCCESS | AVAILABLE   | 1757326402739265000 | 2025-09-08 18:13:22.739265 | 1757326961311034000 | 2025-09-08 18:22:41.311034000 |  file:///data/nfs/backup/data |
   |      1002 |             5 | FULL        |                       0 |                      0 | SUCCESS | AVAILABLE   | 1757326402739265000 | 2025-09-08 18:13:22.739265 | 1757327092469081000 | 2025-09-08 18:24:52.469081000 |  file:///data/nfs/backup/data |
   |      1002 |             6 | FULL        |                       0 |                      0 | SUCCESS | AVAILABLE   | 1757326402739265000 | 2025-09-08 18:13:22.739265 | 1757327219591390000 | 2025-09-08 18:26:59.591390000 |  file:///data/nfs/backup/data |
   +-----------+---------------+-------------+-------------------------+------------------------+---------+-------------+---------------------+----------------------------+---------------------+-------------------------------+-------------------------------+
   6 rows in set
   

   The query results show that under the tenant with ID 1002, five full backups and one incremental backup are all located in the same backup directory. Specifically:

   • Backups with BACKUP_SET_ID 1, 3, 4, 5, and 6 are full backups.
   • The backup with BACKUP_SET_ID 2 is an incremental backup and depends on the full backup with BACKUP_SET_ID 1.

   According to the cleanup requirements:

   • You cannot delete the full backup with BACKUP_SET_ID 1 before deleting the incremental backup with BACKUP_SET_ID 2.

   • If the backup directory where the backup set to be deleted is located is currently in use by the tenant, you must retain the latest full backup data that can be used for recovery. Backups from before this can be deleted.

     Whether a backup is "usable for recovery" depends on whether its required log archive is complete. For example, in this scenario, you can determine whether the full backup with BACKUP_SET_ID 6 can be used for recovery as follows:

     • If backup BACKUP_SET_ID 6 itself contains compensatory logs (the PLUS ARCHIVELOG parameter was specified when initiating the data backup), then this backup set can be used directly for recovery. When deleting the backup set, you need to keep BACKUP_SET_ID 6 and can delete other backup sets from before it.
     • If backup BACKUP_SET_ID 6 does not contain compensatory logs (the PLUS ARCHIVELOG parameter was not specified when initiating the data backup), you need to check the log archive pieces in the current archive directory to ensure there is a complete and continuous log archive piece from START_REPLAY_SCN to MIN_RESTORE_SCN for that backup set. If no such log archive piece exists, it means BACKUP_SET_ID 6 cannot be used for recovery. You then need to confirm whether the previous full backup, BACKUP_SET_ID 5, can be used for recovery using the same method, and so on.

4. Run the following command to delete the specified data backup.

   The statement is as follows:

   ALTER SYSTEM DELETE BACKUPSET backup_set_id[,backup_set_id,...] TENANT [=] tenant_name;
   

   Where:

   • backup_set_id: Specifies the ID of the backup set to be deleted. Multiple data backup sets can be specified in a single command. When cleaning up by specifying multiple backup_set_ids, all backup_set_ids must be in the same backup directory. Cleaning up data backups from multiple directories in the same command is not supported.

   • tenant_name: Specifies the tenant whose backup sets need to be deleted.

   Examples:

   • Delete the data backup set with backup_set_id 3 for tenant mysql001.

     obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUPSET 3 TENANT = mysql001;
     

   • Simultaneously delete three data backup sets with backup_set_id 1, 2, and 4 for tenant mysql001.

     obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUPSET 1,2,4 TENANT = mysql001;
     

User tenant cleans up specified data backup sets of itself

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

   Note

   The administrator user for MySQL-compatible tenants is the root user, and for Oracle-compatible tenants, it is the SYS user.

   The connection example below is for reference only; please adjust it according to your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. Obtain the backup_set_id of the backup set to be cleaned up.

   MySQL-compatible mode

   Oracle-compatible mode

   Example query in MySQL-compatible mode:

   obclient(root@mysql001)[(none)]> SELECT BACKUP_SET_ID, BACKUP_TYPE, PREV_FULL_BACKUP_SET_ID, PREV_INC_BACKUP_SET_ID, STATUS, FILE_STATUS, START_REPLAY_SCN, START_REPLAY_SCN_DISPLAY, MIN_RESTORE_SCN, MIN_RESTORE_SCN_DISPLAY, PATH FROM oceanbase.DBA_OB_BACKUP_SET_FILES;
   

   Example query in Oracle-compatible mode:

   obclient(SYS@oracle001)[SYS]> SELECT BACKUP_SET_ID, BACKUP_TYPE, PREV_FULL_BACKUP_SET_ID, PREV_INC_BACKUP_SET_ID, STATUS, FILE_STATUS, START_REPLAY_SCN, START_REPLAY_SCN_DISPLAY, MIN_RESTORE_SCN, MIN_RESTORE_SCN_DISPLAY, PATH FROM SYS.DBA_OB_BACKUP_SET_FILES;
   

3. Run the following command to delete the specified data backup.

   The statement is as follows:

   ALTER SYSTEM DELETE BACKUPSET backup_set_id[,backup_set_id,...];
   

   Where:

   • backup_set_id: Specifies the ID of the backup set to be deleted. Multiple data backup sets can be specified in a single command, separated by commas. When cleaning up with multiple backup_set_ids, all backup_set_ids must be under the same backup path. Cleaning up data backups from multiple paths in a single command is not supported.

   • tenant_name: Specifies the tenant for which to delete the backup set.

   Examples:

   • Delete the data backup set with backup_set_id 3 for the current tenant.

     obclient> ALTER SYSTEM DELETE BACKUPSET 3;
     

   • Delete the three data backup sets with backup_set_id 1, 2, and 4 for the current tenant simultaneously.

     obclient> ALTER SYSTEM DELETE BACKUPSET 1,2,4;
     

Clean up a specified log archive piece

Considerations

When cleaning up a specified log archive piece, note the following considerations:

• You must clean up log archive pieces in order.

• When cleaning up pieces in the current archive path, ensure that the piece to be cleaned up is not depended on by any data backup in the current data backup path.

  To determine whether a piece to be cleaned up is depended on by a data backup set, perform the following steps:

  1. In the tenant's current backup path, find a data backup set with an older time.

  2. Check the START_REPLAY_SCN of this data backup set.

     • System tenant

       obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_SET_FILES WHERE TENANT_ID = tenant_id AND BACKUP_SET_ID = set_id;
       

     • User tenant

       obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_BACKUP_SET_FILES WHERE BACKUP_SET_ID = set_id;
       

       obclient(SYSt@oracl001)[SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_SET_FILES WHERE BACKUP_SET_ID = set_id;
       

     For detailed operations and instructions, see View data backup results.

  3. Check the END_SCN of the piece to be cleaned up.

     • System tenant

       obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES WHERE TENANT_ID = tenant_id AND PIECE_ID = piece_id;
       

     • User tenant

       obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_ARCHIVELOG_PIECE_FILES WHERE PIECE_ID = piece_id;
       

       obclient(SYSt@oracl001)[SYS]> SELECT * FROM SYS.DBA_OB_ARCHIVELOG_PIECE_FILES WHERE PIECE_ID = piece_id;
       

     For detailed operations and instructions, see View piece information.

  4. Compare the obtained information. If the END_SCN of the piece to be cleaned up is greater than or equal to the START_REPLAY_SCN of a data backup in the current backup path, it means this piece is depended on by this data backup set.

• If you need to specify multiple PIECE_ID for cleanup, ensure all PIECE_ID are under the same archive path. Cleaning up log archive pieces from multiple paths in the same command is not supported.

System tenant cleans up specified log archive pieces for a specified tenant

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

   The following example shows how to connect to a database. Please use the actual database connection information in your production environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Query tenant information and obtain the tenant's TENANT_ID.

   This document uses the tenant named mysql001 as an example. The query statement is as follows:

   obclient(root@sys)[(none)]> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'mysql001';
   

   The query result is as follows:

   +-----------+-------------+-------------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE |
   +-----------+-------------+-------------+
   |      1002 | mysql001    | USER        |
   +-----------+-------------+-------------+
   1 row in set
   

   According to the query result, the TENANT_ID of this tenant is 1002.

3. Obtain the piece_id of the log archive piece to be cleaned up.

   obclient(root@sys)[(none)]> SELECT TENANT_ID, ROUND_ID, PIECE_ID, STATUS, FILE_STATUS, START_SCN, START_SCN_DISPLAY, END_SCN, END_SCN_DISPLAY, PATH FROM oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+----------+----------+--------+-------------+---------------------+----------------------------+---------------------+----------------------------+----------------------------------+
   | TENANT_ID | ROUND_ID | PIECE_ID | STATUS | FILE_STATUS | START_SCN           | START_SCN_DISPLAY          | END_SCN             | END_SCN_DISPLAY            | PATH                             |
   +-----------+----------+----------+--------+-------------+---------------------+----------------------------+---------------------+----------------------------+----------------------------------+
   |      1002 |        1 |        1 | FROZEN | AVAILABLE   | 1757333158537074000 | 2025-09-08 20:05:58.537074 | 1757333218537074000 | 2025-09-08 20:06:58.537074 | file:///data/nfs/backup/archive1 |
   |      1002 |        1 |        2 | FROZEN | AVAILABLE   | 1757333218537074000 | 2025-09-08 20:06:58.537074 | 1757333278537074000 | 2025-09-08 20:07:58.537074 | file:///data/nfs/backup/archive1 |
   |      1002 |        1 |        3 | FROZEN | AVAILABLE   | 1757333278537074000 | 2025-09-08 20:07:58.537074 | 1757333338537074000 | 2025-09-08 20:08:58.537074 | file:///data/nfs/backup/archive1 |
   |      1002 |        1 |        4 | ACTIVE | AVAILABLE   | 1757333338537074000 | 2025-09-08 20:08:58.537074 | 1757333398537074000 | 2025-09-08 20:09:58.537074 | file:///data/nfs/backup/archive1 |
   |      1002 |        2 |        5 | FROZEN | AVAILABLE   | 1757333589335423000 | 2025-09-08 20:13:09.335423 | 1757333649335423000 | 2025-09-08 20:14:09.335423 | file:///data/nfs/backup/archive2 |
   |      1002 |        3 |        6 | FROZEN | AVAILABLE   | 1757333769705693000 | 2025-09-08 20:16:09.705693 | 1757333829705693000 | 2025-09-08 20:17:09.705693 | file:///data/nfs/backup/archive3 |
   |      1002 |        3 |        7 | FROZEN | AVAILABLE   | 1757333829705693000 | 2025-09-08 20:17:09.705693 | 1757333889705693000 | 2025-09-08 20:18:09.705693 | file:///data/nfs/backup/archive3 |
   |      1002 |        3 |        8 | ACTIVE | AVAILABLE   | 1757333889705693000 | 2025-09-08 20:18:09.705693 | 1757333949705693000 | 2025-09-08 20:19:09.705693 | file:///data/nfs/backup/archive3 |
   |      1002 |        3 |        9 | FROZEN | AVAILABLE   | 1757333949705693000 | 2025-09-08 20:19:09.705693 | 1757334009705693000 | 2025-09-08 20:20:09.705693 | file:///data/nfs/backup/archive3 |
   |      1002 |        3 |       10 | FROZEN | AVAILABLE   | 1757334009705693000 | 2025-09-08 20:20:09.705693 | 1757334069705693000 | 2025-09-08 20:21:09.705693 | file:///data/nfs/backup/archive3 |
   +-----------+----------+----------+--------+-------------+---------------------+----------------------------+---------------------+----------------------------+----------------------------------+
   10 rows set
   

   According to the query result, the tenant with ID 1002 has 10 log archive pieces. Specifically:

   • The log archive pieces with PIECE_ID 1, 2, 3, and 4 are from the same archive path: file:///data/nfs/backup/archive1.
   • The log archive piece with PIECE_ID 5 is from the archive path: file:///data/nfs/backup/archive2.
   • The log archive pieces with PIECE_ID 6, 7, 8, 9, and 10 are from the archive path: file:///data/nfs/backup/archive3.

   According to the cleanup requirements:

   • Pieces must be cleaned up in order. For example, you cannot clean up one or more pieces with PIECE_ID 2, 3, or 4 before cleaning up the piece with PIECE_ID 1. Similarly, you cannot clean up any one or more pieces with PIECE_ID 7, 8, 9, or 10 before cleaning up the piece with PIECE_ID 6.

     Multiple pieces can be cleaned up simultaneously in order. For example, pieces with IDs 1, 2, and 3 can be cleaned up together.

   • When specifying multiple PIECE_ID values, all PIECE_ID values must refer to pieces in the same archive path. For example, you cannot clean up pieces with PIECE_ID 1, 2, and 5 or 5, 6, and 7 at the same time.

4. Run the following command to delete the specified log archive piece.

   The statement is as follows:

   ALTER SYSTEM DELETE ARCHIVELOG_PIECE piece_id[,piece_id,...] TENANT [=] tenant_name;
   

   Where:

   • piece_id: the ID of the log archive piece to be deleted. Multiple pieces can be specified in a single command. When specifying multiple piece_id values for cleanup, all piece_id values must refer to pieces in the same backup path. Log archive pieces from multiple paths cannot be cleaned up in the same command.

   • tenant_name: the tenant whose log archive piece is to be deleted.

   Example:

   • Delete the log archive piece with piece_id 5 for tenant mysql001.

     obclient(root@sys)[(none)]> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 5 TENANT = mysql001;
     

   • Delete four log archive pieces with piece_id 6, 7, 8, and 9 for tenant mysql001 simultaneously.

     obclient(root@sys)[(none)]> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 6,7,8,9 TENANT = mysql001;
     

User tenant cleans up specified log archive pieces of itself

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

   Note

   The administrator user of a MySQL-compatible tenant is the root user, and the administrator user of an Oracle-compatible tenant is the SYS user.

   The following is an example of connecting to the database. Please use the actual environment settings when connecting.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. Obtain the piece_id of the log archive piece to be cleaned up.

   MySQL-compatible mode

   Oracle-compatible mode

   The following is an example query in MySQL-compatible mode:

   obclient(root@mysql001)[(none)]> SELECT ROUND_ID, PIECE_ID, STATUS, FILE_STATUS, START_SCN, START_SCN_DISPLAY, END_SCN, END_SCN_DISPLAY, PATH FROM oceanbase.DBA_OB_ARCHIVELOG_PIECE_FILES;
   

   The following is an example query in Oracle-compatible mode:

   obclient(SYS@oracle001)[SYS]> SELECT ROUND_ID, PIECE_ID, STATUS, FILE_STATUS, START_SCN, START_SCN_DISPLAY, END_SCN, END_SCN_DISPLAY, PATH FROM SYS.DBA_OB_ARCHIVELOG_PIECE_FILES;
   

3. Execute the following statement to delete the specified log archive piece.

   The statement is as follows:

   ALTER SYSTEM DELETE ARCHIVELOG_PIECE piece_id[,piece_id,...];
   

   Where:

   • piece_id: specifies the ID of the log archive piece to be deleted. Multiple pieces can be specified in a single command. When cleaning up by specifying multiple piece_id, all piece_id must be under the same backup path. Cleaning up log archive pieces from multiple paths in a single command is not supported.

   • tenant_name: Specifies the tenant for which to delete log archive pieces.

   Examples:

   • Delete the log archive piece with piece_id 5 for the current tenant.

     obclient> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 5;
     

   • Delete four log archive pieces with piece_id 6, 7, 8, and 9 for the current tenant.

     obclient> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 6,7,8,9;
     

References

• Query the backup cleanup progress

• Query the backup cleanup results

• Cancel a cleanup task