Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Wednesday, September 26, 2007

DB invalids in 11.5.10.2 after upgrading from 9207 to 10203

In a test environment, we got more than 300 invalids after upgrading an 11.5.10.2 instance from DB 9.2.0.7 to 10.2.0.3. Metalink had a few notes on some of the objects:

458370.1: After Upgrade To Db 10.2.0.3, several Ctxsys Procedures/Packages ('WWSBR%) Are Invalid
380480.1: Invalid Objects After Upgrading Database From 9.2.0.4 To 10.2.0.2
386746.1: PORTAL Synonyms are Invalid After RDBMS Upgrade 9i to 10g

However, we had 250 invalids which were public synonyms referring to OLAPSYS, AMD and ODM. alter synonym compile resulted in ORA-00980: synonym translation is no longer valid. I checked and found that base objects for these synonyms did not exist. I went a step further and found that the schemas, OLAPSYS, AMD and ODM also did not exist. The conclusion was this:

1. In 2004, this instance was upgraded from 11.5.8 to 11.5.10.2 and DB upgraded from 8i to 9i. The components of OLAPSYS, AMD, and ODM which come preinstalled in a fresh 11.5.10.2 installation, were not installed separately during this upgrade. Hence these schemas were missing which resulted in invalid synonyms.

2. When we upgraded 9207 to 10203, it upgraded only the components which were present in 9207. It did not install OLAPSYS, AMD and ODM.

We followed some metalink notes to create the schemas for OLAPSYS, AMD and ODM.

420791.1 How To Manually Install Data Mining
296187.1 How To Manually Install Oracle OLAP into a 9i or 10g Database After the DB Has Been Created

Once we followed the steps in these notes, the OLAPSYS, AMD and ODM schemas were created and the 250 invalid synonyms became valid.

1 comment:

Nandkishor Wagh said...

nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanx a ton once again, Regards, Synonyms In Oracle