一步一脚印

今天接到开发人员报告：开发库的一个表不能更新数据。
登上开发库执行一下发过来的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 oname => '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 oname => '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 oname => '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', ONAME => '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', ONAME => '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', ONAME => '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', ONAME => '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;
/