ORA-02063,ORA-01555

有一个定时脚本任务，目的是每天凌晨，通过dblink方式，把远程的一些表数据，以insert .... select ...的方式同步到本地库里面，
部署完发现，连续两天，第一个insert都会失败，而从第二个insert开始，都没有问题

SQL> SQL>       insert into p_category select * from p_category@ofdqs_to_nc
                                     *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 14 with name
"_SYSSMU14_2546509538$" too small
ORA-02063: preceding line from OFDQS_TO_NC

  p_category这个表数据量并不大，只有200多条数据，不到10个字段，也没有clob等大字段，
确定这个错误并不是真的是undo段空间导致

查询MOS发现一片文章：
ORA-1555 When Using Active Data Guard in Distributed Queries (文档 ID 1433442.1)

文档描述完全符合我这边的情况

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

SYMPTOMS

Running a distributed query from 11.2.0.2.0 active Data Guard 
database and accessing a 10.2.0.4 database causes following error:

ERROR at line 5:
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$"
too small
ORA-02063: preceding line from XXXX.YYYY.NET

CAUSE

Per bug 13782013, this is not a product defect.

When using active DG in distributed queries, it is likely to get ORA-1555.
This is not a product defect.

An ADG standby cannot adjust/Lamport its scn when it is involved in
distributed queries. Thus if the remote database is at scn 1000 while the ADG
is at 100, it is not possible for the ADG to adjust its scn to 1000 and then
execute the remote query at say 1000 or higher. Therefore, the remote query
will be sent scn 100 as the execution scn and since this is very old for the
remote database you may run into ORA-1555.

SOLUTION

Changes to the Data Guard configuration can help to minimize the error. 
For additional information on SCN gaps between the two databases.  Please review the following:

Oracle? Database Administrator's Guide
11g Release 2 (11.2)
Part Number E25494-02
Managing Read Consistency

There are two workarounds for this behavior listed in the documentation:

Because SCNs are synchronized at the end of a remote query, precede each
remote query with a dummy remote query to the same site, for example, SELECT
* FROM DUAL@REMOTE.

Because SCNs are synchronized at the start of every remote transaction,
commit or roll back the current transaction before issuing the remote query.
SCNs are synchronized during every distributed operation, even if it is just
a query. This could explain why one set of databases does not have this
issue; if there is heavier traffic between them, then the the SCN gap will
not be as large.

Please open a new service request for  Data Guard team (Product:5,  Component: DATAGUARD,
Sub Component: CONFIGPHY) if further assistance is needed with  Data Guard configuration verification and modification.

解决：
可以在第一个insert前增加一个select语句
select * from dual@ofdqs_to_nc;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1411751/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1411751/