在dg备库使用DB LINK跨库查询时遇到ORA-16000: database open for read-only access错误。

执行以下语句报错：
select * from userA.v_view1;

查看对象 v_view1：

PROMPT View v_view1;
--
-- v_view1  (View)
--

CREATE OR REPLACE FORCE VIEW userA.v_view1
(
   TIMESTAMP,
   COMPNAME,
   INNERBATCH,
   VENDER,
   AMOUNT
)
AS
   SELECT ta.timestamp,
          ta.compname,
          tb.innerbatch,
          tb.vender,
          ta.amount
     FROM tab1 ta
          LEFT JOIN tab2@db1 tb ON ta.compid = tb.mrn
    WHERE 1 = 1 --and timestamp >= TO_DATE(TO_CHAR(SYSDATE-1,'YYYYMMDD'),'YYYYMMDD')
             --and timestamp < TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD')
           AND ta.actionid IN (300, 301, 302)
   UNION ALL
   SELECT tc.insert_time,
          tc.compname,
          td.innerbatch,
          td.vender,
          tc.amount
     FROM tab3 tc
          LEFT JOIN tab2@db2 td ON tc.compid = td.mrn
    WHERE 1 = 1
--and insert_time >= TO_DATE(TO_CHAR(SYSDATE-1,'YYYYMMDD'),'YYYYMMDD')
--and insert_time < TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD')
;


Prompt 将 VIEW v_view1 TO BITEST 的权限授予 BITEST;
GRANT SELECT ON WMS.v_view1 TO BITEST;

分析：

这个视图里使用了两个DB LINK，使用DB LINK查询会创建一个事务，同时使用两个DB LINK就需要创建两个事务，这就需要支持分布式事务，新数据库是ADG的一个备库，并不支持分布式事务，就会上面的错误。

可以通过以下实验来验证。

打开一个会话，先使用dblinkA这个DB LINK查询，，这样第二个DB LINK的查询就会遇到ORA-16000错误。



    SQL> select 1 from dual@dblinkA;

             1
    ----------
             1

    SQL> select 1 from dual@dblinkB;
    select 1 from dual@dblinkB
                       *
    ERROR at line 1:
    ORA-16000: database open for read-only access

在第一个DB LINK打开的事务结束后，第二个DB LINK可以创建事务，也就可以使用了。
    SQL> select 1 from dual@dblinkA;

             1
    ----------
             1
    SQL> commit;

    Commit complete.

    SQL> select 1 from dual@dblinkB;

             1
    ----------
             1

    SQL>  
    

如视图或存储过程中使用了两个DBLINK就会报错，如类似如下报表查询想放在ADG上执行以减轻主库的性能压力，就会报错ORA-16000: database open for read-only access：

  CREATE OR REPLACE FORCE VIEW "test"."T_REPORT" (………) AS    select from test.test_REPORT@testzrzk a,mpi.test222@test222 b where a.pid=b.pid and a.TIME>to_char(sysdate-40,'YYYYMMDDHHMISS')
  或
select * from table@dblink1
union
select * from table@dblink2;

参考：

在相关MOS文档中也有对应测试案例，如

Query with multiple dblinks in ADG / read-only database fails with ORA-16000 (Doc ID 2462936.1)

In this Document
Symptoms
Cause
Solution
References

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.
SYMPTOMS
In an active Data Guard (ADG) database, execution of a select statement using multiple database links, for example:

select * from table@dblink1
union
select * from table@dblink2
union
select * from table@dblink3;

 

fails with the following error:

ORA-16000: database or pluggable database open for read-only access
*Cause: The database or pluggable database was opened for read-only access.
  Attempts to modify the database using DML or DDL statements generate this error.
*Action: In order to modify the database or pluggable database, it must first be shut down and reopened for read/write access.

CAUSE
This issue is due to a limitation with read-only databases including ADG and is therefore expected behaviour as documented in the following:


11.2 Database Administrator's Guide https://docs.oracle.com/cd/E11882_01/server.112/e25494/toc.htm
Chapter 3 Starting Up and Shutting Down
Section: Opening a Database in Read-Only Mode
Limitations of a Read-only Database

which states:

"When executing on a read-only database, you must commit or roll back any in-progress transaction that involves one database link before you use another database link.
This is true even if you execute a generic SELECT statement on the first database link and the transaction is currently read-only."
 
This issue was also addressed in the following bug closed as 'Not a Bug' for the above reason:

Bug:17630569: ADG STANDBY QUERY WITH MULTIPLE DB LINKS THROWS ORA-16000
 

NOTE:  The following note recommends using "set transaction read only", but this workaround doesn't resolve the ORA-16000 when the select includes multiple dblinks:

Dblink on Physical standby - ORA-16000 (Doc ID 1296288.1)

SOLUTION
Do not use multiple dblinks in a select query in an ADG or read-only database. Consider using a temporary table as an option.