Query the backup cleanup history

After a cleanup strategy is configured and automatic cleanup is triggered, expired backup data will be automatically cleaned up based on the strategy. You can query views for the backup cleanup history.

Query views for the backup cleanup history

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

2. Query historical cleanup jobs.

   • Query the historical backup cleanup jobs.

     You can query the oceanbase.CDB_OB_BACKUP_DELETE_JOB_HISTORY view from the sys tenant for the historical cleanup jobs of all tenants. You can query the oceanbase.DBA_OB_BACKUP_DELETE_JOB_HISTORY view from a MySQL tenant or the sys.DBA_OB_BACKUP_DELETE_JOB_HISTORY view from an Oracle tenant for the historical cleanup jobs of the current tenant.

     To query the historical cleanup jobs from the sys tenant, execute the following statement:

     obclient> SELECT * FROM CDB_OB_BACKUP_DELETE_JOB_HISTORY\G
     *************************** 1. row ***************************
                    TENANT_ID: 1002
                       JOB_ID: 7
                  INCARNATION: 1
          INITIATOR_TENANT_ID: 1002
             INITIATOR_JOB_ID: 7
           EXECUTOR_TENANT_ID: 1002
                         TYPE: DELETE OBSOLETE BACKUP
                    PARAMETER: 2022-05-31 12:09:40.060284
                    JOB_LEVEL: USER_TENANT
              START_TIMESTAMP: 2022-06-01 12:09:53.389512
                END_TIMESTAMP: 2022-06-01 12:10:36.829576
                       STATUS: COMPLETED
                   TASK_COUNT: 2
           SUCCESS_TASK_COUNT: 2
                       RESULT: 0
                      COMMENT:
     1 row in set
     

     According to the query result, the user tenant with the tenant ID 1002 initiated a job for cleaning up expired backup data at 2022-06-01 12:09:53.389512, and the job was completed at 2022-06-01 12:10:36.829576. The success rate of the tasks under the job is 100%, which is calculated by using the following formula: TASK_COUNT/SUCCESS_TASK_COUNT × 100%. The value of RESULT is 0, indicating that the job was successfully executed.

   • Query historical backup set/back piece cleanup tasks.

     You can query the oceanbase.CDB_OB_BACKUP_DELETE_TASK_HISTORY view from the sys tenant for the historical cleanup tasks of all tenants. You can query the oceanbase.DBA_OB_BACKUP_DELETE_TASK_HISTORY view from a MySQL tenant or the sys.DBA_OB_BACKUP_DELETE_TASK_HISTORY view from an Oracle tenant for the historical cleanup tasks of the current tenant.

     To query historical backup set/back piece cleanup tasks from the sys tenant, execute the following statement:

     obclient> SELECT * FROM oceanbase.CDB_OB_BACKUP_DELETE_TASK_HISTORY\G
     *************************** 1. row ***************************
              TENANT_ID: 1002
                TASK_ID: 4
            INCARNATION: 1
                 JOB_ID: 7
              TASK_TYPE: BACKUP SET
                     ID: 1
               ROUND_ID: 0
                DEST_ID: 1002
        START_TIMESTAMP: 2022-06-01 12:09:50.800772
          END_TIMESTAMP: 2022-06-01 12:09:54.954691
                 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: 5
            INCARNATION: 1
                 JOB_ID: 7
              TASK_TYPE: BACKUP PIECE
                     ID: 1
               ROUND_ID: 1
                DEST_ID: 1001
        START_TIMESTAMP: 2022-06-01 12:09:50.968413
          END_TIMESTAMP: 2022-06-01 12:09:56.283804
                 STATUS: COMPLETED
         TOTAL_LS_COUNT: 2
        FINISH_LS_COUNT: 2
                 RESULT: 0
                COMMENT:
                   PATH: file:///data/nfs/backup/archive
     2 rows in set
     

     According to the query result, the backup file of the data backup with backup_set_id=1 in the destination file:///data/nfs/backup/data was deleted at 2022-06-01 12:09:54.954691. In the destination file:///data/nfs/backup/archive, the backup file of archive logs with round_id=1/backup_piece_id=1 was deleted at 2022-06-01 12:09:56.283804. You can query the information about initiated cleanup tasks in the job-level view based on JOB_ID.