Preparations

This topic describes the preparations for log archiving.

(Optional) Configure the log archiving concurrency

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

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

2. Select an appropriate statement and specify the log_restore_concurrency parameter.

   The tenant-level log_archive_concurrency parameter 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 that OceanBase Database automatically adapts the log archiving concurrency. We recommend that you use the default value.

   Notice

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

   You can modify the log archiving concurrency by using the following methods:

   • Execute the following statement in the sys tenant to modify 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 modify 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

     In OceanBase Database V4.2.1 and later, 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 modify its log archiving concurrency:

     ALTER SYSTEM SET log_archive_concurrency = 10;
     

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 in to the database as an administrator of the sys tenant or a user tenant.

   Note

   The administrator user is the root user in MySQL mode and the SYS user in Oracle mode.

2. Configure the archive destination.

   • Execute the following statement in the sys tenant to configure an archive destination for a specified tenant:

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

   • Execute the following statement in a user tenant to configure its archive destination:

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

   Notice

   After you upgrade OceanBase Database from V4.0.x to V4.1.0, you must change the archive path. However, when you upgrade OceanBase Database from V4.1.x to V4.2.x, you do not need to change the archive path. Moreover, you can archive logs during the upgrade from V4.1.x to V4.2.x.

   Here are some examples:

   • (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). 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.

     OSS

     NFS

     COS

     S3

     Object storage services compatible with the S3 protocol

     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, 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.

     Execute the following statement in the sys tenant to set an OSS bucket as the archive destination and specify the delete cleanup mode for the mysql_tenant tenant:

     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;
     

     Execute the following statement in a user tenant to set an OSS bucket as the archive destination and specify the delete cleanup mode for the tenant:

     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 pair of the account to access 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.
     • Make sure 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.

     Execute the following statement in the sys tenant to set NFS as the archive destination for the mysql_tenant tenant:

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

     Execute the following statement in a user tenant to set NFS as the archive destination for the tenant:

     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

     When you use COS as the archive destination, you can no longer access COS based on the COS protocol. We recommend that you use an access method compatible with the S3 protocol to access COS.

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

     • Execute the following statement in the sys tenant to set a COS bucket as the archive destination and specify the delete cleanup mode for the mysql_tenant tenant:

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

     • Execute the following statement in a user tenant to set a COS bucket as the archive destination and specify the delete cleanup mode for the tenant:

       obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=cos://oceanbase-test-appid/backup/archive?
       host=cos.ap-****x.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, namely the endpoint (without the bucket name) of the bucket. The access_id and access_key parameters specify the access key pair 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.

     Here are some examples:

     • Execute the following statement in the sys tenant to set an S3 bucket as the archive destination and specify the delete cleanup mode for the mysql_tenant tenant:

       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;
       

     • Execute the following statement in a user tenant to set an S3 bucket as the archive destination and specify the delete cleanup mode for the tenant:

       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 pair 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 OBS, GCS, COS, or another object storage service that is compatible with the S3 protocol and the behaviors of S3 APIs of OceanBase Database, 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 examples describe how to set an OBS bucket as the archive destination:

     • Execute the following statement in the sys tenant to set an OBS bucket as the archive destination for the mysql_tenant tenant:

       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;
       

     • Execute the following statement in a user tenant to set an OBS bucket as the archive destination for the tenant:

       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 pair of the account to access OBS.

     The following examples describe how to set a GCS bucket as the archive destination:

     • Execute the following statement in the sys tenant to set a GCS bucket as the archive destination for the mysql_tenant tenant:

       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;
       

     • Execute the following statement in a user tenant to set a GCS bucket as the archive destination for the tenant:

       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 pair of the account to access GCS.

     To set COS as the archive destination, perform the following steps:

     1. Query the value of the cluster-level parameter ob_storage_s3_url_encode_type.

        obclient> SHOW PARAMETERS LIKE '%ob_storage_s3_url_encode_type%';
        

        The query result is as follows:

        +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
        | zone  | svr_type | svr_ip         | svr_port | name                          | data_type | value   | info                                                                                                                                                                                        | section  | scope   | source  | edit_level        |
        +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
        | zone1 | observer | 172.xx.xxx.xxx |     2882 | ob_storage_s3_url_encode_type | NULL      | default | Determines the URL encoding method for S3 requests."default": Uses the S3 standard URL encoding method."compliantRfc3986Encoding": Uses URL encoding that adheres to the RFC 3986 standard. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
        +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
        1 row in set
        

     2. If the value is default, change it to compliantRfc3986Encoding.

        obclient> ALTER SYSTEM SET ob_storage_s3_url_encode_type='compliantRfc3986Encoding';
        

        For more information about this parameter, see ob_storage_s3_url_encode_type.

     3. Specify the archive destination and use the delete_mode parameter to configure the cleanup mode for archive files. Here are some examples:

        Execute the following statement in the sys tenant to set a COS bucket as the archive destination and specify the delete cleanup mode for the mysql_tenant tenant:

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

        Execute the following statement in a user tenant to set a COS bucket as the archive destination and specify the delete cleanup mode for the tenant:

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

        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, 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, namely the endpoint (without the bucket name) of the COS bucket. The access_id and access_key parameters specify the access key pair of COS. The cleanup mode is set to delete.

        Notice

        During log archiving, you cannot change the access protocol used. For example, you cannot switch the access protocol from S3 to OSS or from OSS to S3.

   • (Optional) Set the BINDING attribute.

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

     • The Optional mode prioritizes user business. 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.

     Execute the following statement in the sys tenant to set NFS as the archive destination and set the BINDING attribute for the mysql_tenant tenant:

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

     Execute the following statement in a user tenant to set NFS as the archive destination and set the BINDING attribute for the tenant:

     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 parameter, the default value 1d is used.

     Execute the following statement in the sys tenant to set NFS as the archive destination and specify to generate a log piece every other day for the mysql_tenant tenant:

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

     Execute the following statement in a user tenant to set NFS as the archive destination and specify to generate a log piece every other day for the tenant:

     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 backup 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 statements to modify the attribute settings.

• Execute the following statement in the sys tenant to modify the attribute settings for a specified tenant:

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

• Execute the following statement in a user tenant to modify its attribute settings:

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

(Optional) 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 real time.

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 or another object storage service compatible with the S3 protocol as the archive destination type, the value of the archive_lag_target parameter cannot be less than 60s. Otherwise, the system returns 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 in 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.

   Here are some examples:

   • Execute the following statement in the sys tenant to configure the archive lag time for a specified tenant:

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

   • Execute the following statement in the sys tenant to configure the archive lag time for all user tenants:

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

   • Execute the following statement in a user tenant to configure its archive lag time:

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

References

• View the archiving parameter settings

• View the archiving progress

• View the archiving history