1.flashback query 闪回查询
3.flashback versions query,transaction query(通过版本查询的xid来做事务查询)
5.闪回数据库(flashback database)
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