Re: Oracle 19c Upgrade Failed

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$

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 <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


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