Security Software

I don't have any ideas, but you're not alone. I experienced a similar issue with ORDS and TNS starting with 21.2 (https://community.oracle.com/tech/developers/discussion/4487085/upgrade-from-ords-20-4-3-to-21-2-failing-with-tns-settings-on-windows) and so did someone else (https://community.oracle.com/mosc/discussion/4499937/ords-21-2-uninstall-issue-on-windows).
The person who made the second post referenced above tried to open an SR with Oracle but didn't get very far.
I was able to upgrade from 20.4.3 to 21.1.3, but I still can't figure out how to get 21.2 to work. I'm trying to figure out what would have changed that would have affected the TNS connection between those two versions, but no luck so far. It seems to not like the backslashes in the TNS_ADMIN path, but when I change them to forward slashes, they still get interpreted as backslashes.
Please post back if you figure out a workaround or solution with your situation - I will do the same. Thanks!

alacourb, User_Z4K9S
If you are using non-Oracle JDK(OpenJDK), this problem is known.
Share the complete stacktrace of the exception.
Thanks, Suresh

OpenJDK is owned by Oracle:
OpenJDK Trademark Notice (java.net)

OpenJDK (the "Name") is a trademark of Oracle America, Inc. ("Oracle")
(the "Trademark Owner").

The issue I'm having is using Oracle JDK 1.8 u311, but I don't want to hijack alacourb's thread so I'll keep the details about my particular issue in my own thread.
Curious to hear if alacourb is using Oracle's JDK or OpenJDK.

I'm using OpenJDK 17.
User_Z4K9S no issue if you share also your details, this will allow to have a better scope of the issue, thanks fo sharing.

Suresh-Sangaran-Oracle Here the full stacktrace as requested:
oracle.net.resolver.EZConnectResolver.parseExtendedProperties Extended settings parsing failed.
java.lang.RuntimeException: Invalid character at 24 : \
at oracle.net.resolver.EZConnectResolver.parseExtendedProperties(EZConnectResolver.java:428)
at oracle.net.resolver.EZConnectResolver.parseExtendedSettings(EZConnectResolver.java:401)
at oracle.net.resolver.EZConnectResolver.parse(EZConnectResolver.java:187)
at oracle.net.resolver.EZConnectResolver.<init>(EZConnectResolver.java:146)
at oracle.net.resolver.EZConnectResolver.newInstance(EZConnectResolver.java:155)
at oracle.jdbc.driver.OracleDriver.resolveNonSimpleURL(OracleDriver.java:860)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:657)
at oracle.jdbc.datasource.impl.OracleDataSource.getPhysicalConnection(OracleDataSource.java:579)
at oracle.jdbc.datasource.impl.OracleDataSource.getConnection(OracleDataSource.java:352)
at oracle.jdbc.datasource.impl.OracleDataSource.getConnectionInternal(OracleDataSource.java:2023)
at oracle.jdbc.datasource.impl.OracleDataSource.getConnection(OracleDataSource.java:325)
at oracle.jdbc.datasource.impl.OracleDataSource.getConnection(OracleDataSource.java:286)
at oracle.dbtools.common.config.db.OracleConnectionFactory._connection(OracleConnectionFactory.java:37)
at oracle.dbtools.common.config.db.ConnectionFactory.getConnection(ConnectionFactory.java:87)
at oracle.dbtools.common.jdbc.provider.HasConnection.getConnection(HasConnection.java:31)
at oracle.dbtools.common.config.db.ValidatedConnectionFactory.from(ValidatedConnectionFactory.java:99)
at oracle.dbtools.common.config.db.ConfiguredPoolDataSourceImplLoader.validatePool(ConfiguredPoolDataSourceImplLoader.java:323)
at oracle.dbtools.common.config.db.ConfiguredPoolDataSourceImplLoader.load(ConfiguredPoolDataSourceImplLoader.java:94)
at oracle.dbtools.common.config.db.ConfiguredPoolDataSourceLoaderImpl.load(ConfiguredPoolDataSourceLoaderImpl.java:16)
at oracle.dbtools.common.config.db.RefreshablePoolDataSourceFactory.load(RefreshablePoolDataSourceFactory.java:28)
at oracle.dbtools.common.config.db.DatabasePoolLoader.load(DatabasePoolLoader.java:35)
at oracle.dbtools.common.config.db.DatabasePoolLoader.load(DatabasePoolLoader.java:8)
at oracle.dbtools.common.config.db.ConfiguredPoolDataSourceLoader.load(ConfiguredPoolDataSourceLoader.java:40)
at oracle.dbtools.common.config.db.DatabasePoolLoader.load(DatabasePoolLoader.java:43)
at oracle.dbtools.common.config.db.DatabasePoolLoader.load(DatabasePoolLoader.java:8)
at oracle.dbtools.common.config.db.RefreshablePoolDataSources$PoolFromDataSourceIdentifierLoader.call(RefreshablePoolDataSources.java:144)
at oracle.dbtools.common.config.db.RefreshablePoolDataSources$PoolFromDataSourceIdentifierLoader.call(RefreshablePoolDataSources.java:133)
at oracle.dbtools.common.functions.Functions$3.get(Functions.java:41)
at oracle.dbtools.common.functions.MaybeValidImpl.load(MaybeValidImpl.java:217)
at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:20)
at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:14)
at oracle.dbtools.common.functions.MaybeValidImpl$LoaderImpl.load(MaybeValidImpl.java:282)
at oracle.dbtools.common.functions.MaybeValidCache$MaybeValidCacheLoader.call(MaybeValidCache.java:346)
at oracle.dbtools.common.functions.MaybeValidCache$MaybeValidCacheLoader.call(MaybeValidCache.java:335)
at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4864)
at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
at com.google.common.cache.LocalCache.get(LocalCache.java:3962)
at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4859)
at oracle.dbtools.common.functions.MaybeValidCache.get(MaybeValidCache.java:101)
at oracle.dbtools.common.functions.MaybeValidCache.get(MaybeValidCache.java:85)
at oracle.dbtools.common.config.db.RefreshablePoolDataSources.pool(RefreshablePoolDataSources.java:69)
at oracle.dbtools.common.config.db.DatabasePools.pool(DatabasePools.java:241)
at oracle.dbtools.common.config.db.DatabasePools.validity(DatabasePools.java:206)
at oracle.dbtools.common.config.db.DataSourceIdentifiers.lambda$validity$0(DataSourceIdentifiers.java:36)
at oracle.dbtools.common.functions.MaybeValidImpl.load(MaybeValidImpl.java:217)
at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:20)
at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:14)
at oracle.dbtools.common.functions.MaybeValidImpl$LoaderImpl.load(MaybeValidImpl.java:282)
at oracle.dbtools.common.functions.MaybeValidImpl$LoaderImpl.load(MaybeValidImpl.java:252)
at oracle.dbtools.common.config.db.DataSourceIdentifiers.validity(DataSourceIdentifiers.java:53)
at oracle.dbtools.common.config.db.DataSourceValidation.startup(DataSourceValidation.java:45)
at oracle.dbtools.common.app.PluginLifeCycles.startup(PluginLifeCycles.java:39)
at oracle.dbtools.common.app.ApplicationContext.<init>(ApplicationContext.java:110)
at oracle.dbtools.common.app.ApplicationContext.<init>(ApplicationContext.java:66)
at oracle.dbtools.common.app.ApplicationContext.using(ApplicationContext.java:230)
at oracle.dbtools.entrypoint.WebApplicationContext.<init>(WebApplicationContext.java:43)
at oracle.dbtools.entrypoint.WebApplicationContext.<init>(WebApplicationContext.java:31)
at oracle.dbtools.entrypoint.WebApplicationContext$Builder.build(WebApplicationContext.java:181)
at oracle.dbtools.entrypoint.WebApplicationEntryPoint.contextInitialized(WebApplicationEntryPoint.java:35)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4768)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5230)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:726)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:698)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:696)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1024)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1911)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.base/java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:123)
at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:825)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:475)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1618)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:319)
at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:423)
at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:366)
at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:946)
at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:835)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1396)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1386)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.base/java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:145)
at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:919)
at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:263)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.StandardService.startInternal(StandardService.java:432)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:927)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.startup.Catalina.start(Catalina.java:772)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:345)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:476)

And then I get also:
oracle.dbtools.common.logging.JDKLogSink.record IO Error: Invalid connection string format, a valid format is: "host:port:sid" (CONNECTION_ID=X8ADepYuRpK1p8FSAKp41Q==),|apex||-2021-10-26T20-46-37.212127400Z,jdbc:oracle:thin:@myDB?TNS_ADMIN=D:\App\Oracle\tns

Try with Oracle JDK 1.8.* latest version.
1.2.3 System Requirements
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/21.3/aelig/installing-REST-data-services.html#GUID-F6A4F94A-D62F-4A35-A471-6306332DF522

OpenJDK not technically supported, neither is 17 (yet) - suggest you try Oracle Java 8 or 11 and see if that helps.

alacourb,
I'd be curious to see your results if you're able to try it using the latest Oracle JDK 1.8. The stack trace you posted has some similarities to the one I get when trying to install an ORDS upgrade 21.2 or higher in my environment, which is running 1.8 update 311.

Suresh-Sangaran-Oracle thanks for your feedback. I did not use the Oracle Java because of the license fees. Could you please confirm if I can use it in my DEV environment for free, at least to validate the issue doesn't occur the same way?

there are no license fees
I published this in 2019, no customers have had any issues
https://www.thatjeffsmith.com/archive/2019/04/oracle-sql-developer-and-the-oracle-jdk-in-2019/

Thanks for your input thatJeffSmith-Oracle. I'm using APEX for more than 10 years and I was looking for a solution with minimal cost as we were used to have prior to ORDS and licensed Java.
I have done some other testing and OpenJDK 16 seems to work nicely with ORDS 21.1 (at least for the TNS part).
Looking forward for an official support of OpenJDK.

Adding to Jeff Smith's response, Java licenses are required when we know the ORDS problem is due to the Oracle JDK, and Support/Dev assistance is required to help with the Java issue. TNS is one of the many problems we have seen in OpenJDK with our customers.
Ensure to use only the softwares recommended by "System Requirements" provided in the product documentation. If you are looking for cost-effective check with OCI Automonous+APEX(inbuilt) if it works out for you.

thatJeffSmith-Oracle thanks for the license clarification, it wasn't clear to me that way.
What about running my Apache Tomcat with Oracle JDK? Is this also license free as it's only used to run ORDS or am I only allowed to use ORDS with embedded Jetty?
Suresh-Sangaran-Oracle we are not yet ready for the cloud, but thanks for the suggestion, I will keep it in mind for later.

ONLY the ORDS servlet in that Tomcat instance would benefit from the licensing - not Tomcat itself.

thatJeffSmith-Oracle thanks for the clarification. That's exactly my point about licensing. As the ORDS servlet in Tomcat will run the same JVM as Tomcat itself, I would have to run Tomcat with Oracle JDK and then pay the associated license fees. So there is no license free supported solution to run ORDS on Tomcat.
Only way would be to run ORDS standalone on Oracle JDK, which is not recommended for a Production environment.

Only way would be to run ORDS standalone on Oracle JDK, which is not recommended for a Production environment.
That's also not true. We ourselves run ORDS in standalone in our Clouds, and have no such reservation or clause in our Docs for customers.
Also, only SQLDev requires a JDK. ORDS is fine to run with only a JRE.

thatJeffSmith-Oracle thank you so much for taking of your precious time to provide clarifications and guidance. I will do the testing as suggested and if you don't mind I would like to summarize all that information in a blog post. I'm probably not the only one making wrong assumption until now.
I will keep my results posted here for the benefit of the community.

I have tried with Oracle JDK 11.0.13 on Jetty (ORDS standalone) and I'm getting the same error message.
Suresh-Sangaran-Oracle what else can I try?

Please contact Support and file a bug, Use this community link as a reference and we can track via MOS Service ticket.
Thanks, Suresh

I have tried to use CustomURL instead of TNS for ORDS connection type and it worked.
My setup is with Oracle JDK 11.0.13 running ORDS 21.3 in Standalone mode.
URL format: jdbc:oracle:thin:@MY_TNS_ALIAS?TNS_ADMIN=D:\\Oracle\\tns\\admin
User_Z4K9S this might be interesting for you and provide a workaround to your issue as well.
By the way, it also seems to work with OpenJDK 17 and Tomcat 9, even though it's not officially supported.

Thanks alacourb - so it seems like your test has proven that whatever change broke the handling of the TNS URL in Windows was something in the ORDS code itself and had nothing to do with the JDK.
When I get some time I'll play around with this in my test environment and see if I'm able to upgrade to 21.3 using the CustomURL.
Also I just noticed that 21.3.1 is out - doesn't say anything about TNS bug fixes in the changelog but I'll try upgrading to that first with TNS and see if maybe they found it and fixed it there.
Just out of curiosity - did you open a SR with Oracle for this? There definitely seems to be a bug there that should be addressed, but I'm glad you found this workaround at least! Thanks!

User_Z4K9S yes I have open a SR for that issue.
Please let me know if 21.3.1 is helping. Thanks.

User_Z4K9S I have tested with ORDS 21.3.1 and result is the same.
I'm not getting any progress on my SR as support keeps asking the same question without reading my input to the end and not checking the provided logs and screenshots.

SR number? Did you refer this community link to the SR.

alacourb - I have found your SR, let me do the best to progress it. Suspecting the feature is broken and a Bug with 21.3.* and 21.2.*. It works for 20.4.* and lower. If this is not the case, let me know.
Thanks, Suresh

Followup Bug 33583853 - FAILED TO CONNECT TO USER: ORDS_PUBLIC_USER URL: JDBC:ORACLE:THIN:@MYDB?TNS_ADMIN

Thank you very much Suresh-Sangaran-Oracle for the follow-up and the bug opening.
I can confirm it isn't working starting with 21.2 upwards. 21.1 is still ok.

User_Z4K9S I have tested with ORDS 21.4 and result is the same.

Thanks for checking that alacourb - I've had other priorities of late so I've temporarily given up on upgrading ORDS and am just running on 21.1.3 in our test environments and 20.4.3 in production.
I think the key to finding this bug is that the change happened somewhere between 21.1.3 and 21.2. Any changes to the code that handles the database connections and TNS that got modified between those two versions likely caused this bug.
I tried using a Java decompiler to compare components of the two ords.war files from 21.1.3 and 21.2, but it's like looking for a needle in a haystack when you don't know exactly what you're looking for. I tried searching some of the Java classes that were throwing exceptions - there were changes to the Universal Connection Pool (ucp-21*.jar) between the two versions, but I couldn't pinpoint anything specific. The word "TNS" is present in several different files in ords.war, so trying to track it down would be time consuming even for someone who knew the codebase.
Suresh-Sangaran-Oracle, is there a way for me to keep an eye on this bug myself and potentially contribute anything I find to it? I tried searching My Oracle Support for the bug numeric id as well as the text from the bug and came up with no results.

I've raised the priority of this bug.

This seems to be a problem still in version 21.4.0.r3481956.
Have there been any progress on the bug 33583853? I am unable to find it on Oracle Support.

Gerardo Gomez-Oracle thanks for your input.
If you look into the trail of the discussion you will see that we have successfully implemented that workaround.

user10444551 according to the updates in my Service Request, the bug should be fixed with upcoming version 21.4.1.014.1401
Let's hope for the best.

Thanks @alacourb
I was not successful trying the workaround, so I guess I just wait in the new version.
Is there an ETA on the new version?
BR
Ove

user10444551 have you confirmed the tnsnames.ora file is having the proper definition with another tool?
Maybe you have some typo in the custom url string.
There is no ETA for the new version in my SR, I leave this question for the oracle people in this discussion.
thatJeffSmith-Oracle do you have any information about ETA for the next ORDS release?

ETA is ... before the Groundhog gets his day in PA.

Yes, it works with TNS on the version of ORDS currently used (ver 20-something). Haven't tried the Custom URL with the old version though but the TNS-alias certainly works.

BR
Ove

As being a non-american I had to google that :)
Anyway before February 2 sounds OK.

Ove

User_Z4K9S, user10444551 it works again with the newly released version 21.4.1.
Thanks a lot thatJeffSmith-Oracle and all Oracle teams who worked on the fix.

Thanks alacourb for doing the leg work and submitting the SR, and thanks thatJeffSmith-Oracle for helping to get it seen and addressed. I'll be excited to test it out and hopefully roll it out to my environments along with the January CPUs!

I too can now verify it works!
Thanks all for the help!

BR
Ove