深入研究:flashback database基於ARC+flashback log還是flashback log；

--測試依據：根據記錄前後的scn進行分別刪除ARC、flashback log檔案測試

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6242537222

SQL> insert into trun_test select * from trun_test;

已建立711行。

SQL> commit;

提交完成。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6242537255

SQL> alter system switch logfile;

系統已更改。

SQL> /

系統已更改。

SQL> /

系統已更改。

SQL> alter system switch logfile;

系統已更改。

已用時間: 00: 00: 02.04
SQL> host dir E:\app\Administrator\flash_recovery_area\oracle11g\ARCHIVELOG\2013_04_18\
驅動器 E 中的卷是中科軟
卷的序列號是 38DC-C2C9

E:\app\Administrator\flash_recovery_area\oracle11g\ARCHIVELOG\2013_04_18 的目錄

2013-04-18 18:41

.
2013-04-18 18:41

          ..
2013-04-18 18:37           168,448 O1_MF_1_1_8PZM7VJM_.ARC
2013-04-18 18:36        44,861,952 O1_MF_1_21_8PZM5Z0H_.ARC
2013-04-18 18:36        42,160,640 O1_MF_1_22_8PZM5VTV_.ARC
2013-04-18 18:36        16,885,760 O1_MF_1_23_8PZM5XNJ_.ARC
2013-04-18 18:40           173,568 O1_MF_1_2_8PZMFM4D_.ARC
2013-04-18 18:40             6,144 O1_MF_1_3_8PZMFPJM_.ARC
2013-04-18 18:40             4,096 O1_MF_1_4_8PZMFS2G_.ARC
2013-04-18 18:41             6,656 O1_MF_1_5_8PZMGG9R_.ARC
               8 個檔案    104,267,264 位元組
               2 個目錄 43,376,058,368 可用位元組

SQL> host del E:\app\Administrator\flash_recovery_area\oracle11g\ARCHIVELOG\2013_04_18\*
E:\app\Administrator\flash_recovery_area\oracle11g\ARCHIVELOG\2013_04_18\*, 是否確認(Y/N)? y

SQL> host dir E:\app\Administrator\flash_recovery_area\oracle11g\ARCHIVELOG\2013_04_18\
驅動器 E 中的卷是中科軟
卷的序列號是 38DC-C2C9

E:\app\Administrator\flash_recovery_area\oracle11g\ARCHIVELOG\2013_04_18 的目錄

2013-04-18 18:42

.
2013-04-18 18:42

          ..
               0 個檔案              0 位元組
               2 個目錄 43,480,342,528 可用位元組
SQL> conn / as sysdba
已連線。
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area 535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             348127696 bytes
Database Buffers          180355072 bytes
Redo Buffers                5804032 bytes
資料庫裝載完畢。
SQL> flashback database to scn 6242537222;
flashback database to scn 6242537222
*
第 1 行出現錯誤:
ORA-38754: FLASHBACK DATABASE 沒有啟動; 所需的重做日誌不可用
ORA-38762: 從 SCN 6242536773 到 SCN 6242537222 需要重做日誌
ORA-38761: 無法訪問重做日誌序列 1 (線上程 1, 原型 2 中)
SQL>alter database open ;

Database altered.

SQL>

測試如果沒有FLASHBACK log怎麼樣的報錯...

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    9795505

SQL> drop table test;

Table dropped.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    9795553

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> flashback database to scn 9795553;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    9795785

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> host del D:\oracle\product\10.1.0\flash_recovery_area\ORCL\FLASHBACK\*
D:\oracle\product\10.1.0\flash_recovery_area\ORCL\FLASHBACK\*, 是否確認(Y/N)? y
D:\oracle\product\10.1.0\flash_recovery_area\ORCL\FLASHBACK\O1_MF_8PZXZY1G_.FLB
另一個程式正在使用此檔案，程式無法訪問。
--強制刪除再閃回報錯....
SQL> flashback database to scn 9795553;
flashback database to scn 9795553
*
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1: "D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\FLASHBACK\O1_MF_8PZ
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
-------------所以測試：flashback database 基於archive log+FLASHBACK log

資料庫打不開關閉閃回區就可以open了

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 4月 18 22:07:44 2013

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SQL> alter database flashback off;

Database altered.

SQL> alter database open;

Database altered.

SQL>

Oracle -- flashback database基於ARC+flashback_log還是flashback_log

相關文章