[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更加實用,可以在需要的時候開啟,而不需要的時候關閉.