How to resolve "ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found" error that is observed when introspecting an Oracle Database?

How to resolve "ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found" error that is observed when introspecting an Oracle Database?

book

Article ID: KB0071142

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All supported versions

Description

This article lists the possible solutions to resolve the "ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found" error that is observed when introspecting an Oracle Database from TIBCO Data Virtualization Studio.

Complete error stack trace: 

ERROR [GetIntrospectableResourceIdsTask Get Introspectable Resource Identifiers] 2023-08-16 19:11:05.909 +0000 TaskManager - Task failed: GetIntrospectableResourceIdsTask Get Introspectable Resource Identifiers
com.compositesw.common.CompositeRuntimeException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at com.compositesw.server.task.TaskWithResults.run(TaskWithResults.java:87) ~[csserver.jar:?]
at com.compositesw.server.task.TaskManager$TaskRunner.run(TaskManager.java:146) [csserver.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: com.compositesw.common.CompositeRuntimeException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at com.compositesw.server.api.core.DataSourceApi.internalGetIntrospectableResourceIds(DataSourceApi.java:1298) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.task.GetIntrospectableResourceIdsTask.doTaskWithResultsRun(GetIntrospectableResourceIdsTask.java:58) ~[csserver.jar:?]
at com.compositesw.server.task.TaskWithResults.run(TaskWithResults.java:77) ~[csserver.jar:?]
... 4 more
Caused by: com.compositesw.common.api.ApiException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at com.compositesw.server.api.core.DataSourceApi.editDataSource(DataSourceApi.java:925) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.setThreadLocalDataSource(IntrospectionHandler.java:136) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.ensureDataSource(IntrospectionHandler.java:116) ~[csserver.jar:?]
at com.compositesw.server.api.core.DataSourceApi.internalGetIntrospectableResourceIds(DataSourceApi.java:1295) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.task.GetIntrospectableResourceIdsTask.doTaskWithResultsRun(GetIntrospectableResourceIdsTask.java:58) ~[csserver.jar:?]
at com.compositesw.server.task.TaskWithResults.run(TaskWithResults.java:77) ~[csserver.jar:?]
... 4 more
Caused by: com.compositesw.cdms.datasource.DataSourceException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at com.compositesw.cdms.datasource.PhysicalConnectionPoolManager.createPool(PhysicalConnectionPoolManager.java:114) ~[csserver.jar:?]
at com.compositesw.cdms.ds.jdbc.AbstractRelationalDataSource.checkResources(AbstractRelationalDataSource.java:472) ~[csmodule_datasource.jar:?]
at com.compositesw.cdms.datasource.DefaultDataSourceManager.editDataSource(DefaultDataSourceManager.java:387) ~[csmodule_datasource.jar:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
at com.compositesw.common.services.ServiceResolver$1.invoke(ServiceResolver.java:18) ~[cscommon.jar:?]
at com.sun.proxy.$Proxy23.editDataSource(Unknown Source) ~[?:?]
at com.compositesw.server.api.core.DataSourceApi.editDataSource(DataSourceApi.java:923) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.setThreadLocalDataSource(IntrospectionHandler.java:136) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.ensureDataSource(IntrospectionHandler.java:116) ~[csserver.jar:?]
at com.compositesw.server.api.core.DataSourceApi.internalGetIntrospectableResourceIds(DataSourceApi.java:1295) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.task.GetIntrospectableResourceIdsTask.doTaskWithResultsRun(GetIntrospectableResourceIdsTask.java:58) ~[csserver.jar:?]
at com.compositesw.server.task.TaskWithResults.run(TaskWithResults.java:77) ~[csserver.jar:?]
... 4 more
Caused by: com.compositesw.common.pool.PoolableInvalidException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at com.compositesw.common.pool.jdbc.JdbcConnectionFactory.create(JdbcConnectionFactory.java:136) ~[csserver.jar:?]
at com.compositesw.cdms.ds.jdbc.JdbcDataSource.getPoolFactoryWithValidation(JdbcDataSource.java:213) ~[csmodule_datasource.jar:?]
at com.compositesw.cdms.ds.jdbc.JdbcDataSource.getPoolFactoryWithValidation(JdbcDataSource.java:54) ~[csmodule_datasource.jar:?]
at com.compositesw.cdms.datasource.PhysicalConnectionPoolManager.createPool(PhysicalConnectionPoolManager.java:96) ~[csserver.jar:?]
at com.compositesw.cdms.ds.jdbc.AbstractRelationalDataSource.checkResources(AbstractRelationalDataSource.java:472) ~[csmodule_datasource.jar:?]
at com.compositesw.cdms.datasource.DefaultDataSourceManager.editDataSource(DefaultDataSourceManager.java:387) ~[csmodule_datasource.jar:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
at com.compositesw.common.services.ServiceResolver$1.invoke(ServiceResolver.java:18) ~[cscommon.jar:?]
at com.sun.proxy.$Proxy23.editDataSource(Unknown Source) ~[?:?]
at com.compositesw.server.api.core.DataSourceApi.editDataSource(DataSourceApi.java:923) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.setThreadLocalDataSource(IntrospectionHandler.java:136) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.ensureDataSource(IntrospectionHandler.java:116) ~[csserver.jar:?]
at com.compositesw.server.api.core.DataSourceApi.internalGetIntrospectableResourceIds(DataSourceApi.java:1295) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.task.GetIntrospectableResourceIdsTask.doTaskWithResultsRun(GetIntrospectableResourceIdsTask.java:58) ~[csserver.jar:?]
at com.compositesw.server.task.TaskWithResults.run(TaskWithResults.java:77) ~[csserver.jar:?]
... 4 more
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[?:?]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:456) ~[?:?]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:451) ~[?:?]
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1123) ~[?:?]
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:552) ~[?:?]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[?:?]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[?:?]
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:501) ~[?:?]
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1292) ~[?:?]
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1025) ~[?:?]
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:747) ~[?:?]
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793) ~[?:?]
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57) ~[?:?]
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747) ~[?:?]
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562) ~[?:?]
at com.compositesw.common.pool.jdbc.JdbcConnectionInitiator.createConnection(JdbcConnectionInitiator.java:60) ~[csserver.jar:?]
at com.compositesw.common.pool.jdbc.JdbcConnectionInitiator.initiateConnection(JdbcConnectionInitiator.java:50) ~[csserver.jar:?]
at com.compositesw.cdms.ds.oracle.OracleDataSource$1.initiateConnection(OracleDataSource.java:433) ~[?:?]
at com.compositesw.common.pool.jdbc.JdbcConnectionFactory.create(JdbcConnectionFactory.java:104) ~[csserver.jar:?]
at com.compositesw.cdms.ds.jdbc.JdbcDataSource.getPoolFactoryWithValidation(JdbcDataSource.java:213) ~[csmodule_datasource.jar:?]
at com.compositesw.cdms.ds.jdbc.JdbcDataSource.getPoolFactoryWithValidation(JdbcDataSource.java:54) ~[csmodule_datasource.jar:?]
at com.compositesw.cdms.datasource.PhysicalConnectionPoolManager.createPool(PhysicalConnectionPoolManager.java:96) ~[csserver.jar:?]
at com.compositesw.cdms.ds.jdbc.AbstractRelationalDataSource.checkResources(AbstractRelationalDataSource.java:472) ~[csmodule_datasource.jar:?]
at com.compositesw.cdms.datasource.DefaultDataSourceManager.editDataSource(DefaultDataSourceManager.java:387) ~[csmodule_datasource.jar:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
at com.compositesw.common.services.ServiceResolver$1.invoke(ServiceResolver.java:18) ~[cscommon.jar:?]
at com.sun.proxy.$Proxy23.editDataSource(Unknown Source) ~[?:?]
at com.compositesw.server.api.core.DataSourceApi.editDataSource(DataSourceApi.java:923) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.setThreadLocalDataSource(IntrospectionHandler.java:136) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.IntrospectionHandler.ensureDataSource(IntrospectionHandler.java:116) ~[csserver.jar:?]
at com.compositesw.server.api.core.DataSourceApi.internalGetIntrospectableResourceIds(DataSourceApi.java:1295) ~[csserver.jar:?]
at com.compositesw.cdms.datasource.introspect.task.GetIntrospectableResourceIdsTask.doTaskWithResultsRun(GetIntrospectableResourceIdsTask.java:58) ~[csserver.jar:?]
at com.compositesw.server.task.TaskWithResults.run(TaskWithResults.java:77) ~[csserver.jar:?]
... 4 more
-----------------------

Issue/Introduction

How to resolve "ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found" error that is observed when introspecting an Oracle Database?

Resolution

Solution 1: To work for a particular Oracle data source configured in the TDV Studio - 
Add the property “oracle.jdbc.timezoneAsRegion= false” in the JDBC connection properties on the Oracle adapter in the TDV Studio.
This property has to be added to all the Oracle data sources created in the TDV Studio that fail with the mentioned error. 

Solution 2: To work for all the Oracle data sources configured in the TDV Studio - 
Add JVM flag -Doracle.jdbc.timezoneAsRegion=false to the TDV JVM arguments using the script_env.sh/bat file.
Note: Reach out to the Support team if more help is needed for this option

Additional Information

Here is the KB to add a new JVM argument to the TIBCO Data Virtualization server hosted on Linux. For this error, we would need to add -Doracle.jdbc.timezoneAsRegion=false to the TDV JVM arguments. 
for TDV hosted on Linux - KB: https://support.tibco.com/s/article/000050777
 
Powered by Wolken Software