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 the Meta tenant. Therefore, you do not need to configure an archive destination.

• OceanBase Database supports the following backup media: NFS, Alibaba Cloud OSS, Azure Blob (supported 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 you to meet certain basic requirements before you can use them. For more information about the requirements of each backup media, see Backup media requirements in Overview of physical backup and restore.

• When you configure LOCATION, BINDING, and PIECE_SWITCH_INTERVAL, 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 other archive destinations. That is, when you execute the ALTER SYSTEM SET LOG_ARCHIVE_DEST statement, you must specify the values of all parameters. Otherwise, the parameters that are not specified 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:

• In MySQL mode, the default administrator is the root user.
• In Oracle mode, the default administrator 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. 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 NFS server path and archive path.

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

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

# 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 related parameters.

• host: the access 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 value of the host parameter must be prefixed 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 Azure Blob. For more information, 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: specifies that the system directly deletes the archived files that meet the cleanup requirements.

    When you configure 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 the Tag of the archived files that meet the cleanup requirements. The archived files are retained.

    When you configure this mode, the system sets the Tag of 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, users 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 you do not explicitly specify this parameter, the system uses the MD5 algorithm for all object storages. The value of this parameter varies based on the object storage. The following table describes the supported values of this parameter.

  • md5: specifies that the system uses the MD5 algorithm to verify the integrity of the archived files.

    Notice

    The MD5 algorithm does not take effect for the GetObject interface.

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

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

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

  OSS supports the WORM retention policy. This policy allows you to store and use data in an unchangeable and non-deletable manner. After you enable the retention policy for the 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 V4.3.5 BP2, you can specify the enable_worm parameter.
  • Before you set the backup path, make sure that the retention policy (WORM policy) is enabled for the OSS bucket and locked.
  • If the WORM policy is enabled and locked for the bucket corresponding to the path that you want to set, but you do not specify enable_worm=true when you set the archiving destination, an error will be 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 WORM policy is enabled and locked for the bucket corresponding to the path, the archiving task may fail with the -9140 error, indicating that the object is locked by the WORM policy. In this case, you must configure a new archiving path and specify enable_worm=true.
  • When you use a bucket that has the WORM policy enabled as the archiving path, we recommend that you set the cleanup mode to tagging.

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

  • true: Yes. This indicates that OceanBase Database will perform write and delete operations on this path in accordance with the WORM specification. When this value is set to true, the checksum_type=md5 parameter must also be explicitly configured.

  • 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 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) and is connected to other parameters using the & symbol. The current valid values for this parameter are as follows:

  • virtual_hosted_style: The default value. This indicates that object storage is accessed in Virtual-hosted-style.
  • path_style: This indicates that 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 would be 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 archiving path for the mysql_tenant tenant. The archiving medium is NFS, the archiving 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 OSS, the archiving mode is delete, and the MD5 algorithm is used to verify the integrity of the archived 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 mode is delete, and the integrity of the archived 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 mode is tagging, and the MD5 algorithm is used to verify the integrity of the archived files. If the path is configured with enable_worm=true, the system will perform write and delete operations on this path according to the WORM specification.

    Notice

    OceanBase Database will not replace your compliance retention strategy. Before setting the archiving path, ensure that 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 AWS S3, the archiving mode is delete, the archiving 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 mode is tagging, and the CRC32 algorithm is used to verify the integrity of the archived 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 it is 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=****' TENANT = mysql_tenant;
    

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

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

    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 archiving mode is delete, the verification algorithm is specified as md5, the archiving 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 archiving 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 OSS, the archiving mode is tagging, and the MD5 algorithm is used to verify the integrity of the archived 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 mode is tagging, and the integrity of the archived 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 mode is tagging, and the MD5 algorithm is used to verify the integrity of the archived files. If the path is configured with enable_worm=true, the system will perform write and delete operations on this path according to the WORM specification.

    Notice

    OceanBase Database will not replace your compliance retention strategy. Before setting the archiving path, ensure that 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 AWS S3, the archiving mode is delete, the archiving 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 archiving path for itself. The archiving medium is AWS S3, the archiving mode is tagging, and the CRC32 algorithm is used to verify the integrity of the archived 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 archiving path for itself. The archiving medium is OBS, and it is 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 mysql_tenant tenant configures the archiving path for itself. The archiving medium is GCS, and it is 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 mysql_tenant tenant configures the archiving path for itself. The archiving medium is COS, the archiving mode is delete, and it is accessed via the S3 protocol.

    Notice

    When you use COS as the archiving medium, 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 medium is Azure Blob. The cleanup mode is set to delete, and the verification algorithm is specified as md5. The business priority mode is used, 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

• Prepare for log archiving