OceanBase Oracle模式下基于UNDO的闪回查询功能

OceanBase 数据库提供了记录级别的闪回查询（Flashback Query）功能，该功能允许用户获取某个历史版本的数据。其中，Oracle 模式支持 AS OF SCN 和 AS OF TIMESTAMP 两种语法来查询；MySQL 模式支持通过 AS OF SNAPSHOT 语法来查询。

当用户设置 undo_retention 变量后，即可使用闪回查询功能查询当前时间 T 到 T - undo_retention 时间范围内的任意多版本数据。

本节主要介绍如何通过设置 undo_retention 变量来进行闪回查询。

注意事项

使用 undo_retention 变量进行闪回查询时，需要注意以下事项：

• 假设 undo_retention 变量设置的时间为 T0，变量设置后对 T0 之前的数据不起作用，对 T0 之后的数据才起作用。
• 如果被查询的表已经被删除进了回收站，则需要先将该表从回收站中恢复。
• 假设当前时间为 T，设置 undo_retention 变量后，如果在 T - undo_retention 时间段内有 DDL 操作，系统会用最新的数据字典解释表的数据。对于 DDL 操作发生前产生的数据，被删除的列的数据将变成删除状态，新添加列的数据则设为 Default 值。

undo_retention

• 在通过 undo_retention 变量进行闪回查询前，需要先修改 undo_retention 变量的值。
• undo_retention 表示系统应保留的多版本数据范围，单位为秒，默认值：1800，在转储时控制多版本数据的回收。
• OceanBase 数据库转储的数据会保留多个版本的历史数据行，您可以通过 undo_retention 来控制转储中保留的多版本数据范围。其中：
  • 当 undo_retention 的值为 0 时，表示未开启多版本转储，即转储文件仅保留当前最新版本的行数据。
  • 当 undo_retention 的值大于 0 时，表示开启多版本转储，并且转储文件保留这段时间（单位为秒）以内的多版本行数据。
• 租户开启多版本转储后，大版本合并会保留对应的增量转储文件，但 Major SSTable 中不会存放多版本数据。建议该参数不要设置过大，防止因参数过大而导致保留的 SSTable 数量超限。

obclient> show variables like 'undo_retention';
+----------------+-------+
| VARIABLE_NAME  | VALUE |
+----------------+-------+
| undo_retention | 1800  |
+----------------+-------+
1 row in set (0.002 sec)

obclient> set global undo_retention=3600;

闪回查询示例

obclient> create table dba_objects_bak as select * from dba_objects;
obclient> create index ind_dba_objects_bak_id on dba_objects_bak(object_id);
obclient> select count(*) from dba_objects_bak where OBJECT_TYPE='TABLE';
+----------+
| COUNT(*) |
+----------+
|    16132 |
+----------+
1 row in set (0.206 sec)

查询当前数据库的SCN和时间，使用 root 用户登录 sys 租户的 oceanbase 进行查询

mysql> select current_scn, usec_to_time(current_scn) as sync_ts from v$ob_cluster;
+------------------+----------------------------+
| current_scn      | sync_ts                    |
+------------------+----------------------------+
| 1692760227932095 | 2023-08-23 11:10:27.932095 |
+------------------+----------------------------+
1 row in set (0.00 sec)

测试删除数据

obclient> delete from dba_objects_bak where OBJECT_TYPE='TABLE';
Query OK, 16132 rows affected (0.606 sec)

obclient> select count(*) from dba_objects_bak where OBJECT_TYPE='TABLE';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.217 sec)

• 通过 TIMESTAMP 指定的历史时间并闪回查询一张单表在该历史时间中的状态的数据。

obclient> obclient [TBCS]> select count(*) from dba_objects_bak as of timestamp to_timestamp('2023-08-23 11:10:27','yyyy-mm-dd hh24:mi:ss') where OBJECT_TYPE='TABLE';
+----------+
| COUNT(*) |
+----------+
|    16132 |
+----------+
1 row in set (0.210 sec)

• 通过 TIMESTAMP 指定的历史时间并闪回查询多表在该历史时间中的状态的数据。

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

• 通过 SCN 指定历史时间并闪回查询单表在该历史时间点的状态的数据。

obclient> select count(*) from dba_objects_bak as of scn 1692760227932095 where OBJECT_TYPE='TABLE';
+----------+
| COUNT(*) |
+----------+
|    16132 |
+----------+
1 row in set (0.213 sec)