Troubleshooting Oracle 12c/19c logon and DML fail with ORA-00604 &ORA-00904: “DECL_OBJ#”: invalid identifier
最近在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)
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 -verboseThis 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)
ChangesThe database was upgraded from 11.2.0.4 to 12.2.0.1.
CauseThis 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
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。