Home » RDBMS Server » Server Administration » XMLDB (11G)
XMLDB [message #599504] Thu, 24 October 2013 21:32 Go to next message
hexmanju
Messages: 62
Registered: September 2005
Location: MUMBAI
Member
Hi Experts,

.Its a case of ORA-7445.



We have got ourselves into a situation where Oracle support is also not able to help us after 3 months of case being open.

We are still trying with them but now i am also looking into forums for any help.



Here is the issue.



We have XMLDB component installed for our Tibco Application.



Once in a week,at random times ,Tibco starts seeing timeouts and we start seeing row lock contention wait events for a below insert statement



INSERT

INTO xml_default

(

doc_id,

static_id,

xml_doc,

app_id,

doc_type,

requestor_id,

created_date,

last_updated_date,

created_by,

last_updated_by

)

VALUES

(

:v0,

:v1,

XMLType(bfilename('XMLDIR_NEW',:v2), nls_charset_id('AL32UTF8')),

:v3,

:v4,

:v5,

sysdate,

sysdate,

:v6,

:v7

)





Tibco App claims all inserts are auto committed.

When that happens we see the below in alert log being flooded.



ORA-07445: exception encountered: core dump [kgghash()+96] [SIGSEGV] [ADDR:0x1600000001] [PC:0x1001A40E0] [Address not mapped to object] []



ORA-07445: exception encountered: core dump [qmtxRemoveRef()+244] [SIGSEGV] [ADDR:0x48] [PC:0x10199AF34] [Invalid permissions for mapped object] []





The only way we get around the issue is we bounce the database and the listener.Its becoming a regular fact that now we are boucing production once a week .



Has anyone seen issues like above and is there anything we can do from Database level.?



Kindly suggest if someone has seen such issues.
Re: XMLDB [message #599505 is a reply to message #599504] Thu, 24 October 2013 21:46 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
post complete results from SQL below


SELECT * FROM V$VERSION;
Re: XMLDB [message #599506 is a reply to message #599505] Thu, 24 October 2013 21:52 Go to previous messageGo to next message
hexmanju
Messages: 62
Registered: September 2005
Location: MUMBAI
Member
SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Re: XMLDB [message #599507 is a reply to message #599506] Thu, 24 October 2013 22:06 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
you are being bit by Oracle bug.

>Tibco App claims all inserts are auto committed.
COMMIT may occur, but Oracle DB has no auto-commit feature.

if SQL_TRACE=TRUE you could confirm that COMMIT occurs after INSERT is executed.
Re: XMLDB [message #599509 is a reply to message #599507] Thu, 24 October 2013 22:17 Go to previous messageGo to next message
hexmanju
Messages: 62
Registered: September 2005
Location: MUMBAI
Member
So is it a known bug? or something which you think Oracle needs to develop in their lab?

What exactly can i do to prove the commits are not happening

I searched more in Google and got something like this also

https://www.tibcommunity.com/thread/23558

Can it help?

Re: XMLDB [message #599510 is a reply to message #599509] Thu, 24 October 2013 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

COMMIT can be mined from REDO logfile using DBMS_LOGMNR (if it exists)
Re: XMLDB [message #599522 is a reply to message #599509] Fri, 25 October 2013 02:44 Go to previous messageGo to next message
John Watson
Messages: 4812
Registered: January 2010
Location: Global Village
Senior Member
hexmanju wrote on Fri, 25 October 2013 04:17
So is it a known bug? or something which you think Oracle needs to develop in their lab?

What exactly can i do to prove the commits are not happening

I searched more in Google and got something like this also

https://www.tibcommunity.com/thread/23558

Can it help?

I can't comment on the ora-07445, but I can answer your question above about how to prove commits are not happening. Query v$transaction, it has one row for every transaction in progress. Join to v$session on ses_addr=saddr to find details of the session. Look at columns such as start_time, sql_id, ad prev_sql_id to see if any inserts are indeed hanging about uncommitted.
Re: XMLDB [message #602687 is a reply to message #599522] Fri, 06 December 2013 14:49 Go to previous messageGo to next message
hexmanju
Messages: 62
Registered: September 2005
Location: MUMBAI
Member
After a long time we are able to figure out a Unique constraint error is showing in logs.

but the point so what of ORA-001 error comes why does the DB gets so badly locked up that only a bounce can help it.
Re: XMLDB [message #602688 is a reply to message #602687] Fri, 06 December 2013 14:58 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
>but the point so what of ORA-001 error comes why does the DB gets so badly locked up that only a bounce can help it.
While ORA-00001 & other performance problems are observed to be happening at the same time, I suspect that they are both symptoms of a deeper problem.

are there any clues in the alert_{SID}.log file?
Consider generating a STATSPACK report when system slows down.
Re: XMLDB [message #602689 is a reply to message #602688] Fri, 06 December 2013 15:04 Go to previous messageGo to next message
hexmanju
Messages: 62
Registered: September 2005
Location: MUMBAI
Member
Alert log gives this

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x72A0FF630C3F0001] [PC:0x1001A40E0, kgghash()+96] [flags: 0x0, count: 1]
Errors in file /u888/oradata/MPIP/diag/rdbms/mpip_p/MPIP/trace/MPIP_ora_62193866.trc (incident=146818):
ORA-07445: exception encountered: core dump [kgghash()+96] [SIGSEGV] [ADDR:0x72A0FF630C3F0001] [PC:0x1001A40E0] [Address not mapped to object] []
ORA-00001: unique constraint (XML.PK_XML_DEFAULT) violated
Incident details in: /u888/oradata/MPIP/diag/rdbms/mpip_p/MPIP/incident/incdir_146818/MPIP_ora_62193866_i146818.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Dec 02 07:43:46 2013
Dumping diagnostic data in directory=[cdmp_20131202074346], requested by (instance=1, osid=62193866), summary=[incident=146818].
Mon Dec 02 07:43:48 2013
Sweep [inc][146818]: completed
Sweep [inc2][146818]: completed
Mon Dec 02 07:43:49 2013
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x72A0FF630C3F0001] [PC:0x1001A40E0, kgghash()+96] [flags: 0x0, count: 1]
Errors in file /u888/oradata/MPIP/diag/rdbms/mpip_p/MPIP/trace/MPIP_ora_2490690.trc (incident=146810):
ORA-07445: exception encountered: core dump [kgghash()+96] [SIGSEGV] [ADDR:0x72A0FF630C3F0001] [PC:0x1001A40E0] [Address not mapped to object] []
ORA-00001: unique constraint (XML.PK_XML_DEFAULT) violated
Incident details in: /u888/oradata/MPIP/diag/rdbms/mpip_p/MPIP/incident/incdir_146810/MPIP_ora_2490690_i146810.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20131202074351], requested by (instance=1, osid=2490690), summary=[incident=146810].


We mined the redo logs and found duplicates being attempted to inserted.That when we told app teams to fix the ORA-001 error..But their claim is so what if duplicates are coming why does the DB so badly lock up itslef that a bounce only can help.

We have a case open with Oracle for 7 months now..Its in the same state.Kindly help.
Re: XMLDB [message #602690 is a reply to message #602689] Fri, 06 December 2013 15:05 Go to previous messageGo to next message
hexmanju
Messages: 62
Registered: September 2005
Location: MUMBAI
Member
Also in trace files it gives this

In qmtxAddRef, schema = http://localhost:8080/MechAuditESB/prod/ROF.xsd
OID_qmts = 1f9555d0
In qmtxAddRef lce->xrefcount_qmtlce = 1
lce->xinfo_qmtlce->xdbsch_qmtxdk (Schema) = schema =
OID_qmts = c3f0000
In qmtxAddRef, schema = http://localhost:8080/MechAuditESB/prod/ROF.xsd
OID_qmts = 1f9555d0
In qmtxAddRef lce->xrefcount_qmtlce = 2
lce->xinfo_qmtlce->xdbsch_qmtxdk (Schema) = schema =
OID_qmts = c3f0000
In qmtxRemoveRef, schema =
OID_qmts = c3f0000

*** 2013-12-02 07:43:49.033
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x72A0FF630C3F0001] [PC:0x1001A40E0, kgghash()+96] [flags: 0x0, count: 1]
Incident 146810 created, dump file: /u888/oradata/MPIP/diag/rdbms/mpip_p/MPIP/incident/incdir_146810/MPIP_ora_2490690_i146810.trc
ORA-07445: exception encountered: core dump [kgghash()+96] [SIGSEGV] [ADDR:0x72A0FF630C3F0001] [PC:0x1001A40E0] [Address not mapped to object] []
ORA-00001: unique constraint (XML.PK_XML_DEFAULT) violated

Re: XMLDB [message #602749 is a reply to message #602689] Sun, 08 December 2013 06:54 Go to previous messageGo to next message
ThomasG
Messages: 3115
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In what state is the call with oracle since 7 month?

Because the first think they (and I too) would suggest is to update to the latest patch set.

Why haven't you done that yet?
Re: XMLDB [message #603485 is a reply to message #602749] Mon, 16 December 2013 23:48 Go to previous message
hexmanju
Messages: 62
Registered: September 2005
Location: MUMBAI
Member
I am planning to move out of XMLDB to CLOB.Any advise on this?

Just FYI We had implemented XMLDB but we really dont use many features of it.
Previous Topic: upgrade database
Next Topic: Cross Platform Database
Goto Forum:
  


Current Time: Sat Nov 29 00:21:54 CST 2014

Total time taken to generate the page: 0.08434 seconds