Home » SQL & PL/SQL » SQL & PL/SQL » ORA-21561: OID generation failed !! (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, Redhat 6)
ORA-21561: OID generation failed !! [message #668642] Wed, 07 March 2018 09:39 Go to next message
fixxxer
Messages: 27
Registered: August 2014
Junior Member
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, Redhat 6 (soon to be upgraded to 12c)

We have a problem in production, where we are intermittently getting this error: ORA-21561: OID generation failed.

There are 3 client side hosts connecting to our 11g database remotely, through a JDBC thin driver, utilising connection pooling. All of the connections are valid on each host, and successfully interacting with the database through stored procedure calls quite frequently; however, intermittently a procedure call will error out with "ORA-21561: OID generation failed". The procedure is successfully being invoked by the application, because the error is being raised from the "WHEN OTHERS" clause in the procedure exception handling, with the procedure error text wrapped around it:

java.lang.RuntimeException: java.sql.SQLException: ORA-20000: Exception in PROCESS_PKG.CALCULATE_PRC - other) ORA-21561: OID generation failed
ORA-06512: at "MAIN.PROCESS_PKG", line 3640
ORA-06512: at line 1

Line 3640 is the RAISE_APPLICATION_ERROR call in "WHEN OTHERS". Line 1 of the procedure is an input parameter of type RAW (a SYS_GUID).

PROCEDURE calculate_prc (IN_uuid IN  calculations_tbl.uuid%TYPE);

Everywhere on google is pointing to the /etc/hosts file on the client side, but that is for the case where the remote client cannot get a connection at all, due to hostname not being properly maintained in /etc/hosts. Our client /etc/hosts file has the server name maintained properly, and in our scenario, the connection is fine, and the application is able to call the package procedure. I have raised an SR with ORACLE, but struggling to get anywhere. I have come to this forum for 2 reasons:

1) In the hope someone has seen this intermittent occurrence of this error, or
2) Is there any easy way to trace the reason for this error during the procedure call, special logging/tracing/debugging (note, we CANNOT simulate this!)? I have seen in other places it mentioned that this problem can occur for SYS_GUID/RAW.

(Worth noting we have another database and application, having the same problem, not as frequent. The database is on a different database server, and the application is on a different app server. The database there is 12c, and again, it looks like it is due to the use of SYS_GUID()).

Any help at all for resolving, or adding some tracing for this, would be much appreciated!

Thanks!

Re: ORA-21561: OID generation failed !! [message #668643 is a reply to message #668642] Wed, 07 March 2018 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26030
Registered: January 2009
Location: SoCal
Senior Member
[oracle@vbgeneric ~]$ oerr ora 21561
21561, 00000, "OID generation failed"
// *Cause: The handles passed in may not be valid
// *Action: Check the validity of the env, svc handles
[oracle@vbgeneric ~]$

based upon above & what you posted I conclude that a bug exists in your code.
Re: ORA-21561: OID generation failed !! [message #668645 is a reply to message #668643] Wed, 07 March 2018 10:21 Go to previous messageGo to next message
fixxxer
Messages: 27
Registered: August 2014
Junior Member
BlackSwan wrote on Wed, 07 March 2018 16:13
[oracle@vbgeneric ~]$ oerr ora 21561
21561, 00000, "OID generation failed"
// *Cause: The handles passed in may not be valid
// *Action: Check the validity of the env, svc handles
[oracle@vbgeneric ~]$

based upon above & what you posted I conclude that a bug exists in your code.
Thanks - how did you come to this conclusion? On both databases where we are seeing this problem, database 1 it is failing at a line number which relates the input parameter in the procedure, database 2 it is failing at a line number where SYS_GUID() function is being invoked. Not sure how I could create a bug in either of those.
Re: ORA-21561: OID generation failed !! [message #668646 is a reply to message #668645] Wed, 07 March 2018 10:33 Go to previous messageGo to next message
BlackSwan
Messages: 26030
Registered: January 2009
Location: SoCal
Senior Member
One of two realities exist.
1) Oracle is correct & you are mistaken.
2) Oracle incorrectly throws error when it should not do so.

For #1 you must find & fix the flawed application code.
For #2 you must produce a reproducible test case so that Oracle can identify their bug.
Please realize & understand that for either case the needed action must be done by you & we can't provide anything useful for either reality.

Alternatively, you can accept the occasional error & do nothing to reduce or eliminate them.

If you disagree, please show where & why I am incorrect.
Re: ORA-21561: OID generation failed !! [message #668647 is a reply to message #668646] Wed, 07 March 2018 10:37 Go to previous messageGo to next message
fixxxer
Messages: 27
Registered: August 2014
Junior Member
BlackSwan wrote on Wed, 07 March 2018 16:33
One of two realities exist.
1) Oracle is correct & you are mistaken.
2) Oracle incorrectly throws error when it should not do so.

For #1 you must find & fix the flawed application code.
For #2 you must produce a reproducible test case so that Oracle can identify their bug.
Please realize & understand that for either case the needed action must be done by you & we can't provide anything useful for either reality.

Alternatively, you can accept the occasional error & do nothing to reduce or eliminate them.

If you disagree, please show where & why I am incorrect.
Well, I think you are pointing out the obvious and not helping at all with the original post. I am aware there is a problem, which needs fixed, and to fix it, you need to know that problem, whether it is mine or Oracle. So, that is why I asked 2 things:

1) Has anyone seen such a scenario before, or know how to resolve it
2) Any idea of a way to enable finer logging/tracing/debugging to see the exact reason for Oracle throwing the error

Neither of which, you have helped with. I did not ask, "Is it my problem, or Oracles problem?".
Re: ORA-21561: OID generation failed !! [message #668648 is a reply to message #668647] Wed, 07 March 2018 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 26030
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/WHEN_OTHERS
Re: ORA-21561: OID generation failed !! [message #668649 is a reply to message #668642] Wed, 07 March 2018 11:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2756
Registered: January 2010
Location: Connecticut, USA
Senior Member

Ora-21561: OID Generation Failed(Doc ID 1335327.1)


SY.
Re: ORA-21561: OID generation failed !! [message #668650 is a reply to message #668649] Wed, 07 March 2018 11:41 Go to previous messageGo to next message
fixxxer
Messages: 27
Registered: August 2014
Junior Member
Solomon Yakobson wrote on Wed, 07 March 2018 17:14

Ora-21561: OID Generation Failed(Doc ID 1335327.1)


SY.
Thanks SY. We have short and fully qualified dB host in hosts file, but, I will try and use this to simulate the problem!
Re: ORA-21561: OID generation failed !! [message #668659 is a reply to message #668650] Thu, 08 March 2018 01:31 Go to previous messageGo to next message
John Watson
Messages: 7424
Registered: January 2010
Location: Global Village
Senior Member
Quote:
We have short and fully qualified dB host in hosts file
Make sure that the file is formatted correctly. Each line should be IP address then the FQDN and then any aliases. The short name is just another alias. Some people, particularly Windows system administrators, put the short name before the FQDN. That is wrong. If your SA doesn't believe this, tell him to read the RFC (952, I think) that describes the file.

Most programmes will accept a file with the wrong ordering, but every now and then you come across something that insists on the correct formatting. I've hit the problem a couple of times in EBS installations.
Re: ORA-21561: OID generation failed !! [message #668666 is a reply to message #668659] Thu, 08 March 2018 04:15 Go to previous messageGo to next message
fixxxer
Messages: 27
Registered: August 2014
Junior Member
John Watson wrote on Thu, 08 March 2018 07:31
Quote:
We have short and fully qualified dB host in hosts file
Make sure that the file is formatted correctly. Each line should be IP address then the FQDN and then any aliases. The short name is just another alias. Some people, particularly Windows system administrators, put the short name before the FQDN. That is wrong. If your SA doesn't believe this, tell him to read the RFC (952, I think) that describes the file.

Most programmes will accept a file with the wrong ordering, but every now and then you come across something that insists on the correct formatting. I've hit the problem a couple of times in EBS installations.
Thanks John. I'm trying to simulate the problem in our dev environment at the minute. Below is the production hostfile on the client (censored):

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
10.XXX.XX.XX       apphost    apphost.domain.com

#DB
10.XXX.XX.XX          db.domain.com               db

Is it possible the line containing the apphost IP could be causing the problem, because the FQDN comes after the short name?
Re: ORA-21561: OID generation failed !! [message #668987 is a reply to message #668666] Tue, 27 March 2018 09:40 Go to previous message
fixxxer
Messages: 27
Registered: August 2014
Junior Member
Just an update on this!

We discovered that on the database server, in the /etc/resolv.conf file, the secondary nameserver IP address was not resovable from that host. While the primary nameserver was resovable, the secondary was not - this may explain why the issue was intermittent, if the secondary nameserver was being used at times for resolving hostnames. We changed this file to contain the correct IP Address and we have not faced the issue since.

If we do face it again i'll update the thread, but this looks to have fixed the issue. Hope this helps someone else facing this difficult issue!
Previous Topic: oracle Trigger for E-mail notifications !!
Next Topic: How to insert table alias in query
Goto Forum:
  


Current Time: Fri Jun 22 01:26:31 CDT 2018