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

• Currently, OceanBase Database 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 specific requirements of 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 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 the default values.

Privilege requirements

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

• The default administrator user in MySQL mode is the root user.
• The default administrator user in Oracle 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 NFS server path and 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 and are separated from the archive path by a question mark (?). You can specify only one of the three parameters or not specify any. By default, no parameter is specified.

  • If you want to specify the region or IDC, you must 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. We recommend that you specify the IDC as the source. This way, the archive task can be executed in an IDC with sufficient network bandwidth, and the archive task has more optional nodes. In addition, you do not need to pay attention to the changes in zones in 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, there is no priority difference in accessing the log archive path. The only restriction is on 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 of the archive task.

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

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 access domain name of the object storage service.

  For Azure Blob, the value of the host parameter can be obtained by concatenating the container property 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 and 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 about how to disable secure transmission for Azure Blob, see Azure Blob documentation.

• access_id: the access key ID of the object storage.

• access_key: the access key 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 cleanup mode is direct deletion of archived files that meet the cleanup requirements.

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

  • tagging: specifies that the cleanup mode is to set the Tag for archived files that meet the cleanup requirements. The archived files are retained.

    If you set this parameter to tagging, the system sets the Tag for 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 allows users to manage the lifecycle of these files in the object storage by using the set tags.

• checksum_type: specifies the algorithm for checking 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.

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

    Notice

    MD5 checks do not take effect for the GetObject interface.

  • no_checksum: specifies that the integrity of the archived files is not checked. 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. This is because the S3 SDK does not support this option.

  • crc32: specifies that the CRC32 algorithm is used to check 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 has a compliance retention policy (WORM), set this parameter to True.

  OSS supports the WORM (Write Once Read Many) feature for retention policies. This feature 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 of an object expires, you can modify or delete the object.

  Notice

  • Since OceanBase Database does not replace your compliance retention strategy, make sure that the OSS bucket has a retention strategy (WORM strategy) and is locked before you set the backup path.
  • If the bucket corresponding to the path to be set has a WORM strategy and is locked, but you do not specify enable_worm=true when configuring the archive destination, an error will be returned after the statement is executed, indicating that enable_worm=true must be specified.
  • Once the enable_worm parameter is set, it cannot be changed later.
  • If the bucket corresponding to the archive path has a WORM strategy and is locked, the archive may fail with error code -9140, indicating the object is locked by worm. If the archive task fails due to the WORM strategy, you must reconfigure the archive path and specify enable_worm=true.
  • When using a bucket with a WORM strategy as the archive path, it is recommended to use the tagging mode for archive cleanup.

  The value of the enable_worm parameter can be set to the following options:

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

  • false: Indicates that the parameter is not explicitly specified. The 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: Specifies the URL format for accessing the object storage service. This parameter is optional for object storage services accessible through S3-compatible protocols (such as OBS, GCS, and COS) and is connected to other parameters using the & symbol. The current supported values are as follows:

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

  For example, if a user tenant sets OBS as the archive medium 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';
  

• zone, idc, and region: Specify the source of the archive path, that is, the range of nodes that can access the path. These parameters are optional and are connected to other parameters using the English symbol &. You can select only one of the zone, idc, and region parameters or none of them. By default, none of them are selected.

  • If you want to set 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. It is recommended to set the IDC level source configuration to utilize the IDC with more network bandwidth resources for executing the archive task, providing more optional nodes, and avoiding the need to monitor changes in zones within the IDC.

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

  • Since all traffic for log archiving is written by the leader replica, there is no access priority difference for log archive paths. It only restricts the range of nodes that can access the archive path. If you want to set 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 configuring zone, idc, or region, ensure that the leader replica is located in the specified zone, idc, or region. Otherwise, the archive task will fail to proceed.

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 NFS. The zones that can access this archiving path are z1, z2, and z3. 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?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 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. When enable_worm=true is configured for this path, the system will perform write and delete operations on this path according to WORM specifications.

    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 OSS. The archiving mode is delete. The zones that can access this 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 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 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 mode is delete, and the S3 protocol is used to access it.

    Notice

    When you use COS as the archiving medium, you must set the cluster-level parameter ob_storage_s3_url_encode_type. 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' 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 NFS. The region that can access this archiving path is region1. 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?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 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. When enable_worm=true is configured for this path, the system will perform write and delete operations on this path according to WORM specifications.

    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 OSS. The archiving mode is delete, and the IDC that can access this 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 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 configures the archive path for the current tenant, with AWS S3 as the archive medium, and the archive file cleanup mode set to tagging. 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';
    

  • Tenant mysql_tenant configures the archive path for the current tenant, with OBS as the archive medium, and the archive files are 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 configures the archive path for the current tenant, with GCS as the archive medium, and the archive files are 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 configures the archive path for the current tenant, with COS as the archive medium, and the archive file cleanup mode set to delete. The archive files are accessed using the S3 protocol.

    Notice

    When using COS as the archive medium, you must 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';
    

  • Tenant mysql_tenant configures the archive path for the current tenant, with Azure Blob as the archive medium, and the archive file cleanup mode set to delete. The archive files are accessed using the S3 protocol. The verification algorithm is specified as md5, and the business priority mode is used. Additionally, a new 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 path and archive path source configuration case

• ARCHIVELOG

• NOARCHIVELOG

• Preparation before log archiving