Preparations

This topic describes the preparations for log archiving.

(Optional) Configure the log archiving concurrency

Before you enable the archiving mode for a tenant, you can configure the log archiving concurrency to accelerate log archiving for the tenant.

1. Log on to the database as an administrator of the sys tenant or a user tenant.

2. Configure the log_archive_concurrency parameter by using a proper statement.

   The tenant-level parameter log_archive_concurrency specifies the total number of worker threads for log archiving. The modification of this parameter takes effect immediately without restarting the OBServer node. The value range of this parameter is [0, 100]. The default value is 0, which means using the adaptive log archiving concurrency of OceanBase Database. We recommend that you use the default value.

   Notice

   If the tenant is configured with two or four CPU cores, we recommend that you use the default value.

   Methods for adjusting the log archiving concurrency are as follows:

   • Execute the following statement in the sys tenant to adjust the log archiving concurrency of a specified tenant:

     ALTER SYSTEM SET log_archive_concurrency = 10 TENANT = mysql_tenant;
     

   • Execute the following statement in the sys tenant to adjust the log archiving concurrency of all tenants:

     ALTER SYSTEM SET log_archive_concurrency = 10 TENANT = all_user;
     

     Alternatively, execute the following statement:

     ALTER SYSTEM SET log_archive_concurrency = 10 TENANT = all;
     

     Note

     Starting from OceanBase Database V4.2.1, TENANT = all_user and TENANT = all express the same semantics. If you want an operation to take effect on all user tenants, we recommend that you use TENANT = all_user. TENANT = all will be deprecated.

   • Execute the following statement in a user tenant to adjust its log archiving concurrency:

     ALTER SYSTEM SET log_archive_concurrency = 10;
     

   For more information about the log_archive_concurrency parameter, see log_archive_concurrency.

Configure the archive destination

Before you initiate a log archiving job, you must execute the ALTER SYSTEM statement to configure the LOG_ARCHIVE_DEST parameter. The sys tenant is used for managing clusters and does not contain user data or support backup or restore. Therefore, you do not need to configure an archive destination for the sys tenant.

Set the LOCATION, BINDING, and PIECE_SWITCH_INTERVAL attributes of the archive destination.

Considerations

Make sure that the archive path for each tenant is a separate empty directory. You cannot configure the same archive path for different tenants.

Procedure

1. Log on to the database as an administrator of the sys tenant or a user tenant.

   Note

   The administrator user of a MySQL tenant is root and that of an Oracle tenant is SYS.

2. Configure the archive destination.

   • Configure the archive destination for a specified tenant as the sys tenant:

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

   • Configure the archive destination for the current tenant as a user tenant:

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

   Notice

   After upgrading OceanBase Database from V4.0.x to V4.1.0, it is necessary to change the archive path. However, when upgrading from V4.1.x to V4.2.x, there is no need to change the archive path. Moreover, during the upgrade process from V4.1.x to V4.2.x, you have the option to archive logs.

   The details are as follows:

   • (Required) Set LOCATION.

     The LOCATION attribute specifies the archive destination. OceanBase Database allows you to use the following types of archive destinations: Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), Amazon Simple Storage Service (S3), and object storage services that are compatible with the S3 protocol, such as Huawei Object Storage Service (OBS) and Google Cloud Storage (GCS).

     OSS

     NFS

     COS

     S3

     Object storage services compatible with the S3 protocol

     When you use an OSS bucket as the archive destination, besides the archive path and the host, access_key, and access_id parameters, you can also set the delete_mode parameter to configure the cleanup mode for archive files.

     The delete_mode parameter has two values: delete and tagging. If you do not specify this parameter, the default value delete takes effect.

     • delete: The archive files that meet the cleanup requirements are directly deleted.

       In this mode, when you enable automatic cleanup for archive files in automatic mode, the system directly deletes the archive files that meet the requirements.

       Notice

       This is the default cleanup mode. If you do not specify delete_mode, the default cleanup mode delete takes effect.

     • tagging: The archive files that meet the cleanup requirements are tagged and retained.

       In this mode, when you enable automatic cleanup for archive files, the system directly tags the archive files that meet the cleanup requirements. The key of the tag is delete_mode and the value is tagging. This way, you can manage the lifecycle of these files on OSS based on the tag.

     To set an OSS bucket as the archive destination and specify the delete cleanup mode for the mysql_tenant tenant as the sys tenant, execute the following statement:

     obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?
     host=***.aliyun-inc.com
     &access_id=***
     &access_key=***
     &delete_mode=delete' TENANT = mysql_tenant;
     

     To set an OSS bucket as the archive destination and specify the delete cleanup mode for the current tenant as a user tenant, execute the following statement:

     obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=oss://oceanbase-test-bucket/backup/archive?
     host=***.aliyun-inc.com
     &access_id=***
     &access_key=***
     &delete_mode=delete';
     

     Here, oss:// indicates that OSS is used as the archive destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the host address of the bucket. The access_id and access_key parameters specify the access key of OSS. The cleanup mode is set to delete.

     For more information about the automatic cleanup of archived data in delete or tagging mode, see Automatic cleanup of archived data.

     Notice

     When you use NFS as the archive destination, note that:

     • The value of LOCATION cannot be a string with a question mark (?).
     • The value of LOCATION must be an absolute path, and the OBServer node must have read and write privileges on the destination specified by LOCATION.
     • Ensure that all OBServer nodes are mounted to NFS of the same server and use the parameter settings recommended in this topic. For more information about the procedure of deploying NFS, see Deploy the NFS client.

     To set an NFS file system as the archive destination for the mysql_tenant tenant as the sys tenant, execute the following statement:

     obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive' TENANT = mysql_tenant;
     

     To set an NFS file system as the archive destination for the current tenant as a user tenant, execute the following statement:

     obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive';
     

     Here, file:// indicates that NFS is used as the archive destination type, and the archive path is /data/nfs/backup/archive.

     Notice

     If you use COS as the archive destination, note that:

     • The list cache of the bucket must be disabled. Otherwise, a backup inconsistency error occurs. For guidance on how to disable the list cache of a bucket, contact the technical support of COS.
     • To use the APPEND Object API for a bucket, you must disable the multi-AZ feature. If the multi-AZ feature is enabled, an error is reported during archiving.

     COS allows you to configure the cleanup mode of archive files by using the delete_mode parameter in the same way as with OSS.

     • To set a COS bucket as the archive destination and specify the delete cleanup mode for the mysql_tenant tenant as the sys tenant, execute the following statement:

       obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=cos://oceanbase-test-appid/backup/archive?
       host=cos.ap-***.myqcloud.com
       &access_id=***
       &access_key=***&appid=***
       &delete_mode=delete' TENANT = mysql_tenant;
       

     • To set a COS bucket as the archive destination and specify the delete cleanup mode for the current tenant as a user tenant, execute the following statement:

       obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=cos://oceanbase-test-appid/backup/archive?
       host=cos.ap-***.myqcloud.com
       &access_id=***
       &access_key=***&appid=***
       &delete_mode=delete';
       

     Here, cos:// indicates that COS is used as the archive destination type, the bucket name is oceanbase-test-appid, the storage path in the bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the host address of the bucket, that is, the endpoint (without the bucket name) of the bucket. The access_id and access_key parameters specify the access key of COS. The appid parameter is required, and it specifies the APPID of the Tencent Cloud account. The cleanup mode is set to delete.

     S3 allows you to configure the cleanup mode of archive files by using the delete_mode parameter in the same way as with OSS and COS.

     • To set an S3 bucket as the archive destination and specify the delete cleanup mode for the mysql_tenant tenant as the sys tenant, execute the following statement:

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

     • To set an S3 bucket as the archive destination and specify the delete cleanup mode for the current tenant as a user tenant, execute the following statement:

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

     Here, s3:// indicates that S3 is used as the archive destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the domain name of S3. The access_id and access_key parameters specify the access key of the account to access S3. The s3_region parameter is required, and it specifies the region where the S3 bucket is located. The cleanup mode is set to delete.

     If you use an object storage service that is compatible with the S3 protocol and the behaviors of S3 APIs of OceanBase Database, such as OBS or GCS, OceanBase Database allows you to use the object storage service as the archive destination type, and access the object storage in the same way as you access an S3 bucket.

     The following example describes how to set an OBS bucket as the archive destination:

     • To set an OBS bucket as the archive destination for the mysql_tenant tenant as the sys tenant, execute the following statement:

       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;
       

     • To set an OBS bucket as the archive destination for the current tenant as a user tenant, execute the following statement:

       obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=s3://oceanbase-test-bucket/backup/archive?
       host=obs.****.myhuaweicloud.com
       &access_id=****
       &access_key=****';
       

     Here, s3:// indicates that an object storage service compatible with the S3 protocol is used as the archive destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the domain name of OBS. The access_id and access_key parameters specify the access key of the account to access OBS.

     The following example describes how to set a GCS bucket as the archive destination:

     • To set a GCS bucket as the archive destination for the mysql_tenant tenant as the sys tenant, execute the following statement:

       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;
       

     • To set a GCS bucket as the archive destination for the current tenant as a user tenant, execute the following statement:

       obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST=
       'LOCATION=s3://oceanbase-test-bucket/backup/archive?
       host=https://storage.googleapis.com
       &access_id=****
       &access_key=****';
       

     Here, s3:// indicates that an object storage service compatible with the S3 protocol is used as the archive destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the domain name of GCS. The access_id and access_key parameters specify the access key of the account to access GCS.

   • (Optional) Set the BINDING attribute.

     The BINDING attribute specifies the prioritizing mode for archiving and businesses. The BINDING attribute has two values: Optional and Mandatory. If you do not specify this attribute, the default value Optional takes effect.

     • The Optional mode prioritizes user businesses. In this mode, when log archiving falls behind log generation, logs may be recycled before being archived. This causes an interruption.

     • The Mandatory mode prioritizes archiving. In this mode, you may be unable to write more data before existing data is archived.

     To set an NFS file system as the archive destination and set the BINDING attribute for the mysql_tenant tenant as the sys tenant, execute the following statement:

     obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive BINDING=Optional' TENANT = mysql_tenant;
     

     To set an NFS file system as the archive destination and set the BINDING attribute for the current tenant as a user tenant, execute the following statement:

     obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive BINDING=Optional';
     

   • (Optional) Set the PIECE_SWITCH_INTERVAL attribute.

     The PIECE_SWITCH_INTERVAL attribute specifies the switching interval for pieces. The value range is [1d, 7d]. If you do not specify this attribute, the default value 1d takes effect.

     To set an NFS file system as the archive destination and specify to generate a log piece every other day for the mysql_tenant tenant as the sys tenant, execute the following statement:

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

     To set an NFS file system as the archive destination and specify to generate a log piece every other day for the current tenant as a user tenant, execute the following statement:

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

3. Query parameter settings from a view.

   You can query parameter settings from the CDB_OB_ARCHIVE_DEST view in the sys tenant or from the DBA_OB_ARCHIVE_DEST view in a user tenant. For more information, see View the archiving parameter settings.

Considerations

After you configure the LOG_ARCHIVE_DEST parameter, by default, the system creates a file named format in the directory to which the specified destination belongs. This file is used to verify the validity information of the archive destination and ensure the integrity of data in the destination. Therefore, observe the following considerations when you configure the log archive destination:

• If the format file does not exist, the directory where the configured destination is located must be empty so that the LOG_ARCHIVE_DEST parameter can be set. If this directory is not empty, the system returns the -9080 error to indicate that the format file does not exist.

• If the format file exists, its content must be verified so that the parameter can be set. Otherwise, the system returns the -9081 error, indicating that the types of the cluster, tenant, and backup destination in the format file do not match those of the current cluster, tenant, and backup destination.

• When you execute a backup job, the job fails to be initiated if the format file does not exist or fails the verification.

In addition, incremental configuration is not supported after the archive destination is configured. Assume that the BINDING attribute is set to Mandatory and the PIECE_SWITCH_INTERVAL attribute is set to 1d for the archive path /data/nfs/backup/archive in NFS. To change the value of PIECE_SWITCH_INTERVAL to 2d and keep the setting of the BINDING attribute to Mandatory, you still need to specify the values of other attributes in the statement. If you do not specify a value for an attribute, the default value is used for the attribute.

Execute the following statement to modify the attribute settings.

• Modify the attribute settings for a specified tenant as the sys tenant:

  obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST = 'LOCATION=file:///data/nfs/backup/archive BINDING=Mandatory PIECE_SWITCH_INTERVAL=2d' TENANT = mysql_tenant;
  

• Modify the attribute settings for the current tenant as a user tenant:

  obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST = 'LOCATION=file:///data/nfs/backup/archive BINDING=Mandatory PIECE_SWITCH_INTERVAL=2d';
  

Set the lag of log archiving

The tenant-level parameter archive_lag_target of OceanBase Database specifies the lag of log archiving or, in other words, the maximum interval between two consecutive archiving I/O operations in a tenant. The value can be in milliseconds (ms), seconds (s), minutes (m), or hours (h). The default lag is 2 minutes. This parameter ensures that online logs are archived in a timely manner, thus reducing the risk of data loss.

The logs of OceanBase Database are archived by log streams. If logs are written to a log stream, and the duration since the last archiving of the log stream reaches the value of the archive_lag_target parameter, the log stream triggers an archiving operation to archive all unarchived logs.

For example, if you set the archive_lag_target parameter to 120s, each log stream triggers an archiving operation every 2 minutes unless other conditions prevent the operation. For example, the aggregation buffer for log archiving is full. Note that if you set the archive_lag_target parameter to 0, log streams achieve logs in a quasi-real-time manner.

For more information about the archive_lag_target parameter, see archive_lag_target.

Prerequisites

Before you modify the value of the archive_lag_target parameter, make sure that you have configured the archive destination. Otherwise, the following message is returned: log_archive_dest has not been set, set archive_lag_target is not allowed.

Considerations

• If you use OSS, NFS, or COS as the archive destination type, you can set the archive_lag_target parameter to any value within the value range. If you use S3 as the archive destination type, the value of the archive_lag_target parameter cannot be smaller than 60s. Otherwise, the system reports an error.

• We recommend that you set the archive_lag_target parameter to a reasonable value. A small value results in frequent I/O operations, which may downgrade system performance, especially when you use object storage services. A large value may affect the data restore timeliness. Therefore, you need to consider the recovery point objective (RPO) based on your business requirements and the performance of the storage media for log archiving.

Procedure

1. Log on to the database as an administrator of the sys tenant or a user tenant.

2. Select an appropriate statement to set the lag of log archiving as needed.

   • To configure the archive lag time for a specific tenant as the sys tenant, execute the following statement:

     obclient> ALTER SYSTEM SET archive_lag_target = '120s' TENANT = mysql_tenant;
     

     Notice

     When configuring the archive_lag_target parameter, the sys tenant is not allowed to specify all user tenants using TENANT = all_user.

   • To configure the archive lag time for the current tenant as a user tenant, execute the following statements:

     obclient> ALTER SYSTEM SET archive_lag_target = '120s';
     

References

• View the archiving parameter settings

• View the archiving progress

• View the archiving history