使用 ora_rowscn函数进行闪回恢复

最近遇到过这样一个案例，用户因为程序Bug误操作更新了一条记录，并且提交，请求恢复。在Oracle 10g中，如果响应及时，这样的恢复将非常容易。

首先登录数据库，检查故障表，发现被更新的实际上是LONG类型数据：

[oracle@order ~]$ sqlplus wapuser/wapuser

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Feb 5 13:17:37 2007
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> desc CMS_WEB_EXT 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WEB_ID                                    NOT NULL NUMBER
 CUSTOM                                             LONG

用户说更新可能发生在11:45左右，可以通过ORA_ROWSCN来获得这个表中最近更新的记录信息：

SQL> select web_id,ora_rowscn from cms_web_ext where web_id=13733;
    WEB_ID ORA_ROWSCN
---------- ----------
     13733   25945019

SQL> select web_id,ora_rowscn,scn_to_timestamp(ora_rowscn) from cms_web_ext where web_id=13733;
    WEB_ID ORA_ROWSCN          SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
     13733   25945019          05-FEB-07 11.56.21.000000000 AM

通过查询可以得到，最近更新的一条记录事在11:56，而其他更新都远远早于这条记录，经过确认，就是WEB_ID为13733的记录被错误更新：

SQL> COL timestamp for a40
SQL> SELECT web_id, ora_rowscn, DBMS_ROWID.rowid_block_number (ROWID) blockno,
  2         scn_to_timestamp (ora_rowscn) TIMESTAMP
  3    FROM cms_web_ext
  4   WHERE web_id IN (SELECT *
  5                      FROM (SELECT   web_id
  6                                FROM cms_web_ext
  7                            ORDER BY ora_rowscn DESC)
  8                     WHERE ROWNUM < 3);
    WEB_ID ORA_ROWSCN    BLOCKNO TIMESTAMP
---------- ---------- ---------- ----------------------------------------
     13733   25945019      99489 05-FEB-07 11.56.21.000000000 AM
     13544   25760050      99495 03-FEB-07 03.37.00.000000000 PM

那么现在可以通过闪回查询恢复这条记录，由于这个表包含LONG类型数据，所以采用EXP/IMP方式进行恢复，首先导出这条记录：

[oracle@order ~]$ exp wapuser/wapuser file=ext0702051156.dmp tables=cms_web_ext FLASHBACK_SCN=25945000 query=\"where web_id=13733\"

Export: Release 10.2.0.2.0 - Production on Mon Feb 5 13:29:23 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                    CMS_WEB_EXT          1 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

然后在数据库中删除这条错误记录：

SQL> show user
USER is "WAPUSER"
SQL> delete from cms_web_ext where web_id=13733;
1 row deleted.

SQL> commit;
Commit complete.

最后导入这条记录完成恢复：

[oracle@order ~]$ imp wapuser/wapuser file=ext0702051156.dmp ignore=y

Import: Release 10.2.0.2.0 - Production on Mon Feb 5 13:31:33 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing WAPUSER's objects into WAPUSER
. importing WAPUSER's objects into WAPUSER
. . importing table                  "CMS_WEB_EXT"          1 rows imported
Import terminated successfully without warnings.

由于有了这些新函数的辅助，很多复杂情况下的恢复就有了更多的选择。