Restore data 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.

• The source and target tenants must be compatible. For example, you can restore a table from an Oracle-compatible tenant to another Oracle-compatible tenant or a MySQL-compatible tenant.

• The name of the table to be restored must exactly match the name of the table stored in the system. For example, in Oracle mode, if you create a table named test but the system stores it 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.

• Like tenant-level restore, table-level restore is supported only from a lower version to the same version or a higher version. In the same version, reverse restore is not supported between minor versions.

• In addition to the specified table, many attributes related to the table will be restored during table restore. However, some attributes are not restored. For more information, see the Schema information restored section in this topic.

Prerequisites

A restore process for a specified table requires a temporary tenant. Therefore, before you restore a table, you need to create a resource pool for the temporary tenant in the cluster to which the target tenant belongs. For more information, see Prepare for a table 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 only full backup is performed, you need to configure the password for restoring the backup data.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   In this example, you need to replace password with the password added when the backup data is backed up. If the password for full backup is different from that for incremental backup, you need to enter both passwords, separated by a comma (,). The password for full backup should be entered first, and then the password for incremental backup.

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

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

   Here is an example where the passwords for 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 format is database_name.table_name1,database_name.table_name2,.... Multiple tables are separated with commas (,).

     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 when you restore the table. Otherwise, the system will return an error because the table does not exist.

     • If database_name or table_name contains special characters, you must enclose the database_name or table_name 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 to which the table is to be restored. You can restore a table only to a user tenant. It cannot be the sys tenant or the meta tenant.

   • uri: the path for restoring data backup and log archive. The format is the same as that for a tenant-level physical restore. If a data backup is initiated through PLUS ARCHIVELOG, you only need to specify the path of the archive. Otherwise, you need to specify the paths of both the data backup and the log archive at least. For example: file:///backup/archive, file:///backup/data.

   • {TIME='timestamp'} \| {SCN=scn}: the restore end point. The data and settings in the tenant are restored to the specified end point, which must be included in the end point. If you want to restore the data and settings to a specific time or SCN, you must use the = sign to connect the time or SCN and the specified value. If you do not specify the UNTIL clause, the data and settings are restored 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 with &. We recommend that you specify the same values as those in the source tenant for locality and primary_zone to ensure homogeneity.

     If you do not specify concurrency, the default value, which is equal to the maximum number of CPU cores allocated to the auxiliary tenant (MAX_CPU), is used. For example, in this topic, the system has allocated 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. You need to specify this parameter only when the source tenant has transparent encryption enabled.

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

     • password: the encryption password set when backing up the encryption key.

     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 but keep the database names unchanged, or you can keep the table names unchanged but rename the databases. You can also rename both the table names and the databases. The source and target table names are separated with a colon (:). Here are some examples:

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

       If the database name remains unchanged when you restore the tables, the system will restore the tables to the database with the same name in the destination tenant by default. 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 student table 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 renamed database_name or table_name, the corresponding name 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, when you restore the tables to the destination tenant, the system will restore the tables 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 tables to one of the other table groups. The source and target table group names are separated with a colon (:).

     For example, restore all tables in the tg1 table group to the newtg1 table group 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, which is currently mainly used for data encryption. If the source table is bound to 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, 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 table space names are separated with a colon (:).

     For example, restore all tables in the ts1 table space to the newts1 table space: 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 the newtg1 table group, and the table space to which the tbl1 table belongs to the newts1 table space. The sample command 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 you restore tables, the following schema information is restored. The information that cannot be restored is described as well.

Next steps

• After you initiate a restore for a specific table, you can query the corresponding views 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 of 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.