ITPub博客

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

高级复制-6、测试是否可同步建表

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

如果一个站点上已经有表,另一个节点上没有表,那么数据库的迁移是否能完成,什么情况下能完成,这就是本节要测试的内容。
1、在主体定义站点建表,但是非主体定义站点,通过高级复制完成把整个表的数据和结构复制到非主体定义站点
1.1、创建表,并确认环境的过程
SQL> conn reptest/reptest
Connected.
SQL> create table tb_test_create_rep (id int ,name varchar(10));

Table created.

SQL> insert into tb_test_create_rep  values(1,'小明明');

1 row created.

SQL> insert into tb_test_create_rep values(2,'小小明');
ERROR:
ORA-01756: quoted string not properly terminated


SQL> insert into tb_test_create_rep values(2,'小小明');

1 row created.

SQL> insert into tb_test_create_rep values(3,'小小小');


1 row created.

SQL> commit;

Commit complete.

SQL> alter table tb_test_create_rep add constraint pk_test_create_rep primary key (id);

Table altered.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------------------------------------
TB_TEST_REP
TB_TEST_CREATE_REP

SQL> select * from tb_test_create_rep;

        ID NAME
---------- --------------------
         1 ???
         2 ???
         3 ???

查询非主体点:
-bash-3.2$ sqlplus reptest/reptest@replication_site

SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 12月 9 21:01:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TB_TEST_REP

找不到创建的表

1.2、将对象加入到主体组
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);
BEGIN dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>true); END;

*
ERROR at line 1:
ORA-23310: object group "PUBLIC"."REP_MYTEST" is not quiesced
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 6187
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2546
ORA-06512: at "SYS.DBMS_REPCAT", line 562
ORA-06512: at line 1


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=>false);
BEGIN dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>false); END;

*
ERROR at line 1:
ORA-23310: object group "PUBLIC"."REP_MYTEST" is not quiesced
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 6187
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2546
ORA-06512: at "SYS.DBMS_REPCAT", line 562
ORA-06512: at line 1


SQL> execute dbms_repcat.resume_master_activity('REP_MYTEST',false);
BEGIN dbms_repcat.resume_master_activity('REP_MYTEST',false); END;

*
ERROR at line 1:
ORA-23310: object group "PUBLIC"."REP_MYTEST" is not quiesced
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 3509
ORA-06512: at "SYS.DBMS_REPCAT", line 826
ORA-06512: at line 1


SQL> dbms_repcat.suspend_master_activity('rep_hh1');   
SP2-0734: unknown command beginning "dbms_repca..." - rest of line ignored.
SQL> exec dbms_repcat.suspend_master_activity('rep_hh1');
BEGIN dbms_repcat.suspend_master_activity('rep_hh1'); END;

*
ERROR at line 1:
ORA-23312: not the masterdef according to MASTER.COM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 891
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 4194
ORA-06512: at "SYS.DBMS_REPCAT", line 946
ORA-06512: at line 1


SQL> show user
USER is "REPADMIN"
SQL> exec dbms_repcat.suspend_master_activity('rep_mytest');

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.resume_master_activity('REP_MYTEST',true);

PL/SQL procedure successfully completed.

从上面显示的错误信息来看,要添加复制对象到主体组中去,必须先把主体组置于静默状态,待加入后,再把它激活

1.3 在非主体定义节点上查看是否已经完成复制过程
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TB_TEST_CREATE_REP
TB_TEST_REP

SQL> select * from tb_test_create_rep;

        ID NAME
---------- ----------
         1 ???
         2 ???
         3 ???

OK,表已经建好,并且数据也完成了复制

2、测试在非主体定义站点上建表,再复制到主体站点上(应该来说是不行的,因为添加复制对象只能在主体定义站点完成)
2.1 在非主体定义站点上创建测试环境
SQL> conn reptest/reptest
Connected.
SQL> create table tb_test_create_rep2(id int ,name varchar(10));

Table created.

SQL> insert into tb_test_create_rep2  values(1,'小明明');

1 row created.


SQL> insert into tb_test_create_rep2 values(2,'小小明');

1 row created.

SQL> insert into tb_test_create_rep2 values(3,'小小小');


1 row created.

SQL> commit;

Commit complete.

SQL> alter table tb_test_create_rep add constraint pk_test_create_rep primary key (id);

Table altered.

SQL> select * from tb_test_create_rep2;

        ID NAME
---------- ----------
         1 小明明
         2 小小明
         3 小小小

2.2 在主体定义站点上查看
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------------------------------------
TB_TEST_REP
TB_TEST_CREATE_REP

2.3 在主体定义节点添加复制对象到主体组中去
SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep2', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>true);
BEGIN dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep2', type=>'table',use_existing_object=>true,gname=>'rep_mytest',copy_rows=>true); END;

*
ERROR at line 1:
ORA-23308: object REPTEST.TB_TEST_CREATE_REP2 does not exist or is invalid
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2627
ORA-06512: at "SYS.DBMS_REPCAT", line 562
ORA-06512: at line 1


SQL> execute dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep2', type=>'table',use_existing_object=>false,gname=>'rep_mytest',copy_rows=>true);
BEGIN dbms_repcat.create_master_repobject(sname=>'reptest',oname=>'tb_test_create_rep2', type=>'table',use_existing_object=>false,gname=>'rep_mytest',copy_rows=>true); END;

*
ERROR at line 1:
ORA-23309: object reptest.tb_test_create_rep2 of type TABLE exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2552
ORA-06512: at "SYS.DBMS_REPCAT", line 562
ORA-06512: at line 1

显然,只能在主体定义节点上添加了表,才能进行同步

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

全部评论

注册时间:2010-12-24

  • 博文量
    24
  • 访问量
    109514