Forum Stats

  • 3,727,629 Users
  • 2,245,420 Discussions
  • 7,852,899 Comments

Discussions

Getting error ORA-12537 lately

Ray007
Ray007 Member Posts: 28 Bronze Badge

I recently started getting "ORA-12537: Network Session: End of file" when attempting to connect to one of my Oracle databases in the cloud (oracle cloud) with this extension.

  • it worked a few days ago with no issues so I don't know what changed
  • the connecting is done over an ssh tunnel
  • everything still works (over the same tunnel) if I use Oracle SQLDeveloper
  • this VSCode extension still works fine for any other database I've tried. It is only this one hosted in Oracle Cloud that is no longer working. This includes other DBs where I need to use an SSH tunnel to connect

Environment:

  • running on macos 11.2.2 (intel)
  • up to date on all updates
  • tried upgrading to dotnet runtime 5.0.3 and same error

Anyone else see this before and know any resolution?

Best Answer

  • Ray007
    Ray007 Member Posts: 28 Bronze Badge
    Accepted Answer

    It doesn't help. But then I decided to dig deeper.

    • installed a MacOS VM and with a fresh everything, it worked again.
    • then, tried something simpler (another user account on my development Mac). And this worked too!! (using the ssh tunnel already set up by my main user account)

    I finally traced it down to something I had done.

    I have a the following launch agent defined in my account to the TNS_ADMIN environment variable which I needed for Oracle SqlDeveloper. This is in a file in /Users/<username>/Library/LaunchAgents:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
    <plist version="1.0">
      <dict>
        <key>Label</key>
        <string>Set TNS_ADMIN at login</string>
        <key>ProgramArguments</key>
        <array>
          <string>sh</string>
          <string>-c</string>
          <string>launchctl setenv TNS_ADMIN /path/to/instantclient_19_3/network/admin</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
      </dict>
    </plist>
    

    Then, in here, I have 2 files. My sqlnet.ora and my tnsnames.ora files. The problem was in my sqlnet.ora file!! At one point our DBAs had us change it to match changes they were doing to our local Radius authenticated DBs. I had this set:

    SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCP, RADIUS)
    

    I changed it to:

    SQLNET.AUTHENTICATION_SERVICES= (ALL) #(BEQ, TCP, RADIUS)
    

    And that fixed this problem!! Also, confirmed that it didn't break my Radius configuration in Oracle SqlDeveloper. 😀


    Thank you for your time and patience and apologies for missing this crucial change to my config. I've documented it here in case this hits anyone else.


    PS: Radius connection still not support with Oracle Developer Tools for VSCode.... 😪

Answers

  • Ray007
    Ray007 Member Posts: 28 Bronze Badge

    I can connect the this DB using PLSQL/Developer from a windows VM (using the same ssh tunnel from the Mac host). So it seems to be something weird with this plugin...

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee
    edited March 3

    This sounds very similar to an issue we saw that was specific to macOS and Oracle Autonomous Database. There was an issue with the SSL implementation in .NET Core Runtime 2.1 on macOS.

    The solution to that problem was to install .NET Core Runtime 3.1. As a result connection attempts might take longer than usual, but they did not get killed.

    Can you please try installing .NET Core Runtime 3.1 and then restart Visual Studio Code? (.NET Core Runtime 5.0 is not used by 19.3.3)

    This issue will be resolved in a better way in 19.3.4 (and when 19.3.4 comes out you should use .NET Core Runtime 5.0)

  • Ray007
    Ray007 Member Posts: 28 Bronze Badge

    Thanks. Tried reinstall of 3.1.12 (I had 3.1.8 before all this) and still no go. It seems so weird that it would work with my non-cloud databases and that other tools still work with this database. As far as I'm aware nothing had changed at my end so I suspect something is different server side?

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee
    edited March 5

    Thanks Ray. Did you install the database in Oracle Compute? If not, which cloud service is it?

    Keep in mind that all the networking code for this tool is written in .NET Core and relies on the .NET Core runtime networking and security code. So there is definitely going to be different bugs/issues than what you will see with tools implemented in java.

    We are very close to the next release (19.3.4). So at this point I would suggested that when 19.3.4 comes out, you upgrade and make sure you are using .NET Core 5.0 (for macOS). As this is similar to other issues I have seen it is possible it could go away with that combination.

    The other thing to watch out for is connection sniping. I don't know if that is happening here, and I am not a networking expert but if the database or listener is killing infrequently used connections, or if your firewall is doing the same, you could see this error.

  • Ray007
    Ray007 Member Posts: 28 Bronze Badge

    More details for your team:

    From the logs, when connecting I see

    Information: 03/07/2021 11:40:47.236331 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Infrastructure\StreamWriter.cs(91) WriteAsync() : Writer : Written.

    Information: 03/07/2021 11:40:47.236644 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Infrastructure\LazyLock.cs(36) ReleaseAsync() : Releasing Lock-->3

    Information: 03/07/2021 11:40:47.237028 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Infrastructure\LazyLock.cs(38) ReleaseAsync() : Released Lock-->3

    Information: 03/07/2021 11:40:47.237299 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Services\ConnectionManager\ConnectionServiceProvider.cs(118) ConnectRequestProcessorAsync() : Connect request processed

    Error: 03/07/2021 11:40:47.434119 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Services\ConnectionManager\ConnectionServiceProvider.cs(545) OpenOracleDBConnectionAsync() : ORA-12537: Network Session: End of file

    Error: 03/07/2021 11:40:47.435909 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Services\ConnectionManager\ConnectionServiceProvider.cs(545) OpenOracleDBConnectionAsync() : at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

    at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

    at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)

    at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

    at OracleVSCodeServer.Services.ConnectionManager.ConnectionServiceProvider.GetNewDatabaseConnectionExt(OracleConnectionInfoStorage connDetails, Boolean alterSession)

    at OracleVSCodeServer.Services.ConnectionManager.ConnectionServiceProvider.GetNewDatabaseConnection(OracleConnectionInfoStorage connDetails, Boolean alterSession, String connectionType)

    at OracleVSCodeServer.Services.ConnectionManager.ConnectionServiceProvider.OpenOracleDBConnectionAsync(ConnectRequestParameters connParam, OracleConnectionInfoStorage connDetails)

    Information: 03/07/2021 11:40:47.438059 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Infrastructure\LazyLock.cs(30) AcquireLockAsync() : Acquiring Lock-->3

    Information: 03/07/2021 11:40:47.438415 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Infrastructure\LazyLock.cs(32) AcquireLockAsync() : Acquired Lock-->3

    Information: 03/07/2021 11:40:47.438695 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Infrastructure\StreamWriter.cs(83) WriteAsync() : Writer : Writing message. Id:, Type:Event, Method:connection/complete

    Information: 03/07/2021 11:40:47.438907 PM: C:\ADE\aime_1\odtvscode\src\server\OracleVSCodeLangServer\OracleVSCodeServer\Infrastructure\StreamWriter.cs(87) WriteAsync() : Bytes to be trasported 1999


    Watching the SSH tunnel I see this when trying to connect using Oracle Dev Tools for VScode:

    16:42:08 debug1: Connection to port 11521 forwarding to 10.195.1.2 port 1521 requested.

    16:42:08 debug2: fd 9 setting TCP_NODELAY

    16:42:08 debug3: fd 9 is O_NONBLOCK

    16:42:08 debug3: fd 9 is O_NONBLOCK

    16:42:08 debug1: channel 2: new [direct-tcpip]

    16:42:08 debug3: send packet: type 90

    16:42:08 debug3: receive packet: type 91

    16:42:08 debug2: channel 2: open confirm rwindow 2097152 rmax 32768

    16:42:08 debug3: receive packet: type 96

    16:42:08 debug2: channel 2: rcvd eof

    16:42:08 debug2: channel 2: output open -> drain

    16:42:08 debug2: channel 2: obuf empty

    16:42:08 debug2: channel 2: chan_shutdown_write (i0 o1 sock 9 wfd 9 efd -1 [closed])

    16:42:08 debug2: channel 2: output drain -> closed


    To contrast, I see this when connecting with Oracle SQLDeveloper:

    16:44:12 debug1: Connection to port 11521 forwarding to 10.195.1.2 port 1521 requested.

    16:44:12 debug2: fd 10 setting TCP_NODELAY

    16:44:12 debug3: fd 10 is O_NONBLOCK

    16:44:12 debug3: fd 10 is O_NONBLOCK

    16:44:12 debug1: channel 3: new [direct-tcpip]

    16:44:12 debug3: send packet: type 90

    16:44:12 debug3: receive packet: type 91

    16:44:12 debug2: channel 3: open confirm rwindow 2097152 rmax 32768


    Also of note:

    • when I disconnect with SQLDeveloper, I see the port get closed when monitoring the SSH tunnel
    • ONLY when I quit VSCode, I see the port get closed. This tells me that the port remains open in the background despite the ORA-12537
    • There is no issues with other SSH tunnels. However in this case, there is a very major difference. This SSH user account that I am using has no shell access while those that work do. It was always this way but perhaps the means of blocking shell access was changed serverside causing this issue. Perhaps experimentation with connecting through SSH tunnels with a shell set to /usr/sbin/nologin as I suspect that is how this environment is setup.
  • Ray007
    Ray007 Member Posts: 28 Bronze Badge

    The whois info on the sshtunnel IP shows the following. Is that Oracle Compute?

    IP Whois

    Oracle Corporation OC-195 (NET-140-238-0-0-1) 140.238.0.0 - 140.238.255.255
    Oracle Public Cloud OC-195 (NET-140-238-0-0-2) 140.238.0.0 - 140.238.255.255
    


  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee

    Hi Ray,

    I can't tell if that is Oracle Compute or not. Maybe it isn't so important.

    In any case, can you please let us know:

    1) How exactly you configured the SSH tunnel for SQL Developer?

    2) How exactly you configured the SSH tunnel for VS Code?

  • Ray007
    Ray007 Member Posts: 28 Bronze Badge

    It's the same tunnel that I am using for both tools.

    ssh -i "path/to/key/file" -vvv -L "*:11521:10.xxx.x.x:1521" -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes -o TCPKeepAlive=no -o ServerAliveInterval=15 [email protected] -N

    Also did some more testing:

    1. installed vscode in a Windows VM and using the same tunnel from the host mac, it works!!
    2. tried uninstalled all versions of .net runtime using https://docs.microsoft.com/en-us/dotnet/core/additional-tools/uninstall-tool?tabs=macos and tried only installing 3.1.8 and still the same error....

    I'm mostly surprised that it used to work and then quit on the mac. Hopefully new version with newer .net core resolves this.

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee

    Hi Ray,

    We just release version 19.3.4. Could you please try it out and see if it helps?

  • Ray007
    Ray007 Member Posts: 28 Bronze Badge
    Accepted Answer

    It doesn't help. But then I decided to dig deeper.

    • installed a MacOS VM and with a fresh everything, it worked again.
    • then, tried something simpler (another user account on my development Mac). And this worked too!! (using the ssh tunnel already set up by my main user account)

    I finally traced it down to something I had done.

    I have a the following launch agent defined in my account to the TNS_ADMIN environment variable which I needed for Oracle SqlDeveloper. This is in a file in /Users/<username>/Library/LaunchAgents:

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
    <plist version="1.0">
      <dict>
        <key>Label</key>
        <string>Set TNS_ADMIN at login</string>
        <key>ProgramArguments</key>
        <array>
          <string>sh</string>
          <string>-c</string>
          <string>launchctl setenv TNS_ADMIN /path/to/instantclient_19_3/network/admin</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
      </dict>
    </plist>
    

    Then, in here, I have 2 files. My sqlnet.ora and my tnsnames.ora files. The problem was in my sqlnet.ora file!! At one point our DBAs had us change it to match changes they were doing to our local Radius authenticated DBs. I had this set:

    SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCP, RADIUS)
    

    I changed it to:

    SQLNET.AUTHENTICATION_SERVICES= (ALL) #(BEQ, TCP, RADIUS)
    

    And that fixed this problem!! Also, confirmed that it didn't break my Radius configuration in Oracle SqlDeveloper. 😀


    Thank you for your time and patience and apologies for missing this crucial change to my config. I've documented it here in case this hits anyone else.


    PS: Radius connection still not support with Oracle Developer Tools for VSCode.... 😪

Sign In or Register to comment.