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

• OceanBase Database currently supports the following backup media: NFS, Alibaba Cloud OSS, Azure Blob (supported starting from V4.3.5 BP3), 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 specific requirements for each backup media, see Backup media requirements in Overview of physical backup and restore.

• When configuring LOCATION, BINDING, and PIECE_SWITCH_INTERVAL, separate the parameters with spaces. Do not add spaces before or after the equal sign (=) when setting parameter values.

• After the 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 parameters. Otherwise, the parameters not specified will use the default values.

Privilege requirements

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

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

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. The following table 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';

In this example, file://your-nfs-server-path/your-archive-path specifies the path of the NFS server and the archive.

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

# 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}]';

# S3-compatible object storage
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]';

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

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 property and the connection string of the storage account. The value must start with http:// or https://. By default, Azure Blob uses secure transmission, which means that it accesses the service through https://. If you want to access the service through http://, you must disable secure transmission. For more information, see Azure Blob documentation.

• access_id: the AccessKey ID of the object storage service.

• access_key: the AccessKey of the object storage service.

• 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 value of this parameter can be either of the following:

  • delete: specifies that the system directly deletes the archived files that meet the cleanup requirements.

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

  • tagging: specifies that the system sets a Tag for the archived files that meet the cleanup requirements. The archived files are retained.

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

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

  • 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 system does not verify the integrity of the archived files. This parameter is supported only by OSS and Azure Blob. For object storage services accessed by using the S3 protocol, such as AWS S3, OBS, GCS, and COS, this parameter 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 parameter is supported only by AWS S3.

• enable_worm: specifies whether to enable the WORM (Write Once Read Many) policy for the object storage service. This parameter is optional. If the object storage service is Alibaba Cloud OSS and the destination bucket has a WORM policy, set this parameter to true.

  The WORM policy of OSS ensures that data can be read but not modified or deleted. After you configure a WORM policy for a bucket and lock the policy, you can upload and read objects in the bucket only before the retention period of the objects expires. After the retention period expires, you can modify or delete the objects.

  Notice

  • Starting from OceanBase Database V4.3.5 BP2, you can specify the enable_worm parameter.
  • Before you set the backup path, make sure that the WORM policy is set and locked for the destination bucket.
  • If the destination bucket has a locked WORM policy and you do not specify enable_worm=true when you set the archiving destination, an error is returned after the statement is executed, indicating that you must set enable_worm=true.
  • After the enable_worm parameter is set, it cannot be changed.
  • After you set the archiving path, if the destination bucket has a locked WORM policy, the archiving task may fail because the system cannot write to the destination path. In this case, an error with the code -9140 is returned, indicating that the object is locked by the WORM policy. If the archiving task fails because of the WORM policy, you must configure a new archiving path and specify enable_worm=true.
  • We recommend that you use the tagging mode for the cleanup mode when you use a bucket with a WORM policy as the archiving path.

  The following table describes the supported values of the enable_worm parameter.

  • true: Yes. Indicates that OceanBase Database performs write and delete operations on the path in accordance with the WORM specification. When this value is set to true, the checksum_type=md5 parameter must be explicitly specified.

  • false: No. The default value of this parameter is false if it is not explicitly specified.

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

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

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

  For example, if a user tenant sets the archival medium to OBS and accesses OBS in Virtual-hosted-style, the 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';
  

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 OSS, the cleanup mode is delete, and the integrity of the archive files 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 path for the mysql_tenant tenant. The archive medium is OSS, the cleanup mode is delete, and the integrity of the archive 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 archive path for the mysql_tenant tenant. The archive medium is OSS, the cleanup mode is tagging, and the integrity of the archive files is verified using the MD5 algorithm. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path according to the WORM specification.

    Notice

    OceanBase Database will 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 AWS S3, the 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 cleanup mode is tagging, and the integrity of the archive files 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 it is accessed using 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=****' TENANT = mysql_tenant;
    

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

  • The system tenant configures the archive path for the mysql_tenant tenant. The archive medium is COS, the cleanup mode is delete, and it is accessed using the S3 protocol.

    Notice

    When using COS as the archive 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 archive path for the mysql_tenant tenant. The archive medium is Azure Blob, the cleanup mode is delete, the integrity 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 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 OSS, the cleanup mode is tagging, and the integrity of the archive files 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=tagging&checksum_type=md5';
    

  • The mysql_tenant tenant configures the archive path for itself. The archive medium is OSS, the cleanup mode is tagging, and the integrity of the archive 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 archive path for itself. The archive medium is OSS, the cleanup mode is tagging, and the integrity of the archive files is verified using the MD5 algorithm. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path according to the WORM specification.

    Notice

    OceanBase Database will 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 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 itself. The archive medium is AWS S3, the cleanup mode is tagging, and the integrity of the archive files 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';
    

  • The mysql_tenant tenant configures the archive path for itself. The archive medium is OBS, and it is accessed using 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 mysql_tenant tenant configures the archive path for itself. The archive medium is GCS, and it is accessed using 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 mysql_tenant tenant configures the archive path for itself. The archive medium is COS, the cleanup mode is delete, and it is accessed using the S3 protocol.

    Notice

    When you use COS as the archiving media, you must set the cluster-level parameter ob_storage_s3_url_encode_type first. For more information, see Prepare 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';
    

  • Configure the archiving path for the mysql_tenant tenant. The archiving media is Azure Blob. Set the cleanup mode to delete, the verification algorithm to md5, and the business priority mode. Also, specify that 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