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@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> 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:
@?/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:
-- Mladen Gogala
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@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.
Database Consultant
Tel: (347) 321-1217
