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 sys tenant user or a user tenant user.

   The following code is a sample code for connecting to a database. Modify the sample code 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 parameters in the statement are described as follows:

   • SCOPE specifies the scope in which the parameter is to be reset. The default value is BOTH. Where:

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

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

   • TENANT: specifies the tenant to reset the tenant parameter of for the sys tenant user. Valid values:

     • 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 specify all user tenants. If you want a setting to take effect for all user tenants, we recommend that you use TENANT = all_user. TENANT = all will be deprecated.

     • all_meta: all meta tenants.

     • tenant_name: the name of the specified tenant.

   You cannot set a cluster parameter or a user parameter by using a user tenant user.

   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 user 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 user parameter.

   Here are some examples:

   • Reset a cluster parameter and a user 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 user parameter in a user tenant.

     obclient [oceanbase]> ALTER SYSTEM RESET undo_retention;
     

4. After the reset is successful, you can execute the SHOW PARAMETERS statement to check whether the parameter value is reset based on whether you want to 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