高级复制实验配置添加复制节点操作时报错:ORA-23308: object GP.T does not exist or is invalid解决
高级复制实验配置添加复制节点操作时报错:ORA-23308: object GP.T does not exist or is invalid解决
出错原因:
使用高级复制时,在源端启动复制支持,执行语句:REPADMIN@bys1>execute dbms_repcat.generate_replication_support('gp','test' ,'table');
这一步时输入了错误的表名或者用户名。
错误提示:
此后,在执行在源端添加复制节点操作时,有如下出错提示:
REPADMIN@bys1>execute dbms_repcat.add_master_database(gname=>'rep',master=>'bys2' ,use_existing_objects=>true ,copy_rows=>false ,propagation_mode=>'synchronous');
BEGIN dbms_repcat.add_master_database(gname=>'rep',master=>'bys2' ,use_existing_objects=>true ,copy_rows=>false ,propagation_mode=>'synchronous'); END;
*
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-23308: object GP.T does not exist or is invalid
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 460
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 1572
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2283
ORA-06512: at "SYS.DBMS_REPCAT", line 146
ORA-06512: at line 1
REPADMIN@bys1>select gname ,dblink ,masterdef ,master from dba_repsites where gname='REP';
select gname ,dblink ,masterdef ,master from dba_repsites where gname='REP'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required
ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required
REPADMIN@bys1>rollback;
Rollback complete.
REPADMIN@bys1>select gname ,master ,status from dba_repgroup where gname = 'REP';
GNAME M STATUS
------------------------------ - ---------
REP Y QUIESCED
错误分析:
此时从dba_repobject;中可以查看到输入的有GP用户的T表,事实上是没有创建此表的。所以才会出现上面的错误:ORA-23308: object GP.T does not exist or is invalid
REPADMIN@bys1>select sname,oname,status,gname from dba_repobject;
SNAME ONAME STATUS
------------------------------ ------------------------------ ----------
GNAME
------------------------------
GP T ERROR
REP
GP TEST VALID
REP
GP TEST$RP VALID
REP
GP TEST$RP VALID
REP
错误解决:
删除错误配置的表和用户:
REPADMIN@bys1>execute DBMS_REPCAT.DROP_MASTER_REPOBJECT (sname=>'gp',oname=>'T',type=>'TABLE');
PL/SQL procedure successfully completed.
验证:
REPADMIN@bys1>col same for a10
REPADMIN@bys1>col sname for a10
REPADMIN@bys1>col oname for a10
REPADMIN@bys1>col gname for a10
REPADMIN@bys1>select sname,oname,status,gname from dba_repobject;
SNAME ONAME STATUS GNAME
---------- ---------- ---------- ----------
GP TEST VALID REP
GP TEST$RP VALID REP
GP TEST$RP VALID REP
此时,再执行在源端添加复制节点的操作就可以顺利完成了。
REPADMIN@bys1>execute dbms_repcat.add_master_database(gname=>'rep',master=>'bys2' ,use_existing_objects=>true ,copy_rows=>false ,propagation_mode=>'synchronous');
PL/SQL procedure successfully completed.
REPADMIN@bys1>col dblink for a10
REPADMIN@bys1> select gname ,dblink ,masterdef ,master from dba_repsites where gname='REP';
GNAME DBLINK M M
---------- ---------- - -
REP BYS1 Y Y
REP BYS2 N Y