使用Oracle10g Flashback database功能恢复用户错误

« 如何启用Oracle10g闪回数据库特性 | Blog首页 | 使用Oracle10g提供的flashback drop 新特性 »

Flashback Database是Oracle10g的新增功能，在启动Flashback Database之后，它定期将已发生变化的块写入闪回日志的日志文件中。这些日志不是由传统的Log Writer (LGWR) 过程写入，而是由一种称作Recovery Writer (RVWR)的新过程写入。这是Oracle10g的新增进程。



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

与常规的重做日志（redo logs）不同，回闪日志既不需要由DBA创建，也不需要由他们维护；它们由Oracle 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');

Flashback 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');

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

历史上的今天...
>> 2013-03-29文章:

不以规矩不成方圆-《Oracle 数据库设计规范》序

>> 2011-03-29文章:

Oracle Clusterware的起停和ocr.loc文件

Oracle与HP的恩怨 - 放弃支持Itanium处理器

>> 2007-03-29文章:

光纤存储、SUN遭遇莫名故障

By eygle on 2005-03-29 17:35 | Comments (8) | Backup&Recovery | 227 |

8 Comments

novenbersky | March 27, 2007 9:08 PM

我有一个疑问，是不是只有在所要求闪回的时间点在flashback logs所记录的最早的信息的时间点之前时，才会使用归档日志？
它在使用归档日志时是不是相当于执行了一系列与recover过程逆向的数据块操作？
请大师释疑，谢谢！

eygle | March 28, 2007 8:45 AM

闪回日志和归档日志是两码事
flashback database不会使用redo log.
redo log是重演事务的，需要从一个历史时间点上开始数据库的恢复才行。

novenbersky | March 28, 2007 9:31 AM

但是我在10G的官方文档《Backup and Recovery Basics》第104页中看到
Flashback Database is, however,
much faster than point-in-time recovery, because it does not require restoring datafiles
from backup and it requires applying fewer changes from the archived redo logs.
它不是说要从归档日志获取信息么？

eygle | March 28, 2007 9:45 PM

这个需要进行一点测试验证一下，最近太忙，明天测一下就有答案了。

novenbersky | March 29, 2007 7:52 AM

我昨天简单试了一下，我原来的猜测有错误，闪回到flashback log记录以前的时间点应该是不行的。
不过假如文档中说得是对的话，我不明白既然已经有了flashback logs为什么还需要归档日志？我不太懂如何对这个问题进行测试，期待大师的结果。

Airlian | April 24, 2007 10:21 AM

flashback database 技術只是「就地還原」datafile 的一種方法。所以有多少 flashback log 就能回復到多久之前。預設是一天。還原之後緊接著進行 recover database 的動作到指定的時間點進行資料庫的不完全回復。

這也就是"說"/"為什麼"：
1.開啟 flashback database 功能的資料庫一定是在 archivelog mode。
2.以 alter database open resetlogs; 方式開啟。

superin | April 21, 2008 8:10 PM

To novenbersky,个人理解是：因为被修改的数据块不是实时写入flashback log的，而是定期写入的。这样的话就很有可能在你需要回到的时间点上有一部分改变没有被记录在flashback log中（夹在两次写中间了），所以需要redo log来帮助恢复。猜想flashback过程如下：先用flashback log中的数据块恢复到最接近用户设定的点，然后apply redo log到精确的用户设定的点。

ernest | October 15, 2009 2:34 PM

it requires applying fewer changes from the archived redo logs.
这句话的意思是使用flashback log 和使用archived redo logs比起来，对数据库所作的修改要来得少。
不是说flashback database的时候要用到redo log