Troubleshooting Oracle 12c/19c logon and DML fail with ORA-00604 &ORA-00904: “DECL_OBJ#”: invalid identifier

首页 » ORACLE 9i-23ai » Troubleshooting Oracle 12c/19c logon and DML fail with ORA-00604 &ORA-00904: “DECL_OBJ#”: invalid identifier

Troubleshooting Oracle 12c/19c logon and DML fail with ORA-00604 &ORA-00904: “DECL_OBJ#”: invalid identifier

2024/08/20
ORACLE 9i-23ai
52 views
没有评论

最近在Oracle 19C环境中，一个用户在尝试登录备用数据库（standby）时失败。在修复主数据库（primary db）并执行datapatch之后，发现大量包（package）失效，导致正常业务运行也出现错误。特别是在递归调用一个触发器（trigger）时，出现了错误：ORA-00904: “DECL_OBJ#”。此外，尝试禁用或删除该触发器时也失败。记录一下这个问题。

SQL> update xxx set xxx where xxx
....
ora-04045: errors during recompilation/revalition of logon_denied_to_alert
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "DECL_OBJ#": invalid identifier
ORA-00971: missing SET keyword

-- 发现大量invalid package. 包括trigger logon_denied_to_alert

SQL> alter tirgger logon_denied_to_alert complie;
ora-04045: errors during recompilation/revalition of logon_denied_to_alert
ORA-00904: "DECL_OBJ#": invalid identifier

SQL> drop tirgger logon_denied_to_alert;
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "DECL_OBJ#": invalid identifier


SQL> alter package xxxxx compile;
alter package vamames.entmatpes compile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "DECL_OBJ#": invalid identifier

ORA-00604 Error Occurs at Recursive SQL level While Creating Package/Procedure using SQL Developer on Upgraded 12c Database (Doc ID 2476156.1)

Cause

Invalid Internal objects / Issue with “DECL_OBJ#”.

Database is upgraded with patch to 12.2.0.1.181016.
The patch modifies SQL files, but does not load it to the upgraded database.

<ORACLE_HOME>/OPatch$ ./datapatch -verbose

This helps to load the SQL’s to the patched database.

但是这里没有使用SQL developer, 并且datapatch -verbose加载一样报错

ORA-00904: "DECL_OBJ #": invalid identifier
ORA-00604: a recursive SQL level 1 error has occurred
ORA-00904: "DECL_OBJ #": invalid identifier [for statement "declare
lockhandle varcahr2(128);
begin
dbms_lock.allocate_unique('sqlpatch_lock',lockhandle);
...

此时可以做10046 跟踪 DECL_OBJ#是哪个内部对象？是plscope_action$. 然后去查询plscope_action$是做什么用的？

[oracle@anbob admin]$ grep -i plscope_action *
grep: backport_files: Is a directory
cdplsql.sql:     sys.plscope_action$ a, sys.user$ u,
cdplsql.sql:     sys.plscope_action$ a, sys.user$ u, sys.plscope_sql$ s
dplsql.bsq:REM   rdecker   07/07/17 - 25872389: Add decl_obj# to plscope_action$
dplsql.bsq:create table plscope_action$ (
dplsql.bsq:create index i_plscope_action$ on plscope_action$(obj#,signature,action)
dplsql.bsq:create index i_plscope_flags_action$ on plscope_action$(obj#,decl_obj#,flags)
dplsql.bsq:create index i_plscope_decl_action$ on plscope_action$(obj#,decl_obj#)
e1201000.sql:truncate table plscope_action$;
e1202000.sql:update plscope_action$ set flags=null;
e1202000.sql:update plscope_action$ set exp1=null;
e1202000.sql:update plscope_action$ set exp2=null;
e1202000.sql:update plscope_action$ set decl_obj#=null;
i1201000.sql:create index i_plscope_action$ on plscope_action$(obj#,signature,action)
i1202000.sql:alter table plscope_action$ add (flags number, exp1 number, exp2 number,
i1202000.sql:create index i_plscope_flags_action$ on plscope_action$(obj#,decl_obj#,flags)
i1202000.sql:create index i_plscope_decl_action$ on plscope_action$(obj#,decl_obj#)
upobjxt.lst:SYS,I_PLSCOPE_ACTION$,,1,
upobjxt.lst:SYS,PLSCOPE_ACTION$,,2,

Note:

可以看到在dplsql.bsq创建，并在i1202000.sql增加了3个列.e1202000.sql有set null值，但是没有赋值的SQL。

[oracle@anbob admin]$ grep -i e1202000.sql *
e1201000.sql:@@e1202000.sql
e1202000.sql:Rem $Header: rdbms/admin/e1202000.sql /st_rdbms_19/3 2022/03/15 01:26:50 subkanch Exp $
e1202000.sql:Rem e1202000.sql
e1202000.sql:Rem      e1202000.sql - downgrade Oracle to 12.2
e1202000.sql:Rem    SQL_SOURCE_FILE: rdbms/admin/e1202000.sql
e1202000.sql:Rem    SQL_SHIPPED_FILE: rdbms/admin/e1202000.sql
e1202000.sql:Rem BEGIN e1202000.sql
e1202000.sql:Rem END   e1202000.sql
e18.sql:Rem                           from e1202000.sql to e18.sql


[oracle@anbob admin]$ grep i1202000 *
catupstr.sql:                       WHEN '12.2' THEN 'i1202000.sql'
catupstr.sql:            WHEN '12.2' THEN 'i1202000.sql'
i1201000.sql:@@i1202000.sql
i1202000.sql:Rem $Header: rdbms/admin/i1202000.sql /main/9 2017/11/25 11:08:26 welin Exp $
i1202000.sql:Rem i1202000.sql
i1202000.sql:Rem      i1202000.sql - load specific tables that are needed to
i1202000.sql:Rem SQL_SOURCE_FILE: rdbms/admin/i1202000.sql
i1202000.sql:Rem SQL_SHIPPED_FILE: rdbms/admin/i1202000.sql
i1202000.sql:Rem END i1202000.sql


[oracle@anbob  admin]$  grep -i i1201000 *
grep: backport_files: Is a directory
c1201000.sql:Rem    yehan       04/08/15 - bug 20495105: relocate to i1201000.sql the code
catupstr.sql:Rem    jaeblee     02/21/14 - 18056941: for CDB, run i1201000.sql instead of
catupstr.sql:                       WHEN '12.1' THEN 'i1201000.sql'
catupstr.sql:            WHEN '12.1' THEN 'i1201000.sql'
i1102000.sql:@@i1201000.sql
i1201000.sql:Rem i1201000.sql
i1201000.sql:Rem      i1201000.sql - load 12.1.0.2 specific tables that are needed to
i1201000.sql:Rem SQL_SOURCE_FILE: rdbms/admin/i1201000.sql
i1201000.sql:Rem SQL_SHIPPED_FILE: rdbms/admin/i1201000.sql
i1201000.sql:Rem END i1201000.sql

[oracle@anbob ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@anbob admin]$ vi catupstr.sql
Rem NAME
Rem catupstr.sql - CATalog UPgrade STaRt script
Rem
Rem DESCRIPTION
Rem This script performs the initial checks for upgrade
Rem (open for UPGRADE, AS SYSDBA, etc.) and then runs
Rem the "i" scripts, utlip.sql, and the "c" scripts
Rem to complete the basic RDBMS upgrade
Rem
Rem NOTES
Rem Invoked from catupgrd.sql
DEFINE i_upgrade_file=i1102000.sql
COLUMN i_script NEW_VALUE i_upgrade_file NOPRINT
SELECT CASE SUBSTR(version,1,4)
            WHEN '19.0' THEN
                  CASE SUBSTR(prv_version,1,4)
                       WHEN '18.0' THEN 'i18.sql'
                       WHEN '12.2' THEN 'i1202000.sql'
                       WHEN '12.1' THEN 'i1201000.sql'
                       ELSE 'i1102000.sql' end
            WHEN '18.0' THEN 'i18.sql'
            WHEN '12.2' THEN 'i1202000.sql'
            WHEN '12.1' THEN 'i1201000.sql'
            ELSE 'i1102000.sql' end i_script
FROM sys.registry$ WHERE cid='CATPROC';

@@&i_upgrade_file

Note：
e1202000.sql 可见是downgrade时使用，那i1202000.sql是升级时调用在i1201000.sql里调用，也就是升级时。

[oracle@anbob ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@anbob admin]$ vi i1202000.sql


Rem *************************************************************************
Rem Begin PL/Scope ER 24622590 and bug 25872389
Rem *************************************************************************
alter table plscope_action$ add (flags number, exp1 number, exp2 number,
                                 decl_obj# number)
/

Note:

增加列是为了解决Begin PL/Scope ER 24622590 and bug 25872389，查询bug 25872389

ALTER PACKAGE … COMPILE BODY = ORA-600[kdtapc_1] after Upgrade from 11.2.0.4 to 12.2.0.1 (Doc ID 2403408.1)
Changes

The database was upgraded from 11.2.0.4 to 12.2.0.1.
Cause

This issue is caused by a product defect.

It was investigated in unpublished Bug 25872389 which was superseded by the unpublished Bug 28508557.

The fix for 25872389 is first included in 12.2.0.1.181016 (Oct 2018) Database Release Update (DB RU) and 18c

25872389 在COMBO OF OJVM RU COMPONENT 12.2.0.1.181016 + 12.2.0.1.181016 dboct2018ru(Patch 28689128) 该补丁属于ES,无法在标服下载。可以使用第二个方案. 手动执行p25872389_12201180417DBAPR2018RU_Linux-x86-64\25872389\files\rdbms\admin 带的 i1201000.sql,就是上面我们在19c环境找到的文件。

-- 11.2.0.4
SQL> @desc plscope_action$
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      OBJ#                                     NUMBER
    2      ACTION#                                  NUMBER
    3      SIGNATURE                                VARCHAR2(32)
    4      ACTION                                   NUMBER
    5      LINE                                     NUMBER
    6      COL                                      NUMBER
    7      CONTEXT#                                 NUMBER

--正常的19c 
SQL> @desc plscope_action$
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      OBJ#                                     NUMBER
    2      ACTION#                                  NUMBER
    3      SIGNATURE                                VARCHAR2(32)
    4      ACTION                                   NUMBER
    5      LINE                                     NUMBER
    6      COL                                      NUMBER
    7      CONTEXT#                                 NUMBER
    8      FLAGS                                    NUMBER
    9      EXP1                                     NUMBER
   10      EXP2                                     NUMBER
   11      DECL_OBJ#                                NUMBER

Note:
问题环境缺少4个列，手动补齐。使用 i1201000.sql 中的SQL

Rem *************************************************************************
Rem Begin PL/Scope ER 24622590 and bug 25872389
Rem *************************************************************************
alter table plscope_action$ add (flags number, exp1 number, exp2 number,
  decl_obj# number)
/

Rem Create some new indexes to help with dictionary table operations
create index i_plscope_flags_action$ on plscope_action$(obj#,decl_obj#,flags)
tablespace sysaux
/
create index i_plscope_decl_action$ on plscope_action$(obj#,decl_obj#)
tablespace sysaux
/

Rem Set the new plscope_action$.decl_obj# to be the declaration object
Rem number of the identifier
update plscope_action$ pa set decl_obj#=(
  (select obj# from plscope_identifier$ where pa.signature=signature) union
  (select obj# from plscope_statement$ where pa.signature=signature));

Rem Set the new plscope_action$.flags fields where the action is a referenced
Rem SQL object type (table/view/column/sequence)
update plscope_action$ pa set flags=
  (select 1
  from plscope_identifier$ pi
  where pi.type# in (20,39,40,55) and
  pa.signature = pi.signature and
  pa.obj# != pa.decl_obj#);

Rem Remove all table/view/sequences that are not referenced
delete from plscope_action$ where obj# in (
 select obj# from plscope_identifier$ where type# in (20,39,55)
 minus select pi.obj#
  from plscope_identifier$ pi, plscope_action$ pa
  where bitand(pa.flags,1) = 1 and pa.decl_obj# = pi.obj#);

delete from plscope_identifier$ where obj# in (
 select obj# from plscope_identifier$ where type# in (20,39,55)
 minus select pi.obj#
  from plscope_identifier$ pi, plscope_action$ pa
  where bitand(pa.flags,1) = 1 and pa.decl_obj# = pi.obj#);

Rem *************************************************************************
Rem End PL/Scope ER 24622590
Rem *************************************************************************

Note:
至此数据库不在报错（实际只增加列，不赋值也不在报该错误，但依旧建议完整执行）。关于方案中说的3 Set plscope_settings=’IDENTIFIERS:none’ 是无法解决问题的，默认已是该值。

SQL> @pd plscope_settings
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX      NAME                                                     VALUE                          DESCRIPTION
---------- ---------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      1865   749      plscope_settings                                         IDENTIFIERS:NONE               plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL
                                                                                                              source code identifier data

总结：
数据库无法登录到datapatch加载发现数据库大量package失效，最终堵塞了业务DML，报错是一个DDL trigger，trigger无法删除，都指向了plscope_action$的decl_obj#(declaration object)无效，后发现缺少该字段，怀疑要么是文件中oracle 软件缺陷这个bug，或是之前的升级少跑了脚本，导致没有执行某个脚本，导致字典缺少字段。

扩展阅读

catupgrd.sql dbupgrade catctl.pl opatch datapatch

The function of the catupgrd.sql script is replaced by the Parallel Upgrade Utility, catctl.pl, and the dbupgrade and dbupgrade.cmd scripts.

In earlier releases of Oracle Database, the catupgrd.sql Upgrade Utility processed the upgrade. Starting with Oracle Database 12c release 1 (12.1), this script is replaced by the catctl.pl Parallel Upgrade Utility, and its command-line script, dbupgrade. The Parallel Upgrade Utility provides both parallel processing mode and serial modes.

The dbupgrade script calls catctl.pl to create and alter a set of data dictionary tables. The upgrade scripts also call catctl.pl to upgrade or install the database components in the new Oracle Database 12c database.

Opatch is the Oracle tool used to install Oracle patches for product bug fixes into an Oracle Home. A bug fix patch can contain “binary files” (.o, .a, .dll, etc.), that are used to relink Oracle executables, and “sql” files (e.g., .sql, .plb) that are used to patch data dictionary objects within the Oracle databases associated with the Oracle Home.

Datapatch uses sqlplus to run the .sql files in the patch on a single database associated with the Oracle Home, to update the data dictionary. In a RAC environment, datapatch is run on one node, after all of the nodes have been relinked and restarted with the patched executables.

cd $ORACLE_HOME/OPatch
./datapatch -verbose
select action,action_time,description from dba_registry_sqlpatch;

when datapatch utility is required to be executed:
1: moving PDB from a lower version (source CDB) to a higher destination CDB
2: creating a new pluggable database in a patched CDB
3: migrating a PDB to a lower version CDB

打赏

DECL_OBJ#, ORA-00904, plscope_action$

目前这篇文章还没有评论(Rss)

我要评论

上一篇： Oracle 19c RAC(19.11) ‘crsctl stop crs’ without -f Terminated Database Another node Instances， and more about Flex ASM

下一篇： Troubleshooting Oracle 19c real-time statistics row cache lock(dc_realtime_tabst)

ANBOB™