归档日志怎样设置自动清理？

[咖啡哥]

【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】
【 使用版本 】4.2.1.8
【问题描述】
为啥设置了备份自动清理7天的数据，归档日志没有自动清理？

OCP备份策略设置如下：

看官网文档，写的意思应该是可以自动清理的：
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001050231

• 由于日志归档数据的清理依赖数据的备份，在清理日志归档数据前，请确认已存在数据备份文件，如果没有数据备份文件，则无法清理日志归档数据。

[root@2883][oceanbase][14:07:29]> select * from oceanbase.CDB_OB_ARCHIVELOG where tenant_id=1036\G
*************************** 1. row ***************************
                   TENANT_ID: 1036
                     DEST_ID: 1001
                    ROUND_ID: 1
                 INCARNATION: 1
                     DEST_NO: 0
                      STATUS: DOING
                   START_SCN: 1718271377188707360
           START_SCN_DISPLAY: 2024-06-13 17:36:17.188707
              CHECKPOINT_SCN: 1724220351339251375
      CHECKPOINT_SCN_DISPLAY: 2024-08-21 14:05:51.339251
                  COMPATIBLE: 1
               BASE_PIECE_ID: 1
               USED_PIECE_ID: 69
       PIECE_SWITCH_INTERVAL: 86400000000
                   UNIT_SIZE: 1
                 COMPRESSION: none
                 INPUT_BYTES: 138644335252
         INPUT_BYTES_DISPLAY: 129.12GB
                OUTPUT_BYTES: 138644335252
        OUTPUT_BYTES_DISPLAY: 129.12GB
           COMPRESSION_RATIO: 1.00
         DELETED_INPUT_BYTES: 0
 DELETED_INPUT_BYTES_DISPLAY: 0.00MB
        DELETED_OUTPUT_BYTES: 0
DELETED_OUTPUT_BYTES_DISPLAY: 0.00MB
                     COMMENT: 
                        PATH: file:///backup/ob_poc/1691672001/tenant_incarnation_1/1036/clog
1 row in set (0.013 sec)

[root@2883][oceanbase][14:07:49]>  SELECT * FROM oceanbase.CDB_OB_BACKUP_DELETE_POLICY where  tenant_id=1036;
+-----------+-------------+-----------------+
| TENANT_ID | POLICY_NAME | RECOVERY_WINDOW |
+-----------+-------------+-----------------+
|      1036 | default     | 7d              |
+-----------+-------------+-----------------+
1 row in set (0.009 sec)

[大飞哥online]

SELECT * from CDB_OB_BACKUP_DELETE_JOB_HISTORY order by end_timestamp DESC;

查看清理历史记录

[大飞哥online]

oceanbase.CDB_OB_ARCHIVELOG这个只是记录从开启归档，就计算了，不包括删除的。

[大飞哥online]

我的是4.2.1.6，版本，是有自动清理的，我是保留31天

[咖啡哥]

SELECT * from CDB_OB_BACKUP_DELETE_JOB_HISTORY where TENANT_ID=1036 order by end_timestamp DESC limit 10;
+-----------+--------+-------------+---------------------+------------------+--------------------+------------------------+----------------------------+-------------+----------------------------+----------------------------+-----------+------------+--------------------+--------+---------+
| TENANT_ID | JOB_ID | INCARNATION | INITIATOR_TENANT_ID | INITIATOR_JOB_ID | EXECUTOR_TENANT_ID | TYPE                   | PARAMETER                  | JOB_LEVEL   | START_TIMESTAMP            | END_TIMESTAMP              | STATUS    | TASK_COUNT | SUCCESS_TASK_COUNT | RESULT | COMMENT |
+-----------+--------+-------------+---------------------+------------------+--------------------+------------------------+----------------------------+-------------+----------------------------+----------------------------+-----------+------------+--------------------+--------+---------+
|      1036 |    167 |           1 |                1036 |              167 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 14:20:51.014831 | USER_TENANT | 2024-08-21 14:20:51.016841 | 2024-08-21 14:20:51.064933 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    166 |           1 |                1036 |              166 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 13:20:50.859983 | USER_TENANT | 2024-08-21 13:20:50.862019 | 2024-08-21 13:20:50.911252 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    165 |           1 |                1036 |              165 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 12:20:50.695960 | USER_TENANT | 2024-08-21 12:20:50.697538 | 2024-08-21 12:20:50.746645 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    164 |           1 |                1036 |              164 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 11:20:50.530011 | USER_TENANT | 2024-08-21 11:20:50.531948 | 2024-08-21 11:20:50.584822 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    163 |           1 |                1036 |              163 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 10:20:50.365728 | USER_TENANT | 2024-08-21 10:20:50.367586 | 2024-08-21 10:20:50.422406 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    162 |           1 |                1036 |              162 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 09:20:50.210025 | USER_TENANT | 2024-08-21 09:20:50.211561 | 2024-08-21 09:20:50.263078 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    161 |           1 |                1036 |              161 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 08:20:50.058788 | USER_TENANT | 2024-08-21 08:20:50.060858 | 2024-08-21 08:20:50.111691 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    160 |           1 |                1036 |              160 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 07:20:49.897405 | USER_TENANT | 2024-08-21 07:20:49.899147 | 2024-08-21 07:20:49.948739 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    159 |           1 |                1036 |              159 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 06:20:49.708919 | USER_TENANT | 2024-08-21 06:20:49.710896 | 2024-08-21 06:20:49.767910 | COMPLETED |          0 |                  0 |      0 |         |
|      1036 |    158 |           1 |                1036 |              158 | 1036               | DELETE OBSOLETE BACKUP | 2024-08-14 05:20:49.442863 | USER_TENANT | 2024-08-21 05:20:49.444817 | 2024-08-21 05:20:49.519147 | COMPLETED |          0 |                  0 |      0 |         |
+-----------+--------+-------------+---------------------+------------------+--------------------+------------------------+----------------------------+-------------+----------------------------+----------------------------+-----------+------------+--------------------+--------+---------+
10 rows in set (0.005 sec)

有清理记录，但磁盘上的文件还在。

[咖啡哥]

select b.tenant_name,a.* from CDB_OB_BACKUP_DELETE_POLICY as a RIGHT JOIN DBA_OB_TENANTS as b on a.tenant_id=b.tenant_id where b.tenant_type=‘USER’ and a.TENANT_ID=1036;

±------------±----------±------------±----------------+
| tenant_name | TENANT_ID | POLICY_NAME | RECOVERY_WINDOW |
±------------±----------±------------±----------------+
| crm | 1036 | default | 7d |
±------------±----------±------------±----------------+
1 row in set (0.015 sec)

[咖啡哥]

我原始版本是4.2.1.3，昨天升级的8.

[大飞哥online]

OCP上的备份策略里存储目录，写的什么路径

[咖啡哥]

/backup/

写的这个，clog、data的备份路径都是ocp上设置后自动生成的。

现在clog下面还有6月份的数据。

如果手动清理有命令吗？还是直接删除OS上的文件目录

[辞霜]

从ocp查看备份大小目前不会缩小，这是个已知问题后续会进行修复

[旭辉]

你可以服务器上 du 一下归档日志目录，应该是是清理掉了（会有少许残留文件），另外ocp上有个已知bug：显示的备份存储趋势不会下降，麻烦发下你的ocp版本 确认下

[大飞哥online]

直接删除OS上的文件目录

[咖啡哥]

是个测试环境，每天400M+，没有清理。

[旭辉]

应该不符合预期，超过保留期限的备份数据应该被清理掉，我反馈下，有进展回复你

[旭辉]

排查下是否存在如下两种情况

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

[旭辉]

上面两项检查有问题吗？

[咖啡哥]

检查了，没问题。
1、因为是测试环境，数据量不大，每天都有全备；
2、个别租户之前设置过其他路径，原来路径下面的文件都手动删了。是当前路径下面的没删。

我手动删了部分归档日志，暂时空间问题缓解了，但是没有自动清理。

备份盘是nfs

[旭辉]

查下这个视图，看下是否有日志文件的清理记录

select * from CDB_OB_BACKUP_DELETE_TASK_HISTORY where TENANT_ID=1036 order by end_timestamp;