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
systenant, 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
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***** -AFor more information about how to connect to a database, see Overview (MySQL mode) and Overview (Oracle mode).
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 setExecute 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:SPFILEandBOTH. 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, andall_meta, and tenant name.allandall_user: all user tenants.Note
Starting from V4.2.1, OceanBase Database allows you to use
TENANT = all_userandTENANT = allinterchangeably to reset tenant parameters for all user tenants. We recommend that you useTENANT = all_userwhen the reset scope is all user tenants.TENANT = allwill 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
systenant.For example, executing the
ALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant'statement will result in an error becausememory_limitis a cluster parameter. You can determine whether a parameter is a cluster parameter or a tenant parameter based on the value of thescopecolumn in the result of theSHOW PARAMETERS LIKE 'parameter_name';statement:If the value of the
scopecolumn isCLUSTER, the parameter is a cluster parameter.If the value of the
scopecolumn isTENANT, 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;
After you reset a parameter, you can execute the
SHOW PARAMETERSstatement 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