闪回Flashback

忘是亡心i 2022-08-06 06:28 163阅读 0赞

1-flashback drop

依赖于tablespace recycle bin 类似WINDOW回收站
    SYS@ fyl>show parameter recyclebin
    NAME                                 TYPE                              VALUE
    ------------------------------------ --------------------------------- ------------------------------
    recyclebin                           string                            on
    Flashback Drop 用于非系统表空间和本地管理的表空间的恢复
    sys/system用户下的默认表空间是system表空间 不能进行闪回

实验--

YALI@ fyl>create table t3 as select * from emp;
    Table created.
    
    YALI@ fyl>drop table t3;
    Table dropped.
    
    YALI@ fyl>show recyclebin;
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    ---------------- ------------------------------ ------------ -------------------
    T3               BIN$C27+lAxZ1xPgUKjAYwYHbg==$0 TABLE        2014-12-30:21:30:28
    
    YALI@ fyl>select count(*) from "BIN$C27+lAxZ1xPgUKjAYwYHbg==$0";
      COUNT(*)
    ----------
            14
    
    YALI@ fyl>create table t3 as select * from dept;
    Table created.
    
    YALI@ fyl>drop table t3;
    Table dropped.
    
    YALI@ fyl>show recyclebin
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    ---------------- ------------------------------ ------------ -------------------
    T3               BIN$C27+lAxa1xPgUKjAYwYHbg==$0 TABLE        2014-12-30:21:34:47
    T3               BIN$C27+lAxZ1xPgUKjAYwYHbg==$0 TABLE        2014-12-30:21:30:28
    
    YALI@ fyl>select count(*) from "BIN$C27+lAxa1xPgUKjAYwYHbg==$0";
      COUNT(*)
    ----------
             4
    
    YALI@ fyl>select table_name from tabs;
    TABLE_NAME
    ------------------------------------------------------------------------------------------
    DEPT
    EMP
    BONUS
    SALGRADE
    T1
    
    闪回删除的count(*)=14的表
    YALI@ fyl>flashback table "BIN$C27+lAxZ1xPgUKjAYwYHbg==$0" to before drop;
    Flashback complete.
    
    YALI@ fyl>select table_name from tabs;
    TABLE_NAME
    ------------------------------------------------------------------------------------------
    DEPT
    EMP
    BONUS
    SALGRADE
    T1
    T3
    
    6 rows selected.
    
    闪回后recclebin自动删除
    YALI@ fyl>show recyclebin
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    ---------------- ------------------------------ ------------ -------------------
    T3               BIN$C27+lAxa1xPgUKjAYwYHbg==$0 TABLE        2014-12-30:21:34:47
    
    YALI@ fyl>flashback table "BIN$C27+lAxa1xPgUKjAYwYHbg==$0" to before drop rename to t3_old;
    Flashback complete.
    
    YALI@ fyl>select table_name from tabs;
    TABLE_NAME
    ------------------------------------------------------------------------------------------
    DEPT
    EMP
    BONUS
    SALGRADE
    T1
    T3
    T3_OLD
    
    7 rows selected.
    
    YALI@ fyl>show recyclebin
    YALI@ fyl>select count(*) from t3;
      COUNT(*)
    ----------
            14
    
    YALI@ fyl>select count(*) from t3_old;
      COUNT(*)
    ----------
             4

2--flashback query

实验--

SYS@ fyl>select * from t1;
        DEPTNO DNAME                                      LOC
    ---------- ------------------------------------------ ---------------------------------------
            10 ACCOUNTING                                 NEW YORK
            20 RESEARCH                                   DALLAS
            30 SALES                                      CHICAGO
            40 OPERATIONS                                 BOSTON
    SYS@ fyl>alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';
    Session altered.
    SYS@ fyl>select sysdate from dual;
    SYSDATE
    -------------------
    2014-12-30 21:53:22
    
    SYS@ fyl>delete from t1;
    4 rows deleted.
    SYS@ fyl>commit;
    Commit complete.
    
    运用闪回查询删除的数据
    SYS@ fyl>select count(*) from t1 as of timestamp
      2  to_timestamp('2014-12-30 21:53:22','yyyy-mm-dd hh24:mi:ss');
        DEPTNO DNAME                                      LOC
    ---------- ------------------------------------------ ---------------------------------------
            10 ACCOUNTING                                 NEW YORK
            20 RESEARCH                                   DALLAS
            30 SALES                                      CHICAGO
            40 OPERATIONS                                 BOSTON

闪回表的语法
    YALI@ fyl>flashback table t1 to timestamp to_timestamp('2014-12-30 22:05:00','yyyy-mm-dd hh24:mi:ss');

3--Flashback Database

Flashback Database架构包括recover write（RVWR）后台进程，闪回日志和闪回区  
启用Flashback Database功能

1）配置闪回区
    SYS@ fyl>show parameter recover
    NAME                                 TYPE                              VALUE
    ------------------------------------ --------------------------------- ------------------------------
    db_recovery_file_dest                string
    db_recovery_file_dest_size           big integer                       0
    recovery_parallelism                 integer                           0
    SYS@ fyl>alter system set db_recovery_file_dest='/oracle/flash_recover_area' scope=spfile;
    System altered.
    SYS@ fyl>alter system set db_recovery_file_dest_size=1g scope=both;
    System altered.
    2）启用闪回功能
    数据库mount下
    SYS@ fyl>select name,current_scn,flashback_on from v$database;
    NAME                   CURRENT_SCN FLASHBACK_ON
    ---------- ----------------------- --------------------
    FYL                              0 NO
    
    SYS@ fyl>alter database flashback on;
    Database altered.
    SYS@ fyl>select name,current_scn,flashback_on from v$database;
    NAME                   CURRENT_SCN FLASHBACK_ON
    ---------- ----------------------- --------------------
    FYL                              0 YES
    
    此时检查闪回区下已经出现闪回日志
    [oracle@fyl flashback]$ pwd
    /oracle/flash_recover_area/FYL/flashback
    [oracle@fyl flashback]$ ls -l
    total 8020
    -rw-r----- 1 oracle dba 8200192 Dec 30 20:44 o1_mf_bb57l963_.flb
    闪回进程也出现
    [oracle@fyl flashback]$ ps -ef | grep rvwr
    oracle    4045     1  0 20:44 ?        00:00:00 ora_rvwr_fyl
    oracle    4057  3840  0 20:46 pts/1    00:00:00 grep rvwr
    闪回时间，此时可闪回1440min=1440/60/24=1天
    SYS@ fyl>show parameter flashback
    NAME                                 TYPE                              VALUE
    ------------------------------------ --------------------------------- ------------------------------
    db_flashback_retention_target        integer                           1440

实验--

查看闪回信息
    SYS@ fyl>select to_char(OLDEST_FLASHBACK_TIME,'yyyy-mm-dd hh24:mi:ss') oldest_time,RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;                          ------v$flashback_database_logfile
    
    OLDEST_TIME                                               RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
    --------------------------------------------------------- ---------------- -------------- ------------------------
    2014-12-30 20:44:58                                                   1440        8192000                        0
    
    SYS@ fyl>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')
    ---------------------------------------------------------
    2014-12-30 21:00:00
    SYS@ fyl>create table t1 as select * from dba_objects;
    Table created.
    SYS@ fyl>select count(*) from t1;
      COUNT(*)
    ----------
         65489
    SYS@ fyl>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')
    ---------------------------------------------------------
    2014-12-30 21:01:29
    SYS@ fyl>truncate table t1;
    Table truncated.
    SYS@ fyl>select count(*) from t1;
      COUNT(*)
    ----------
             0
    SYS@ fyl>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')
    ---------------------------------------------------------
    2014-12-30 21:03:15
    
    基于时间闪回
    SYS@ fyl>shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@ fyl>startup mount
    ORACLE instance started.
    Database mounted.
    
    SYS@ fyl>flashback database to timestamp to_timestamp('2014-12-30 21:01:29','yyyy-mm-dd hh24:mi:ss');  --count（*）=65489时间
    Flashback complete.
    
    SYS@ fyl>alter database open read only;         ----只读打开确实闪回时间点是否正确
    Database altered.
    
    SYS@ fyl>select count(*) from t1;         ---确认OK
      COUNT(*)
    ----------
         65489
    
    SYS@ fyl>shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@ fyl>startup mount
    ORACLE instance started.
    Database mounted.
    
    SYS@ fyl>flashback database to timestamp to_timestamp('2014-12-30 21:00:00 ','yyyy-mm-dd hh24:mi:ss');   ---还未建表t1时间
    Flashback complete.
    
    SYS@ fyl>alter database open read only;
    Database altered.
    
    SYS@ fyl>select count(*) from t1;
    select count(*) from t1
                         *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    如果确定好闪回时间位置
    SYS@ fyl>shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@ fyl>startup mount;
    ORACLE instance started.
    Database mounted.
    SYS@ fyl>alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    
    SYS@ fyl>alter database open resetlogs;
    Database altered.
    
    SYS@ fyl>select count(*) from t1;
    select count(*) from t1
                         *
    ERROR at line 1:
    ORA-00942: table or view does not exist             此时恢复到未建表t1 时间