• 【Oracle】闪回技术


    1.闪回技术描述

    2.数据库的准备:

    --undo表空间要设置成AUTO,设置合适的保存时间、undo表空间:
    
    SYS@ENMOEDU> show parameter undo
    
    NAME                        TYPE        VALUE
    ------------------------ -----------   -----
    undo_management           string       AUTO
    undo_retention            integer      900
    undo_tablespace           string      UNDOTBS1

    3.闪回查询数据
    (1)闪回查询
    ①基于timestamp

    SCOTT@LGR> create table t1 as select * from dept;
    
    Table created.
    
    SCOTT@LGR> select * from t1;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    18:25:50 SCOTT@LGR> set time on
    18:26:00 SCOTT@LGR> delete t1 where deptno=10;
    
    1 row deleted.
    
    18:26:21 SCOTT@LGR> commit;
    
    Commit complete.
    
    18:26:58 SCOTT@LGR> select * from t1;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    18:31:26 SCOTT@LGR> select * from t1 as of timestamp to_timestamp('2016-12-15 18:24:00','yyyy-mm-dd hh24:mi:ss');
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    ②基于SCN

    SYS@LGR> grant execute on dbms_flashback to scott;
    
    Grant succeeded.
    
    SYS@LGR> conn scott/tiger
    Connected.
    
    SCOTT@LGR> create table t2 as select * from dept;
    
    Table created.
    
    SCOTT@LGR> select * from t2;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SCOTT@LGR> select dbms_flashback.get_system_change_number as scn from dual;
    
           SCN
    ----------
        539268
    
    
    SCOTT@LGR> delete t2;
    
    4 rows deleted.
    
    
    SCOTT@LGR> commit;
    
    Commit complete.
    
    
    SCOTT@LGR> select * from t2;
    
    no rows selected
    
    
    SCOTT@LGR> select * from t2 as of scn 539268;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    ---也可以通过以下方式获取SCN
    SCOTT@LGR> conn / as sysdba
    Connected.
    SYS@LGR> select current_scn from v$database;
    
    CURRENT_SCN
    -----------
         539373

    ③基于dbms_flashback 包

    语法:
    
     ---会话启用闪回指定时间:
    
    dbms_flashback.enable_at_time(query_time in timestamp);
    
     ---会话启用闪回指定SCN:
    
    dbms_flashback.enable_at_system_change_number(query_scn in number);
    
        ---关闭闪回:
    
    dbms_flashback.disable;
    ---把dbms_flashback的权限授权给scott
    SYS@LGR> grant execute on dbms_flashback to scott;
    
    Grant succeeded.
    
    ---创建测试表
    SCOTT@LGR> create table t3 as select * from dept;
    
    Table created.
    
    SCOTT@LGR> select * from t3;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    ---获取当前的SCN
    SCOTT@LGR> select dbms_flashback.get_system_change_number as scn from dual;
    
           SCN
    ----------
        540384
    ---对t3表进行操作
    SCOTT@LGR> update t3 set dname=null,loc=null;
    
    4 rows updated.
    
    SCOTT@LGR> commit;
    
    Commit complete.
    
    SCOTT@LGR> select * from t3;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10
            20
            30
            40
    ---执行dbms_flashback包
    SCOTT@LGR> exec dbms_flashback.enable_at_system_change_number(540384);
    
    PL/SQL procedure successfully completed.
    ---再次查看测试表
    SCOTT@LGR> select * from t3;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    ---注意的是,在闪回模式中不能进行DML操作
    
    SCOTT@LGR> insert into t3 values (1,'a','a');
    insert into t3 values (1,'a','a')
                *
    ERROR at line 1:
    ORA-08182: operation not supported while in Flashback mode
    
    ---结束操作,需要执行如下过程
    SCOTT@LGR> exec dbms_flashback.disable;
    
    PL/SQL procedure successfully completed.
    
    ---查询测试表,恢复原貌,且可以进行DML操作
    
    SCOTT@LGR> select * from t3;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10
            20
            30
            40
    
    SCOTT@LGR> delete t3 where deptno=20;
    
    1 row deleted.
    
    SCOTT@LGR> select * from t3;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10
            30
            40
    
    注:sys下不允许使用dbms_flashback包
    SYS@LGR> exec dbms_flashback.enable_at_time(sysdate-5/1440);
    BEGIN dbms_flashback.enable_at_time(sysdate-5/1440); END;
    
    *
    ERROR at line 1:
    ORA-08185: Flashback not supported for user SYS
    ORA-06512: at "SYS.DBMS_FLASHBACK", line 3
    ORA-06512: at line 1

    (2)闪回版本查询 

    语法:
    
    select column_name[,column_name,...]
    from table_name
    versions between [SCN | TIMESTAMP] [expr | MINVALUE] and [expr | MAXVALUE];
    ---连续三次对emp表进行修改
    SCOTT@ENMOEDU> select ename,job,sal from emp where empno=7369;
    
    ENAME      JOB              SAL
    ---------- --------- ----------
    SMITH      CLERK            800
    
    SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369;
    
    1 row updated.
    
    SCOTT@ENMOEDU> commit;
    
    Commit complete.
    
    SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369;
    
    1 row updated.
    
    SCOTT@ENMOEDU> commit;
    
    Commit complete.
    
    SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369;
    
    1 row updated.
    
    SCOTT@ENMOEDU> commit;
    
    Commit complete.
    
    ---查看修改后的sal
    SCOTT@ENMOEDU> select ename,job,sal from emp where empno=7369;
    
    ENAME      JOB              SAL
    ---------- --------- ----------
    SMITH      CLERK           1400
    
    ---闪回版本查询,可以清晰的看到每次更新的数据
    SCOTT@ENMOEDU> select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,versions_xid,ename,job,sal from emp versions between timestamp sysdate-10/1440 and sysdate where empno=7369;
    
    STARTTIME           VERSIONS_XID     ENAME      JOB              SAL
    ------------------- ---------------- ---------- --------- ----------
    2016-12-16 09:57:13 02001400B3030000 SMITH      CLERK           1400
    2016-12-16 09:57:09 05002000B3030000 SMITH      CLERK           1200
    2016-12-16 09:56:54 03001900B3030000 SMITH      CLERK           1000
                                         SMITH      CLERK            800

    —附:设计到的伪列
    这里写图片描述

    —闪回版本注意事项
    VERSIONS 子句不能用于:
    —外部表
    —临时表
    —固定表
    —视图
    VERSIONS不能跨DDL命令。
    段收缩操作被过滤掉。

    (3)闪回事务处理查询
    ①11g闪回事务处理处理查询必须要启用最小补充日志

    ---启用最小补充日志
    SYS@ENMOEDU> alter database add supplemental log data; 
    
    Database altered.
    
    ---查询最小补充日志是否开启
    SYS@ENMOEDU> select supplemental_log_data_min from v$database;
    
    SUPPLEME
    --------
    YES

    ②建立测试表,并对其进行DML操作

    SYS@ENMOEDU> create table t (id number(2),name varchar(10)) tablespace users;
    
    Table created.
    
    SYS@ENMOEDU> insert into t values (1,'zhangsan');
    
    1 row created.
    
    SYS@ENMOEDU> update t set name='zs' where id=1;
    
    1 row updated.
    
    SYS@ENMOEDU> commit; 
    
    Commit complete.

    ③执行闪回版本查询,得到事务ID

    SYS@ENMOEDU> select versions_xid,versions_startscn,id,name from t versions between timestamp minvalue and maxvalue;
    
    VERSIONS_XID     VERSIONS_STARTSCN         ID NAME
    ---------------- ----------------- ---------- ----------
    07001600CB020000           1035729          1 zs

    ④通过事务ID得到相关SQL
    事务‘07001600CB020000’做了两个操作:插入数据、更新数据,闪回事务会逆向的返回这个事务的操作,通过这组SQL可修改回事务修改前的样子。

    SYS@ENMOEDU> select undo_sql from flashback_transaction_query where xid='07001600CB020000';
    
    UNDO_SQL
    --------------------------------------------------------------
    update "SYS"."T" set "NAME" = 'zhangsan' where ROWID = 'AAAVslAAEAAAAJMAAA';
    delete from "SYS"."T" where ROWID = 'AAAVslAAEAAAAJMAAA';

    ⑤关闭最小补充日志

    SYS@ENMOEDU> alter database drop supplemental log data;
    
    Database altered.

    4.闪回表
    语法:

    FLASHBACK TABLE[ schema.] table [, [ schema. ] table ]...
      TO {{{ SCN |TIMESTAMP }expr | RESTORE POINT restore_point}
      [{ ENABLE | DIABLE} TRIGGERS ]| BEFORE DROP [ RENAME TO table]};

    注:FLASHBACK TABLE 是DDL命令
    ①创建测试表 fb_table, 在表 fb_table 上创建触发器(trg_fb_table ),fb_table 的 的 x 字段创建一个索引(idx_fb_table ),此时,与 fb_table 相关的对象就有两个,一个是触发器,一个是索引,这三个对象的状态都是有效的。

    SYS@ENMOEDU> conn scott/tiger
    Connected.
    SCOTT@ENMOEDU> create table fb_table(x number(2),d date);
    
    Table created.
    
    SCOTT@ENMOEDU> create or replace trigger trg_fb_table
      2  before insert on fb_table for each row 
      3  begin
      4   if :new.d is null then
      5    :new.d :=to_date('20160606','yyyymmdd');
      6   end if;
      7  end;
      8  /
    
    Trigger created.
    
    SCOTT@ENMOEDU> create unique index idx_fb_table on fb_table(x);
    
    Index created.
    
    SCOTT@ENMOEDU> COL OBJECT_NAME FOR A15
    SCOTT@ENMOEDU> /
    
    OBJECT_NAME     STATUS
    --------------- -------
    IDX_FB_TABLE    VALID
    FB_TABLE        VALID
    TRG_FB_TABLE    VALID

    ②插入测试数据,验证触发器

    SCOTT@ENMOEDU> insert into fb_table(x) values(1);
    
    1 row created.
    
    SCOTT@ENMOEDU> insert into fb_table(x,d) values (2,sysdate);
    
    1 row created.
    
    SCOTT@ENMOEDU> commit;
    
    Commit complete.
    
    
    SCOTT@ENMOEDU> select * from fb_table;
    
             X D
    ---------- ---------
             1 06-JUN-16
             2 16-DEC-16

    ③返回当前时间

    SCOTT@ENMOEDU> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    
    Session altered.
    SCOTT@ENMOEDU> select sysdate from dual;
    
    SYSDATE
    -------------------
    2016-12-16 11:33:29

    ④删除表中的一条记录

    SCOTT@ENMOEDU> delete fb_table where x=2;
    
    1 row deleted.
    
    SCOTT@ENMOEDU> commit;
    
    Commit complete.
    
    SCOTT@ENMOEDU>  select * from fb_table;
    
             X D
    ---------- -------------------
             1 2016-06-06 00:00:00

    ⑤尝试用闪回表的方式恢复数据

    --第一次闪回报错很明显是因为没有启动行移动,启动后,成功闪回表
    
    SCOTT@ENMOEDU> flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss');
    flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss')
                    *
    ERROR at line 1:
    ORA-08189: cannot flashback the table because row movement is not enabled
    
    
    SCOTT@ENMOEDU> select table_name,row_movement from user_tables where table_name='FB_TABLE';
    
    TABLE_NAME                     ROW_MOVE
    ------------------------------ --------
    FB_TABLE                       DISABLED
    
    SCOTT@ENMOEDU> alter table fb_table enable row movement;
    
    Table altered.
    
    SCOTT@ENMOEDU> select table_name,row_movement from user_tables where table_name='FB_TABLE';
    
    TABLE_NAME                     ROW_MOVE
    ------------------------------ --------
    FB_TABLE                       ENABLED
    
    SCOTT@ENMOEDU> flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss');
    
    Flashback complete.
    
    
    SCOTT@ENMOEDU> select * from fb_table;
    
             X D
    ---------- -------------------
             1 2016-06-06 00:00:00
             2 2016-12-16 11:33:19

    ⑥查看与测试表相关对象

    --所有对象的状态均为有效的,测试的时候我们创建触发器和索引意义不大了,不过要说明的是,11g在闪回表上做了升级,10g闪回表后,触发器为失效的状态。
    
    SCOTT@ENMOEDU> select object_name,status from user_objects where object_name like '%FB_TABLE';
    
    OBJECT_NAME     STATUS
    --------------- -------
    TRG_FB_TABLE    VALID
    FB_TABLE        VALID
    IDX_FB_TABLE    VALID

    ⑦也可以基于时间间隔、scn闪回一张或者多张表。

    ---基于时间间隔闪回
    SCOTT@ENMOEDU> flashback table fb_table to timestamp sysdate-7/1440;
    
    Flashback complete.
    
    SCOTT@ENMOEDU> flashback table fb_table to timestamp sysdate-interval'8'minute;
    
    Flashback complete.
    
    ---基于scn闪回
    SCOTT@ENMOEDU> flashback table fb_table to scn 1041216;
    
    Flashback complete.

    5.闪回归档

    语法: 
    CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive 
    TABLESPACE tablespace_name 
    [QUOTA integer {K| M| G| T| P| E}] 
    RETENTION integer {YEAR | MONTH | DAY}; 

    ① 创建测试表空间用户,分配角色

    SYS@ENMOEDU> create tablespace ts_users datafile'/u01/app/oracle/oradata/ENMOEDU/ts_users.dbf' size 10M;
    
    Tablespace created.
    
    SYS@ENMOEDU> create user lgr identified by oracle default tablespace ts_users;
    
    User created.
    
    SYS@ENMOEDU> grant connect,resource to lgr;
    
    Grant succeeded.

    ② 创建flashback archive

    SYS@ENMOEDU> create flashback archive fbad_ts_users tablespace ts_users retention 1 day;
    
    Flashback archive created.

    ③创建表t1,启用闪回归档数据(这个动作需要有“flashback archive”权限)

    SYS@ENMOEDU> conn lgr/oracle
    Connected.
    LGR@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users;
    create table t1(x int) flashback archive fbad_ts_users
    *
    ERROR at line 1:
    ORA-55620: No privilege to use Flashback Archive
    
    
    LGR@ENMOEDU> conn / as sysdba
    Connected.
    SYS@ENMOEDU> grant flashback archive on fbad_ts_users to lgr;
    
    Grant succeeded.
    
    SYS@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users;
    create table t1(x int) flashback archive fbad_ts_users
                 *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object
    
    
    SYS@ENMOEDU> conn lgr/oracle
    Connected.
    LGR@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users;
    
    Table created.

    ④对已存在的表启动闪回归档数据,如下

    LGR@ENMOEDU> create table t2(x int);
    
    Table created.
    
    LGR@ENMOEDU> alter table t2 flashback archive fbad_ts_users;
    
    Table altered.

    ⑤查看已启用闪回归档数据的表(其中:E ARCHIVE_TABLE_NAME 为记录闪回数据的表,我们无法查看它)

    TABLE_NAME OWNER_NAME  FLASHBACK_ARCHI ARCHIVE_TABLE_NAME   STATUS  
    ---------- ----------- --------------- -------------------- ------- 
    T1         LGR         FBAD_TS_USERS   SYS_FBA_HIST_88884   ENABLED 
    T2         LGR         FBAD_TS_USERS   SYS_FBA_HIST_88885   ENABLED 

    ⑥禁用闪回归档数据 

    使用“no flashback archive”子句可以disable flashback archive(注:需要有权限)
    
    LGR@ENMOEDU> alter table t2 no flashback archive;
    alter table t2 no flashback archive
    *
    ERROR at line 1:
    ORA-55620: No privilege to use Flashback Archive
    
    LGR@ENMOEDU> conn / as sysdba
    Connected.
    
    SYS@ENMOEDU> alter table lgr.t2 no flashback archive;
    
    Table altered.
    
    TABLE_NAME OWNER_NAME  FLASHBACK_ARCHI ARCHIVE_TABLE_NAME   STATUS 
    ---------- ----------- --------------- -------------------- -------
    T1         LGR         FBAD_TS_USERS   SYS_FBA_HIST_88884   ENABLED

    ⑦删除数据

    ---查看t1表数据
    SYS@ENMOEDU> select * from lgr.t1;
    
             X
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
    
    10 rows selected.
    ---查看当前的scn
    SYS@ENMOEDU> select dbms_flashback.get_system_change_number as scn from dual;
    
           SCN
    ----------
       1046908
    ---删除t1表的数据
    SYS@ENMOEDU> delete lgr.t1 where x<10;
    
    9 rows deleted.
    ---查看删除后的数据
    SYS@ENMOEDU> select * from lgr.t1;
    
             X
    ----------
            10
    
    SYS@ENMOEDU> commit;
    
    Commit complete.
    
    SYS@ENMOEDU> select * from lgr.t1 as of scn 1046908;
    
             X
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
    
    10 rows selected.
  • 相关阅读:
    vscode默认vue模板设置 Jim
    LaTeX公式中括号大小不一致问题与绝对值符号问题
    二分查找总结
    drools规则的入门使用
    XtraReport中序号的实现
    数据转换位串字节数组
    实验二验收2
    实验二验收1
    SQL SERVER之分区表创建
    RabbitMq基础二之direct模式
  • 原文地址:https://www.cnblogs.com/NextAction/p/7366701.html
Copyright © 2020-2023  润新知