SET LOG_ARCHIVE_DEST

Purpose

You can use the ALTER SYSTEM SET LOG_ARCHIVE_DEST statement to configure an archive destination for a user tenant. You can configure an archive destination by using the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL parameters.

Limitations and considerations

• The backup and restore feature is not supported for the sys tenant and meta tenants. Therefore, you do not need to configure an archive destination for them.

• OceanBase Database allows you to use Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), Amazon Simple Storage Service (S3), and other object storage services that are compatible with the S3 protocol, such as Huawei Object Storage Service (OBS) and Google Cloud Storage (GCS), as the backup media. Some backup media can be used only after certain basic requirements are met. For more information about backup media, see Introduction to physical backup and restore.

• When you configure the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL parameters, separate the parameters with spaces and do not leave spaces before and after the equal sign (=) that is used to join the parameter name and value.

• After you configure an archive destination, you must specify values for all parameters each time when you execute the ALTER SYSTEM SET LOG_ARCHIVE_DEST statement. Otherwise, the default value is used for a parameter for which no value is specified.

Required privileges

You must execute this statement as the root user of the sys tenant (namely, root@sys) or as the administrator of a user tenant.

• 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

Parameter examples

Example of archive_path setting when NFS is used as the archive media

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 setting is as follows:

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

Example of archive_path setting when OSS is used as the archive media

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 setting is as follows:

• LOCATION=oss://your-bucket-name/your-archive-path: the name of the OSS bucket and the archive path.
• host=oss-your-region.aliyuncs.com: the domain name used to access OSS. your-region must be replaced with the actual OSS region code.
• access_id=your-access-id and access_key=your-access-key: the AccessKey ID and AccessKey secret used for authentication.

Example of archive_path setting when COS is used as the archive media

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 setting is as follows:

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

Example of archive_path for S3 archiving

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 setting is as follows:

• LOCATION=s3://your-bucket-name/your-archive-path: the name of the S3 bucket and the archive path.
• host=s3.your-region.amazonaws.com: the domain name used to access S3. your-region must be replaced with the actual AWS region code.
• access_id=your-access-id and access_key=your-access-key: the AccessKey ID and AccessKey secret for AWS.
• s3_region=your-s3-region: the region where the S3 bucket is deployed.

Examples

• sys tenant

  • Configure an archive destination for the mysql_tenant tenant from the sys tenant. Specifically, set the destination path to an NFS path, and specify to prioritize user business and generate a new log piece 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;
    

  • Configure an archive destination for the mysql_tenant tenant from the sys tenant. Specifically, set the destination path to an OSS path and the archive file cleanup mode to 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;
    

  • Configure an archive destination for the mysql_tenant tenant from the sys tenant. Specifically, set the destination path to a COS path and the archive file cleanup mode to delete, and specify to prioritize archiving and generate a new log piece every other two days.

    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;
    

    Here, appid indicates the AppID of your Tencent Cloud account.

  • Configure an archive destination for the mysql_tenant tenant from the sys tenant. Specifically, set the destination path to an S3 path and the archive file cleanup mode to tagging.

    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;
    

    Here, s3_region indicates the region where the S3 bucket is deployed.

• User tenant

  • Configure an archive destination for the mysql_tenant tenant from the current tenant. Specifically, set the destination path to an NFS path, and specify to prioritize user business and generate a new log piece every other day.

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

  • Configure an archive destination for the mysql_tenant tenant from the current tenant. Specifically, set the destination path to an OSS path and the archive file cleanup mode to 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';
    

  • Configure an archive destination for the mysql_tenant tenant from the current tenant. Specifically, set the destination path to a COS path and the archive file cleanup mode to delete, and specify to prioritize archiving and generate a new log piece every other two days.

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

    Here, appid indicates the AppID of your Tencent Cloud account.

  • Configure an archive destination for the mysql_tenant tenant from the current tenant. Specifically, set the destination path to an S3 path and the archive file cleanup mode to tagging.

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

    Here, s3_region indicates the region where the S3 bucket is deployed.

References

• ARCHIVELOG

• NOARCHIVELOG

• Preparations