高階複製錯誤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 ;

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 ;
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;
/