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 an archive destination mainly involves 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 no archive destination needs to be configured.

• OceanBase Database currently supports the following backup media: NFS, Alibaba Cloud OSS, Azure Blob (supported from V4.2.5 BP6), AWS S3, and object storage services compatible with the S3 protocol, such as Huawei OBS, Google GCS, and Tencent Cloud COS. Some backup media may have specific 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 configuring the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL attributes, separate the attributes with spaces. Do not add spaces before or after the equals sign (=) in the attribute value.

• After an archive destination is configured, incremental configuration is not supported. That is, when you execute the ALTER SYSTEM SET LOG_ARCHIVE_DEST statement, you must specify the values of all attributes. Otherwise, the attributes not specified will use their default values.

Privilege requirements

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

• root in MySQL mode.
• SYS in Oracle mode.

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 path for archiving and the 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 range of nodes that can access the path. This is an optional parameter. Separate this parameter from the archive path by using a question mark (?). You can specify only one of zone, idc, and region or do not specify any of them.

  Note

  For V4.2.5, you can specify the source of the archive path starting from V4.2.5 BP2.

  • Before you specify the source at the region or IDC level, run 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 source at the IDC level. This way, you can use an IDC with abundant network bandwidth resources to perform archive tasks and have more options for nodes. In addition, you do not need to pay attention to changes in zones within an IDC in the cluster.

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

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

  • The leader replica must be located 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}][&enable_worm={true | false}][&{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 properties or the connection string of the storage account. The host parameter must be prefixed with http:// or https://. By default, Azure Blob uses secure transmission, which means that it uses https:// to access the service. If you want to access the service by using http://, you must disable secure transmission for Azure Blob. For more information about how to disable secure transmission for Azure Blob, 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 OSS, AWS S3, COS (accessed by using the S3 protocol), and Azure Blob. This parameter is separated from other parameters by the & symbol. The value of this parameter can be set to one of the following options:

  • delete: In this mode, the system directly deletes the archived files that meet the cleanup requirements.

    If you set this parameter to delete, the system directly deletes the archived files that meet the cleanup requirements when you clean up the archived files automatically.

  • tagging: In this mode, the system sets a tag for the archived files that meet the cleanup requirements. The archived files are retained.

    If you set this parameter to tagging, the system sets a tag for the archived files that meet the cleanup requirements when you clean up the archived files automatically. The tag has the key value of "delete_mode" and the value value of "tagging". This way, you can manage the lifecycle of these files on the object storage by using the tags.

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

  • md5: specifies the MD5 algorithm for verifying the integrity of the archived files.

    Notice

    MD5 verification does not apply to the GetObject interface.

  • no_checksum: specifies that the integrity of the archived files is not verified. This value is supported only by OSS and Azure Blob. For object storages that are accessed by using the S3 protocol, such as AWS S3, OBS, GCS, and COS, this value is not supported. This is because the S3 SDK does not support this value.

  • crc32: specifies the CRC32 algorithm for verifying the integrity of the archived files. This value is supported only by AWS S3.

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

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

  Notice

  • Starting from V4.2.5 BP7, you can specify the enable_worm parameter in the V4.2.5 version.
  • Before you set the backup path, make sure that the OSS bucket has been configured with the retention policy (WORM policy) and locked.
  • If the WORM policy has been set and locked for the bucket corresponding to the path to be set, but the enable_worm=true option is not specified when you configure the archive destination, an error will be returned after the statement is executed, indicating that the enable_worm=true option must be specified.
  • Once the enable_worm parameter is set, it cannot be changed.
  • After the archive path is set, if the WORM policy has been set and locked for the bucket corresponding to the path, an error with the code -9140 will be returned when the archive task fails to write to the destination path, indicating that the object is locked by WORM. If the archive task fails due to the WORM policy, you must configure a new archive path and specify the enable_worm=true option.
  • When you use a bucket with a WORM policy as the archive path, we recommend that you use the tagging mode for archive cleanup.

  The valid values of the enable_worm parameter are as follows:

  • true: Yes. OceanBase Database will perform write and delete operations on the path in accordance with the WORM policy. When the value is set to true, you must explicitly specify the checksum_type=md5 parameter.

  • false: No. If this parameter is not explicitly specified, its default value is false.

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

• addressing_model: the URL format for accessing the object storage. This parameter is optional for object storage accessed through an S3-compatible protocol (such as OBS, GCS, or COS). You can specify this parameter together with other parameters by using the & symbol. The value of this parameter can be set to one of the following two options:

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

  For example, if you want to use OBS as the archive medium and access OBS in the Virtual-hosted-style, you can execute the following statement in the user tenant:

  obclient> 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 source of the archive path, that is, the node range that can access the path. These parameters are optional. You can specify these parameters together with other parameters by using the & symbol. You can specify only one of the three options: zone, idc, or region. By default, no option is specified.

  Note

  Starting from V4.2.5 BP2, you can specify the source of the archive path in the V4.2.5 version.

  • If you want to specify the Region or IDC level, you must first execute 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 or IDC information. We recommend that you specify the IDC level. This way, you can use an IDC with more network bandwidth resources to execute the archive task. In addition, the archive task has more optional nodes, and you do not need to pay attention to the changes in the zones within the IDC.

  • 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 of log archiving is written by the leader replica. Therefore, the leader replica has no priority over other replicas for accessing the log archive path. The only limitation 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 indicates that the archive task is executed in the z1, z2, and z3 zones.

  • If the leader replica is not located in the specified zone, idc, or region, the archive task cannot be executed.

Examples

• System tenant

  • The system tenant configures the archive path for the mysql_tenant tenant. The archive 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 archive path for the mysql_tenant tenant. The archive medium is NFS, the zones that can access the archive 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 archive path for the mysql_tenant tenant. The archive medium is OSS, the archive file cleanup mode is delete, and the archive file integrity is verified using the MD5 algorithm.

    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 archive destination for the mysql_tenant tenant. The archive medium is OSS, the archive file cleanup mode is delete, and the archive file integrity 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 archive path for the mysql_tenant tenant. The archive medium is OSS, the archive file cleanup mode is tagging, and the archive file integrity is verified using the MD5 algorithm. When enable_worm=true is configured for this path, the system performs write and delete operations on this path according to the WORM specification.

    Notice

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting the archive path, make sure that the OSS bucket has been correctly configured with a retention strategy (WORM strategy) and 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 archive path for the mysql_tenant tenant. The archive medium is OSS, the archive file cleanup mode is delete, and the zones that can access the archive 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 archive path for the mysql_tenant tenant. The archive medium is AWS S3, the archive 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 archive path for the mysql_tenant tenant. The archive medium is AWS S3, the archive file cleanup mode is tagging, and the archive file integrity is verified using the CRC32 algorithm.

    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 archive path for the mysql_tenant tenant. The archive medium is OBS, and 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=****' TENANT = mysql_tenant;
    

  • The system tenant configures the archive path for the mysql_tenant tenant. The archive medium is GCS, and 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=****' TENANT = mysql_tenant;
    

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

    Notice

    When you use COS as the archive medium, you must first set the cluster-level parameter ob_storage_s3_url_encode_type. For more information, see Preparations before 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 archive path for the mysql_tenant tenant. The archive medium is Azure Blob, the cleanup mode is delete, the verification algorithm is 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 archive path for itself. The archive 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 archive path for itself. The archive medium is NFS, the region that can access the archive 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 archive path for itself. The archive medium is OSS, and the archive file integrity is verified using the MD5 algorithm.

    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';
    

  • The mysql_tenant tenant configures the archive path for itself. The archive medium is OSS, the archive file cleanup mode is delete, and the archive file integrity is not verified.

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

  • The mysql_tenant tenant configures the archive path for itself. The archive medium is OSS, the archive file cleanup mode is delete, and the IDC that can access the archive 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 archive path for itself. The archive medium is OSS, the archive file cleanup mode is tagging, and the archive file integrity is verified using the MD5 algorithm. When enable_worm=true is configured for this path, the system performs write and delete operations on this path according to the WORM specification.

    Notice

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting the archive path, make sure that the OSS bucket has been correctly configured with a retention strategy (WORM strategy) and 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 archive path for itself. The archive medium is AWS S3, the archive 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 tenant configures the archive path for the current tenant, with OBS as the archive medium, accessed via the S3 protocol.

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

  • The tenant configures the archive path for the current tenant, with GCS as the archive medium, accessed via the S3 protocol.

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

  • The 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 must first set the cluster-level parameter ob_storage_s3_url_encode_type. 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, and the verification algorithm is specified as md5. The business priority mode is enabled, 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';
    

References

• ARCHIVELOG

• NOARCHIVELOG

• Preparation before log archiving