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:
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 |
*** WARNING: ERRORS FOUND DURING UPGRADE *** 1. Evaluate the errors found in the upgrade logs and determine the proper action. 2. Rerun the upgrade when the problem is resolved REASON: ERRORS FOUND: During Upgrade FILENAME: /u01/app/oracle/home/autoupgrade_cerndb/logs_MYDB/MYDB1/109/dbupgrade/catupgrd20200205101408MYDB_misc_rac520.log AT LINE NUMBER: 46964 ------------------------------------------------------ Identifier OWM 20-02-05 10:18:43 SCRIPT = [/u01/app/oracle/product/rdbms1960_jvm1960/rdbms/admin/owmuany.plb] ERROR = [ORA-02002: error while writing to audit trail] STATEMENT = [select * from wmsys.wm$migration_error_view] ------------------------------------------------------ ------------------------------------------------------ Identifier OWM 20-02-05 10:18:43 SCRIPT = [/u01/app/oracle/product/rdbms1960_jvm1960/rdbms/admin/owmuany.plb] ERROR = [ORA-00942: table or view does not exist] STATEMENT = [as above] ------------------------------------------------------ ------------------------------------------------------ Identifier OWM 20-02-05 10:18:43 SCRIPT = [/u01/app/oracle/product/rdbms1960_jvm1960/rdbms/admin/owmuany.plb] ERROR = [ORA-00942: table or view does not exist] STATEMENT = [as above] ------------------------------------------------------ ------------------------------------------------------ Identifier OWM 20-02-05 10:18:43 SCRIPT = [/u01/app/oracle/product/rdbms1960_jvm1960/rdbms/admin/owmuany.plb] ERROR = [ORA-06512: at "WMSYS.LTUTIL", line 4762] STATEMENT = [as above] ------------------------------------------------------ |
Indeed, executing the statement was leading consistently to this problem:
1 2 3 4 5 6 7 8 |
sys@MYDB:SQL> select * from wmsys.wm$migration_error_view; select * from wmsys.wm$migration_error_view * ERROR at line 1: ORA-02002: error while writing to audit trail ORA-00942: table or view does not exist ORA-00942: table or view does not exist ORA-06512: at "WMSYS.LTUTIL", line 4762 |
and we have had this result:
1 2 3 4 5 6 7 8 9 10 |
SQL> SELECT distinct(substr(to_char(message),1,60)) OWM_Upgrade_Errors 2 FROM sys.registry$error 3 WHERE substr(to_char(message),1,9) != 'ORA-00001' 4 AND trim(identifier) = 'OWM'; OWM_UPGRADE_ERRORS ------------------------------------------------------------------- ORA-02002: error while writing to audit trail ORA-00942: table or view does not exist ORA-06512: at "WMSYS.LTUTIL", line 4762 |
So, resuming the autoupgrade job was not a solution.
The view definition is:
1 2 3 4 |
create or replace view wmsys.wm$migration_error_view as select vfield3 error_text from table(wmsys.ltUtil.wm$getErrors) / |
but the package wmsys.ltUtil is wrapped, so no chance to understand what was happening.
As a quick fix, we have recompiled the binaries with mixed auditing:
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 |
# [ oracle@myhost:/u01/app/oracle/product/rdbms1960_jvm1960/rdbms/lib [11:10:35] [19.6.0.0.0 [DBMS EE] SID=MYDB1] 0 ] # $ make -f ins_rdbms.mk uniaud_off ioracle /usr/bin/ar d /u01/app/oracle/product/rdbms1960_jvm1960/rdbms/lib/libknlopt.a kzaiang.o /usr/bin/ar cr /u01/app/oracle/product/rdbms1960_jvm1960/rdbms/lib/libknlopt.a /u01/app/oracle/product/rdbms1960_jvm1960/rdbms/lib/kzanang.o chmod 755 /u01/app/oracle/product/rdbms1960_jvm1960/bin - Linking Oracle rm -f /u01/app/oracle/product/rdbms1960_jvm1960/rdbms/lib/oracle /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` rm -f /u01/app/oracle/product/rdbms1960_jvm1960/bin/oracle mv /u01/app/oracle/product/rdbms1960_jvm1960/rdbms/lib/oracle /u01/app/oracle/product/rdbms1960_jvm1960/bin/oracle chmod 6751 /u01/app/oracle/product/rdbms1960_jvm1960/bin/oracle (if [ ! -f /u01/app/oracle/product/rdbms1960_jvm1960/bin/crsd.bin ]; then \ getcrshome="/u01/app/oracle/product/rdbms1960_jvm1960/srvm/admin/getcrshome" ; \ if [ -f "$getcrshome" ]; then \ crshome="`$getcrshome`"; \ if [ -n "$crshome" ]; then \ if [ $crshome != /u01/app/oracle/product/rdbms1960_jvm1960 ]; then \ oracle="/u01/app/oracle/product/rdbms1960_jvm1960/bin/oracle"; \ $crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \ fi \ fi \ fi \ fi\ ); |
and put the audit_trail=DB in the upgrade pfile (was NONE in this specific case).
After that, restarted the DB in upgrade mode using the same pfile.
After that, the view was giving no errors anymore and we resumed the autoupgrade job.
1 2 3 |
SQL> select * from wmsys.wm$migration_error_view; no rows selected |
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.
It would be interesting to know if anyone of you have had the same problem, and what were the auditing parameters in your case…
—
Ludovico
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Hi Ludovico,
I had the same issue while updating my 12cr1 instance (with OWM) to 19c.
What i did :
– test if OWM is used
– if not : remove it on 12c
– upgrade to 19c
-and reInstall it again
Now , i have an other issue with DVSYS schema :
…
2:01:58 13 END;
12:01:58 14
12:01:58 15 IF (simTblExists = TRUE) THEN
12:01:58 16 — If no simulation log records exist, drop the existing table. It is
12:01:58 17 — created in rdbms/admin/catmacc.sql.
12:01:58 18 IF (cnt = 0) THEN
12:01:58 19 EXECUTE IMMEDIATE ‘drop table dvsys.simulation_log$’;
12:01:58 20 ELSE
12:01:58 21 — If simulation log records exist, move them to
12:01:58 22 — dvsys.old_simulation_log$ table.
12:01:58 23 EXECUTE IMMEDIATE ‘ALTER TABLE dvsys.simulation_log$
12:01:58 24 RENAME TO old_simulation_log$’;
12:01:58 25 sys.dbms_registry.set_progress_value(‘DV’, ‘SIMULATION LOGS’,
12:01:58 26 ‘The existing simulation logs have been moved to dvsys.old_simulation_log$’);
12:01:58 27 END IF;
12:01:58 28 END IF;
12:01:58 29 END;
12:01:58 30 /
DECLARE
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-06512: at line 11
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
ORA-06512: at line 6
…
Have you met this situation ?
Thanks
Hafedh KADDACHI
Hi Hafedh,
I cannot remember, but indeed your case looks very similar. Which type of audit trail do you have? Mixed or unified only?