oracle 检查点与scn关系,DB link与检查点(checkpoint)和SCN

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-

By eygle on 2006-11-08 11:48 |

Comments (0) |

Internal | 960 |