Query the backup cleanup results

After a cleanup strategy is configured and automatic cleanup is triggered, when a cleanup job is completed, you can query the execution result of the cleanup job from history views.

Query the cleanup job history from the sys tenant

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

   The connection example is as follows. Connect to the database based on your actual environment.

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

2. Query the historical cleanup jobs.

   • Query the historical Job-level cleanup jobs.

     Job-level cleanup jobs record information about the initiation of cleanup jobs.

     obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_DELETE_JOB_HISTORY WHERE TENANT_ID=1002\G
     

     The following is a sample query result:

     *************************** 1. row ***************************
               TENANT_ID: 1002
                  JOB_ID: 4
             INCARNATION: 1
     INITIATOR_TENANT_ID: 1002
        INITIATOR_JOB_ID: 0
      EXECUTOR_TENANT_ID: 1002
                    TYPE: DELETE BACKUPSET
               PARAMETER: backup_set_id:2,1
               JOB_LEVEL: USER_TENANT
         START_TIMESTAMP: 2025-09-15 14:06:55.225984
           END_TIMESTAMP: 2025-09-15 14:07:13.253627
                  STATUS: FAILED
              TASK_COUNT: 0
      SUCCESS_TASK_COUNT: 0
                  RESULT: -9045
                 COMMENT: delete backup set do not allow; cannot_delete_backup_set_in_current_path_when_delete_policy_is_set
     *************************** 2. row ***************************
               TENANT_ID: 1002
                  JOB_ID: 6
             INCARNATION: 1
     INITIATOR_TENANT_ID: 1002
        INITIATOR_JOB_ID: 0
      EXECUTOR_TENANT_ID: 1002
                    TYPE: DELETE OBSOLETE BACKUP
               PARAMETER: expired_time:2025-09-14 14:30:13.309380
               JOB_LEVEL: USER_TENANT
         START_TIMESTAMP: 2025-09-15 14:30:13.372683
           END_TIMESTAMP: 2025-09-15 14:30:13.407852
                  STATUS: COMPLETED
              TASK_COUNT: 1
      SUCCESS_TASK_COUNT: 1
                  RESULT: 0
                 COMMENT:
     *************************** 3. row ***************************
               TENANT_ID: 1002
                  JOB_ID: 7
             INCARNATION: 1
     INITIATOR_TENANT_ID: 1002
        INITIATOR_JOB_ID: 0
      EXECUTOR_TENANT_ID: 1002
                    TYPE: DELETE BACKUPSET
               PARAMETER: backup_set_id:2,1
               JOB_LEVEL: USER_TENANT
         START_TIMESTAMP: 2025-09-15 14:44:09.511412
           END_TIMESTAMP: 2025-09-15 14:44:47.328929
                  STATUS: COMPLETED
              TASK_COUNT: 2
      SUCCESS_TASK_COUNT: 2
                  RESULT: 0
                 COMMENT:
     *************************** 4. row ***************************
               TENANT_ID: 1002
                  JOB_ID: 8
             INCARNATION: 1
     INITIATOR_TENANT_ID: 1002
        INITIATOR_JOB_ID: 0
      EXECUTOR_TENANT_ID: 1002
                    TYPE: DELETE BACKUP ALL
               PARAMETER: data_backup_dest:file:///data/nfs/backup/data
               JOB_LEVEL: USER_TENANT
         START_TIMESTAMP: 2025-09-15 14:47:45.835726
           END_TIMESTAMP: 2025-09-15 14:48:19.354828
                  STATUS: COMPLETED
              TASK_COUNT: 1
      SUCCESS_TASK_COUNT: 1
                  RESULT: 0
                 COMMENT:
     *************************** 5. row ***************************
               TENANT_ID: 1002
                  JOB_ID: 9
             INCARNATION: 1
     INITIATOR_TENANT_ID: 1002
        INITIATOR_JOB_ID: 0
      EXECUTOR_TENANT_ID: 1002
                    TYPE: DELETE ARCHIVELOG_PIECE
               PARAMETER: archivelog_piece_id:1
               JOB_LEVEL: USER_TENANT
         START_TIMESTAMP: 2025-09-16 13:50:43.315505
           END_TIMESTAMP: 2025-09-16 13:51:27.427522
                  STATUS: COMPLETED
              TASK_COUNT: 1
      SUCCESS_TASK_COUNT: 1
                  RESULT: 0
                 COMMENT:
     5 rows in set
     

     As shown in the query result, the user tenant with the ID 1002 initiated four cleanup jobs:

     • As shown in the first row, the tenant initiated a cleanup job to delete the specified backup sets at 2025-09-15 14:06:55.225984. The values of backup_set_id of the backup sets to be deleted are 1 and 2. The job was completed at 2025-09-15 14:07:13.253627. The value of RESULT is -9045, indicating that the job failed to be executed. As shown by the COMMENT field, the failure occurred because the current tenant has configured an automatic cleanup strategy. After an automatic cleanup strategy is configured, manual cleanup is not supported.

     • As shown in the second row, the tenant initiated a cleanup job for expired backup data at 2025-09-15 14:30:13.372683. The expiration time of the backup data is 2025-09-14 14:30:13.309380. The job was completed at 2025-09-15 14:30:13.407852. The success rate of the subtasks in the job is 100% (TASK_COUNT/SUCCESS_TASK_COUNT). The value of RESULT is 0, indicating that the job was successfully executed.

     • As shown in the third row, the tenant initiated a cleanup job to delete the specified backup sets at 2025-09-15 14:44:09.511412. The values of backup_set_id of the backup sets to be deleted are 1 and 2. The job was completed at 2025-09-15 14:44:47.328929. The value of RESULT is 0, indicating that the job was successfully executed.

     • As shown in the fourth row, the tenant initiated a cleanup job to clear the specified backup path at 2025-09-15 14:47:45.835726. The backup path to be cleared is file:///data/nfs/backup/data. The job was completed at 2025-09-15 14:48:19.354828. The value of RESULT is 0, indicating that the job was successfully executed.

     • As shown in the fifth row, the tenant initiated a cleanup job to delete the specified log archive piece at 2025-09-16 13:50:43.315505. The piece_id of the log archive piece to be deleted is 1. The job was completed at 2025-09-16 13:51:27.427522. The value of RESULT is 0, indicating that the job was successfully executed.

   • Query the historical Task-level cleanup jobs.

     Task-level cleanup jobs record the detailed information about the cleanup of backup_set/backup_piece.

     obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_DELETE_TASK_HISTORY WHERE TENANT_ID=1002\G
     

     The query result is as follows:

     *************************** 1. row ***************************
           TENANT_ID: 1002
             TASK_ID: 5
         INCARNATION: 1
              JOB_ID: 7
           TASK_TYPE: BACKUP SET
                  ID: 2
            ROUND_ID: 0
             DEST_ID: 1002
     START_TIMESTAMP: 2025-09-15 14:44:13.534722
       END_TIMESTAMP: 2025-09-15 14:44:45.294097
              STATUS: COMPLETED
      TOTAL_LS_COUNT: 2
     FINISH_LS_COUNT: 2
              RESULT: 0
             COMMENT:
                PATH: file:///data/nfs/backup/data
     *************************** 2. row ***************************
           TENANT_ID: 1002
             TASK_ID: 6
         INCARNATION: 1
              JOB_ID: 7
           TASK_TYPE: BACKUP SET
                  ID: 1
            ROUND_ID: 0
             DEST_ID: 1002
     START_TIMESTAMP: 2025-09-15 14:44:13.540341
       END_TIMESTAMP: 2025-09-15 14:44:47.322651
              STATUS: COMPLETED
      TOTAL_LS_COUNT: 2
     FINISH_LS_COUNT: 2
              RESULT: 0
             COMMENT:
                PATH: file:///data/nfs/backup/data
     *************************** 3. row ***************************
           TENANT_ID: 1002
             TASK_ID: 7
         INCARNATION: 1
              JOB_ID: 8
           TASK_TYPE: BACKUP SET
                  ID: 3
            ROUND_ID: 0
             DEST_ID: 1002
     START_TIMESTAMP: 2025-09-15 14:47:47.412430
       END_TIMESTAMP: 2025-09-15 14:48:19.347954
              STATUS: COMPLETED
      TOTAL_LS_COUNT: 2
     FINISH_LS_COUNT: 2
              RESULT: 0
             COMMENT:
                PATH: file:///data/nfs/backup/data
     *************************** 4. row ***************************
           TENANT_ID: 1002
             TASK_ID: 8
         INCARNATION: 1
              JOB_ID: 9
           TASK_TYPE: BACKUP PIECE
                  ID: 1
            ROUND_ID: 1
             DEST_ID: 1001
     START_TIMESTAMP: 2025-09-16 13:50:55.286400
       END_TIMESTAMP: 2025-09-16 13:51:27.419650
              STATUS: COMPLETED
      TOTAL_LS_COUNT: 2
     FINISH_LS_COUNT: 2
              RESULT: 0
             COMMENT:
                PATH: file:///data/nfs/backup/archive
     4 rows in set
     

     As shown in the query result, for the user tenant with the ID 1002, the backup files with backup_set_id=1, backup_set_id=2, and backup_set_id=3 in the data backup at the destination file:///data/nfs/backup/data have been successfully cleaned up. In addition, the backup files with round_id=1/backup_piece_id=1 in the log archive at the archive destination file:///data/nfs/backup/archive have also been successfully cleaned up. You can query the information about the initiation of the cleanup jobs in the Job-level job view by using JOB_ID.

Query the cleanup job history from a user tenant

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

   Note

   The administrator user is root in MySQL-compatible mode and SYS in Oracle-compatible mode.

   The connection example is as follows. Connect to the database based on your actual environment.

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

2. Query the historical cleanup jobs.

   • Query the historical Job-level cleanup jobs.

     Job-level cleanup jobs record information about the initiation of cleanup jobs.

     MySQL-compatible mode

     Oracle-compatible mode

     The query example in MySQL-compatible mode is as follows:

     obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_BACKUP_DELETE_JOB_HISTORY\G
     

     The query example in Oracle-compatible mode is as follows:

     obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_DELETE_JOB_HISTORY\G
     

     Except for the missing TENANT_ID field, all fields in the DBA_OB_BACKUP_DELETE_JOB_HISTORY view correspond to those in the CDB_OB_BACKUP_DELETE_JOB_HISTORY view. For more information about the query result, see Query the cleanup job history from the sys tenant.

   • Query the historical Task-level cleanup jobs.

     Task-level cleanup jobs record the detailed information about the cleanup of backup_set/backup_piece.

     MySQL-compatible mode

     Oracle-compatible mode

     The query example in MySQL-compatible mode is as follows:

     obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_BACKUP_DELETE_TASK_HISTORY\G
     

     The query example in Oracle-compatible mode is as follows:

     obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_DELETE_TASK_HISTORY\G
     

     Except for the missing TENANT_ID field, all fields in the DBA_OB_BACKUP_DELETE_TASK_HISTORY view correspond to those in the CDB_OB_BACKUP_DELETE_TASK_HISTORY view. For more information about the query result, see Query the cleanup job history from the sys tenant.

References

Query the backup cleanup progress