ALTER/CHANGE/MODIFY EXTERNAL_STORAGE_DEST

Purpose

This statement is used to modify the source configuration (such as zone, idc, or region), I/O throttling parameters (max_iops and max_bandwidth), and key information such as access_id and access_key for the backup or archive path of the current tenant. After modification, the new configuration information will overwrite the original configuration information.

Limitations and considerations

• Only user tenants can execute this statement. System tenants (sys tenants) cannot execute this statement.

• Before you modify the zone, idc, or region configurations of a path, you must use the ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; statement to set the region or IDC information. We recommend that you set the IDC information of the source path. This way, you can use an IDC with more network bandwidth resources to perform backup tasks, and the backup tasks have more optional nodes. In addition, you do not need to pay attention to the changes in zones within an IDC in the cluster.

• When you modify the source configurations of a backup path, if a backup task is being executed, the task may take up to a few minutes to detect the configuration changes. During this period, nodes outside the configuration scope may still access the backup path, resulting in cross-data center or cross-region traffic.

• Only object storage paths support changing the I/O throttling parameters (max_iops and max_bandwidth) and access_id and access_key information. If you set the I/O throttling parameters (max_iops and max_bandwidth) or access_id and access_key information for an NFS path, the system returns an error with the error code 1235.

• If the format of the parameters for setting the ACCESS_INFO attribute (access_id and access_key) is incorrect (for example, there are extra = signs, or access_id or access_key is missing), the system returns the OB_OBJECT_STORAGE_PERMISSION_DENIED error (error code -9116).

• When an object storage path is accessed by using the Assume Role method:

  • If you set ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key', an error is returned.

  • If you set only the I/O throttling parameters in the ATTRIBUTE attribute without setting the ACCESS_INFO attribute, the system may prompt that the setting is not supported or that the setting does not take effect.

• You can set the same ATTRIBUTE + ACCESS_INFO attribute multiple times for the same path.

Privilege requirements

The user who executes this statement must have the ALTER SYSTEM privilege. For more information about OceanBase Database privileges, see Privilege types in Oracle mode.

Syntax

ALTER SYSTEM {ALTER|CHANGE|MODIFY} EXTERNAL_STORAGE_DEST 
  PATH [=] 'path' 
  SET [ATTRIBUTE = '[{zone=zone_name | idc=idc_name | region=region_name}][&max_iops=iops_num][&max_bandwidth=bandwidth_num]']
  [ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key'];

Parameters

Examples

Change the source configuration of an archive path

Assume that the original source configuration of an archive path is zone=z1,z2,z3. You need to change the source configuration to region=region1. After the change, only the nodes in region1 can access the archive path.

• OSS

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'region=region1';
  

• NFS

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='file:///data/nfs/backup/archive' SET ATTRIBUTE = 'region=region1';
  

• S3

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'region=region1';
  

• Object storage services compatible with the S3 protocol (OBS, GCS, and COS)

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'region=region1';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ATTRIBUTE = 'region=region1';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'region=region1';
  

Change the source configuration of a backup path

Assume that the original source configuration of a backup path is zone=z1,z2;z3. You need to change the source configuration to idc=idc1;idc2. After the change, the nodes in idc1 and idc2 can access the backup path. The nodes in idc1 have higher priority than those in idc2.

• OSS

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

• NFS

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='file:///data/nfs/backup/data' SET ATTRIBUTE = 'idc=idc1;idc2';
  

• S3

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

• Object storage services compatible with the S3 protocol (OBS, GCS, and COS)

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

Change the access_id and access_key values of an archive path

• OSS

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
  

  • S3

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

  • Object storage services compatible with the S3 protocol (OBS, GCS, and COS)

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

Change the access_id and access_key values of a backup path

• OSS

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
  

  • S3

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

  • Object storage services compatible with the S3 protocol (OBS, GCS, and COS)

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

Set the I/O throttling parameters (max_iops and max_bandwidth) of an archive path

You can set the max_iops and max_bandwidth parameters at the same time, or set only the max_iops or max_bandwidth parameter. You can set the I/O throttling parameters only for object storage paths. You cannot set the I/O throttling parameters for NFS paths.

• OSS

  Set the max_iops and max_bandwidth parameters at the same time. Example:

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  Set only the max_iops parameter. Example:

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';
  

  Set only the max_bandwidth parameter. Example:

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';
  

• S3

  Set the max_iops and max_bandwidth parameters at the same time. Example:

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

• Object storage services compatible with the S3 protocol (OBS, GCS, and COS)

  Set the max_iops and max_bandwidth parameters at the same time. Example:

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

Set the I/O throttling parameters (max_iops and max_bandwidth) for the backup path

You can set both the max_iops and max_bandwidth parameters or set only one of them. Only object storage paths support setting I/O throttling parameters. NFS paths do not support setting I/O throttling parameters.

• OSS

  You can set both the max_iops and max_bandwidth parameters. Here is an example.

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  You can set only the max_iops parameter. Here is an example.

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';
  

  You can set only the max_bandwidth parameter. Here is an example.

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';
  

• S3

  You can set both the max_iops and max_bandwidth parameters. Here is an example.

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

• Object storage services compatible with the S3 protocol (OBS, GCS, and COS)

  You can set both the max_iops and max_bandwidth parameters. Here is an example.

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

References

• Modify the source backup path

• Backup path and archive path source configuration cases

• Update the key information of the object storage