CREATE RESTORE POINT

Purpose

This statement is used to create a restore point. With this feature, you can save a data snapshot at the current moment and query data from this snapshot later. However, you cannot roll back data to this restore point.

After creating a restore point, you can query its SNAPSHOT value from the V$RESTORE_POINT view and then use the SELECT * FROM table_name AS OF SNAPSHOT snapshot; statement to query data from the snapshot.

Limitations and considerations

The limitations of the restore point feature are as follows:

• Physical backup is not supported.

• Master-slave databases are not supported.

• Each tenant is limited to 10 restore points.

• If you execute a DDL statement on a table that existed before the restore point was created, an error will be returned.

• The restore point feature relies on GTS to maintain a globally consistent snapshot. Therefore, GTS must be enabled when using restore points.

  The SQL command to enable GTS is as follows.

  obclient> SET GLOBAL ob_timestamp_service='GTS';
  

Syntax

CREATE RESTORE POINT restore_point;

Parameters

Examples

1. Create a table named test1.

   obclient> CREATE TABLE test1 (c1 INT,c2 INT);
   

2. Insert a row of data.

   obclient> INSERT INTO test1(c1,c2) values (1,1);
   

3. Create a restore point named rp1.

   obclient> CREATE RESTORE POINT rp1;
   

4. Insert more data into the test1 table and commit the changes.

   obclient> INSERT INTO test1(c1,c2) values (2, 2),(3,3);
   obclient> commit;
   

5. Query the current version of the test1 table.

   obclient> SELECT * FROM test1;
   +------+------+
   | c1   | c2   |
   +------+------+
   |    1 |    1 |
   |    2 |    2 |
   |    3 |    3 |
   +------+------+
   3 rows in set
   

6. View the information of the created restore point rp1 and query historical data based on the version number.

   obclient> SELECT * FROM V$RESTORE_POINT;
   

   The result is as follows:

   +-----------+------------------+----------------------------+------+
   | TENANT_ID | SNAPSHOT         | TIME                       | NAME |
   +-----------+------------------+----------------------------+------+
   |      1001 | 1630407064663511 | 2021-08-31 18:51:04.665692 | rp1  |
   +-----------+------------------+----------------------------+------+
   1 row in set
   
   obclient> SELECT * FROM test1 AS OF SNAPSHOT 1630407064663511;
   +------+------+
   | c1   | c2   |
   +------+------+
   |    1 |    1 |
   +------+------+
   1 row in set