Restore data by table

This topic explains 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 have the same compatibility. For example, both must be Oracle-compatible tenants or both must be MySQL-compatible tenants.

• When restoring a table, specify the actual name of the table stored in the system. For example, in Oracle-compatible tenant, a table named test is created, but 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.

• Table-level restore supports only the same versions of backup data that tenant-level restore supports. In other words, you can restore a table from a lower version to the same version or a higher version. However, reverse restore between subversions is not supported. For more information, see Prepare for the restore.

• In addition to restoring the table, many details related to the table, such as the table metadata, privileges, and schemas, are also restored. However, some details, such as the data in some system tables, are not restored. For more information, see the Schema information restored section in this topic.

Prerequisites

A resource pool is required for the restore process, which uses a temporary tenant. Therefore, before you restore a table, you must create a resource pool in the cluster to which the target tenant belongs. 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 have 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 have the same password:

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

   Here is an example where full backup and incremental backup have 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, indicating that the table does not exist.

     • If special characters are included in database_name or table_name, the entire database_name or table_name 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 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 logs are restored to the specified restore end point, which must include the specified point. If you want to restore the data and logs 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 logs are restored to the latest point.

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

     If you do not specify the concurrency parameter, it defaults to the maximum 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 must specify this parameter when restoring an encrypted tenant.

     • backup_key_path: the path where the tenant's encryption key is stored.

     • password: the password used to encrypt the encryption key.

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

   • remap_table_name_list: The renamed table name after recovery. It supports renaming only the table name while keeping the database to which the table belongs unchanged; it also supports keeping the table name unchanged and only renaming it to another database; it further supports renaming the table name and simultaneously changing the database to which it belongs. The source object and the renamed object are connected using a colon (:), and the specific format is illustrated in the following examples:

     • Rename the table named student 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 target tenant by default. If no database with the same name exists, the table restore fails.

     • 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 renamed, the entire database_name or table_name must be enclosed in backticks (`).

   • remap_tablegroup_list: Renames the table group to which the table belongs. If the source table is bound to a table group, when restoring the table in the target tenant, the system will by default restore the table to a table group with the same name in the target tenant. If a table group with the same name does not exist, the table restoration will fail. If there are other table groups in the target tenant, the table can be restored to another table group using this statement, with the source object and the renamed object connected by an English colon (:).

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

   • remap_tablespace_list: Renames the tablespace to which the table belongs. A tablespace in the OceanBase database is a logical unit, currently mainly used for data encryption. If the source table is bound to a tablespace, when restoring the table to the target tenant, the system will by default restore the table to a tablespace with the same name in the target tenant; if a tablespace with the same name does not exist, the table restoration will fail. If there are other tablespaces in the target tenant, the table can also be restored to another tablespace through this statement, with the source object and the renamed object connected by an English colon (:).

     For example, restore all tables in the source table space ts1 to the target table space newts1: 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 of the target tenant. Rename the restored table tbl1 to newtbl, and redirect the table group and table space to which the table belongs. 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 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 table restore and Query the result of table restore.

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