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.
