We have several Oracle Text indexes of type CTXCAT in our Oracle 11.2.0.3 database. A process from Oracle Enterprise Manager is running every 8 minutes and dumping errors about an index that hasn't existed in years, like this one in the trace file $ORACLE_HOME/node_SID/sysman/log/emagent.trc:
==================================================================
2013-08-13 05:51:09,882 Thread-1079278176 WARN vpxoci: OCI Error -- ErrorCode(20000): ORA-20000: Oracle Text error:
DRG-10502: index PRODUCTION.IX2_WEB_SESSION_DETAIL does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
SQL = "/* OracleOEM */
DECLARE
TYPE data_cursor_type IS REF CURSOR;
data_c"...
LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip)(PORT=1521))(CONNECT_DATA=(SID=ORCL1)))
2013-08-13 05:51:09,882 Thread-1079278176 ERROR fetchlets.sql: ORA-20000: Oracle Text error:
DRG-10502: index PRODUCTION.IX2_WEB_SESSION_DETAIL does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
2013-08-13 05:51:09,882 Thread-1079278176 ERROR engine: [rac_database,ORCL,textIndexStats] : nmeegd_GetMetricData failed : ORA-20000: Oracle Text error:
DRG-10502: index PRODUCTION.IX2_WEB_SESSION_DETAIL does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
2013-08-13 05:51:09,882 Thread-1079278176 WARN collector: <nmecmc.c> Error exit. Error message: ORA-20000: Oracle Text error:
DRG-10502: index PRODUCTION.IX2_WEB_SESSION_DETAIL does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
==================================================================
I took a wild stab and created a new index by that name of type CONTEXT (CTXCAT didn't work) and the error stopped for a while. I dropped that index, and then started getting the following, which was the same error I saw when I tried creating an index as type CTXCAT:
==================================================================
2013-08-30 02:13:07,129 Thread-1075751520 WARN vpxoci: OCI Error -- ErrorCode(20000): ORA-20000: Oracle Text error:
DRG-11119: operation is not supported by this index type
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
SQL = "/* OracleOEM */
DECLARE
TYPE data_cursor_type IS REF CURSOR;
data_c"...
LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip)(PORT=1521))(CONNECT_DATA=(SID=ORCL1)))
2013-08-30 02:13:07,129 Thread-1075751520 ERROR fetchlets.sql: ORA-20000: Oracle Text error:
DRG-11119: operation is not supported by this index type
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
2013-08-30 02:13:07,130 Thread-1075751520 ERROR engine: [rac_database,ORCL,textIndexStats] : nmeegd_GetMetricData failed : ORA-20000: Oracle Text error:
DRG-11119: operation is not supported by this index type
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
2013-08-30 02:13:07,130 Thread-1075751520 WARN collector: <nmecmc.c> Error exit. Error message: ORA-20000: Oracle Text error:
DRG-11119: operation is not supported by this index type
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_REPORT", line 534
ORA-06512: at line 48
==================================================================
I did some sleuthing and found out that calling ctx_report.index_stats( ctxcat_indexname ) on any CTXCAT type index gave me the exact same error, down to the line numbers.
More sleuthing followed, since looking for textIndexStats on google didn't turn up much. I finally found it in the output list of:
emctl status agent scheduler | grep textIndexStats
But there's nothing in select * from dba_scheduler_jobs matched textIndexStats, so I was unclear where to look next, and would like to know how to prevent a recurrence.
I was able to fix the issue, and decided to answer my own question for others that might run into this problem and hit the same wall I did. I still don't know what caused it, but it is fixed.
Further research pointed me to the following link, which contained enough hints to point me in the right direction. http://docs.oracle.com/cd/B14099_19/manage.1012/b16242/emctl.htm
The section 2.7.6 "Reevaluating Metric Collections" had information on the files that the Enterprise Manager Metrics are stored in. To avoid dead links, I will copy some excerpts of that article here:
1. Go to $ORACLE_HOME/sysman/admin/metadata directory, where $ORACLE_HOME is the Oracle Home of the Management Agent.
2. Locate the XML file for the target type. For example, if you are interested in the host metric 'Filesystem Space Available(%)' metric, look for the host.xml file.
I actually grep'd for textIndexStats in this directory and found it in a file called database.xmlp. I found a lot of information inside the following line:
<Metric NAME="textIndexStats" TYPE="RAW" IS_METRIC_LONG_RUNNING="TRUE" >
The most useful piece of information came from SQL embedded as CDATA, which included the lines:
cursor idx_cur IS
select owner,job_name,comments
from dba_scheduler_jobs where job_name like 'EM_IDX_STAT_JOB%' and
upper(owner) = 'DBSNMP';
idx_rec idx_cur%ROWTYPE;
BEGIN
OPEN idx_cur;
FETCH idx_cur into idx_rec;
guid := :1;
IF idx_cur%FOUND THEN
dbms_lob.createtemporary(statData,false);
dbms_lob.createtemporary(sizeData,false);
dbms_lob.createtemporary(objectsData,false);
idx_name := substr(idx_rec.comments,1,instr(idx_rec.comments,'|')-1);
This makes it obvious that the non-existent index name was being parsed out of a comments column in dba_scheduler_jobs for the DBSNMP user, with a job name like 'EM_IDX_STAT_JOB%'.
Running the same query used in the cursor above showed me a number of records in the scheduler table. Apparently they aren't true scheduler entries but are used to queue this script, which inserts data into sysman.mgmt_text_index_stats. A number of CTXCAT and missing indexes were in the scheduler table. Apparently the rows in the scheduler table are only removed on success, and an incorrect entry will hang around for years.
To fix this issue, I ran the following as user DBSNMP:
BEGIN
for idx_rec in (
select owner,job_name,comments
from dba_scheduler_jobs
where job_name like 'EM_IDX_STAT_JOB%' and upper(owner) = 'DBSNMP')
LOOP
DBMS_SCHEDULER.DROP_JOB( idx_rec.job_name );
END LOOP;
END;
/
This has eliminated the issue of the SPAM'd trace log file. It would be good if CTXCAT indexes could not be added, or that they were handled gracefully when in there. I hope this helps the next DBA down the road, because I spent way too much time on it.