SET LOG_ARCHIVE_DEST

Description

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 NFS, Alibaba Cloud OSS, Azure Blob (supported starting from V4.2.5 BP6 in V4.2.5), AWS S3, and S3-compatible object storage such as Huawei OBS, Google GCS, and Tencent Cloud COS as backup media. Some backup media may need to meet basic requirements before use. For the specific requirements of each backup medium, 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 statement must be executed by the root user (root@sys) of the sys tenant or the administrator user of each tenant:

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

Syntax

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=archive_path [BINDING=archive_mode] [PIECE_SWITCH_INTERVAL=piece_switch_interval]' [TENANT = tenant_name];

Parameter description

archive_path

Specifies the archive path and source-side configuration. The following describes the archive_path format for different media types.

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

Parameter descriptions are as follows:

• file://your-nfs-server-path/your-archive-path: specifies the NFS server path and archive path.

• zone, idc, and region: specify the source side of the archive path, that is, the range of nodes that can access this path. This parameter is optional and is separated from the archive path by a question mark (?). You can configure zone, idc, or region at only one level, or leave it unset. By default, it is not set.

  Note

  In OceanBase Database V4.2.5, starting from V4.2.5 BP2, you can configure the source side of an archive path.

  • If you need to configure the Region or IDC level, run ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; first to set the Region or IDC information. We recommend configuring the source side at the IDC level. This allows archive tasks to use an IDC with abundant network bandwidth, gives archive tasks a wider range of candidate nodes, and frees you from tracking zone changes within an IDC in the cluster.

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

  • Because all log archiving traffic is written by the leader replica, log archive paths have no access-priority difference. These parameters only limit the range of nodes that can access the archive path. To specify multiple zones, IDCs, or regions, separate values with commas (,) or semicolons (;). For example, zone=z1,z2;z3 specifies z1, z2, and z3 as the source side for archiving.

  • When you configure zone, idc, or region, the leader replica must be in the specified zone, idc, or region. Otherwise, archiving 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}];

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

Parameter descriptions are as follows:

• host: the domain name used to access the object storage service.

  For Azure Blob, the value of host can be obtained from the container properties or the storage account connection string. The prefix of host must be http:// or https://. Because Azure Blob enables secure transfer by default, it is accessed through https:// by default. To access it through http://, you must disable the secure transfer option for Azure Blob. For more information, 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: configures the cleanup mode of archive files. This optional parameter is supported for OSS, AWS S3, COS (accessed through the S3 protocol), and Azure Blob. It is connected to other parameters with &. This parameter supports the following values:

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

    After this mode is configured, when archive files are cleaned up automatically, the system directly deletes archive files that meet the cleanup requirements.

  • tagging: in this mode, the system sets tags on archive files that meet the cleanup requirements. The archive files are retained.

    After this mode is configured, when archive files are cleaned up automatically, the system sets tags on archive files that meet the cleanup requirements. The tag key is "delete_mode" and the value is "tagging", so that you can manage the lifecycle of these files on object storage based on the tags.

• checksum_type: specifies the algorithm used to verify the integrity of archive files. If this parameter is not explicitly specified, all object storage services use the MD5 algorithm by default. Supported values vary by object storage service:

  • md5: uses the MD5 algorithm to verify the integrity of archive files.

    Notice

    MD5 verification does not take effect for the GetObject interface.

  • no_checksum: does not verify the integrity of archive files. Only OSS and Azure Blob support this value. For object storage accessed through the S3 protocol, including AWS S3, OBS, GCS, and COS, this value is not supported because of S3 SDK limitations.

  • crc32: uses the CRC32 algorithm to verify the integrity of archive files. Only AWS S3 supports this value.

• enable_worm: optional. When the archive destination is an Alibaba Cloud OSS bucket with a compliance retention policy (WORM) configured, set this parameter to true.

  OSS retention policies provide WORM (Write Once Read Many) behavior, allowing data to be stored and used in a non-deletable and non-tamperable manner. After you configure and lock a retention policy for a bucket, you can upload and read objects in that bucket only before the retention period expires. After the retention period expires, the objects can be modified or deleted.

  Notice

  • In OceanBase Database V4.2.5, starting from V4.2.5 BP7, you can specify the enable_worm parameter.
  • OceanBase Database does not configure a compliance retention policy for you. Before you set the archive path, make sure that the OSS bucket retention policy (WORM policy) has been properly configured and locked.
  • If the bucket corresponding to the path to be configured has a WORM policy configured and locked, but enable_worm=true is not specified when you configure the archive destination, the statement fails and prompts you to set enable_worm=true.
  • After the enable_worm parameter is set, it cannot be changed.
  • After the archive path is set, if a WORM policy is configured and locked for the bucket corresponding to the path, archiving may fail with error -9140 because data cannot be appended to the target path, indicating the object is locked by worm. If archiving fails because of the WORM policy, you must configure a new archive path and specify enable_worm=true.
  • When you use a bucket with a WORM policy as the archive path, we recommend that you use the tagging cleanup mode.

  The enable_worm parameter supports the following values:

  • true: yes. OceanBase Database performs write and delete operations on this path according to WORM rules. When you set this value to true, you must also explicitly set checksum_type=md5.

  • false: no. If this parameter is not explicitly specified, the default value is false.

• s3_region: the region of the Amazon S3 bucket. This parameter is required when AWS S3 is used as the archive medium.

• addressing_model: sets the URL format used to access object storage. This optional parameter applies to object storage accessed through the S3 protocol, such as OBS, GCS, and COS. It is connected to other parameters with &. This parameter supports the following values:

  • virtual_hosted_style: the default value. Object storage is accessed in virtual-hosted style.
  • path_style: object storage is accessed in path style.

  For example, the following statement shows how a user tenant configures OBS as the archive medium and accesses OBS in virtual-hosted style:

  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: specify the source side of the archive path, that is, the range of nodes that can access this path. These parameters are optional and are separated from other parameters with &. You can configure zone, idc, or region at only one level, or leave it unset. By default, it is not set.

  Note

  In OceanBase Database V4.2.5, starting from V4.2.5 BP2, you can configure the source side of an archive path.

  • If you need to configure the Region or IDC level, run ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; first to set the Region or IDC information. We recommend configuring the source side at the IDC level. This allows archive tasks to use an IDC with abundant network bandwidth, gives archive tasks a wider range of candidate nodes, and frees you from tracking zone changes within an IDC in the cluster.

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

  • Because all log archiving traffic is written by the leader replica, log archive paths have no access-priority difference. These parameters only limit the range of nodes that can access the archive path. To specify multiple zones, IDCs, or regions, separate values with commas (,) or semicolons (;). For example, zone=z1,z2;z3 specifies z1, z2, and z3 as the source side for archiving.

  • When you configure zone, idc, or region, the leader replica must be in the specified zone, idc, or region. Otherwise, archiving cannot proceed.

Examples

• Sys tenant

  • The sys tenant configures the archive path for tenant mysql_tenant. The archive medium is NFS. Optional binding is used, and one piece is switched every 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is NFS. Zones z1, z2, and z3 can access this archive path. Optional binding is used, and one piece is switched every 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is OSS. The archive file cleanup mode is delete, and the MD5 algorithm is used to verify the integrity of 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 sys tenant configures the archive destination for tenant mysql_tenant. The archive medium is OSS. The archive file cleanup mode is delete, and the integrity of 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is OSS. The archive file cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of archive files. When enable_worm=true is configured for this path, the system performs write and delete operations on this path according to WORM rules.

    Notice

    OceanBase Database does not configure a compliance retention policy for you. Before you set the archive path, make sure that the OSS bucket retention policy (WORM policy) has been properly configured 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is OSS. The archive file cleanup mode is delete. Zones z1, z2, and z3 can access this archive path.

    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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is AWS S3. The archive file cleanup mode is delete. Optional binding is used, and one piece is switched every 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 sys tenant configures the archive path for tenant mysql_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 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is OBS and 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is GCS and 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is COS. The archive file cleanup mode is delete, and COS is accessed through the S3 protocol.

    Notice

    When you use COS as the archive medium, you must first set the cluster-level parameter ob_storage_s3_url_encode_type. For detailed steps, see Preparation 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 sys tenant configures the archive path for tenant mysql_tenant. The archive medium is Azure Blob. The cleanup mode is delete, the verification algorithm is md5, Optional binding is used, and one piece is switched every 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

  • Tenant mysql_tenant configures the archive path for the current tenant. The archive medium is NFS. Optional binding is used, and one piece is switched every day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • Tenant mysql_tenant configures the archive path for the current tenant. The archive medium is NFS. Region region1 can access this archive path. Optional binding is used, and one piece is switched every day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive?region=region1 BINDING=Optional PIECE_SWITCH_INTERVAL=1d';
    

  • Tenant mysql_tenant configures the archive path for the current tenant. The archive medium is OSS. The archive file cleanup mode is delete, and the MD5 algorithm is used to verify the integrity of 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 configures the archive path for the current tenant. The archive medium is OSS. The archive file cleanup mode is delete, and the integrity of archive files 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';
    

  • Tenant mysql_tenant configures the archive path for the current tenant. The archive medium is OSS. The archive file cleanup mode is delete. IDC IDC3 can access this archive path.

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

  • Tenant mysql_tenant configures the archive path for the current tenant. The archive medium is OSS. The archive file cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of archive files. When enable_worm=true is configured for this path, the system performs write and delete operations on this path according to WORM rules.

    Notice

    OceanBase Database does not configure a compliance retention policy for you. Before you set the archive path, make sure that the OSS bucket retention policy (WORM policy) has been properly configured 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 tenant configures the archive path for the current tenant. The archive medium is AWS S3. The archive file cleanup mode is delete. Optional binding is used, and one piece is switched every 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. The archive medium is AWS S3. The archive file cleanup mode is tagging, and the CRC32 algorithm is used to verify the integrity of 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. The archive medium is OBS and 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=****';
    

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

  • The tenant configures the archive path for the current tenant. The archive medium is COS. The archive file cleanup mode is delete, and COS is accessed through the S3 protocol.

    Notice

    When you use COS as the archive medium, you must first set the cluster-level parameter ob_storage_s3_url_encode_type. For detailed steps, see Preparation 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 configures the archive path for the current tenant. The archive medium is Azure Blob. The cleanup mode is delete, the verification algorithm is md5, Optional binding is used, and one piece is switched every 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