高级复制错误ORA-23474解决方法

今天接到开发人员报告：开发库的一个表不能更新数据。
登上开发库执行一下发过来的SQL，果然报错：

SQL> update CAT_AUTH_BAD_DRUG set PROCLAMATION_NUMBER = 'ss';
update CAT_AUTH_BAD_DRUG set PROCLAMATION_NUMBER = 'ss'
       *
ERROR at line 1:
ORA-23474: definition of "NDMAIN"."CAT_AUTH_BAD_DRUG" has changed since
generation of replication support

这是一个物化视图高级复制主表。从报错信息可以清晰知道错误原因：有人直接操作了表CAT_AUTH_BAD_DRUG，复制环境被破坏。

遇到这种错误，解决起来比较麻烦，下面的解决步骤：

1、在物化视图站点删除物化视图
SQL> conn mvadmin/mvadmin
Connected.
--删除复制对象
SQL> exec DBMS_REPCAT.DROP_MVIEW_REPOBJECT(sname => 'NDMAIN',oname =>'CAT_AUTH_BAD_DRUG',type => 'SNAPSHOT');

PL/SQL procedure successfully completed.
--删除物化视图
SQL> DROP MATERIALIZED VIEW NDMAIN.CAT_AUTH_BAD_DRUG;

Materialized view dropped.

 

2、在主站点重建复制对象
coonn repadmin/repadmin

--删除复制对象
SQL> exec DBMS_REPCAT.DROP_MASTER_REPOBJECT(sname => 'NDMAIN',oname =>'CAT_AUTH_BAD_DRUG',type => 'TABLE');

PL/SQL procedure successfully completed

--挂起复制
SQL> BEGIN
  2  DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
  3  gname => 'REP_GROUP');
  4  END;
  5  /

PL/SQL procedure successfully completed

--重新创建复制对象
SQL> BEGIN
  2  DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
  3  gname => 'REP_GROUP',
  4  type => 'TABLE',
  5  name => 'CAT_AUTH_BAD_DRUG',
  6  sname => 'NDMAIN',
  7  use_existing_object => TRUE,
  8  copy_rows => FALSE);
  9  END;
 10  /

PL/SQL procedure successfully completed

--生成复制支持
SQL> BEGIN
  2  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
  3  sname => 'NDMAIN',
  4  name => 'CAT_AUTH_BAD_DRUG',
  5  type => 'TABLE',
  6  min_communication => TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed

--启动复制
SQL> BEGIN
  2  DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
  3  gname => 'REP_GROUP');
  4  END;
  5  /

PL/SQL procedure successfully completed

3、在物化视图站点重新创建物化视图
conn mvadmin/mvadmin
--创建物化视图
SQL> CREATE MATERIALIZED VIEW NDMAIN.CAT_AUTH_BAD_DRUG REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM NDMAIN.CAT_AUTH_BAD_DRUG@MYDB.US.ORACLE.COM;

Materialized view created

--创建物化视图复制对象
SQL> BEGIN
  2  DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
  3  gname => 'REP_GROUP',
  4  sname => 'NDMAIN',
  5  name => 'CAT_AUTH_BAD_DRUG',
  6  type => 'SNAPSHOT',
  7  min_communication => TRUE);
  8  END;
  9  /

PL/SQL procedure successfully completed

--添加到刷新组
SQL>
SQL> BEGIN
  2  DBMS_REFRESH.ADD (
  3  name => 'NDMAIN.REP_REFRESH',
  4  list => 'NDMAIN.CAT_AUTH_BAD_DRUG',
  5  lax => TRUE);
  6  END;
  7  /

PL/SQL procedure successfully completed

--提交
SQL> commit;

Commit complete

至此，问题得以解决。

 

下面简单说说在物化视图高级复制环境中应如何修改复制表结构：

1、主站点调用包修改表结构
SQL> BEGIN
  2  DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(GNAME => 'REP_GROUP');
  3  DBMS_REPCAT.ALTER_MASTER_REPOBJECT (SNAME => 'NDMAIN', NAME => 'CAT_AUTH_BAD_DRUG', TYPE => 'TABLE', DDL_TEXT => 'ALTER TABLE NDMAIN.CAT_AUTH_BAD_DRUG ADD (
  4  PROCLAMATION_TOTAL VARCHAR2(50)
  5  )');
  6  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(SNAME => 'NDMAIN', NAME => 'CAT_AUTH_BAD_DRUG', TYPE => 'TABLE', MIN_COMMUNICATION => TRUE);
  7  DBMS_REPCAT.RESUME_MASTER_ACTIVITY(GNAME => 'REP_GROUP');
  8  COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed

2、物化视图站点重建物化视图及复制对象
BEGIN
DBMS_REPCAT.DROP_MVIEW_REPOBJECT(SNAME => 'NDMAIN', NAME => 'CAT_AUTH_BAD_DRUG', TYPE => 'SNAPSHOT', DROP_OBJECTS => TRUE);
END;
/
  CREATE MATERIALIZED VIEW NDMAIN.CAT_AUTH_BAD_DRUG  AS SELECT * FROM NDMAIN.CAT_AUTH_BAD_DRUG@MYDB.US.ORACLE.COM ;
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT(GNAME => 'REP_GROUP', SNAME => 'NDMAIN', NAME => 'CAT_AUTH_BAD_DRUG', TYPE => 'SNAPSHOT', MIN_COMMUNICATION => TRUE);
DBMS_REFRESH.ADD(NAME => 'NDMAIN.REP_REFRESH', LIST => 'NDMAIN.CAT_AUTH_BAD_DRUG', LAX => TRUE);
COMMIT;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-227956/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-227956/