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

If you need to clean up specific backups or archives for business reasons, refer to this topic to manually clean up specified data backup sets or log archive pieces.

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 are not allowed to manually clean up specified log archive pieces. Users can only achieve the cleanup of log archive pieces by configuring the automatic cleanup strategy as 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 depended on by associated incremental backups, the depended-on data backup can be deleted only 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 a single command is not supported.
For the currently used backup path, you must retain at least one latest full data backup that can be used for restore. After retaining one restorable full backup, you can delete other backup sets that are older than that backup set.
If the data backup set to be deleted is not in the currently used backup path, ensure OceanBase Database has access permissions for that backup path.

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

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

The connection example below is for reference only; please adjust according to your actual environment when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

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.

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 path. 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 path where the backup set to be deleted is located is the current backup path being used by the tenant, you must retain the latest full backup data that can be used for recovery, and the earlier data backups 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 retain BACKUP_SET_ID 6 and can delete other backup sets 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 path 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.

Execute 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 path. Cleaning up data backups from multiple paths 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 for itself

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

Note

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

The connection example below is for reference only; please use the actual environment settings when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
```

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

MySQL mode

Oracle mode

Example query in MySQL 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 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;

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 current backup path of the tenant, find a data backup set with a relatively old 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 any data backup in the current backup path, it means this piece is depended on by that 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.

Clean up specified log archive pieces for a specified tenant in the system tenant

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

The following example shows how to connect to a database. You must modify the parameters in the example according to your actual database configuration.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

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.

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:

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.
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 1, 2, and 3 can be cleaned up together.
When specifying multiple PIECE_IDs, all PIECE_IDs must be from 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.

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 cleaning up by specifying multiple piece_ids, all piece_ids must be from the same backup path. Cleaning up log archive pieces from multiple paths in the same command is not supported.
- tenant_name: The tenant containing the log archive piece to be deleted.
Examples:
- Delete the log archive piece with piece_id 5 for tenant mysql001.
```
obclient(root@sys)[(none)]> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 5 TENANT = mysql001;
```
- Simultaneously delete four log archive pieces with piece_id 6, 7, 8, and 9 for tenant mysql001.
```
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

The tenant administrator of the user tenant logs in to the database.

Note

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

The connection example below is for reference only; please use your actual environment settings when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
```

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

MySQL-compatible mode

Oracle-compatible mode

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;

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;

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,...];
```
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 from 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 simultaneously.
```
obclient> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 6,7,8,9;
```

References

If you need to clean up specific backups or archives for business reasons, refer to this topic to manually clean up specified data backup sets or log archive pieces.

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 are not allowed to manually clean up specified log archive pieces. Users can only achieve the cleanup of log archive pieces by configuring the automatic cleanup strategy as 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 depended on by associated incremental backups, the depended-on data backup can be deleted only 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 a single command is not supported.
For the currently used backup path, you must retain at least one latest full data backup that can be used for restore. After retaining one restorable full backup, you can delete other backup sets that are older than that backup set.
If the data backup set to be deleted is not in the currently used backup path, ensure OceanBase Database has access permissions for that backup path.

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

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

The connection example below is for reference only; please adjust according to your actual environment when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

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.

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 path. 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 path where the backup set to be deleted is located is the current backup path being used by the tenant, you must retain the latest full backup data that can be used for recovery, and the earlier data backups 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 retain BACKUP_SET_ID 6 and can delete other backup sets 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 path 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.

Execute 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 path. Cleaning up data backups from multiple paths 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 for itself

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

Note

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

The connection example below is for reference only; please use the actual environment settings when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
```

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

MySQL mode

Oracle mode

Example query in MySQL 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 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;

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 current backup path of the tenant, find a data backup set with a relatively old 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 any data backup in the current backup path, it means this piece is depended on by that 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.

Clean up specified log archive pieces for a specified tenant in the system tenant

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

The following example shows how to connect to a database. You must modify the parameters in the example according to your actual database configuration.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

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.

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:

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.
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 1, 2, and 3 can be cleaned up together.
When specifying multiple PIECE_IDs, all PIECE_IDs must be from 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.

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 cleaning up by specifying multiple piece_ids, all piece_ids must be from the same backup path. Cleaning up log archive pieces from multiple paths in the same command is not supported.
- tenant_name: The tenant containing the log archive piece to be deleted.
Examples:
- Delete the log archive piece with piece_id 5 for tenant mysql001.
```
obclient(root@sys)[(none)]> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 5 TENANT = mysql001;
```
- Simultaneously delete four log archive pieces with piece_id 6, 7, 8, and 9 for tenant mysql001.
```
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

The tenant administrator of the user tenant logs in to the database.

Note

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

The connection example below is for reference only; please use your actual environment settings when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
```

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

MySQL-compatible mode

Oracle-compatible mode

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;

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;

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,...];
```
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 from 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 simultaneously.
```
obclient> ALTER SYSTEM DELETE ARCHIVELOG_PIECE 6,7,8,9;
```

OceanBase

Customer Stories

Documentation

Manually clean up specified backup or archive data

Limitations

Clean up a specified data backup set

Considerations

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

User tenant cleans up specified data backup sets for itself

Note

Clean up a specified log archive piece

Considerations

Clean up specified log archive pieces for a specified tenant in the system tenant

User tenant cleans up specified log archive pieces of itself

Note

References

Manually clean up specified backup or archive data

Limitations

Clean up a specified data backup set

Considerations

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

User tenant cleans up specified data backup sets for itself

Note

Clean up a specified log archive piece

Considerations

Clean up specified log archive pieces for a specified tenant in the system tenant

User tenant cleans up specified log archive pieces of itself

Note

References