ORA-12705 and ORA-00604 error in production

Question

We have the following ORA error where it prints the error message in different language. This comes very rarely and we will have to restart our application servers. In my analysis I could figured out that, while connecting to database JDBC is internally altering the session by taking user's locale like below

ALTER SESSION SET TIME_ZONE='America/Los_Angeles' NLS_LANGUAGE='LATIN AMERICAN SPANISH' NLS_TERRITORY='LAOS'

Oracle team says, there is no language exists called LATIN AMERICAN SPANISH, please check from application side where is it setting. We have hard times to figure out where it is happening.

Outline of my project, It's a web application developed using following tech stack. 1. Oracle 11g 2. Spring MVC 3.2.3 3. Hibernate 3.5.6 4. JBOSS application servers with clustering.

The following are the logs extracted from DB team side.

*** 2016-05-26 05:23:40.831
*** SESSION ID:(58.51587) 2016-05-26 05:23:40.831
*** CLIENT ID:() 2016-05-26 05:23:40.831
*** SERVICE NAME:(NACCO) 2016-05-26 05:23:40.831
*** MODULE NAME:(JDBC Thin Client) 2016-05-26 05:23:40.831
*** ACTION NAME:() 2016-05-26 05:23:40.831

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3,    mask=0x0)
----- Error Stack Dump -----
ORA-12705: No se puede acceder a los archivos de datos NLS o se ha especificado un entorno no vÃÂ¡lido
----- Current SQL Statement for this session (sql_id=b6m5bkmu49p0j) -----
ALTER SESSION SET TIME_ZONE='America/Los_Angeles' NLS_LANGUAGE='LATIN  AMERICAN SPANISH' NLS_TERRITORY='LAOS'

We are having very hard times in figuring out the root cause and fixing it. If any one can help us in fixing or reproducing this issue locally will be appreciated. Thanks in advance.

Exception stack trace for reference

java.lang.Exception: Exception Identifier : 7843441347475577703, java.sql.SQLException: ORA-00604: se ha producido un error a nivel 1 de SQL recursivo

ORA-12705: No se puede acceder a los archivos de datos NLS o se ha especificado un entorno no válido

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:392)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:385)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1018)
    at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:497)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:433)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:950)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:639)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
    at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:172)
    at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:619)
    at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:264)
    at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:575)
    at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:347)
    at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:330)
    at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:402)
    at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:849)
    at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)
    at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
    at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
    at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1463)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at com.sun.proxy.$Proxy159.findUserByName(Unknown Source)
    at tavant.twms.web.actions.ManageSSO.createOrUpdateUser(ManageSSO.java:112)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:453)
    at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:292)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:255)
    at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265)
    at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at tavant.twms.interceptor.TrimListByElementIdInterceptor.intercept(TrimListByElementIdInterceptor.java:44)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:211)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:190)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:90)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at tavant.twms.interceptor.TwmsFileUploadInterceptor.intercept(TwmsFileUploadInterceptor.java:44)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at tavant.twms.interceptor.BUSpecificLabelsLocaleInterceptor.intercept(BUSpecificLabelsLocaleInterceptor.java:109)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:192)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
    at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)
    at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:511)
    at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
    at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at tavant.twms.filter.ResponseTimeLogFilter.doFilter(ResponseTimeLogFilter.java:90)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:143)
    at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at tavant.twms.filter.SelectedBusinessUnitsFilter.doFilter(SelectedBusinessUnitsFilter.java:39)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
    at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)
    at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
    at java.lang.Thread.run(Thread.java:662)

All, We have the following system properties are being set in all my production servers, we are still facing this issue though.

user.language: en
user.country: US
user.timezone: America/Los_Angeles

Any help will be appreciated.

Thanks Naveen

Deinum - we are not altering the session. Hibernate/JDBC from application is altering it internally. [link](http://stackoverflow.com/a/35673927/5559722) — Naveen, Jun 21 '16 at 08:52
I'm assuming a server application with a connection pool, thus nothing should change. Why would a get connect alter the session with the locale? I would say currently there is too little information (add the full stack trace for instance). Also what has Spring and/or hibernate to do with this? If that is so important add some of that configuration. — M. Deinum, Jun 21 '16 at 08:56
Thanks for reply Deinum!. I've updated the stack trace with full details. This issue is sporadic and start comes first from EMEA(Europe) region and effects all other regions. We have had multiple follow ups with Oracle support teams and they said it is happening from application side. First log says the same too. I could figured out in the link provided in my previous comment helped me that JDBC/Hibernate alter session before getting connection. BTW our severs are in PDT region. — Naveen, Jun 21 '16 at 09:04
I don't see why that would happen, especially if yu have a connection pool configured on the server side. Add some configuration/code as currently there is way too little information. — M. Deinum, Jun 21 '16 at 09:06
Yes Deinum you are right, Server application with connection pool. — Naveen, Jun 21 '16 at 09:06
Hmm appears to be default oracle behavior for a logon (which could occur when the pool has all its connections used and needs to scale up). You can override/add a trigger for after the logon the change the NLS stuff to something default instead of the one you have now. — M. Deinum, Jun 21 '16 at 09:16
Appreciate your time Deinum. Would you please let me know where can I change the NLS stuff. I am thinking to put these values in environmental variable of OS or sending as VM options in wrapper conf. Please let me know if I am thinking in right way. Thanks — Naveen, Jun 21 '16 at 09:29
Do some googling, there are different solutions, also you won't be able to solve it on the java side, you have to fix it on the DB side by setting up the DB correctly. — M. Deinum, Jun 21 '16 at 09:30
Check this link. It might be helpful https://community.oracle.com/thread/2212427 — phonetic_man, Jun 21 '16 at 09:55
Try to set proper NLS_LANG environment variable on the application server side. See this for details: http://www.orafaq.com/wiki/NLS_LANG. If it is not explicitely set, then JDBC is borrowing language settings directly from the operating system (Windows or Linux ?). — krokodilko, Jun 21 '16 at 10:09

score 2 · Answer 1 · answered Jun 21 '16 at 13:15

2

When establishing a connection to the Database, the Oracle JDBC thin driver looks up the default Java Locale, retrieves the language and territory and executes an ALTER SESSION to set NLS_LANGUAGE and NLS_TERRITORY.

This works quite well for me: I'm French, I live in Paris and when I connect to a Database in the US, I get the error messages in French automatically and that's because my Locale is set to french/France.

The thin driver calls Locale.getDefault(Category.FORMAT) and from there figures out the language and territory.

I tried to set my Locale to es/419 by doing this Locale.setDefault(new Locale("es","419")); and I can see that the driver executes the following query:

ALTER SESSION SET TIME_ZONE='Europe/Paris' NLS_LANGUAGE='LATIN AMERICAN SPANISH' NLS_TERRITORY='AMERICA'

(We can ignore the time_zone)

This seams to work fine in my setup. If it doesn't in yours maybe you can try a different language. You can change the default language by setting the user.language and user.country system properties when you start your Java runtime.

For example if I do this:

java -Duser.language=es -Duser.country=ES ...

then the driver executes the following query when it connects:

ALTER SESSION SET TIME_ZONE='Europe/Paris' NLS_LANGUAGE='SPANISH' NLS_TERRITORY='SPAIN'

es_ES must be very common and I'd be shocked if it didn't work in your environment.

I understand that you use JBOSS so these two -D system properties should be set in the command line that starts JBOSS. Note that if you prefer you can also achieve the same thing from within your code by calling Locale.setDefault.

Good luck!

answered Jun 21 '16 at 13:15

Jean de Lavarene

3,461
1
20
28

Thank you so much @Jean for detail explanation. I have got the list of languages and countries from oracle support team where I figured out that there was a problem with territory. ** ALTER SESSION SET TIME_ZONE='America/Los_Angeles' NLS_LANGUAGE='LATIN AMERICAN SPANISH' NLS_TERRITORY='LAOS'**. There is no territory called as LAOS from oracle side. I will try out your suggestion and keep posted. – Naveen Jun 22 '16 at 06:17
FYI It tried with `-Duser.language=es -Duser.country=LA` and it executes `ALTER SESSION SET TIME_ZONE='Europe/Paris' NLS_LANGUAGE='LATIN AMERICAN SPANISH' NLS_TERRITORY='LAOS' ` successfully on my 12.1.0.2 database. A workaround in your case would be to alter the territory with one that works. – Jean de Lavarene Jun 22 '16 at 08:16
Thank you Jean. I am just checking how to set the territory, if you have any good hands please let me know. – Naveen Jun 22 '16 at 14:19
The system property `-Duser.country` sets the country in the Locale (for example `java -Duser.country=LA ...` for Laos) which is then used by the driver to set NLS_TERRITORY. There is a list here: http://mindprod.com/jgloss/countrycodes.html – Jean de Lavarene Jun 22 '16 at 14:46
Jean, I've checked in my production servers and I could see all the above values are set already and still we are getting this issue. – Naveen Jul 05 '16 at 09:25
Have you tried a different territory to see if it makes a difference?`-Duser.country=VN` (for Vietnam). – Jean de Lavarene Jul 06 '16 at 14:27
Jean, I can not do that change right away, I would be able to do that change in the next production release. I will try your suggestion and let you know. Thanks for your time. – Naveen Jul 08 '16 at 05:52

ORA-12705 and ORA-00604 error in production

1 Answers1