Customer is using OD 7.0 and are facing issues with DB failover when the DB server is down as described below.

==================================================================================


We have configured the source and failover database context as per the Avaya document. As per expected functionality the OD application should connect to failover database in case the primary database is down. Also the point to note here is that the same configuration is working when only the database services are stopped on primary DB and server is UP. The OD application automatically points to the failover database. However when the primary server itself is down the OD application does not switch to failover database.
==============================================================================

I could see the below error in the application logs.

28/06/2015 00:45:35:938 INFO - 858392DBC1072F0A3D60B320C5AF0858:/UAE_MAIN_APP : Assigning [OD_CALL_LOG_MASTER:AGENT] to [Insert_Call_Log_Master_Report:AGENT]
28/06/2015 00:45:35:938 DEBUG - 858392DBC1072F0A3D60B320C5AF0858:/UAE_MAIN_APP : session id:AEXVIPDXBMAEP01-2015178204311-23 | UAE_MAIN_APP:Table_A_Call_Log_Master | /////////////////////////////////////////AGENT. //////////////////////////// | OD_CALL_LOG_MASTER:AGENT : 1111
28/06/2015 00:45:35:938 INFO - 858392DBC1072F0A3D60B320C5AF0858:/UAE_MAIN_APP : Assigning [OD_CALL_LOG_MASTER:BANK_CARD] to [Insert_Call_Log_Master_Report:BANK_CARD]
28/06/2015 00:45:35:938 DEBUG - 858392DBC1072F0A3D60B320C5AF0858:/UAE_MAIN_APP : session id:AEXVIPDXBMAEP01-2015178204311-23 | UAE_MAIN_APP:Table_A_Call_Log_Master | /////////////////////////////////////////BANK/CARD //////////////////////////// | OD_CALL_LOG_MASTER:BANK_CARD :
28/06/2015 00:45:35:938 DEBUG - 858392DBC1072F0A3D60B320C5AF0858:/UAE_MAIN_APP : Executing DB statement : [INSERT INTO SCBIVR_AE.CALL_LOG_MASTER_REPORT ( CLI, DNIS, LANG, EXIT_VDN, IVR_HOST, STARTTIME, ENDTIME, UCID, RMN, ACCOUNT_CARD, DISPOSITION_TYPE, DISPOSITION_LOCATION, PRODUCT_ID, TRNF_DISC_CATEGORY, BANK_CARD, LOCATION, AGENT ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )]
28/06/2015 00:45:36:827 ERROR - AAB3F6C4DA6062DA539083F58DB0FFF7:/UAE_MAIN_APP : Error executing sql. Exception : java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
28/06/2015 00:45:36:827 DEBUG - AAB3F6C4DA6062DA539083F58DB0FFF7:/UAE_MAIN_APP : Execution fails over to data source - DB_Source_Failover
28/06/2015 00:45:37:420 DEBUG - AAB3F6C4DA6062DA539083F58DB0FFF7:/UAE_MAIN_APP : Rows affected : [1]

====================================================================

Attached the application trace.

Many thanks for the help in advance!

I am not sure how to open the gsd files in the zip file attached. The log you copied here is the working one right? The application level fail over can only be triggered when there is an exception returned from executing a query against the primary data source. It depends on the jdbc driver. I suspect when the host server is down, there was no exception thrown. If you have a server down issue, I suggest you look for a solution provided by the database server instead. That's a more prudent way to handle fail over at the server level.

Hi Wilson,

Thank you for the response!

The files can be opened with Notepad or Notepad++, its just a normal log file.

the exception snippet I shared in the above post is for the "NON-Working" scenario. As you can see, even after receiving the exception, the DB is not switching if the DB server is down as a whole.

Does our OD react to only DB services down but not the whole server down scenario?

I forgot to mention the files in the zip file were password protected. I could not open it.

Now you got me confused. The logs you posted (below) actually means the execution fails over to a different datasource and finished successfully.

28/06/2015 00:45:36:827 ERROR - AAB3F6C4DA6062DA539083F58DB0FFF7:/UAE_MAIN_APP : Error executing sql. Exception : java.sql.SQLException: Io exception: The Network Adapter could not establish the connection

28/06/2015 00:45:36:827 DEBUG - AAB3F6C4DA6062DA539083F58DB0FFF7:/UAE_MAIN_APP : Execution fails over to data source - DB_Source_Failover

28/06/2015 00:45:37:420 DEBUG - AAB3F6C4DA6062DA539083F58DB0FFF7:/UAE_MAIN_APP : Rows affected : [1]

The password is "scb".

The logs I had posted is for the non working scenario. Where the customer had shutdown the DB and the "DB failover" data source is the one they had configured as a failover. At this point, it is throwing the exception but the DB is not switching to the failover DB.

As I said the logs you posted is a working scenario.

Below is something from the logs you attached. It show failover happened - Execution fails over to data source - DB_Source_Failover; however the 2nd datasource didn't work either. I seems some networking problem.

ORA-06512: at "SCBIVR_AE.SS_SMS_ALERT", line 12
ORA-06512: at line 1

27/06/2015 22:44:46:572 DEBUG - 71C3D97B0FCF8CA03ACA39544A5B538B:/UAE_MAIN_APP : Execution fails over to data source - DB_Source_Failover
27/06/2015 22:44:46:978 ERROR - 71C3D97B0FCF8CA03ACA39544A5B538B:/UAE_MAIN_APP : Error executing sql. Exception : 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

27/06/2015 22:44:46:978 INFO - 71C3D97B0FCF8CA03ACA39544A5B538B:/UAE_MAIN_APP : Capturing exception [com.avaya.sce.runtimecommon.SCERuntimeException]. Message [Database exceution failed. Failovers attempted.]
27/06/2015 22:44:46:978 DEBUG - 71C3D97B0FCF8CA03ACA39544A5B538B:/UAE_MAIN_APP : session id:AEXVIPDXBMAEP01-2015178184259-5 | UAE_SecuredService:DC_PIN_CREATION-Dat_UpdateSMSAlert | ::::::::::::::Error in SMS PROCESS Operation:::::::::::::: | ddLastException:stacktrace : com.avaya.sce.runtimecommon.SCERuntimeException: Database exceution failed. Failovers attempted.
at com.avaya.sce.runtimecommon.SCESession.throwRTException(SCESession.java:2316)
at com.avaya.sce.runtime.connectivity.db.DbQuery.execute(DbQuery.java:165)
at com.avaya.sce.runtime.Data.evaluateActions(Data.java:191)
at flow.subflow.DC_PIN_CREATION.Dat_UpdateSMSAlert.executeDataActions(Dat_UpdateSMSAlert.java:145)
at com.avaya.sce.runtime.Data.handleRequest(Data.java:104)
at com.avaya.sce.runtime.AppServlet.processRequest(AppServlet.java:96)
at com.avaya.sce.runtime.SCEServlet.requestHandler(SCEServlet.java:285)
at com.avaya.sce.runtime.SCEServlet.doGet(SCEServlet.java:182)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

Dear Team,

I want to highlight following things once again,

DB failover working scenario

1. if the production DB services are stopped then IVR application is communicating with failover DB.
2. through web browser as well we can validate the failover DB source


DB failover Not Working Scenario (for same application)
1. if the production DB is not in network (shut down) then IVR application is not communicating with failover DB (our issue)
2. at the same time if we manually validate through browser then we are able to validate the failover DB through validation option in application.

Regards,
Nilesh
+91-9920775766

As I pointed out earlier, there is no evidence in the logs that the primary data source didn't fail over. You need to give me some logs to prove that.

Hi Wilson,

Thanks for the response, actually in good call we cant see the DB source,

good call Log
--------------------------------------------------------------
27/06/2015 22:36:52:526 DEBUG - 0640449C29BEF4257818EBBCDBA920AF:/UAE_MAIN_APP : Executing DB statement : [INSERT INTO SCBIVR_AE.CALL_LOG_MASTER_REPORT ( CLI, DNIS, LANG, EXIT_VDN, IVR_HOST, STARTTIME, ENDTIME, UCID, RMN, ACCOUNT_CARD, DISPOSITION_TYPE, DISPOSITION_LOCATION, PRODUCT_ID, TRNF_DISC_CATEGORY, BANK_CARD, LOCATION, AGENT ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )]
27/06/2015 22:36:52:775 DEBUG - 0640449C29BEF4257818EBBCDBA920AF:/UAE_MAIN_APP : Rows affected : [1]
27/06/2015 22:36:52:775 DEBUG - 0640449C29BEF4257818EBBCDBA920AF:/UAE_MAIN_APP : session id:AEXVIPDXBMAEP01-2015178183338-10 | UAE_MAIN_APP:Account_Sel_Contd-Table_A_Call_Log_Master | ///////////////////////////////// Successful Insert //////////////////
---------------------------------------------------------------------------------------------

Bad call Log

--------------------------------------------------------------------------------------
28/06/2015 00:52:58:050 DEBUG - F2E863A55BB949B2B88DFDBA2D94E63F:/UAE_MAIN_APP : Executing DB statement : [SELECT EXIT_VDN.CALLFLOW, EXIT_VDN.PRODUCT_ID, EXIT_VDN.LANGUAGE, EXIT_VDN.VDN, EXIT_VDN.REGION FROM SCBIVR_AE.EXIT_VDN WHERE ( ( ( EXIT_VDN.CALLFLOW = ? ) AND ( EXIT_VDN.PRODUCT_ID = ? ) ) AND ( ( EXIT_VDN.REGION = ? ) AND ( EXIT_VDN.LANGUAGE = ? ) ) )]
28/06/2015 00:52:58:378 ERROR - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Error executing sql. Exception : java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
28/06/2015 00:52:58:378 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Execution fails over to data source - DB_Source_Failover
28/06/2015 00:52:59:189 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : set value to variable/field - MESSAGE_ID
28/06/2015 00:52:59:189 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : set value to variable/field - IVR_EMERGENCY_MESSAGE
28/06/2015 00:52:59:189 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Rows affected : [1]
28/06/2015 00:52:59:189 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : session id:AEXVIPDXBMAEP01-2015178203429-13 | UAE_MAIN_APP:Fetch_ExitVDN | /////////////////////////////// Emergency Msg Details ////////// | Emergency_Msg:IVR_EMERGENCY_MESSAGE : D
28/06/2015 00:52:59:189 INFO - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Assigning [Emergency_Msg:IVR_EMERGENCY_MESSAGE] to [Details(L)]
28/06/2015 00:52:59:189 INFO - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Capturing exception [javax.servlet.ServletException]. Message [ EXCEPTION>
java.lang.NullPointerException
at com.avaya.sce.runtime.varoperations.Assign.evaluate(Assign.java:48)
at com.avaya.sce.runtime.Data.evaluateActions(Data.java:218)
at flow.Fetch_Fest_Msg_Details.executeDataActions(Fetch_Fest_Msg_Details.java:103)
at com.avaya.sce.runtime.Data.handleRequest(Data.java:104)
at com.avaya.sce.runtime.AppServlet.processRequest(AppServlet.java:96)
at com.avaya.sce.runtime.SCEServlet.requestHandler(SCEServlet.java:285)
at com.avaya.sce.runtime.SCEServlet.doGet(SCEServlet.java:182)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:486)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:411)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:338)
at com.avaya.sce.runtime.SCEServlet.forward(SCEServlet.java:1364)
at com.avaya.sce.runtime.Data.handleRequest(Data.java:153)
at com.avaya.sce.runtime.AppServlet.processRequest(AppServlet.java:96)
at com.avaya.sce.runtime.SCEServlet.requestHandler(SCEServlet.java:285)
at com.avaya.sce.runtime.SCEServlet.doGet(SCEServlet.java:182)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
]
28/06/2015 00:52:59:220 INFO - 1F1734BD64C50DC0708CF2467507A555:/UAE_MAIN_APP : Using SCESession 1F1734BD64C50DC0708CF2467507A555:/UAE_MAIN_APP servlet : Ann_Sys_Unable
28/06/2015 00:52:59:220 DEBUG - 1F1734BD64C50DC0708CF2467507A555:/UAE_MAIN_APP : *** Reply for [/UAE_MAIN_APP/Ann_Sys_Unable] ***
28/06/2015 00:52:59:220 DEBUG - 1F1734BD64C50DC0708CF2467507A555:/UAE_MAIN_APP : 0:<?xml version="1.0" encoding="UTF-8"?>
1:<vxml version="2.1" xmlns="http://www.w3.org/2001/vxml" xml:lang="en-us">
2:<meta name="author" content="Avaya Aura Orchestration Designer"/>
3:<meta name="runtime-version" content="07.01.08.04"/>
4:<meta name="runtimecommon-version" content="07.01.08.04"/>
5:<meta name="copyright" content="Copyright (c) 2002-2011, Avaya"/>
6:<form id="Ann_Sys_Unable">
7:<block>
8:<prompt bargein="true" bargeintype="speech" timeout="8000ms">
9:<audio src="http://10.145.170.38:8080/UAE_MAIN_APP/data/english/phrases/Sys_Unavailable.wav"/>
10:</prompt>
11:</block>
12:<block>
13:<submit next="Play_Xfer?___DDSESSIONID=1F1734BD64C50DC0708CF2467507A555%3A%2FUAE_MAIN_APP"/>
14:</block>
15:</form>
16:</vxml>
----------------------------------------------------------------------------------------------
so its normal if you are not getting the primary DB source in the logs since its not coming in good call as well.

let me know whether AEP 7.0 supports this feature or not (as per my previous post)

Regards,
Nilesh

How come you don't understand? The logs here means it fails over and the query got executed by saying "Rows affected : [1]"

28/06/2015 00:52:58:378 ERROR - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Error executing sql. Exception : java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
28/06/2015 00:52:58:378 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Execution fails over to data source - DB_Source_Failover
28/06/2015 00:52:59:189 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : set value to variable/field - MESSAGE_ID
28/06/2015 00:52:59:189 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : set value to variable/field - IVR_EMERGENCY_MESSAGE
28/06/2015 00:52:59:189 DEBUG - E2932BF8006AA4EA50BF2E25446CB9D9:/UAE_MAIN_APP : Rows affected : [1]

The null exception that follows means a different thing. Maybe the data is not correct which causes the exception.

Dear Team,

Thanks to reply on this post....
the issue is still there, you might be not clear or we are able to make you understand the issue.
i want to share few points which we are using for OD app context module having following parameters to set,

defaultMaxActive ------- 100
defaultMaxIdle ----------50
defaultMaxWait-----------30000
removeAbandoned----------true
removeAbandonedtimeout --60


when the production DB server is in network (reachable) and service is down then OD application is automatically switching to DR DB.
if the production DB server is shutdown (not reachable) then OD application is not automatically switching to DR.

I am suspecting the above parameters may cause to different behaviour in both case.
the removeAbandonedtimeout is for what and what should be the idle value for this parameter.

please rethink on this issue .


Regards,
Nilesh
+91-9920775766

I have already explained previously that since OD is doing everything at the app level, the DB failover feature is not a bulletproof solution and it depends on the sql exception to determine the failure of a db server. I suspect that in case of the server shutdown, it is not getting the exception. Therefore the execution is not failing over. However, I have not seen any evident in the logs you provided.
The paramters you mentioned are part of the Apache DBCP component on Tomcat. We don't have control over it. You can change the configuration according to the documentation provided in the link below:

https://commons.apache.org/proper/commons-dbcp/configuration.html