11G归档模式下开启闪回可以OPEN状态下执行

在10G中，如果要开启数据库级别的闪回，需要设置相关的参数，并且使数据库处于归档模式，然后再在MOUNT状态下开启闪回。
如果在OPEN状态下开启闪回，将会遇到如下的错误：

 

SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------

db_recovery_file_dest string /test/orcl/flashback
db_recovery_file_dest_size big integer 2G
SQL> select log_mode,flashback_on from v$database;

LOG_MODE FLASHBACK_ON
------------------------ ------------------------------------

ARCHIVELOG NO

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

可以看到如果要开启闪回，数据库需要启动到MOUNT状态才可以。

下面是10G中开启数据库闪回的大体步骤，如果没有开启归档的话，还得需要先开启归档。

 

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

Total System Global Area 1610612736 bytes
Fixed Size 2084400 bytes
Variable Size 402653648 bytes
Database Buffers 1191182336 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE FLASHBACK_ON
------------------------ ------------------------------------

ARCHIVELOG YES

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL>

这是10G中的情况，在11G中，如果设置了相关的参数及其开启了归档，那么可以再OPEN状态下打开闪回。
如下：

[oracle@db2server ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 5 17:05:38 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 506368000 bytes
Fixed Size 1337520 bytes
Variable Size 331351888 bytes
Database Buffers 167772160 bytes
Redo Buffers 5906432 bytes
Database mounted.
Database opened.
SQL> select log_mode,flashback_on from v$database;

LOG_MODE FLASHBACK_ON
------------ ------------------

ARCHIVELOG NO

SQL> show parameter db_recover

NAME TYPE VALUE
------------------------------------ ----------- -----------------------------------

db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 3852M

SQL> alter database flashback on;

Database altered.

SQL> select log_mode,flashback_on from v$database;

LOG_MODE FLASHBACK_ON
------------ ------------------

ARCHIVELOG YES


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

算是11G的一个小改进吧。