Props to David Latham: Apache and SELinux

So, I was working on a CentOS 6.4 box to set up an SVN server. Early in the process, I decided I would try (for the first time) to successfully live with SELinux and its requirements, hoops and other arcana. Generally I just turn the POS off to allow me to admin my Linux boxes (which are usually behind multiple levels of fires, etc. etc. etc.). This time, I wanted to try to live with it for a while.

Things were going great until I realized I needed to use some space assigned to /home to host some data. So, I setup a directory there (using Location in a an Apache .conf file). I restarted apache with service httpd restart and launched my browser... and got a 500 error.

Well. Well. So, went through the normal hoops - checking for permissions and usernames and lions and tigers and bears oh my. No joy. However, I saw a reference somewhere that SELinux might be causing the problem (which I kinda expected) and that I could test that proposition with setenforce 0. Viola'! It worked!

Of course, the whole reason for this charade was to try to live with SELinux (even though we use trivial passwords behind the firewall) as a learning experience. Hmmmmph. I was starting to regret this already. So, I used setenforce 1 to turn the beast back on, and set off on my quest.

A few googles away, I found the link below, which let me resolve the problem. The magic incantations I recited included the following two lines:

setsebool httpd_enable_homedirs true
chcon -R -t httpd_sys_content_t .


I was able to view the current extended attributes for SE by using:
ls -alZ

I'll include the entire blog post as a help to others, but there was no way I was gonna chmod 777 on the directories to make this work! Now, if I could just figure out WHERE those magic incantations come from, without having to learn 1,000 pages of arcana that don't really matter.

David Latham: Allow httpd ( apache ) to write to files and folde...: You may have read my previous post about configuring apache for public_html with selinux. Now today we look at extending this a little wit...

Cool page talking about Linux RAM usage

So, first, I've been using Linux for a really, really, really long time. Like... 1994. I had to hack a SCSI driver to get my Adaptec 2940W working. Yeah, I know - bragging, right?

I've been using top for even longer - it was a good friend on AIX, HPUX, etc. And I've certainly peeked at /proc/meminfo a few times. But something I never really new existed until recently was a cool command line tool called "free". And more to the point, I never knew something really, really basic about memory usage on Linux.

I stumbled across this page, and had to smile at the title:
Linux Ate My RAM!

It definitely opened my eyes about something I hadn't spent much time thinking about, so - thanks!
free -m... it's your friend.

Resolving TONS of Oracle Text errors in emagent.trc

I originally tried to post my question and my eventual answer to my own question on Stack Overflow, but they won't let me answer my own question for 8 hours (SIGH!). Here is the original question and my answer. I hope it helps other part time Oracle DBAs out there. Or full time DBAs for that matter!

http://stackoverflow.com/questions/18543098/resolve-a-drg-11119-error-in-emagent-trc-on-oracle-11-2

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 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 posted the question above after about 6 hours of detective work and frustration. I don't normally throw a question to the net for others to answer, but I do like to provide answers for others - weird, I know. In any event, within an hour, I had stumbled on an article that gave me a few clues. Some filesystem grepping and XML file reading, and I found another set. So... here is the solution I tried to post to Stack Overflow.

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:


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.