闪回技术(flashback)

1.flashback query 闪回查询

SQL> select * from bayue;
NAME                         ID
-------------------- ----------
one                           1
two                           2
three                         3

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
     388161

SQL> insert into bayue values('four',4);
已创建 1 行。

SQL> commit;
提交完成。

SQL> select * from bayue;
NAME                         ID
-------------------- ----------
one                           1
two                           2
three                         3
four                          4

SQL> select * from bayue as of scn 388161;
NAME                         ID
-------------------- ----------
one                           1
two                           2
three                         3

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

SQL> select * from bayue;
NAME                         ID
-------------------- ----------
one                           1
two                           2
three                         3
four                          4

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
     388283

SQL> 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 enabled

SQL> alter table bayue enable row movement;
表已更改。

SQL> flashback table system.bayue to scn 388283;
闪回完成。

SQL> select * from bayue;
NAME                         ID
-------------------- ----------
one                           1
two                           2
three                         3
four                          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
         2

SQL> drop table t;

表已删除。

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T                BIN$3ZyycsU433ngQKjAeAFObg==$0 TABLE        2013-05-26:17:31:30
TABLE_1          BIN$3PsVTtbErNXgQKjAeAEc6g==$0 TABLE        2013-05-18:16:42:44
SQL> 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 Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     16

Next log sequence to archive   18

Current log sequence           18

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

————————————

YES

 

select * from v$bgprocess where name='RVWR';产生flashback log

select * 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

———–

     362552

SQL> 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 mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  2020448 bytes

Variable Size             130026400 bytes

Database Buffers          180355072 bytes

Redo Buffers                2170880 bytes

Database 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 bytes

Fixed Size                  2020448 bytes

Variable Size             130026400 bytes

Database Buffers          180355072 bytes

Redo Buffers                2170880 bytes

Database 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