用Flashback database功能恢复用户错误

10g中有一个新增得功能back Database，当你启动它以后，它会定期将已发生变化的块写入到闪回日志的日志文件中。请注意这些日志不是由传统的Log Writer （LGWR） 过程写入，而是由一种称作Recovery Writer的新过程来写入。

$ ps -ef|grep rvwr|grep -v grep
oracle 27231     1  0 17:10:30 ?        0:00 ora_rvwr_eygle

　　注释：与常规的重做日志有所不同，回闪日志既不需要DBA创建，也不需要DBA来维护；它们由 Managed Files（OMF）自动在闪回恢复区域所指定的目录中创建。请注意这些文件不会归档，所以，如果在该目录发生介质故障后就不可能再进行恢复了。

　　（1）首先启用闪回数据库特性

SYS AS SYSDBA on 2005-03-29 16:42:19 >startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.

SYS AS SYSDBA on 29-MAR-05 >alter database archivelog;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >alter database flashback on;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >alter database open;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >alter session set nls_date_format=
'yyyy-mm-dd hh24:mi:ss';


Session altered.


SYS AS SYSDBA on 2005-03-29 17:01:42 >SELECT OLDEST_FLASHBACK_SCN,
                                        OLDEST_FLASHBACK_TIME
  2  FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
            10642627 2005-03-29 17:01:02

　　（2）闪回日志

　　注释：Oracle会在闪回区来记录日志。

$ pwd
/data5/flash_recovery_area/EYGLE/flashback
$ ls -l
total 32064
-rw-r-----   1 oracle   dba      8200192 Mar 29 16:49 o1_mf_14l5bclp_.flb
-rw-r-----   1 oracle   dba      8200192 Mar 29 17:31 o1_mf_14l6w5h4_.flb

　　（3）然后模拟用户错误

SYS AS SYSDBA on 2005-03-29 17:01:44 >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
SYS AS SYSDBA on 2005-03-29 17:01:55 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:02:09

SYS AS SYSDBA on 2005-03-29 17:02:16 >drop table t3;

Table dropped.

SYS AS SYSDBA on 2005-03-29 17:02:22 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:02:28

SYS AS SYSDBA on 2005-03-29 17:02:28 >create table t1 as select * 
from dba_users;

Table created.


SYS AS SYSDBA on 2005-03-29 17:02:42 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:02:48

SYS AS SYSDBA on 2005-03-29 17:02:48 >create table t2 as select * 
 from dba_tablespaces;

Table created.

SYS AS SYSDBA on 2005-03-29 17:03:01 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:03:06

SYS AS SYSDBA on 2005-03-29 17:03:06 >select sysdate from dual;

SYSDATE
-------------------
2005-03-29 17:03:17

 

（4）下面我们来进行闪回操作

SYS AS SYSDBA on 2005-03-29 17:03:43 >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA on 2005-03-29 17:04:04 >startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.

SYS AS SYSDBA on 29-MAR-05 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SYS AS SYSDBA on 2005-03-29 17:05:31 >select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ----------------
            10642627 2005-03-29 17:01:02             1440        8192000                        0


SYS AS SYSDBA on 2005-03-29 17:06:38 >flashback database to timestamp
  2  to_timestamp ('2005-03-29 17:02:28','yyyy-mm-dd hh24:mi:ss');

back complete.

　　（5）只读打开，验证数据

SYS AS SYSDBA on 2005-03-29 17:06:58 >alter database open read only;

Database altered.

SYS AS SYSDBA on 2005-03-29 17:07:07 >desc t3    
ERROR:
ORA-04043: object t3 does not exist


SYS AS SYSDBA on 2005-03-29 17:07:39 >desc t1
ERROR:
ORA-04043: object t1 does not exist

　　（6）最后继续修正恢复

SYS AS SYSDBA on 2005-03-29 17:07:42 >alter database close;

Database altered.

SYS AS SYSDBA on 2005-03-29 17:07:49 >flashback database to timestamp
  2  to_timestamp ('2005-03-29 17:02:48','yyyy-mm-dd hh24:mi:ss');

back complete.

SYS AS SYSDBA on 2005-03-29 17:08:17 >alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed


SYS AS SYSDBA on 2005-03-29 17:08:24 >alter database dismount;

Database altered.

SYS AS SYSDBA on 2005-03-29 17:08:35 >shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SYS AS SYSDBA on 2005-03-29 17:08:48 >startup mount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.
SYS AS SYSDBA on 29-MAR-05 >alter database open read only;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >select count(*) from t1;

  COUNT(*)
----------
        12

　　（7）resetlogs打开数据库

SYS AS SYSDBA on 29-MAR-05 >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA on 29-MAR-05 >startup mount
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1301704 bytes
Variable Size             261890872 bytes
Database Buffers           50331648 bytes
Redo Buffers                1048576 bytes
Database mounted.

SYS AS SYSDBA on 29-MAR-05 >alter database open resetlogs;

Database altered.

SYS AS SYSDBA on 29-MAR-05 >

　　注释：resetlogs后，则不会再flashback到resetlogs之前的时间点。