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:
@?/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:
@?/rdbms/admin/dbmsslrt.sql
@?/rdbms/admin/catalrt.sql
These two files will rebuild the queues and queue tables. After that,
you will have to recompile using @?/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@xxxxxxx <mailto:srcdco@xxxxxxx>_| 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.
