Validate a specified backup set or log archive piece

OceanBase Database supports validating a specified backup set or log archive piece. You can choose to validate only the specified backup set under the current backup path or only the log archive piece under the current archive path, depending on your business requirements.

Limitations and considerations

• This topic applies only to validating backup sets or log archive pieces under the currently specified paths in DATA_BACKUP_DEST and LOG_ARCHIVE_DEST for tenants within the current cluster. To validate all backup sets and archive pieces under the current backup and archive paths, see Validate the entire backup and archive path.

  Additionally, for scenarios where the backup or archive destination has been changed, if you need to validate data under the original backup or archive path, see Validate backup sets or log archive pieces in a specified path.

• Since validation tasks frequently access backup media to read data, thereby competing for network resources of backup tasks, it is recommended to execute validation tasks when user tenants are not running backup tasks and the system load is low.

• When specifying the backup validation level:

  • For V4.4.2, backup sets and log archive pieces from versions earlier than V4.4.2 BP2 (excluding V4.4.2 BP2) do not support Basic validation, meaning file integrity validation is not supported.

  • For backup sets from versions earlier than V4.3.4 (excluding V4.3.4), Physical validation is not supported, meaning validation of physical data correctness and logical consistency is not supported.

• When the specified backup set to be validated contains incremental backup sets, at the Physical validation level, the current path must include the full backup set on which the incremental backup set depends; otherwise, validation fails.

Validate a specified backup set

Initiate backup validation for a specified backup set of a user tenant from the system tenant (sys tenant)

1. Log in to the cluster as the root user of the system tenant (sys tenant).

   The following is a sample connection command. Use the actual environment parameters when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. (Optional) Obtain the backup_set_id of the backup set in the user tenant to be validated.

   obclient(root@sys)[(none)]> SELECT a.TENANT_ID, b.TENANT_NAME, a.BACKUP_SET_ID, a.BACKUP_TYPE, a.STATUS, a.FILE_STATUS, a.PATH 
   FROM oceanbase.CDB_OB_BACKUP_SET_FILES a, oceanbase.DBA_OB_TENANTS b 
   WHERE a.TENANT_ID = b.TENANT_ID
   AND b.TENANT_NAME = 'mysqltenant';
   

   The query result is as follows:

   +-----------+-------------+---------------+-------------+---------+-------------+------------------------------------------+
   | TENANT_ID | TENANT_NAME | BACKUP_SET_ID | BACKUP_TYPE | STATUS  | FILE_STATUS | PATH                                     |
   +-----------+-------------+---------------+-------------+---------+-------------+------------------------------------------+
   |      1002 | mysqltenant |             1 | FULL        | SUCCESS | AVAILABLE   | file:///data/nfs/backup/data             |
   |      1002 | mysqltenant |             2 | FULL        | SUCCESS | AVAILABLE   | file:///data/nfs/backup/data             |
   +-----------+-------------+---------------+-------------+---------+-------------+------------------------------------------+
   2 rows in set
   

3. Execute the following command to initiate validation for the specified backup set in the user tenant.

   The statement is as follows:

   ALTER SYSTEM VALIDATE BACKUPSET [= 'backup_set_id[,backup_set_id,...]'] [LEVEL = { 'basic' | 'physical' }] [TENANT = tenant_name];
   

   The relevant parameters in the statement are described as follows:

   • BACKUPSET: Specifies the backup set to be validated. You can validate all backup sets under the tenant's current backup path or a specified list of backup sets. When specifying a list of backup sets, separate the multiple backup set backup_set_ids with commas. If the backup_set_id of a backup set is not specified, all backup sets under the tenant's current backup path are validated by default.

     Note

     If you do not specify backup_set_id, the system will obtain all backup sets under the currently configured backup path for validation, which will result in a large number of validation tasks and take a long time. If you are sure you do not need to validate all backup sets, it is recommended to specify backup_set_id in the validation command.

   • LEVEL: An optional parameter that indicates the validation level. If not specified, the default value is physical. Wherein:

     • basic: checks only the integrity of the file list.

     • physical: checks the correctness of physical data and logical consistency.

   • TENANT: Specifies the user tenant to validate. If not specified, the specified backup sets of all user tenants in the cluster are validated.

   Examples:

   • The system tenant (sys tenant) initiates Basic validation for all backup sets under the current backup path of all user tenants.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE BACKUPSET LEVEL = 'basic';
     

   • The system tenant (sys tenant) initiates Basic validation for all backup sets under the current backup path of user tenant mysqltenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE BACKUPSET LEVEL = 'basic' TENANT = mysqltenant;
     

   • The system tenant (sys tenant) initiates Physical validation for backup sets with backup_set_id 1 and 2 under the current backup path of user tenant mysqltenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE BACKUPSET = '1,2' LEVEL = 'physical' TENANT = mysqltenant;
     

   • The system tenant (sys tenant) initiates Physical validation for backup sets with backup_set_id 1 and 2 under the current backup path of all user tenants.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE BACKUPSET = '1,2' LEVEL = 'physical';
     

4. After the statement is successfully executed, you can view the status, progress, and execution results of the validation tasks through the CDB_OB_BACKUP_VALIDATE_JOBS, CDB_OB_BACKUP_VALIDATE_TASKS, CDB_OB_BACKUP_VALIDATE_JOB_HISTORY, and CDB_OB_BACKUP_VALIDATE_TASK_HISTORY views.

   For detailed operations and information on viewing backup validation progress, see View backup validation progress.

   For detailed operations and information on viewing backup validation results, see View backup validation results.

User tenant initiates backup validation for specified backup sets of the current tenant

1. Log in to the database as the administrator of the user tenant.

   Note

   For a MySQL tenant, the administrator user is the root user. For an Oracle tenant, it is the SYS user.

   The following is a sample connection command. Use the actual environment parameters when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. (Optional) Obtain the backup_set_id of the backup set to be validated.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

   obclient(root@mysqltenant)[(none)]> SELECT BACKUP_SET_ID, BACKUP_TYPE, STATUS, FILE_STATUS, PATH FROM oceanbase.DBA_OB_BACKUP_SET_FILES;
   

   The query statement in Oracle-compatible mode is as follows:

   obclient(SYS@oracletenant)[SYS]> SELECT BACKUP_SET_ID, BACKUP_TYPE, STATUS, FILE_STATUS, PATH FROM SYS.DBA_OB_BACKUP_SET_FILES;
   

3. Execute the following command to initiate validation for the specified backup set.

   The statement is as follows:

   ALTER SYSTEM VALIDATE BACKUPSET [= 'backup_set_id[,backup_set_id,...]'] [LEVEL = { 'basic' | 'physical' }];
   

   The relevant parameters in the statement are described as follows:

   • BACKUPSET: Specifies the backup set to be validated. You can validate all backup sets under the tenant's current backup path or a specified list of backup sets. When specifying a list of backup sets, separate multiple backup_set_id values with commas. If the backup_set_id of a backup set is not specified, all backup sets under the tenant's current backup path are validated by default.

     Note

     If you do not specify backup_set_id, the system will obtain all backup sets under the currently configured backup path for validation, which will result in a large number of validation tasks and take a long time. If you are sure you do not need to validate all backup sets, it is recommended to specify backup_set_id in the validation command.

   • LEVEL: An optional parameter that indicates the validation level. If not specified, the default value is physical. Valid levels are:

     • basic: Verifies only the integrity of the file list.

     • physical: Verifies the correctness of physical data and logical consistency.

   Examples:

   • The user tenant initiates Basic validation for all backup sets under the current backup path of the current tenant.

     obclient> ALTER SYSTEM VALIDATE BACKUPSET LEVEL = 'basic';
     

   • The user tenant initiates Physical validation for backup sets with backup_set_id 1 and 2 under the current backup path of the current tenant.

     obclient> ALTER SYSTEM VALIDATE BACKUPSET = '1,2' LEVEL = 'physical';
     

4. After the statement is executed successfully, you can view the status, progress, and execution results of the validation task through views such as DBA_OB_BACKUP_VALIDATE_JOBS, DBA_OB_BACKUP_VALIDATE_TASKS, DBA_OB_BACKUP_VALIDATE_JOB_HISTORY, and DBA_OB_BACKUP_VALIDATE_TASK_HISTORY.

   For detailed operations and information on viewing backup validation progress, see View backup validation progress.

   For detailed operations and information on viewing backup validation results, see View backup validation results.

Validate a specified log archive piece

Initiate backup validation for a specified log archive piece of a user tenant from the system tenant (sys tenant)

1. Log in to the cluster as the root user of the system tenant (sys tenant).

   The following is a sample connection command. Use the actual environment parameters when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. (Optional) Obtain the piece_id of the log archive piece from the user tenant to be validated.

   obclient(root@sys)[(none)]> SELECT a.TENANT_ID, b.TENANT_NAME, a.ROUND_ID, a.PIECE_ID, a.STATUS, a.FILE_STATUS, a.PATH 
   FROM oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES a, oceanbase.DBA_OB_TENANTS b 
   WHERE a.TENANT_ID = b.TENANT_ID
   AND b.TENANT_NAME = 'mysqltenant';
   

   The query result is as follows:

   +-----------+-------------+----------+----------+--------+-------------+---------------------------------------------+
   | TENANT_ID | TENANT_NAME | ROUND_ID | PIECE_ID | STATUS | FILE_STATUS | PATH                                        |
   +-----------+-------------+----------+----------+--------+-------------+---------------------------------------------+
   |      1002 | mysqltenant |        1 |        1 | ACTIVE | AVAILABLE   | file:///data/nfs/backup/archive             |
   |      1002 | mysqltenant |        1 |        2 | ACTIVE | AVAILABLE   | file:///data/nfs/backup/archive             |
   +-----------+-------------+----------+----------+--------+-------------+---------------------------------------------+
   2 row in set
   

3. Execute the following command to initiate validation for the specified log archive piece of the user tenant.

   The statement is as follows:

   ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE [= 'piece_id[,piece_id,...]'] [LEVEL = { 'basic' | 'physical' }] [TENANT = tenant_name];
   

   The parameters in the statement are described as follows:

   • ARCHIVELOG_PIECE: Specifies the log archive piece to be validated. You can validate all archive pieces under the tenant's current archive path or a specified list of archive pieces. When specifying a list of archive pieces, separate multiple piece_id values with commas. If no piece_id is specified, all log archive pieces under the tenant's current archive path are validated by default.

     Note

     If you do not specify piece_id, the system will obtain all archive pieces under the currently configured archive path for validation, which will result in a large number of validation tasks and take a long time. If you are sure you do not need to validate all archive pieces, it is recommended to specify piece_id in the validation command.

   • LEVEL: An optional parameter that indicates the validation level. If not specified, the default value is physical. Valid levels are:

     • basic: Verifies only the integrity of the file list.

     • physical: Verifies the correctness of physical data and logical consistency.

   • TENANT: Optional. Specifies the user tenant to validate. If not specified, the specified log archive pieces of all user tenants in the cluster are validated.

   Examples:

   • The system tenant (sys tenant) initiates Basic validation for all archive pieces under the current archive path of all user tenants.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE LEVEL = 'basic';
     

   • The system tenant (sys tenant) initiates Basic validation for all archive pieces under the current archive path of user tenant mysqltenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE LEVEL = 'basic' TENANT = mysqltenant;
     

   • The system tenant (sys tenant) initiates Physical validation for log archive pieces with piece_id 1 and 2 under the current archive path of user tenant mysqltenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE = '1,2' LEVEL = 'physical' TENANT = mysqltenant;
     

   • The system tenant (sys tenant) initiates Physical validation for log archive pieces with piece_id 1 and 2 under the current archive path of all user tenants.

     obclient(root@sys)[(none)]> ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE = '1,2' LEVEL = 'physical';
     

4. After the statement is executed successfully, you can view the status, progress, and execution results of the validation task through the CDB_OB_BACKUP_VALIDATE_JOBS, CDB_OB_BACKUP_VALIDATE_TASKS, CDB_OB_BACKUP_VALIDATE_JOB_HISTORY, and CDB_OB_BACKUP_VALIDATE_TASK_HISTORY views, respectively.

   For detailed operations and information on viewing backup validation progress, see View backup validation progress.

   For detailed operations and information on viewing backup validation results, see View backup validation results.

User tenant initiates backup validation for specified log archive pieces of the current tenant

1. Log in to the database as the administrator of the user tenant.

   Note

   For a MySQL tenant, the administrator user is the root user. For an Oracle tenant, it is the SYS user.

   The following is a sample connection command. Use the actual environment parameters when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. (Optional) Obtain the piece_id of the log archive piece to be validated.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

   obclient(root@mysqltenant)[(none)]> SELECT ROUND_ID, PIECE_ID, STATUS, FILE_STATUS, PATH FROM oceanbase.DBA_OB_ARCHIVELOG_PIECE_FILES;
   

   The query statement in Oracle-compatible mode is as follows:

   obclient(SYS@oracletenant)[SYS]> SELECT ROUND_ID, PIECE_ID, STATUS, FILE_STATUS, PATH FROM SYS.DBA_OB_ARCHIVELOG_PIECE_FILES;
   

3. Execute the following command to initiate validation for the specified log archive piece.

   The statement is as follows:

   ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE [= 'piece_id[,piece_id,...]'] [LEVEL = { 'basic' | 'physical' }];
   

   The parameters in the statement are described as follows:

   • ARCHIVELOG_PIECE: Specifies the log archive piece to be validated. You can validate all archive pieces under the tenant's current archive path or a specified list of archive pieces. When specifying a list of archive pieces, separate multiple piece_id values with commas. If no piece_id is specified, all log archive pieces under the tenant's current archive path are validated by default.

     Note

     If you do not specify piece_id, the system will obtain all archive pieces under the currently configured archive path for validation, which will result in a large number of validation tasks and take a long time. If you are sure you do not need to validate all archive pieces, it is recommended to specify piece_id in the validation command.

   • LEVEL: Optional. Specifies the validation level. If not specified, the default value is physical. Valid values:

     • basic: Verifies only the integrity of the file list.

     • physical: Verifies the correctness of physical data and logical consistency.

   Examples:

   • The user tenant initiates Basic validation for all archive pieces under the current archive path of the current tenant.

     obclient> ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE LEVEL = 'basic';
     

   • The user tenant initiates Physical validation for log archive pieces with piece_id 1 and 2 under the current archive path of the current tenant.

     obclient> ALTER SYSTEM VALIDATE ARCHIVELOG_PIECE = '1,2' LEVEL = 'physical';
     

4. After the statement is executed successfully, you can view the status, progress, and execution results of the validation task through views such as DBA_OB_BACKUP_VALIDATE_JOBS, DBA_OB_BACKUP_VALIDATE_TASKS, DBA_OB_BACKUP_VALIDATE_JOB_HISTORY, and DBA_OB_BACKUP_VALIDATE_TASK_HISTORY.

   For detailed operations and information on viewing backup validation progress, see View backup validation progress.

   For detailed operations and information on viewing backup validation results, see View backup validation results.

References

• Cancel backup validation

• View backup validation progress

• View backup validation results

• Validate the entire backup and archive path

• Validate backup sets or log archive pieces in a specified path