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 specify the LOCATION, BINDING and PIECE_SWITCH_INTERVAL parameters for the configuration.

The sys tenant and meta tenants do not support backup and restore. You do not need to configure archive destinations for them.

Limitations and considerations

• When you specify the LOCATION, BINDING and PIECE_SWITCH_INTERVAL parameters, separate them with spaces. There cannot be spaces before or after the equal sign (=) of each parameter value.

• Incremental configuration is not supported after the archive destination is configured. Each time you execute this statement, you must specify all parameters that you need; otherwise, default values are used for unspecified parameters.

Required privileges

Only the root user of the sys tenant (root@sys) or the administrator user of a user tenant can execute this statement.

• The default administrator user in MySQL mode is root.
• The default administrator user in Oracle mode is SYS.

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 for NFS archiving

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

archive_path consists of the following parts:

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

Example of archive_path for OSS archiving

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

archive_path consists of the following parts:

• 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 for COS archiving

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

archive_path consists of the following parts:

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

archive_path consists of the following parts:

• 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

  • In the sys tenant, configure an archive destination for the mysql_tenant tenant. Specifically, set the destination path to an NFS path, specify to prioritize user business, and set the switching interval for pieces to 1d.

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

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

  • In the sys tenant, configure an archive destination for the mysql_tenant tenant. Specifically, set the destination path to a COS path and the cleanup mode for archive files to delete, specify to prioritize archiving, and set the switching interval for pieces to 2d.

    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 your Tencent Cloud account.

  • In the sys tenant, configure an archive destination for the mysql_tenant tenant. Specifically, set the destination path to an S3 path and the cleanup mode for archive files 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;
    

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

• User tenant

  • In the mysql_tenant tenant, configure an archive destination for the current tenant. Specifically, set the destination path to an NFS path, specify to prioritize user business, and set the switching interval for pieces to 1d.

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

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

  • In the mysql_tenant tenant, configure an archive destination for the current tenant. Specifically, set the destination path to a COS path and the cleanup mode for archive files to delete, specify to prioritize archiving, and set the switching interval for pieces to 2d.

    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 your Tencent Cloud account.

  • In the mysql_tenant tenant, configure an archive destination for the current tenant. Specifically, set the destination path to an S3 path and the cleanup mode for archive files 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';
    

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

References

• ARCHIVELOG

• NOARCHIVELOG

• Preparations