闪回技术(flashback)

1.flashback query 闪回查询

SQL> select * from bayue;NAME                         ID-------------------- ----------one                           1two                           2three                         3SQL> select current_scn from v$database;CURRENT_SCN-----------     388161SQL> insert into bayue values('four',4);已创建 1 行。SQL> commit;提交完成。SQL> select * from bayue;NAME                         ID-------------------- ----------one                           1two                           2three                         3four                          4SQL> select * from bayue as of scn 388161;NAME                         ID-------------------- ----------one                           1two                           2three                         3

2:闪回表(flashback table)sys用户不支持闪回表

SQL> select * from bayue;NAME                         ID-------------------- ----------one                           1two                           2three                         3four                          4SQL> select current_scn from v$database;CURRENT_SCN-----------     388283SQL> insert into bayue values('five',5);已创建 1 行。SQL> commit;提交完成。SQL> flashback table bayue to scn 388283;flashback table bayue to scn 388283                *第 1 行出现错误:ORA-08189: cannot flashback the table because row movement is not enabledSQL> alter table bayue enable row movement;表已更改。SQL> flashback table system.bayue to scn 388283;闪回完成。SQL> select * from bayue;NAME                         ID-------------------- ----------one                           1two                           2three                         3four                          4

3.flashback versions query,transaction query(通过版本查询的xid来做事务查询)

select versions_startscn,versions_endscn,versions_xid,versions_operation,versions_starttime,versions_endtime,
id from bayue versions between scn minvalue and maxvalue order by versions_startscn;

select * from flashback_transaction_query where xid='0A000100A4000000';

4.flashback drop(并非SYS以及SYSTEM系统用户下所有表都不能被闪回这里再次强调，只有那些创建在SYSTEM表空间上的表不可以被drop闪回) 只有本地管理和非system表空间里的才能flashback drop

SQL> create table t(id int) tablespace test;表已创建。SQL> insert into t values(1);已创建 1 行。SQL> insert into t values(2);已创建 1 行。SQL> commit;提交完成。SQL> select  * from t;        ID----------         1         2SQL> drop table t;表已删除。SQL> show recyclebinORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME---------------- ------------------------------ ------------ -------------------T                BIN$3ZyycsU433ngQKjAeAFObg==$0 TABLE        2013-05-26:17:31:30TABLE_1          BIN$3PsVTtbErNXgQKjAeAEc6g==$0 TABLE        2013-05-18:16:42:44SQL> flashback table t to before drop;闪回完成。SQL> select  * from t;        ID----------         1         2

5.闪回数据库(flashback database)

SQL> select flashback_on from v$database; FLASHBACK_ON————————————NO 要启用闪回数据库必须满足 archivelog 和 使用 flash_recovery_area shutdown immediate;startup mount;alter database archivelog;alter database flashback on;alter database open; SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     16Next log sequence to archive   18Current log sequence           18 SQL> select flashback_on from v$database; FLASHBACK_ON————————————YES select * from v$bgprocess where name='RVWR';产生flashback logselect * from v$flashback_database_stat;select * from v$flashback_database_log;select * from v$flashback_database_logfile;select * from v$flash_recovery_area_usage; SQL> select current_scn from v$database; CURRENT_SCN———–     362552SQL> drop user ranyuan cascade;User dropped. shutdown immediate;startup mount;SQL> flashback database to scn 363552;Flashback complete. SQL> alter database open read only;alter database open read only*ERROR at line 1:ORA-16006: audit_trail destination incompatible with database open mode  SQL> alter system set audit_trail=false scope=spfile; System altered. SQL> shutdown immediate;ORA-01109: database not open  Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started. Total System Global Area  314572800 bytesFixed Size                  2020448 bytesVariable Size             130026400 bytesDatabase Buffers          180355072 bytesRedo Buffers                2170880 bytesDatabase mounted.SQL> alter database open read only; Database altered. SQL> select username from dba_users where username='RANYUAN'; USERNAME——————————RANYUAN SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down. SQL> startup mount;ORACLE instance started. Total System Global Area  314572800 bytesFixed Size                  2020448 bytesVariable Size             130026400 bytesDatabase Buffers          180355072 bytesRedo Buffers                2170880 bytesDatabase mounted.SQL> alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database open  SQL> alter database open resetlogs; Database altered. SQL> show parameter db_flashback_  (flashback log保留时间) NAME                                 TYPE        VALUE———————————— ———– ——————————db_flashback_retention_target        integer     1440