Bug Busted with Apps/EBS R12 Cloning
Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:ORA-12514, TNS:listener does not currently know of service requested in connect descriptor Connection could not be obtained; returning null
After having upgraded our EBS to 12.1.3, it was the time to have the test env bring up to the speed. Started cloning with hot backup and was running as usual until hit the following error when afcfgclone reached at 50%.
Error -
ADX Database Utility
---------------------------------------------------------------
getConnectionUsingAppsJDBCConnector() -->
APPS_JDBC_URL='null'
Trying to get connection using SID based connect descriptor
getConnection() -->
sDbHost : apps_rac01
sDbDomain : localdomain
sDbPort : 1521
sDbSid : visr12
sDbUser : apps!s
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@apps_rac01.localdomain:1521:visr12
Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apps_rac01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=visr12)))
Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apps_rac01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=visr12.localdomain)))
Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Connection could not be obtained; returning null
Analysis reveals that one needs to change the values of the target and keeping the same will result in failure around 50% and 70%.
Raised TAR/SR with oracle but No real help from My Oracle Support
Mean while also figured out that Oracle has also following bug under development
Bug 12598630 - 'R12 CLONING FAILS AT 50% DUE TO LISTENER.ORA CONTAINING SOURCE NAME'
after lot of head banging I found out that the issue is with handing off connection from listener as listener doesnt find any DB. Ideally the auto config start the db and hand off the connection but that was not happening here
I also found couple of other blogs suggesting that one should try to configure the techStack first and then try the database as different phase. I did try that one and phase 1 went fine. however the bad luck haunted again during configuring the DB. so back to square one again.
During research, I came across following solution on Oracle Forums and was claimed to be able to resolve the issue. Hence I decided to give it a go.
1. Edit $ORACLE_HOME/appsutil/clone/context/db/CTXORIG.xml (the original context from source)
2. Change the value for "s_db_listener" to the correct target system value (to the new target system value)
3. Shutdown Target Database
4. Rerun "perl adcfgclone.pl dbTier"
Oracle says -
The Context file ($ORACLE_HOME/appsutil/<SID>_<HOST>.xml) created during adcfgclone.pl contains the source value for the variable "s_db_listener". The CloneContext.log shows following :
...
PROMPT :
Target System Database SID
ANSWER :
<SID of the source Instance>
...
The root cause of this failure is a bug in "CloneContext.java", which fails to update the variable "s_db_listener" in the new (target) Context file. Instead the value stored in the file 'CTXORIG.xml' is used, which was created during the pre-cloning ("adpreclone.pl") steps on the source Instance.
Mean while also figured out that Oracle has also following bug under development for the above issue and it affects the versions from 12.1.3 and later. That's the version I'm running on. So that indeed was a bug giving me hard time.
Bug 12598630 - 'R12 CLONING FAILS AT 50% DUE TO LISTENER.ORA CONTAINING SOURCE NAME'
Since Oracle has provided the workaround, which was not working in my case, I was fairly disheartened. The only thing I could do now is to change the values as Oracle Support says and try it.
The thing that catches my eye was that almost everyone mentioned to change the name of the system/sid during cloning. I, actually, want to keep the same to have more real look and feel of the prod env. So I wanted to keep it same.
After a break of a day, I decided to look at things more closely and try few tricks and Voila... After couple of failed attempts I hit the pot. Following is the work around, which I think is pretty basic. Obviously someone rightly said, "Devil lies in details"
work around -
All the issues boils down to one simple thing and its about getting connection to DB. So what I did is start the DB before the running adcfgclone, in mount stage. so when the stage comes during apply database, listener doesn't have difficulty finding and connecting the DB.
ADX Database Utility
---------------------------------------------------------------
getConnectionUsingAppsJDBCConnector() -->
APPS_JDBC_URL='null'
Trying to get connection using SID based connect descriptor
getConnection() -->
sDbHost : apps_rac01
sDbDomain : localdomain
sDbPort : 1521
sDbSid : visr12
sDbUser : apps!s
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@apps_rac01.localdomain:1521:visr12
Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apps_rac01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=visr12)))
Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apps_rac01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=visr12.localdomain)))
Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Connection could not be obtained; returning null
Analysis reveals that one needs to change the values of the target and keeping the same will result in failure around 50% and 70%.
Raised TAR/SR with oracle but No real help from My Oracle Support
Mean while also figured out that Oracle has also following bug under development
Bug 12598630 - 'R12 CLONING FAILS AT 50% DUE TO LISTENER.ORA CONTAINING SOURCE NAME'
after lot of head banging I found out that the issue is with handing off connection from listener as listener doesnt find any DB. Ideally the auto config start the db and hand off the connection but that was not happening here
I also found couple of other blogs suggesting that one should try to configure the techStack first and then try the database as different phase. I did try that one and phase 1 went fine. however the bad luck haunted again during configuring the DB. so back to square one again.
During research, I came across following solution on Oracle Forums and was claimed to be able to resolve the issue. Hence I decided to give it a go.
1. Edit $ORACLE_HOME/appsutil/clone/context/db/CTXORIG.xml (the original context from source)
2. Change the value for "s_db_listener" to the correct target system value (to the new target system value)
3. Shutdown Target Database
4. Rerun "perl adcfgclone.pl dbTier"
Oracle says -
The Context file ($ORACLE_HOME/appsutil/<SID>_<HOST>.xml) created during adcfgclone.pl contains the source value for the variable "s_db_listener". The CloneContext.log shows following :
...
PROMPT :
Target System Database SID
ANSWER :
<SID of the source Instance>
...
The root cause of this failure is a bug in "CloneContext.java", which fails to update the variable "s_db_listener" in the new (target) Context file. Instead the value stored in the file 'CTXORIG.xml' is used, which was created during the pre-cloning ("adpreclone.pl") steps on the source Instance.
Mean while also figured out that Oracle has also following bug under development for the above issue and it affects the versions from 12.1.3 and later. That's the version I'm running on. So that indeed was a bug giving me hard time.
Bug 12598630 - 'R12 CLONING FAILS AT 50% DUE TO LISTENER.ORA CONTAINING SOURCE NAME'
| B - Defect | 12.1.3+ | ||
| 2 - Severe Loss of Service | 12.1.3 | ||
| 74 - Closed, Verified by QA |
Since Oracle has provided the workaround, which was not working in my case, I was fairly disheartened. The only thing I could do now is to change the values as Oracle Support says and try it.
The thing that catches my eye was that almost everyone mentioned to change the name of the system/sid during cloning. I, actually, want to keep the same to have more real look and feel of the prod env. So I wanted to keep it same.
After a break of a day, I decided to look at things more closely and try few tricks and Voila... After couple of failed attempts I hit the pot. Following is the work around, which I think is pretty basic. Obviously someone rightly said, "Devil lies in details"
work around -
All the issues boils down to one simple thing and its about getting connection to DB. So what I did is start the DB before the running adcfgclone, in mount stage. so when the stage comes during apply database, listener doesn't have difficulty finding and connecting the DB.
following is what my ApplyDBTier log says....
DEBUG: checkDBConnection in()
---------------------------------------------------------------
ADX Database Utility
---------------------------------------------------------------
getConnection() -->
sDbHost : apps_rac01
sDbDomain : localdomain
sDbPort : 1521
sDbSid : visr12
sDbUser : apps
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@apps_rac01.localdomain:1521:visr12
Connection obtained
-------------------ADX Database Utility Finished---------------
............................
............................
Executing txkcreateACL.sql ...
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 19 04:05:31 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected.
Checking for existence of the ACL ....
ACL "/sys/acls/OracleEBS.xml"already exists, checking for privilege ...
Already privileged ACL, no need to add privilege
Done !
PL/SQL procedure successfully completed.
Commit complete.
ApplyDatabase Completed Successfully.
Querying the DB from sqlplus shows as follows
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
VISR12 READ WRITE
--------- --------------------
VISR12 READ WRITE
Hope this will help you in over-coming your cloning issue without changing your target SID :)
wat's d solution then???
ReplyDelete