Oracle10g New Feature -- 4. Flashback Database

Oracle的flashback database比起RMAN或者其他的不完全恢復，是簡單多了。

對資料庫的要求除了和其他不完全恢復一樣要求archive log模式外，還需要設定flashback on。

對於恢復truncate table的誤操作，目前好像只有回滾整個資料庫，沒有其他更簡單的辦法。

[@more@]

1.    Flashback Database

1.     Introduction

    Flashback Database is implemented using a new type of log file called the Flashback Database log. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

    A new RVWR background process writes Flashback Database data to the Flashback Database logs.

    For instance “grid”:

   $ ps -ef | grep grid

  oracle 25124     1  0 16:32:05 ?        0:00 ora_s000_grid
  oracle 25116     1  0 16:32:04 ?        0:00 ora_reco_grid
  oracle 25169     1  0 16:32:22 ?        0:00 ora_rvwr_grid
  oracle 25112     1  0 16:32:04 ?        0:00 ora_ckpt_grid

  …………

2.     Enabling Flashback Database

1.             Make sure the database is in archive mode.

SQL>archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     0

Next log sequence to archive   1

Current log sequence           1

2.             Configure the recovery area by setting the two parameters:

• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE

SQL>show parameter db_recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      C:oracleproduct10.1.0flash_recovery_area

db_recovery_file_dest_size           big integer 2G

3.             Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:

SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;

4.             Set the Flashback Database retention target:

• DB_FLASHBACK_RETENTION_TARGET

5.             Determine if Flashback Database is enabled

SQL> select flashback_on from  v$database;
   
    FLASHBACK_ON
    ------------
    YES

3.     Disabling Flashback Database

SQL> ALTER DATABASE FLASHBACK OFF;

4.     Monitoring Flashback Database

• Monitor logging in the Flashback Database logs:

SQL> select begin_time, flashback_data,
   2        db_data, redo_data, ESTIMATED_FLASHBACK_SIZE
   3 from   v$flashback_database_stat;

BEGIN_TIME           FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
-------------------- -------------- ---------- ---------- ------------------------
Feb 22 2004 01:05:14         147456    2719744      92160                        0
Feb 22 2004 00:05:09        3891200    5857280    2537984                252788736
Feb 21 2004 23:05:04        7979008   13615104    3385344                254877696

• Monitor the Flashback Database retention target:

SQL> select *
   2 from   v$flashback_database_log;

    OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE

ESTIMATED_FLASHBACK_SIZE
   -------------------- --------------------- ---------------- -------------- -----

-------------------
            2.2029E+12 Oct 06 2003 09:44:42              1440       48316416

21774336

• Adjust recovery area disk quota:

SQL> select estimated_flashback_size from   v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE
------------------------
                21823488

5.     Example

ZHYUH @ orcl>select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

TEST1                          TABLE

TEST2                          TABLE

ZHYUH @ orcl>select * from test1;

COL1

----------

dfa

ZHYUH @ orcl>select * from test2;

      COL1

----------

     34245

ZHYUH @ orcl>select to_char(sysdate,'mmddyyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'

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

09092004 11:26:21

ZHYUH @ orcl>truncate table test1;

Table truncated.

ZHYUH @ orcl>drop table test2 purge;

Table dropped.

ZHYUH @ orcl>select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

TEST1                          TABLE

ZHYUH @ orcl>select * from test1;

no rows selected

Now, we try to flashback to timestamp “09092004 11:26:21”

SQL>select OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'mmddyyyy hh24:mi:ss'), RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE

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

ESTIMATED_FLASHBACK_SIZE

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

              654057 09092004 10:41:38             1440        8192000

                       0

SYS @ orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS @ orcl>startup mount exclusive;

ORACLE instance started.

Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145750508 bytes

Database Buffers           25165824 bytes

Redo Buffers                 262144 bytes

Database mounted.

SYS @ orcl>flashback database to timestamp(to_date('09092004 11:26:21','mmddyyyy hh24:mi:ss'));

Flashback complete.

SYS @ orcl>alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS @ orcl>alter database open resetlogs;

Database altered.

Check the flashback result:

SYS @ orcl>connect zhyuh/zhyuh

Connected.

ZHYUH @ orcl>select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

TEST1                          TABLE

TEST2                          TABLE

ZHYUH @ orcl>select * from test1;

COL1

----------

dfa

ZHYUH @ orcl>select * from test2;

      COL1

----------

     34245

Recovery is successful!