高階複製-5、測試是否可同步DML操作
兩個節點中任意一上節點的資料發生修改，在另一個節點上是否能得到體現，這就是本節要測試的內容。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 高階複製-6、測試是否可同步建表
- 高階複製-7、測試是否非主鍵表的同步
- OGG雙向DML複製操作
- GoldenGate同步DML功能測試Go
- oracle 高階複製Oracle
- Oracle高階複製Oracle
- 高階複製總結
- 關於高階複製的一些資料同步
- 高階複製主站點主表新增欄位操作
- MySQL 5.5 Semi-sync 半同步複製測試MySql
- Oracle高階複製Step by StepOracle
- oracle高階複製（轉載）Oracle
- oracle 高階複製簡介Oracle
- 複製管理工具介紹——高階複製
- 高階複製-3、主體定義站點上的操作
- [zt] 高階複製、流複製（Streams）、備庫區別
- 高階複製-2、準備工作
- RMAN高階應用之Duplicate複製資料庫(5)補充資料庫
- 高階複製-4、非主體定義站點上的操作
- 主體複製概念和體系結構——高階複製
- MySQL 的主從複製（高階篇）MySql
- 關於Oracle 高階複製的概念Oracle
- Oracle 高階複製的概念及配置Oracle
- 物化檢視複製概念和體系結構——高階複製
- RMAN高階應用之Duplicate複製資料庫(3)複製流程資料庫
- 改變複製物件結構對高階複製的影響物件
- MySQL 同步複製及高可用方案總結MySql
- MySQL的非同步複製和半同步複製MySql非同步
- 高階複製初步研究－2005-2-5 更新到版本0.6
- 學習Advanced Replication(高階複製) -zt
- Oracle 10g高階複製例項Oracle 10g
- 測試CMS同步測試CMS同步測試CMS同步
- Oracle9i高階複製（單向複製部分）實施完畢Oracle
- “高階”的程式語言是否真的高階？
- MySQL 8 複製（一）——非同步複製MySql非同步
- MySQL 8 複製（二）——半同步複製MySql
- MySQL進階：主主複製+Keepalived高可用MySql
- 資料庫移植後高階複製出錯資料庫