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

   Here is an example of establishing a connection. Make sure to use the actual connection details for your environment.

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

   For more information about how to connect to the 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        |
   +-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | 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 |
   +-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   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 scope that the parameter to be modified takes effect in. Valid value: BOTH or SPFILE. The default value is BOTH. The following describes the two values:

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

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

   • TENANT: the tenant scope in which the parameter to be modified is reset. Valid values: all, all_user, and all_meta, or the name of a specified tenant.

     • 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-level parameters for all user tenants. We recommend that you use TENANT = all_user when you want the modification to take effect in all user tenants. TENANT = all will be deprecated.

     • all_meta: all meta tenants.

     • tenant_name: the name of a specified tenant.

   You cannot set the value of a cluster-level parameter in a user tenant or in 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-level parameter. You can determine whether a parameter is cluster-level or tenant-level by the value in the scope column in the result of the SHOW PARAMETERS LIKE 'parameter_name'; statement:

   • scope is CLUSTER, indicating that the parameter is cluster-level.

   • scope is TENANT, indicating that the parameter is tenant-level.

   The following examples demonstrate how to reset cluster-level and tenant-level parameters in the sys tenant, and how to reset tenant-level parameters in a user tenant.

   • Reset a cluster-level parameter and a tenant-level 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-level 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 as expected, depending 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        |
   +-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | 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 |
   +-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   1 row in set
   

References

• Cluster parameters

• View cluster parameters

• Modify cluster parameters