ALTER SYSTEM 语句用来对 OceanBase 数据库发送命令，执行某项指定操作。

BOOTSTRAP

描述

该语句用于自举 OceanBase 集群。

格式

alter_system_bootstrap_stmt:
    ALTER SYSTEM BOOTSTRAP opt_cluster_type region_zone_server_list;
opt_cluster_type:
    [CLUSTER cluster_role]
cluster_role:
    PRIMARY | STANDBY
region_zone_server_list:
    region_zone_server [, region_zone_server ...]
region_zone_server:
    [region] zone server
region:
    REGION [=] region_name
zone:
    ZONE [=] zone_name
server:
    SERVER [=] ip_port
ip_port:
    'STR_VALUE:INT_VALUE'

参数解释

系统自举时，需要指定 RootService 信息，多台 RootService 通过逗号（，）分隔。

示例

• 指定一台 RootService：

ALTER SYSTEM BOOTSTRAP ZONE 'zone1' SERVER '10.218.248.178:55410';

• 多台 RootService 通过逗号（，）分隔：

ALTER SYSTEM BOOTSTRAP ZONE 'zone1' SERVER '172.24.65.24:55410', ZONE 'zone2' 
SERVER '172.24.65.114:55410';

• 启动备库

ALTER SYSTEM BOOTSTRAP CLUSTER STANDBY ZONE 'zone1' SERVER '10.218.248.178:55410';

JOB

描述

该语句触发参数指定的后台任务。

格式

alter_system_job_stmt:
    ALTER SYSTEM RUN JOB job_name
    [zone | server];

参数解释

示例

• 触发 RootService 自检任务。

ALTER SYSTEM RUN JOB "root_inspection";

MERGE

描述

该语句发起存储层合并。

格式

alter_system_merge_stmt:
    ALTER SYSTEM merge_action;
merge_action:
      MAJOR FREEZE
    | MINOR FREEZE
      [tenant_list | replica] [server_list] [zone]
    | START MERGE
      zone
    | {SUSPEND | RESUME} MERGE
      [zone]
    | CLEAN MERGE ERROR
tenant_list:
    TENANT [=] (tenant_name_list)
tenant_name_list:
    tenant_name [, tenant_name ...]
replica:
    PARTITION_ID [=] 'partition_id%partition_count@table_id'
server_list:
    SERVER [=] ip_port_list

参数解释

示例

• 发起每日合并。

OceanBase(root@oceanbase)>alter system major freeze;
Query OK, 0 rows affected (0.06 sec)

PARAMETER

描述

该语句用来修改配置项信息。

格式

alter_system_parameter_stmt:
    ALTER SYSTEM [SET]
    parameter_name = expression [SCOPE = {MEMORY | SPFILE | BOTH}] [COMMENT [=} 'text']
                                                            {SERVER [=] ‘ip:port’ | ZONE [=] ‘zone’};

参数解释

说明

同时修改多个系统配置项时，用“,”隔开。

查看系统配置项的格式：SHOW PARAMETERS [LIKE 'pattern' | WHERE expr];

示例

• 修改配置项 enable_sql_audit 的信息。

OceanBase(root@oceanbase)>show parameters like 'enable_sql_audit';
+------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone | svr_type | svr_ip        | svr_port | name             | data_type | value | info                                                                                                          | section  | scope   | source  | edit_level        |
+------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| z1   | observer | 11.11.111.111 |    19510 | enable_sql_audit | NULL      | 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 rows in set (0.02 sec)
OceanBase(root@oceanbase)>alter system set enable_sql_audit = false scope = BOTH;
Query OK, 0 rows affected (0.05 sec)
OceanBase(root@oceanbase)>show parameters like 'enable_sql_audit';
+------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone | svr_type | svr_ip        | svr_port | name             | data_type | value | info                                                                                                          | section  | scope   | source  | edit_level        |
+------+----------+---------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| z1   | observer | 11.11.111.111 |    19510 | enable_sql_audit | NULL      | 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 rows in set (0.02 sec)

REFRESH

描述

该语句系列用来触发刷新操作，包括刷新 schema、时区信息、各类缓存等。

格式

alter_system_refresh_stmt:
    ALTER SYSTEM refresh_action;
refresh_action:
      REFRESH SCHEMA
      [zone | server]
    | REFRESH TIME_ZONE_INFO
    | FLUSH cache_type CACHE
      [tenant_list] [GLOBAL]
    | FLUSH KVCACHE
      [tenant] [CACHE [=] cache_name]
cache_type:
      ALL
    | LOCATION
    | CLOG
    | ILOG
    | COLUMN_STAT
    | BLOCK_INDEX
    | BLOCK
    | ROW
    | BLOOM_FILTER
    | SCHEMA
    | PLAN
tenant:
    TENANT [=] tenant_name
cache_name:

参数解释

示例

• 刷新单台 OBServer 的 Schema：

ALTER SYSTEM REFRESH SCHEMA SERVER='172.24.65.24:55410';

• 刷新整个 Zone 的 Schema：

ALTER SYSTEM REFRESH SCHEMA ZONE='zone1';

REPLICA

描述

该语句用来处理副本的迁移，复制，删除，副本类型转换，角色切换等行为。

格式

alter_system_replica_stmt:
    ALTER SYSTEM replica_action;
replica_action:
      SWITCH REPLICA
      {LEADER | FOLLOWER}
      {replica server | server [tenant_name] | zone [tenant_name]}
    | DROP REPLICA partition_id_desc
      replica server [create_timestamp] [zone] [FORCE]
    | {MOVE | COPY} REPLICA
      replica source destination
    | REPORT REPLICA partition_id_desc 
      {zone | server}
    | RECYCLE REPLICA partition_id_desc 
      {zone | server}
    | {ALTER | CHANGE | MODIFY} REPLICA  
      replica server [set] REPLICA_TYPE = replica_type
source:
    SOURCE [=] 'ip:port'
destination:
    DESTINATION [=] 'ip:port'
partition_id_desc
    PARTITION_ID partition_id%partition_count@table_id
partition_idx | partition_count | table_id | task_id:
    INT_VALUE
create_timestamp:
    CREATE_TIMESTAMP [=] INT_VALUE
tenant_name_list:
    tenant_name [, tenant_name ...]
replica_type:
      {FULL | F}
    | {READONLY | R}
    | {LOGONLY | L}

参数解释

示例

• 副本迁移

ALTER SYSTEM MOVE REPLICA PARTITION_ID '0%4@1100611139403777'
SOURCE '172.24.65.24:55410'
DESTINATION '172.24.65.26:55410';

• 删除副本

ALTER SYSTEM DROP REPLICA PARTITION_ID '0%4@1100611139403777'
SERVER '172.24.65.26:55410';

• 修改副本类型

ALTER SYSTEM CHANGE REPLICA PARTITION_ID '0%4@1100611139403777'
SERVER '172.24.65.26:55410';
CHANGE REPLICA_TYPE = 'L';

• Leader改选

ALTER SYSTEM SWITCH REPLICA LEADER PARTITION_ID '0%4@1100611139403777'
SERVER '172.24.65.26:55410';

ROOTSERVICE

描述

切换 RS 的角色。

格式

alter_system_rootservice_stmt:
    ALTER SYSTEM SWITCH ROOTSERVICE {LEADER | FOLLOWER} {zone | server};

参数解释

示例

• 把z1的 RS 切换为 Leader

ALTER SYSTEM SWITCH ROOTSERVICE LEADER ZONE 'z1';

SERVER

描述

该语句维护系统中 OBServer 状态，控制 OBServer 的增、删、启、停等操作。

格式

alter_system_server_stmt:
    ALTER SYSTEM server_action SERVER ip_port_list [zone];
server_action:
      ADD
    | DELETE
    | CANCEL DELETE
    | START
    | STOP
    | FORCE STOP
ip_port_list:
    ip_port [, ip_port ...]

参数解释

服务器状态图如下：

示例

• 新增服务器

ALTER SYSTEM ADD SERVER '172.24.65.113:55410' ZONE 'zone1';

注意

• 通过 Add 和 Delete 命令将服务器加入到服务列表，只有服务列表中的服务器才可以提供服务。

• Delete 操作执行 leader 改选以及 replica 复制。

• Delete 操作执行时间很长，因此，允许通过 Cancel 命令取消该操作。

THROTTLE

描述

该语句用来设置特征限流。

格式

alter_system_throttle_stmt:
    ALTER SYSTEM throttle_action;
throttle_action:
      ENABLE SQL THROTTLE [priority_option] [using_metric_option_list]
    | DISABLE SQL 
priority_option:
    FOR PRIORITY <= INT_VALUE
using_metric_option_list:
    USING metric_option_list
metric_option_list:
    metric_option [metric_option ...]
metric_option:
      RT = {INT_VALUE | DECIMAL_VALUE}
    | CPU = {INT_VALUE | DECIMAL_VALUE}
    | IO = INT_VALUE
    | NETWORK = {INT_VALUE | DECIMAL_VALUE}
    | QUEUE_TIME = {INT_VALUE | DECIMAL_VALUE}
    | LOGICAL_READS = {INT_VALUE | DECIMAL_VALUE}

参数解释

示例

• 对 prioriy<=100 的 session 上队列等待时间超过 0.1s 的请求限流。

alter system enable sql throttle for priority <= 100 using queue_time=0.1

UNIT

描述

该语句负责 UNIT 资源迁移。

格式

alter_system_unit_stmt:
    ALTER SYSTEM MIGRATE
    UNIT [=] unit_id DESTINATION [=] ip_port
unit_id:
    INT_VALUE

参数解释

示例

• 将 UNIT 1001 的资源迁移到 11.11.111.111:19510。

OceanBase(root@oceanbase)>alter system migrate unit = 1001 destination = '11.11.111.111:19510';
Query OK, 0 rows affected (0.05 sec)

ZONE

描述

该语句负责维护 Zone 的状态，如 Zone 的增、删、启、停等操作。

格式

alter_system_zone_stmt:
      ADD ZONE zone_name
      [zone_option_list]
    | {ALTER | CHANGE | MODIFY} ZONE zone_name
      [SET] zone_option_list
    | {DELETE | START | STOP | FORCE STOP} ZONE zone_name
zone_option_list:
    zone_option [, zone_option ...]
zone_option:
      region
    | idc
    | ZONE_TYPE {READONLY | READWRITE}
idc:
    STR_VALUE

参数解释

示例

• 删除 Zone。

OceanBase(root@oceanbase)>alter system delete zone 'z1';
ERROR 4668 (HY000): The zone is not empty and can not be deleted. You should delete the servers of the zone. There are 1 servers alive and 0 not alive.

CLUSTER

描述

集群管理相关操作，可以新增、删除、修改集群级相关属性。

格式

ALTER SYSTEM  cluster_action cluster_name CLUSTER_ID INTNUM;
cluster_action:
    ADD CLUSTER
    | REMOVE CLUSTER
    | ENABLE CLUSTER SYNCHRONIZATION
    | DISABLE CLUSTER SYNCHRONIZATION
    | MODIFY CLUSTER REDO_TRANSPORT_OPTIONS = 'SYNC|ASYNC'

参数解释

示例

• 新增备库

ALTER SYSTEM ADD CLUSTER 'ob1.test' cluster_id = 1;

SWITCHOVER

描述

集群无损、有损切换语法，包括主库切成备库，备库切成主库。

格式

ALTER SYSTEM commit_switchover_clause;
commit_switchover_clause：
    COMMIT TO SWITCHOVER TO PRIMARY
    | COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
    | ACTIVATE PHYSICAL STANDBY CLUSTER
    | CONVERT TO PHYSICAL STANDBY

参数解释

示例

• 主库切换成备库

ALTER SYSTEM COMMIT TO SWITCHOVER TO PHYSICAL STANDBY；

• 备库切换成主库

ALTER SYSTEM COMMIT TO SWITCHOVER TO PRIMARY；

• 主库宕机，备库切换成主库

ALTER SYSTEM ACTIVATE PHYSICAL STANDBY CLUSTER；

• 旧主库重启，切成备库

ALTER SYSTEM CONVERT TO PHYSICAL STANDBY；

BALANCE TASK

描述

该语句用来清除没有正在调度的负载均衡任务。

格式

ALTER SYSTEM REMOVE BALANCE TASK opt_tenant_list opt_zone_list opt_balance_task_type;
opt_tenant_list
    TENANT [=] name,name_list
opt_zone_list
    ZONE [=] zone_name, zone_list
opt_balance_task_type
    ALL
    | MANUAL
    | AUTO

参数解释

示例

• 清除所有租户所有没有在调度的任务。

ALTER SYSTEM REMOVE BALANCE TASK；

CANCEL MIGRATE UNIT

描述

该语句用来取消 UNIT 的迁移。

格式

ALTER SYSTEM CANCEL MIGRATE UNIT unit_id;

参数解释

示例

• 取消 UNIT 1001 的迁移。

ALTER SYSTEM CANCEL MIGRATE UNIT 1001;

RESTORE

描述

该语句用来恢复租户的数据。

格式

ALTER SYSTEM RESTORE [table_name_list FOR] dest_tenant FROM source_tenant AT 'url' UNTIL 'timestamp' WITH 'restore_option';

参数解释

示例

• 恢复租户的数据。

ALTER SYSTEM RESTORE restored_trade FROM trade 
    AT 'oss://antsys-oceanbasebackup/backup_rd/20200323?host=cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access_id=xxx&access_key=xxx'
  UNTIL ' 2020-03-23 08:59:45'
  WITH 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool';
ALTER SYSTEM RESTORE restored_trade FROM trade 
  AT 'file:///data/nfs/physical_backup_test/20200520'
  UNTIL '2020-05-21 09:39:54.071670' 
  WITH 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool';

• 恢复租户中指定表的数据

ALTER SYSTEM RESTORE `crm`.`sales`, `crm`.`products`, `hr`.`employees` FOR new_tenant1 FROM tenant1 
    AT 'oss://antsys-oceanbasebackup/backup_rd/?host=cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access_id=xxx&access_key=xxx' 
    UNTIL '2021-02-28 08:59:45' 
    WITH 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool';

CHANGE TENANT

描述

该语句用来切换租户。

格式

ALTER SYSTEM CHANGE TENANT tenant_name;
ALTER SYSTEM CHANGE TENANT TENANT_ID [=] INTNUM;

参数解释

示例

• 切换至 ID 为 1001 的租户。

ALTER SYSTEM CHANGE TENANT TENANT_ID = 1001;

注意事项

1. 需以系统租户身份登陆，普通租户无法执行该命令。

2. 需直连 OBServer 执行，断连接后需重新执行。

3. 不能在事务执行过程中执行该命令。

4. 切换到非系统租户后，无法执行 DDL 操作。

BACKUP

描述

该语句用来触发备份。

格式

//设置备份的介质路径
ALTER SYSTEM SET backup_dest = <backup_uri>;
//开启 logarchive
ALTER SYSTEM ARCHIVELOG;
//关闭 logarchive
ALTER SYSTEM NOARCHIVELOG;
//触发基线备份（只支持集群级别）
ALTER SYSTEM BACKUP DATABASE;
//取消当前备份
ALTER SYSTEM CANCEL BACKUP;
//设置备份备份的介质路径
ALTER SYSTEM SET backup_backup_dest = <backup_backup_uri>;
//备份备份的日志
ALTER SYSTEM BACKUP BACKUPPIECE ALL NOT BACKED UP N TIMES [tenant_id = N] [tenant_name = name] [backup_backup_dest=uri];
ALTER SYSTEM BACKUP BACKUPPIECE [= N] [tenant_id = N] [tenant_name = name]  [backup_backup_dest=uri] [WITH ACTIVE BACKUPPIECE];
//取消正在备份的备份日志
ALTER SYSTEM CANCEL BACKUP BACKUPPIECE;
//备份备份的数据
ALTER SYSTEM BACKUP BACKUPSET ALL NOT BACKED UP N TIMES [tenant_id = N] [tenant_name = name] [backup_backup_dest=uri];
ALTER SYSTEM BACKUP BACKUPSET = N [tenant_id = N] [tenant_name = name]  [backup_backup_dest=uri];
//取消正在备份的备份数据
ALTER SYSTEM CANCEL BACKUP BACKUPSET;
//启动自动备份备份日志
ALTER SYSTEM START BACKUP ARCHIVELOG;
//停止自动备份备份日志
ALTER SYSTEM STOP BACKUP ARCHIVELOG;

参数解释

示例

• 设置备份的路径。

alter system set backup_dest='oss://antsys-oceanbasebackup/backup_dir?host=xxx&access_id=xxx&access_key=xxx';
alter system set backup_dest='file:///data/nfs/physical_backup_dir';

• 备份备份的日志。

alter system backup backuppiece all not backed up 2 times backup_backup_dest= 'file:///ob_backup_backup/ob_cluster_1_backup_piece_20200125';
alter system backup backuppiece 1 tenant_name tenant_1 backup_backup_dest= 'file:///ob_backup_backup/ob_cluster_1_backup_piece_20200225';

注意事项

需以系统租户身份登陆，普通租户无法执行该命令。

MAXIMIZE

描述

该语句用来设置主备库的保护模式，只能在主库上执行。

格式

ALTER SYSTEM SET STANDBY CLUSTER TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

参数解释

示例

• 设置主备库的保护模式为最大可用模式。

ALTER SYSTEM SET STANDBY CLUSTER TO MAXIMIZE AVAILABILITY;

DELETE BACKUPSET

描述

该语句用来清理一个备份。

格式

ALTER SYSTEM DELETE BACKUPSET backup_set_id

参数解释

示例

• 清理 backup_set_id 为 1 的备份数据。

ALTER SYSTEM DELETE BACKUPSET 1;

DELETE OBSOLETE BACKUP

描述

该语句用来清理过期的备份数据。

说明

使用该语句清理过期的备份数据之前，需要先配置过期时间。

格式

ALTER SYSTEM SET backup_recovery_window = <过期时间>;
ALTER SYSTEM DELETE OBSOLETE BACKUP;

参数解释

示例

• 设置备份数据的过期时间为一周，一周后备份数据被清理。

ALTER SYSTEM SET backup_recovery_window = '7d';
ALTER SYSTEM DELETE OBSOLETE BACKUP;