Reset cluster parameters

After the values of parameters are modified, you can execute the ALTER SYSTEM RESET statement to reset the parameters to default values.

Considerations

• You can view and reset cluster-level parameters in the sys tenant, but can only view cluster-level parameters in a user tenant.

• You can view and reset tenant-level parameters in the sys tenant or a user tenant.

• You cannot reset parameters on the specified server or in the specified zone.

Procedure

1. Log on to the sys tenant or a user tenant.

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

   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) or Overview (Oracle mode).

2. Execute the following statement to view a parameter value:

   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 parameter value:

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

   where

   • SCOPE specifies the effective scope of the resetting. The default value is BOTH. Valid values are as follows:

     • SPFILE: specifies only to reset the parameter value in the configuration table. The modification takes effect after an OBServer node is restarted.

     • BOTH: specifies to reset the parameter value in both the configuration table and the memory. The modification takes effect immediately and remains effective after an OBServer node is restarted.

     Notice

     An error is returned if SCOPE is set to BOTH for parameters whose modifications do not take effect immediately, that is, parameters whose edit_level is set to STATIC_EFFECTIVE.

   • TENANT is used in the sys tenant to specify the tenants for which you want to reset tenant-level parameters. Valid values are as follows:

     • all or all_user: all user tenants.

       Note

       In OceanBase Database V4.2.1 and later, TENANT = all_user and TENANT = all express the same semantics. If you want an operation to take effect on 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 a user tenant.

   You cannot reset cluster-level parameters in a user tenant or in the sys tenant for the specified user tenant.

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

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

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

   Here are some examples:

   • Reset cluster-level and tenant-level parameters 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. Restart the OBServer node or not, depending on the parameter effective mode, and then execute the SHOW PARAMETERS statement to check whether a parameter is reset successfully.

   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