[20120810]11GR2的flashback database.txt

[20120810]11GR2的flashback database.txt

flashback database是oracle 10g下非常好的特性,它可以回滚到特定的时刻,而不需要rman之类的程序恢复.
但是10g下我想许多人选择的是不打开这个功能,除非一些特殊的情况,比如升级等情况.

而且10g仅仅在在database mount阶段才能打开flashback database这个功能:

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 Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> select log_mode,flashback_on,open_mode  from v$database;

LOG_MODE     FLASHBACK_ON       OPEN_MODE

------------ ------------------ ----------

ARCHIVELOG   NO                 READ WRITE

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.

--很明显在10g下数据库在open后无法激活flashback database这个功能.

那么11G下呢?

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit 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

SQL> select log_mode,flashback_on,open_mode  from v$database;

LOG_MODE     FLASHBACK_ON       OPEN_MODE

------------ ------------------ --------------------

ARCHIVELOG   NO                 READ WRITE

SQL> alter database flashback on;

Database altered.

SQL> select log_mode,flashback_on,open_mode  from v$database;

LOG_MODE     FLASHBACK_ON       OPEN_MODE

------------ ------------------ --------------------

ARCHIVELOG   YES                READ WRITE

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE

-------------------- ------------------- ---------------- -------------- ------------------------

            15807763 2012-08-10 10:19:46             1440       15941632                        0

--可以发现11G2可以在数据库open的状态下打开flashback database功能.

--测试是否能正常使用!

1.建立测试数据:

create table t (id number,ins_date date,ins_scn number);

SQL> select 1,sysdate,current_scn from v$database ;

         1 SYSDATE             CURRENT_SCN

---------- ------------------- -----------

         1 2012-08-10 10:26:46    15808025

SQL> insert into t select 1,sysdate,current_scn from v$database ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select sysdate,current_scn from v$database ;

SYSDATE             CURRENT_SCN

------------------- -----------

2012-08-10 10:27:06    15808081

SQL> insert into t select 2,sysdate,current_scn from v$database ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select sysdate,current_scn from v$database ;

SYSDATE             CURRENT_SCN

------------------- -----------

2012-08-10 10:27:18    15808088

SQL> select * from t;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

         1 2012-08-10 10:26:57   15808073

         2 2012-08-10 10:27:13   15808084

2.测试开始:

SQL> startup mount

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2215064 bytes

Variable Size            1543504744 bytes

Database Buffers          587202560 bytes

Redo Buffers                4964352 bytes

Database mounted.

SQL> flashback database to scn 15808081;

Flashback complete.

SQL> alter database open read only ;

Database altered.

SQL> select * from scott.t;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

         1 2012-08-10 10:26:57   15808073

--可以发现仅仅存在1条记录.

SQL> shutdown immediate ;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2215064 bytes

Variable Size            1543504744 bytes

Database Buffers          587202560 bytes

Redo Buffers                4964352 bytes

Database mounted.

SQL> flashback database to scn 15808088;

Flashback complete.

SQL> alter database open read only ;

Database altered.

SQL> select * from scott.t ;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

         1 2012-08-10 10:26:57   15808073

         2 2012-08-10 10:27:13   15808084

--可以发现仅仅存在2条记录.说明这样操作没有问题.

3.关闭flashback database.

SQL> select database_role,open_mode,flashback_on,current_scn from v$database;

SQL> select database_role,open_mode,flashback_on,current_scn from v$database;

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON       CURRENT_SCN

---------------- -------------------- ------------------ -----------

PRIMARY          READ WRITE           YES                   15808929

SQL> alter database flashback off;

Database altered.

SQL> select database_role,open_mode,flashback_on,current_scn from v$database;

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON       CURRENT_SCN

---------------- -------------------- ------------------ -----------

PRIMARY          READ WRITE           NO                    15808981

总结:
--oracle总是在不经意间改进oracle数据库.看来11G越来越好用了.
--这样flashback database更加实用,可以在需要的时候打开,而不需要的时候关闭.