flashback

开启闪回模式：

开启到mount状态

SYS@ yisidb2>startup mount;

查看归档模式

SYS@ yisidb2>archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch

Oldest online log sequence     1

Current log sequence           3

开启到归档模式----------------------------------------&gt只有归档模式才能使用闪回

SYS@ yisidb2>alter database archivelog;

开启闪回模式

SYS@ yisidb2>alter database flashback on;

开启数据库到open

SYS@ yisidb2>alter database open;

设置闪回表空间

create tablespace fdatbs1 datafile '/u01/app/oracle/oradata/yisidb2/fdatbs101.dbf'size 50m;

创建闪回数据归档

create flashback archive fda1 tablespace fdatbs1 quota 20m retention 1 day;

设置闪回恢复区80兆，

alter system set db_recovery_file_dest_size=80m scope=spfile;

设置默认的闪回数据归档

alter flashback archive fda1 set default;

设置恢复5天

SYS@ yisidb2>alter system set db_flashback_retention_target=7200 scope=spfile;

sys@TESTDB12>select empno from scott.emp as of timestamp to_timestamp

  2  ('2015-05-04 09:31:00' ,'YY-MM-DD HH24:MI:SS');

 

 

 

 

 

关于回收站

 

打开回收站

 

SYS@ yisidb2>alter system set recyclebin=on scope=spfile;

SCOTT@ yisidb2>create table emp1 as select * from emp;

Table created.

SCOTT@ yisidb2>alter table emp1 add constraint emp1_pk primary key(empno);

Table altered.

SCOTT@ yisidb2>create index idx_emp1_ename on emp1(ename);

 

Index created.

 

SCOTT@ yisidb2>drop table emp1;

 

Table dropped.

查看回收站

SCOTT@ yisidb2>select original_name,object_name,type from user_recyclebin;

 

ORIGINAL_NAME                    OBJECT_NAME                    TYPE

-------------------------------- ------------------------------ -------------------------

IDX_EMP1_ENAME                   BIN$FT51EIlHCurgU2YfqMAm7g==$0 INDEX

EMP1_PK                          BIN$FT51EIlICurgU2YfqMAm7g==$0 INDEX

EMP1                             BIN$FT51EIlJCurgU2YfqMAm7g==$0 TABLE

 

SCOTT@ yisidb2>select original_name from recyclebin;

 

ORIGINAL_NAME

--------------------------------

IDX_EMP1_ENAME

EMP1_PK

EMP1

SCOTT@ yisidb2>flashback table emp1 to before drop;

Flashback complete.

 

 

 

SCOTT@ yisidb2>select index_name from user_indexes;

INDEX_NAME

------------------------------

PK_EMP

PK_DEPT

BIN$FT51EIlHCurgU2YfqMAm7g==$0

BIN$FT51EIlICurgU2YfqMAm7g==$0

闪回成功

 

 

 

 

闪回数据库

 

建立恢复点

SCOTT@ yisidb2>create restore point rp_before_delete;

删除表

SYS@ yisidb2>delete from scott.emp;

 

14 rows deleted.

 

SYS@ yisidb2>commit;

 

Commit complete.

 

SYS@ yisidb2>create restore point rp_before_delete;

 

Restore point created.

 

SYS@ yisidb2>delete from scott.emp;

 

14 rows deleted.

 

SYS@ yisidb2>commit;

 

Commit complete.

 

SYS@ yisidb2>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ yisidb2>startup mount;

ORACLE instance started.

 

Total System Global Area  795127808 bytes

Fixed Size                  1347876 bytes

Variable Size             494931676 bytes

Database Buffers          293601280 bytes

Redo Buffers                5246976 bytes

Database mounted.

SYS@ yisidb2>flashback database to restore point rp_before_delete;

 

Flashback complete.

以只读模式开库，验证数据库的内容

SYS@ yisidb2>alter database open read only;

 

Database altered.

 

SYS@ yisidb2>select * from scott.emp;

SYS@ yisidb2>select * from scott.emp;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                  20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300       30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500

…………………….

 

 

 

查看当前的恢复窗口

 

SYS@ yisidb2>select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

 

OLDEST_FLASHBACK_SCN OLDEST_FL

-------------------- ---------

             1163856 03-MAY-15

 

SYS@ yisidb2>select * from v$flashback_database_stat;

 

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE

--------- --------- -------------- ---------- ---------- ------------------------

04-MAY-15 04-MAY-15        3637248    7626752          0                        0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30151911/viewspace-1622564/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30151911/viewspace-1622564/