Perform table-level restore

This topic describes how to perform a table-level restore.

Table restore methods

There are two methods for the physical restore auxiliary tenant phase of table restore: full restore and quick restore.

The table restore process consists of three phases: physical restore auxiliary tenant, cross-tenant table import, and cleanup auxiliary tenant. In previous versions, the full restore method was used in the physical restore auxiliary tenant phase. This method requires restoring all data under the source tenant from backup media to the auxiliary tenant, which leads to the following two issues:

1. You must reserve sufficient CPU, memory, and disk resources on the cluster to support the temporary restoration of the auxiliary tenant.
2. Restoring all data from backup media to the auxiliary tenant consumes a significant amount of time and network bandwidth.

To address these issues, OceanBase Database supports restoring auxiliary tenants using the quick restore method. When using this method to restore an auxiliary tenant, only a read-only auxiliary tenant needs to be restored. 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 can be directly read from backup media. This method reduces resource usage for restoring the temporary auxiliary tenant during table restore and shortens the time overhead of the restore task.

After performing table restore, you can query the RESTORE_TYPE column in the CDB_OB_RESTORE_HISTORY view in the sys tenant to confirm the restoration method used for the auxiliary tenant.

Limitations and considerations

• Only user tables can be restored; temporary tables, views, materialized views, materialized view logs, indexes, etc., cannot be restored separately.

• Table restore supports tables with full-text indexes, JSON multi-valued indexes, or vector indexes.

• Tables in columnar and hybrid row-column formats are supported.

• The source and target tenants for table restore must have the same compatibility level. For example, both must be Oracle-compatible tenants or both must be MySQL-compatible tenants.

• When restoring tables, the specified table name must match the actual table name stored in the system. For example, if a table named test is created in an Oracle-compatible tenant, but the actual stored table name is TEST, then the table name TEST must be specified when restoring the table. Otherwise, the system will report an error indicating the table does not exist.

• Similar to backup data versions supported for tenant-level restore, table-level restore currently only supports restoring tables from lower-version backup data to the same or higher version. Reverse restore between minor versions within the same version is also not supported. For detailed information about backup data versions supported for tenant-level restore, see Preparation before restore.

• In addition to restoring the table, table-level restore also restores much information associated with that table. However, some information is not restored. For specific information that can be restored, see Schema restore description for table-level restore.

Prerequisites

Since an auxiliary tenant is required during the restore process for a specified table, you must create the required resource pool for the auxiliary tenant in the cluster where the target tenant resides before performing table restore. For detailed operations on creating the required resource pool for the auxiliary tenant, see Preparation before table restore.

Procedure

1. Log in to the sys tenant of the cluster where the target tenant is located as the root user.

2. (Optional) If encryption was configured for the backup data used to restore the specified table, you need to configure the encryption information for the backup set.

   You only need to set the backup restore password if a password was added during data backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   Here, password should be replaced with the password added during backup. If the passwords set for full backup and incremental backup are different, you need to enter multiple passwords, separated by commas (the full backup password comes first, followed by the incremental backup passwords).

   Example where the passwords for full and incremental backups are the same:

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

   Example where the passwords for full and incremental backups are different:

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

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

   • Single-table parallel restore

     Before performing table-level restore, you can set the concurrency through the parameter recover_table_dop. After setting it, the system will use this parallelism in the following two phases:

     • Primary table data restore phase: The system splits each partition of the primary table into multiple subtasks for parallel execution.
     • Index restore phase: Based on the restored primary table data, the system rebuilds the table's index using parallel execution (Parallel Execution, PX).

     The syntax is as follows:

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

   • Multi-table parallel restore (Optional)

     Before performing a multi-table table-level restore, you can set the concurrency through the parameter recover_table_concurrency. After setting it, multiple tables can execute restore tasks in parallel, improving restore performance.

     The syntax is as follows:

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

   • Set the number of worker threads for restoring primary table data per observer node in the tenant

     After setting the single-table parallelism recover_table_dop and multi-table parallelism recover_table_concurrency, you also need to set the number of worker threads for data restoration per observer node in the tenant through the ddl_thread_score parameter. During the cross-tenant import phase of table-level restore, the restoration of primary table data depends on specific DAG threads on the target tenant.

     The syntax is as follows:

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

     Notice

     • During the execution phase of physical restore in an auxiliary tenant, you can dynamically adjust the restore concurrency by modifying the ha_high_thread_score parameter. The change takes effect immediately. You can view the name of the auxiliary tenant in the task progress table for table-level restore via the CDB_OB_RECOVER_TABLE_JOBS view. For specific adjustment instructions, see Physical Restore
     • During cross-tenant table import, you can dynamically adjust the concurrency for multi-table or single-table restore by modifying the recover_table_concurrency or recover_table_dop parameter of the target tenant. The change takes effect immediately.

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

   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 relevant parameters are described as follows:

   • table_name_list: The tables to be restored, in the format database_name.table_name1,database_name.table_name2,.... Separate multiple tables with commas (,).

     When specifying database_name and table_name:

     • table_name must match the actual table name stored by the system. For example, if a table named test is created in an Oracle-compatible tenant, but the system actually stores it as TEST, you must specify TEST when restoring the table; otherwise, the system will report an error indicating the table does not exist.

     • If database_name or table_name contains special characters, enclose the name in backticks (``).

     • To restore all tables under a database, use database_name.*.

     • To restore all user tables under a tenant, use *.*.

   • dest_tenant_name: The target tenant name to which the tables are to be restored. Tables can only be restored to a user tenant; restoration to the sys or Meta tenant is not supported.

   • uri: Specifies the paths for data backup and log archiving, similar to the parameters in a tenant-level physical restore command. If data backup is initiated via the PLUS ARCHIVELOG method, only one path is required. Otherwise, at least two paths for data backup and log archiving must be provided, for example: 'file:///backup/archive, file:///backup/data'.

   • {TIME='timestamp'} \| {SCN=scn}: The specified restore endpoint. Restores to this point in time, including it. When specifying to restore to TIME or SCN, the specified value must be preceded by =. If the UNTIL clause is not specified, the latest available point in time is restored by default.

   • restore_option: Specifies the pool_list, locality, primary_zone, and concurrency for the auxiliary tenant. Separate different parameters with &. When specifying locality and primary_zone, it is recommended to keep them as homogeneous as possible with the source tenant.

     For concurrency, if not specified, it defaults to the MAX_CPU allocated to the auxiliary tenant. For example, in this document, the system tenant allocates a MAX_CPU of 16 to the auxiliary tenant.

     For detailed descriptions of each parameter, see Table-level restore related parameters.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': Specifies the key backup information for encrypted tenants. Specify this information only if transparent encryption is configured in the source tenant.

     • backup_key_path: The backup path of the key.

     • password: The encryption password set during key backup.

     For operations related to key backup, see Backup key in Prepare before backup.

   • remap_table_name_list: Renames the restored tables. You can rename only the table name while keeping the database unchanged; rename only the database while keeping the table name unchanged; or rename both the table name and its database. Separate the source object from the renamed object with a colon (:). Example formats:

     • The table name is changed from student to student2, and the database remains unchanged: REMAP TABLE school.student:student2.

       When the database remains unchanged, upon restoring the table to the target tenant, the system restores the table to a database with the same name in the target tenant by default. If a database with the same name does not exist, the table restoration will fail.

     • The table name remains unchanged, but the database changes from school to college: REMAP TABLE school.student:college.student.

     • The table name is changed from student to student2, and the database changes from school to college: REMAP TABLE school.student:college.student2.

     • Restore all tables under school to college: <code>REMAP TABLE school.:college.</code>

     Note

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

   • remap_tablegroup_list: Renames the table group to which the table belongs. If the source table is bound to a table group, when restoring it to the target tenant to create a table, the system will default to restoring the table to a table group with the same name in the target tenant. If a table group with the same name does not exist, the table restoration will fail. If there are other table groups in the target tenant, the table can be restored to one of those other table groups using this statement. The source object and the renamed object are separated by a colon (:).

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

   • remap_tablespace_list: Renames the tablespace to which the table belongs. A tablespace is a logical unit in OceanBase Database, currently mainly used for data encryption. If the source table is bound to a tablespace, when restoring it to the target tenant to create a table, the system will default to restoring the table to a tablespace with the same name in the target tenant. If a tablespace with the same name does not exist, the table restoration will fail. If there are other tablespaces in the target tenant, the table can also be restored to one of those other tablespaces using this statement. The source object and the renamed object are separated by a colon (:).

     For example, restore all tables from the source tablespace ts1 to the target tenant's tablespace newts1: REMAP TABLESPACE ts1:newts1.

   For more detailed information about table restore parameters, see Table restore parameters.

   Restore tables tbl1 and tbl2 from the infodb database to the infodb database of the target tenant. Rename table tbl1 to newtbl, redirect its table group to newtg1, and redirect its tablespace to newts1. Example:

   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;
   

What to do next

• After initiating the restore of a specified table, you can view the progress and results of the table restore through views. For specific operations, see View the progress of table-level restore and View the results of table-level restore.

• After the restore of a specified table is completed, you also need to execute 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 represents the name of the resource pool created for the auxiliary tenant during the preparation phase before restore.

  For detailed operations and information on deleting a resource pool, see Delete a resource pool.

References

For information about the recovery status of schema information during table-level restore, see Schema recovery description for table-level restore.