闪回Flashback

最新推荐文章于 2018-09-19 11:33:29 发布

Two手揣兜

最新推荐文章于 2018-09-19 11:33:29 发布

阅读量615

点赞数

分类专栏： ORACLE--安装备份恢复文章标签：闪回技术

本文链接：https://blog.csdn.net/u013820054/article/details/42275521

版权

ORACLE--安装备份恢复专栏收录该内容

14 篇文章 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 时间