Re: Oracle 19c Upgrade Failed

Mladen, I faced a bug while patching a 19.3.0 to 19.5.0. The problem is (very) well described by Rodrigo Jorge (who is smiling very large today because his soccer team) in this post:

https://www.dbarj.com.br/2019/10/datapatch-fails-with-ora-29516-ora-00604-and-ora-29532-java-lang-arrayindexoutofboundsexception

So I advise always apply 19.4.0 before 19.5.0, if this last is the goal patch.

Em sex., 22 de nov. de 2019 às 22:21, Mladen Gogala <gogala.mladen_at_gmail.com> escreveu:

> Hi Scott!
>
> The objects that you're concerned with are queues used by OEM to monitor
> server alerts, and were introduced in 11.1 or even 10g. Queue tables are
> infamous upgrade killers. So, what you can do is to drop them all using the
> following:
>
> _at_?/rdbms/admin/catnoalr.sql
> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => ‘sys.alert_qt’, FORCE =>
> TRUE);
>
> Then you can upgrade your database as you normally would and rebuild the
> structures using:
>
> _at_?/rdbms/admin/dbmsslrt.sql
> _at_?/rdbms/admin/catalrt.sql
>
> These two files will rebuild the queues and queue tables. After that, you
> will have to recompile using _at_?/rdbms/admin/utlrp
>
> The files exist even on 19c:
>
> -bash-4.2$ cd $ORACLE_HOME/rdbms/admin
> -bash-4.2$ pwd
> /opt/oracle/product/19c/dbhome_1/rdbms/admin
> -bash-4.2$ ls -l catalrt.sql
> -rw-r--r-- 1 oracle oinstall 15260 Jun 26 2017 catalrt.sql
> -bash-4.2$ ls -l dbmsalrt.sql
> -rw-r--r-- 1 oracle oinstall 15308 Feb 20 2014 dbmsalrt.sql
> -bash-4.2$
>
> BTW, is there any reason for upgrading to 19.4 and not 19.5? I am using
> the latter:
>
> -bash-4.2$ sqlplus / as sysdba
>
> SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 22 20:14:01 2019
> Version 19.5.0.0.0
>
> Copyright (c) 1982, 2019, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
> Version 19.5.0.0.0
>
> SQL>
>
> In order to get 19.5 you have to apply the upgrade bundle from October
> 2019.
>
>
> On 11/22/19 10:12 AM, Scott Canaan wrote:
>
> We have done over 70 Oracle 19c upgrades (from 12.1.0.2), using dbua,
> without any failures. Today, I tried to run through one and it failed.
> The failure is with the SYS.ALERT_QUE stuff:
>
>
>
> QL> select substr(owner||'.'||object_name,1,45), substr(object_type,1,15)
>
> 2 from dba_objects where status <> 'VALID' order by 1,2;
>
>
>
> SUBSTR(OWNER||'.'||OBJECT_NAME,1,45) SUBSTR(OBJECT_T
>
> --------------------------------------------- ---------------
>
> SYS.ALERT_QT TABLE
>
> SYS.ALERT_QUE QUEUE
>
> SYS.ALERT_QUE$1 RULE
>
> SYS.ALERT_QUE$1 RULE SET
>
> SYS.ALERT_QUE_N RULE SET
>
> SYS.ALERT_QUE_R RULE SET
>
> SYS.AQ$_ALERT_QT_E QUEUE
>
> SYS.AQ$_ALERT_QT_F VIEW
>
> SYS.AQ$_ALERT_QT_V EVALUATION CONT
>
> SYS.AQ$ALERT_QT VIEW
>
>
>
> I tried to drop and rebuild this and it worked, except that about 50 other
> items then because invalid and would not validate. Looking online, I found
> a suggestion to run catproc, so I tried that and ended up back where I
> started, with the SYS.ALERT_QUE stuff invalid again.
>
>
>
> The only issue I ran into in the pre-upgrade steps was that it complained
> that job_queue_processes was set to 0. The suggestion was to either remove
> it from the spfile or set it to a non-zero value. I chose to remove it.
>
>
>
> I am in the process of restoring the database from the last backup, but I
> am not sure how to proceed as I’m sure if I just try to upgrade again, it
> will fail with the same error.
>
>
>
> Does anyone have any idea what is causing this and how to fix / avoid it?
>
>
>
> Stats:
>
> Original version: 12.1.0.2
>
> New version: 19.4.0.0
>
> O/S: Red Hat 7
>
>
>
> Thank you,
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l