SQL> create public database link TEST connect to system identified by oracle using 'abmsrv1';

Database link created.

SQL> select count(*) from dba_users@TEST;

select count(*) from dba_users@TEST

*

ERROR at line 1:

ORA-19706: invalid SCN

通过dblink访问的时候，两个数据库会做scn号的同步，SCN Headroom的结果必须要比参数_external_scn_rejection_threshold_hours的值大才行，检查现在数据库该值为744即31天，而对端数据库的SCN Headroom值为24.85

把scnhealthcheck.sql脚本里面的VERBOSE改成TRUE就可以显示SCN Headroom值了，检查结果如下：

SQL> @scn.sql

--------------------------------------------------------------

ScnHealthCheck

--------------------------------------------------------------

Current Date: 2012/03/19 16:29:59

Current SCN: 12717326143484

SCN Headroom: 24.85

Version: 11.2.0.2.0

--------------------------------------------------------------

Result: B - SCN Headroom is low

If you have not already done so apply

the latest recommended patches right now

For further information review MOS document id 1393363.1

查看该隐含参数：

SQL> set linesize 160 pages 1000;

SQL> col "_add_skiprules" format a30 ;

SQL> col "Instance Value" format a30;

SQL> col "Session Value" format a30;

SQL> col Parameter format a50;

SQL> SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value",c.ksppstvl "Instance Value"

FROM x$ksppi a,x$ksppcv b,x$ksppsv c

WHERE a.indx = b.indx AND a.indx = c.indx

AND

a.ksppinm LIKE '/_external%' escape '/';

Parameter

--------------------------------------------------------------------------------

Session Value

------------------------------------------------------------------------------------------------------------------------------------

Instance Value

------------------------------------------------------------------------------------------------------------------------------------

_external_scn_rejection_threshold_hours

744

值为31天。

The "scnhealthcheck.sql" script. can be downloaded here: Patch:13498243.

In addition to the above result the script. output may advise to set the hidden parameter "_external_scn_rejection_threshold_hours" on some Oracle versions. The following text gives more information about setting this parameter:

Set _external_scn_rejection_threshold_hours=24 after apply

The hidden parameter "_external_scn_rejection_threshold_hours" is introduced in January 2012 Critical Patch Update (CPU) and Patch Set Update (PSU) releases (and related bundles). Oracle recommends setting this parameter to the value 24 in 10g and 11.1 releases - it does not need to be set in 11.2 releases. The parameter is static and so must be set in the init.ora or spfile used to start the instance.

eg:

In init.ora:

# Set threshold on dd/mon/yyyy - See MOS Document 1393363.1

_external_scn_rejection_threshold_hours = 24

In the spfile:

alter system set "_external_scn_rejection_threshold_hours" = 24

comment='Set threshold on dd/mon/yyyy - See MOS Document 1393363.1'

scope=spfile ;

Why do I need to set "_external_scn_rejection_threshold_hours"=24 ?

Oracle has many hidden parameters which all have default or derived values, and those parameters are not generally intended to be set by customers. Oracle determined that the most suitable value for this new hidden parameter is "24" and that this value should be used across all releases. 10g and 11.1 January 2012 CPU / PSU releases have a different default value compiled in and so this setting has to be made explicitly on those releases to ensure that the required value of 24 is used. Customers are not expected to tune this value themselves.

修改参数，重启数据库，恢复正常。

ITPub博客

ORA-19706: invalid SCN

最新文章