Restore tables

This topic describes how to restore tables.

Limitations

• Only user tables can be restored. Temporary tables, views, and indexes cannot be restored.

• The source and target tenants must be compatible. For example, both must be Oracle-compatible tenants or both must be MySQL-compatible tenants.

• The name of the table to be restored must exactly match the name of the table stored in the system. For example, in Oracle-compatible mode, if you create a table named test but the system actually stores the table as TEST, you must specify the table name as TEST during the restore. Otherwise, the system will return an error, indicating that the table does not exist.

• Table restore supports only the same versions as tenant restore. In other words, you can restore a table from a backup created in the same version or a lower version. For example, you can restore a table from a minor backup in a major version. For more information, see Prepare for the restore.

• When you restore a table, the system will restore a lot of information related to the table. However, some information is not restored. For more information, see the Schema information restored section below.

Prerequisites

A restore for a specified table requires a temporary tenant. Therefore, before you restore a table, you must create a resource pool for the temporary tenant in the cluster where the target tenant resides. For more information, see Prepare for the restore.

Procedure

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

2. (Optional) If the backup data of the specified table is encrypted, configure the encryption information of the backup set.

   If a password is added when you back up data, you must configure a password for restoring the backup data.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   In this example, you must replace password with the password added when you back up data. If the full backup and incremental backup have different passwords, you need to enter both passwords, separated by a comma (the password of the full backup placed in front of the password of the incremental backup).

   Here is an example where the passwords of the full backup and incremental backup are the same:

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

   Here is an example where the passwords of the full backup and incremental backup are different:

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

3. Run the following command to 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];
   

   The parameters are described as follows:

   • table_name_list: the name of the table to restore. The tables are separated by commas (,). The format is database_name.table_name1,database_name.table_name2,....

     When you specify database_name and table_name:

     • You must specify table_name exactly the same as the name stored in the system. For example, if you create a table named test in Oracle mode, the system actually stores the table name as TEST. Therefore, you must specify the table name as TEST during restore. Otherwise, the system will return an error because the table does not exist.

     • If special characters are included in database_name or table_name, enclose the database_name or table_name with backticks (`).

     • You can specify database_name.* to restore all tables in a database.

     • You can specify *.* to restore all user tables in a tenant.

   • dest_tenant_name: the name of the destination tenant where the table is to be restored. You can restore a table only to a user tenant. It cannot be the sys tenant or the sys tenant.

   • uri: the path where the data backup is stored and the path where the log archive is stored. It is the same as the parameter in a tenant-level physical restore. If the data backup is initiated through PLUS ARCHIVELOG, you only need to specify one path. Otherwise, you need to specify at least two paths for data backup and log archive, respectively. For example: file:///backup/archive, file:///backup/data'.

   • {TIME='timestamp'} \| {SCN=scn}: the restore end point. The data and settings restored to this point (including this point) will be retained. If you specify the UNTIL clause, you must use the format = to connect the specified value. If you do not specify the UNTIL clause, the system will restore data and settings to the latest point by default.

   • restore_option: specifies the pool_list, locality, primary_zone, and concurrency of the auxiliary tenant. The parameters are separated by &. We recommend that you specify homogeneous values for locality and primary_zone as those in the source tenant.

     If you do not specify the concurrency parameter, the system will restore data with the maximum number of concurrent threads equal to the number of CPU cores allocated to the auxiliary tenant. For example, in this topic, the system allocates 16 CPU cores to the auxiliary tenant.

     For more information about the parameters, see Parameters for restoring a table.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': the encryption information of the tenant to be restored. You need to specify this parameter only when the source tenant has transparent encryption enabled.

     • backup_key_path: the path where the encryption key of the tenant is backed up.

     • password: the password for encrypting the encryption key, which is the same as the one added during backup.

     For more information about how to back up the encryption key, see the Backup encryption key section in Prepare for data backup.

   • remap_table_name_list: the new names of the restored tables. You can rename only the table names, keep the table names unchanged but rename the databases to which the tables belong, or rename both the table names and the databases to which the tables belong. The source and target objects are connected by using colons (:). Here are some examples:

     • Rename the table named student to student2. The database to which the table belongs remains unchanged: REMAP TABLE school.student:student2.

       If the database to which the table belongs remains unchanged, when you restore the table to the destination tenant, the system will restore the table to the database with the same name in the destination tenant. If no database with the same name exists in the destination tenant, the table restore will fail.

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

     • Rename the table named student to student2, and change the database name from school to college: REMAP TABLE school.student:college.student2.

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

     Note

     If special characters are included in the database_name or table_name of the new name, enclose the database_name or table_name with backticks (`).

   • remap_tablegroup_list: the new names of the table groups to which the tables belong. If the source table is bound with a table group, when you restore the table to the destination tenant, the system will restore the table to the table group with the same name in the destination tenant. If no table group with the same name exists in the destination tenant, the table restore will fail. If the destination tenant has other table groups, you can restore the table to one of the other table groups. The source and target objects are connected by using colons (:).

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

   • remap_tablespace_list: the new names of the table spaces to which the tables belong. Table spaces are logical units in OceanBase Database, currently used mainly for data encryption. If the source table is bound with a table space, when you restore the table to the destination tenant, the system will restore the table to the table space with the same name in the destination tenant. If no table space with the same name exists in the destination tenant, the table restore will fail. If the destination tenant has other table spaces, you can restore the table to one of the other table spaces. The source and target objects are connected by using colons (:).

     For example, restore all tables in the source table space ts1 to the destination table space newts1: REMAP TABLESPACE ts1:newts1.

   For more information about the parameters, see Parameters for restoring a table.

   Restore the tbl1 and tbl2 tables from the infodb database to the infodb database of the destination tenant, and rename the tbl1 table to newtbl. Additionally, redirect the table group to which the tbl1 table belongs to newtg1, and the table space to which the tbl1 table belongs to newts1. The sample code 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;
   

Schema information after restore

After a table restore, the following schema information is restored, along with the tables.

Next steps

• After you initiate a restore for a specific table, you can query the view for the restore progress and result. For more information, see Query the progress of restoring a table and Query the result of restoring a table.

• After the restore for a specific table is completed, you must manually release the resource pool created for the auxiliary tenant in the cluster where the tenant resides by running the following command.

  DROP RESOURCE POOL restore_pool;
  

  Here, restore_pool is 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.