After you enable log archiving, you can query for information about pieces from the corresponding view.
Background information
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 CDB_OB_ARCHIVELOG_PIECE_FILES view from the sys tenant for all pieces of all tenants in the current cluster.
Log in to the
systenant of the cluster as therootuser.Execute the following statement to query all pieces of all tenants.
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 END_SCN_DISPLAY: 2022-06-02 06:00:00.000000 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 END_SCN_DISPLAY: 2022-06-03 06:00:00.000000 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 END_SCN_DISPLAY: 2022-06-04 06:00:00.000000 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 END_SCN_DISPLAY: 2022-06-21 12:00:00.000000 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 END_SCN_DISPLAY: 2022-06-21 01:00:00.000000 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 END_SCN_DISPLAY: 2022-06-22 01:00:00.000000 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 setIn the preceding example, the tenant with the tenant ID
1002has four pieces and the tenant with the tenant ID1004has two pieces.For the four pieces of tenant
1002:round_idis1for the three pieces with the piece IDs1to3. This means that the three pieces are generated in the first round of archiving. The piece with the piece ID4is generated in the second round of archiving. Logs whose system change numbers (SCNs) fall within the range of'2022-06-01 06:00:00.000000'to'2022-06-02 05:59:00.000000'are archived topiece 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 theFILE_STATUScolumn ofpiece 1isDELETED. This indicates that the data in this piece has expired and been deleted. If the value of theSTATUScolumn of a piece isFROZEN, the data in this piece is complete and no more logs will be archived to this piece.The value of the
FILE_STATUScolumn isAVAILABLEfor the three pieces with the piece IDs2to4. This indicates that the data in the three pieces is not deleted and can be accessed. Forpiece 3,CHECKPOINT_SCNchanges toFROZENbefore'2022-06-04 06:00:00.000000'. This is because log archiving is disabled and no more logs will be archived topiece 3. Currently, tenant1002is undergoing the second round of archiving, andpiece 4is the first piece generated during this round of archiving. The status ofpiece 4isACTIVE, indicating that logs are being archived to the piece. Tenant1004is also undergoing the first round of archiving. Two pieces have been generated, with the piece IDs1and2. The archiving ofpiece 1is completed. Therefore,piece 1is in theFROZENstate. The archiving ofpiece 2is in progress. Therefore,piece 2is in theACTIVEstate.
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.
Log in to the database as an administrator of a user tenant.
Note
The administrator user is the
rootuser in a MySQL tenant and theSYSuser in an Oracle tenant.Query all pieces of the current tenant.
To query 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 END_SCN_DISPLAY: 2022-06-02 06:00:00.000000 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 END_SCN_DISPLAY: 2022-06-03 06:00:00.000000 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 END_SCN_DISPLAY: 2022-06-04 06:00:00.000000 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 END_SCN_DISPLAY: 2022-06-21 12:00:00.000000 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