Perform table-level restore

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

Table-level restore methods

In the physical restore phase for the auxiliary tenant during table-level restore, OceanBase Database supports both 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 for the auxiliary tenant used the full restore method. This method requires restoring all data from the source tenant to the auxiliary tenant from the backup media, which leads to the following two issues:

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

To address these issues, OceanBase Database supports the fast restore method for restoring the auxiliary tenant. With fast restore, only a read-only auxiliary tenant is restored. User data in the tenant does not need to be restored from the backup media. Instead, during the cross-tenant table import phase, the backup macro block data of the table to be restored is read directly from the backup media. This method reduces resource usage for temporarily restoring the auxiliary tenant during table-level restore and shortens the recovery task duration.

Limitations and considerations

• Only user tables can be restored. Temporary tables, views, materialized views, materialized view logs, and indexes cannot be restored individually.

• Vector indexes are not supported during table restore.

• Tables in columnar and hybrid row-column storage formats can be restored.

• The source tenant and target tenant must have the same compatibility mode. For example, both must be Oracle-compatible tenants or both must be MySQL-compatible tenants.

• The specified 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, the system stores the table name as TEST. Therefore, you must specify TEST when restoring the table. Otherwise, the system will report 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 a lower version to the same or a higher version. Reverse restore between minor versions within the same major version is not supported. For more information about supported backup data versions for tenant-level restore, see Preparation before recovery.

• In addition to restoring the table, much of the information associated with the table will also be restored during table-level restore. However, some information will not be restored. For details about the restorable schema information, see Description of eestorable schema information in this section.

Prerequisites

Since an auxiliary tenant is required during the restore of a specified table, you must create the necessary resource pool for the auxiliary tenant in the cluster where the target tenant resides before performing table restore. For detailed instructions on creating the required resource pool for the auxiliary tenant, see Preparations before table-level restore.

Procedure

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

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

   You need to set the restore password only when you added a password during data backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   Here, password must be replaced with the password added during backup. If the passwords for full backup and incremental backup are different, you must enter multiple passwords, which are separated with commas (,). The full backup password is entered first, and the incremental backup password is entered later.

   If the passwords for full backup and incremental backup are the same, the statement is as follows:

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

   If the passwords for full backup and incremental backup are different, the statement is as follows:

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

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

   • Single-table parallel restore

     Before you execute table-level restore, you can set the degree of parallelism by using the recover_table_dop parameter. After you set the degree of parallelism, the system uses this parallelism in the following two phases:

     • Primary table data restore phase: The system splits each partition of the primary table into multiple subtasks and restores the subtasks in parallel.
     • Index restore phase: The system rebuilds the table indexes by using the recovered primary table data and parallel execution (PX).

     The syntax is as follows:

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

   • (Optional) Multi-table parallel restore

     Before you execute multi-table table-level restore, you can set the degree of parallelism by using the recover_table_concurrency parameter. After you set the degree of parallelism, multiple tables can restore data in parallel, improving the restore performance.

     The syntax is as follows:

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

   • Set the number of primary table data restore worker threads on each OBServer node of a tenant

     After you set the single-table parallelism recover_table_dop and multi-table parallelism recover_table_concurrency, you also need to set the number of worker threads on each OBServer node of the tenant by using the ddl_thread_score parameter. The primary table data restore phase of table-level restore across tenants depends on the specific DAG threads of the target tenant.

     The syntax is as follows:

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

     Notice

     • During the physical restore auxiliary tenant execution phase, you can dynamically adjust the restore parallelism by modifying the ha_high_thread_score parameter, and the changes take effect immediately. The name of the auxiliary tenant can be found in the table-level restore job progress view CDB_OB_RECOVER_TABLE_JOBS. For more information, see Physical restore.
     • During the cross-tenant table import phase, you can dynamically adjust the parallelism for multi-table or single-table restore by modifying the target tenant's parameter recover_table_concurrency or recover_table_dop, and the changes take effect immediately.

4. Execute the following statement 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 tables to be restored, in the format database_name.table_name1,database_name.table_name2,.... Multiple tables are separated by commas (,).

   When specifying database_name and table_name:

   • The table_name must exactly match the name stored in the system. For example, if you create a table named test in an Oracle-compatible tenant, the actual table name is TEST. Therefore, you must specify TEST when restoring the table; otherwise, the system will return an error indicating that the table does not exist.

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

   • To restore all tables in a database, specify database_name.*.

   • To restore all user tables in a tenant, specify *.*.

   • dest_tenant_name: The target tenant name to which the tables will be restored. You can restore tables only to a user tenant, not to the sys tenant or Meta tenant.

   • uri: The path(s) for data backup and log archiving, consistent with the parameters in the tenant-level physical restore command. If the data backup is initiated by using PLUS ARCHIVELOG mode, you only need to specify one path; otherwise, specify at least two paths for data backup and log archiving, for example: 'file:///backup/archive, file:///backup/data'.

   • {TIME='timestamp'} | {SCN=scn}: The restore endpoint. The system restores to the specified point and includes it. When specifying TIME or SCN, use = to assign the value. If the UNTIL clause is not specified, the system restores to the latest point by default.

   • restore_option: The auxiliary tenant's pool_list, locality, primary_zone, and concurrency parameters, separated by &. It is recommended to keep locality and primary_zone consistent with those of the source tenant.

     If concurrency is not specified, the default value is the MAX_CPU allocated to the auxiliary tenant. For example, in this topic, the system tenant allocates 16 MAX_CPU cores to the auxiliary tenant.

     For more information, see Table-level restore parameters.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': Specifies the key backup information for encrypted tenants. This is required only if the source tenant uses transparent encryption.

     • backup_key_path: The backup path for the key.

     • password: The encryption password set for the backup key.

     For more information, see Backup keys in Preparations before backup.

   • remap_table_name_list: Specifies the new names for restored tables. You can rename only the table name (keeping the database unchanged), rename only the database (keeping the table name unchanged), or rename both. The source object and the target object are separated by a colon (:). For example:

     • Rename table student to student2 without changing the database: REMAP TABLE school.student:student2.

       If the database is unchanged, the system will restore the table to the database with the same name in the target tenant. If the database does not exist, the restore will fail.

     • Rename the database from school to college without changing the table name: REMAP TABLE school.student:college.student.

     • Rename both the table and database: REMAP TABLE school.student:college.student2.

     • Restore all tables in school to college: REMAP TABLE school.*:college.*.

     Note

     If the database_name or table_name to be renamed contains special characters, enclose the entire name in backticks (``).

   • remap_tablegroup_list: Specifies the target table group for restored tables. If the source table is bound to a table group, the system attempts to restore it to the table group with the same name in the target tenant. If the table group does not exist, the restore will fail. You can also restore tables to other table groups in the target tenant by specifying the mapping with a colon (:).

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

   • remap_tablespace_list: Specifies the target tablespace for restored tables. Tablespaces are logical units in OceanBase Database, mainly used for data encryption. If the source table is bound to a tablespace, the system attempts to restore it to the tablespace with the same name in the target tenant. If the tablespace does not exist, the restore will fail. You can also restore tables to other tablespaces in the target tenant by specifying the mapping with a colon (:).

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

   For more information about the parameters, see Table-level restore parameters.

   Restore the tbl1 and tbl2 tables in the infodb database of the infodb database in the target tenant, rename the tbl1 table to newtbl, and redirect the table group and tablespace to newtg1 and newts1, respectively. The statement 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;
   

Description of eestorable schema information

The following table describes the schema information that can be restored after a table-level restore is performed.

What to do next

• After initiating the restore of the specified table, you can view the progress and results of the table restoration. For more information, see Check the progress of table-level restore and Check the results of table-level restore.

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

  DROP RESOURCE POOL restore_pool;
  

  Here, restore_pool indicates the name of the resource pool created for the auxiliary tenant during the preparation phase before restoration.

  For more information about deleting a resource pool, see Delete a resource pool.