oracle-故障-在dg备库使用DB LINK跨库查询时遇到ORA-16000

简介:

在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.
目录
相关文章
|
2月前
|
SQL Oracle 关系型数据库
Oracle查询优化-查询只包含数字或字母的数据
【2月更文挑战第4天】【2月更文挑战第10篇】查询只包含数字或字母的数据
247 1
|
2月前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之和Oracle数据同步必须是使用主库吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
实时计算 Flink版产品使用合集之和Oracle数据同步必须是使用主库吗
|
3天前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle 故障转移能力
【7月更文挑战第10天】
11 2
|
2月前
|
SQL Oracle 关系型数据库
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
86 0
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
|
2月前
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之在 DataWorks 中,使用Oracle作为数据源进行数据映射和查询,如何更改数据源为MaxCompute或其他类型
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
43 1
|
2月前
|
Oracle Java 关系型数据库
实时计算 Flink版产品使用合集之支持 Oracle 整库同步吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
SQL 消息中间件 Oracle
实时计算 Flink版产品使用合集之怎么同步Oracle备库
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
Oracle 安全 关系型数据库
Oracle数据守卫(DG):数据的“守护者”与“时光机”
【4月更文挑战第19天】Oracle Data Guard保障数据安全,通过实时维护备库实现故障切换,保证业务连续性。它使用日志传输和应用保持数据同步,如同“时光机”,借助闪回技术能恢复误操作数据。此外,它还提供数据压缩、加密和故障转移等功能,提升数据库安全性与性能。作为数据管理员,理解并善用Data Guard是确保企业数据安全的关键。
|
2月前
|
SQL Oracle 关系型数据库
Oracle系列之八:SQL查询
Oracle系列之八:SQL查询
|
2月前
|
Oracle 关系型数据库
Oracle 递归查询
Oracle 递归查询
20 0