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