Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ORA-39714: upgrade script utlmmig.sql failed
raj.oracledba
post Jun 14 2010, 09:12 AM
Post #1


Member
**

Group: Members
Posts: 13
Joined: 3-June 10
Member No.: 43,215



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
Go to the top of the page
 
+Quote Post
burleson
post Jun 14 2010, 09:59 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,605
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
raj.oracledba
post Jun 14 2010, 10:04 AM
Post #3


Member
**

Group: Members
Posts: 13
Joined: 3-June 10
Member No.: 43,215



QUOTE (burleson @ Jun 14 2010, 08:30 PM) *
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
Go to the top of the page
 
+Quote Post
burleson
post Jun 14 2010, 10:24 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,605
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
limsk
post Jun 14 2010, 09:48 PM
Post #5


Advanced Member
***

Group: Members
Posts: 76
Joined: 20-January 08
From: Kuala Lumpur, MY
Member No.: 16,279



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

Go to the top of the page
 
+Quote Post
damekdavid
post Jan 5 2012, 12:25 PM
Post #6


Newbie
*

Group: Members
Posts: 1
Joined: 5-January 12
Member No.: 46,535



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....
Go to the top of the page
 
+Quote Post
burleson
post Jan 5 2012, 02:12 PM
Post #7


Advanced Member
***

Group: Members
Posts: 11,605
Joined: 26-January 04
Member No.: 13



Hi David,

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

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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 21st October 2014 - 05:18 AM