View archiving parameter settings

During log archiving, you can view the settings of archiving parameters.

View the archiving parameter settings of tenants from the sys tenant

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

2. Execute the following statements to view the archiving parameter settings of tenants.

   • View the archive parameter information for the log archive configuration of all tenants.

     The view CDB_OB_ARCHIVE_DEST records the archive parameter information configured for the log archives of all tenants.

     obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_ARCHIVE_DEST;
     

     The query result is as follows:

     +-----------+---------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | TENANT_ID | DEST_NO | NAME                  | VALUE                                                                                                                                                      |
     +-----------+---------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
     |      1002 |       0 | binding               | OPTIONAL                                                                                                                                                   |
     |      1002 |       0 | dest_id               | 1002                                                                                                                                                       |
     |      1002 |       0 | path                  | file:///data/nfs/backuparchive                                                                                                                             |                
     |      1002 |       0 | piece_switch_interval | 1d                                                                                                                                                         |
     |      1002 |       0 | state                 | ENBALE                                                                                                                                                     |
     |      1004 |       0 | binding               | Mandatory                                                                                                                                                  |
     |      1004 |       0 | dest_id               | 1001                                                                                                                                                       |
     |      1004 |       0 | path                  | oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=tagging&checksum_type=md5&enable_worm=true     |
     |      1004 |       0 | piece_switch_interval | 1d                                                                                                                                                         |
     |      1004 |       0 | state                 | ENBALE                                                                                                                                                     |
     +-----------+---------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
     10 rows in set
     

     In the query example, there are two tenants in the current cluster with tenant IDs 1002 and 1004 that have configured archive parameters.

     For the tenant with ID 1002, the archive path for log archiving is file:///data/nfs/backuparchive. The value of binding is OPTIONAL, which indicates that the priority mode between archiving and business operations is set to the Optional mode, meaning user business operations take priority. The piece_switch_interval is set to 1d, meaning a log piece is split every day during log archiving. The state is ENABLE, indicating that this archive destination is enabled.

     For the tenant with ID 1004, the archive path and related parameters for log archiving are oss://oceanbase-test-bucket/backup/archive?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=tagging&checksum_type=md5&enable_worm=true. The value of binding is Mandatory, which indicates that the priority mode between archiving and business operations is set to the Mandatory mode, meaning archiving takes priority. The piece_switch_interval is set to 1d, meaning a log piece is split every day during log archiving. The state is ENABLE, indicating that this archive destination is enabled.

     For more details about the CDB_OB_ARCHIVE_DEST view, refer to CDB_OB_ARCHIVE_DEST.

   • View the archive path-related information for log archiving of all tenants.

     The view CDB_OB_BACKUP_STORAGE_INFO records the configuration information of backup or archive paths for all tenants.

     obclient [oceanbase]> SELECT PATH, DEST_TYPE, EXTENSION FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='archive_log';
     

     The query result example is as follows:

     +-----------+---------------------------------------------+-------------+------------------------------------+
     | TENANT_ID | PATH                                        | DEST_TYPE   | EXTENSION                          | 
     +-----------+---------------------------------------------+-------------+------------------------------------+
     |      1004 | oss://oceanbase-test-bucket/backup/archive  | archive_log | checksum_type=md5&enable_worm=true |
     +-----------+---------------------------------------------+-------------+------------------------------------+
     1 row in set
     

     In the query result, for the tenant with ID 1004, the archive path is oss://oceanbase-test-bucket/backup/archive. The parameter enable_worm=true indicates that OceanBase Database performs write and delete operations on this path according to WORM (Write Once Read Many) specifications. The parameter checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of the archive files.

   • View the log archive concurrency of a tenant.

     The log archive concurrency of a tenant is controlled by the tenant-level configuration item log_archive_concurrency. The value range for this configuration item is [0, 100], and the default value is 0, which means OceanBase Database will use an adaptive log archive concurrency. For detailed information about the configuration item log_archive_concurrency, refer to log_archive_concurrency.

     obclient [(none)]> SHOW PARAMETERS LIKE 'log_archive_concurrency';
     

     An example of the query result is as follows:

     +-------+----------+----------------+----------+-------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+---------+-------------------+---------------+-----------+
     | zone  | svr_type | svr_ip         | svr_port | name                    | data_type | value | info                                                                                                                                                                                                                                                                                                                               | section    | scope  | source  | edit_level        | default_value | isdefault |
     +-------+----------+----------------+----------+-------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+---------+-------------------+---------------+-----------+
     | zone1 | observer | 172.xx.xxx.xxx |     2882 | log_archive_concurrency | INT       | 0     | log archive concurrency, for both archive fetcher and sender. If the value is default 0, the database will automatically calculate the number of archive worker threads based on the tenant specification, which is tenant max_cpu divided by 4; otherwise set the the worker count equals to the value.Range: [0, 100] in integer | LOGSERVICE | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | 0             |         1 |
     +-------+----------+----------------+----------+-------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+---------+-------------------+---------------+-----------+
     1 row in set
     

View the archiving parameter settings of a user tenant

1. Log in to the database as a tenant administrator.

   Note

   The administrator user of a MySQL tenant is root and that of an Oracle tenant is SYS.

2. Execute the following statements to view the archiving parameters of the current tenant:

   • View the archive parameter information of the log archive configuration for the current tenant.

     User tenants can view the archive parameter information configured for the current tenant through the oceanbase.DBA_OB_ARCHIVE_DEST view (MySQL mode) and the SYS.DBA_OB_ARCHIVE_DEST view (Oracle mode).

     MySQL mode

     Oracle mode

     An example query in MySQL mode is as follows:

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_ARCHIVE_DEST;
     

     An example query in Oracle mode is as follows:

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_ARCHIVE_DEST;
     

     The query result is as follows:

     +---------+-----------------------+---------------------------------+
     | DEST_NO | NAME                  | VALUE                           |
     +---------+-----------------------+---------------------------------+
     |       0 | binding               | OPTIONAL                        |
     |       0 | dest_id               | 1002                            |
     |       0 | path                  | file:///data/nfs/backup/archive |
     |       0 | piece_switch_interval | 1d                              |
     |       0 | state                 | ENABLE                          |
     +---------+-----------------------+---------------------------------+
     6 rows in set
     

   • View the related information of the archive path for log archiving of the current tenant.

     User tenants can view the configuration information of the archive path for log archiving through the view oceanbase.DBA_OB_BACKUP_STORAGE_INFO (MySQL mode) and the view SYS.DBA_OB_BACKUP_STORAGE_INFO (Oracle mode).

     MySQL Mode

     Oracle Mode

     An example query in MySQL mode is as follows:

     obclient [oceanbase]> SELECT PATH, DEST_TYPE, EXTENSION FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO;
     

     An example query in Oracle mode is as follows:

     obclient [SYS]> SELECT PATH, DEST_TYPE, EXTENSION FROM SYS.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='archive_log';
     

     An example of the query result is as follows:

     +--------------------------------------------+-------------+-------------------+
     | PATH                                       | DEST_TYPE   | EXTENSION         | 
     +--------------------------------------------+-------------+-------------------+
     | oss://oceanbase-test-bucket/backup/archive | archive_log | checksum_type=md5 |
     +--------------------------------------------+-------------+-------------------+
     1 row in set
     

   • View the log archive concurrency of a tenant.

     The log archive concurrency of a tenant is controlled by the tenant-level configuration item log_archive_concurrency. The value range for this configuration item is [0, 100], and the default value is 0, which means OceanBase Database will use an adaptive log archive concurrency. For detailed information about the configuration item log_archive_concurrency, refer to log_archive_concurrency.

     obclient [(none)]> SHOW PARAMETERS LIKE 'log_archive_concurrency';
     

     An example of the query result is as follows:

     +-------+----------+----------------+----------+-------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+---------+-------------------+---------------+-----------+
     | zone  | svr_type | svr_ip         | svr_port | name                    | data_type | value | info                                                                                                                                                                                                                                                                                                                               | section    | scope  | source  | edit_level        | default_value | isdefault |
     +-------+----------+----------------+----------+-------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+---------+-------------------+---------------+-----------+
     | zone1 | observer | 172.xx.xxx.xxx |     2882 | log_archive_concurrency | INT       | 0     | log archive concurrency, for both archive fetcher and sender. If the value is default 0, the database will automatically calculate the number of archive worker threads based on the tenant specification, which is tenant max_cpu divided by 4; otherwise set the the worker count equals to the value.Range: [0, 100] in integer | LOGSERVICE | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | 0             |         1 |
     +-------+----------+----------------+----------+-------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+---------+-------------------+---------------+-----------+
     1 row in set
     

References

For more information about how to set the archiving parameters, see Prepare for log archiving.