eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 如何通过DB link进行远程过程或函数调用 | Blog首页 | 赛门铁克推出数据库安全产品SDSA 3100 »

DB link与检查点(checkpoint)和SCN

昨天在测试的时候发现了一个有趣的现象,就是通过DB查询的2个数据库间,SCN会被同步。

测试的过程如下.
1.首先获得本地的SCN

[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 7 21:07:56 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual;
SCN
----------
5287824

2.通过db link进行SCN查询

SQL> set serveroutput on
SQL> set feedback off
SQL> declare
  2  r_gname    varchar2(40);
  3  l_gname    varchar2(40);
  4  begin
  5     execute immediate
  6     'select GLOBAL_NAME from global_name@hsbill' into r_gname;
  7     dbms_output.put_line('gname of remote:'||r_gname);
  8     select GLOBAL_NAME into l_gname from global_name;
  9     dbms_output.put_line('gname of locald:'||l_gname);
 10  end;
 11  /
gname of remote:HSBILL.HURRAY.COM.CN
gname of locald:EYGLE

SQL> declare
  2  r_scn      number;
  3  l_scn      number;
  4  begin
  5     execute immediate
  6     'select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER@hsbill from dual' into r_scn;
  7     dbms_output.put_line('scn of remote:'||r_scn);
  8     select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER into l_scn from dual;
  9     dbms_output.put_line('scn of locald:'||l_scn);
 10  end;
 11  /
scn of remote:18992092078
scn of locald:18992092078

我们可以看到,通过DB Link查询后,两个数据库的SCN被同步。
手工执行checkpoint,此时可以发现数据库的checkpoint scn被增进:

SQL> col scn for 999999999
SQL> select file#,CHECKPOINT_CHANGE# scn from v$datafile;


     FILE#               SCN
---------- -----------------
         1           5287605
         2           5287605
         3           5287605
         4           5287605


SQL> alter system checkpoint;


System altered.


SQL> select file#,CHECKPOINT_CHANGE# scn from v$datafile;


     FILE#               SCN
---------- -----------------
         1       18992092162
         2       18992092162
         3       18992092162
         4       18992092162

这种机制其实是为了满足分布式事务(Distributed Transaction)的需要,只不过这里通过db link被触发。

-The End-


历史上的今天...
    >> 2015-11-08文章:
    >> 2011-11-08文章:
    >> 2009-11-08文章:
    >> 2007-11-08文章:
    >> 2005-11-08文章:
           离开UT
    >> 2004-11-08文章:
           滚滚红尘
           月光-王心凌
           Rattle and Burn
           九月的高跟鞋
           Lemon Tree(柠檬树)
           纯真年代
           推荐第一首歌:同步过冬
           上海滩

无觅

By eygle on 2006-11-08 11:48 | Comments (0) | Internal | 960 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com