使用dbms_flashback工具包实现闪回查询功能
Flashback Query是借助Oracle Undo过期数据而实现的一种方便的逻辑恢复功能。在Undo Tablespace支持的情况下,我们可以查询到过去
Flashback Query是借助Oracle Undo过期数据而实现的一种方便的逻辑恢复功能。在Undo Tablespace支持的情况下,我们可以查询到过去一个特定的时间点(或者SCN点)某个数据表的时间版本。
标准的Flashback Query语句是需要借助as of timestamp| as of scn语句在数据表后面,用于指定查看的数据表过去时间点是什么。这种方式从数据库管理员的角度的确是很方便,特别是那些直接访问后台挽救数据的开发管理人员。
但是在两种情况下,as of指定时间的方式存在一些问题。首先是应用程序中的语句,开发嵌入到应用程序的代码是不能轻易修改的,也就是说我们在procedure或者package的外面,是不能加入那些as of语句指定时间。另一方面,一个时间点数据可能是涉及多个数据表版本操作,逐个表指定是存在很多的问题。于是,使用dbms_flashback包的过去时间点上下文指定功能,就可以解决上面说的问题。
1、环境说明
笔者使用Oracle 11gR2进行测试实验,具体版本为11.2.0.4。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
当前没有配置补充日志supplemental log data,同时Undo配置关键参数如下:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_MIN
-------------------------
NO
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 9000
undo_tablespace string UNDOTBS1
dbms_flashback包的描述信息如下:
SQL> desc dbms_flashback
Element Type
------------------------------ ---------
ENABLE_AT_TIME PROCEDURE
ENABLE_AT_SYSTEM_CHANGE_NUMBER PROCEDURE
DISABLE PROCEDURE
GET_SYSTEM_CHANGE_NUMBER FUNCTION
NOCASCADE CONSTANT
NOCASCADE_FORCE CONSTANT
NONCONFLICT_ONLY CONSTANT
CASCADE CONSTANT
TRANSACTION_BACKOUT PROCEDURE
在笔者之前的文章中,经常使用dbms_flashback.get_system_change_number来获取系统的SCN编号,并且演示过transaction_backout方法逆转整体事务的策略。本篇集中在enable_at_time、enable_at_system_change_number和disable方法上。
2、dbms_flashback时间机器
enable_at_time和enable_at_system_change_number的作用相同,都是将当前会话的上下文逆转到过去的一个时间点,区别仅在于制定的是时间点还是SCN编号。
正确执行两个方法之后,所有的查询都是基于指定的时间点进行的,类似于电影中的“时间机器”。背后使用的Flashback Query过程根本不需要我们手工指定时间点在数据表后面。
注意:同flashback query使用相同,dbms_flashback方法不允许在SYS用户下使用,如果使用就会报错。
SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107410);
begin dbms_flashback.enable_at_system_change_number(query_scn => 2107410); end;
ORA-08185: ???§ SYS ???§??????
ORA-06512: ?? "SYS.DBMS_FLASHBACK", line 12
ORA-06512: ?? line 1
我们的演示实验会在scott用户下进行。首先需要给scott用户赋予dbms_flashback包的执行权限。
SQL> grant execute on dbms_flashback to scott;
Grant succeeded
切换到scott用户,创建实验数据表。
SQL> create table test as select empno, sal from emp where rownum
Table created
SQL> select * from test;
EMPNO SAL
----- ---------
7369 800.00
7499 1600.00
7521 1250.00
此时系统时间和SCN编号如下:
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
2107631
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2015-06-29 13:49:13
之后进行所谓的“误操作”。
SQL> update test set sal=1000 where empno=7521;
1 row updated
SQL> commit;
Commit complete
SQL> select * from test where empno=7521;
EMPNO SAL
----- ---------
7521 1000.00
传统的Flashback Query策略。
SQL> select * from test as of scn 2107631 where empno=7521;
EMPNO SAL
----- ---------
7521 1250.00
下面使用dbms_flashback方法,,指定出一个SCN编号。
SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107631); --开启了查询;
PL/SQL procedure successfully completed
SQL> select * from test where empno=7521;
EMPNO SAL
----- ---------
7521 1250.00
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed
--disable之后,数据恢复
SQL> select * from test where empno=7521;
EMPNO SAL
----- ---------
7521 1000.00
注意:使用enable方法之后,我们以直接的方式查询到过去的时间点方法。如果操作结束,需要使用disable方法关闭设置的上下文时间。
如果指定timestamp方法,效果是相同的。
SQL> exec dbms_flashback.enable_at_time(query_time => to_timestamp('2015-06-29 13:49:13','yyyy-mm-dd hh24:mi:ss'));
PL/SQL procedure successfully completed
SQL> select * from test where empno=7521;
EMPNO SAL
----- ---------
7521 1250.00
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed
最后,我们考虑一下,如果在过去的时间上下文中进行修改,修改相关数据和无关数据,结果是如何呢?
SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107631);
PL/SQL procedure successfully completed
SQL> select * from test where empno=7521;
EMPNO SAL
----- ---------
7521 1250.00
SQL> select * from test;
EMPNO SAL
----- ---------
7369 800.00
7499 1600.00
7521 1250.00
SQL> delete test where empno=7499;
delete test where empno=7499
ORA-08182: 在闪回模式下操作不受支持
SQL> update test set sal=1000 where empno=7369;
update test set sal=1000 where empno=7369
ORA-08182: 在闪回模式下操作不受支持
SQL> insert into test values (1000,1000);
insert into test values (1000,1000)
ORA-08182: 在闪回模式下操作不受支持
SQL> create table m as select * from test;
create table m as select * from test
ORA-08182: 在闪回模式下操作不受支持
和时间机器一样,不能改变历史。
3、结论
Dbms_flashback工具包提供了关于闪回技术的很多功能和有意义的场景。借助dbms_flashback的flashback query上下文,我们可以方便的实现上下文历史数据查询检索。
本文永久更新链接地址:

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.

The article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.

Article discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.

Article discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)
