Restore by table

This topic describes how to restore data by table.

Limitations

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

• Tables stored in columnar storage mode or hybrid row-column storage mode are not supported.

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

• The table name specified during the restore must exactly match the name of the table stored in the system. For example, in Oracle-compatible tenant, a table named test is created. However, the system actually stores the table as TEST. Therefore, 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.

• Like tenant-level restore, table-level restore supports restoring data only from a lower version or the same version to another lower version or the same version, and does not support reverse restore between subversions of the same version. For more information, see Prepare for the restore.

• In addition to the specified tables, many related configurations of the tables are also restored during the restore, but some configurations are not. For more information, see the Schema information restored section below.

Prerequisites

A resource pool is required for the restore process, which needs a temporary tenant. Therefore, before you restore a table, you must create a resource pool 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 full backup and incremental backup are encrypted with different passwords, you need to enter both passwords, separated by a comma (full backup password first, then incremental backup password).

   Here is an example where full backup and incremental backup are encrypted with the same password:

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

   Here is an example where full backup and incremental backup are encrypted with different passwords:

   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, in the format of database_name.table_name1,database_name.table_name2,.... Multiple tables are separated with commas (,).

     When you specify database_name and table_name:

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

     • If special characters are included in database_name or table_name, the special characters must be enclosed in 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. Only user tenants, not the sys tenant or the sys tenant, are supported.

   • 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 need to specify only 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 endpoint. The restore is performed to the specified point in time or SCN and the data at the specified point in time or SCN is included in the restore. If you specify TIME or SCN, you must use = to connect the restore endpoint and the specified value. If you do not specify the UNTIL clause, the restore is performed to the latest point in time or SCN.

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

     If you do not specify concurrency, the default value, which is equal to the maximum number of CPU cores allocated to the auxiliary tenant, takes effect. For example, in this topic, the system allocates 16 CPU cores to the auxiliary tenant in the sys 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. This clause is required only when the source tenant has been transparently encrypted.

     • backup_key_path: the backup path of the tenant's encryption key.

     • password: the encryption password of the backup key.

     For more information about how to back up the encryption key, see the Backup encryption key section in Prepare before 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 separated with a colon (:). Here are some examples:

     • Rename the table named student to student2 in the same database: REMAP TABLE school.student:student2.

       If the database name remains unchanged during the restore, the system will restore the table to the database with the same name in the destination tenant by default when you create the table. If no database with the same name exists, 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 that is being remapped, the special characters must be enclosed in backticks (`).

   • remap_tablegroup_list: the new names of the table groups to which the tables belong. If the source table is bound to a table group, by default 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, the table restore will fail. If the destination tenant has other table groups, you can restore the table to another table group. The source and target table groups are separated with a colon (:).

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

   • remap_tablespace_list: the new names of the table spaces to which the tables belong. In OceanBase Database, a table space is a logical unit that is primarily used for data encryption. If the source table is bound to a table space, by default 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, the table restore will fail. If the destination tenant has other table spaces, you can restore the table to another table space. The source and target table spaces are separated with a colon (:).

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

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

   Restore the tables tbl1 and tbl2 from the infodb database to the infodb database in the destination tenant, rename the tbl1 table to newtbl, and redirect the table group and table space to which the tbl1 table belongs. 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;
   

Schema information after restore

After a restore by table, the following schema information is restored.

Next steps

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

• 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.