DROP RESTORE POINT

Purpose

This statement is used to delete a restore point.

Syntax

DROP RESTORE POINT restore_point;

Parameters

Examples

1. View existing restore points of the current tenant.

   obclient> SELECT * FROM V$RESTORE_POINT;
   +-----------+------------------+----------------------------+------+
   | TENANT_ID | SNAPSHOT         | TIME                       | NAME |
   +-----------+------------------+----------------------------+------+
   |      1001 | 1630407064663511 | 2021-08-31 18:51:04.665692 | rp1  |
   +-----------+------------------+----------------------------+------+
   1 row in set
   

2. Delete the restore point rp1.

   obclient> DROP RESTORE POINT rp1;
   Query OK, 0 rows affected
   

Purpose

This statement is used to create a restore point. You can use this feature to retain a snapshot of the current data and query the data from this snapshot later. Currently, you cannot roll back data to this restore point.

After a restore point is created, you can query the SNAPSHOT value of the restore point from the V$RESTORE_POINT view. Then, you can query the value from the data snapshot by using the SELECT * FROM table_name AS OF SNAPSHOT snapshot; statement.

Notice

Restore points cannot be created in the sys tenant.

Syntax

CREATE RESTORE POINT restore_point;

Parameters

Considerations

The following limitations apply to the restore point feature:

• Physical backup is not supported.

• Primary-standby database deployment is not supported.

• Each tenant can create up to 10 restore points.

• If you execute a DDL statement on a table that exists before a restore point is created, an error is returned.

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

  The following SQL statement is used to enable GTS.

  obclient> SET GLOBAL ob_timestamp_service='GTS';
  

Examples

1. Create a table named test1 and insert a row of data.

   obclient> CREATE TABLE test1 (c1 INT,c2 INT);
   Query OK, 0 rows affected
   
   obclient> INSERT INTO test1(c1,c2) values (1,1);
   Query OK, 1 row affected
   

2. Create a restore point named rp1.

   obclient> CREATE RESTORE POINT rp1;
   Query OK, 0 rows affected
   

3. Insert some data into the test1 table and commit the transaction.

   obclient> INSERT INTO test1(c1,c2) values (2, 2),(3,3);
   Query OK, 2 rows affected
   Records: 2  Duplicates: 0  Warnings: 0
   
   obclient> commit;
   Query OK, 0 rows affected
   

4. Query the current version of the test1 table.

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

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

   obclient> SELECT * FROM V$RESTORE_POINT;
   +-----------+------------------+----------------------------+------+
   | 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