flashback (2)

jonelaw

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   --闪回区日志。

 

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

Session altered.

 

1. 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.创建测试数据

1. SQL> select sysdate from dual;

                   

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

 

1. SQL> create table t as select * from dba_users;

Table created.

 

1. SQL> select count(*) from dba_users;

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

 

1. SQL> select sysdate from dual;

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

 

1. SQL> create table t as select * from dba_users;

Table created.

 

1. SQL> select sysdate from dual;

  

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

 

c.进行闪回操作

1. SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

1. 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.

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

Session altered.

 

1. 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

 

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

Flashback complete.

 

1. SQL> alter database open read only;

Database altered.

 

1. SQL> desc t;

ERROR:
ORA-04043: object t does not exist

 

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

 

1. SQL> alter database close;

Database altered.

 

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

Flashback complete.

 

1. SQL> alter database open;

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

1. SQL> alter database open read only;

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

1. SQL> alter database dismount;

Database altered.

 

1. SQL> startup mount;

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

1. SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

1. 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.

1. SQL> alter database open;

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

1. 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之前的时间点。