flashback能够用来
flashback query 闪回查询
flashback table 闪回表
flashback database 闪回数据库
一:开启flashback(开启了归档模式):
1.指定 db_recovery_file_dest_size和db_recovery_file_dest参数
SYS@ORCL>sho parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
SYS@ORCL>alter system set db_recovery_file_dest_size=4G;
System altered.
SYS@ORCL>alter system set db_recovery_file_dest='/home/oracle/flash';//若指定了归档目录,会和它有冲突。
System altered.
2.在mount状态下开启flashback
SYS@ORCL>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 104859024 bytes
Database Buffers 205520896 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS@ORCL>alter database flashback on;
Database altered.
SYS@ORCL>alter database open;
Database altered.
3.确认开启
SYS@ORCL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 47
Next log sequence to archive 49
Current log sequence 49
SYS@ORCL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
二:flashback query 闪回查询
Undo段:
当用户对数据库进行一次操作后,就会产生一个undo信息记录到undo段中。正如上图中的,undo段是像数据结构中的循环队列一样,队尾的信息会覆盖队头的信息。而一旦用户发起的commit请求来结束一个会话,undo段中的信息就会消除。
当做闪回查询时,Oracle就会去undo段中找记录着一起的Oracle数据的信息。
1.test表中原有数据
HR@PROD>select * from test;
ID NAME
---------- --------------------
1 red
2 red
3 green
4 green
5 blue
6 blue
6 rows selected.
2.更新表中数据
HR@ORCL>update hr.test set name='yellow' where id=1;
1 row updated.
3.闪回查询
HR@ORCL>select name from hr.test as of timestamp(to_date('2014-09-17 14:04:59','yyyy-mm-dd hh24:mi:ss')) where id=1;
NAME
--------------------
red
三:闪回表
1.drop掉表
HR@ORCL>drop table test;
Table dropped.
2.在recyclebin中查看,会看到test表
HR@ORCL>sho recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$Azyz/StAdSPgUKjAZWUUIA==$0 TABLE 2014-09-17:14:08:56
HR@ORCL>desc "BIN$Azyz/StAdSPgUKjAZWUUIA==$0"
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
3.闪回表
HR@ORCL>flashback table "BIN$Azyz/StAdSPgUKjAZWUUIA==$0" to before drop;
Flashback complete.
HR@ORCL>select * from test;
ID NAME
---------- --------------------
1 yellow
2 red
3 green
4 green
5 blue
6 blue
6 rows selected.