View information about pieces

After you enable log archiving, you can query for information about pieces from the corresponding view.

Background

This topic describes how to view pieces in log archiving. For more information about pieces, see Overview.

View all pieces of all tenants from the sys tenant

You can query the oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES view from the sys tenant for all pieces of all tenants in the current cluster.

1. Log on to the sys tenant as the root user.

2. Execute the following statement to view all pieces of all tenants.

   For example:

   obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES\G
   *************************** 1. row ***************************
                TENANT_ID: 1002
                  DEST_ID: 1002
                 ROUND_ID: 1
                 PIECE_ID: 1
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: FROZEN
                START_SCN: 1654034400000000000
        START_SCN_DISPLAY: 2022-06-01 06:00:00.000000
           CHECKPOINT_SCN: 1654120740000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-02 05:59:00.000000
                  MAX_SCN: 1654120770000000000
                  END_SCN: 1654120800000000000
               COMPATIBLE: 1
              INPUT_BYTES: 26843545600
      INPUT_BYTES_DISPLAY: 25.00GB
             OUTPUT_BYTES: 26843545600
     OUTPUT_BYTES_DISPLAY: 25.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: DELETED
                     PATH: file:///data/nfs/backup/archive
   *************************** 2. row ***************************
                TENANT_ID: 1002
                  DEST_ID: 1002
                 ROUND_ID: 1
                 PIECE_ID: 2
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: FROZEN
                START_SCN: 1654120800000000000
        START_SCN_DISPLAY: 2022-06-02 06:00:00.000000
           CHECKPOINT_SCN: 1654207080000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-03 05:58:00.000000
                  MAX_SCN: 1654207140000000000
                  END_SCN: 1654207200000000000
               COMPATIBLE: 1
              INPUT_BYTES: 48318382080
      INPUT_BYTES_DISPLAY: 45.00GB
             OUTPUT_BYTES: 48318382080
     OUTPUT_BYTES_DISPLAY: 45.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: file:///data/nfs/backup/archive
   *************************** 3. row ***************************
                TENANT_ID: 1002
                  DEST_ID: 1002
                 ROUND_ID: 1
                 PIECE_ID: 3
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: FROZEN
                START_SCN: 1654207200000000000
        START_SCN_DISPLAY: 2022-06-03 06:00:00.000000
           CHECKPOINT_SCN: 1654228800000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-03 12:00:00.000000
                  MAX_SCN: 1654228810000000000
                  END_SCN: 1654293600000000000
               COMPATIBLE: 1
              INPUT_BYTES: 21474836480
      INPUT_BYTES_DISPLAY: 20.00GB
             OUTPUT_BYTES: 21474836480
     OUTPUT_BYTES_DISPLAY: 20.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: file:///data/nfs/backup/archive
   *************************** 4. row ***************************
                TENANT_ID: 1002
                  DEST_ID: 1002
                 ROUND_ID: 2
                 PIECE_ID: 4
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: ACTIVE
                START_SCN: 1655697600000000000
        START_SCN_DISPLAY: 2022-06-20 12:00:00.000000
           CHECKPOINT_SCN: 1655769600000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-21 08:00:00.000000
                  MAX_SCN: 1655769610000000000
                  END_SCN: 1655784000000000000
               COMPATIBLE: 1
              INPUT_BYTES: 16106127360
      INPUT_BYTES_DISPLAY: 15.00GB
             OUTPUT_BYTES: 16106127360
     OUTPUT_BYTES_DISPLAY: 15.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: file:///data/nfs/backup/archive
   *************************** 5. row ***************************
                TENANT_ID: 1004
                  DEST_ID: 1001
                 ROUND_ID: 1
                 PIECE_ID: 1
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: FROZEN
                START_SCN: 1655658000000000000
        START_SCN_DISPLAY: 2022-06-20 01:00:00.000000
           CHECKPOINT_SCN: 1655744370000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-21 00:59:30.000000
                  MAX_SCN: 1655744380000000000
                  END_SCN: 1655744400000000000
               COMPATIBLE: 1
              INPUT_BYTES: 53687091200
      INPUT_BYTES_DISPLAY: 50.00GB
             OUTPUT_BYTES: 53687091200
     OUTPUT_BYTES_DISPLAY: 50.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com
   *************************** 6. row ***************************
                TENANT_ID: 1004
                  DEST_ID: 1001
                 ROUND_ID: 1
                 PIECE_ID: 2
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: ACTIVE
                START_SCN: 1655744400000000000
        START_SCN_DISPLAY: 2022-06-21 01:00:00.000000
           CHECKPOINT_SCN: 1655769600000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-21 08:00:00.000000
                  MAX_SCN: 1655769620000000000
                  END_SCN: 1655830800000000000
               COMPATIBLE: 1
              INPUT_BYTES: 107374182400
      INPUT_BYTES_DISPLAY: 100.00GB
             OUTPUT_BYTES: 107374182400
     OUTPUT_BYTES_DISPLAY: 100.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com
   6 rows in set
   

   In the preceding example, the tenant with the tenant ID 1002 has four pieces and the tenant with the tenant ID 1004 has two pieces.

   For the four pieces of tenant 1002:

   • round_id is 1 for the three pieces with the piece IDs 1 to 3. This means that the three pieces are generated in the first round of archiving. The piece with the piece ID 4 is generated in the second round of archiving. Logs whose SCNs fall within the range of '2022-06-01 06:00:00.000000' to '2022-06-02 05:59:00.000000' are archived to piece 1. The amount of raw log data is 25 GB, and the amount of archived data is 25 GB, with a compression ratio of 1.00. The value of the FILE_STATUS field of piece 1 is DELETED. This indicates that the data in this piece has expired and been deleted. If the value of the STATUS field of a piece is FROZEN, the data in this piece is complete and no more logs will be archived to this piece.

   • The value of the FILE_STATUS field is AVAILABLE for the three pieces with the piece IDs 2 to 4. This indicates that the data in the three pieces is not deleted and can be accessed. For Piece 3, CHECKPOINT_SCN changes to FROZEN before '2022-06-04 06:00:00.000000'. This is because log archiving is disabled and no more logs will be archived to Piece 3. Currently, tenant 1002 is undergoing the second round of archiving, and Piece 4 is the first piece generated during this round of archiving. STATUS of Piece 4 is ACTIVE, indicating that logs are being archived to the piece. Tenant 1004 is undergoing the first round of archiving. Two pieces have been generated, with the piece IDs 1 and 2. The archiving of Piece 1 is completed. Therefore, Piece 1 is in the FROZEN state. The archiving of Piece 2 is in progress. Therefore, Piece 2 is in the ACTIVE state.

View all pieces of a user tenant

You can query the oceanbase.DBA_OB_ARCHIVELOG_PIECE_FILES view from a user tenant in MySQL mode or the sys.DBA_OB_ARCHIVELOG_PIECE_FILES view from a user tenant in Oracle mode for all pieces of the tenant.

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

   Note:

   The administrator user is root for MySQL tenants and SYS for Oracle tenants.

2. Execute the following statement to view all pieces of the current tenant.

   To view all pieces of a MySQL tenant, execute the following statement:

   obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_ARCHIVELOG_PIECE_FILES\G
   *************************** 1. row ***************************
                  DEST_ID: 1002
                 ROUND_ID: 1
                 PIECE_ID: 1
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: FROZEN
                START_SCN: 1654034400000000000
        START_SCN_DISPLAY: 2022-06-01 06:00:00.000000
           CHECKPOINT_SCN: 1654120740000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-02 05:59:00.000000
                  MAX_SCN: 1654120770000000000
                  END_SCN: 1654120800000000000
               COMPATIBLE: 1
              INPUT_BYTES: 26843545600
      INPUT_BYTES_DISPLAY: 25.00GB
             OUTPUT_BYTES: 26843545600
     OUTPUT_BYTES_DISPLAY: 25.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: DELETED
                     PATH: file:///data/nfs/backup/archive
   *************************** 2. row ***************************
                  DEST_ID: 1002
                 ROUND_ID: 1
                 PIECE_ID: 2
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: FROZEN
                START_SCN: 1654120800000000000
        START_SCN_DISPLAY: 2022-06-02 06:00:00.000000
           CHECKPOINT_SCN: 1654207080000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-03 05:58:00.000000
                  MAX_SCN: 1654207140000000000
                  END_SCN: 1654207200000000000
               COMPATIBLE: 1
              INPUT_BYTES: 48318382080
      INPUT_BYTES_DISPLAY: 45.00GB
             OUTPUT_BYTES: 48318382080
     OUTPUT_BYTES_DISPLAY: 45.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: file:///data/nfs/backup/archive
   *************************** 3. row ***************************
                  DEST_ID: 1002
                 ROUND_ID: 1
                 PIECE_ID: 3
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: FROZEN
                START_SCN: 1654207200000000000
        START_SCN_DISPLAY: 2022-06-03 06:00:00.000000
           CHECKPOINT_SCN: 1654228800000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-03 12:00:00.000000
                  MAX_SCN: 1654228810000000000
                  END_SCN: 1654293600000000000
               COMPATIBLE: 1
              INPUT_BYTES: 21474836480
      INPUT_BYTES_DISPLAY: 20.00GB
             OUTPUT_BYTES: 21474836480
     OUTPUT_BYTES_DISPLAY: 20.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: file:///data/nfs/backup/archive
   *************************** 4. row ***************************
                  DEST_ID: 1002
                 ROUND_ID: 2
                 PIECE_ID: 4
              INCARNATION: 1
                  DEST_NO: 0
                   STATUS: ACTIVE
                START_SCN: 1655697600000000000
        START_SCN_DISPLAY: 2022-06-20 12:00:00.000000
           CHECKPOINT_SCN: 1655769600000000000
   CHECKPOINT_SCN_DISPLAY: 2022-06-21 08:00:00.000000
                  MAX_SCN: 1655769610000000000
                  END_SCN: 1655784000000000000
               COMPATIBLE: 1
              INPUT_BYTES: 16106127360
      INPUT_BYTES_DISPLAY: 15.00GB
             OUTPUT_BYTES: 16106127360
     OUTPUT_BYTES_DISPLAY: 15.00GB
        COMPRESSION_RATIO: 1.00
              FILE_STATUS: AVAILABLE
                     PATH: file:///data/nfs/backup/archive
   4 rows in set
   

For more information about the CDB_OB_ARCHIVELOG_PIECE_FILES view, see CDB_OB_ARCHIVELOG_PIECE_FILES.