[转自:http://www.itpub.net/thread-1594434-1-1.html]
现象:
数据库运行正常,但是业务系统被阻塞。报ora-29861:domain index is marked LOADING/FAILED/UNUSABLE 错误
检查数据库发现,数据库确实使用了全文索引,在一张应用日志表的long型字段建立了全文索引
检查发现其状态为POPULATE
select idx_name,idx_status from ctxsys.ctx_indexes;
IND_T_LONG_COL POPULATE
于是rebuild了这个全文索引,问题解决
疑问:
1. idx_status有哪些状态?分别都表示什么含义?我查询到的有POPULATE,POPULATE_K,INDEXED,NO_INDEX
2. 网上有文章说是需要在listener.ora和tnsnames.ora内添加一些内容
如下
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MYDATABASE)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = mydatabase.world) (ORACLE_HOME = /u01/app/oracle/product/8.1.6) (SID_NAME = mydatabase) ) (SID_DESC = (PROGRAM = extproc) (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/8.1.6) ) )
EXTPROC_CONNECTION_DATA,EXTPROC_CONNECTION_DATA.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (CONNECT_DATA = (SID = PLSExtProc) ) )
我的RAC环境内的listener.ora和tnsnames.ora并没有添加这些
一定要添加这些吗?
3. ora-29861到底是因为什么原因会被触发?
oracle 的解释是
An attempt has been made to access a domain index that is being built or is marked failed by an unsuccessful DDL or is marked unusable by a DDL operation. |
而且ora-29861还被列为了全文索引常见的错误
那么到底是怎么样的情况就会导致全文索引失效呢?如何预防?(定期重建吗?)
重点是到底什么原因会导致全文索引挂了?