Forum OpenACS Q&A: SWS CR indexing stoped working - max number of extents reached in table
My SWS stoped working, I have realized today that the oracle job updating cr_rev_content_index has been failing for some time.
Tried to run and got this error:
CR_REV_CONTENT_INDEX 2004-02-06 ALTER INDEX DRG-10595: ALTER INDEX CR_REV_CONTENT_INDEX failed DRG-50857: oracle error in dreii0fsh ORA-01631: max # extents (249) reached in table MYUSER.DR$CR_REV_CONTENT_INDEX
The SWS is integrated only with the BBoard package and there is around 10000 posts to that forum.
What is best way to fix and prevent from this accuring again?
Cause: A table tried to extend past maxextents
Action: If maxextents is less than the system maximum, raise it. Otherwise, you must recreate with larger initial, next or pctincrease params.
Explanation: The max extents error occurs when the current number of extents equals the maximum number of extents in the max_extents parameter for the object, or the maximum number of extents allowable for the db_block_size, whichever is smaller, and an attempt is made to add another extent. Max extents can be set for an object using the MAXEXTENTS option of the storage clause. These kind of problems could be avoided by pro-actively monitoring the object sizes that may reach their max_extents. Diagnostic Steps: 1. Run the following script to identify the tables with extent problems.
SQL> select segment_name, owner, extents, max_extents 2 from dba_segments 3 where segment_type = 'TABLE' 4 and (extents +1) >= max_extents;
2. Follow up the scripts in: [NOTE:1019721.6] SCRIPT: SCRIPT TO REPORT TABLES APPROACHING MAXEXTENTS. Possible solutions: ------------------- 1. If the value of max_extents for the affected object is less than the limit of maximum extents for the db_block_size of the database (refer to [NOTE:1015356.4]), then more extents could be allocated to the object. Alter the STORAGE clause of the object to increase MAXEXTENTS. In databases version 7.3.X or higher one could specify maxextents UNLIMITED (i.e. 2147483645), but use this with caution. ALTER TABLE <owner>.table STORAGE ( MAXEXTENTS x); where x is greater than max_extents and lesser than unlimited (2147483645); ALTER TABLE <owner>.table STORAGE ( MAXEXTENTS UNLIMITED); Remark: [NOTE:50380.1] ALERT: Using UNLIMITED Extent Format 2. It is not advisable to have a lot (hundreds of thousands, etc.) of extents that could result in large amounts of of space management operations in dictionary managed tablespase. In that case, or when the number of max_extents for the affected object reached the limit of maximum extents for the db_block_size, recreate the object with more efficient storage. Export the table and precreate the table before import, using larger storage parameters (initial, next, pctincrease) - to result in fewer extents, or export using COMPRESS=Y - to minimize number of extents after import.
First I have applied the command suggested by C.R.Oldham
ALTER TABLE owner>.table STORAGE ( MAXEXTENTS x);on MYUSER.DR$CR_REV_CONTENT_INDEX table
I have set MAXEXTENTS to 500,
My CR_REVISIONS table has arount 10000 lines in it.
select count(*) from cr_revisions; 10560then i have tried to continue with the inexing by using the 'resume' option, what took around 10h of time with 70-80% CPU use and filled around 1.8G of my HD and then at the end got an error.
SQL> ALTER INDEX MYUSER.CR_REV_CONTENT_INDEX REBUILD PARAMETERS('RESUME'); ALTER INDEX MYUSER.CR_REV_CONTENT_INDEX REBUILD PARAMETERS('RESUME') * ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: interMedia Text error: DRG-50857: oracle error in drueixe oracle error in drueixe ORA-06512: at "CTXSYS.DRUE", line 126 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 214 ORA-06512: at line 1any idea??
I dropped the index
drop index cr_rev_content_index;and recreated it.
The problem here is that create index for context index type does not allow you to enter storage option, and the default maxextents is applied. This default in my case 249 extents which depends on your db_block_size parameter (see: http://www.orapub.com/papers2/doc104.pdf )
To go around this limitation I have issued
create index cr_rev_content_index ....and immediately after
ALTER TABLE MYUSER.DR$CR_REV_CONTENT_INDEX STORAGE ( UNLIMITED );this helped to overcome maxextens issue but did not fix the problem as you will soon see:
ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: interMedia Text error: DRG-50857: oracle error in drixtab.create_index_indexes ORA-01237: cannot extend datafile 7 ORA-01110: data file 7: '/ora8/m01/app/oracle/oradata/ora8/drsys01.dbf' ORA-19502: write error on file "/ora8/m01/app/oracle/oradata/ora8/drsys01.dbf", blockno 347393 (blocksize=4096) ORA-27072: skgfdisp: I/O error Linux Error: 9: Bad file descriptor Additional information: 347393 ORA-06512: at "CTXSYS.DRUE", line 126 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78 ORA-06512: at line 1as you see drsys01.dbf grew so big that filled my disk partition ora8 and indexing failed
next step will be to move drsys01.dbf to another location and try again.
ora> shutdown immediate Copy drsys01.dbf to another location cp ... Startup mount alter database rename file '/ora8/m01/app/oracle/oradata/ora8/drsys01.dbf' to '/new/location/drsys01.dbf'; >statement processed create index cr_rev_content_index ... and right after it started ... alter table MYUSER.DR$CR_REV_CONTENT_INDEX$I storage (maxextents 2000); ====================== after about 2h got an error: ----------------------
SQL> create index cr_rev_content_index on cr_revisions (content) 2 indextype is ctxsys.context 3 parameters ('FILTER content_filter_pref'); create index cr_rev_content_index on cr_revisions (content) * ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: interMedia Text error: DRG-50857: oracle error in drixtab.create_index_indexes ORA-01630: max # extents (249) reached in temp segment in tablespace MYUSER ORA-06512: at "CTXSYS.DRUE", line 126 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78 ORA-06512: at line 1 SQL>checked index cr_rev_content_index and it shows as valid, will investigate if this caused any damage.
Now, what does it mean? and why?
- cr_rev_content_index is in VALID state and SWS is working,
- DR$CR_REV_CONTENT_INDEX$I table has 15million rows in it (after indexing cr_revision with 10000 rows)
- ERROR on INSERT in News package (this was working before, and News package was not touched) Errow in News on insert new item.
Error: ora8.c:3930:ora_tcl_command: error in`OCIStmtExecute ()': ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE ORA-06512: at "MYUSER.ACS_CONTENTS_SWS_UTR", line 6 ORA-04088: error during execution of trigger 'MYUSER.ACS_CONTENTS_SWS_UTR' ORA-06512: at "MYUSER.CONTENT_REVISION_UTRG", line 5 ORA-04088: error during execution of trigger 'MYUSER.CONTENT_REVISION_UTRG' ORA-06512: at "MYUSER.NEWS", line 208 ORA-06512: at line 4 SQL: begin news.set_approve( approve_p => 't', revision_id => :id, publish_date => :publish_date_ansi, archive_date => :archive_date_ansi, approval_user => :approval_user, approval_date => :approval_date, approval_ip => :approval_ip, live_revision_p => :live_revision_p ); end;this is a little too much for 4 o'clock in the morning, what is domain index that is marked as FAILED? all indexes are shown as VALID in MYUSER schema. Ideas?
Error: ora8.c:3930:ora_tcl_command: error in`OCIStmtExecute ()': ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE ORA-06512: at "MYUSER.ACS_CONTENTS_SWS_UTR", line 6problem with Failed index, did some investigation:
There is no index marked as INVALID or Loading
Checked Acs_contents_sws_utr trigger,
Noticed that sws depends on sws_srch_cts_ds_iidx index.
Index is marked as valid but its field: DOMIDX_OPTSTATUS is marked as FAILED
Droped and recreated sws_srch_cts_ds_iidx index
SWS search with Intermedia works, but why is so slow?
it takes around 2 minutes to complete a search! ( already done analyze with dbms_stats.gather_schema_stats..)
Is this normal? I hope not.
it was CR_REV_CONTENT_INDEX that was so slow and not SWS as SWS does not use this index but its own sws_srch_cts_ds_iidx
I'm using Null filter and not Inso and this seemed to be the problem for CR_REV_CONTENT_INDEX as it tried to index everything including binary files generating million of rows in the index and filling up disc space. Instead SWS has a clean table for indexing containing only data you need for your search hence Null intermedia filter is working fine here.
Droped cr_rev_content_index entirely as not needed anywhere in the system and using sws ( sws_srch_cts_ds_iidx) search instead.