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:
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
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?
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)
ROWVERSION NOT NULL DATE
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.
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 184.108.40.206 or 220.127.116.11, where filtering performance is also improved.
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.
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
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.
DRG-11207: user filter command exited with status 1
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.