dg 执行explain ORA-00604: ORA-03180

原创刘继超云和恩墨技术服务团队 2025-12-14

112

[[toc]]

一、适用范围

oracle dg

二、问题概述

近期。在配合国产库迁移时，从dg断进行执行，代码中有一个explain的语句，发现执行报错
ORA-00604: error occurred at recursive SQL level 1
ORA-03180: Sequence values cannot be allocated for Oracle Active Data Guard standby.

三、问题原因猜测

问题1、explain 语句需要往plan_tablel临时表插入数据
问题2、根据报错看需要获取Sequence 号

四、问题原因

1、查看plan_table 是正常的

SQL> @o %.plan_table

owner                     object_name                    object_type          status           OID      D_OID CREATED             LAST_DDL_TIME
------------------------- ------------------------------ -------------------- --------- ---------- ---------- ------------------- -------------------
BILLING                   PLAN_TABLE                     TABLE                VALID         240122     240122 2023-06-01 09:24:00 2023-06-01 09:24:00
DBMT                      PLAN_TABLE                     SYNONYM              VALID         320488            2024-08-07 16:47:46 2024-08-07 16:47:46
PUBLIC                    PLAN_TABLE                     SYNONYM              VALID           7533            2019-04-17 01:01:55 2019-04-17 01:01:55
SYS                       PLAN_TABLE$                    TABLE                VALID           7530            2019-04-17 01:01:55 2019-04-17 01:01:55
PUBLIC                    PLAN_TABLE_OBJECT              SYNONYM              VALID           7545            2019-04-17 01:01:55 2019-04-17 01:01:55
SYS                       PLAN_TABLE_OBJECT              TYPE                 VALID           7544            2019-04-17 01:01:55 2019-04-17 01:01:55
SYS                       PLAN_TABLE_OBJECT              TYPE BODY            VALID          19180            2019-04-17 01:11:25 2019-04-17 01:11:25

7 rows selected.

2、带着问题2做个10046

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> explain plan for select 1+1 from dual;
explain plan for select 1+1 from dual
                                 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-03180: Sequence values cannot be allocated for Oracle Active Data Guard standby.


SQL>  oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/sxptb/sxpta1/trace/sxpta1_ora_57065_ljc.trc

3、查看trace文件

=====================
PARSING IN CURSOR #140737261877016 len=37 dep=0 uid=0 oct=50 lid=0 tim=11487100727587 hv=3883021901 ad='f7df77d8' sqlid='frwbvtvmr4dkd'
explain plan for select 1+1 from dual
END OF STMT
PARSE #140737261877016:c=2008,e=2440,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=11487100727578
=====================
PARSING IN CURSOR #140737261726104 len=586 dep=1 uid=0 oct=2 lid=0 tim=11487100727956 hv=2508123768 ad='ff87e340' sqlid='99qa3zyarxvms'
insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_type,search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, pa
rtition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,
:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)
END OF STMT
PARSE #140737261726104:c=109,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=11487100727956
=====================
PARSING IN CURSOR #140737261727744 len=41 dep=1 uid=0 oct=3 lid=0 tim=11487100729164 hv=387538332 ad='1cfeaca50' sqlid='15knr3nbjkrcw'
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL
END OF STMT
PARSE #140737261727744:c=1478,e=1087,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2501920895,tim=11487100729163
EXEC #140737261727744:c=74,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2501920895,tim=11487100729343
WAIT #140737261727744: nam='PGA memory operation' ela= 20 p1=327680 p2=1 p3=0 obj#=-1 tim=11487100729449
WAIT #140737261727744: nam='control file sequential read' ela= 563 file#=0 block#=1 blocks=1 obj#=-1 tim=11487100730426
WAIT #140737261727744: nam='control file sequential read' ela= 594 file#=0 block#=39 blocks=1 obj#=-1 tim=11487100731083
WAIT #140737261727744: nam='control file sequential read' ela= 529 file#=0 block#=41 blocks=1 obj#=-1 tim=11487100731659
WAIT #140737261727744: nam='control file sequential read' ela= 622 file#=0 block#=1070 blocks=1 obj#=-1 tim=11487100732333
krsu_reinit_conn: OCIServerAttach failed -1
krsu_dump_oci_emsg: Detailed OCI error val is 12541 and errmsg is 'ORA-12541: TNS:no listener
'
krsu_reinit_conn: OCIServerAttach failed -1
krsu_dump_oci_emsg: Detailed OCI error val is 12541 and errmsg is 'ORA-12541: TNS:no listener
'
krsu_reinit_conn: OCIServerAttach failed -1
krsu_dump_oci_emsg: Detailed OCI error val is 12541 and errmsg is 'ORA-12541: TNS:no listener
'
krsu_reinit_conn: OCIServerAttach failed -1
krsu_dump_oci_emsg: Detailed OCI error val is 12541 and errmsg is 'ORA-12541: TNS:no listener
'
*** 2024-08-07 17:06:33.720293 [krsh.c:6348]
Error 12541 received logging on to the standby
*** 2024-08-07 17:06:33.720412 [krsh.c:6348]
Check whether the listener is up and running.
*** 2024-08-07 17:06:33.720445 [krsu.c:2025]
krsu_rmi_lwc_connect: Encountered error status 12541 attempting connection to sxpta1
sxpta1: Encountered connect exception 12541

从trace文件中发现，需要连接到主库去查询 SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL sql语句，发现ORA-12541: TNS:no listener错误，sxpta1有问题了

4、尝试tnsping

SQL> !
[oracle@sxptb1:/home/oracle]$ tnsping SXPTA1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-AUG-2024 17:26:51

Copyright (c) 1997, 2020, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(ADDRESS_LIST =(LOAD_BALANCE=off) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 133.96.123.44)(PORT = 1529)) (ADDRESS = (PROTOCOL = TCP)(HOST = 133.96.123.45)(PORT = 1529))) (CONNECT_DATA = (SERVICE_NAME = sxpta)) (FAILOVER_MODE=(TYPE=SELECT)) (METHOD=BASIC))
TNS-12541: TNS:no listener

是通过1529的端口连接的主库

解决方案

1、把1529的监听拉起来

 
topteact@sxpta1 ~]$ su  - grid
Password: 
Last login: Wed Aug  7 17:31:10 CST 2024
[grid@sxpta1:/home/grid]$ cd $ORACLE__HOME 

[grid@sxpta1:/oracle/app/19.0.0/grid/network/admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

LISTENER_DG=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 133.96.123.44)(PORT = 1529))
    )
  )

SID_LIST_LISTENER_DG=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sxpta)
      (ORACLE_HOME = /oracle/app/oracle/product/19.0.0/db_1)
    (SID_NAME = sxpta1)
    )
  )
[grid@sxpta1:/oracle/app/19.0.0/grid/network/admin]$ lsnrctl start LISTENER_DG

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-AUG-2024 17:37:34

Copyright (c) 1991, 2020, Oracle.  All rights reserved.

Starting /oracle/app/19.0.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /oracle/app/19.0.0/grid/network/admin/listener.ora
Log messages written to /oracle/app/grid/diag/tnslsnr/sxpta1/listener_dg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=133.96.123.44)(PORT=1529)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=133.96.123.44)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DG
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-AUG-2024 17:37:34
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/19.0.0/grid/network/admin/listener.ora
Listener Log File         /oracle/app/grid/diag/tnslsnr/sxpta1/listener_dg/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=133.96.123.44)(PORT=1529)))
Services Summary...
Service "sxpta" has 1 instance(s).
  Instance "sxpta1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

2、再次做explain


SQL> explain plan for select 2+4 from dual;

Explained.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL>

墨力计划 oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者