Oracle Applications DBA Blog

The Case.. The Mess up and The Quick Fix or Re Creating APPS Synonyms without using ADADMIN

My current post(or a story rather) deals about a real life mess up and the quck solution we did to fix it with the least possible down time.
The content in this post is scritly for informational purposes only. DO NOT try this method even on your TEST or Development environment.

The Case
We all know that the APPS schema in Oracle Applications does not really hold many objects but holds synonyms to all the application objects in various schemas.
Also it is a common practice for the development environment to have a schema similar to the apps schema in Oracle Applications with only read access.
Some of these synonyms had gone invalid after a cloning process and were giving a synonym translation invalid error. It was  decided to drop and recreate all the synonyms for the READ ONLY schema.

A dynamic script was crated for dropping all the synonyms form this schema.

spool /tmp/dropsynonym.sql
select 'drop synonym '||SYNONYM_NAME ||';' from user_synonyms;
spool off

This was supposed to be executed in the READ ONLY schema.

After this the synonyms were to be recreated again using another script

spool /tmp/synonym.sql
select distinct 'create synonym "'||object_name||'" for '||owner||'."'||object_n
ame||'";' from dba_objects where owner = 'APPS' ;
spool off

The above process worked fine always and seemed like a fairly tale.. until one fine day..

The Mess up
It all looked OK that day until a poor soul did this.

The drop synonym script was executed from the APPS schema instead of the APPS read only schema. The result.. All the  synonyms from the APPS user got knocked off. Everything in Oracle Applications came crashing down.

(This is the part where my pager starts beeping..)

After we realized what had happened the first thought to strike my mind was to run adadmin and re create the grants and  synonyms. But as expected adadmin also refused to work in the absence of the apps synonyms. Classic!!

The Quick Fix
We did have multiple environments of oracle applications at this particular client so we just made up a script to create all the synonyms again from a working similar instance. We this script in the APPS schema of the working environment.

spool fixsynonym.sql
select 'create synonym "'||synonym_name||'" for '||table_owner||'."'||table_name||'";' from user_synonyms;
spool off

Next we ran the fixsynonym.sql from the apps schema of the environment which had all its synonyms deleted.
Once done we were able to get back almost all of our synonyms and atleast were able to get adadmin working back again.

Next we ran adadmin and selected to recreate the grants and sysnonyms for the APPS Schema.
We did a clean shutdown of the instance and then brought it up again.. and it was back to perfect.