Monday, November 4, 2019

What do do when an upgrade from 12cR1 to 18c is seemingly frozen in Phase #:50



During an upgrade from 12.1 to 18.0 my upgrade process seemed to be completely frozen at stage 50:
Restart  Phase #:47   [proddb01] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [proddb01] Files:1    Time: 4s
Restart  Phase #:49   [proddb01] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [proddb01] Files:1

The catupgrd0.log file located in the $ORACLE_HOME/cfgtoollogs/proddb01/upgrade20191103120250 showed that the last entry was
12:23:40 SQL> update tabpart$
12:23:40   2    set hiboundval='4294967296', bhiboundval = '06C52B5F614961'
12:23:40   3    where obj# in (select t1.obj# as part_obj_no
12:23:40   4      from sys.tabpart$ t1
12:23:40   5        left join sys.tabpart$ tmax
12:23:40   6          on (t1.bo#=tmax.bo# and t1.part# < tmax.part#)
12:23:40   7        join sys.tab$ t3 on (t1.bo#=t3.obj#)
12:23:40   8      where tmax.part# is null and t1.hiboundval is not null
12:23:40   9        and t1.hiboundlen = 10 and bitand(t3.property/power(2, 75), 1) = 1);
This may happen when your database have tables with lots and lots of partitions. A check against the database proves this:
select table_owner,count(*)
from dba_tab_partitions
where table_owner <> 'SYS'
group by table_owner
order by 2 desc;
TABLE_OWNER COUNT(*)
USER1
3497525
USER2
569471
USER3
288664
USER4
59857
USER5
28032
USER6
7239
USER7
1481

In our case, the grant total upgrade time was 10 hours and 11 minutes.
The "update tabpart$" statement above counted for 9,5 hours of this time.

The team responsible for application design later revealed that they have been meaning to redesign the tables to cut down on the number of partitions, but not yet come around to it.

Lession learned....

;-)

No comments:

Post a Comment