Reset cluster parameters

After the value of a parameter is modified, you can use the ALTER SYSTEM RESET statement to reset it to its default value based on your business requirements.

Considerations

• The system tenant (i.e., the sys tenant) can view and reset cluster-level parameters. User tenants can only view cluster-level parameters but cannot reset them.

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

• Resetting parameter values for a specific server or zone is not supported.

Procedure

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

   The following is an example connection string. Please adjust it according to your actual database configuration when connecting to the database.

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

   For more detailed instructions on how to connect to a database, see Overview of database connections (MySQL-compatible mode) and Overview of database connections (Oracle-compatible mode).

2. Execute the following command to view the parameter values.

   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 command to modify the parameter values.

   The statement is as follows:

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

   The relevant parameters are described as follows:

   • SCOPE specifies the scope of effect for resetting the parameter. The default value is BOTH.

     • SPFILE: Resets only the parameter values in the parameter file. The change takes effect after the OBServer node restarts.

     • BOTH: Resets both the parameter file and the memory values. The modification takes effect immediately and remains valid after the OBServer node restarts.

   • TENANT: Used by the system tenant to reset tenant-level parameters for all user tenants, all Meta tenants, or a specified tenant.

     • all and all_user: All user tenants.

       Note

       Starting from OceanBase Database V4.2.1, TENANT = all_user and TENANT = all have the same semantics. When you need to set the scope to all user tenants, we recommend that you use TENANT = all_user. The latter TENANT = all will be deprecated in the future.

     • all_meta: all Meta tenants.

     • tenant_name: the name of the specified tenant.

   When resetting parameters, cluster-level parameters cannot be set through a user tenant, nor can they be specified for a user tenant by using the system tenant (i.e., the sys tenant).

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

   • A scope value of CLUSTER indicates a cluster-level parameter.

   • A scope value of TENANT indicates a tenant-level parameter.

   Here is an example:

   • The system tenant resets the values of cluster-level and tenant-level parameters.

     obclient [oceanbase]> ALTER SYSTEM RESET enable_sql_audit;
     

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

   • A user tenant resets the value of a tenant-level parameter.

     obclient [oceanbase]> ALTER SYSTEM RESET undo_retention;
     

4. After successful reset, you can verify whether the parameter values were successfully reset by executing the SHOW PARAMETERS statement, depending on your actual situation (whether to restart the OBServer node or not).

   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