Master Replication (一)

时间：2010-06-15 来源：rushmeplz

基本配置环境 RAC和单实例双向同步，同步和异步方式 SQL> select * from gv$version;    INST_ID BANNER
---------- ----------------------------------------------------------------
         2 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
         2 PL/SQL Release 10.2.0.4.0 - Production
         2 CORE 10.2.0.4.0      Production
         2 TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
         2 NLSRTL Version 10.2.0.4.0 - Production
         1 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
         1 PL/SQL Release 10.2.0.4.0 - Production
         1 CORE 10.2.0.4.0      Production
         1 TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
         1 NLSRTL Version 10.2.0.4.0 - Production SQL> select * from v$version; BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production 1.配置复制管理用户repadmin(所有节点) create user repadmin identified by repadmin; execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin; 2.启用global_name(所有节点) alter system set global_names=true scope=spfile; alter system set db_domain='lab.com' scope=spfile; alter database rename global_name to gc.lab.com; alter database rename global_name to orcl.lab.com; select * from global_name; 3.建立dblink(所有节点) ORCL上 CREATE PUBLIC DATABASE LINK "GC.LAB.COM"
CONNECT TO SYSTEM
IDENTIFIED BY <PWD>
USING 'GC'; GC上 CREATE PUBLIC DATABASE LINK "ORCL.LAB.COM"
CONNECT TO SYSTEM
IDENTIFIED BY <PWD>
USING 'orcl1'; 通过select * from global_name@dblinkname来测试创建私有database link,用于拉起和停止节点repgroup的状态 repadmin下 ORCL CREATE DATABASE LINK "GC.LAB.COM"
CONNECT TO REPADMIN
IDENTIFIED BY <PWD>; GC CREATE DATABASE LINK "ORCL.LAB.COM"
CONNECT TO REPADMIN
IDENTIFIED BY <PWD>; 通过select * from global_name@dblinkname来测试 4.双向复制环境建立(同步) masterdef节点(ORCL)建立复制组 SQL> exec dbms_repcat.create_master_repgroup('sync_rg'); 建立复制组内的复制对象 SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'am_cnt',type =>'table',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true); 为对象建立内置package和trigger SQL> exec dbms_repcat.generate_replication_support('test','am_cnt','table'); 添加master对象(GC) SQL> exec dbms_repcat.add_master_database(gname =>'sync_rg',master =>'gc.lab.com',use_existing_objects =>true,copy_rows =>true,propagation_mode =>'synchronous'); use_existing_objects:Indicate true if you want to reuse any objects of the same type and shape that already exist in the schema at the new master site. copy_rows:Indicate true if you want the initial contents of a table at the new master site to match the contents of the table at the master definition site. 这样就可以通过复制把源上的表和数据同步过去激活repgroup SQL> exec dbms_repcat.resume_master_activity('sync_rg',true); 查看复制组状态 SQL> select sname,status from dba_repgroup; SNAME                          STATUS
------------------------------ ---------
SYNC_RG                        NORMAL SQL> select sname,status from dba_repgroup; SNAME                          STATUS
------------------------------ ---------
SYNC_RG                        NORMAL 查看管理日志 select * from dba_repcatlog; 状态为ready,exec dbms_repcat.do_deferred_repcat_admin('"SYNC_RG"', FALSE);来执行请求状态为await_callback,等待其他master执行并返回结果状态为error,请求执行错误状态为do_callback,出现在master上，表示要通知masterdef执行结果 test.am_cnt表结构 SQL> desc test.am_cnt;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DJLX                                      NOT NULL VARCHAR2(4)
XDJLX                                     NOT NULL VARCHAR2(4)
QSRQ                                               VARCHAR2(8)
JZRQ                                               VARCHAR2(8)
OPRID                                              VARCHAR2(8)
ADTID                                              VARCHAR2(8)
RQ                                                 VARCHAR2(8)
HTH                                       NOT NULL VARCHAR2(6)
SUPID                                              VARCHAR2(4)
CORPID                                             VARCHAR2(2)
YRSTRID                                            VARCHAR2(10)
CHARACTER                                          VARCHAR2(1)
KIND                                               VARCHAR2(1)
LEGALMAN                                           VARCHAR2(8)
JYFS                                               VARCHAR2(1)
GLFS                                               VARCHAR2(1)
DELIVERYTYPE                                       VARCHAR2(1)
DELIVERYADDRESS                                    VARCHAR2(1)
REACHDAYS                                          NUMBER(5)
ORDERTIMES                                         NUMBER(5)
PAYTYPE                                            VARCHAR2(1)
PAYDAYS                                            NUMBER(5)
BILLTYPE                                           VARCHAR2(1)
TAX_I                                              NUMBER(5,4)
TAX_S                                              NUMBER(5,4)
SIGNDATE                                           VARCHAR2(8)
STARTDATE                                          VARCHAR2(8)
ENDDATE                                            VARCHAR2(8)
AREA                                               FLOAT(126)
MYSIGNATORY                                        VARCHAR2(8)
BM                                                 VARCHAR2(10)
SIGNATORY                                          VARCHAR2(8)
LINKMAN                                            VARCHAR2(8)
TEL                                                VARCHAR2(36)
BP                                                 VARCHAR2(36)
MOBILETEL                                          VARCHAR2(36)
FAX                                                VARCHAR2(36)
ADDR                                               VARCHAR2(60)
ZIPCODE                                            VARCHAR2(12)
TAXNO                                              VARCHAR2(36)
ACCOUNT                                            VARCHAR2(36)
BANK                                               VARCHAR2(36)
WSXKZH                                             VARCHAR2(36)
SHSXF                                              FLOAT(126)
YYZZH                                              VARCHAR2(36)
KHF                                                FLOAT(126)
ZXSPTH                                             VARCHAR2(1)
BZHSPTH                                            VARCHAR2(1)
JJXSP                                              VARCHAR2(1)
SXQ                                                NUMBER(5)
XYKJS                                              VARCHAR2(1)
YJKCFF                                             VARCHAR2(1)
JS                                                 VARCHAR2(1)
JSRQ1                                              NUMBER(5)
JSRQ2                                              NUMBER(5)
JSRQ3                                              NUMBER(5)
JSRQSET                                            VARCHAR2(1)
ZCKL                                               FLOAT(126)
CEKL                                               FLOAT(126)
CEKL2                                              FLOAT(126)
BDXSJE                                             FLOAT(126)
JHXSJE                                             FLOAT(126)
VIPBL                                              FLOAT(126)
JLBL                                               FLOAT(126)
ZRJBL                                              FLOAT(126)
FLG_ORD                                            VARCHAR2(1)
FLG_JS                                             VARCHAR2(1)
FLG_OUT                                            VARCHAR2(1)
AREA_HT                                            VARCHAR2(4)
LSLX                                               VARCHAR2(1)
SPLY                                               VARCHAR2(1)
CGY                                                VARCHAR2(8)
NUM1                                               FLOAT(126)
NUM2                                               FLOAT(126)
STR1                                               VARCHAR2(100)
STR2                                               VARCHAR2(100)
DHMEMO                                             VARCHAR2(200)
NOTES                                              VARCHAR2(100)
REFUNDTYPE                                         VARCHAR2(1)
REFUNDRATIO                                        FLOAT(126)
PREPAYMENTRATIO                                    FLOAT(126)
ARRIVALWAY                                         VARCHAR2(1) 测试 SQL> delete from test.am_cnt where yrstrid='asdf'; 1 row deleted. SQL> commit; Commit complete. SQL> select count(*) from test.am_cnt; COUNT(*)
----------
        20 SQL> select count(*) from [email protected]; COUNT(*)
----------
        20 5.添加与减少复制对象(masterdef) 停止repobject SQL> exec dbms_repcat.suspend_master_activity(gname =>'sync_rg'); 添加复制对象 SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'am_cntgds',type =>'table',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true); 增加复制支持trigger和package SQL> exec dbms_repcat.generate_replication_support('test','am_cntgds','table'); 激活repgroup SQL> exec dbms_repcat.resume_master_activity('sync_rg',true); 删除复制对象 dbms_repcat.drop_master_repobject和dbms_repcat.drop_repgroup 6.双向同步复制(异步) 在masterdef节点添加master时指定propagation_mode='asynchronous'; SQL> exec dbms_repcat.add_master_database(gname =>'sync_rg',master =>'gc.lab.com',use_existing_objects =>true,copy_rows =>true,propagation_mode =>'asynchronous'); 异步复制需要job支持使得定期同步复制(所有节点) ORCL begin
dbms_defer_sys.schedule_push (destination => 'gc.lab.com',
interval => 'sysdate + 10/1440',
next_date => sysdate);
end;
/ 　--10分钟1次push数据到gc.lab.com begin
dbms_defer_sys.schedule_purge (next_date => sysdate,
interval => 'sysdate + 10/1440',
delay_seconds => 0,
);
end;
/   --10分钟1次清除延迟队列的job GC begin
dbms_defer_sys.schedule_push (destination => 'orcl.lab.com',
interval => 'sysdate + 10/1440',
next_date => sysdate);
end;
/ 　--10分钟1次push数据到orcl.lab.com begin
dbms_defer_sys.schedule_purge (next_date => sysdate,
interval => 'sysdate + 10/1440',
delay_seconds => 0,
);
end;
/   --10分钟1次清除延迟队列的job
7.维护 masterdef节点因故障切换 GC masterdef节点交换 SQL> exec dbms_repcat.relocate_masterdef(gname =>'sync_rg',old_masterdef =>'orcl.lab.com',new_masterdef =>'gc.lab.com',notify_masters =>true,include_old_masterdef =>false); SQL> exec dbms_repcat.suspend_master_activity('sync_rg'); 删除对端master节点 SQL> exec dbms_repcat.remove_master_databases(gname =>'sync_rg',master_list =>'orcl.lab.com'); 当ORCL恢复后 SQL> exec dbms_repcat.drop_master_repgroup('sync_rg'); GC exec dbms_repcat.add_master_database(gname =>'sync_rg',master =>'orcl.lab.com',use_existing_objects =>true,copy_rows =>true,propagation_mode =>'synchronous'); 8.关于procedure和view的复制 procedure和view只是简单同步，而不需要生成双向复制，如 SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'am_cntgds_vw',type =>'view',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true); SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'proced',type =>'procedure',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true); 不需要dbms_repcat.generate_replication_support