Validate a specified backup set or log archive piece

OceanBase Database allows you to validate a specified backup set or log archive piece. You can choose to validate only the specified backup set in the current backup path or only the log archive piece in the current archive path, based on your business needs.

Limitations and considerations

• This topic describes how to verify the backup sets and log archive pieces in the paths currently specified by DATA_BACKUP_DEST and LOG_ARCHIVE_DEST of tenants in the current cluster. If you want to verify all backup sets and log archive pieces in the current backup and archive paths, see Verify the entire backup path and archive path.

  Additionally, if you have changed the backup destination or archive destination and want to verify data in the original backup or archive path, see Verify the backup set or log archive piece in the specified path.

• Verification tasks frequently access the backup media to read data, which may affect the network resources of backup tasks. We recommend that you execute verification tasks when no backup tasks are being executed in the user tenant and the system load is low.

• When you specify the verification level for backup verification:

  • For backup sets and log archive pieces created before V4.6.0, basic verification (file integrity verification) is not supported.

  • For backup sets created before V4.3.4, physical verification (verification of the correctness of physical data and logical consistency) is not supported.

• If the specified backup set contains incremental backup sets, the full backup set on which the incremental backup set depends must exist in the current path. Otherwise, the verification fails.

Validate a specified backup set

Validate a specified backup set for a user tenant by the sys tenant

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

   The following example shows how to connect to the database. Replace the actual environment.

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

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

   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 validate the specified backup set for 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 parameters in the statement are described as follows:

   • BACKUPSET: specifies the backup set to be validated. You can validate all backup sets under the current backup path of the tenant or a specified list of backup sets. If you specify a list of backup sets, separate the backup_set_id values of the backup sets with commas. If you do not specify the backup_set_id value, all backup sets under the current backup path of the tenant are validated by default.

     Notice

     If you do not specify backup_set_id, the system will validate all backup sets under the current backup path, which will generate a large number of validation tasks and take a long time. If you do not need to validate all backup sets, we recommend that you specify backup_set_id in the validation command.

   • LEVEL: an optional parameter that specifies the validation level. The default value is physical. The following table describes the validation levels.

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

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

   • TENANT: specifies the user tenant to be validated. If you do not specify this parameter, all user tenants in the cluster are validated.

   Here are some examples:

   • The sys tenant validates all user tenants under the current backup path of the sys tenant for Basic validation.

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

   • The sys tenant validates all user tenants under the current backup path of the sys tenant for Basic validation.

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

   • The sys tenant validates the backup sets with backup_set_id values of 1 and 2 under the current backup path of the sys tenant for Physical validation.

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

   • The sys tenant validates the backup sets with backup_set_id values of 1 and 2 under the current backup path of the sys tenant for Physical validation.

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

4. After the statement is executed, you can query 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 to view the status, progress, and results of the validation tasks.

   For more information about how to view the backup validation progress, see View the backup validation progress.

   For more information about how to view the backup validation results, see View the backup validation results.

Validate a specified backup set for the current user tenant

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

   Note

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

   The following example shows how to connect to the database. Replace the actual environment.

   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 validate the specified backup set.

   The statement is as follows:

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

   The parameters in the statement are described as follows:

   • BACKUPSET: specifies the backup set to be validated. You can validate all backup sets under the current backup path of the tenant or a specified list of backup sets. If you specify a list of backup sets, separate the backup_set_id values of the backup sets with commas. If you do not specify the backup_set_id value, all backup sets under the current backup path of the tenant are validated by default.

     Notice

     If you do not specify backup_set_id, the system will validate all backup sets under the current backup path, which will generate a large number of validation tasks and take a long time. If you do not need to validate all backup sets, we recommend that you specify backup_set_id in the validation command.

   • LEVEL: an optional parameter that specifies the validation level. The default value is physical. The following table describes the validation levels.

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

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

   Here are some examples:

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

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

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

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

4. After the statement is executed, you can query the DBA_OB_BACKUP_VALIDATE_JOBS, DBA_OB_BACKUP_VALIDATE_TASKS, DBA_OB_BACKUP_VALIDATE_JOB_HISTORY, and DBA_OB_BACKUP_VALIDATE_TASK_HISTORY views to view the status, progress, and results of the validation tasks.

   For more information about how to view the backup validation progress, see View the backup validation progress.

   For more information about how to view the backup validation results, see View the backup validation results.

Validate a specified log archive piece

Initiate backup validation for a specified archive piece of a user tenant

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

   The following example shows how to connect to the database. Replace the actual environment variables as needed.

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

2. (Optional) Query the piece_id of the archive piece 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 statement to initiate backup validation for the specified 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 archive piece to be validated. You can specify the archive piece to be validated, or you can specify a list of archive pieces. If you specify a list of archive pieces, separate the piece_id values with commas. If you do not specify piece_id, all archive pieces under the current archive path of the tenant are validated by default.

     Notice

     If you do not specify piece_id, the system will validate all archive pieces under the current archive path, which will generate a large number of validation tasks and take a long time. If you do not need to validate all archive pieces, we recommend that you specify piece_id in the validation command.

   • LEVEL: an optional parameter that specifies the validation level. The default value is physical. Valid values:

     • basic: validates only the completeness of the file list.

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

   • TENANT: an optional parameter that specifies the user tenant to be validated. If you do not specify this parameter, all user tenants in the cluster are validated.

   Here are some examples:

   • The system 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 initiates basic validation for all archive pieces under the current archive path of the mysqltenant user tenant.

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

   • The system tenant initiates physical validation for the archive pieces with piece_id values of 1 and 2 under the current archive path of the mysqltenant user tenant.

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

   • The system tenant initiates physical validation for the archive pieces with piece_id values of 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 query 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 to view the status, progress, and execution result of the validation task.

   For more information about how to view the backup validation progress, see View the backup validation progress.

   For more information about how to view the backup validation result, see View the backup validation result.

Initiate backup validation for a specified log archive piece of the user tenant

1. The tenant administrator of the user tenant logs in to the database.

   Note

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

   The following example shows how to connect to the database. Please replace the parameters with the actual values in your environment.

   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

   Oracle

   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 statement to initiate backup 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 are described as follows:

   • ARCHIVELOG_PIECE: specifies the log archive piece to be validated. You can validate all log archive pieces in the current archive path or a specified list of log archive pieces. If you specify a list of log archive pieces, separate the piece_id values with commas. If you do not specify piece_id, all log archive pieces in the current archive path are validated by default.

     Notice

     If you do not specify piece_id, the system will validate all log archive pieces in the current archive path, which will generate a large number of validation tasks and take a long time. If you do not need to validate all log archive pieces, we recommend that you specify piece_id in the validation command.

   • LEVEL: an optional parameter that specifies the validation level. The default value is physical. Valid values: basic and physical.

     • basic: validates only the completeness of the file list.

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

   Here are some examples:

   • The user tenant initiates basic validation for all log archive pieces in the current archive path of the user tenant.

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

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

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

4. After the statement is executed successfully, you can query the DBA_OB_BACKUP_VALIDATE_JOBS, DBA_OB_BACKUP_VALIDATE_TASKS, DBA_OB_BACKUP_VALIDATE_JOB_HISTORY, and DBA_OB_BACKUP_VALIDATE_TASK_HISTORY views to view the status, progress, and execution results of the validation tasks.

   For more information about how to view the backup validation progress, see View the backup validation progress.

   For more information about how to view the backup validation results, see View the backup validation results.

References

• Cancel Backup Validation

• View Backup Validation Progress

• View Backup Validation Results

• Validate the Data Backup Destination and Log Archive Destination

• Validate the Backup Set or Archive Piece at the Specified Destination