flashback (2)

2.使用Flashback database功能

a.背景知识
启动Flashback Database之后，oracle定期将已发生变化的块写入闪回日志的日志文件中。这些日志不是由传统的Log Writer (LGWR) 过程写入，而是由一种称作Recovery Writer (RVWR)的新过程写入。这是Oracle10g的新增进程，如下：
# ps -ef|grep rvwr|grep -v grep
  oracle  6028     1   0 16:42:48 ?           0:00 ora_rvwr_sjh10g

 

与常规的重做日志（redo logs）不同，回闪日志既不需要由DBA创建，也不需要由他们维护；它们由Oracle Managed Files（OMF）自动在闪回恢复区域所指定的目录中创建。这些文件不会归档，所以，如果在该目录发生介质故障后就不可能再进行恢复。 
# pwd
/oracle/product/10.2.0/db_1/flash_recovery_area/SJH10G/flashback

# ls
o1_mf_4wmvn7f0_.flb   --闪回区日志。

 

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

 

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

 

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
             1383302 2009-03-25 16:42:48

 

--通过以上SQL我们可以知道，可以前滚恢复到的最早的SCN与时间点是多少，如果没有确保flashback database，该视图将没有查询结果。

 

b.创建测试数据

SQL> select sysdate from dual;

                   

SYSDATE
-------------------
2009-03-25 17:01:55

 

SQL> create table t as select * from dba_users;

Table created.

 

SQL> select count(*) from dba_users;

  COUNT(*)
----------
        22

 

SQL> select sysdate from dual;

SYSDATE
-------------------
2009-03-25 17:03:15

 

SQL> create table t as select * from dba_users;

Table created.

 

SQL> select sysdate from dual;

  

SYSDATE
-------------------
2009-03-25 17:03:55

 

c.进行闪回操作

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes
Database mounted.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

 

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
             1383302 2009-03-25 16:42:48

 

SQL> flashback database to timestamp to_timestamp('2009-03-25 17:01:55','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

 

SQL> alter database open read only;

Database altered.

 

SQL> desc t;

ERROR:
ORA-04043: object t does not exist

 

--显然在2009-03-25 17:01:55这个时间点sjh0还没有创建。所以对象不存在。

 

SQL> alter database close;

Database altered.

 

SQL> flashback database to timestamp to_timestamp('2009-03-25 17:03:18','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

 

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

SQL> alter database open read only;

alter database open read only
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

SQL> alter database dismount;

Database altered.

 

SQL> startup mount;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size             104860528 bytes
Database Buffers           58720256 bytes
Redo Buffers                2912256 bytes

Database mounted.

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

 

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

 

SQL> desc t
ERROR:
ORA-04043: object t does not exist

 

--恢复的时间点选在t创建之前。一旦resetlogs之后，将不能再flashback的resetlogs之前的时间点。