Today I hit an issue upgrading our database from 10.2.0.4 to 22.214.171.124.
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:
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.
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?