分享程序员开发的那些事...
更新时间：2022-09-12 13:10:58
[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将跟准确一些.