Friday, August 24, 2007

[Oracle Grid Control] java.sql.SQLException: ORA-01422: ORA-06512: at "SYSMAN.EMD_MNTR_HOST", line 213

I found Below Errors when I click host on Oracle Grid Control.

java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSMAN.EMD_MNTR_HOST", line 213

-----when I checked emoms.log file--------------
2007-08-24 15:39:00,151 [EMUI_15_39_00_/console/monitoring/hostOverview$ctxType=Hosts$type=host$target=new01.xxx] ERROR host.HostOverviewDataObject getHostInfo.1676 - SQL Error Code: 1422

2007-08-24 15:39:00,153 [EMUI_15_39_00_/console/monitoring/hostOverview$ctxType=Hosts$type=host$target=new01.xxx] ERROR host.HostOverviewDataObject getHostInfo.1687 - ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSMAN.EMD_MNTR_HOST", line 213
ORA-06512: at line 1

java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSMAN.EMD_MNTR_HOST", line 213
ORA-06512: at line 1

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:137)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:625)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:183)
at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:872)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1162)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2932)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3023)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4132)
at oracle.sysman.util.jdbc.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:173)
at oracle.sysman.util.jdbc.CallableStatementWrapper.execute(CallableStatementWrapper.java:135)
at oracle.sysman.eml.mntr.host.HostOverviewDataObject.getHostInfo(HostOverviewDataObject.java:1596)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at oracle.cabo.ui.data.provider.MethodDataProvider.getDataObject(Unknown Source)
at oracle.cabo.ui.data.provider.TableDataProvider.getDataObject(Unknown Source)
at oracle.cabo.ui.data.provider.CachingDataProvider.getDataObject(Unknown Source)
at oracle.cabo.ui.data.provider.DataProviderStack.getDataObject(Unknown Source)
at oracle.cabo.ui.LogicalNodeRenderingContext.getDataObject(Unknown Source)
at oracle.cabo.ui.RootRenderingContext.getDataObject(Unknown Source)
at oracle.cabo.ui.LogicalNodeRenderingContext.getDataObject(Unknown Source)
at oracle.cabo.ui.RootRenderingContext.getDataObject(Unknown Source)
at oracle.cabo.ui.data.DataBoundValue.getValue(Unknown Source)
at oracle.cabo.ui.BaseUINode.getAttributeValueImpl(Unknown Source)
at oracle.cabo.ui.BaseUINode.getAttributeValue(Unknown Source)
at oracle.cabo.ui.laf.base.TextRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.laf.base.DataScopeRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.laf.base.xhtml.BodyRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderIndexedChild(Unknown Source)
at oracle.cabo.ui.BaseRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.laf.base.xhtml.DocumentRenderer.renderContent(Unknown Source)
at oracle.cabo.ui.BaseRenderer.render(Unknown Source)
at oracle.cabo.ui.laf.base.xhtml.DocumentRenderer.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.ui.BaseUINode.render(Unknown Source)
at oracle.cabo.servlet.ui.UINodePageRenderer.renderPage(Unknown Source)
at oracle.cabo.servlet.AbstractPageBroker.renderPage(Unknown Source)
at oracle.cabo.servlet.PageBrokerHandler.handleRequest(Unknown Source)
at oracle.cabo.servlet.UIXServlet.doGet(Unknown Source)
at oracle.sysman.emSDK.svlt.EMServlet.doGet(EMServlet.java:377)
at oracle.sysman.eml.app.Console.doGet(Console.java:318)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65)
at oracle.sysman.eml.app.EMRepLoginFilter.doFilter(EMRepLoginFilter.java:109)
at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:16)
at oracle.sysman.eml.app.BrowserVersionFilter.doFilter(BrowserVersionFilter.java:122)
at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:20)
at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:102)
at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:20)
at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:336)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:659)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:330)
at 
com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:830)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:224)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:133)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:186)
at java.lang.Thread.run(Thread.java:534)


So.................

1. I check target_guid on MGMT_TARGETS table OF new01.xxx (ERROR) AND newX.xxx (NO ERROR).

SQL> SELECT TARGET_NAME,TARGET_GUID FROM MGMT_TARGETS WHERE TARGET_NAME LIKE 'new%.xxx';

new01.xxx 6AA0469CCE8982003573C61BC2623C57
new02.xxx C4CCA2A50FE28DB1A18F4F0EF8881FA7



2. USE TARGET_GUID FOR QUERY some different

SQL> SELECT ct.target_name, ct.type_display_name,mem.assoc_target_guid,mem.source_target_guid
FROM mgmt_target_assocs mem, mgmt_targets ct, mgmt_targets mt
WHERE
mem.assoc_target_guid = 'C4CCA2A50FE28DB1A18F4F0EF8881FA7'
AND ct.target_guid = mem.source_target_guid
AND mt.target_guid = mem.assoc_target_guid
AND mt.target_type ='host'
AND ct.target_type = 'cluster';

TARGET_NAME          TYPE_DISPLAY_NAME ASSOC_TARGET_GUID    SOURCE_TARGET_GUID
-------------------------------------------------------------------------------------------------
crs01 Cluster  C4CCA2A50FE28DB1A18F4F0EF8881FA7 8C33F220A960508518F8A9FF0467DABB


SQL> SELECT ct.target_name, ct.type_display_name,mem.assoc_target_guid,mem.source_target_guid
FROM mgmt_target_assocs mem, mgmt_targets ct, mgmt_targets mt
WHERE
mem.assoc_target_guid = '6AA0469CCE8982003573C61BC2623C57'
AND ct.target_guid = mem.source_target_guid
AND mt.target_guid = mem.assoc_target_guid
AND mt.target_type ='host'
AND ct.target_type = 'cluster';

TARGET_NAME    TYPE_DISPLAY_NAME   ASSOC_TARGET_GUID SOURCE_TARGET_GUID
-------------------------------- --------------------------------
crs02  Cluster   6AA0469CCE8982003573C61BC2623C57 654FADF35158A875826CD45B1EB41931

crs01  Cluster  6AA0469CCE8982003573C61BC2623C57 8C33F220A960508518F8A9FF0467DABB

crs01  Cluster  6AA0469CCE8982003573C61BC2623C57 8C33F220A960508518F8A9FF0467DABB


I FOUND SOURCE_TARGET_GUID, THAT DIFFERENT "654FADF35158A875826CD45B1EB41931"

3. Resovle by delete on mgmt_target_assocs TABLE.

SQL> delete from mgmt_target_assocs where SOURCE_TARGET_GUID='654FADF35158A875826CD45B1EB41931' and ASSOC_TARGET_GUID like '6AA0469CCE8982003573C61BC2623C57%';

1 row deleted.

SQL> commit;

Commit complete.


------------------
Remark:

When you find the same Error. You can use this blog to only idea.

4 comments:

Anonymous said...

Just fyi, my Google search on:

oracle "java.sql.SQLException: ORA-01422" grid control

...pointed me straight to this blog entry, and I was able to use the fix described here practically line for line. Got me past the issue and back to work right away - thanks!

Surachart said...

Thank You.
I'm glad my blog be able help.

-)

Anonymous said...

I tried lots of other things to get rid of few unwated targets from Grid Console, nothing worked.

Fortunately ran into this page from google and was able to fix things.

Thanks a lot

Anonymous said...

Please follow the doc.

Doc ID 433178.1)