OS：AIX
DB：10.2.0.1 （RAC）
检查到数据库alert日志中出现大量的
Tue Mar 17 04:24:11 2009
Errors in file /oracle/admin/pmos/bdump/pmos1_mmon_823446.trc:
ORA-01000: maximum open cursors exceeded
正常情况ora-01000的提示是说明open_cursors 设置过小，但是现在这套库的open_cursor已经设置到了3000
仔细查看了下alert日志，发现该报错从2009年开始就一直在报，平均5分钟报一次，感觉不是open_cursor设置这么简单。
查看当前的cursor情况
select a.value,s.username,s.sid,s.serial#,b.name,a.sid
from v$sesstat a,v$statname b,v$session s
where a.statistic# = b.statistic#
and s.sid=a.sid
and b.name = 'opened cursors current';
VALUE USERNAME SID SERIAL# NAME SID
---------- ------------------------------ ---------- ---------- ---------------------------------------------------------------- ----------
3 TIS 248 3302 opened cursors current 248
2 TIS 264 17862 opened cursors current 264
3 BTMON 276 33741 opened cursors current 276
2 TIS 287 25452 opened cursors current 287
3 TIS 298 37083 opened cursors current 298
4 LOGWRITE 300 37606 opened cursors current 300
11 SCSYSTEM 302 11171 opened cursors current 302
3 TIS 304 1473 opened cursors current 304
3 TIS 312 31653 opened cursors current 312
2 TIS 332 6521 opened cursors current 332
3 BTMON 333 21432 opened cursors current 333
2 TIS 355 9282 opened cursors current 355
2 TIS 357 63139 opened cursors current 357
2 TIS 372 16266 opened cursors current 372
2 TIS 376 34425 opened cursors current 376
14 SCSYSTEM 397 35087 opened cursors current 397
3 TIS 399 34363 opened cursors current 399
11 LOGWRITE 406 37426 opened cursors current 406
3 LOGWRITE 410 13969 opened cursors current 410
3 TIS 417 16602 opened cursors current 417
2 QMX 423 60855 opened cursors current 423
13 LOGWRITE 431 45599 opened cursors current 431
3 TIS 433 34803 opened cursors current 433
35 LOGWRITE 434 5820 opened cursors current 434
3000 541 1 opened cursors current 541sid为541的会话为MMON进程，查看了mmon进程的trace文件
查看该trc文件，有以下内容：
*** 2013-02-04 12:21:29.474
Checking RLB queue status failed. ORA-01000: maximum open cursors exceeded
Error executing stmt : CREATE type SYS$RLBTYP as object (srv VARCHAR2(1024), payload VARCHAR2(2048))
RLB SQL failed. ORA-01000: maximum open cursors exceeded
*** 2013-02-04 12:21:29.474
ksedmp: internal or fatal error
ORA-01000: maximum open cursors exceeded
*** 2013-02-04 12:21:29.474
ksedmp: internal or fatal error
ORA-01000: maximum open cursors exceeded
*** 2013-02-04 12:26:20.486
MMON encountered error 1000, clearing the error
MMON encountered error 1000, clearing the error
Checking RLB queue status failed. ORA-01000: maximum open cursors exceeded
Error executing stmt : CREATE type SYS$RLBTYP as object (srv VARCHAR2(1024), payload VARCHAR2(2048))
RLB SQL failed. ORA-01000: maximum open cursors exceeded
*** 2013-02-04 12:26:29.495
ksedmp: internal or fatal error
ORA-01000: maximum open cursors exceeded
*** 2013-02-04 12:26:29.495
ksedmp: internal or fatal error
ORA-01000: maximum open cursors exceeded
感觉像是什么有什么对象不存在，查看无效对象：
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ---------------------------------------- ------------------- -------
SYS ALL_APPLY VIEW INVALID
SYS ALL_APPLY_ENQUEUE VIEW INVALID
SYS ALL_APPLY_ERROR VIEW INVALID
SYS ALL_APPLY_PARAMETERS VIEW INVALID
SYS ALL_APPLY_PROGRESS VIEW INVALID
SYS ALL_CAPTURE VIEW INVALID
SYS ALL_CAPTURE_EXTRA_ATTRIBUTES VIEW INVALID
SYS ALL_CAPTURE_PARAMETERS VIEW INVALID
SYS ALL_DEQUEUE_QUEUES VIEW INVALID
SYS ALL_PROPAGATION VIEW INVALID
SYS ALL_QUEUES VIEW INVALID
SYS ALL_QUEUE_PUBLISHERS VIEW INVALID
SYS ALL_QUEUE_SUBSCRIBERS VIEW INVALID
SYS ALL_QUEUE_TABLES VIEW INVALID
SYS ALL_STREAMS_GLOBAL_RULES VIEW INVALID
SYS ALL_STREAMS_MESSAGE_CONSUMERS VIEW INVALID
SYS ALL_STREAMS_MESSAGE_RULES VIEW INVALID
SYS ALL_STREAMS_RULES VIEW INVALID
SYS ALL_STREAMS_SCHEMA_RULES VIEW INVALID
SYS ALL_STREAMS_TABLE_RULES VIEW INVALID
SYS AQ$INTERNET_USERS VIEW INVALID
SYS AQ$_ALERT_QT_F VIEW INVALID
SYS AQ$_AQ$_MEM_MC_F VIEW INVALID
SYS AQ$_AQ_EVENT_TABLE_F VIEW INVALID
SYS AQ$_AQ_SRVNTFN_TABLE_F VIEW INVALID
SYS AQ$_GET_SUBSCRIBERS FUNCTION INVALID
SYS AQ$_KUPC$DATAPUMP_QUETAB_F VIEW INVALID
SYS AQ$_SCHEDULER$_EVENT_QTAB_F VIEW INVALID
SYS AQ$_SCHEDULER$_JOBQTAB_F VIEW INVALID
SYS AQ$_SYS$SERVICE_METRICS_TAB_F VIEW INVALID
SYS DBA_AQ_AGENTS VIEW INVALID
SYS DBA_AQ_AGENT_PRIVS VIEW INVALID
SYS DBA_PROPAGATION VIEW INVALID
SYS DBA_QUEUES VIEW INVALID
SYS DBA_QUEUE_PUBLISHERS VIEW INVALID
SYS DBA_QUEUE_SCHEDULES VIEW INVALID
SYS DBA_QUEUE_SUBSCRIBERS VIEW INVALID
SYS DBA_QUEUE_TABLES VIEW INVALID
SYS DBMS_APPLY_ERROR PACKAGE BODY INVALID
SYS DBMS_AQADM_SYS PACKAGE BODY INVALID
SYS DBMS_AQ_IMPORT_INTERNAL PACKAGE BODY INVALID
SYS DBMS_AQ_INV PACKAGE BODY INVALID
SYS DBMS_AQ_SYS_EXP_ACTIONS PACKAGE BODY INVALID
SYS DBMS_AQ_SYS_EXP_INTERNAL PACKAGE BODY INVALID
SYS DBMS_AQ_SYS_IMP_INTERNAL PACKAGE BODY INVALID
SYS DBMS_ASYNCRPC_PUSH PACKAGE BODY INVALID
SYS DBMS_DEFER PACKAGE BODY INVALID
SYS DBMS_DEFER_IMPORT_INTERNAL PACKAGE BODY INVALID
SYS DBMS_DEFER_INTERNAL_SYS PACKAGE BODY INVALID
SYS DBMS_DEFER_QUERY_UTL PACKAGE BODY INVALID
SYS DBMS_DEFER_REPCAT PACKAGE BODY INVALID
SYS DBMS_DEFER_SYS PACKAGE BODY INVALID
SYS DBMS_DEFER_SYS_PART1 PACKAGE BODY INVALID
SYS DBMS_IAS_MT_INST PACKAGE BODY INVALID
SYS DBMS_LOGREP_EXP PACKAGE BODY INVALID
SYS DBMS_LOGREP_UTIL PACKAGE BODY INVALID
SYS DBMS_OFFLINE_RGT PACKAGE BODY INVALID
SYS DBMS_PRVTAQIM PACKAGE BODY INVALID
SYS DBMS_PRVTAQIP PACKAGE BODY INVALID
SYS DBMS_PRVTAQIS PACKAGE BODY INVALID
SYS DBMS_REFRESH_EXP_LWM PACKAGE BODY INVALID
SYS DBMS_REPCAT_ADD_MASTER PACKAGE BODY INVALID
SYS DBMS_REPCAT_MIG_INTERNAL PACKAGE BODY INVALID
SYS DBMS_REPCAT_SNA_UTL PACKAGE BODY INVALID
SYS DBMS_REPCAT_UTL PACKAGE BODY INVALID
SYS DBMS_REPCAT_VALIDATE PACKAGE BODY INVALID
SYS DBMS_SNAPSHOT PACKAGE BODY INVALID
SYS DBMS_STREAMS_ADM PACKAGE BODY INVALID
SYS DBMS_STREAMS_ADM_UTL PACKAGE BODY INVALID
SYS DEFCALLDEST VIEW INVALID
SYS DEFDEFAULTDEST VIEW INVALID
SYS DEFERRCOUNT VIEW INVALID
SYS DEFERROR VIEW INVALID
SYS DEFLOB VIEW INVALID
SYS DEFPROPAGATOR VIEW INVALID
SYS DEFSCHEDULE VIEW INVALID
SYS DEFTRANDEST VIEW INVALID
SYS KU$_QUEUES_VIEW VIEW INVALID
SYS KU$_QUEUE_TABLE_VIEW VIEW INVALID
SYS KUPC$QUE_INT PACKAGE BODY INVALID
SYS PLUGGABLE_SET_CHECK VIEW INVALID
SYS STRADDLING_TS_OBJECTS VIEW INVALID
SYS SYS_PLSQL_4060_32_1 TYPE INVALID
SYS SYS_PLSQL_4060_9_1 TYPE INVALID
SYS SYS_PLSQL_4246_525_1 TYPE INVALID
SYS SYS_PLSQL_4246_543_1 TYPE INVALID
SYS SYS_PLSQL_4246_592_1 TYPE INVALID
SYS SYS_PLSQL_4246_688_1 TYPE INVALID
SYS SYS_PLSQL_4246_716_1 TYPE INVALID
SYS SYS_PLSQL_4246_761_1 TYPE INVALID
SYS SYS_PLSQL_5333_2231_1 TYPE INVALID
SYS SYS_PLSQL_5333_2259_1 TYPE INVALID
SYS TS_PLUG_INFO VIEW INVALID
SYS UNI_PLUGGABLE_SET_CHECK VIEW INVALID
SYS USER_AQ_AGENT_PRIVS VIEW INVALID
SYS USER_QUEUES VIEW INVALID
SYS USER_QUEUE_PUBLISHERS VIEW INVALID
SYS USER_QUEUE_SCHEDULES VIEW INVALID
SYS USER_QUEUE_SUBSCRIBERS VIEW INVALID
SYS USER_QUEUE_TABLES VIEW INVALID
SYS _ALL_STREAMS_PROCESSES VIEW INVALID
SYS _DEFSCHEDULE VIEW INVALID
SYS _DEFTRANDEST VIEW INVALID发觉有大量的SYS用户下的无效对象，通过查看这些无效对象的定义，发现主要是以下的对象已经被删除
system.aq$_queues
system.aq$_queue_tables
system.aq$_Internet_agent_privs
system.aq$_internet_agents
system.def$_destination
system.def$_defaultdest
system.def$_error
都是一些跟aq有关的基本，由于是生产环境，不管轻易的进行重建基表的操作，所以只能在自己的虚拟机上进行简单的测试，
重建步骤如下：
drop table system.aq$_queues;
drop table system.aq$_queue_tables;
drop table system.aq$_Internet_agent_privs;
drop table system.aq$_internet_agents;
drop table system.def$_defaultdest;
drop table system.def$_error;
ALTER TABLE SYSTEM.DEF$_DESTINATION DROP PRIMARY KEY CASCADE;
DROP TABLE SYSTEM.DEF$_DESTINATION CASCADE CONSTRAINTS;
shutdown immediate
startup upgrade
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
@?/rdbms/admin/utlrp
delete from obj$ where name='SYS$SERVICE_METRICS';
delete from obj$ where name='AQ$_SYS$SERVICE_METRICS_TAB_E';
delete from obj$ where name='ALERT_QUE';
delete from obj$ where name='AQ$_ALERT_QT_E';
delete from obj$ where name='AQ$_KUPC$DATAPUMP_QUETAB_E';
commit;
exec dbms_aqadm.drop_queue_table( queue_table=>'SYS.SYS$SERVICE_METRICS_TAB', force => TRUE);
exec dbms_aqadm.drop_queue_table( queue_table=>'SYS.ALERT_QT', force => TRUE);
@?/rdbms/admin/execsvr
@?/rdbms/admin/catnoalr.sql
@?/rdbms/admin/dbmsslrt.sql
@?/rdbms/admin/catalrt.sql
@?/rdbms/admin/utlrp
如果出现
SYS@hcndb>@showinvalid
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ---------------------------------------- ------------------- -------
DBSNMP MGMT_BSLN_INTERNAL PACKAGE BODY INVALID
1 row selected.
SYS@hcndb>
则运行：
SYS@hcndb>@?/rdbms/admin/catsnmp.sql
@?/rdbms/admin/utlrp
shutdown immediate
startup
内容参考：
How to recreate the SYS.ALERT_QUE [ID 430146.1]
Messages ORA-0 and SMG-4120 SMG-4121 in the Alert Log File [ID 756994.1]
ORA-1000 Maximum Open Cursors Exceeded and RLB SQL failed. ORA-24010 QUEUE SYS.SYS$SERVICE_METRICS does not exist [ID 1298968.1]
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
