Oracle -- flashback database基于ARC+flashback_log还是flashback_log

深入研究: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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28602568/viewspace-758930/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28602568/viewspace-758930/