Thursday

EBS: DB Upgrade to 11gR2 – Autoconfig Fails

Today I hit an issue upgrading our database from 10.2.0.4 to 11.2.0.1.  

The main metalink note which details the steps needed is: Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]


Step 22 involves implementing autoconfig in the new database home.  However, when I ran $ORACLE_HOME/appsutil/bin/adconfig.sh it would fail


Checking the logfile I found that afdbprf.sh fails with ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Metalink has an article which discusses the issue:


ORA-12504 When Using (HOSTNAME) Method For 11G Client/Database [ID 556996.1]


The note goes into a fair bit of detail about why this error is happening and how to resolve it.  In a nutshell, 11g expects the service name to be specified in the connect string.  If one isn’t specified then it uses the default service name specified at the listener level.   If the listener is not configured with a default then an error is thrown: 


ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Previous to 11g, if you dídn’t specify the service name, then the connect string alias was used instead.  In the case of the following connection string, VIS is the alias:


sqlplus apps/pass@VIS


The solution is to configure listener with a default service name using the DEFAULT_SERVICE_listener_name parameter.   I added the following to my listener.ora ifile, reloaded the listener and re-ran adconfig.sh successfully. Note: if you add it directly to the listener.ora file and not the ifile, then the change will be lost when your run adconfig.sh.


DEFAULT_SERVICE_VIS=(VIS)


Since this is an EBS environment I always search to make sure there are no issues but I was surprised to find nothing.   I’m wondering if I missed something in the upgrade steps.  Have you upgraded to 11g?  Did you hit this issue?

2 comments:

Anonymous said...

I experienced same sort of issue with 11.2.0.2 DB and 12.1.3. Autoconfig decides to create an oaprd entry with no CONNECT_DATA after the valid one. sqlplus picks up the second version and doesn't know how to connect.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.