4 Replies Latest reply: Mar 29, 2012 1:48 PM by 927123 RSS

    Empty Oracle text error when trying to create index for IdcColl1

    927123
      I've been messing around with Oracle UCM 11.1.1.6 (using Oracle DB 11gR2) and I'm stuck as to why I can't seem to get the Oracle Full Text indexing to work through the Repository Manager.

      When I try to rebuild the collection (with configuration variable: SearchIndexerEngineName=DATABASE.FULLTEXT), I get this exception:

      An error occurred during indexing. Indexer could not create table or index: IdcColl1. Unable to execute SQL statement 'CREATE INDEX FT_IdcColl1 ON IdcColl1 (dDocFullText) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE FILTER CTXSYS.AUTO_FILTER FORMAT COLUMN DFULLTEXTFORMAT CHARSET COLUMN DFULLTEXTCHARSET LEXER OCS_IDCCOLL1_LEXER SYNC (ON COMMIT)')'. ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
      ORA-20000: Oracle Text error:

      ORA-06512: at "CTXSYS.DRUE", line 160
      ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

      I'm running out of ideas on the database and UCM configuration end. The blank oracle text error doesn't help either.
        • 1. Re: Empty Oracle text error when trying to create index for IdcColl1
          ryan sullivan2
          There are a few related topics in the forums. Here's the result of the most popular thread:

          "The patch from Oracle for Bug #8430592 "EMPTY ORA-20000 ORACLE TEXT ERROR WITH PARALLEL QUERY" to 10.2.0.4.0 on Linux x86_64 did the trick. After installing the patch, I was able to create the text index without any issues."

          Text index creation error
          That thread also has a number of other tips and troubleshooting steps.

          Other options:
          ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

          There are a few other possible fixes out there, but I believe most of them assume that the db is configured correctly for full-text and has all of the needed authorizations in place.

          You mention that you're "messing around with .. 11.1.1.6", does that mean that you already have 11.1.1.5 ( or .x) already installed? Is it on the same db? If so, is it working correctly?

          -ryan
          • 2. Re: Empty Oracle text error when trying to create index for IdcColl1
            927123
            Hi Ryan,

            Thanks for responding and for the useful links.

            Yeah it's possible I don't have the correct roles applied. I'll have to double check this.

            I used to have 11.1.1.4 installed with the same version of database (11gR2) and that worked fine, but I already have a working 11.1.1.6 build with the full text search working.
            • 3. Re: Empty Oracle text error when trying to create index for IdcColl1
              Srinath Menon-Oracle
              Hi ,

              It seems that you activeindex.hda file has ActiveIndex=database.1 set .

              Open the activeindex.hda file , change the database.1 to IdcColl1 or IdcColl2 and this will have to be changed at 2 places :

              ActiveIndex=IdcColl1

              and 4th from the @end as well.

              After the change the file should look like this :(the changes related to IdcColl1 are to be noted only)


              <?hda version="11gR1-11.1.1.4.0-idcprod1-111209T175736" jcharset=UTF8 encoding=utf-8?>
              @Properties LocalData
              UseImplicitZonedSecurityField=true
              blFieldTypes=
              ActiveIndex=IdcColl1
              blDateFormat=M/d{yy}{ h:mm[:ss]{ a}}!mAM,PM!tAsia/Calcutta
              @end
              @ResultSet SearchCollections
              7
              sCollectionID
              sDescription
              sVerityLocale
              sProfile
              sLocation
              sFlag
              sUrlScript
              xxxxxxxx
              !csSearchDefaultSearchCollection
              englishx
              local
              databasesearch.1
              enabled
              <$URL$>
              UCM_PS3
              !csSearchDefaultSearchCollection
              English-US
              local
              IdcColl1
              enabled
              <$URL$>
              @end


              After this save the file , then go to <domain_home>/ucm/cs/search and in there will be 3 sub-folders:

              /lock
              /rebuild
              /update

              delete the contents under these 3 sub-folders and then restart the server .

              After that run Collection Rebuild cycle , test to see if the same issue still shows up .

              Hope this helps .

              Thanks
              Srinath

              Edited by: Srinath Menon on Mar 29, 2012 12:17 PM

              Edited by: Srinath Menon on Mar 29, 2012 12:26 PM
              • 4. Re: Empty Oracle text error when trying to create index for IdcColl1
                927123
                Hi Srinath,

                Thanks for your response. I didn't know there were two places for the active index. I tried this out, but still no luck. It's now having issues trying to build the same indexes for IdcColl2.

                An error occurred during indexing. Indexer could not create table or index: IdcColl2. Unable to execute SQL statement 'CREATE INDEX FT_IdcColl2 ON IdcColl2 (dDocFullText) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE FILTER CTXSYS.AUTO_FILTER FORMAT COLUMN DFULLTEXTFORMAT CHARSET COLUMN DFULLTEXTCHARSET LEXER OCS_IDCCOLL2_LEXER SYNC (ON COMMIT)')'. ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
                ORA-20000: Oracle Text error:

                ORA-06512: at "CTXSYS.DRUE", line 160
                ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

                Shouldn't there be an error message under the ORA-20000: Oracle Text error? I'm not too sure why it's blank.