由于高级复制中明确要求复制的表需要有主键,那么如果没有主键复制是否能正常进行,如果不能正常进行那么有什么补救措施,这就是本节要测试的内容。
1、在主体定义表中创建不含主键的表,试图将它加入主体组(复制组),看是否能完成复制:
1、1主体定义站点:
SQL> create table tb_test_nokey_rep(id int,name varchar2(10));
Table created.
SQL> declare
2 i int;
3 begin
4 for i in 1..100 loop
5 insert into tb_test_nokey_rep values(i,'liyx');
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(1) from tb_test_nokey_rep;
COUNT(1)
----------
100
2、非主体定义站点
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
TB_TEST_CREATE_REP
TB_TEST_CREATE_REP2
TB_TEST_REP
SQL> create table tb_test_nokey_rep as select * from reptest.tb_test_nokey_rep@master.com;
Table created.
SQL> select count(1) from tb_test_nokey_rep;
COUNT(1)
----------
100
2、将测试表加入到复制组
SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_nokey_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>false);
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.resume_master_activity('REP_MYTEST',true);
PL/SQL procedure successfully completed.
3、测试在复制对象中修改数据,查看同步情况:
3.1、主体定义站点:
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
SQL> insert into reptest.tb_test_nokey_rep values(100,'lirq');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
100 lirq
3.2 非主体定义站点:
插入前:
SQL> select * from tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
SQL> reptest.tb_test_nokey_rep where id=100;
SP2-0734: unknown command beginning "reptest.tb..." - rest of line ignored.
插入后:
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
看来,没有主建的主体复制对象,是无法同步的。
4、补救措施:
4.1、依据:
Q:如果一张表没有主键,又确实需要复制,怎么办?
A:不建议在高级复制的环境中出现这样的情况,应该确保每张表都有主键。如果确实存在这种情况,那么需要用DBMS_REPCAT.SET_COLUMNS 来生成代用主键。
execute DBMS_REPCAT.SET_COLUMNS(sname => test', name => 'tabel', column_list =>'col1,col2,col3,col4');
其中column_list 是用逗号隔开的字段列表,不能有空格。
注意:不要在执行set_columns 之前生成对于没有主键的对象的复制支持,也就是不要运行generate_replication_support,否则会导致all_repobject 视图中该对象状态变为ERROR,而无法再次set_columns。
如果误运行了复制支持而又没有成功,那么需要删除掉这个复制对象再重新生成。也就是在对没有主键的表生成复制的时候,必须遵循以下顺序:
create_master_repobject -> set_columns -> generate_replication_support
4.2、删除复制对象组:
SQL> execute dbms_repcat.drop_master_repobject(sname=>'reptest',oname=>'tb_test_nokey_rep',type=>'table');
PL/SQL procedure successfully completed.
4.3、再次创建对象组
SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_nokey_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_repcat.SET_COLUMNS(sname=>'reptest',oname=>'tb_test_nokey_rep',column_list=>'id,name');
PL/SQL procedure successfully completed.
SQL> exec dbms_repcat.resume_master_activity('rep_mytest',true);
PL/SQL procedure successfully completed.
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
100 lirq
在非主体定义站点测试:
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
还是没有同步.
查看dba_repobject发现复制对象并没有产生复制支持,晕:
SQL> col sname for a10;
SQL> col oname for a10;
SQL> col gname for a10;
SQL> select gname,sname,oname,status from dba_repobject;
GNAME SNAME ONAME STATUS
---------- ---------- ---------- --------------------
REP_MYTEST REPTEST TB_TEST_CR VALID
EATE_REP
REP_MYTEST REPTEST TB_TEST_CR ERROR
EATE_REP2
REP_MYTEST REPTEST TB_TEST_NO VALID
KEY_REP
REP_MYTEST REPTEST TB_TEST_RE VALID
P
GNAME SNAME ONAME STATUS
---------- ---------- ---------- --------------------
REP_MYTEST REPTEST TB_TEST_RE VALID
P$RP
REP_MYTEST REPTEST TB_TEST_RE VALID
P$RP
6 rows selected.
再次回头使复制对象产生复制支持:
SQL> execute dbms_repcat.suspend_master_activity('rep_mytest');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.GENERATE_REPLICATION_SUPPORT(sname=>'reptest',oname=>'tb_test_nokey_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.GENERATE_REPLICATION_SUPPORT(sname=>'reptest',oname=>'tb_test_create_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.resume_master_activity('rep_mytest',true);
PL/SQL procedure successfully completed.
SQL> select gname,sname,oname,status from dba_repobject;
GNAME SNAME ONAME STATUS
---------- ---------- ---------- --------------------
REP_MYTEST REPTEST TB_TEST_CR VALID
EATE_REP
REP_MYTEST REPTEST TB_TEST_CR VALID
EATE_REP$R
P
REP_MYTEST REPTEST TB_TEST_CR VALID
EATE_REP$R
P
GNAME SNAME ONAME STATUS
---------- ---------- ---------- --------------------
REP_MYTEST REPTEST TB_TEST_CR ERROR
EATE_REP2
REP_MYTEST REPTEST TB_TEST_NO VALID
KEY_REP
REP_MYTEST REPTEST TB_TEST_NO VALID
KEY_REP$RP
REP_MYTEST REPTEST TB_TEST_NO VALID
KEY_REP$RP
GNAME SNAME ONAME STATUS
---------- ---------- ---------- --------------------
REP_MYTEST REPTEST TB_TEST_RE VALID
P
REP_MYTEST REPTEST TB_TEST_RE VALID
P$RP
REP_MYTEST REPTEST TB_TEST_RE VALID
P$RP
10 rows selected.
但是在向tb_test_nokey_rep表插入数据时,还是报错:
SQL> delete from reptest.tb_test_nokey_rep where id=100;
delete from reptest.tb_test_nokey_rep where id=100
*
ERROR at line 1:
ORA-04067: ????stored procedure "REPTEST.TB_TEST_NOKEY_REP$RP" ???
ORA-01085: ?? rpc ? "REPTEST.TB_TEST_NOKEY_REP$RP.REP_DELETE" ?????
ORA-02063: preceding 2 lines from SNAP
这个错误就是报存储过程不存在的问题,算了,还是重新注册一次。
SQL> execute dbms_repcat.suspend_master_activity('rep_mytest');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.drop_master_repobject(sname=>'reptest',oname=>'tb_test_nokey_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL> select sname,oname,status from dba_repobject;
SNAME ONAME STATUS
---------- ---------- --------------------
REPTEST TB_TEST_CR VALID
EATE_REP
REPTEST TB_TEST_CR VALID
EATE_REP$R
P
REPTEST TB_TEST_CR VALID
EATE_REP$R
P
SNAME ONAME STATUS
---------- ---------- --------------------
REPTEST TB_TEST_CR ERROR
EATE_REP2
REPTEST TB_TEST_RE VALID
P
REPTEST TB_TEST_RE VALID
P$RP
REPTEST TB_TEST_RE VALID
P$RP
SNAME ONAME STATUS
---------- ---------- --------------------
7 rows selected.
SQL> execute dbms_repcat.drop_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.drop_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_nokey_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_repcat.SET_COLUMNS(sname=>'reptest',oname=>'tb_test_nokey_rep',column_list=>'id,name');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.GENERATE_REPLICATION_SUPPORT(sname=>'reptest',oname=>'tb_test_nokey_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>true);
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.GENERATE_REPLICATION_SUPPORT(sname=>'reptest',oname=>'tb_test_create_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.drop_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep2',type=>'table');
PL/SQL procedure successfully completed.
SQL> select sname,oname,status from dba_repobject;
SNAME ONAME STATUS
---------- ---------- --------------------
REPTEST TB_TEST_CR VALID
EATE_REP
REPTEST TB_TEST_CR VALID
EATE_REP$R
P
REPTEST TB_TEST_CR VALID
EATE_REP$R
P
SNAME ONAME STATUS
---------- ---------- --------------------
REPTEST TB_TEST_NO VALID
KEY_REP
REPTEST TB_TEST_NO VALID
KEY_REP$RP
REPTEST TB_TEST_NO VALID
KEY_REP$RP
REPTEST TB_TEST_RE VALID
P
SNAME ONAME STATUS
---------- ---------- --------------------
REPTEST TB_TEST_RE VALID
P$RP
REPTEST TB_TEST_RE VALID
P$RP
9 rows selected.
查看数据同步的情况:
主体定义节点:
SQL> select count(1) from reptest.tb_test_create_rep;
COUNT(1)
----------
3
SQL> select * from reptest.tb_test_create_rep;
ID NAME
---------- --------------------
4 lirq
1 ???
2 ???
SQL> select count(1) from reptest.tb_test_nokey_rep;
COUNT(1)
----------
101
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
100 lirq
非主体定义节点:
SQL> select count(1) from reptest.tb_test_create_rep;
COUNT(1)
----------
3
SQL> select * from reptest.tb_test_create_rep;
ID NAME
---------- ----------
4 lirq
1 ???
2 ???
SQL> select count(1) from reptest.tb_test_nokey_rep;
COUNT(1)
----------
100
SQL> select count(1) from reptest.tb_test_nokey_rep;
COUNT(1)
----------
100
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
对于不含有主键的表复制,还是没有做成功。
查看错误
select source,status,timestamp,master,sname,oname,message from dba_repcatlog
order by timestamp desc
;
发现最近的几次错误都是23308或23309
在网上找了下关于23308和23309的错误,摘录如下:
在维护主备库的高级复制时,发现有一张表始终无法复制,复制时,总是报以下错误:
ORA-23309: object TEST.RECORD_DETAIL of type TABLE exists
ORA-23308: object TEST.RECORD_DETAIL does not exist or is invalid
ORA-23419: regenerate replication support before resuming master activity
解决:
偶然的机会,在比较主备库的表结构时,发现主库此表的user_id字段有非空约束,而备库没有此约束。
在给备库此表的user_id字段加上非空约束后,重新执行高级复制成功。
从内容上来看,说明两边表结构不一致时可能会出现这上错误。
回到开头,记得在非主体站点建表是通过CTAS创建的,这确实可能会造成表结构不一致,果然发现两表的NAME的字段类型上一个为varchar2(10),另一个为varchar2(20)。下面的调整就很简单了:
接下来要做的事情就简单了,请看下流程:
主体定义站点:
SQL> exec dbms_repcat.suspend_master_activity('rep_mytest');
PL/SQL procedure successfully completed.
非主体定义站点:
SQL> drop table reptest.tb_test_nokey_rep;
Table dropped.
SQL> create table reptest.tb_test_nokey_rep(id number(38),name varchar2(20));
Table created.
主体定义站点:
SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_nokey_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>true);
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.set_columns(sname=>'reptest',oname=>'tb_test_nokey_rep',column_list=>'id,name');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat.GENERATE_REPLICATION_SUPPORT(sname=>'reptest',oname=>'tb_test_nokey_rep',type=>'table');
PL/SQL procedure successfully completed.
SQL>
SQL> execute dbms_repcat.resume_master_activity('rep_mytest',true);
PL/SQL procedure successfully completed.
SQL>
SQL> select count(1) from reptest.tb_test_nokey_rep;
COUNT(1)
----------
101
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- ----------------------------------------
100 liyx
100 lirq
非主体定义站点:
SQL> select count(1) from reptest.tb_test_nokey_rep;
COUNT(1)
----------
101
SQL> select * from reptest.tb_test_nokey_rep where id=100;
ID NAME
---------- --------------------
100 liyx
100 lirq
总算是对不含主键的复制对象成功进行了复制。
5、顺便测试truncate操作是否可以同步:
5.1、操作前:
5.1.1、主体定义站点:
SQL> select count(1) from reptest.tb_test_create_rep;
COUNT(1)
----------
3
5.1.2、非主体定义站点:
SQL> select count(1) from reptest.tb_test_create_rep;
COUNT(1)
----------
0
5.2 、操作
SQL> truncate table reptest.tb_test_create_rep;
Table truncated.
5.3、操作后:
5.3.1、主体定义站点:
SQL> select count(1) from reptest.tb_test_create_rep;
COUNT(1)
----------
0
5.3.2、非主体定义站点:
SQL> select count(1) from reptest.tb_test_create_rep;
COUNT(1)
----------
3
从测试结果来看,同步的是DML操作,并不能对DDL操作进行同步,要想将TRUNCATE这种操作进行同步,必须通过重新注册复制对象来完成
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12350275/viewspace-683780/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12350275/viewspace-683780/