Monday, April 24, 2017

ORA-29855: error occurred DRG-10700: preference does not exist while creating Text Index


User come across an issue that creating index hanging during datapump import. We have excluded the index during export and trying to create after importing data.

CREATE INDEX "AMX"."BRR_360_TXT" ON "AMX"." BRR_360_BLOB" ("FILE_DATA")
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('POPULATE FILTER ODP_FILTER LEXER ODP_LEXER WORDLIST ODP_WORDLIST STOPLIST ODP_STOPLIST SECTION GROUP ODP_SECTION_GROUP')
PARALLEL 4

This was the error that we got

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: ODP_FILTER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

User have generated the index creating script using dbms_metadata.get_ddl. The index creating script works in case normal index.

But noticed that the index that user was creating is a Text index and we need to use different package i.e CTX_REPORT.CREATE_INDEX_SCRIPT

I have used below script on the source DB to create index for Text index

SET LONG 32000 LINES 200
SET HEAD OFF
SET PAGESIZE 10000
SET TRIMOUT ON TRIMSPOOL ON
SELECT CTX_REPORT.CREATE_INDEX_SCRIPT('<your_text_index_name>') FROM DUAL;
spool off


Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Domain index creation hangs during datapump import

The impdp hangs while creating Domain index and never completes. User waited for 3 days but no luck. All the objects got imported except one domain index.


$ tail -f output
…..
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

When I query data_datapump_jobs , I can see that import is still running.

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SYS SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA EXECUTING 32 2 35
SAPCLD SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0 0 0

When I query v$session_longops, I can see that 48915 out of 48916 MB done and job hangs

select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, message
from v$session_longops order by start_time desc)
…..

SYS_IMPORT_SCHEMA_01 48915 48916 MB 81659 SYS_IMPORT_SCHEMA_01: IMPORT : 48915 out of 48916 MB done

Solution: After research I found that there is a bug in 11.2.0.4 and applying below patch resolved the issue.
Patch 23521888: MERGE REQUEST ON TOP OF 11.2.0.4.0 FOR BUGS 20503463 16683112


Reference: Please find couple of datapump performance Bugs in 11.2.0.4
============================================================
For 11.2.0.4
EXPDP Performance Bugs:
MLR Patch 21443197 released on top of 11.2.0.4 contains the fixes for the bugs: 18082965 18469379 18793246 20236523 19674521 20532904 20548904

IMPDP Performance Bugs:
- Bug 19520061 - IMPDP: EXTREMELY SLOW IMPORT FOR A PARTITIONED TABLE
- Bug 13609098 - IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW >>>>> This patch is already in lsinventory

Bug 21128593 : UPDATING THE MASTER TABLE AT THE END OF DP JOB IS SLOW STARTING WITH 12.1.0.2>> But patch is already in lsinventory