ORA-39714: upgrade script utlmmig.sql failed Options

[raj.oracledba]

Hi,

while upgrading to 11.1.0.7 from 10.2.0.4
at the end we are facing this error

ORA-39714: upgrade script utlmmig.sql failed

*Cause: A normal database open was attempted, but the upgrade script
utlmmig.sql failed to complete.
*Action: Use the UPGRADE option when opening the database and then run
utlmmig.sql.


i ran the utlmmig.sql script and i am facing this issue

SQL> -- we also need to update the statistic
SQL> begin
2 dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
3 dbms_stats.delete_table_stats('SYS', 'USER$MIG');
4 dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG', estimate_percent => 100,
5 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
6 dbms_Stats.gather_table_stats('SYS', 'USER$MIG', estimate_percent => 100,
7 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
8 end;
9 /
begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2

compiled the invalid objects using utlirp.sql and utlrp.sql but of no use..

please advice

[burleson]

Hi,

Evidently, you have another prerequisite script missing; the one that installs the dbms_stats packages.

Check catproc.sql or catalog.sql

Are you changing from 32-bit to 64-bit?

This error happens after you run @?/rdbms/admin/catupgrd.sql and then open the database:

ORA-39714: upgrade script utlmmig.sql failed
Cause: A normal database open was attempted, but the upgrade script utlmmig.sql failed to complete.
Action: Use the UPGRADE option when opening the database and then run utlmmig.sql.

>> compiled the invalid objects using utlirp.sql and utlrp.sql but of no use..

That won't help!

You need to re-run utlmmig.sql



See here:

http://hrivera99.blogspot.com/2010/04/ora-...er-upgrade.html

[raj.oracledba]

Hi,

Are you changing from 32-bit to 64-bit?

This error happens after you run @?/rdbms/admin/catupgrd.sql and then open the database:

ORA-39714: upgrade script utlmmig.sql failed
Cause: A normal database open was attempted, but the upgrade script utlmmig.sql failed to complete.
Action: Use the UPGRADE option when opening the database and then run utlmmig.sql.

>> compiled the invalid objects using utlirp.sql and utlrp.sql but of no use..

That won't help!

You need to re-run utlmmig.sql

See here:

http://hrivera99.blogspot.com/2010/04/ora-...er-upgrade.html

Hi Don,
Thanks for the reply.

i am upgrading from 10.2.0.4 to 11.1.0.7

in the pst upgradation steps oracle db failed to open throwing the error in alert.log and trace file that
ORA-39714: upgrade script utlmmig.sql failed

so i started the db in upgrade mode and i ran the script utlmmig.sql. but that script is throwing
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2


as per the info i got from MOS docs and google search, i have compiled the INVALID objects and i ran this script again

but still i am getting the same error ..

i am not able to open the db normally..

please advice

Raj

[burleson]

Hi Raj,

>> please advice

I already did!

You have made errors by not running important catalog scripts.

You need to work backwards.

>> ORA-04063: package body "SYS.DBMS_STATS" has errors

Start by fixing dbms_stats. Re-compile it first, and identify AND FIX ERRORS.

My guess is that you did not properly run several cxataloag scripts, but who knows?

Then, once you have dbms_stats working, continue, by re-run utlmmig.sql

[limsk]

Have seen DBMS_STATS get invalid after patches and upgrades. Try recreating it:

Backup your database

Make the <oracle home>\rdbms\admin your working directory

-- Start the database in upgrade
SQL> shutdown immediate
SQL> startup upgrade

-- recreate DBMS_STATS
SQL> @dbmstat.sql
SQL> @prvtstas.plb
SQL> @prvtstat.plb

-- Bounce the database
SQL> shutdown immediate
SQL> startup

-- Recompile any invalid objects
SQL> @utlrp.sql

[damekdavid]

I know this is old, but this is one of the first sites to come up when googling this problem so I figured I'd add what worked for me. According to Metalink article, ID 1271490.1, this is a problem with a public synonym for XMLCONCAT. Here were the steps performed to fix this:

SQL>startup upgrade ;

1. Drop the synonym:
SQL> drop public synonym XMLCONCAT;

2. Recompile the package
SQL> alter package DBMS_SQLTUNE_INTERNAL compile body;

If the above is success, then re-run CATUPGRD.SQL again :- (or in our case, the utlmmig.sql script)

SQL>@catupgrd.sql ; (@utlmmig.sql)

SQL>exit

Once these steps were performed, the utlmmig.sql script ran successfully, and all was good. I was then able to start up the database. Hope this helps someone out there....

[burleson]

Hi David,

>> Hope this helps someone out there....

That is VERY nice of you! Thanks for being an alturist!