SET LOG_ARCHIVE_DEST

Description

The ALTER SYSTEM SET LOG_ARCHIVE_DEST statement is used to configure an archive destination for a user tenant. The configuration of an archive destination includes parameters such as LOCATION, BINDING, and PIECE_SWITCH_INTERVAL.

Limitations and considerations

• The sys tenant and the meta tenant do not support backup and restore. Therefore, there is no need to configure an archive destination for these tenants.

• At present, OceanBase Database supports backup to NFS, Alibaba Cloud OSS, Tencent Cloud COS, AWS S3, and object storage services compatible with the S3 protocol, such as Huawei Cloud OBS and Google Cloud Storage. Some backup media may have basic requirements. For more information, see the Backup media requirements section in Overview of physical backup and restore.

• When you configure the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL parameters, separate them with spaces, and do not add spaces between the equal sign (=) and the parameter values.

• After an archive destination is configured, incremental configuration is not supported. Namely, you must specify all parameter values in the ALTER SYSTEM SET LOG_ARCHIVE_DEST statement each time you execute it. Otherwise, the unspecified parameters will use default values.

Privilege requirements

You must perform this operation either as the root user of the sys tenant (root@sys) or as the administrator of the tenant. By default:

• 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

Examples

archive_path example for NFS

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=nfs://your-nfs-server-path/your-archive-path [BINDING=your-binding-mode] [PIECE_SWITCH_INTERVAL=your-interval]' [TENANT = your-tenant-name];

The archive_path parameter is explained as follows:

• LOCATION=nfs://your-nfs-server-path/your-archive-path: the NFS server path and the archive path.

archive_path example for 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 [BINDING=your-binding-mode] [PIECE_SWITCH_INTERVAL=your-interval]' [TENANT = your-tenant-name];

The archive_path parameter is explained as follows:

• LOCATION=oss://your-bucket-name/your-archive-path: the Alibaba Cloud OSS bucket and the archive path.
• host=oss-your-region.aliyuncs.com: the access domain name of the Alibaba Cloud OSS service. Replace your-region with the actual region code of OSS.
• access_id=your-access-id and access_key=your-access-key: the access key ID and access key for authentication.

archive_path example for Tencent Cloud COS

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=cos://your-bucket-name/your-archive-path?host=cos.your-region.myqcloud.com&access_id=your-access-id&access_key=your-access-key&appid=your-appid [BINDING=your-binding-mode] [PIECE_SWITCH_INTERVAL=your-interval]' [TENANT = your-tenant-name];

The archive_path parameter is explained as follows:

• LOCATION=cos://your-bucket-name/your-archive-path: the Tencent Cloud COS bucket and the archive path.
• host=cos.your-region.myqcloud.com: the access domain name of the Tencent Cloud COS service. Replace your-region with the actual region code.
• access_id=your-access-id and access_key=your-access-key: the access ID and access key for authentication.
• appid=your-appid: the AppID of the Tencent Cloud account.

archive_path example for Amazon 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 [BINDING=your-binding-mode] [PIECE_SWITCH_INTERVAL=your-interval]' [TENANT = your-tenant-name];

The archive_path parameter is explained as follows:

• LOCATION=s3://your-bucket-name/your-archive-path: the Amazon S3 bucket and the archive path.
• host=s3.your-region.amazonaws.com: the domain name of the Amazon S3 service. Replace your-region with the actual region code of AWS.
• access_id=your-access-id and access_key=your-access-key: the access key ID and access key for authentication.
• s3_region=your-s3-region: the region where the Amazon S3 bucket is located.

Examples

• System tenant

  • The system tenant configures an archive destination for the mysql_tenant tenant. The archive medium is NFS. The user business priority mode is adopted. An archive piece is generated 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 an archive destination for the mysql_tenant tenant. The archive medium is OSS. The archive file deletion mode is tagging.

    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=tagging' TENANT = mysql_tenant;
    

  • The system tenant configures an archive destination for the mysql_tenant tenant. The archive medium is COS. The archive file deletion mode is delete. The archive priority mode is adopted. An archive piece is generated every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST = 'LOCATION=cos://oceanbase-test-bucket/archive?host=cos.ap-xxx.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx&delete_mode=delete BINDING=Mandatory PIECE_SWITCH_INTERVAL=2d' TENANT = mysql_tenant;
    

    • appid indicates the AppID of the Tencent Cloud account. You need to replace it with the actual AppID.

  • The system tenant configures an archive destination for the mysql_tenant tenant. The archive medium is S3. Here is an example of how to set the archive destination and configure the tagging deletion mode:

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=xxx&access_key=xxx&s3_region=xxx&delete_mode=tagging' TENANT = mysql_tenant;
    

    • s3_region indicates the region where the S3 bucket is located.

• User tenant

  • The mysql_tenant tenant configures an archive destination for the current tenant. The archive medium is NFS. The user business priority mode is adopted. An archive piece is generated 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 an archive destination for the current tenant. The archive medium is OSS. The archive file deletion mode is tagging.

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

  • The mysql_tenant tenant configures an archive destination for the current tenant. The archive medium is COS. The archive file deletion mode is delete. The archive priority mode is adopted. An archive piece is generated every other day.

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST = 'LOCATION=cos://oceanbase-test-bucket/archive?host=cos.ap-xxx.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx&delete_mode=delete BINDING=Mandatory PIECE_SWITCH_INTERVAL=2d';
    

    • appid indicates the AppID of the Tencent Cloud account. You need to replace it with the actual AppID.

  • The mysql_tenant tenant configures an archive destination for the current tenant. The archive medium is S3. Here is an example of how to set the archive destination and configure the tagging deletion mode:

    obclient [oceanbase]> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=xxx&access_key=xxx&s3_region=xxx&delete_mode=tagging';
    

    • s3_region indicates the region where the S3 bucket is located.

References

• ARCHIVELOG

• NOARCHIVELOG

• Prepare for log archive