Restore by table

This topic describes how to restore data by table.

Methods

During the physical restore of an auxiliary tenant, there are two methods for table-level restore: full restore and fast restore

The table-level restore process consists of three phases: physical restore of the auxiliary tenant, cross-tenant table import, and cleanup of the auxiliary tenant. In earlier versions, the physical restore phase of the auxiliary tenant used the full phases method, which required all data from the source tenant to be restored from backup media to the auxiliary tenant. This approach led to two main issues:

1. Users needed to reserve sufficient CPU, memory, and disk resources in the cluster to support the temporary restore of the auxiliary tenant.
2. Restoring all data from backup media to the auxiliary tenant consumed significant time and network bandwidth.

To address these issues, starting from OceanBase Database V4.3.5 BP1, quick restore was introduced as a new method to restore the auxiliary tenant. With this method, only a read-only auxiliary tenant needs to be set up, and user data within the tenant does not need to be restored from backup media. Instead, during the cross-tenant table import phase, the backup macroblock data for the tables to be restored is directly accessed from the backup media. This approach reduces the resource usage for temporary auxiliary tenant restore and significantly shortens the time required for the restore task.

Limitations and considerations

• Only user tables are supported for restoration. Temporary tables, views, materialized views, materialized view logs, and indexes cannot be restored individually.

• Vector indexes are not supported for restoration during table restore.

• For OceanBase Database V4.3.5, the restoration of tables stored in columnar format or hybrid row-column format is supported starting from V4.3.5 BP2.

• The source and target tenants must both be Oracle-compatible or both be MySQL-compatible during table restoration.

• When restoring a table, the specified table name must match the actual table name stored in the system. For example, if a table named test is created under an Oracle-compatible tenant, the system stores it as TEST. Therefore, you must specify the table name as TEST during restoration; otherwise, the system will return an error indicating that the table does not exist.

• Similar to tenant-level restore, table-level restore only supports restoring tables from backup data of older versions to the same version or a newer version. Reverse restore between minor versions of the same major version is not supported. For more details about the backup data versions supported by tenant-level restore, see Prepare for restore.

• In addition to the table, much of the information related to it is restored during table restore. However, certain information is not restored. For details about what is restored, see the Restored schema information section in this topic.

Prerequisites

Since auxiliary tenants are used during the restoration of specified tables, you need to create the required resource pools for auxiliary tenants in the cluster where the target tenant resides before restoring tables. For detailed steps on creating the required resource pools for auxiliary tenants, see Preparation for specified table restoration.

Procedure

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

2. (Optional) If the backup data used to restore the specified table is encrypted, you need to configure the encryption information of the backup set.

   This step is only required when a password was set during data backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   Here, replace password with the password that was set during the backup. If the passwords for full backups and incremental backups are different, you need to provide multiple passwords separated by commas in English (the password for the full backup should be placed first, followed by the password for the incremental backup).

   An example where the passwords for full and incremental backups are the same is as follows:

   SET DECRYPTION IDENTIFIED BY '******';
   

An example where the passwords for full and incremental backups are different is as follows:

SET DECRYPTION IDENTIFIED BY '******','******';

3. (Optional) Set table-level restore parallelism.

   • Single-table parallel restore

     Before performing table-level restoration, you can set the degree of parallelism through the parameter recover_table_dop. Once configured, the system will use this parallelism in the following two phases:

     • Primary table data restoration phase: The system will split each partition of the primary table into multiple subtasks and execute them in parallel.
     • Index restoration phase: Based on the restored primary table data, the system will rebuild the table's indexes using parallel execution (PX).

     The syntax is as follows:

     -- tenant_name specifies the target tenant.
     ALTER SYSTEM SET recover_table_dop=INT_VALUE tenant=tenant_name;
     

   • (Optional) Restore multiple tables in parallel

     Before performing multi-table restoration, you can set the degree of parallelism through the parameter recover_table_concurrency. Once configured, multiple tables can execute restoration tasks in parallel, improving restoration performance.

     The syntax is as follows:

     -- tenant_name specifies the target tenant.
     ALTER SYSTEM SET recover_table_concurrency=INT_VALUE tenant=tenant_name;
     

   • Set the number of primary table data restoration worker threads on each OBServer node for the tenant

     After setting the single-table parallelism recover_table_dop and multi-table parallelism recover_table_concurrency, you also need to configure the number of data restoration worker threads on each OBServer node for the tenant using the parameter ddl_thread_score. During the cross-tenant table import phase of table-level restoration, the restoration of main table data relies on specific DAG threads on the target tenant.

     The syntax is as follows:

     -- tenant_name specifies the target tenant.
     ALTER SYSTEM SET ddl_thread_score=INT_VALUE tenant=tenant_name;
     

     Notice

     • Physical restore phase of auxiliary tenants:You can dynamically adjust the parallelism of the restore by modifying the ha_high_thread_score parameter, which takes effect immediately. The name of the auxiliary tenant can be found in the table-level restore task progress table CDB_OB_RECOVER_TABLE_JOBS. For specific adjustments, see Physical restore.
     • Cross-tenant table import phase: You can dynamically adjust the parallelism of multi-table/single-table restore by modifying the target tenant's recover_table_concurrency or recover_table_dop parameters, which take effect immediately.

4. Execute the following command to restore the specified table.

The SQL statement is as follows:

ALTER SYSTEM 
RECOVER TABLE table_name_list 
TO [TENANT [=]] dest_tenant_name 
FROM uri 
[UNTIL {TIME='timestamp'} | {SCN=scn} ]
WITH 'restore_option' 
[WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password']
[REMAP TABLE remap_table_name_list] 
[REMAP TABLEGROUP remap_tablegroup_list] 
[REMAP TABLESPACE remap_tablespace_list]
[DESCRIPTION [=] description];

The related parameters are described as follows:

• table_name_list: the table to be restored, in the format of database_name.table_name1,database_name.table_name2,.... The names of the tables are separated with commas (,).

When you specify database_name and table_name:

 * `table_name` must be the same as the actual table name stored in the system. For example, if you create a table named `test` in an Oracle tenant, the actual table name stored in the system is `TEST`. Therefore, you must specify the table name as `TEST` when restoring the table, otherwise the system will return an error indicating that the table does not exist.

 * If `database_name` or `table_name` contains special characters, the actual `database_name` or `table_name` must be enclosed in backticks (``).

 * You can use `database_name.*` to specify all tables in a database.

 * You can use `*.*` to specify all user tables in a tenant.

• dest_tenant_name: the name of the target tenant to which the table is to be restored. You can restore a table only to a user tenant, but not to the sys tenant or Meta tenant.

• uri: the paths for data backup and log archiving, which are the same as those for tenant-level physical restore commands. If the data backup is initiated by using PLUS ARCHIVELOG, you only need to specify one path. Otherwise, you need to specify at least two paths for data backup and log archiving, for example: file:///backup/archive, file:///backup/data.

• {TIME='timestamp'} \| {SCN=scn}: the specified restore endpoint, which is included in the restore operation. The restore operation is performed until this point. When you specify to restore to TIME or SCN, you must use = to connect the parameter and the value. If you do not specify the UNTIL clause, the system restores to the latest point by default.

• restore_option: specifies the auxiliary tenant's pool_list, locality, primary_zone, and concurrency. The parameters are separated with &. We recommend that you configure the locality and primary_zone parameters to be consistent with those in the source tenant.

If you do not specify the concurrency parameter, it defaults to the maximum number of CPU cores available to the auxiliary tenant. In this topic, the system tenant allocates 16 CPU cores to the auxiliary tenant.

 For more information about the parameters, see [Parameters related to table restore](600.parameters-of-the-table-recovery.md).

• WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': specifies the key backup information for transparently encrypted tenants. You only need to specify this clause when the source tenant is transparently encrypted.

  • backup_key_path: the path for backing up the key.

  • password: the encryption password used during key backup.

  For more information about key backup, see Backup the key in Prepare before data backup.

• remap_table_name_list: the new name of the restored table. You can change only the table name while keeping the database unchanged, or change only the database name while keeping the table name unchanged, or change both the table name and the database name. The syntax is as follows:

  • Change the table name from student to student2 while keeping the database unchanged: REMAP TABLE school.student:student2.

  If the database is unchanged, by default the system restores the table to a database with the same name in the target tenant. If no database with the same name exists in the target tenant, the restore operation will fail.

  • Change the database from school to college while keeping the table name unchanged: REMAP TABLE school.student:college.student.
  • Change the table name from student to student2 and change the database from school to college: REMAP TABLE school.student:college.student2.
  • Restore all tables in school to college: REMAP TABLE school.*:college.*

  Note

  If the new database_name or table_name contains special characters, the actual database_name or table_name must be enclosed in backticks (``).

• remap_tablegroup_list: the new name of the table group to which the table belongs. You can restore the table to another table group in the target tenant by using this clause. The syntax is as follows:

  For example, restore all tables in the source table group tg1 to the table group newtg1 in the target tenant: REMAP TABLEGROUP tg1:newtg1.

• remap_tablespace_list: the new name of the restored table. A tablespace in OceanBase Database is a logical unit primarily used for data encryption. If the source table is bound to a tablespace, the system will, by default, restore the table to a tablespace with the same name in the target tenant when creating the table during restoration. If a tablespace with the same name does not exist, the table restoration will fail. If there are other tablespaces in the target tenant, you can use this statement to restore the table to a different tablespace. The source object and the renamed object are connected using a colon (:).

  For example, restore all tables in the source table space ts1 to the table space newts1 in the target tenant: REMAP TABLESPACE ts1:newts1.

For more information about the parameters, see Parameters related to table restore.

Restore the tables tbl1 and tbl2 in the infodb database to the infodb database in the target tenant. At the same time, change the name of the table tbl1 to newtbl, and change the table group and table space to newtg1 and newts1, respectively. The syntax is as follows:

ALTER SYSTEM 
RECOVER TABLE infodb.tbl1,infodb.tbl2 
TO TENANT oracle001 
FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' 
UNTIL TIME='2023-09-30 00:00:00' 
WITH 'pool_list=restore_pool'
REMAP TABLE infodb.tbl1:newtbl 
REMAP TABLEGROUP tg1:newtg1
REMAP TABLESPACE ts1:newts1;

Schema information for restore

The following table describes the schema information that is restored by using the table restore method and the descriptions of the schema information.

What to do next

• After you initiate a restore for a specified table, you can view the progress and results of the table restore in the view. For more information, see View the progress of a table restore and View the results of a table restore.

• After the restore of the specified table is completed, you also need to run the following command in the cluster where the auxiliary tenant resides to manually release the resource pool created for the auxiliary tenant.

  DROP RESOURCE POOL restore_pool;
  

  Here, restore_pool is the name of the resource pool created for the auxiliary tenant before the restore.

  For more information about how to delete a resource pool, see Delete a resource pool.