{"id":1918,"date":"2020-02-05T12:58:33","date_gmt":"2020-02-05T10:58:33","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1918"},"modified":"2020-08-18T15:58:54","modified_gmt":"2020-08-18T13:58:54","slug":"ora-02002-upgrading-owm-19c","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/ora-02002-upgrading-owm-19c\/","title":{"rendered":"ORA-02002 and ORA-00942 while upgrading OWM to 19c"},"content":{"rendered":"

This is a quick post about a problem that we have had while upgrading a DB to 19c.<\/p>\n

At 91% of the upgrade, the OWM (Workspace Manager) upgrade was failing with this error error:<\/p>\n

*** WARNING: ERRORS FOUND DURING UPGRADE ***\r\n\r\n 1. Evaluate the errors found in the upgrade logs\r\n    and determine the proper action.\r\n 2. Rerun the upgrade when the problem is resolved\r\n\r\nREASON:\r\n      ERRORS FOUND: During Upgrade\r\n         FILENAME: \/u01\/app\/oracle\/home\/autoupgrade_cerndb\/logs_MYDB\/MYDB1\/109\/dbupgrade\/catupgrd20200205101408MYDB_misc_rac520.log AT LINE NUMBER: 46964\r\n------------------------------------------------------\r\nIdentifier OWM 20-02-05 10:18:43\r\nSCRIPT    = [\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/admin\/owmuany.plb]\r\nERROR     = [ORA-02002: error while writing to audit trail]\r\nSTATEMENT = [select * from wmsys.wm$migration_error_view]\r\n------------------------------------------------------\r\n------------------------------------------------------\r\nIdentifier OWM 20-02-05 10:18:43\r\nSCRIPT    = [\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/admin\/owmuany.plb]\r\nERROR     = [ORA-00942: table or view does not exist]\r\nSTATEMENT = [as above]\r\n------------------------------------------------------\r\n------------------------------------------------------\r\nIdentifier OWM 20-02-05 10:18:43\r\nSCRIPT    = [\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/admin\/owmuany.plb]\r\nERROR     = [ORA-00942: table or view does not exist]\r\nSTATEMENT = [as above]\r\n------------------------------------------------------\r\n------------------------------------------------------\r\nIdentifier OWM 20-02-05 10:18:43\r\nSCRIPT    = [\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/admin\/owmuany.plb]\r\nERROR     = [ORA-06512: at \"WMSYS.LTUTIL\", line 4762]\r\nSTATEMENT = [as above]\r\n------------------------------------------------------<\/pre>\n
Indeed, executing the statement was leading consistently to this problem:<\/p>\n
sys@MYDB:SQL> select *  from wmsys.wm$migration_error_view;\r\nselect *  from wmsys.wm$migration_error_view\r\n                                     *\r\nERROR at line 1:\r\nORA-02002: error while writing to audit trail\r\nORA-00942: table or view does not exist\r\nORA-00942: table or view does not exist\r\nORA-06512: at \"WMSYS.LTUTIL\", line 4762<\/pre>\n
and we have had this result:<\/p>\n
SQL> SELECT distinct(substr(to_char(message),1,60)) OWM_Upgrade_Errors\r\n  2 FROM sys.registry$error\r\n  3  WHERE substr(to_char(message),1,9) != 'ORA-00001'\r\n  4     AND trim(identifier) = 'OWM';\r\n\r\nOWM_UPGRADE_ERRORS\r\n-------------------------------------------------------------------\r\nORA-02002: error while writing to audit trail\r\nORA-00942: table or view does not exist\r\nORA-06512: at \"WMSYS.LTUTIL\", line 4762\r\n<\/pre>\n
So, resuming the autoupgrade job was not a solution.<\/p>\n
The view definition is:<\/p>\n
create or replace view wmsys.wm$migration_error_view as\r\nselect vfield3 error_text\r\nfrom table(wmsys.ltUtil.wm$getErrors)\r\n\/<\/pre>\n
but the package wmsys.ltUtil is wrapped, so no chance to understand what was happening.<\/p>\n
As a quick fix, we have recompiled the binaries with mixed auditing:<\/p>\n
# [ oracle@myhost:\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib [11:10:35] [19.6.0.0.0 [DBMS EE] SID=MYDB1] 0 ] #\r\n$ make -f ins_rdbms.mk uniaud_off  ioracle\r\n\/usr\/bin\/ar d \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/libknlopt.a kzaiang.o\r\n\/usr\/bin\/ar cr \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/libknlopt.a \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/kzanang.o\r\nchmod 755 \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/bin\r\n\r\n - Linking Oracle\r\nrm -f \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/oracle\r\n\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/bin\/orald  -o \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/ -L\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/ -L\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/stubs\/   -Wl,-E \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/opimai.o \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/ssoraed.o \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/nautab.o \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/naeet.o \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/naect.o \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/naedhs.o \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/config.o  -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19  -lrt -lplp19 -ldmext -lserver19 -lclient19  -lvsnst19 -lcommon19 -lgeneric19 `if [ -f \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/libavserver19.a ] ; then echo \"-lavserver19\" ; else echo \"-lavstub19\"; fi` `if [ -f \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/libavclient19.a ] ; then echo \"-lavclient19\" ; fi` -lknlopt -lslax19 -lpls19  -lrt -lplp19 -ljavavm19 -lserver19  -lwwg  `cat \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/ldflags`    -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19   -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if \/usr\/bin\/ar tv \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/libknlopt.a | grep \"kxmnsd.o\" > \/dev\/null 2>&1 ; then echo \" \" ; else echo \"-lordsdo19 -lserver19\"; fi` -L\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/ctx\/lib\/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19  -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore  -lippcp -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19  -lsnls19 -lnls19  -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19  -ledtn19 -laio -lons  -lmql1 -lipc1 -lfthread19    `cat \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/sysliblist` -Wl,-rpath,\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib -lm    `cat \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib\/sysliblist` -ldl -lm   -L\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/lib `test -x \/usr\/bin\/hugeedit -a -r \/usr\/lib64\/libhugetlbfs.so && test -r \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`\r\nrm -f \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/bin\/oracle\r\nmv \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/rdbms\/lib\/oracle \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/bin\/oracle\r\nchmod 6751 \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/bin\/oracle\r\n(if [ ! -f \/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/bin\/crsd.bin ]; then \\\r\n    getcrshome=\"\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/srvm\/admin\/getcrshome\" ; \\\r\n    if [ -f \"$getcrshome\" ]; then \\\r\n        crshome=\"`$getcrshome`\"; \\\r\n        if [ -n \"$crshome\" ]; then \\\r\n            if [ $crshome != \/u01\/app\/oracle\/product\/rdbms1960_jvm1960 ]; then \\\r\n                oracle=\"\/u01\/app\/oracle\/product\/rdbms1960_jvm1960\/bin\/oracle\"; \\\r\n                $crshome\/bin\/setasmgidwrap oracle_binary_path=$oracle; \\\r\n            fi \\\r\n        fi \\\r\n    fi \\\r\nfi\\\r\n);\r\n<\/pre>\n
and put the audit_trail=DB in the upgrade pfile (was NONE in this specific case).<\/p>\n
After that, restarted the DB in upgrade mode using the same pfile.<\/p>\n
After that, the view was giving no errors anymore and we resumed the autoupgrade job.<\/p>\n
SQL> select *  from wmsys.wm$migration_error_view;\r\n\r\nno rows selected\r\n<\/pre>\n
This is an old troubleshooting method that I call “Database Administration by guess”: I am not sure about the real cause, but the workaround just worked fine for us.<\/p>\n
It would be interesting to know if anyone of you have had the same problem, and what were the auditing parameters in your case…<\/p>\n
—<\/p>\n
Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":"
This is a quick post about a problem that we have had while upgrading a DB to 19c. At 91% of the upgrade, the OWM (Workspace Manager) upgrade was failing with this error error: *** WARNING: ERRORS FOUND DURING UPGRADE … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[321,326,3,315,330],"tags":[322,323,238],"class_list":["post-1918","post","type-post","status-publish","format-standard","hentry","category-aced","category-oracle","category-oracledb","category-oracle-database-19c","category-oracle-inst-upg","tag-autoupgrade","tag-owm","tag-upgrade"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1918","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1918"}],"version-history":[{"count":1,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1918\/revisions"}],"predecessor-version":[{"id":1919,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1918\/revisions\/1919"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1918"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1918"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1918"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}