高階複製-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/，如需轉載，請註明出處，否則將追究法律責任。