ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 高级复制-7、测试是否非主键表的同步

高级复制-7、测试是否非主键表的同步

原创 Linux操作系统 作者:lirenquan 时间:2011-01-13 19:01:44 0 删除 编辑

由于高级复制中明确要求复制的表需要有主键,那么如果没有主键复制是否能正常进行,如果不能正常进行那么有什么补救措施,这就是本节要测试的内容。
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/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2010-12-24

  • 博文量
    24
  • 访问量
    109531