Oracle’s utlrp.sql Script and Edition-based Redefinition

Edition-based Redefinition can be a real asset when modifying existing application code such as procedures, packages and functions but it can also throw a monkey wrench into the works when invalid objects rear their ugly heads, as ORA-00600 errors can be thrown in Oracle release 12.1.0.2:

ERROR at line 1:
ORA-4045: errors during recompilation/revalidation of ...
ORA-600: internal error code, arguments: [17016], [0x1BD9EC6A0], [], [],[],[], [], [], [], [], [], []
ORA-6512: at "SYS.DBMS_UTILITY", line 1294
ORA-6512: at line 1

The customer reporting this has gone through the following steps:

Upgrade Oracle applications
Upgrade Oracle database
Upgrade third-party application which uses Edition-based Redefinition

The steps to upgrade the Oracle applications and the database complete without error; the problem arises in the upgrade of the third-party application where the cleanup process (which identifies and recompiles invalid objects) fails with the above-mentioned error. The utlrp script was then used to validate the invalid objects, which returned the same error.

Although the error was reported for Linux x86-64 it has been determined this is a generic error that can affect any port of Oracle. A similar issue plagued Oracle releases using Edition-based Redefinition (Bug 12950694) but this has been listed as fixed, the only known issue to affect utlrp with respect to Edition-based Redefinition. An interesting point to consider is that the UTL_RECOMP package has not been reported as invalid. Another interesting piece is that from the SQL*Plus command line objects that throw this error from utlrp.sql recompile successfully. From logs generated when the error occurs the offending statement is an ALTER TYPE for an Oracle-generated type name. For releases not using Edition-based Redefinition no such errors have been reported.

One possibility is the existence of more than one type with the same name, yet querying the database throwing this error proves only one record is returned when querying against the object name. Another problem with this issue is that existing editions cannot be pushed into production. So, even though the objects can be validated from the SQL*Plus command line the edition changes cannot be moved into production.

Oracle Support has no work-around for this, and the customer who reported the problem can always reproduce the issue. Oracle Support cannot generate the error and no test case is available, which makes it difficult, if not impossible, to investigate. Since no other reports of this behavior have been submitted to Oracle Support it’s possible that this is a one-off issue related to either the client’s configuration or relating to the third-party application they are using (this application has not been identified in the bug report, and no indication of any communication with the application vendor was supplied to Oracle Support). The incident file from the customer reports:

Starting incident default dumps (flags=0x2, level=3,mask=0x0)[TOC00003]
----- Current SQL Statement for this session (sql_id=c19hkha8n9qcy) -----
ALTER TYPE "*********"."SYS_PLSQL_4282DF2D_74_1" COMPILE SPECIFICATION REUSE SETTINGS
[TOC00004]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object handle     line number  object name
0x1befdc068              1294  package body SYS.DBMS_UTILITY
0x1b3bcfb48                 1  anonymous block
0x1b3f6f268               413  package body SYS.UTL_RECOMP
0x1b3f6f268               559  package body SYS.UTL_RECOMP
0x1b3f6f268               807  package body SYS.UTL_RECOMP
0x1bee3c3f0                 4  anonymous block
[TOC00004-END]

Only the third-party cleanup process or utlrp generate this error and call stack trace. Even when the object is listed as valid (through a command-line recompilation) this error is thrown at the customer site. Again, as of now no reproducible test case can be created and the error cannot be duplicated by Oracle Support.

This was reported toward the end of November of this year so I suspect that Oracle Support is still digging into the issue to see what may be causing the problem. Since it’s a third-party application that is causing the issue (from all current reports) it may be a problem the vendor needs to address. For at least one customer this has been a show stopper, and they are in a bind until a resolution can be found.

Remember that the Oracle applications upgrade and the Oracle database upgrade were successful and error-free, so using Edition-based Redefinition doesn’t seem to be an issue. Unfortunately for the customer involved this is a production system; a properly configured test environment, including the third-party application, may have revealed this issue before it affected the production environment and the customer could still conduct business while the issue was being investigated and, hopefully, resolved. This is one of the best arguments for having a test environment that mirrors production. As can be seen from this case having such an environment can be invaluable, which makes it definitely worth the investment.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles