jump to navigation

ORA-06512 executing utlu112i.sql 7 May 2010

Posted by David Alejo Marcos in Oracle 11.2.

I have been upgrading some of our databases from to on Development and QA in order to test performance and stability before upgrading our Production databases.

While this task can be monotonous and repetitive, sometimes Oracle surprises us with some interesting errors that keep us in our toes (unless it is 06:30 am like today…).

While most of the databases I upgraded went fine, two of them reported the following error:

SQL> @/opt/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 3667<

The problem:

Before performing a manual upgrade, Oracle recommends to execute utlu112i.sql to perform some check like space on tablespaces, obsolete/deprecated parameters, timezones, etc.

The output looks like the following:
--> name:       xxxxxx
--> version:
--> compatible:
--> blocksize:       8192
--> platform:       Linux x86 64-bit
--> timezone file: V4
Tablespaces: [make adjustments in the current environment]
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 423 MB
--> UNDOTBS01 tablespace is adequate for the upgrade.
.... minimum required size: 838 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 1098 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
Flashback: OFF
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
-- No update parameter changes are required.
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
-- No renamed parameters found. No changes are required.
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
-- No obsolete parameters found. No changes are required
Components: [The following database components will be upgraded or installed]
--> Oracle Catalog Views     [upgrade]  VALID
--> Oracle Packages and Types     [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
Miscellaneous Warnings
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
PL/SQL procedure successfully completed.

But this morning the output was different:

SQL> @/opt/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 3667

I did check the script and went straight to line 3667:

idx:=idx + 1;

From there to check the values for idx, etc, etc, etc.

The solution:

I have seen more than once and twice that the line listed on the error is not actually the line where the code fails so I started tracing the script.

Finally I found out the problem.

When the database to be upgraded is using Flashback, Oracle retrieves some information like how much space is reclaimable, how much space is in used, the location of the file among others using the following query (line 3739):

execute immediate 'SELECT rfd.name, rfd.space_limit, rfd.space_used,
rfd.space_reclaimable, rfd.number_of_files,
vp1.value, vp2.value
FROM v$recovery_file_dest rfd, v$parameter vp1, v$parameter vp2
INTO flashback_info.name, flashback_info.limit, flashback_info.used,
flashback_info.reclaimable, flashback_info.files,
flashback_info.file_dest, flashback_info.dsize;

and stores the information on a variable called flashback_info defined as:

TYPE fb_record_t IS RECORD (
active              BOOLEAN,       -- ON or OFF
file_dest         VARCHAR2(30), -- db_recovery_file_dest
dsize                 NUMBER,        -- db_recovery_file_dest_size
name                VARCHAR2(513), -- name
limit                  NUMBER,        -- space limit
used                  NUMBER,        -- Used
reclaimable    NUMBER,
files          NUMBER         -- number of files
flashback_info fb_record_t;

and here is the problem.

Oracle has defined the variable to store db_recover_file_dest as file_dest varchar2(30), but in our case the length of the path+file_name was 36 (‘/u01/oradata/xxx/flash_recovery_area’).

There is two ways to fix the problem:

1.- Disable Flashback. Doing this Oracle will skip this step.

2.- modifying the definition of file_dest to be 513, like the variable used to store the value v$recovery_file_dest.name.

As I do not know if this variable is used somewhere else during the upgrade, I decided to disable flashback and play safe as my rollback strategy in case the upgrade fails is base on restoring the database using a RMAN backup I did before the upgrade.


Sometimes, a simple, repetitive and monotonous task can become a challenge.

As always, comments are welcome.



1. Michael Seberg - 16 October 2010

Thanks! Only ran into this on one server but it was at a very bad time. You saved me a ton of time!

slartibartfast - 2 November 2010

good find. saved me time too.

2. Ayesha - 8 May 2012

many thanks!!!!! saved my day!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: