Oracle 备份恢复：flashback闪回

Oracle 备份恢复：flashback闪回

flashback闪回分为三大类

第一，flashback table

第二，flashback database

第三，flashback query

(一)，开启关闭回收站

         alter session setrecyclebin =on;

         altersession set recyclebin= off;

         altersystem set recyclebin=on;

         altersystem set recyclebin=off;

         1,查看回收站里的内容

         show recyclebin

         2,,情况回收站

         purge recyclebin

         3,从回收站闪回的时候

         flashback table t1to before drop

         flashback table"BIN$mKoYM02RCojgQAB/AQAvyw==$0" to before drop;

(二)flashback query 闪回

         1,select* from emp as of timestamp sysdate-1/1440; 查询一分钟以前数据库的状态

         查询完毕之后我们插入数据

         insertinto emp select * from emp as of timestamp sysdate-1/1440

         2,用scn的方式闪回

         取得系统当前的scn

         (1)9i的时候我们取得scn比较麻烦

         selectdbms_flashback.get_system_change_number from dual;

         (2)10g之后我们可以直接从视图里查询到

         select current_scnfrom v$database;

 

         SQL>select current_scn from v$database;

 

         CURRENT_SCN

         -----------

    534578

         3,可以scn跟timestamp互换

SQL> select scn_to_timestamp(534578)from dual;

 

SCN_TO_TIMESTAMP(534578)

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

06-SEP-12 11.05.52.000000000 AM

SQL> select timestamp_to_scn('06-SEP-1211.05.52.000000000') from dual;

 

TIMESTAMP_TO_SCN('06-SEP-1211.05.52.000000000')

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

                                         534574

scn闪回查询

select * from emp as of scn 534574

         4,两个scn之间的交易  闪回事物查询

select xid,commit_scn,commit_timestamp,operation,undo_sqlfrom flashback_transaction_querywhere table_name='EMP' and table_owner='SCOTT' and commit_scn>=534574 andcommit_scn<=534624

 

         (1)闪回版本查询

         SQL>!date "+%F %T"

         2012-09-0611:37:13

         SQL>colversions_starttime for a25

         SQL>colversions_endtime for a25

 

SQL> select versions_starttime,versions_endtime, versions_xid, versions_operation,ename,sal

         fromscott.emp versions between timestamp to_timestamp('2012-09-06 11:37:13','YYYY-MM-DD HH24:MI:SS')

         andmaxvalue order by VERSIONS_STARTTIME ;

 

做交易在查询

(三)flashback database

         1,,激活数据库闪回

         alter databaseflashback on   ----在mount状态下

         showparameter db_recover

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string     /u01/tiger/flash_recovery_area

db_recovery_file_dest_size           big integer 2G

         mount状态下闪回数据库

         flashback database  to scn  534574

         flashbackdatabase to timestamp sysdate-10/1440

创建存储点

create restore point aa;

select * from v$restore_point   查看存储点对应的scn

flashback database restore point aa;   mount状态下恢复

drop restore point aa;   删除存储点