oracle中dblink怎么用,Oracle 使用DBLINK 注意事项

Oracle

数据库通过跨库查询数据库，我们使用DBLINK

的时候，会触发两套库的SCN

号发生传播，导致源端和对端的SCN

向最高的SCN

的节点看齐。在SCN

传播过程种会发生SCN

号小的节点剩余SCN

耗尽或者告警。

该文档主要描述，我们SCN

号剩余量底或者耗尽的报错信息，对数据库的影响及风险的评估。

本文档主要内容如下：

nOracle rdbms

数据库软件的版本

n

数据库使用dblink

注意事项

相关问题修复建议

建议在

2019

年

6

月之前将

Oracle

数据库版本

12.1.0.1

、

11.2.0.3

、

11.1.0.7

、

10.2.0.5

和

10.2.0.4

补丁到下面提到的

patchset/PSU

级别，以解决未来

dblinks

互操作性方面的潜在问题。如果您运行的是数据库版本

/

版本

12.2

、

12.1.0.2

或

11.2.0.4

，则无需操作。如果您仍然在使用

10.2.0.3

或更早的版本，并且在以后的数据库版本中使用

dblinks

，那么这个注意也适用于该场景。

Patch 23054354 (Prerequisite Patch 12.1.0.1.160719 PSU) + Patch 14121009

DATABASE PATCH SET UPDATE 11.2.0.3.9 (INCLUDES CPUJAN2014) - This contains Basic fix.

DATABASE PATCH SET UPDATE 11.1.0.7.20 (INCLUDES CPUJUL2014)   Patch 18522513

Patch 17082367 (Prerequisite Patch 11.2.0.2.12 PSU) + Patch 14121009

Patch 26493118 (10.2.0.5.171017 This requires password to download, please raise SR with Oracle support to get the password) + Patch 14121009

ORACLE 10G 10.2.0.5 PATCH 170718 BUG FOR WINDOWS 64-BIT   Prerequisite Patch 26401242 + Patch 14121009

DBLINK

导致的错误信息

ORA-19706: invalid SCN

ORA-19706: invalid SCN

ORA-02063: preceding line from REMDB

内容：

1

)Warning - High Database SCN: Current SCN value is 0x0b7b.0008e40b, threshold SCN value is 0x0b75.055dc000

If you have not previously reported this warning on this database, please notify Oracle Support so that additional diagnosis can be performed.

2

)Warning: The SCN headroom for this database is only NN days!

3

)Warning: The SCN headroom for this database is only N hours!

4

)WARNING: This patch can not take full effect until this RAC database has been completely shutdown and restarted again.

Oracle recommends that it is done at the earliest convenience.

5

)Rejected the attempt to advance SCN over limit by 9374 hours worth to 0x0c00.00000f66, by distributed transaction remote logon, remote DB: REMDB.XX.ORACLE.COM.

Client info : DB logon user ME, machine yy, program sqlplus@yy (TNS V1-V3), and OS user uuu

6

)Rejected the attempt to advance SCN over limit by 9375 hours worth to 0x0c00.000003c6, by distributed transaction logon, remote DB: REMDB.XX.ORACLE.COM.

Client info : DB logon user TC, machine xx, program oracle@xx (TNS V1-V3), and OS user xxx

7

)Rejected the attempt to advance SCN over limit by 9374 hours worth to 0x0c00.00000f66, by XXXXX

Client info : DB logon user TC, machine mmm, program sqlplus@mmm (TNS V1-V3), and OS user uuu

Where XXXXX is a string such as:

? PL/SQL RPC (remote)

? sql exec with curscn

? sql exec with outscn

及以上会有如下信息：

Warning: The SCN intrinsic growth rate has been consistently

higher than system default 16384 per sec. for last 60 mins.

Current SCN intrinsic growth rate is 24416 per sec., zas 7fffff!

号导致数据库状态

如果数据库告警将要在多少天使用完成，那么当前数据库是可以正常打开和使用的

如果数据库在打开时报数据库SCN

用完，那么数据库就无法打开！

DBLINK

导致SCN

问题

查询信息

--      Currently no exceptions are thrown.

--      rsl             - Reasonable SCN Limit as of 'now'

--      headroom_in_scn - Difference between current SCN and RSL

--      headroom_in_sec - number of seconds it would take to reach RSL

--                        assuming a constant SCN consumption rate associated

--                        with current SCN compatibility level

--      cur_scn_compat  - current value of SCN compatibility

--      max_scn_compat  - max value of SCN compatibility this database

--                        understands

set serveroutput on ;

declare

rsl number;

headroom_in_scn number;

headroom_in_sec number;

cur_scn_compat number;

max_scn_compat number;

begin

dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);

dbms_output.put_line('rsl=' || rsl);

dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);

dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);

dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);

dbms_output.put_line('max_scn_compat=' || max_scn_compat);

end;

/

rsl             - Reasonable SCN Limit as of 'now'

a

库:rsl   b

库:rsl

a

库访问b

库: b

库:rsl > a

库:rsl

可以使用

b

库:rsl < a

库:rsl

数据库无法开启，可以开启实际预估： ( a

库:rsl - b

库:rsl )/

增长率(16K

)/60/60 min