程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle11G閃回flashback

oracle11G閃回flashback

編輯：Oracle教程

oracle11G閃回flashback

oracle11G閃回flashback oracle9i的閃回：

閃回查詢從9i引入，可以按照時間點或者SCN向前查

詢，獲取修改前的數據。

閃回查詢依賴於回滾段中存儲的數據前鏡像，通過設置

undo_retention參數設置前鏡像的保留時間。

查詢的語法：

select … as of scn | timestamp

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as hr@JIAGULUN
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 1 zhangsan 21 2 lisi 22 3 wangwu 23
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 17:58:58
SQL> SQL> update student s set s.name='mazi' where s.id = 1; 1 row updated
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 18:02:56
SQL> delete from student s where s.name = 'lisi'; 1 row deleted
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 1 mazi 21 3 wangwu 23
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 18:04:00
SQL> delete from student; 2 rows deleted
SQL> select * from student; ID NAME AGE ----------- -------------------- -----------
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 18:04:26
SQL> select * from student as of scn 3099731; ID NAME AGE ----------- -------------------- ----------- 1 zhangsan 21 2 lisi 22 3 wangwu 23 SQL> select * from student; ID NAME AGE ----------- -------------------- -----------
SQL> commit; Commit complete
SQL> insert into student values(1,'zhangsan',23); 1 row inserted
SQL> commit; Commit complete
SQL> SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:36:30
SQL> SQL> delete from student; 1 row deleted
SQL> commit; Commit complete
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:36:51
SQL> select * from student as of timestamp to_date('20150124 19:36:30','yyyymmdd hh24:mi:ss'); ID NAME AGE ----------- -------------------- ----------- 1 zhangsan 23
SQL> select * from student as of timestamp to_date('20150124 18:04:26','yyyymmdd hh24:mi:ss'); ID NAME AGE ----------- -------------------- ----------- 1 zhangsan 21 2 lisi 22 3 wangwu 23
SQL>

oracle10g的閃回版本查詢：

對於9i的閃回查詢進行增強，Oracle10g提供了閃回版本

查詢，因為9i的閃回查詢僅僅能夠得到過去某個時間點上的

數據，但是無法反映出一段時間內數據表中數據的變化，

10g的閃回版本查詢可以對時間段內數據表的不同版本進行

查詢。

閃回版本查詢的語法：

select … from … versions between

其中，select後面可以選擇偽列，來獲得事務的開始、結束時間、SCN號、ID號等。

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:40:11
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------使用上面的系統包必須賦予權限：grant execute on dbms_flashback to hr; 3115836
SQL> SQL> insert into student values(1,'zhangsan',23); 1 row inserted
SQL> commit; Commit complete
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:51:36
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3115938
SQL> update student s set s.name='wangwu' where s.id = 1; 1 row updated
SQL> commit; Commit complete
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3115954
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:52:26
SQL> insert into student values (2,'mazi',24); 1 row inserted
SQL> commit; Commit complete
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:52:58
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3116062
SQL> SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,id,name,age from student 2 versions between scn minvalue and maxvalue; VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ID NAME AGE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ------------------ ----------- -------------------- ----------- 24-1月 -15 07.52.54 下午 0B00080005010000 I 2 mazi 24 24-1月 -15 07.52.15 下午 13000100FE000000 U 1 wangwu 23 24-1月 -15 07.51.29 下午 24-1月 -15 07.52.15 下午 12001F00FF000000 I 1 zhangsan 23
SQL> SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student 2 versions between scn minvalue and maxvalue; TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE ------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- ----------- 20150124 19:52:54 0B00080005010000 I 2 mazi 24 20150124 19:52:15 13000100FE000000 U 1 wangwu 23 20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I 1 zhangsan 23
SQL> SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student 2 versions between timestamp minvalue and maxvalue; TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE ------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- ----------- 20150124 19:52:54 0B00080005010000 I 2 mazi 24 20150124 19:52:15 13000100FE000000 U 1 wangwu 23 20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I 1 zhangsan 23
SQL> 過一段時間你會發現查詢的結果變少了： SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student 2 versions between timestamp minvalue and maxvalue; TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE ------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- ----------- 2 mazi 24 1 wangwu 23
SQL> 這是因為undo_retention這個值設置的時間到期了。

oracle10g的閃回事務查詢：

Oracle10g可以進行基於閃回版本查詢的恢復，就是閃回事務查詢。

從flashback_transaction_query中查詢引起數據變化的

事務，和撤銷事務的SQL語句，就是查詢 operation和undo_sql 列。

SQL> select xid,start_timestamp,operation,undo_sql from flashback_transaction_query where table_name='STUDENT'; XID START_TIMESTAMP OPERATION UNDO_SQL ---------------- --------------- -------------------------------- -------------------------------------------------------------------------------- 0F00020002010000 24-1月-15 20:32: UNKNOWN 0F00040002010000 24-1月-15 20:32: UNKNOWN 0F00040002010000 24-1月-15 20:32: UNKNOWN 13000100FE000000 24-1月-15 19:52: UNKNOWN 1400200004010000 24-1月-15 20:32: UNKNOWN

如上圖所示的，但是這裡查不出來奇怪了。。。。通過undo_sql來進行回滾事務（就是再做次反向操作）

oracle10g的閃回表：

Oracle10g的閃回表是把表裡的數據回退到以前的某個時

刻或者SCN上。

特點：可以在線操作；自動恢復相關的屬性，包括索引、

觸發器等。

前提：對表啟用行遷移。

語法：flashback table <table_name> to timestamp | scn

Connected as hr@JIAGULUN
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 3 zhangsan 21
SQL> alter table student enable row movement; Table altered
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3143334
SQL> delete from student where id in(1); 0 rows deleted
SQL> delete from student where id in(3); 1 row deleted
SQL> commit; Commit complete
SQL> flashback table student to scn 3143334; Done
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 3 zhangsan 21
SQL>

注意：sys的表不能閃回。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as sys@JIAGULUN AS SYSDBA
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 1 zhangsan 20 2 lisi 21 3 wangwu 22
SQL> alter table student enable row movement; Table altered
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3142994
SQL> delete from student where id in(1,2); 2 rows deleted
SQL> commit; Commit complete
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 3 wangwu 22
SQL> flashback table student to scn 3142994; flashback table student to scn 3142994 ORA-08185: 用戶 SYS 不支持閃回
SQL> show user; User is "SYS"
SQL>

oracle10g的閃回刪除：

Oracle10g的閃回刪除：可以恢復一個被drop的對象，因

為進行drop時，Oracle先把它放到回收站中。（回收站和操作系統的回收站很相似）

回收站內的信息：show recyclebin

閃回刪除：flashback table <table_name> to before drop;

徹底刪除：drop table <table_name> purge;

清空回收站: purge recyclebin;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as hr@JIAGULUN
SQL> purge recyclebin; Done
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- ADDRESS TABLE
SQL> select * from address; ID NAME ----------- -------------------- 1 jiangxi 2 hunan
SQL> show recyclebin; SQL> select * from user_recyclebin;--回收站並沒有任何垃圾 OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
SQL> drop table address;--刪除表 Table dropped
SQL> show recyclebin; SQL> select * from user_recyclebin;--會發現回收站中出現了——表，索引垃圾 OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ---------- BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:49:08 3145534 YES YES 77236 77236 77236 8 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 PK_ADDRESS DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:49:08 3145530 NO YES 77236 77236 77239 8 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 INDEX_ADDRESS_NAME DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:49:08 3145525 NO YES 77236 77236 77240 8
SQL> select * from tab;--剛剛存在address表不見了，但是多了一張下面的表 TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 TABLE
SQL> flashback table student to before drop; flashback table student to before drop ORA-38305: 對象不在回收站中
SQL> flashback table address to before drop;--對回收站的表進行閃回 Done
SQL> select * from user_recyclebin;--回收站中的表被閃回了 OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
SQL> select * from tab;--表被閃回了 TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- ADDRESS TABLE
SQL> drop table address; Table dropped
SQL> create table address(id number(10)); Table created
SQL> insert into address values(1); 1 row inserted
SQL> commit; Commit complete
SQL> drop table address; Table dropped
SQL> select * from user_recyclebin;--回收站中有兩個address表 OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ---------- BIN$DXVcPQqkU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-24:22:51:52 2015-01-24:22:52:19 3145976 YES YES 77424 77424 77424 8 BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145840 YES YES 77236 77236 77236 8 BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145836 NO YES 77236 77236 77239 8 BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145831 NO YES 77236 77236 77240 8
SQL> flashback table address to before drop;--閃回表 Done
SQL> select * from user_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ---------- BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145840 YES YES 77236 77236 77236 8 BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145836 NO YES 77236 77236 77239 8 BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145831 NO YES 77236 77236 77240 8
SQL> select * from address;--被閃回的表示最晚送到回收站的那張表 ID ----------- 1
SQL> SQL> drop table address purge;--刪除表並不送入到回收站中 Table dropped
SQL> select * from user_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ---------- BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS DROP TABLE USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145840 YES YES 77236 77236 77236 8 BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145836 NO YES 77236 77236 77239 8 BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP INDEX USERS 2015-01-22:06:10:58 2015-01-24:22:51:38 3145831 NO YES 77236 77236 77240 8
SQL> SQL> flashback table address to before drop;--閃回原來存在於回收站的表 Done
SQL> select * from user_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
SQL> select * from address; ID NAME ----------- -------------------- 1 jiangxi 2 hunan
SQL>

通過參數recyclebin來啟用、禁用回收站。

SQL> show parameter recycle; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string on
SQL> SQL> select * from address; ID NAME ----------- -------------------- 1 jiangxi 2 hunan
SQL> alter session set recyclebin = off;--關閉回收站 Session altered
SQL> drop table address; Table dropped
SQL> select * from user_recyclebin;--回收站為空的 OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
SQL>

閃回數據庫：

如果數據庫出現邏輯錯誤，無法采用閃回表的方式進行恢

復，或者數據庫的結構發生了改變，可以通過閃回數據庫的

方式把整個數據庫回退到出錯前的時間點上。步驟：

1，配置數據庫為歸檔模式

2，配置閃回恢復區

3，配置閃回保留時間

4，啟用數據庫閃回 alter database flashback on;

5，進行閃回數據庫

flashback database to timestamp |scn

SQL> show user
USER is "SYS"
SQL> archive log list;--查看是否是歸檔模式
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/archive02/
Oldest online log sequence 362
Next log sequence to archive 365
Current log sequence 365
SQL> alter system set log_archive_start = false scope =spfile;--設置為不是自動歸檔（在9i後失效了，歸檔模式就是自動的）

System altered.

SQL> shutdown immediate--關閉數據庫
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount--打開數據庫到mount狀態
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 400846848 bytes
Fixed Size 2213776 bytes
Variable Size 276826224 bytes
Database Buffers 117440512 bytes
Redo Buffers 4366336 bytes
Database mounted.
SQL> alter database noarchivelog--修改為非歸檔模式
2 ;

Database altered.

SQL> alter database open--打開數據庫
2 ;
------------------------------------前面是修改數據庫為非歸檔模式------------------ Database altered.

SQL> archive log list;--查看是否歸檔
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/archive/archive02/
Oldest online log sequence 362
Current log sequence 365
SQL> clear
SQL> !clear

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/archive/archive02/
Oldest online log sequence 362
Current log sequence 365
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/archive02/--歸檔區，默認是在閃回區
Oldest online log sequence 362
Next log sequence to archive 365
Current log sequence 365
SQL> show parameter db_rec

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
--閃回區配置 _area
db_recovery_file_dest_size big integer 3882M
db_recycle_cache_size big integer 0
SQL> show parameter db_flashback_retent

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440--閃回保留時間默認是24小時，也就是1440分鐘
SQL> !ls /u01/app/oracle/flash_recovery_area
jiagulun JIAGULUN——裡面是空的

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 400846848 bytes
Fixed Size 2213776 bytes
Variable Size 276826224 bytes
Database Buffers 117440512 bytes
Redo Buffers 4366336 bytes
Database mounted.

SQL> alter database flashback on;--啟動數據庫閃回

Database altered.

SQL> !ls /u01/app/oracle/flash_recovery_area
jiagulun JIAGULUN
[oracle@localhost flashback]$ pwd
/u01/app/oracle/flash_recovery_area/JIAGULUN/flashback
[oracle@localhost JIAGULUN]$ cd flashback/
[oracle@localhost flashback]$ ls
o1_mf_bd97qnhk_.flb o1_mf_bd987hyb_.flb SQL> alter database open;

Database altered.

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'
-----------------
20150124 23:47:01

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TESTTS
TEMP2
TEMP3
UNDOTBS2
TEST_TRAN_TS

11 rows selected.

SQL> create tablespace test_flashback_database datafile '/u01/app/oracle/oradata/jiagulun/test_flashback_database.dbf' size 10m;

Tablespace created.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TESTTS
TEMP2
TEMP3
UNDOTBS2
TEST_TRAN_TS

NAME
------------------------------
TEST_FLASHBACK_DATABASE

12 rows selected.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 400846848 bytes
Fixed Size 2213776 bytes
Variable Size 276826224 bytes
Database Buffers 117440512 bytes
Redo Buffers 4366336 bytes
Database mounted.

SQL> flashback database to timestamp to_date('20150124 23:47:01','yyyymmdd hh24:mi:ss');

Flashback complete.

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 400846848 bytes
Fixed Size 2213776 bytes
Variable Size 276826224 bytes
Database Buffers 117440512 bytes
Redo Buffers 4366336 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;--為什麼會要求open為resetlog？ 因為：數據庫被閃回了，那麼從閃回前在到閃回點的重做日志對於數據庫來說已經是無效的了。所以要求重設日志。 SQL> alter database open read only;--有兩種模式：read only、read write；之所以為這種狀態，因為數據庫在閃回後，就不是現在的數據庫了
數據庫的狀態發生了改變，不希望其他用戶登錄進來防止出現問題
Database altered.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TESTTS
TEMP2
TEMP3
UNDOTBS2
TEST_TRAN_TS
TEST_FLASHBACK_DATABASE--已經不存在了，被閃回了 11 rows selected.

SQL>

Database altered.

SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archive/archive02/
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> alter database noarchivelog;--歸檔變成非歸檔
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
--表示在flashback狀態不能變成非歸檔
SQL> alter database flashback off;--關閉flashback

Database altered.

SQL> alter database noarchivelog;--再進行歸檔到非歸檔的切換

Database altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/archive/archive02/
Oldest online log sequence 1
Current log sequence 1

SQL> alter database open;

Database altered.

SQL>