今天同事在新搭建的压力测试环境启动某个 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