SET LOG_ARCHIVE_DEST

Purpose

The ALTER SYSTEM SET LOG_ARCHIVE_DEST statement is used to configure the archive destination for a user tenant. The configuration of the archive destination mainly includes attributes such as LOCATION, BINDING, and PIECE_SWITCH_INTERVAL.

Limitations and considerations

• Backup and restore are not supported for the sys tenant and meta tenant, and you do not need to configure an archive destination.

• OceanBase Database currently supports the following backup media: NFS, Alibaba Cloud OSS, Azure Blob, AWS S3, and object storage services that are compatible with the S3 protocol, such as Huawei OBS, Google GCS, and Tencent Cloud COS. Some backup media may require meeting certain basic requirements before they can be used. For more information about the requirements for each backup media, see Backup media requirements in Overview of physical backup and restore.

• When you configure the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL parameters, separate the parameters with spaces. Do not add spaces before or after the equal sign (=) in the parameter value.

• After you configure an archive destination, you cannot incrementally configure it. That is, when you execute the ALTER SYSTEM SET LOG_ARCHIVE_DEST statement, you must specify the values of all parameters. Otherwise, the unspecified parameters will use their default values.

Privilege requirements

The root user of the sys tenant (root@sys) or the administrator of each tenant must execute this procedure. Specifically:

• The default administrator user in MySQL-compatible mode is the root user.
• The default administrator user in Oracle-compatible mode is the SYS user.

Syntax

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=archive_path [BINDING=archive_mode] [PIECE_SWITCH_INTERVAL=piece_switch_interval]' [TENANT = tenant_name];

Parameters

archive_path

Specifies the archive path and source configuration. The following describes the archive_path format for different types of media.

NFS

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file://your-nfs-server-path/your-archive-path[?{zone=zone_name | idc=idc_name | region=region_name}];

The following table describes the related parameters.

• file://your-nfs-server-path/your-archive-path: specifies the path of the NFS server and the archive path.

• zone, idc, and region: specify the source of the archive path, that is, the node range that can access the path. These parameters are optional. The archive path and the parameters are separated by a question mark (?). You can specify only one of zone, idc, and region, or specify none of them. By default, none of them are specified.

  • If you want to specify the region or IDC level, you must first 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 specify the IDC level. This way, you can use an IDC with more network bandwidth resources to perform the archive task and specify more node ranges for the archive task. In addition, you do not need to pay attention to the changes in zones within the IDC in the cluster.

  • If the specified zone, idc, or region does not exist, an error is returned indicating that the source information does not exist (error code -9133).

  • All traffic for log archiving is written by the leader replica. Therefore, there is no access priority difference for log archive paths. The only restriction is the node range that can access the archive path. If you want to specify multiple zones, multiple IDCs, or multiple regions, separate the values with commas (,) or semicolons (;). For example, zone=z1,z2;z3 specifies z1, z2, and z3 as the sources for archiving.

  • When you specify zone, idc, or region, the leader replica must be in the specified zone, idc, or region. Otherwise, the archive task cannot proceed.

Object storage

# Alibaba Cloud OSS
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://your-bucket-name/your-archive-path?host=oss-your-region.aliyuncs.com&access_id=your-access-id&access_key=your-access-key[&delete_mode={delete | tagging}][&checksum_type={md5 | no_checksum}][&{zone=zone_name | idc=idc_name | region=region_name}];

# AWS S3
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://your-bucket-name/your-archive-path?host=s3.your-region.amazonaws.com&access_id=your-access-id&access_key=your-access-key&s3_region=your-s3-region[&delete_mode={delete | tagging}][&checksum_type={md5 | crc32}][&{zone=zone_name | idc=idc_name | region=region_name}];

# Object storage compatible with the S3 protocol
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://your-bucket-name/your-archive-path?host=your-service.com&access_id=your-access-id&access_key=your-access-key[&addressing_model={virtual_hosted_style | path_style}][&checksum_type=md5][&{zone=zone_name | idc=idc_name | region=region_name}];

# Azure Blob
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=azblob:////your-bucket-name/your-archive-path?host=your-service.com&access_id=your-access-id&access_key=your-access-key[&delete_mode={delete | tagging}][&checksum_type=md5][&{zone=zone_name | idc=idc_name | region=region_name}]';

The following table describes the parameters.

• host: the domain name of the object storage service.

  For Azure Blob, the value of the host parameter can be obtained by concatenating the container attribute or the connection string of the storage account. The prefix of the host parameter must be specified as http:// or https://. By default, Azure Blob enables secure transmission, which uses https:// to access the service. If you want to use http:// to access the service, you must disable secure transmission. For more information about how to disable secure transmission, see Azure Blob documentation.

• access_id: the AccessKey ID of the object storage.

• access_key: the AccessKey of the object storage.

• delete_mode: specifies the cleanup mode of the archived files. This parameter is optional for Alibaba Cloud OSS, AWS S3, object storage compatible with the S3 protocol, and Azure Blob. This parameter is connected to other parameters by using the & symbol. The value of this parameter can be set to one of the following options:

  • delete: specifies that the archived files that meet the cleanup requirements are directly deleted.

    When you use this mode to clean up the archived files, the system directly deletes the archived files that meet the cleanup requirements.

  • tagging: specifies that the Tag of the archived files that meet the cleanup requirements are set. The archived files are retained.

    When you use this mode to clean up the archived files, the system sets the Tag of the archived files that meet the cleanup requirements. The key of the Tag is "delete_mode" and the value is "tagging". This way, you can manage the lifecycle of these files by using the tags in the object storage.

• checksum_type: specifies the algorithm for verifying the integrity of the archived files. If you do not explicitly specify this parameter, the MD5 algorithm is used by default for all object storages. The following table describes the values of this parameter for different object storages.

  • md5: specifies that the MD5 algorithm is used to verify the integrity of the archived files.

    Notice

    The MD5 algorithm is not supported for the GetObject interface.

  • no_checksum: specifies that the integrity of the archived files is not verified. This option is supported only by Alibaba Cloud OSS and Azure Blob. For object storages that are accessed by using the S3 protocol, including AWS S3, OBS, GCS, and COS, this option is not supported because of the limitations of the S3 SDK.

  • crc32: specifies that the CRC32 algorithm is used to verify the integrity of the archived files. This option is supported only by AWS S3.

• enable_worm: optional. If the archiving destination is Alibaba Cloud OSS and the destination bucket is configured with a compliance retention policy (WORM), set this parameter to True.

  The retention policy of Alibaba Cloud OSS has the WORM (Write Once Read Many) feature, which allows you to save and use data in an immutable manner. After you configure and lock the retention policy for a bucket, you can upload and read objects in the bucket only before the retention period of the objects expires. After the retention period of an object expires, you can modify or delete the object.

  Notice

  • Due to the fact that OceanBase Database currently does not support auto-configuration of compliance retention policies, you must ensure that the OSS bucket has the retention policy (WORM policy) correctly configured and locked before setting the backup path.
  • If the retention policy (WORM policy) for the target path is already set and locked, but you do not specify the enable_worm=true parameter when configuring the archive destination, an error will be reported after the statement is executed, indicating that the enable_worm=true parameter must be set.
  • Once the enable_worm parameter is set, it cannot be changed later.
  • If you set the retention policy (WORM policy) for the bucket associated with the archive path after the archive path is configured, the archive task may fail due to the inability to append to the target path and the system will return an error code of -9140, indicating that the object is locked by the worm. After discovering such an error, you must configure a new archive path and specify the enable_worm=true parameter.
  • When using an OSS bucket with the retention policy (WORM policy) configured as the archive path, it is recommended to use the tagging mode for archive cleanup.

  The valid values of the enable_worm parameter are as follows:

  • true: Yes. It indicates that OceanBase Database will perform write and delete operations according to the WORM policy on the specified path. When you set the value of this parameter to true, you must explicitly set the value of the checksum_type parameter to md5.

  • false: No. The default value of this parameter is false when you do not explicitly specify it.

• s3_region: The region of the Amazon S3 bucket. This parameter is required when the archive medium is AWS S3.

• addressing_model: The URL format for accessing an object storage service. The value of this parameter is optional. It can be used to access object storage services, such as OBS, GCS, or COS, with an S3-compatible protocol. Other parameters are separated from this parameter by the & symbol. The current value of this parameter can be set to one of the following:

  • virtual_hosted_style: The default value. It indicates that the object storage service is accessed in Virtual-hosted-style.
  • path_style: It indicates that the object storage service is accessed in Path-style.

  For example, if you are a tenant in the database, and you specify OBS as the archive medium and use Virtual-hosted-style for accessing OBS, the sample statement is as follows:

  ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****&addressing_model=virtual_hosted_style';
  

• zone, idc, and region: The data source of the archive path, which specifies the node ranges that can access the archive path. These parameters are optional and separated from each other by an English comma (,). They support three different configuration levels: zone, idc, and region. You can select only one level or choose not to specify the parameters.

  • If you want to specify the region or idc configuration level, you must first 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 specify the region information or IDC information. It is recommended that you specify the idc configuration level so that you can use the IDC with abundant network bandwidth to execute the archive task, and there are more optional nodes for the archive task. This way, you need not pay attention to the changes in the zone level under each IDC in the cluster.

  • If the specified zone, idc, or region does not exist, the system will return an error message indicating that the data source information does not exist (error code -9133).

  • All traffic of log archiving must be written by the leader replica. Therefore, there is no access priority difference for log archiving paths. It only restricts the nodes that can access the archiving path. If you need to specify multiple zones, multiple IDCs, or multiple regions, separate the values with commas (,) or semicolons (;). For example, zone=z1,z2;z3 indicates that the archive source includes z1, z2, and z3.

  • For a specified zone, idc, or region, the leader replica must exist in the specified zone, idc, or region, otherwise the archive task cannot proceed.

Examples

• System tenant

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is NFS, the mode is user business priority, and a piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive BINDING=Optional PIECE_SWITCH_INTERVAL=1d' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is NFS, the zones that can access the archiving path are z1, z2, and z3, the mode is user business priority, and a piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive?zone=z1,z2,z3 BINDING=Optional PIECE_SWITCH_INTERVAL=1d' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is OSS, the archiving file cleanup mode is delete, and the MD5 algorithm is used to verify the integrity of the archiving files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&checksum_type=md5' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is OSS, the archiving file cleanup mode is delete, and the integrity of the archiving files is not verified.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&checksum_type=no_checksum' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is OSS, the archiving file cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of the archiving files. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path according to WORM specifications.

    Notice

    OceanBase Database will not replace your compliance retention strategy. Before setting up the archiving path, make sure the OSS bucket has the correct retention strategy (WORM strategy) and is locked.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=tagging&checksum_type=md5&enable_worm=true' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is OSS, the archiving file cleanup mode is delete, and the zones that can access the archiving path are z1, z2, and z3.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&zone=z1,z2,z3' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is AWS S3, the archiving file cleanup mode is delete, the mode is user business priority, and a piece is split every other day.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=******&access_key=******&s3_region=******&delete_mode=delete BINDING=Optional PIECE_SWITCH_INTERVAL=1d' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is AWS S3, the archiving file cleanup mode is tagging, and the CRC32 algorithm is used to verify the integrity of the archiving files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***&delete_mode=tagging&checksum_type=crc32' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is OBS, and the S3 protocol is used to access it.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is GCS, and the S3 protocol is used to access it.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com&access_id=****&access_key=****' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is COS, the archiving file cleanup mode is delete, and the S3 protocol is used to access it.

    Notice

    When using COS as the archiving medium, you need to set the cluster-level parameter ob_storage_s3_url_encode_type first. For more information, see Preparations for log archiving.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test/backup/archive?host=cos.ap-***.myqcloud.com&access_id=***&access_key=***&delete_mode=delete' TENANT = mysql_tenant;
    

  • The system tenant configures the archiving path for the mysql_tenant tenant. The archiving medium is Azure Blob, the cleanup mode is delete, the verification algorithm is specified as md5, the mode is user business priority, and a piece is split every other day.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=azblob://oceanbase-test-bucket/backup/archive?host=http://****.blob.core.windows.net&access_id=****&access_key=****&delete_mode=delete&checksum_type=md5 BINDING=Optional PIECE_SWITCH_INTERVAL=1d' TENANT = mysql_tenant;
    

• User tenant

  • The mysql_tenant tenant configures the archiving path for itself. The archiving medium is NFS, the mode is user business priority, and a piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • The mysql_tenant tenant configures the archiving path for itself. The archiving medium is NFS, the region that can access the archiving path is region1, the mode is user business priority, and a piece is split every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive?region=region1 BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • The mysql_tenant tenant configures the archiving path for itself. The archiving medium is OSS, the archiving file cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of the archiving files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=tagging&checksum_type=md5';
    

  • The mysql_tenant tenant configures the archiving path for itself. The archiving medium is OSS, the archiving file cleanup mode is tagging, and the integrity of the archiving files is not verified.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=tagging&checksum_type=no_checksum';
    

  • The mysql_tenant tenant configures the archiving path for itself. The archiving medium is OSS, the archiving file cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of the archiving files. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path according to WORM specifications.

    Notice

    OceanBase Database will not replace your compliance retention strategy. Before setting up the archiving path, make sure the OSS bucket has the correct retention strategy (WORM strategy) and is locked.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=tagging&checksum_type=md5&enable_worm=true';
    

  • The mysql_tenant tenant configures the archiving path for itself. The archiving medium is OSS, the archiving file cleanup mode is delete, and the IDC that can access the archiving path is IDC3.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&idc=idc3';
    

  • The mysql_tenant tenant configures the archiving path for itself. The archiving medium is AWS S3, the archiving file cleanup mode is delete, the mode is user business priority, and a piece is split every other day.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=******&access_key=******&s3_region=******&delete_mode=delete BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • The mysql_tenant tenant configures the archive path for the current tenant, with AWS S3 as the archive medium. The archive file cleanup mode is set to tagging, and the CRC32 algorithm is used to verify the integrity of the archive files.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***&delete_mode=tagging&checksum_type=crc32';
    

  • The mysql_tenant tenant configures the archive path for the current tenant, with OBS as the archive medium. The S3 protocol is used for access.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****';
    

  • The mysql_tenant tenant configures the archive path for the current tenant, with GCS as the archive medium. The S3 protocol is used for access.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com&access_id=****&access_key=****';
    

  • The mysql_tenant tenant configures the archive path for the current tenant, with COS as the archive medium. The archive file cleanup mode is set to delete, and the S3 protocol is used for access.

    Notice

    When using COS as the archive medium, you need to set the cluster-level configuration item ob_storage_s3_url_encode_type first. For more information, see Preparations for log archiving.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test/backup/archive?host=cos.ap-***.myqcloud.com&access_id=***&access_key=***&delete_mode=delete';
    

  • The mysql_tenant tenant configures the archive path for the current tenant, with Azure Blob as the archive medium. The cleanup mode is set to delete, the verification algorithm is specified as md5, and the business priority mode is used. Additionally, a piece is split every other day.

    obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=azblob://oceanbase-test-bucket/backup/archive?host=http://****.blob.core.windows.net&access_id=****&access_key=****&delete_mode=delete&checksum_type=md5 BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

References

• Backup and archive paths source configuration case

• ARCHIVELOG

• NOARCHIVELOG

• Preparation before log archiving