OEM 13C表空间使用率监控项失效报错"ORA-1000 : Maximum Open Cursors Exceeded"

 

OEM 13C表空间使用率监控项失效报错"ORA-1000 : Maximum Open Cursors Exceeded"

 

前言

自从上了OEM 13C后，对所有数据库都做了表空间使用率的监控，监控阀值为超过90%就会发邮件报警。

最近发现有一个数据库表空间超过90%了还没有发邮件，登录oem网页管理端一看，会有如下报错：

 

原因

查看了当前打开的游标数量，离open_cursors还有很远，因此排除了游标数满了的情况。

select sid,count(*) from gv$open_cursor where user_name='DBSNMP' and sid in (select sid from gv$session where username='DBSNMP' and program='OMS') group by sid;

       SID   COUNT(*)
---------- ----------
      2009         58
      1777         67
       878         65
      1463         66
       387         66
      1554         70
       920         21
       708         66
       684         69
       186         22
      1345         65
       855         69

12 rows selected.

Elapsed: 00:00:01.81


show parameter  open_cursors

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
open_cursors                         integer                           300

 

查看mos会发现一篇匹配情况的文档：EM 13c, EM 12c: Tablespaces Full Metric Evaluation for 11.2.0.4 Databases Fails with "ORA-1000 : Maximum Open Cursors Exceeded" (Doc ID 1940500.1)，是由于bug引起的。

打上PSU就可以了。

This is fixed in the RDBMS Patch 20299013 - "DATABASE PATCH SET UPDATE 11.2.0.4.6 (INCLUDES CPUAPR2015)" or alternatively it is fixed by one-off Patch 18235390.

However, it is strongly advised to apply the PSU (Patch 20299013) rather than to apply the one-off fix for this bug.  This is because applying the one off Patch 18235390 can introduce Bug 18723434 "INCORRECT VALUE IN TABLESPACE_SIZE OF DBA_TABLESPACE_USAGE_METRICS IN 11.2.0.4".  

Both of these bugs are fixed in the PSU patch. For more details, refer to:

<Doc ID 17897511.8> : Bug 17897511 - ORA-1000 from query on DBA_TABLESPACE_USAGE_METRICS after upgrade to 11.2.0.4 - superseded.

For Windows environment, issue is fixed in 11.2.0.4 Bundle Patch 15. Refer the following note for bundle patch details:
Note 1454618.1 - Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets