Restore point

This topic describes the restore point feature of OceanBase Database and the limits of this feature.

In many application systems, users need to query data at a specific point in time or data of a specific version in a database for data analysis or aggregation.

OceanBase Database V2.2.7x provides the restore point feature, which allows you to create tenant-specific restore points to save historical data. Restore points are similar to snapshot points for tenants. You can access historical data of specific versions by using flashback queries.

Limitations

Note the following limits for using the restore points:

• A restore point does not support physical backup.

• A restore point is not applicable to the primary/standby cluster configuration.

• Restore points cannot be created in the sys tenant.

• A maximum of 10 restore points can be created in a tenant.

• If you execute a DDL statement for a table that is backed up by creating a restore point, an error is returned. In MySQL mode, the error is: ERROR 4179 (HY000): restore point exist, create index/alter not allowed. In Oracle mode, the error is: ORA-00600: internal error code, arguments: -4179, restore point exist, create index/alter not allowed.

Prerequisites

Restore points are used to maintain the global consistent snapshots based on the Global Timestamp Service (GTS). Therefore, GTS must be enabled when you use restore points.

You can execute the following SQL statement to enable GTS:

obclient> set GLOBAL ob_timestamp_service='GTS ';

Create a restore point

Execute the CREATE RESTORE POINT statement to create a tenant-specific restore point. Example:

obclient> CREATE RESTORE POINT restore_point;

For more information about CREATE RESTORE POINT, see CREATE RESTORE POINT (MySQL mode) and CREATE RESTORE POINT (Oracle mode).

Query a restore point

Query available restore points in the V$RESTORE_POINT view and perform data analysis based on the queried restore points.

To query the restore point, perform the following steps:

1. Log on to the database as a tenant administrator.

2. Execute the following SQL statements to query available restore points in the tenant and corresponding version number.

   • MySQL mode

     obclient> SELECT * FROM oceanbase.V$RESTORE_POINT;
     

   • Oracle mode

     obclient> SELECT * FROM V$RESTORE_POINT;
     

   Columns in the query result:

   • TENANT_ID: the ID of the tenant in which restore points are created.

   • SNAPSHOT: the version number corresponding to a restore point.

   • TIME: the time when a restore point was created.

   • NAME: the name of a restore point.

   For more information about fields in the V$RESTORE_POINT view, see v$restore_point (MySQL mode) or V$RESTORE_POINT (Oracle mode).

3. Execute the following statements to perform data analysis based on the data version returned by the preceding statement.

   For example, if the data version returned is 1638501121443992, you can view data in the specified table of the version.

   • MySQL mode

     obclient> SELECT * FROM table_name AS OF SNAPSHOT 1638501121443992;
     

   • Oracle mode

     obclient> SELECT * FROM table_name AS OF SCN 1638501121443992;
     

Drop a restore point

Data corresponding to a restore point occupies storage resources. After the business analysis is completed, you need to manually drop the restore point to release the resources.

The syntax is as follows:

obclient> DROP RESTORE POINT restore_point;

For more information about DROP RESTORE POINT, see DROP RESTORE POINT (MySQL mode) or DROP RESTORE POINT (Oracle mode).

Examples

This example shows how to access data of a specific version by using a restore point in Oracle mode.

1. Create a table named test and insert data into the table.

   obclient> CREATE TABLE test ( ID NUMBER PRIMARY KEY, NAME varchar2(20));
   
   obclient> INSERT INTO test VALUES (1,'LI');
   
   obclient> commit;
   

2. Create a restore point.

   obclient> CREATE RESTORE POINT restore_point;
   

3. Insert data into the table and commit the insert.

   obclient> INSERT INTO test VALUES (2, 'WANG');
   Query OK, 1 row affected
   
   obclient> INSERT INTO test VALUES (3, 'SU');
   Query OK, 1 row affected
   
   obclient> commit;
   Query OK, 0 rows affected
   

4. Query the data of the current version in the test table.

   obclient> SELECT * FROM test;
   +----+------+
   | ID | NAME |
   +----+------+
   |  1 | LI   |
   |  2 | WANG |
   |  3 | SU   |
   +----+------+
   3 rows in set
   

5. Query information about the created restore point and view historical data based on the version.

   obclient>SELECT * FROM V$RESTORE_POINT;
   +-----------+------------------+------------------------------+---------------+
   | TENANT_ID | SNAPSHOT         | TIME                         | NAME          |
   +-----------+------------------+------------------------------+---------------+
   |      1002 | 1637549041740744 | 22-NOV-21 10.44.01.742454 AM | RESTORE_POINT |
   +-----------+------------------+------------------------------+---------------+
   1 row in set
   
   obclient> SELECT * FROM test AS OF SCN 1637549041740744;
   +----+------+
   | ID | NAME |
   +----+------+
   |  1 | LI   |
   +----+------+
   1 row in set