OGG-00665 (status = 1502-ORA-01502: index 'index' or partition of such index is in unusable state)
来源:互联网 发布:linux服务器安装mysql 编辑:程序博客网 时间:2024/04/30 06:53
今天同事在新搭建的压力测试环境启动某个 OGG replicat 进程时报了如下错误:
2013-05-14 16:37:41 ERROR OGG-00665 OCI Error executing single row select (status = 1502-ORA-01502: index 'GGS.SYS_C00188744' or partition of such index is in unusable state), SQL<INSERT I
NTO ggs.ggs_checkpoint (group_name, group_key, current_dir, create_ts, last_update_ts, seqno, rba, audit_ts, version) VALUES (:group_name, :group_key, :current_dir, :create_ts, sysdate, :seqn
o, :rba, :audit_ts, :version)>.
2013-05-14 16:37:41 ERROR OGG-01668 PROCESS ABENDING.
从错误描述得知,在启动 replicat 进程时往 replicat 进程注册的 checkpoint table 中插入记录时,该表上的索引 GGS.SYS_C00188744 不可用导致数据库报 ORA-01502 无法插入记录。
查看 goldengate 用户ggs下该索引确实处于 unusable 状态。
set linesize 200
select INDEX_NAME,TABLE_OWNER,TABLE_NAME,status from dba_indexes where owner='GGS';
INDEX_NAME TABLE_OWNER TABLE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
SYS_C00188745 GGS GGS_CHECKPOINT_LOX VALID
SYS_C00188744 GGS GGS_CHECKPOINT UNUSABLE
尝试创建该 GGS.SYS_C00188744 索引,又报 ORA-01452——checkpoint 表中存在重复的记录。
SQL> alter index GGS.SYS_C00188744 rebuild;
alter index GGS.SYS_C00188744 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
查看 ggs.ggs_checkpoint 表发现,该记录中3个 replicat 进程的检查点信息,但是均存在重复的条目。
SQL> select GROUP_NAME,GROUP_KEY,SEQNO,RBA from ggs.ggs_checkpoint;
GROUP_NA GROUP_KEY SEQNO RBA
-------- ---------- ---------- ----------
RZJTS_TS 1073473462 153 2758109
RZJ_TS1 3800184173 97 93284
RCX_TS1 1668969800 3668 38271465
RZJTS_TS 1073473462 153 2758109
RZJ_TS1 3800184173 97 93284
RCX_TS1 1668969800 3668 38271465
尝试备份该表中的信息,然后删除记录再插入唯一条目,无法成功。
create table hx_bak.ggs_checkpoint_bak as select * from ggs.ggs_checkpoint;
*
ERROR at line 1:
ORA-01502: index 'GGS.SYS_C00188744' or partition of such index is in unusable state
SQL> delete from ggs.ggs_checkpoint;
delete from ggs.ggs_checkpoint
*
ERROR at line 1:
ORA-01502: index 'GGS.SYS_C00188744' or partition of such index is in unusable state
由于此环境中的 ggs schema 完全导入自生产环境,而且在灌入数据初期因为表空间分配不合理做过多次 move tablespace 操作,可能是导致上述索引失效。
现在索引无法 rebuild ,也无法删除重复记录,于是考虑删除 ggs.ggs_checkpoint 表重建(删除前先用csv或其他方式备份原有记录)。
SQL> drop table ggs.ggs_checkpoint_lox;
Table dropped.
GGSCI (bjcszjdb02) 3> dblogin userid ggs,password register
Successfully logged into database.
GGSCI (bjcszjdb02) 1> view params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.ggs_checkpoint
GGSCI (bjcszjdb02) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (ggs.ggs_checkpoint)...
ERROR: OCI Error ORA-00955: name is already used by an existing object (status = 955). Creating transaction table ggs.ggs_checkpoint_lox, SQL <CREATE TABLE ggs.ggs_checkpoint_lox ( group_name VARCHAR2(8) NOT NULL, group_key NUMBER(19) NOT NULL, log_cmplt_csn VARCHAR2(129) NOT NULL, log_cmplt_xids_seq NUMBER(5) NOT NULL, log_cmplt_xids VARCHAR2(2000) NOT NULL, PRIMARY KEY(group_name, group_key, log_cmplt_csn, log_cmplt_xids_seq))>.
错误提示创建事务表 ggs.ggs_checkpoint_lox 时,ggs schema 下已存在同名的对象,导致 add checkpointtable 执行失败,ggs.ggs_checkpoint 添加失败。
删除 ggs.ggs_checkpoint_lox 后再次尝试添加,结果成功。
GGSCI (bjcszjdb02) 4> add checkpointtable
No checkpoint table specified, using GLOBALS specification (ggs.ggs_checkpoint)...
Successfully created checkpoint table ggs.ggs_checkpoint.
2013-05-14 16:37:41 ERROR OGG-00665 OCI Error executing single row select (status = 1502-ORA-01502: index 'GGS.SYS_C00188744' or partition of such index is in unusable state), SQL<INSERT I
NTO ggs.ggs_checkpoint (group_name, group_key, current_dir, create_ts, last_update_ts, seqno, rba, audit_ts, version) VALUES (:group_name, :group_key, :current_dir, :create_ts, sysdate, :seqn
o, :rba, :audit_ts, :version)>.
2013-05-14 16:37:41 ERROR OGG-01668 PROCESS ABENDING.
从错误描述得知,在启动 replicat 进程时往 replicat 进程注册的 checkpoint table 中插入记录时,该表上的索引 GGS.SYS_C00188744 不可用导致数据库报 ORA-01502 无法插入记录。
查看 goldengate 用户ggs下该索引确实处于 unusable 状态。
set linesize 200
select INDEX_NAME,TABLE_OWNER,TABLE_NAME,status from dba_indexes where owner='GGS';
INDEX_NAME TABLE_OWNER TABLE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
SYS_C00188745 GGS GGS_CHECKPOINT_LOX VALID
SYS_C00188744 GGS GGS_CHECKPOINT UNUSABLE
尝试创建该 GGS.SYS_C00188744 索引,又报 ORA-01452——checkpoint 表中存在重复的记录。
SQL> alter index GGS.SYS_C00188744 rebuild;
alter index GGS.SYS_C00188744 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
查看 ggs.ggs_checkpoint 表发现,该记录中3个 replicat 进程的检查点信息,但是均存在重复的条目。
SQL> select GROUP_NAME,GROUP_KEY,SEQNO,RBA from ggs.ggs_checkpoint;
GROUP_NA GROUP_KEY SEQNO RBA
-------- ---------- ---------- ----------
RZJTS_TS 1073473462 153 2758109
RZJ_TS1 3800184173 97 93284
RCX_TS1 1668969800 3668 38271465
RZJTS_TS 1073473462 153 2758109
RZJ_TS1 3800184173 97 93284
RCX_TS1 1668969800 3668 38271465
尝试备份该表中的信息,然后删除记录再插入唯一条目,无法成功。
create table hx_bak.ggs_checkpoint_bak as select * from ggs.ggs_checkpoint;
*
ERROR at line 1:
ORA-01502: index 'GGS.SYS_C00188744' or partition of such index is in unusable state
SQL> delete from ggs.ggs_checkpoint;
delete from ggs.ggs_checkpoint
*
ERROR at line 1:
ORA-01502: index 'GGS.SYS_C00188744' or partition of such index is in unusable state
由于此环境中的 ggs schema 完全导入自生产环境,而且在灌入数据初期因为表空间分配不合理做过多次 move tablespace 操作,可能是导致上述索引失效。
现在索引无法 rebuild ,也无法删除重复记录,于是考虑删除 ggs.ggs_checkpoint 表重建(删除前先用csv或其他方式备份原有记录)。
SQL> drop table ggs.ggs_checkpoint_lox;
Table dropped.
GGSCI (bjcszjdb02) 3> dblogin userid ggs,password register
Successfully logged into database.
GGSCI (bjcszjdb02) 1> view params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.ggs_checkpoint
GGSCI (bjcszjdb02) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (ggs.ggs_checkpoint)...
ERROR: OCI Error ORA-00955: name is already used by an existing object (status = 955). Creating transaction table ggs.ggs_checkpoint_lox, SQL <CREATE TABLE ggs.ggs_checkpoint_lox ( group_name VARCHAR2(8) NOT NULL, group_key NUMBER(19) NOT NULL, log_cmplt_csn VARCHAR2(129) NOT NULL, log_cmplt_xids_seq NUMBER(5) NOT NULL, log_cmplt_xids VARCHAR2(2000) NOT NULL, PRIMARY KEY(group_name, group_key, log_cmplt_csn, log_cmplt_xids_seq))>.
错误提示创建事务表 ggs.ggs_checkpoint_lox 时,ggs schema 下已存在同名的对象,导致 add checkpointtable 执行失败,ggs.ggs_checkpoint 添加失败。
删除 ggs.ggs_checkpoint_lox 后再次尝试添加,结果成功。
GGSCI (bjcszjdb02) 4> add checkpointtable
No checkpoint table specified, using GLOBALS specification (ggs.ggs_checkpoint)...
Successfully created checkpoint table ggs.ggs_checkpoint.
然后利用备份的数据再往 ggs.ggs_checkpoint 表插入唯一记录便可正常启动 replicat 进程。
转载请注明作者错处及原文链接:
http://blog.csdn.net/xiangsir/article/details/8927833
- OGG-00665 (status = 1502-ORA-01502: index 'index' or partition of such index is in unusable state)
- ORA-20000: index " "." " or partition of such index is in unusable state
- ORA-20000: index " "." " or partition of such index is in unusable state
- ORA-20000 index ...or partition of such index is in unusable state
- ORA-20000: index "FCSASUSER"."IDX_DN_TREE_ID" or partition of such index is in unusable state
- 分析和解决 ORA-01502: index '某分区表索引名' or partition of such index is in unusable state
- 索引错误 index VHL_V6.PK_WEB_APP_TGT_OBJ or partition of such index is in unusable state 的解决办法
- ORA-14517: Subpartition of index "string.string" is in unusable state
- 今天移动表到另一个表空间出现 :ORA-01502 Index state unusable
- oracle 脚本(2)-修复 status 为 unusable 的 index(ORA-01502)
- oracle 脚本(2)-修复 status 为 unusable 的 index(ORA-01502)
- Index or Not Index
- unusable index and invisible index 的区别
- INDEX UNUSABLE--〉重建索引
- index invisible与unusable 特性
- Oracle_create an index using unusable
- Is index important in addView()?
- Index
- 失业的程序员(十):分歧的产生
- 失业的程序员(十一):焉知非福(上)
- 程序员的进化
- 翻译.po文件
- [Python]网络爬虫(六):一个简单的百度贴吧的小爬虫
- OGG-00665 (status = 1502-ORA-01502: index 'index' or partition of such index is in unusable state)
- opencv用SVM进行多类分类
- 失业的程序员(十二):潜意识的智商
- linux oracle导数据
- 使用WindowBuilder辅助Java GUI开发
- chapt15、使用虚拟内存
- C++中extern “C”含义深层探索
- 关于颜色、纹理和分层的目标检测(object detect)相关论文
- uva 11464 - Even Parity(3级)