ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 高级复制-5、测试是否可同步DML操作

高级复制-5、测试是否可同步DML操作

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

两个节点中任意一上节点的数据发生修改,在另一个节点上是否能得到体现,这就是本节要测试的内容。

1、在主体定义站点和非主体定义站创建测试用户,测试表,和测试对象
在主体定义站点创建测试用户、测试表和测试对象:
SQL> create user reptest identified by reptest default tablespace users temporary tablespace temp quota unlimited on users;

User created.

SQL> grant connect to reptest;

Grant succeeded.

SQL> grant resource to reptest;

Grant succeeded.

SQL> conn reptest/reptest;
Connected.
SQL> create table tb_test_rep(id int primary key ,name varchar(10));      

Table created.

SQL>  declare
  2     i int;
  3     v_sql varchar2(100);
  4     begin
  5         for i in 1 .. 100 loop
  6            v_sql:='insert into tb_test_rep values(:x,''liyx'')';
  7            execute immediate v_sql using i;
  8         end loop;
  9         commit;
 10    end;
 11   /

PL/SQL procedure successfully completed.

SQL> select count(1) from tb_test_rep;

  COUNT(1)
----------
       100

SQL> select * from tb_test_rep where rownum<10;

        ID NAME
---------- --------------------
         1 liyx
         2 liyx
         3 liyx
         4 liyx
         5 liyx
         6 liyx
         7 liyx
         8 liyx
         9 liyx

9 rows selected.

在非主体定义数据库上进行同样的操作:
SQL> create user reptest identified by reptest default tablespace users temporary tablespace temp quota unlimited on users;

User created.

SQL> grant connect to reptest;

Grant succeeded.

SQL>  grant resource to reptest;

Grant succeeded.

SQL> conn reptest/reptest@replication_site
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> create table tb_test_rep(id int primary key ,name varchar(10));            

Table created.

SQL> insert into tb_test_rep select * from reptest.tb_test_rep@master.com;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from tb_test_rep;

  COUNT(1)
----------
       100

两边数据已经相同。

2、在主体定义站点的主体组里添加对象
execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>false);

SQL> conn repadmin/repadmin
Connected.
SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>false);

PL/SQL procedure successfully completed.

查看复制组同的对象:
SQL> desc dba_repobject;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 SNAME                                                                                                                      VARCHAR2(30)
 ONAME                                                                                                                      VARCHAR2(30)
 TYPE                                                                                                                       VARCHAR2(16)
 STATUS                                                                                                                     VARCHAR2(10)
 GENERATION_STATUS                                                                                                          VARCHAR2(9)
 ID                                                                                                                         NUMBER
 OBJECT_COMMENT                                                                                                             VARCHAR2(80)
 GNAME                                                                                                                      VARCHAR2(30)
 MIN_COMMUNICATION                                                                                                          VARCHAR2(1)
 REPLICATION_TRIGGER_EXISTS                                                                                                 VARCHAR2(1)
 INTERNAL_PACKAGE_EXISTS                                                                                                    VARCHAR2(1)
 GROUP_OWNER                                                                                                                VARCHAR2(30)
 NESTED_TABLE                                                                                                               VARCHAR2(1)

SQL> col object_name format a20;
SQL> col gname format a10;
SQL> col oname format a10;
SQL> col sname format a10;
SQL> select gname,sname,oname,status from dba_repobject where gname='REP_MYTEST';

GNAME      SNAME      ONAME      STATUS
---------- ---------- ---------- --------------------
REP_MYTEST REPTEST    TB_TEST_RE VALID
                      P

3、对复制对象产生复制支持
exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('REPTEST','TB_TEST_REP','TABLE');


SQL> exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('REPTEST','TB_TEST_REP','TABLE');

PL/SQL procedure successfully completed.

SQL> select gname, master, status from dba_repgroup where gname='REP_TEST';

no rows selected

SQL> select gname, master, status from dba_repgroup where gname='REP_MYTEST';

GNAME      MA STATUS
---------- -- ------------------
REP_MYTEST Y  QUIESCED

SQL> select sname,oname,status,gname from dba_repobject where gname='REP_MYTEST';

SNAME      ONAME      STATUS               GNAME
---------- ---------- -------------------- ----------
REPTEST    TB_TEST_RE VALID                REP_MYTEST
           P

REPTEST    TB_TEST_RE VALID                REP_MYTEST
           P$RP

REPTEST    TB_TEST_RE VALID                REP_MYTEST
           P$RP

4、到非主体定义站点查看复制对象:
SQL> conn repadmin/repadmin@replication_site
Connected.
SQL> col sname for a10;
SQL> col oname for a10;
SQL> col gname for a10;
SQL>  select sname,oname,status,gname from dba_repobject where gname='REP_MYTEST';

SNAME      ONAME      STATUS     GNAME
---------- ---------- ---------- ----------
REPTEST    TB_TEST_RE VALID      REP_MYTEST
           P

REPTEST    TB_TEST_RE VALID      REP_MYTEST
           P$RP

REPTEST    TB_TEST_RE VALID      REP_MYTEST
           P$RP

5、到主体定义站点去启动复制
execute dbms_repcat.resume_master_activity('REP_MYTEST',true);


SQL> execute dbms_repcat.resume_master_activity('REP_MYTEST',true);

PL/SQL procedure successfully completed.

6、在主体定义站点对数据进行DML操作,查看非主体定义站点的数据变化:
6.1 删除数据:
6.1.1 主体定义站点:
SQL> conn reptest/reptest
Connected.
SQL> delete from tb_test_rep where rownum<10;

9 rows deleted.

SQL> select count(1) from tb_test_rep;

  COUNT(1)
----------
        91

SQL> commit;

Commit complete.

6.1.2 非主体定义站点:

SQL> conn reptest/reptest@replication_site
Connected.
SQL> select count(1) from tb_test_rep;

  COUNT(1)
----------
        91

6.2 更新数据:
6.2.1 主体定义站点:
SQL> update tb_test_rep set name='lirq' where id=10;

1 row updated.

SQL> select * from tb_test_rep where id=10;

        ID NAME
---------- --------------------
        10 lirq

SQL> commit;

Commit complete.

SQL> select * from tb_test_rep where id=10;

        ID NAME
---------- --------------------
        10 lirq

6.2.2 主体非定义站点:
6.2.2.1 更新前:
SQL> select * from tb_test_rep where id=10;

        ID NAME
---------- ----------
        10 liyx
6.2.2.2 更新未提交:
SQL> select * from tb_test_rep where id=10;

        ID NAME
---------- ----------
        10 liyx
6.2.2.3 更新已提交:
SQL> select * from tb_test_rep where id=10;

        ID NAME
---------- ----------
        10 lirq

6.3  插入数据:
6.3.1 主体定义站点:
SQL> insert into tb_test_rep values(101,'yaocb');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tb_test_rep where id=101;

        ID NAME
---------- --------------------
       101 yaocb
6.3.2非主体定义站点:
6.3.2.1 插入前:
SQL> select * from tb_test_rep where id=101;

no rows selected
6.3.2.2 插入后:
SQL> select * from tb_test_rep where id=101;

        ID NAME
---------- ----------
       101 yaocb

7、在非主体定义站点对数据进行DML操作,查看主体定义站点的数据变化:
7.1、删除数据:
7.1.1、非主体定义站点:
SQL> select count(1) from tb_test_rep;                                      

  COUNT(1)
----------
        92

SQL> delete from tb_test_rep where id between 91 and 100;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(1) from tb_test_rep;

  COUNT(1)
----------
        82

7.1.2、主体定义站点:
7.1.2.1、删除前:
SQL> select count(1) from tb_test_rep;

  COUNT(1)
----------
        92
7.1.2.2、删除后:
SQL> select count(1) from tb_test_rep;

  COUNT(1)
----------
        82

7.2 更新
7.2.1、非主体定义站点:
SQL> select * from tb_test_rep where id=12;

        ID NAME
---------- ----------
        12 liyx

SQL> update tb_test_rep set name='liyx-yaocb' where id=12;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from tb_test_rep where id=12;

        ID NAME
---------- ----------
        12 liyx-yaocb
7.2.2、主体定义站点:
7.2.2.1、更新前
SQL> select * from tb_test_rep where id=12;

        ID NAME
---------- --------------------
        12 liyx
7.2.2.2、更新后
SQL>  select * from tb_test_rep where id=12;

        ID NAME
---------- --------------------
        12 liyx-yaocb

7.3 插入
7.3.1、非主体定义站点:
SQL> insert into tb_test_rep values(102,'liyxbb');

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from tb_test_rep where id=102;

        ID NAME
---------- ----------
       102 liyxbb

7.3.2、主体定义站点:
7.3.2.1、插入前
SQL> select * from tb_test_rep where id=102;

no rows selected
7.3.2.2、插入后
SQL>  select * from tb_test_rep where id=102;

        ID NAME
---------- --------------------
       102 liyxbb

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12350275/viewspace-683778/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2010-12-24

  • 博文量
    24
  • 访问量
    109514