历史备份无法手动清理

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB】
【 使用版本 】4.3.5.3
obclient(root@sys)[oceanbase]> \s

obclient Ver 2.2.10 Distrib 10.4.18-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 3221603975
Current database: oceanbase
Current user: root@192.168.207.18
SSL: Not in use
Current pager: stdout
Using outfile: ‘’
Using delimiter: ;
Server version: OceanBase 4.3.5.3 (r103040032025092501-bcea1d40bd48deb5da7b483709579d2f8ea84ee6) (Built Sep 25 2025 01:27:34)
Protocol version: 10
Connection: 192.168.207.18 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 2881
Protocol: Compressed
Active --------------
【问题描述】参考文档4.4.1手动清理指定的备份或归档数据
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000003977429
无法清理历史备份，执行命令报错，4.3.5上面不支持该功能吗？如果要手动清理，如何操作？
OBE-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘BACKUPSET 1’ at line 1
和
ERROR 1235 (0A000): need to specify a tenant name not supported
`
[root@ob backup_sets]# obclient -h192.168.207.18 -ugistar@lnzyob -p’res#pwd’ -P2881
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221556778
Server version: OceanBase 4.3.5.3 (r103040032025092501-bcea1d40bd48deb5da7b483709579d2f8ea84ee6) (Built Sep 25 2025 01:27:34)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]> select BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM SYS.DBA_OB_BACKUP_SET_FILES;
±--------------±--------------------±--------------------±--------±---------------------------+
| BACKUP_SET_ID | START_TIMESTAMP | END_TIMESTAMP | STATUS | PATH |
±--------------±--------------------±--------------------±--------±---------------------------+
| 1 | 2025-10-29 13:23:55 | 2025-10-29 14:18:50 | SUCCESS | file:///backup/lnzyob/data |
| 2 | 2025-11-10 10:42:02 | 2025-11-10 11:33:22 | SUCCESS | file:///backup/lnzyob/data |
| 3 | 2025-11-14 11:31:40 | 2025-11-14 12:19:21 | SUCCESS | file:///backup/lnzyob/data |
±--------------±--------------------±--------------------±--------±---------------------------+
3 rows in set (0.110 sec)

obclient(GISTAR@lnzyob)[GISTAR]> ALTER SYSTEM DELETE BACKUPSET 1;
OBE-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘BACKUPSET 1’ at line 1
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]> ALTER SYSTEM DELETE BACKUPSET 1;
OBE-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘BACKUPSET 1’ at line 1
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]> select BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM SYS.DBA_OB_BACKUP_SET_FILES;
±--------------±--------------------±--------------------±--------±---------------------------+
| BACKUP_SET_ID | START_TIMESTAMP | END_TIMESTAMP | STATUS | PATH |
±--------------±--------------------±--------------------±--------±---------------------------+
| 1 | 2025-10-29 13:23:55 | 2025-10-29 14:18:50 | SUCCESS | file:///backup/lnzyob/data |
| 2 | 2025-11-10 10:42:02 | 2025-11-10 11:33:22 | SUCCESS | file:///backup/lnzyob/data |
| 3 | 2025-11-14 11:31:40 | 2025-11-14 12:19:21 | SUCCESS | file:///backup/lnzyob/data |
±--------------±--------------------±--------------------±--------±---------------------------+
3 rows in set (0.004 sec)

obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]> SELECT * FROM sys.DBA_OB_BACKUP_DELETE_POLICY;
±------------±----------------+
| POLICY_NAME | RECOVERY_WINDOW |
±------------±----------------+
| default | 7d |
±------------±----------------+
1 row in set (0.066 sec)

obclient(GISTAR@lnzyob)[GISTAR]> select BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM SYS.DBA_OB_BACKUP_SET_FILES;
±--------------±--------------------±--------------------±--------±---------------------------+
| BACKUP_SET_ID | START_TIMESTAMP | END_TIMESTAMP | STATUS | PATH |
±--------------±--------------------±--------------------±--------±---------------------------+
| 1 | 2025-10-29 13:23:55 | 2025-10-29 14:18:50 | SUCCESS | file:///backup/lnzyob/data |
| 2 | 2025-11-10 10:42:02 | 2025-11-10 11:33:22 | SUCCESS | file:///backup/lnzyob/data |
| 3 | 2025-11-14 11:31:40 | 2025-11-14 12:19:21 | SUCCESS | file:///backup/lnzyob/data |
±--------------±--------------------±--------------------±--------±---------------------------+
3 rows in set (0.031 sec)

obclient(GISTAR@lnzyob)[GISTAR]> ALTER SYSTEM DELETE BACKUPSET 1;
OBE-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘BACKUPSET 1’ at line 1
obclient(GISTAR@lnzyob)[GISTAR]> ALTER SYSTEM DELETE BACKUPSET 1 TENANT = lnzyob;
OBE-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘BACKUPSET 1 TENANT = lnzyob’ at line 1
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]>
obclient(GISTAR@lnzyob)[GISTAR]> exit
Bye
[root@ob backup_sets]# obclient -h192.168.207.18 -P2881 -uroot@sys -p’linux2025.’ -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221603975
Server version: OceanBase 4.3.5.3 (r103040032025092501-bcea1d40bd48deb5da7b483709579d2f8ea84ee6) (Built Sep 25 2025 01:27:34)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

obclient(root@sys)[oceanbase]>
obclient(root@sys)[oceanbase]> SELECT * FROM DBA_OB_BACKUP_DELETE_POLICY;
Empty set (0.006 sec)

obclient(root@sys)[oceanbase]> SELECT * FROM CDB_OB_BACKUP_DELETE_POLICY;
±----------±------------±----------------+
| TENANT_ID | POLICY_NAME | RECOVERY_WINDOW |
±----------±------------±----------------+
| 1002 | default | 7d |
±----------±------------±----------------+
1 row in set (0.019 sec)

obclient(root@sys)[oceanbase]>
obclient(root@sys)[oceanbase]> select BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM SYS.DBA_OB_BACKUP_SET_FILES;
ERROR 1146 (42S02): Table ‘SYS.DBA_OB_BACKUP_SET_FILES’ doesn’t exist
obclient(root@sys)[oceanbase]> select BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM CDB_OB_BACKUP_SET_FILES;
±--------------±---------------------------±---------------------------±--------±---------------------------+
| BACKUP_SET_ID | START_TIMESTAMP | END_TIMESTAMP | STATUS | PATH |
±--------------±---------------------------±---------------------------±--------±---------------------------+
| 1 | 2025-10-29 13:23:54.969878 | 2025-10-29 14:18:49.743895 | SUCCESS | file:///backup/lnzyob/data |
| 2 | 2025-11-10 10:42:01.799105 | 2025-11-10 11:33:21.944992 | SUCCESS | file:///backup/lnzyob/data |
| 3 | 2025-11-14 11:31:39.747239 | 2025-11-14 12:19:21.404765 | SUCCESS | file:///backup/lnzyob/data |
±--------------±---------------------------±---------------------------±--------±---------------------------+
3 rows in set (0.030 sec)

obclient(root@sys)[oceanbase]> select tenant_id,BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM CDB_OB_BACKUP_SET_FILES;
±----------±--------------±---------------------------±---------------------------±--------±---------------------------+
| tenant_id | BACKUP_SET_ID | START_TIMESTAMP | END_TIMESTAMP | STATUS | PATH |
±----------±--------------±---------------------------±---------------------------±--------±---------------------------+
| 1002 | 1 | 2025-10-29 13:23:54.969878 | 2025-10-29 14:18:49.743895 | SUCCESS | file:///backup/lnzyob/data |
| 1002 | 2 | 2025-11-10 10:42:01.799105 | 2025-11-10 11:33:21.944992 | SUCCESS | file:///backup/lnzyob/data |
| 1002 | 3 | 2025-11-14 11:31:39.747239 | 2025-11-14 12:19:21.404765 | SUCCESS | file:///backup/lnzyob/data |
±----------±--------------±---------------------------±---------------------------±--------±---------------------------+
3 rows in set (0.017 sec)

obclient(root@sys)[oceanbase]> SELECT TENANT_NAME,TENANT_ID FROM DBA_OB_TENANTS;
±------------±----------+
| TENANT_NAME | TENANT_ID |
±------------±----------+
| sys | 1 |
| META$1002 | 1001 |
| lnzyob | 1002 |
| META$1004 | 1003 |
| mysqlob | 1004 |
±------------±----------+
5 rows in set (0.005 sec)

obclient(root@sys)[oceanbase]> SELECT TENANT_NAME,TENANT_ID FROM DBA_OB_TENANTS where TENANT_ID=1002;
±------------±----------+
| TENANT_NAME | TENANT_ID |
±------------±----------+
| lnzyob | 1002 |
±------------±----------+
1 row in set (0.013 sec)

obclient(root@sys)[oceanbase]>
obclient(root@sys)[oceanbase]> ALTER SYSTEM DELETE BACKUPSET 1 TENANT = lnzyob;
ERROR 1235 (0A000): need to specify a tenant name not supported
obclient(root@sys)[oceanbase]>
`

#3

手动清理功能在4.4.1支持，之前的版本不支持

image
image1824×640 164 KB

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000004044584

1 个赞
#4

@论坛小助手

#5

根据您提供的信息，您正在尝试在 OceanBase 4.3.5.3 版本中手动清理指定的备份或归档数据，但在执行过程中遇到了错误。根据官方文档的描述，清理指定的备份或归档数据需要遵循一定的步骤和注意事项，以下是针对您问题的具体分析和解决方案：

1. 错误分析

1.1 OBE-00900: You have an error in your SQL syntax

这个错误表明 SQL 语法有问题，通常是因为命令不符合 OceanBase 的语法规范。根据文档中的示例，正确的语法应该是：

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

1.2 ERROR 1235 (0A000): need to specify a tenant name not supported

这个错误提示您需要指定租户名称。这通常发生在没有正确指定租户名称的情况下。

2. 解决方案

2.1 确认租户名称和备份集 ID

首先，确保您已经正确获取了租户名称和备份集 ID。您已经执行了以下查询来获取这些信息：

obclient(root@sys)[oceanbase]> SELECT TENANT_NAME,TENANT_ID FROM DBA_OB_TENANTS where TENANT_ID=1002;

查询结果显示租户名称为 lnzyob，ID 为 1002。

接下来，获取备份集 ID：

obclient(root@sys)[oceanbase]> select tenant_id,BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM CDB_OB_BACKUP_SET_FILES;

查询结果显示备份集 ID 为 1、2 和 3。

2.2 使用正确的语法清理备份集

根据文档中的示例，使用以下命令来清理备份集：

ALTER SYSTEM DELETE BACKUPSET 1 TENANT = lnzyob;

如果需要同时清理多个备份集，可以这样写：

ALTER SYSTEM DELETE BACKUPSET 1,2,3 TENANT = lnzyob;

3. 注意事项

  • 依赖关系：确保删除的备份集不被其他增量备份依赖。例如，如果备份集 1 被备份集 2 依赖，那么在删除备份集 1 之前必须先删除备份集 2。
  • 保留最新备份：如果备份路径是当前正在使用的路径，需要保留一份最新的且能够用于恢复的全量备份。
  • 权限：确保执行清理操作的用户有足够的权限。

4. 示例

假设您需要删除备份集 1 和 2，可以执行以下命令：

obclient(root@sys)[oceanbase]> ALTER SYSTEM DELETE BACKUPSET 1,2 TENANT = lnzyob;

5. 验证

执行清理命令后，可以再次查询备份集列表，确认备份集已被成功删除：

obclient(root@sys)[oceanbase]> select tenant_id,BACKUP_SET_ID,START_TIMESTAMP,END_TIMESTAMP,STATUS,PATH FROM CDB_OB_BACKUP_SET_FILES;

总结

根据文档中的信息和您的操作步骤，确保使用正确的语法和指定租户名称是解决问题的关键。如果您仍然遇到问题，建议检查租户名称和备份集 ID 是否正确，并确保遵循文档中的注意事项。如果问题依旧存在，可以联系 OceanBase 的技术支持获取进一步的帮助。

更多信息请查看:

  1. 使用备份恢复（带日志）功能创建备租户
  2. 使用 OUTFILE 语句导出数据