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 involves attributes such as LOCATION, BINDING, and PIECE_SWITCH_INTERVAL.

Limitations and considerations

• The system tenant and Meta tenant do not support backup and restore operations, so 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 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 the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL parameters, separate the parameters with spaces. Do not add spaces before or after the equals sign (=) when setting the parameter values.

• After the archive destination is configured, 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 parameters 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 statement. 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 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 and are separated from the archive path by a question mark (?). You can specify only one of the three parameters or specify none. By default, none of these parameters are specified.

  • 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. We recommend that you set the IDC level. This way, the archive task can be performed on an IDC with sufficient network bandwidth, and the archive task has more node options. In addition, you do not need to pay attention to the changes of zones in the IDCs of 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 of log archiving is written by the leader replica. Therefore, there is no access priority difference for log archive paths. The only restriction is on the node range that can access the archive path. If you need 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 of the archive.

  • 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}][&{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 in the preceding statements.

• 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 attributes or 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 uses https:// to access the service. If you want to use http:// to access the service, 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 archived files that meet the cleanup requirements are directly deleted.

    When you clean up the archived files in automatic mode, the system directly deletes the archived files that meet the cleanup requirements.

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

    When you clean up the archived files in automatic mode, the system sets the Tag for 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 on the object storage by using the tags.

• checksum_type: specifies the algorithm for checking 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 value of this parameter varies based on the object storage. The following table describes the supported values.

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

    Notice

    The MD5 algorithm does 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 that are 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 CRC32 algorithm is used to check the integrity of the archived files. This option is supported only by AWS S3.

• enable_worm: optional. This parameter is required only when the archiving destination is an OSS bucket that is configured with a compliance retention policy (WORM). Set this parameter to True.

  The retention policy of OSS has the WORM (Write Once Read Many) feature. 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 expires, you can modify or delete the objects.

  Notice

  • Since OceanBase Database does not replace your compliance retention strategy, make sure that the OSS bucket has been correctly configured with a retention strategy (WORM strategy) and locked before setting the backup path.
  • If the bucket corresponding to the path to be set has been configured and locked with a WORM strategy, but the enable_worm=true parameter is not specified when configuring the archive destination, an error will be returned after the statement is executed, indicating that the enable_worm=true parameter needs to be set.
  • Once the enable_worm parameter is set, it cannot be changed later.
  • If the bucket corresponding to the archive path is configured and locked with a WORM strategy after the archive path is set, an error -9140 will be returned when the archive task fails to write to the target path, indicating the object is locked by worm. In this case, you need to reconfigure a new archive path and specify the enable_worm=true parameter.
  • When a bucket configured with a WORM strategy is used 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 write to and delete objects from the path in accordance with the WORM strategy. When the value is set to true, you must also explicitly set the checksum_type=md5 parameter.

  • false: No. The default value is false when this parameter is not explicitly specified.

• 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 service. This parameter is optional for object storage services that support S3-compatible protocols (such as OBS, GCS, and COS). You can specify this parameter together with other parameters by using the & symbol. The current valid values are as follows:

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

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

  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 parameters, or you can specify none of them. By default, none of these parameters is specified.

  • If you want to specify the region or IDC, 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. We recommend that you specify the IDC as the source. This way, you can use the IDC with more network bandwidth resources to execute the archive task, and the archive task has more optional nodes. 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).

  • Since all traffic of log archiving is written by the leader replica, there is no priority difference in accessing the log archive path. 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 indicates that the archive task uses the z1, z2, and z3 zones as the sources.

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

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 two days.

    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 two days.

    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 cleanup mode is delete, and the MD5 algorithm is used to verify the integrity of the archive 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 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 MD5 algorithm is used to verify the integrity of the archive files. When 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

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting the archive path, ensure 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 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 cleanup mode is delete, the mode is user business priority, and a piece is split every two days.

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

  • The system tenant configures the archive path for the mysql_tenant tenant. The archive medium is OBS, and it is accessed through 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 through 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 through 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 verification algorithm is specified as md5, the mode is user business priority, and a piece is split every two days.

    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 two days.

    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 two days.

    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, the cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of the archive 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 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 MD5 algorithm is used to verify the integrity of the archive files. When 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

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting the archive path, ensure 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 OSS, the 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 AWS S3, the cleanup mode is delete, the mode is user business priority, and a piece is split every two days.

    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 an archive path for the current tenant. The archive medium is AWS S3, the archive file cleanup mode is 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 an archive path for the current 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=****';
    

  • The mysql_tenant tenant configures an archive path for the current 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=****';
    

  • The mysql_tenant tenant configures an archive path for the current 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 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 an archive path for the current tenant. The archive medium is Azure Blob, the cleanup mode is delete, the verification algorithm is md5, the business priority mode is used, and one piece is split every two days.

    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