Re: Oracle 19c Upgrade Failed

Hi Luis!

Thanks for letting me know. It is a really valuable information.I have upgraded one 12.1 database and 12.2 ASM to 19.5 without any issues. The que tables that have been mentioned by Scott have created problems even when upgrading to 12.1.

Regards

On 11/26/19 12:02 PM, Luis Claudio Dias dos Santos wrote:
> 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 <mailto: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 <http://SYS.AQ>$_ALERT_QT_E QUEUE
>>
>> SYS.AQ <http://SYS.AQ>$_ALERT_QT_F VIEW
>>
>> SYS.AQ <http://SYS.AQ>$_ALERT_QT_V EVALUATION CONT
>>
>> SYS.AQ <http://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 <mailto: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
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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