9 Replies Latest reply: Oct 25, 2012 3:09 AM by Roger Ford-Oracle RSS

    Create context index problem

    957499
      Hi,
      I'm trying to exp/imp a database from hpux to linux and have some problems with one of the text indexes.
      It generates a lot of files on /tmp and takes forever.

      When I create a testtable with 20 rows and run the create index statement I get these errors:

      begin
      ctx_ddl.create_preference('"EDM_FILE_STORAGE_SEARCH"','DIRECT_DATASTORE');
      end;
      /


      SQL> CREATE INDEX "EDM_FILE_STORAGE_Idx_JIO"
      ON "EDM_FILE_STORAGE_TAB_JIO" ("FILE_DATA")
      INDEXTYPE IS "CTXSYS"."CONTEXT"
      PARAMETERS ( 'DATASTORE EDM_FILE_STORAGE_SEARCH' );

      2 3 4 the file /tmp//tmp/drgitmpMFi4dy4qWGlj0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyx26xnl0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyWEEbIp0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dy5FRxnn0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyIOMbno0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyawgZ2s0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyviNINz0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dySl4JIE0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dytZ6CaJ0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dybA4JNN0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyfNuwRP0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dy20GavV0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dybEI91Z0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dya004h10.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyDXdF650.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dy6o9cPb0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyIripWg0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyEl2Rzi0.tmp can not be opened, check it
      the file /tmp//tmp/drgitmpMFi4dyZvtrGn0.tmp can not be opened, check it

      Index created.

      But the same test on HPUX finishes ok.

      The HPUX database is 10.2.0.4 and the Linux database is 10.2.0.5. It should be ok?

      Any suggestions abut this problem, please?

      Brg
      jio
        • 1. Re: Create context index problem
          957499
          Mor info:
          The indexed column is a blob

          SQL> desc edm_file_storage_tab_jio;
          Name Null? Type
          ----------------------------------------- -------- ----------------------------
          DOC_CLASS NOT NULL VARCHAR2(12)
          DOC_NO NOT NULL VARCHAR2(120)
          DOC_SHEET NOT NULL VARCHAR2(10)
          DOC_REV NOT NULL VARCHAR2(6)
          DOC_TYPE NOT NULL VARCHAR2(18)
          ENTITY_STATE VARCHAR2(100)
          ENTITY_TYPE VARCHAR2(50)
          CREATED_BY VARCHAR2(100)
          CREATED_DATE DATE
          LOCAL_FILE_NAME VARCHAR2(500)
          ROWVERSION NOT NULL DATE
          FILE_DATA BLOB
          • 2. Re: Create context index problem
            Roger Ford-Oracle
            Something strange there. I'm not sure why it's trying to write to /tmp//tmp.

            Can you try creating a directory /tmp/tmp (with world write access) and see if the problem disappears? Also check for any environment variables (TEMP or TMP perhaps) which point to this directory when you start the db.

            Also, after running that create index statement, can you do

            select * from ctx_user_index_errors;

            10.2.0.4 and 10.2.0.5 uses filters from different vendors (10.2.0.4 is Verity, 10.2.0.5 is Stellent/Oracle), so it's possible (though unlikely) that your files are in a format not handled by the newer filters.
            • 3. Re: Create context index problem
              Ebalthes-Oracle
              Hi

              What documents have you stored in the BLOB column ?

              Text filter has been changed in 10.2.0.5 thus the slow filtering issue, more details in note 1105717.1
              In 10.2.0.4 filter used by Text did not support archive file formats.
              The message "tmpfile can not be opened" is ignorable, mostly from filtering archive files and this is fixed in latest filter release used by Text, ie 11.2.0.2 or 11.2.0.3, where filtering performance is also improved.

              -Edwin
              • 4. Re: Create context index problem
                957499
                Thanks for your replies! :-)

                When I try to index my testtable (with 20 rows) I only get the "the file /tmp/..." messages, but when running the import with the whole table (730 000 rows) it also writes a lot of files (not all) to the /tmp directory.
                Most of the documents is scanned documents in jpg format. I don't now if the indexing works on these filetypes - but again, no problems on the existing hpux/10.2.0.4 database.

                brg,
                jio
                • 5. Re: Create context index problem
                  Ebalthes-Oracle
                  again: in 10.2.0.4 Text uses a different filter which has other supported document formats, full list in note 309154.1

                  do you have any entries in ctx_user_index_errors view ?
                  • 6. Re: Create context index problem
                    957499
                    No errors in the view.

                    I guess I should install 10.2.0.4 and try

                    Thanks!

                    Brg
                    jio
                    • 7. Re: Create context index problem
                      957499
                      I got a step further wiith 10.2.0.5.
                      I had to create a directory under /tmp as some of you said and point $TMPDIR to that dir.

                      But it still creates a lot of temp files and it takes forever.
                      Is this normal behavior when creating this type of index?


                      -rw-r--r-- 1 oracle oinstall 261076480 Oct 24 23:34 drgitmpyWu2CFbWwG1U0.tmp
                      -rw-r--r-- 1 oracle oinstall 238624512 Oct 24 23:36 drgitmpyWu2CFhwJm5y0.tmp
                      -rw-r--r-- 1 oracle oinstall 240269568 Oct 24 23:38 drgitmpyWu2CFiLB43x0.tmp

                      ...

                      brg,
                      jio
                      • 8. Re: Create context index problem
                        957499
                        I also get a lot of errors in the ctx_index_errors view now:
                        DRG-11207: user filter command exited with status 2
                        DRG-11225: Third-party filter timed out.

                        or

                        DRG-11207: user filter command exited with status 1
                        • 9. Re: Create context index problem
                          Roger Ford-Oracle
                          The temp files should get deleted after each file is processed.

                          "Forever" is meaningless. How long does it take, and for how many documents? Text indexes do take a long time to create - can be measured in days in some situations.

                          The errors you're seeing in ctx_user_index_errors are probably caused by damaged documents - files that the filter thinks it recognizes, but then fails to process according to the expected layout for those documents. If there are only a few of these, then I wouldn't worry too much - if there are lots (> 1% of your total, perhaps) then it's definitely worth investigating further.

                          Scanned documents stored as images (jpegs, tiffs, etc) cannot be indexed. Generally the filters should recognize and skip such documents without errors.