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 from the source tenant to the auxiliary tenant from backup media, 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 restoring an auxiliary tenant using this method, 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 restore 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.

• When restoring tables, tables with full-text indexes, JSON multi-valued indexes, or vector indexes are supported.

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

• The source tenant and target tenant 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 in the system 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 Prepare for restore.

• In addition to restoring the table, table-level restore also restores much information associated with that table, but 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 Prepare for 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.

   The restore password for the backup is required only if a password was added during data backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   Here, password needs to 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).

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

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

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

   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 (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 multi-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 a 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 a tenant through the ddl_thread_score parameter. During the cross-tenant table 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 physical restore of the auxiliary tenant phase, 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 CDB_OB_RECOVER_TABLE_JOBS table. For more information, see Physical restore.
     • During the cross-tenant table import phase, you can dynamically adjust the restore concurrency for multiple tables or a single table by modifying the recover_table_concurrency or recover_table_dop parameter of the target tenant. The change takes effect immediately.

4. Run 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 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 in the system. For example, if you create a table named test in an Oracle-compatible tenant, but the system actually stores it as TEST, you must specify TEST when restoring the table. Otherwise, the system reports an error indicating the table does not exist.

     • If database_name or table_name contains special characters, enclose it 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 where 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 was initiated via the PLUS ARCHIVELOG method, only one path is required. Otherwise, enter 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. Restores to this point in time, including it. When specifying to restore to TIME or SCN, you must use = to connect the specified values. If the UNTIL clause is not specified, the latest 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 parameters.

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

     • backup_key_path: The backup path of the key.

     • password: The encryption password set when backing up the key.

     For operations related to key backup, see Back up the key in Preparations 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 to another database. Separate the source object from the renamed object with a colon (:). Example formats:

     • Rename the table student to student2, and keep the database unchanged: REMAP TABLE school.student:student2.

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

     • Keep the table name unchanged, and change the database from school to college: REMAP TABLE school.student:college.student.

     • Rename the table student to student2, and change the database 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 a 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 by default restore 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, you can use this statement to restore the table to another table group. 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 a 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 by default restore 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, you can also use this statement to restore the table to another tablespace. 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 more information, see View the progress of a table-level restore and View the results of a table-level restore.

• After the restore of a specified table is complete, you must also 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 is the name of the resource pool created for the auxiliary tenant during preparation before the 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.