Thursday, September 26, 2013

How long did your Database Upgrade actually take?

This might be old news, but it became new news for me after I discovered it last week. As far as I know, there's no way to determine the length of a database upgrade. There are, however, ways to influence the length by gathering dictionary stats, truncate AUD$ and FGA_LOG$. At the end of the upgrade process, either by DBUA, or via Manually I'd want to know the actual time taken by the scripts.

With a recent upgrade from 11.1.0.7 t o 11.2.0.3 on Solaris, I ran into this issue that disrupted the upgrade process and my (inadequate mental) timer. After reviewing the scripts called internally by catupgd.sql, I found this one that I thought was quite helpful.

SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           09-20-2013 14:53:05
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.3.0  00:15:13
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.3.0  00:03:39
Oracle Real Application Clusters
.                                         VALID      11.2.0.3.0  00:00:00
Oracle Workspace Manager
.                                         VALID      11.2.0.3.0  00:00:50
OLAP Analytic Workspace
.                                         VALID      11.2.0.3.0  00:00:23
OLAP Catalog
.                                         VALID      11.2.0.3.0  00:00:59
Oracle OLAP API
.                                         VALID      11.2.0.3.0  00:00:25
Oracle Enterprise Manager
.                                         VALID      11.2.0.3.0  00:04:56
Oracle XDK
.                                         VALID      11.2.0.3.0  00:00:44
Oracle Text
.                                         VALID      11.2.0.3.0  00:00:46
Oracle XML Database
.                                         VALID      11.2.0.3.0  00:04:48
Oracle Database Java Packages
.                                         VALID      11.2.0.3.0  00:00:26
Oracle Multimedia
.                                         VALID      11.2.0.3.0  00:10:05
Spatial
.                                         VALID      11.2.0.3.0  00:05:34
Oracle Expression Filter
.                                         VALID      11.2.0.3.0  00:00:10
Oracle Rules Manager
.                                         VALID      11.2.0.3.0  00:00:09
Gathering Statistics
.                                                                00:05:56
Total Upgrade Time: 00:55:14 <--

PL/SQL procedure successfully completed.

As it turns out, Tim Hall has blogged about it within his DB12c upgrade steps. The script in 12c is simply called utlu121s.sql.


Cheers!

No comments :

Post a Comment