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;
     

     or

     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 in 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 a user tenant from the current 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 media as the archive destination: Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), and Amazon Simple Storage Service (S3).

     OSS

     NFS

     COS

     S3

     When you use OSS 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 OSS as the archive destination and configure the delete cleanup mode for the mysql_tenant tenant in 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 OSS as the archive destination and configure the delete cleanup mode for the current 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, the storage bucket name is oceanbase-test-bucket, the path in the storage bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the host address of the storage 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 NFS as the archive destination for the mysql_tenant tenant in 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 NFS as the archive destination for the current 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, 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 also 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 COS as the archive destination and configure the delete cleanup mode for the mysql_tenant tenant in 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 COS as the archive destination and configure the delete cleanup mode for the current 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, the storage bucket name is oceanbase-test-appid, the path in the storage bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the host address of the storage 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 also 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 S3 as the archive destination and configure the delete cleanup mode for the mysql_tenant tenant in 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 S3 as the archive destination and configure the delete cleanup mode for the current 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, the storage bucket name is oceanbase-test-bucket, the path in the storage bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the domain name of the S3 service. The access_id and access_key parameters specify the access key of AWS services. The s3_region parameter is required, and it specifies the region where the S3 storage bucket is located. The cleanup mode is set to delete.

   • (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 NFS as the archive destination and set the BINDING attribute for the mysql_tenant tenant in 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 NFS as the archive destination and set the BINDING attribute for the current 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 NFS as the archive destination and configure to generate a log piece every other day for the mysql_tenant tenant in 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 NFS as the archive destination and configure to generate a log piece every other day for the current 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 in 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 user tenant

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

Configure the archive lag

OceanBase Database uses the tenant-level parameter archive_lag_target to determine the duration that the system waits before archiving log data. This parameter supports units of milliseconds (ms), seconds (s), minutes (m), and hours (h), with a default value of 2 minutes. It specifies the maximum time interval between two consecutive archiving I/O operations, ensuring that online logs are promptly archived to mitigate the risk of data loss.

The database performs archiving on a per-log stream basis. When the current log stream has ongoing log writes and the time since the last archiving action exceeds the interval specified by the archive_lag_target parameter, the log stream initiates an archiving operation to process any unread logs.

For example, if the parameter is configured as archive_lag_target='120s' (equivalent to 2 minutes), the system will try to archive data within each log stream at 2-minute intervals, unless specific conditions, such as a full log archiving buffer, are met. This ensures that the time between consecutive archiving operations for the log stream remains under 2 minutes. Note that when the archive_lag_target parameter is set to 0, the system will perform nearly real-time archiving.

For more information about the archive_lag_target parameter, see archive_lag_target.

Prerequisites

Before modifying the value of the archive_lag_target parameter, ensure that the configuration of the archiving destination has been completed. Otherwise, the system will prompt the message log_archive_dest has not been set, set archive_lag_target is not allowed when attempting to modify the value of the archive_lag_target parameter.

Considerations

• For scenarios where the archiving medium is OSS, NFS, or COS, the value of the archive_lag_target parameter can be set to any value within the specified range. However, for scenarios where the archiving medium is S3, the value of the archive_lag_target parameter cannot be less than 60s. Otherwise, the system will generate an error.

• We recommend that you set the archive_lag_target parameter to a reasonable value to avoid excessive impact on system performance due to frequent I/O operations, especially when using object storage. At the same time, a value set too high may not meet the timeliness requirements for data recovery. Therefore, it is necessary to balance the RPO based on specific business needs and the performance of the archiving medium.

Procedure

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

2. Choose the appropriate statement to configure the archive lag based on the specific scenario. The configuration methods are as follows:

   • 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