Manually clean up specified backup or archive data|V4.4.2| docs|Distributed Database

Manually clean up specified backup or archive data

Last Updated：2026-03-06 07:02:42 Updated

When you need to clean up specific backup or archive data, you can refer to this topic to manually clean up the specified data backup set or log archive piece.

Limitations

When you manually clean up specified backup or archive data, the user tenant to which the data belongs must have an automatic cleanup strategy configured. Otherwise, the cleanup cannot be performed.
In a scenario where only log archiving is enabled and no data backup path is configured, you cannot manually clean up specified log archive pieces. To clean up log archive pieces, you must configure the automatic cleanup strategy to log_only.

For more information about how to configure the automatic cleanup strategy, see Clean up expired backups automatically.

Clean up a specified data backup set

Considerations

When you clean up a specified data backup set, consider the following:

You cannot break the dependency relationships between data backups. If a data backup is depended on by an incremental backup, you can delete the data backup only after you delete all incremental backups that depend on it.
If you need to specify multiple BACKUP_SET_ID values for cleanup, all specified BACKUP_SET_ID values must be in the same backup path. You cannot clean up data backups in different backup paths in the same statement.
For the current backup path, you must retain at least one full backup that can be used for recovery. That is, you can delete data backups in earlier backup sets only after you retain a full backup that can be used for recovery.
If the data backup set to be deleted is not in the current backup path, you must have the access permission on the corresponding backup path.

Clean up a specified data backup set of a user tenant

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

The following example shows how to connect to the database. Replace the actual environment parameters as needed.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

Query the TENANT_ID of the user tenant.

The following example shows how to query the TENANT_ID of the mysql001 tenant:

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

The TENANT_ID of the mysql001 tenant is 1002.

Query the backup_set_id of the data 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

Based on the query results, for the tenant with ID 1002, there are 5 full backups and 1 incremental backup stored in the same backup path. Specifically:

The backups with BACKUP_SET_ID values of 1, 3, 4, 5, and 6 are full backups.
The backup with BACKUP_SET_ID value of 2 is an incremental backup, and it depends on the full backup with BACKUP_SET_ID value of 1.

According to the cleanup requirements:

The full backup with BACKUP_SET_ID value of 1 cannot be deleted until the incremental backup with BACKUP_SET_ID value of 2 is deleted.
If the backup path of the backup set to be deleted is the current backup path of the tenant, you must retain one full backup that is the most recent and can be used for recovery. The earlier backups can be deleted.

A backup can be used for recovery only if the required log archives are complete. For example, to determine whether the full backup with BACKUP_SET_ID value of 6 can be used for recovery, you can do the following:
- If the backup set with BACKUP_SET_ID value of 6 contains compensating logs (the PLUS ARCHIVELOG parameter was specified when the data backup was initiated), then this backup set can be used for recovery. When you delete this backup set, you can delete other backup sets created before it.
- If the backup set with BACKUP_SET_ID value of 6 does not contain compensating logs (the PLUS ARCHIVELOG parameter was not specified when the data backup was initiated), you must check the log archives in the current archive path to ensure that there are complete and continuous log archives from the START_REPLAY_SCN to the MIN_RESTORE_SCN of this backup set. If such log archives do not exist, this backup set cannot be used for recovery. In this case, you must use the same method to determine whether the previous full backup with BACKUP_SET_ID value of 5 can be used for recovery, and so on.

Execute the following statement to delete the specified data backup.

Statement:
```
ALTER SYSTEM DELETE BACKUPSET backup_set_id[,backup_set_id,...] TENANT [=] tenant_name;
```
Parameters:
- backup_set_id: specifies the ID of the backup set to be deleted. Multiple backup sets can be specified in a single statement. When multiple backup sets are specified in a single statement, all the specified backup sets must be stored in the same backup path. You cannot delete backup sets stored in different paths in a single statement.
- tenant_name: specifies the tenant to which the backup set belongs.
Here are some examples:
- Delete the data backup set with backup_set_id value of 3 in the mysql001 tenant.
```
obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUPSET 3 TENANT = mysql001;
```
- Delete the data backup sets with backup_set_id values of 1, 2, and 4 in the mysql001 tenant.
```
obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUPSET 1,2,4 TENANT = mysql001;
```

Clean up data backup sets in the current user tenant

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

The following is an example of connecting to the database. Please replace the parameters with those in your environment.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
```

Obtain the backup_set_id of the backup set to be deleted.

MySQL-compatible mode

Oracle-compatible mode

The following is an example of querying the backup set 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;

The following is an example of querying the backup set 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;

Execute the following command to delete the specified data backup set.

The statement is as follows:
```
ALTER SYSTEM DELETE BACKUPSET backup_set_id[,backup_set_id,...];
```
The parameters are described as follows:
- backup_set_id: the ID of the backup set to be deleted. Multiple data backup sets can be specified in a single command, with different backup_set_id values separated by commas. When multiple backup_set_id values are specified, all backup_set_id values must belong to the same backup path. Multiple data backup sets from different paths cannot be deleted in a single command.
- tenant_name: the tenant to which the backup set to be deleted belongs.
Here are some examples:
- Delete the data backup set with backup_set_id 3 in the current user tenant.
```
obclient> ALTER SYSTEM DELETE BACKUPSET 3;
```
- Delete three data backup sets with backup_set_id 1, 2, and 4 in the current user tenant.
```
obclient> ALTER SYSTEM DELETE BACKUPSET 1,2,4;
```

Clean up the specified log archive piece

Considerations

When you clean up an archive log piece, note the following:

You must clean up the archive log pieces in order.
When you clean up an archive log piece in the current archive log path, the archive log piece must not be referenced by any data backup in the current data backup path.

To determine whether an archive log piece is referenced by a data backup set, perform the following steps:
1. In the current backup path of the tenant, find a data backup set that is created at an earlier time.
2. View the START_REPLAY_SCN of the data backup set.
  - For the sys tenant:
```
obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_SET_FILES WHERE TENANT_ID = tenant_id AND BACKUP_SET_ID = set_id;
```
  - For a 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 more information, see View data backup results.
3. View the END_SCN of the archive log piece.
  - For the sys tenant:
```
obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES WHERE TENANT_ID = tenant_id AND PIECE_ID = piece_id;
```
  - For a 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 more information, see View piece information.
4. Compare the END_SCN of the archive log piece with the START_REPLAY_SCN of the data backup set. If the END_SCN of the archive log piece is greater than or equal to the START_REPLAY_SCN of the data backup set, the archive log piece is referenced by the data backup set.
If you want to clean up multiple PIECE_IDs, all PIECE_IDs must be in the same archive log path. You cannot clean up archive log pieces in different paths in the same command.

Clean up log archive pieces of a specified tenant

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

The following example shows how to connect to the database. Please replace the actual environment variables.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

Query the TENANT_ID of the tenant.

The following example shows how to query the TENANT_ID of the mysql001 tenant:

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

The TENANT_ID of the mysql001 tenant is 1002.

Query 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

Based on the query results, the tenant with ID 1002 has 10 log archive pieces, as follows:

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

According to the cleanup requirements:

You must clean up the pieces in sequence. For example, you cannot clean up one or more pieces with PIECE_ID values 2, 3, or 4 before cleaning up the piece with PIECE_ID value 1. Similarly, you cannot clean up one or more pieces with PIECE_ID values 7, 8, 9, or 10 before cleaning up the piece with PIECE_ID value 6.

You can clean up multiple pieces in sequence. For example, you can clean up pieces with PIECE_ID values 1, 2, and 3 at the same time.
When you specify multiple PIECE_ID values, all of them must be in the same archive path. For example, you cannot clean up pieces with PIECE_ID values 1, 2, and 5, or 5, 6, and 7, at the same time.

Execute 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: specifies the ID of the log archive piece to be deleted. You can specify multiple piece_id values in a single statement. When you specify multiple piece_id values for deletion, all of them must be in the same archive path. You cannot delete log archive pieces from multiple paths in a single statement.
- tenant_name: specifies the tenant to which the log archive piece to be deleted belongs.
Here are some examples:
- Delete the log archive piece with piece_id value 5 in the mysql001 tenant.
```
obclient(root@sys)[(none)]> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 5 TENANT = mysql001;
```
- Delete four log archive pieces with piece_id values 6, 7, 8, and 9 in the mysql001 tenant.
```
obclient(root@sys)[(none)]> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 6,7,8,9 TENANT = mysql001;
```

Clean up log archive pieces in the current tenant

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

The following is an example of connecting to the database. Please replace the parameters with those in your environment.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
```

Query the piece_id of the log archive piece to be deleted.

MySQL

Oracle

The following example shows how to query the piece_id 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 example shows how to query the piece_id 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;

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. You can specify multiple piece_id values in one statement. When you specify multiple piece_id values, all the specified piece_id values must be in the same backup path. You cannot delete log archive pieces in different paths in one statement.
- tenant_name: specifies the tenant to which the log archive piece belongs.
Here are some examples:
- Delete the log archive piece with piece_id 5 in the current tenant.
```
obclient> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 5;
```
- Delete four log archive pieces with piece_id 6, 7, 8, and 9 in the current tenant.
```
obclient> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 6,7,8,9;
```

Manually clean up specified backup or archive data

Limitations

Clean up a specified data backup set

Considerations

Clean up a specified data backup set of a user tenant

Clean up data backup sets in the current user tenant

Note

Clean up the specified log archive piece

Considerations

Clean up log archive pieces of a specified tenant

Clean up log archive pieces in the current tenant

Note

Related Documents