Perform table restore

This topic describes how to restore a table.

Limitations

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

• 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 test in an Oracle tenant, which is stored by the name of TEST in the system. To restore this table, you must specify the table name TEST. Otherwise, the system will report 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

1. Log in as the root user to the sys tenant of the cluster where the destination tenant resides.

2. (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, password must be replaced with the password added during data backup. If the password for full backup is different from the password for incremental backup, you must enter the password for full backup before the password 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 '******','******';
   

3. Restore the specified table.

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

   where:

   • table_name_list specifies the tables to be restored. The list is in the format of database_name.table_name1,database_name.table_name2,..., where the tables are separated with commas (,).

     When specifying database_name and table_name, note that:

     • table_name must match the actual table name stored in the system. For example, if a table named test is created in an Oracle tenant, and the actual table name is TEST, then when restoring the table, the table name must be specified as TEST. Otherwise, the system will throw an error indicating that the table does not exist.

     • For scenarios where database_name or table_name contains special characters, the database_name or table_name with special characters should be enclosed in backticks (``).

     • If you want to restore all tables in a database, specify table_name_list in the format of database_name.*.

     • If you need to restore all user tables in a tenant, specify table_name_list in the format of *.*.

   • dest_tenant_name specifies the name of the destination tenant to which the table is to be restored. You can restore a table only to a user tenant, but not to the sys tenant or a meta tenant.

   • uri specifies the data backup path and the log archive path, which are the same as those in the tenant physical restore command. If the data backup is initiated by using PLUS ARCHIVELOG, you need to specify only one path. Otherwise, the value must contain at least one data backup path and one log archive path, for example, 'file:///backup/archive, file:///backup/data'.

   • {TIME='timestamp'} \| {SCN=scn} specifies the timestamp or SCN to which the table is restored. To restore to a specified timestamp or SCN, join the parameter name and value with an equal sign (=). By default, if you do not specify the UNTIL clause, the table is restored to the latest checkpoint.

   • restore_option specifies the restore options of the auxiliary tenant, including pool_list, locality, primary_zone, and concurrency, which are separated with ampersands (&). We recommend that you set locality and primary_zone to the same values as those of the source tenant.

     If concurrency is not specified, the MAX_CPU value assigned to the auxiliary tenant is used as the default value. In this example, the MAX_CPU value for the auxiliary tenant is 16.

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

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password' specifies the backup information of the key for encrypting the tenant. You need to specify the related information for key backup during restore only when transparent encryption is configured for the source tenant.

     • backup_key_path: the backup path for the key.

     • password: the encryption password specified during key backup.

     For more information about how to back up keys, see Back up a key in Preparations.

   • remap_table_name_list specifies the name of the restored table. You can perform any of the following operations: rename the restored table without changing the database to which the table belongs, rename the restored table to another database without changing the table name, or rename the restored table and rename the table to another database. Use a colon (:) to join the source object and the renamed object. The syntax varies with the operation that you perform:

     • To rename the student table as student2 without changing the database to which the table belongs, use the following syntax: REMAP TABLE school.student:student2.

       By default, if you do not change the database to which the table belongs when you restore the table to the destination tenant, the system will restore the table to the database with the same name as the destination tenant. If the database with the same name does not exist, the table fails to be restored.

     • To change the database to which the table belongs from school to college without changing the table name, use the following syntax: REMAP TABLE school.student:college.student.

     • To rename the student table as student2 and change the database to which the table belongs from school to college, use the following syntax: REMAP TABLE school.student:college.student2.

     • To restore all tables in the school database to the college database, use the following syntax: REMAP TABLE school.*:college.*

     Note

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

   • remap_tablegroup_list specifies the table group to which the restored table belongs. By default, if the source table is bound to a table group, the system restores the table to a table group with the same name in the destination tenant. If the table group with the same name does not exist, the table fails to be restored. If the destination tenant has other table groups, you can execute this statement to restore the table to another table group. Join the source object and the renamed-to object with a colon (:).

     For example, to restore all tables in the source table group tg1 to the newtg1 table group in the destination tenant, use the following syntax: REMAP TABLEGROUP tg1:newtg1.

   • remap_tablespace_list specifies the tablespace to which the restored table belongs. A tablespace is a logical unit in OceanBase Database mainly used for data encryption. By default, if the source table is bound to a tablespace, the system restores the table to the tablespace with the same name in the destination tenant. If the tablespace with the same name does not exist, the table fails to be restored. If the destination tenant has other tablespaces, you can execute this statement to restore the table to another tablespace. Join the source object and the renamed-to object with a colon (:).

     For example, to restore all tables in the source tablespace ts1 to the newts1 tablespace in the destination tenant, use the following syntax: REMAP TABLESPACE ts1:newts1.

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

   Assume that you want to restore the tbl1 and tbl2 tables in the infodb database to the infodb database in the destination tenant, rename the tbl1 table to newtbl, and remap the tables to the newtg1 table group and the newts1 tablespace. 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;
   

What to do next

• After you initiate table restore, you can view 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_pool specifies 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.