Perform table restore|V4.2.5| docs|Distributed Database

Perform table restore

Last Updated：2026-01-14 09:30:32 Updated

This topic describes how to restore a table.

Limitations

Only user tables can be restored. Temporary tables, views, indexes, and similar objects cannot be restored individually.
The source tenant and target tenant must have the same compatibility for table restore. For example, both must be Oracle-compatible tenants or both must be MySQL-compatible tenants.
When restoring a table, the specified table name must exactly match the name stored in the system. For instance, if you create a table named test under an Oracle tenant, the system saves it as TEST. Therefore, you must specify TEST when restoring the table; otherwise, the system will report an error stating that the table does not exist.
Table-level restore, like tenant-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 details about supported backup versions for tenant-level restore, see Preparation before restore.
Table-level restore restores the table itself along with most associated information, but some details may not be restored.

Prerequisites

Since an auxiliary tenant is required for table restore, you need to create the required resource pool for the auxiliary tenant in the cluster where the destination tenant resides. For details about how to create the resource pool for the auxiliary tenant, see Prepare for table restore.

Procedure

Log in to the sys tenant of the cluster where the target tenant resides as the root user.
(Optional) If the backup data used for restoring the specified table is encrypted, configure the encryption information of the backup set.

You need to set the password for restoring the backup only when you added a password during full data backup.
```
SET DECRYPTION IDENTIFIED BY 'password';
```
In this statement, replace password with the password added during backup. If the passwords for full backup and incremental backup are different, you need to enter multiple passwords, which are separated by commas. The password for full backup is placed before the password for incremental backup.

Here is an example when the passwords for full backup and incremental backup are the same:
```
SET DECRYPTION IDENTIFIED BY '******';
```
Here is an example when the passwords for full backup and incremental backup are different:
```
SET DECRYPTION IDENTIFIED BY '******','******';
```
(Optional) Set the parallelism for table-level restore.
- Single-table parallel restore
  
  Before you perform table-level restore, you can set the parallelism by using the recover_table_dop parameter. After you set the parallelism, the system uses this parallelism in the following two stages:
  - Main table data restore stage: The system splits each partition of the main table into multiple subtasks for parallel execution.
  - Index restore stage: The system rebuilds the indexes of the table based on the restored main table data by using parallel execution (Parallel Execution, PX).
  Syntax:
```
-- tenant_name specifies the target tenant.
ALTER SYSTEM SET recover_table_dop=INT_VALUE tenant=tenant_name;
```
- Multi-table parallel restore (optional)
  
  Before you perform multi-table table-level restore, you can set the parallelism by using the recover_table_concurrency parameter. After you set the parallelism, multiple tables can be restored in parallel to improve the restore performance.
  
  Syntax:
```
-- tenant_name specifies the target tenant.
ALTER SYSTEM SET recover_table_concurrency=INT_VALUE tenant=tenant_name;
```
- Set the number of worker threads for main table data restore on each observer node of the tenant.
  
  After you set the single-table parallelism recover_table_dop and the multi-table parallelism recover_table_concurrency, you also need to set the number of worker threads for main table data restore on each observer node of the tenant by using the ddl_thread_score parameter. During the cross-tenant table export stage of table-level restore, the system relies on specific DAG threads on the target tenant for main table data restore.
  
  Syntax:
```
-- tenant_name specifies the target tenant.
ALTER SYSTEM SET ddl_thread_score=INT_VALUE tenant=tenant_name;
```
  Notice
  - During physical restore, you can dynamically adjust the parallelism by modifying the ha_high_thread_score parameter. The adjustment takes effect immediately. You can view the name of the auxiliary tenant from the CDB_OB_RECOVER_TABLE_JOBS table of the table-level restore task. For more information, see Physical restore.
  - During the cross-tenant table export stage, you can dynamically adjust the parallelism for multi-table or single-table restore by modifying the recover_table_concurrency or recover_table_dop parameter of the target tenant. The adjustment takes effect immediately.

Execute the following command to restore the specified table.

Syntax:

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];

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

Assume that you want to restore the tbl1 and tbl2 tables from the infodb database of the infodb database of the target tenant, and rename the tbl1 table to newtbl, and redirect the table group and tablespace of the tbl1 table to newtg1 and newts1, respectively. Here is an 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;

Notice

If the table data is successfully restored, the table is considered to be restored, even if the indexes, constraints, or other associated schemas fail to be restored.
After table restore is completed, we recommend that you restore the ddl_thread_score, recover_table_concurrency, and recover_table_dop parameters to their original values. Otherwise, the parallelism for subsequent table-level restores will be based on the set values.

What to do next

After you initiate the restore of the specified table, you can view the progress and results of the table restore in a view. For more information, see View the progress of table restore.
After the specified table is recovered, 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;
```
In this command, restore_pool specifies 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.