OceanBase 闪回查询（Flashback Query）操作示例 -- cnDBA.cn

1 闪回查询（Flashback Query）概述

OceanBase 数据库提供了记录级别的闪回查询（Flashback Query）功能，该功能允许用户获取某个历史版本的数据。

Oracle 模式支持 AS OF SCN和 AS OF TIMESTAMP 两种语法来查询；
MySQL 模式支持通过 AS OF SNAPSHOT 语法来查询。

OceanBase 数据库当前支持用户通过设置租户级配置项 undo_retention 的方式来进行闪回查询。

undo_retention 参数是租户级别的参数，默认值为 1800，单位为秒。

连接租户，查看参数：http://www.cndba.cn/dave/article/131403

[dave@www.cndba.cn ~]$obclient -h192.168.1.21 -P2881 -uroot@open_gpt -p'xxxxxx' -Doceanbase -A
Welcome to the OceanBase.  Commands end with ; or /g.
Your OceanBase connection id is 3221592057
Server version: OceanBase_CE 4.1.0.0 (r101010022023051821-f7379b26f8cd11f026e06846043550f9e0d42ead) (Built May 18 2023 21:14:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

obclient [oceanbase]>
obclient [oceanbase]>  SHOW PARAMETERS LIKE '%undo%'/G
*************************** 1. row ***************************
      zone: zone2
  svr_type: observer
    svr_ip: 192.168.1.22
  svr_port: 2882
      name: undo_retention
 data_type: NULL
     value: 7200
      info: the low threshold value of undo retention. The system retains undo for at least the time specified in this config when active txn protection is banned. Range: [0, 4294967295]
   section: TENANT
     scope: TENANT
    source: DEFAULT
edit_level: DYNAMIC_EFFECTIVE
……

我们这里修改为 4 小时：3600*4 = 14400

obclient [oceanbase]> ALTER SYSTEM SET undo_retention = 14400;
Query OK, 0 rows affected (0.017 sec)

obclient [oceanbase]>  SHOW PARAMETERS LIKE '%undo%'/G
*************************** 1. row ***************************
      zone: zone1
  svr_type: observer
    svr_ip: 192.168.1.21
  svr_port: 2882
      name: undo_retention
 data_type: NULL
     value: 14400
      info: the low threshold value of undo retention. The system retains undo for at least the time specified in this config when active txn protection is banned. Range: [0, 4294967295]
   section: TENANT
     scope: TENANT
    source: DEFAULT
edit_level: DYNAMIC_EFFECTIVE

注意事项：http://www.cndba.cn/dave/article/131403http://www.cndba.cn/dave/article/131403

如果被查询的表已经被删除进了回收站，则需要先将该表从回收站中恢复。
假设当前时间为 T，设置 undo_retention 后，如果在 T - undo_retention 时间段内有 DDL 操作，系统会用最新的数据字典解释表的数据。对于 DDL 操作发生前产生的数据，被删除的列的数据将变成删除状态，新添加列的数据则设为 Default 值。
闪回查询需要额外的存储空间来保留历史数据，如果调大 undo_retention 会导致存储空间的放大。建议调大 undo_retention 后，留意存储空间的变化。

2 操作示例

2.1 MySQL 模式租户

MySQL 模式支持通过 AS OF SNAPSHOT 语法来查询。

2.1.1 操作数据

obclient [backup]> select * from bmsql_config;
+-------------+-----------+
| cfg_name    | cfg_value |
+-------------+-----------+
| nURandCC_ID | 727       |
| nURandCI_ID | 4738      |
| nURandCLast | 93        |
| warehouses  | 200       |
+-------------+-----------+
4 rows in set (0.003 sec)

obclient [backup]> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-07-04 11:18:08 |
+---------------------+
1 row in set (0.001 sec)

obclient [backup]> delete from bmsql_config;
Query OK, 4 rows affected (0.004 sec)

obclient [backup]>  select * from bmsql_config;
Empty set (0.001 sec)

obclient [backup]>

2.1.2 转换时间戳

OB 闪回的语法是：

obclient [(none)]> SELECT * FROM table1 AS OF SNAPSHOT 1597306800000000000;
http://www.cndba.cn/dave/article/131403

http://www.cndba.cn/dave/article/131403

http://www.cndba.cn/dave/article/131403

其中，1597306800000000000 为时间戳，单位为纳秒。时间戳的起点时间为北京时间 1970 年 08 点 00 分 00 秒。http://www.cndba.cn/dave/article/131403

所以在操作之前需要将待查询的时间转换为时间戳格式。http://www.cndba.cn/dave/article/131403

obclient [backup]> SELECT time_to_usec('2023-07-04 11:18:08') * 1000;
+--------------------------------------------+
| time_to_usec('2023-07-04 11:18:08') * 1000 |
+--------------------------------------------+
|                        1688440688000000000 |
+--------------------------------------------+
1 row in set (0.001 sec)

2.1.3 执行闪回查询

obclient [backup]> select * from bmsql_config AS OF SNAPSHOT  1688440688000000000;
+-------------+-----------+
| cfg_name    | cfg_value |
+-------------+-----------+
| nURandCC_ID | 727       |
| nURandCI_ID | 4738      |
| nURandCLast | 93        |
| warehouses  | 200       |
+-------------+-----------+
4 rows in set (0.003 sec)

obclient [backup]> insert into bmsql_config select * from bmsql_config AS OF SNAPSHOT  1688440688000000000;
Query OK, 4 rows affected (0.004 sec)
Records: 4  Duplicates: 0  Warnings: 0

obclient [backup]> select * from bmsql_config;
+-------------+-----------+
| cfg_name    | cfg_value |
+-------------+-----------+
| nURandCC_ID | 727       |
| nURandCI_ID | 4738      |
| nURandCLast | 93        |
| warehouses  | 200       |
+-------------+-----------+
4 rows in set (0.001 sec)

obclient [backup]>

2.2 Oracle 模式租户

Oracle 模式支持 AS OF SCN和 AS OF TIMESTAMP 两种语法来查询。

OB 开源版本只有 MySQL 租户，Oracle 模式租户只有企业版本才有，所以我们这里直接看官网的示例。

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

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

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

obclient [SYS]> 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 [SYS]> SELECT * FROM table1 AS OF SCN 1597306800000000000;

其中，1597306800000000000 为时间戳，单位为纳秒。时间戳的起点时间为北京时间 1970 年 01 月 01 日 08 点 00 分 00 秒。您可以根据需要将待查询的时间转换为时间戳格式。

例如，本示例中，需要闪回查询 2020 年 08 月 13 日 16 点 20 分 00 秒的表数据，转换时间的方法如下：

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

签到成功

CNDBA社区