Restore points

This topic describes the restore point feature of OceanBase Database and the use constraints 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 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 through flashback queries.

Create a restore point

Run the CREATE RESTORE POINT command to create a tenant-specific restore point, for example:

obclient> CREATE RESTORE POINT restore_point;

Query a restore point

After you create a restore point, you can query available restore points in the V$RESTORE_POINT view. The following is a sample command:

obclient> SELECT * FROM V$RESTORE_POINT;

To further query data, you can run the following command based on the version, for example, 10000, obtained by using the foregoing command:

• MySQL mode

  obclient> SELECT * FROM table_name AS OF SNAPSHOT 10000;
  

• Oracle mode

  obclient> SELECT * FROM table_name AS OF SCN 10000;
  

Delete a restore point

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

obclient> DROP RESTORE POINT restore_point;

Example

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

1. Create a test table.

   obclient> CREATE TABLE test ( id number primary key, name varchar2(20));
   

2. Create a restore point.

   obclient> CREATE RESTORE POINT restore_point;
   

3. Insert data into the table and commit it.

   obclient> INSERT INTO test values (1, 'test');
   Query OK, 1 row affected (0.00 sec)
   
   obclient> INSERT INTO test values (2, 'test1');
   Query OK, 1 row affected (0.00 sec)
   
   obclient> commit;
   Query OK, 0 rows affected (0.00 sec)
   

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

   obclient> SELECT * FROM test;
   +----+-------+
   | ID | NAME  |
   +----+-------+
   | 1 | test |
   |  2 | test1 |
   2 rows in set (0.00 sec)
   

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

   obclient>SELECT * FROM V$RESTORE_POINT;
   +-----------+------------------+------------------------------+---------------+
   | TENANT_ID | SCN | TIME | NAME |
   +-----------+------------------+------------------------------+---------------+
   |      1007 | 1611578659061356 | 25-JAN-21 08.44.19.062296 PM | RESTORE_POINT |
   +-----------+------------------+------------------------------+---------------+
   1 row in set (0.01 sec)
   
   obclient> SELECT * FROM test AS OF SCN 1611578659061356;
   Empty set (0.00 sec)
   

Use constraints

Use constraints on restore points are as follows:

• A restore point does not support physical backup.

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

• After a restore point is created, the system will generate a -4179 error if you execute a DDL statement on a table that has existed before the restore point was created.

• A restore point relies on Global Timestamp Service (GTS) to maintain the global consistent snapshot. Therefore, GTS must be enabled when you use a restore point.

  You can run the following SQL command to enable GTS:

  obclient> set GLOBAL ob_timestamp_service='GTS ';