ORA-03113 using Toad to connect to old 10.2 database

Toad for Oracle
1

Hi there,

My first post on this forum, so a bit about me:

I'm an Oracle DBA for 23 years now.
I have lots of experience with databases ranging from 10.2 up to 19c
I have been working with Toad for Oracle for the last 12 years.
But even with years of experience, sometimes a strange thing happens, like now:

We're seeing some strange behavior when using the new Toad 14 in combination with Oracle client 19c when connecting to an old 10.2. database.
The connection results in an ORA-03113 when using a TNS connection setup
When using a direct connection instead, Toad connects fine.

Also when using e.g. SQLplus, or SQL-developer also the TNS connections works fine.
All other connections from e.g. other databases also have no problems.
Even better, using Toad with a TNS connection to another 10.2 database also works fine.
All applications use the same tnsnames.ora file

It is just Toad that runs into an ORA-03113 when a TNS connection is used with this database.
Has anyone seen this before, and or know what to do to solve it?

Thank you

2

It's odd that direct works and tns doesn't. "Direct" is a bit of a misnomer. When you supply those "direct" values like host, IP, service name, we just build a string like:

(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=the_host_name)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=the_service_name)))

then we use that value as "server". As you probably know, you can also connect this way with SQLPlus. So basically, all "direct" does is bypass tnsnames.ora.

3

I'll weigh in here and say that I seem to recall (5-6 years ago?) reading a post where the same 3113 message came up for a DBA. Bottom line is that, after many hours on the phone with Oracle support and his own research, turned out to be an issue with insufficient disk in the recovery space (had to do with one of the Oracle params, like db_recovery_file_dest_size, maybe?)

Anyway, doubt if that's the issue here, especially if the Direct method of connection works, but TNS doesn't on that same DB.

4

Thank you both for the reactions.

The idea Gary gave would have been a solution none would ever think about, but as we don't use the recovery area with this database, your doubt is well-founded.

Even more ridiculous is that the database(s), actually 2, having this problem are full RMAN clones of a production database, that does NOT have this problem. All running the same Oracle version 10.2.0.5. So, we keep digging into every aspect of these installations

Thanks

5

Try this:

  1. Turn on spool SQL in Toad (main menu -> database -> Spool SQL -> Spool to screen)
  2. make a connection using the "direct" method
  3. Spool SQL will show the connect string that Toad creates.
  4. compare that connect string to your tnsnames.ora entry for that database