Reset a cluster parameter

After you modify the value of a parameter, you can execute the ALTER SYSTEM RESET statement to reset the parameter to its default value based on your business requirements.

Considerations

• The system tenant, which is the sys tenant, can view and reset cluster-level parameters. User tenants can only view cluster-level parameters but cannot reset them.

• Both system and user tenants can view and reset tenant-level parameters.

• Resetting the parameter values on specified servers or in specified zones is not supported.

Procedure

1. Log in to the cluster as the system tenant or a user tenant.

   Here is an example of how to connect to the database. Modify the connection string based on your actual database settings.

   obclient -h10.xx.xx.xx -P2883 -utest@mysql#obdemo -p***** -A
   

   For more information about how to connect to a database, see Overview (MySQL mode) and Overview (Oracle mode).

2. Execute the following statement to query the value of a parameter:

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

   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 | enable_sql_audit | BOOL      | False | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | true          |         0 |
   +-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
   1 row in set
   

3. Execute the following statement to modify the value of a parameter:

   The statement is as follows:

   ALTER SYSTEM RESET
       parameter_name [SCOPE = {SPFILE | BOTH}]
            [TENANT [=] all | all_user | all_meta | tenant_name ];
   

   The following table describes the parameters in the statement.

   • SCOPE: the reset scope of the parameter. Valid values: SPFILE and BOTH. Default value: BOTH.

     • SPFILE: indicates that the parameter value is reset only in the configuration table. The reset takes effect only after the OBServer node is restarted.

     • BOTH: indicates that the parameter value is reset in both the configuration table and memory. The reset takes effect immediately and the new parameter value takes effect after the OBServer node is restarted.

   • TENANT: the tenant to reset the tenant parameter of. Valid values: all, all_user, and all_meta, and tenant name.

     • all and all_user: all user tenants.

       Note

       Starting from V4.2.1, OceanBase Database allows you to use TENANT = all_user and TENANT = all interchangeably to reset tenant parameters for all user tenants. We recommend that you use TENANT = all_user when the reset scope is all user tenants. TENANT = all will be deprecated.

     • all_meta: all meta tenants.

     • tenant_name: the name of the specified tenant.

   You cannot set the value of a cluster parameter or a user tenant parameter by using a user tenant or the sys tenant.

   For example, executing the ALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant' statement will result in an error because memory_limit is a cluster parameter. You can determine whether a parameter is a cluster parameter or a tenant parameter based on the value of the scope column in the result of the SHOW PARAMETERS LIKE 'parameter_name'; statement:

   • If the value of the scope column is CLUSTER, the parameter is a cluster parameter.

   • If the value of the scope column is TENANT, the parameter is a tenant parameter.

   Here are some examples:

   • Reset a cluster parameter and a tenant parameter in the sys tenant.

     obclient [oceanbase]> ALTER SYSTEM RESET enable_sql_audit;
     

     obclient [oceanbase]> ALTER SYSTEM RESET undo_retention TENANT = all_user;
     

   • Reset a tenant parameter in a user tenant.

     obclient [oceanbase]> ALTER SYSTEM RESET undo_retention;
     

4. After you reset a parameter, you can execute the SHOW PARAMETERS statement to check whether the parameter is reset based on whether you restart the OBServer node.

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

   The 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 | enable_sql_audit | BOOL      | True  | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | true          |         1 |
   +-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
   1 row in set
   

References

• Cluster parameters

• View cluster parameters

• Modify cluster parameters