goldengate的实施过程

1125_查询分发库信息_02.txt

建用户：
空表我们来建  select * from  where 1=2
授权          insert,update,delete to ggt

-----在源端机器上操作：                                                                   
在/gglog目录下建立日志存放目录：                                                        
mkdir -p /gglog/dirdat/esxdb905 

---编辑mgr参数 
edit param mgr                                                                         
purgeoldextracts /gglog/dirdat/esxdb905/ex*, usecheckpoints, minkeepdays 7                                                          
                                                                                                                                                           
--/已添加开启表级附加日志                                                                        
--/ggsci                                                                                   
--/dblogin userid ggt, password delong##cafe                                            
--/add trandata db_sbzs.t_zs_yzmx

所有表查看有没超过32列字段,超过32列的话：
sqlplus:
alter table db_gsxt.t_gs_grjbxx add supplemental log group ggs_t_gs_grjbxx_01 (NSGRNBM,GJDQ_DM,SFZMLB_DM,SFZMHM,XM,XM_E,CS_RQ,XB,LX_DH,SSGJDQ_DM,TX_DZ,YZBM,JWZZ_DZ,JWFWDW,JTRK,SBBH,JZFWLB_DM,HJDSWJG_DM,PZSDFS_DM,GRCXMM,ZHDJZT_DM,CZRY_DM,LR_SJ,XG_SJ,SFJS,HYZXL_DM,DJZCLX_DM,LSGX_DM,DH_RQ,JCJZDXQXZQY_DM,HJSZDXQXZQY_DM,HJSZD_DZ)
always;

alter table db_gsxt.t_gs_grjbxx add supplemental log group ggs_t_gs_grjbxx_02 (CSD,JLXKHM,LDJYZHM,XL_DM,ZYZC_DM,JWPQDW_DZ,EMAIL_DZ,SJHM,NDHZZXSBHJDSWJG_DM,GRZHMMZT,JCJZD_DZ) always;

 

                                                                                
增加抽取进程esxdb905
add extract esxdb905, tranlog, begin now
add exttrail /gglog/dirdat/esxdb905/ex, extract esxdb905,megabytes 200
edit params esxdb905

加入下面内容：
extract esxdb905
OBEY /software/ggs/dirprm/pwd.obey
tranlogoptions rawdeviceoffset 0
exttrail /gglog/dirdat/esxdb905/ex
table db_sbzs.t_zs_yzmx;

 

 

增加传输进程psxfx905
ggsci
add extract psxfx905, exttrailsource /gglog/dirdat/esxdb905/ex
add rmttrail /gglog/dirdat/rsxdb905/re, extract psxfx905, megabytes 200

edit params psxfx905

extract psxfx905
rmthost nhdb26, mgrport 7820
passthru
rmttrail /gglog/dirdat/rsxdb905/re
table db_sbzs.t_zs_yzmx;

 

 

5、创建生成表的定义结构：
cd /software/ggs/dirprm
vi dsxdb905.prm

defsfile /software/ggs/dirdef/dsxdb905.def, purge
OBEY /software/ggs/dirprm/pwd.obey
table db_sbzs.t_zs_yzmx;

 

生成表结构定义文件:
cd /software/ggs
defgen paramfile /software/ggs/dirprm/dsxdb905.prm

再把dsxdb905.def拷贝到目标端/software/ggs/dirdef/目录下

 

                                                                                        
-----在目标机器上操作：                                                            
在/gglog目录下建立日志存放目录：                                                        
mkdir -p /gglog/dirdat/rsxdb905                                                      
mkdir -p /gglog/dirrpt/rsxdb905 

---编辑mgr参数   
edit param mgr                                                                       
purgeoldextracts /gglog/dirdat/rsxdb905/re*, usecheckpoints, minkeepdays 7

增加复制进程rsxdb905

ggsci
add replicat rsxdb905, exttrail /gglog/dirdat/rsxdb905/re, checkpointtable ggt.ckptsxdb
    
edit params rsxdb905

replicat rsxdb905
sourcedefs /software/ggs/dirdef/dsxdb905.def
handlecollisions
batchsql
SETENV ( NLS_LANG = ".ZHS16GBK") 
OBEY /software/ggs/dirprm/pwd.obey
discardfile /gglog/dirrpt/rsxdb905/reprsxdb905.dsc,append,megabytes 100
map db_sbzs.t_zs_yzmx, target db_sbzs.t_zs_yzmx,  colmap (usedefaults,sjjhpt_sj=@getenv("GGHEADER", "COMMITTIMESTAMP")),    keycols (PZ_XH, YZMX_XH);

 

 

启动抽取进程
alter extract esxdb905,tranlog,begin now
alter extract psxfx905,begin now
start esxdb905
start psxfx905
info all 

   不启动复制进程

下面一步
SQL> select to_char(dbms_flashback.get_system_change_number,99999999999999999999999999) getscn  from dual;

GETSCN
---------------------------
             12557678449691

CREATE PUBLIC DATABASE LINK DB_LINK_SXDB
 CONNECT TO ggt
 IDENTIFIED BY delong##cafe
 USING 'sxdb';

 

数据初始化
dblink模式并且加主键
conn DB_SBZS/DB_SBZS
insert /*+ append parallel(T_ZS_YZMX,8) */  into  T_ZS_YZMX  nologging select a.*,sysdate fromggt.V_ZS_YZMX_1124_P244000000@DB_LINK_SXDB a       ;
commit;

create unique index db_sbzs.PK_T_ZS_YZMX        on db_sbzs.T_ZS_YZMX(PZ_XH, YZMX_XH)    tablespace TS_SBZS_IDX2 nologging parallel 20;     
alter table db_sbzs.T_ZS_YZMX        add constraint  PK_T_ZS_YZMX        primary key  (PZ_XH, YZMX_XH)  using index    db_sbzs.PK_T_ZS_YZMX;      
alter table  db_sbzs.T_ZS_YZMX add sjjhpt_dz number(14) ;

 

启动目标库复制进程

start rsxdb905, aftercsn 12557678449691

stats rsxdb905