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 parameters for configuring an archive destination include LOCATION, BINDING, and PIECE_SWITCH_INTERVAL.

Backup and restore are not supported for system tenants and meta tenants. Therefore, you do not need to configure an archive destination for these tenants.

Limitations and considerations

• When you configure the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL parameters, separate them with spaces and ensure that there is no space before and after the equal sign (=) for each parameter.

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

Privilege requirements

You must perform this operation either by the root user of the sys tenant (root@sys) or by the administrator of each 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 an NFS archive

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 archive path.

archive_path example for an Alibaba Cloud OSS archive

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 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 a Tencent Cloud COS archive

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 storage bucket and 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 an Amazon S3 archive

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 storage bucket and 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 storage 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. A 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 cleanup mode of archive files 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 cleanup mode of archive files is delete. The archive files are generated in the priority mode. A 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 it for the tagging cleanup 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. A 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 cleanup mode of archive files 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 cleanup mode of archive files is delete. The archive files are generated in the priority mode. A 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 it for the tagging cleanup 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