We're experiencing intermittent connectivity using Toad against an Oracle database. 1 in 3 attempts to connect to our database results in the following error:
Aside from the error we receive: ORA-12545: Connect failed because target host or object does not exist
Is there any other debugging or logging information we can obtain from within TOAD? during the connection process?
Any help would be greatly appreciated!
Toad sends queries to the database and waits for an answer. Whatever happens in between is between the Oracle client and server.
So maybe there is some Oracle client logging that can be enabled (or trace the session)
(I know you said "connection" but still, this is all up to Oracle client)
Hi John. Thanks for a quick response. I'm not inquiring about queries. I'm inquiring about the connection process. Yes - once you are connected to the database you can trace SQL, but that's not what I am asking for here.
The Oracle client provides some logging.
Trace and Log Parameter Reference.
See: Client Trace and Log Parameter Description
I have to admit, I'm not very familiar with this, but I think it will log the connection attempt and hopefully give you some info.
Thanks, John. I will try the suggestions in the notes.
John posted a link to a very old version of the documentation on this. You don't say your version, but here is the 12c info:
Database Net Services Reference 12c Chapter 5 Parameters for the sqlnet.ora File
Intermittent 12545 errors usually indicates network route problems. An Oracle net trace on either or both server and client sides should provide more information about the problem. You can configure connection trace logging settings in sqlnet.ora on both the server and the client. Depending on the settings, this can log a great deal of information about a connection along with packet dumps. These are the settings:
On the server:
TRACE_LEVEL_SERVER='trace level'
TRACE_DIRECTORY_SERVER='full path to a dir for trace files'
TRACE_FILE_SERVER='base-file-name.trc'
TRACE_UNIQUE_SERVER=ON
TRACE_TIMESTAMP_SERVER=ON
On the client:
TRACE_LEVEL_CLIENT='trace-level'
TRACE_DIRECTORY_CLIENT='full path to a dir for trace files'
TRACE_FILE_CLIENT='base-file-name.trc'
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
Set 'trace-level' of TRACE_LEVEL_CLIENT on the client and/or TRACE_LEVEL_SERVER on the server to one of:
OFF - No trace output. Keep it here except when actively tracing.
USER - Lowest level. Least output.
ADMIN - Medium level. More output.
SUPPORT - Highest level. Most output.
I usually use 'SUPPORT' for the highest level of output.
Be aware that this can produce a huge amount of data in trace log files if left enabled, so it should only be enabled when needed. I have usually set this up on the server side to capture data about incoming connections, but it traces ALL connections when enabled. There is no way to focus it in on a single session on the server side. That would be more easily done on the client side by just having it on for a single session. But, the server and client traces are not identical. Each captures what information it can from its side which is mostly but not completely the same. You do not have to enable it on both sides. It just depends on your needs and what information you are after. It sounds to me that you would want to do this on the client side to diagnose 12545 errors.
See your version's Database Net Services Reference for more info.
Cheers,
Russ
Thanks, @russell.cannon ! I edited my sqlnet.ora file in the 19c client network admin directory.
When I launched sql plus and tried to connect to the database there was no trace file generated.
Any troubleshooting steps you could recommend? Here's my options:
TRACE_LEVEL_CLIENT=SUPPORT
TRACE_DIRECTORY_CLIENT=C:\trace
TRACE_FILE_CLIENT=base-file-name.trc
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
There are two possibiliities:
Cheers,
Russ
Yea - I wasn't sure about what gets cached and what doesn't in TOAD so I stopped troubleshooting in TOAD and am using SQL Plus in Windows, and the trace files are still not being generated. I have it set up so I start a new SQL Plus session each time I test so I know its picking up the sqlnet.ora changes. I've also found that there are several different variations of the parameters and values that can go in the sqlnet.ora file all for client side tracing. For example - some documents say:
TRACE_LEVEL_CLIENT=SUPPORT
is correct - other documents say I need to convert SUPPORT into the bitmapped 16 and use that instead:
TRACE_LEVEL_CLIENT=16
Several parameters have this ambiguity - and there are thousands of permutations testing what works.
I don't have TNS_ADMIN environment variable defined - I was going to try that next, if that doesn't work I will start an SR with Oracle support.
You do NOT need to convert the names to numbers. The names work. If you are not getting trace files even with sqlplus, then you are not updating the correct sqlnet.ora file. How many Oracle Clients do you have installed? Have you VALIDATED that the sqlplus command you are running is out of the same Oracle Client home as the sqlnet.ora file you updated? I don't see that this is a matter for an SR. There is just something not configured correctly on your workstation.
Cheers,
Russ
If you do set TNS_ADMIN, it will point all Oracle Clients to the specified TNS config path so that you wouldn't have to update multiple sqlnet.ora files.
Cheers,
Russ
Tried defining TNS_ADMIN - didn't work. Opened SR with Oracle, and they provided the options to use below.
Used the following lines in the sqlnet.ora location:
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=c:\trace
TRACE_UNIQUE_CLIENT=ON
DIAG_ADR_ENABLED =OFF
And this worked. Got two trace files - one with successful connection and one with unsuccessful connection.
The successful connection is returing the Virtual IP address of the load balancer.
The unsuccessful connection is returning a Public IP - which is not defined as this is a hosted system.
Anyone else with RAC experience to understand what might be going on? I opened another SR with Oracle to address, but it seems like it isn't getting anywhere.
Hi Russell - I have two Oracle Homes - a 12c client and a 19c client. I always update in both places, however I'm mainly only concerned about the 19c client as we upgraded our databases to 19c. I will be doing a hard uninstall of 12c client soon - as we migrated to production already.
You may want to keep your 12c client for use with Toad. The 19c client has a memory leak with LOBs. 18c does not exhibit the leak. 19c and 21c both do.