ITPub博客

首页 > 数据库 > Oracle > [20150917]恢复使用scn比time更好.txt

[20150917]恢复使用scn比time更好.txt

Oracle 作者:lfree 时间:2015-09-17 11:48:25 0 删除 编辑

[20150917]恢复使用scn比time更好.txt

--oracle 提供一个函数SCN_TO_TIMESTAMP将scn转换成时间,但是这个存在一个精度问题,误差大约是3秒.
--转换实际上通过sys.SMON_SCN_TIME表.

--正是这样误差,一些恢复或者回滚到特定的时间点,使用scn更加准确.通过例子来说明问题.

1.检查测试环境:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

CREATE TABLE t (id number, update_scn number, commit_scn number,ins_date date);

BEGIN
  FOR i IN 1..9
  LOOP
   INSERT INTO t VALUES(i, dbms_flashback.get_system_change_number,userenv('commitscn'),sysdate);
   dbms_lock.sleep(1);
   COMMIT;
  END LOOP;
END;
/

--关于userenv('commitscn')可以参考我以前的链接:http://blog.itpub.net/267265/viewspace-1787037/
--[20150828]插入commit scn到记录.txt

SCOTT@test> SELECT ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) c30, t.* FROM t;
ORA_ROWSCN C30                                     ID  UPDATE_SCN  COMMIT_SCN INS_DATE
----------- ------------------------------ ----------- ----------- ----------- -------------------
13201900710 2015-09-17 11:34:14.000000000            1 13201900686 13201900692 2015-09-17 11:34:08
13201900710 2015-09-17 11:34:14.000000000            2 13201900694 13201900694 2015-09-17 11:34:09
13201900710 2015-09-17 11:34:14.000000000            3 13201900696 13201900696 2015-09-17 11:34:10
13201900710 2015-09-17 11:34:14.000000000            4 13201900698 13201900698 2015-09-17 11:34:11
13201900710 2015-09-17 11:34:14.000000000            5 13201900700 13201900700 2015-09-17 11:34:12
13201900710 2015-09-17 11:34:14.000000000            6 13201900702 13201900703 2015-09-17 11:34:13
13201900710 2015-09-17 11:34:14.000000000            7 13201900705 13201900705 2015-09-17 11:34:14
13201900710 2015-09-17 11:34:14.000000000            8 13201900707 13201900707 2015-09-17 11:34:15
13201900710 2015-09-17 11:34:14.000000000            9 13201900709 13201900709 2015-09-17 11:34:17
9 rows selected.

--因为插入到1个数据块里面ORA_ROWSCN是最后的提交scn.应该改写如下:

SCOTT@test> SELECT SCN_TO_TIMESTAMP(commit_SCN) c30, t.* FROM t;
C30                                     ID  UPDATE_SCN  COMMIT_SCN INS_DATE
------------------------------ ----------- ----------- ----------- -------------------
2015-09-17 11:34:08.000000000            1 13201900686 13201900692 2015-09-17 11:34:08
2015-09-17 11:34:08.000000000            2 13201900694 13201900694 2015-09-17 11:34:09
2015-09-17 11:34:08.000000000            3 13201900696 13201900696 2015-09-17 11:34:10
2015-09-17 11:34:11.000000000            4 13201900698 13201900698 2015-09-17 11:34:11
2015-09-17 11:34:11.000000000            5 13201900700 13201900700 2015-09-17 11:34:12
2015-09-17 11:34:11.000000000            6 13201900702 13201900703 2015-09-17 11:34:13
2015-09-17 11:34:14.000000000            7 13201900705 13201900705 2015-09-17 11:34:14
2015-09-17 11:34:14.000000000            8 13201900707 13201900707 2015-09-17 11:34:15
2015-09-17 11:34:14.000000000            9 13201900709 13201900709 2015-09-17 11:34:17
9 rows selected.

--注意看SCN_TO_TIMESTAMP(commit_SCN)与后面的ins_date,前面3个一组,与后面的存在误差.

SCOTT@test> select * from t as of timestamp to_date('2015-09-17 11:34:08','yyyy-mm-dd hh24:mi:ss');
no rows selected

SCOTT@test> select * from t as of timestamp to_date('2015-09-17 11:34:09','yyyy-mm-dd hh24:mi:ss');
no rows selected

SCOTT@test> select * from t as of timestamp to_date('2015-09-17 11:34:10','yyyy-mm-dd hh24:mi:ss');
no rows selected

SCOTT@test> select * from t as of timestamp to_date('2015-09-17 11:34:11','yyyy-mm-dd hh24:mi:ss');
         ID  UPDATE_SCN  COMMIT_SCN INS_DATE
----------- ----------- ----------- -------------------
          1 13201900686 13201900692 2015-09-17 11:34:08
          2 13201900694 13201900694 2015-09-17 11:34:09
          3 13201900696 13201900696 2015-09-17 11:34:10

--可以发现11:34:08插入第1条数据,理论讲11:34:09已经提交,至少11:34:10应该能看到第1条记录,而实际上执行:
--select * from t as of timestamp to_date('2015-09-17 11:34:11','yyyy-mm-dd hh24:mi:ss');才看到记录.

SCOTT@test> select * from t as of scn 13201900692;
no rows selected

SCOTT@test> select * from t as of scn 13201900693;
         ID  UPDATE_SCN  COMMIT_SCN INS_DATE
----------- ----------- ----------- -------------------
          1 13201900686 13201900692 2015-09-17 11:34:08

SCOTT@test> select * from t as of scn 13201900694;
         ID  UPDATE_SCN  COMMIT_SCN INS_DATE
----------- ----------- ----------- -------------------
          1 13201900686 13201900692 2015-09-17 11:34:08

SCOTT@test> select * from t as of scn 13201900695;
         ID  UPDATE_SCN  COMMIT_SCN INS_DATE
----------- ----------- ----------- -------------------
          1 13201900686 13201900692 2015-09-17 11:34:08
          2 13201900694 13201900694 2015-09-17 11:34:09

--可以发现scn可以很精确的定位相关记录.从这些测试可以发现在一些恢复回滚到特定的时间点,选择scn更加好更加准确.

--当然如果如果你选择恢复until time 不存在这个问题.下午我重新测试,在dg上打开flashback.

--删除表,重新插入:
--主数据库:
SCOTT@test> select * from t;
         ID  UPDATE_SCN  COMMIT_SCN INS_DATE
----------- ----------- ----------- -------------------
          1 13201922719 13201922723 2015-09-17 15:37:10
          2 13201922725 13201922725 2015-09-17 15:37:11
          3 13201922727 13201922736 2015-09-17 15:37:12
          4 13201922738 13201922739 2015-09-17 15:37:13
          5 13201922741 13201922741 2015-09-17 15:37:14
          6 13201922743 13201922743 2015-09-17 15:37:15
          7 13201922745 13201922745 2015-09-17 15:37:16
          8 13201922747 13201922747 2015-09-17 15:37:17
          9 13201922749 13201922749 2015-09-17 15:37:18
9 rows selected.

SCOTT@test> select * from t as of timestamp to_date('2015-09-17 15:37:13','yyyy-mm-dd hh24:mi:ss');
         ID  UPDATE_SCN  COMMIT_SCN INS_DATE
----------- ----------- ----------- -------------------
          1 13201922719 13201922723 2015-09-17 15:37:10
          2 13201922725 13201922725 2015-09-17 15:37:11

--在dg上flashback:

SYS@testdg> flashback database to timestamp  to_date('2015-09-17 15:37:13','yyyy-mm-dd hh24:mi:ss');
Flashback complete.

SYS@testdg> alter database open read only ;
Database altered.

SYS@testdg> select * from scott.t;
          ID   UPDATE_SCN   COMMIT_SCN INS_DATE
------------ ------------ ------------ -------------------
           1  13201922719  13201922723 2015-09-17 15:37:10
           2  13201922725  13201922725 2015-09-17 15:37:11
           3  13201922727  13201922736 2015-09-17 15:37:12

--说明不存在这个问题,但是使用scn将跟准确一些.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1802023/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292092