RECOVER TABLE

Purpose

You can use this statement to restore a specified table from the backup data to an existing tenant, which can be the original tenant to which the table belongs or a different tenant that is located in the same or a different cluster as the original tenant.

Limitations and considerations

• You can restore only a user table. You cannot independently restore a temporary table, view, or index.

• You cannot restore foreign keys, triggers, or statistics of a table.

• The source tenant of the table to be restored and the destination tenant must be of the same type. For example, both are Oracle tenants or both are MySQL tenants.

• You can restore a table only to an OceanBase database of the same or a later version.

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

• An auxiliary tenant is required when you use the ALTER SYSTEM RECOVER TABLE statement to restore a table. Therefore, before you execute the statement, 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.

Required privileges

You can execute the ALTER SYSTEM RECOVER TABLE statement only as the root user of the sys tenant (namely root@sys).

Syntax

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

table_name_list:
    database_name.table_name [, database_name.table_name ...]

remap_table_name_list:
    /* Retain the database name and change only the table name. */
    database_name.old_table_name:new_table_name
    /* Retain the table name and change only the database name. */
    |old_database_name.table_name:new_database_name.table_name
    /* Change both the database name and table name. */
    | old_database_name.old_table_name:new_database_name.new_table_name
    /* Restore all tables in the source database to the new database. */
    | old_database_name.`*`:new_database_name.`*`

remap_tablegroup_list:
    old_tablegroup_name:new_tablegroup_name

remap_tablespace_list:
    old_tablespace_name:new_tablespace_name

Parameters

Examples

For the test database in the MySQL tenant obt, the data backup path is set to file:///data/backup/data, the log archive path is set to file:///data/backup/archive, log archiving is enabled, and a full data backup is completed. Assume that the student table is dropped by mistake. The following example shows how to restore the test.student table in a tenant to the timestamp 2023-08-21 19:47:20 and rename the table as student_recover.

1. Create resources required by the auxiliary tenant in the sys tenant. This example creates the resource unit recover_4c5g and uses it to create a resource pool.

   1. Create a resource unit.

      obclient [oceanbase]> CREATE RESOURCE UNIT recover_4c5g MAX_CPU 4, MEMORY_SIZE = '5G', MAX_IOPS 1024, MIN_IOPS=1024;
      

   2. Create a resource pool.

      obclient [oceanbase]> CREATE RESOURCE POOL recover_tmp_pool UNIT = 'recover_4c5g', UNIT_NUM = 1, ZONE_LIST = ('z1','z2','z3');
      

2. Restore the student table to the test database under the obt tenant and rename the table as student_recover. The backup path is file:///data/backup/data, the log archive path is file:///data/backup/archive, the table is restored to the timestamp 2023-08-21 19:47:20, the resource pool used by the auxiliary tenant is recover_tmp_pool, and the primary zone is z1.

   obclient [oceanbase]> ALTER SYSTEM RECOVER TABLE test.student TO TENANT obt FROM 'file:///data/backup/data,file:///data/backup/archive' UNTIL TIME='2023-08-21 19:47:20' WITH 'pool_list=recover_tmp_pool&primary_zone=z1' REMAP TABLE test.student:student_recover;
   

3. After the table restore statement is executed, you can check the restore progress in the CDB_OB_RECOVER_TABLE_JOBS view. The STATUS field indicates the restore stage. In this example, the stage is RESTORE_AUX_TENANT, which means that the auxiliary tenant is being restored.

   obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_RECOVER_TABLE_JOBS\G
   

   The query result is as follows:

   *************************** 1. row ***************************
               TENANT_ID: 1
                  JOB_ID: 2
     INITIATOR_TENANT_ID: 1
        INITIATOR_JOB_ID: 2
         START_TIMESTAMP: 2023-08-21 19:54:25
           END_TIMESTAMP: 1970-01-01 08:00:00
                  STATUS: RESTORE_AUX_TENANT
         AUX_TENANT_NAME: AUX_RECOVER$1692618864733548
      TARGET_TENANT_NAME: OBT
              IMPORT_ALL: 0
                 DB_LIST: NULL
              TABLE_LIST: `TEST`.`STUDENT`
             RESTORE_SCN: 1692618440000000000
     RESTORE_SCN_DISPLAY: 21-AUG-23 07.47.20.000000000 PM
          RESTORE_OPTION: pool_list=RECOVER_TMP_POOL&primary_zone=z1
             BACKUP_DEST: file:///data/backup/data,file:///data/backup/archive
         BACKUP_SET_LIST: file:///data/backup/data/backup_set_1_full
       BACKUP_PIECE_LIST: file:///data/backup/archive/piece_d1002r1p1
           BACKUP_PASSWD: NULL
       EXTERNAL_KMS_INFO: NULL
           REMAP_DB_LIST: NULL
        REMAP_TABLE_LIST: `TEST`.`STUDENT`:`TEST`.`STUDENT_RECOVER`
   REMAP_TABLEGROUP_LIST: NULL
   REMAP_TABLESPACE_LIST: NULL
                  RESULT: SUCCEESS
                 COMMENT: NULL
             DESCRIPTION: NULL
   

References

Perform table restore