Flashback queries

OceanBase Database supports record-specific flashback queries, which allow you to obtain data of a specific historical version. You can use the AS OF SCN and AS OF TIMESTAMP clauses to perform flashback queries in Oracle-compatible mode and the AS OF SNAPSHOT clause to perform flashback queries in MySQL-compatible mode.

OceanBase Database allows you to set the undo_retention parameter for a tenant to perform a flashback query. After you set the undo_retention parameter, you can use the flashback query feature to obtain data of multiple versions in the time range from T - undo_retention to T. The default value of the undo_retention parameter is 1800, in seconds. For more information about this parameter, see undo_retention.

Considerations

When you set the undo_retention parameter to perform a flashback query, note that:

• If you set the undo_retention parameter to T0, this feature does not take effect on data generated before T0 and takes effect only on data generated after T0.

• If the queried table is moved to the recycle bin, you must restore it from the recycle bin before you can query data in the table.

  For more information, see Restore objects from the recycle bin.

• Assuming the current time is T, after setting undo_retention, if there were any DDL operations within the time period T - undo_retention, then:

  • For adding column operations, when performing a flashback query to view data before the operation, all newly added columns will have Default values.

  • For operations like creating tables, dropping tables, or dropping columns, when performing a flashback query to view data before the operation, the system will report an error.

• Flashback queries require additional storage space to retain historical data. If you increase the value of the undo_retention parameter, the storage space required is increased. We recommend that you observe the change of the storage space after you increase the value of the undo_retention parameter.

Prerequisites

Before you perform a flashback query using the tenant-level parameter undo_retention, you must first modify the value of undo_retention. The default value of the undo_retention parameter is 1800, in seconds.

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

   Note

   The administrator user of a MySQL-compatible tenant is root, and that of an Oracle-compatible tenant is SYS.

   Below is a connection example. Use your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql#demo -p***** -A
   

   For more information about how to connect to a database, see Overview (MySQL-compatible mode) or Overview (Oracle-compatible mode).

2. Execute the following statement to modify the value of the tenant-level parameter undo_retention.

   Example:

   obclient [(none)]> ALTER SYSTEM SET undo_retention=900;
   

   For more information about the undo_retention parameter, see undo_retention.

Run a flashback query in MySQL-compatible mode

1. Log in to the database as a MySQL-compatible tenant user.

   Below is a connection example. Use your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uinfo@mysql#demo -p***** -A
   

   For more detailed guidance on connecting to the database, see Overview of connection methods (MySQL-compatible mode).

2. Perform a flashback query on a table.

   Assume that the value of the undo_retention parameter is 900 and you have a table named table1. Specify a historical point in time by using AS OF SNAPSHOT and query the status and data of the table at this point in time.

   obclient [(none)]> SELECT * FROM table1 AS OF SNAPSHOT 1597306800000000000;
   

   Here, 1597306800000000000 is a timestamp, in nanoseconds. The timestamp is the time since 08:00:00 (UTC+8) on January 1, 1970. You can convert the point in time to be queried into a timestamp based on your business needs.

   For example, if you want to perform a flashback query on table data at 16:20:00 on August 13, 2020, you can convert the time as follows:

   obclient [(none)]> SELECT time_to_usec('2020-08-13 16:20:00') * 1000;
   +--------------------------------------------+
   | time_to_usec('2022-01-01 00:00:00') * 1000 |
   +--------------------------------------------+
   |                        1597306800000000000 |
   +--------------------------------------------+
   1 row in set
   

Run a flashback query in Oracle-compatible mode

1. Log in to the database as an Oracle-compatible tenant user.

   Below is a connection example. Use your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uinfo@oracle#demo -p***** -A
   

   For more detailed guidance on connecting to the database, see Overview of connection methods (Oracle-compatible mode).

2. Perform a flashback query on a table.

   Assume that the value of the undo_retention parameter is 900 and you have three tables: table1, table2, and expr1. The following examples show the sample code for flashback queries in Oracle-compatible mode.

   • Specify a historical point in time by using TIMESTAMP and query the status and data of a single table at this point in time:

      obclient [SYS]> SELECT * FROM table1 AS OF TIMESTAMP TO_TIMESTAMP('2020-08-13 16:20:00','yyyy-mm-dd hh24:mi:ss');
     

   • Specify a historical point in time by using TIMESTAMP and query the status and data of the multiple tables at this point in time:

      obclient [SYS]> SELECT * FROM table1,table2 AS OF TIMESTAMP TO_TIMESTAMP('2020-08-13 16:20:00','yyyy-mm-dd hh24:mi:ss');
     

   • Specify a historical point in time by using SCN and query the status and data of a single table at this point in time:

      obclient [SYS]> SELECT * FROM table1 AS OF SCN 1597306800000000000;
     

     Here, 1597306800000000000 is a timestamp, in nanoseconds. The timestamp is the time since 08:00:00 (UTC+8) on January 1, 1970. You can convert the point in time to be queried into a timestamp based on your business needs.

     For example, if you want to perform a flashback query on table data at 16:20:00 on August 13, 2020, you can convert the time as follows:

      obclient [(none)]> SELECT (to_date('2020-08-13 16:20:00','yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 86400 * 1000 * 1000 * 1000 AS unix_nsec_timestamp FROM DUAL;
      +---------------------+
      | UNIX_NSEC_TIMESTAMP |
      +---------------------+
      | 1597306800000000000 |
      +---------------------+
      1 row in set