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

如果一个站点上已经有表，另一个节点上没有表，那么数据库的迁移是否能完成，什么情况下能完成，这就是本节要测试的内容。
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12350275/viewspace-683779/