This topic describes how to restore a table.
Limitations
Only user tables can be restored. Temporary tables, views, and indexes cannot be restored separately.
Foreign keys, triggers, and statistics on tables cannot be restored.
The source and destination tenants for table restore must be of the same tenant mode. For example, they must be both Oracle tenants or both MySQL tenants.
The specified table name must be consistent with that stored in the system. For example, assume that you have created a table named
testin an Oracle tenant, which is stored with the nameTESTin the system. To restore this table, you must specify the table nameTEST. Otherwise, the system will return an error indicating that the table does not exist.Similar to tenant restore, table restore supports data restore only to a tenant of the same or a later version. You cannot restore tables to a tenant of an earlier version, not even to an earlier minor version.
Prerequisites
An auxiliary tenant is required during table restore. Before you restore a table, you must create a resource pool for the auxiliary tenant in the cluster where the destination tenant resides. For more information about how to create a resource pool for an auxiliary tenant, see Preparations.
Procedure
Log in as the
rootuser to thesystenant of the cluster where the destination tenant resides.(Optional) If the backup data for restoring the table is encrypted, you must configure the encryption information of the backup set.
You need to set a restore password for the backup only if a password is added during data backup.
SET DECRYPTION IDENTIFIED BY 'password';In the statement,
passwordmust be replaced with the password added during data backup. If the password for full backup is different from that for incremental backup, you must enter the password for full backup before that for incremental backup and separate them with a comma.If the password for full backup is the same as that for incremental backup, use the following syntax:
SET DECRYPTION IDENTIFIED BY '******';If the password for full backup is different from that for incremental backup, use the following syntax:
SET DECRYPTION IDENTIFIED BY '******','******';(Optional) Set the concurrency for table restore.
Before you initiate restore jobs for multiple tables, you can use the recover_table_concurrency parameter to set the concurrency for the restore jobs. This way, the system can run the restore jobs concurrently, thus improving the restore performance.
Notice
- Applicable phases:
- This parameter applies only to the cross-tenant table import phase.
- The optional `concurrency` parameter in the `restore_option` field applies only to the physical restore phase. For more information about the table restore process, see Restore process.
- Dynamic modification:
- During table restore, you can use the `recover_table_concurrency` parameter to dynamically modify the concurrency for the cross-tenant table import phase. The modification takes effect immediately.
- After you create a meta tenant for an auxiliary tenant, you can use the ha_high_thread_score parameter to dynamically modify the concurrency for the physical restore phase. The modification takes effect immediately.
-- tenant_name specifies the name of the destination tenant. ALTER SYSTEM SET recover_table_concurrency=INT_VALUE tenant=tenant_name;Restore the specified table.
The syntax 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];For more information about the parameters, see Parameters related to table restore.
Assume that you want to restore the
tbl1andtbl2tables in theinfodbdatabase to theinfodbdatabase in the destination tenant, rename thetbl1table tonewtbl, and remap the tables to thenewtg1table group and thenewts1tablespace. 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
The table restore is considered successful when the table data is restored, even if the indexes, constraints, or other associated schemas fail to be restored.
What to do next
After you initiate table restore, you can query views for the table restore progress and results. For more information, see View the table restore progress.
After a specified table is restored, you need to execute the following statement 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_poolspecifies the name of the resource pool created for the auxiliary tenant before the restore.For more information about how to drop a resource pool, see Drop a resource pool.