ogg双向同步配置

ogg 双向同步：
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:/lib:/usr/lib

useradd -g oinstall -G oracle ogg
echo ogg_123|passwd ogg --stdin

更改数据设置
开启归档
mkdir /u01/archive
 alter system set log_archive_dest_1='location=/u01/archive' scope=spfile;
开启forceloggin
alter database force logging;
开启最小化捕捉日志
alter database add supplemental log data;
select log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

shutdown immediate
startup mount;
alter database archivelog;
alter database open;
 select log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
 
创建ogg表空间和用户
create tablespace ogg datafile '/u01/app/oracle/oradata/orcl/ogg01.dbf' size 10g autoextend on;
craete user ogg identified by ogg_123 default tablespace ogg;

grant dba to ogg;
grant connect to  ogg;
grant alter any table to  ogg;
grant alter session to  ogg;
grant create session to  ogg;
grant flashback any table to  ogg;
grant select any dictionary to  ogg;
grant select any table to  ogg;
grant resource to  ogg;
grant select any transaction to  ogg;
grant execute on utl_file to ogg;
grant dba to ogg;
@marker_setup ogg
@ddl_setup 
@role_setup
grant GGS_GGSUSER_ROLE to ogg;
@ddl_enable
@marker_status.sql
@?/rdbms/admin/dbmspool.sql
@ddl_pin.sql ogg
edit params mgr :

PORT 7809
dynamicportlist 7800-8000
autorestart extract *,retries 5,waitminutes 2,resetminutes 5
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1  
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

 dblogin userid ogg, password ogg_123
 
 
 select owner||'.'||table_name table_name,logging from dba_tables   where owner='HIS' ;
 
 
SELECT table_name FROM user_tables A
WHERE 
    NOT EXISTS (SELECT * FROM user_constraints b WHERE A .table_name = b.table_name AND b.constraint_type = 'P');
    
内端：    
增加抽取进程：
 add extract ext_nw, tranlog, begin now
EXTRACT added.
info all

添加源端的队列文件。
add EXTTRAIL /home/ogg/ogg/dirdat/e1, extract ext_nw,megabytes 512

edit param ext_nw

EXTRACT ext_nw
setenv (ORACLE_SID = orcl)
setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg_123
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE /home/ogg/ogg/dirrpt/ext_nw.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 3:00
exttrail /home/ogg/ogg/dirdat/e1
dynamicresolution
TRANLOGOPTIONS EXCLUDEUSER ogg
DBOPTIONS  ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 5m
FETCHOPTIONS NOUSESNAPSHOT
TABLEEXCLUDE his.CGMX;
TABLEEXCLUDE his.CRB; 
TABLEEXCLUDE his.CSKC;
TABLEEXCLUDE his.LIS_CONFIRM_LOG;
TABLEEXCLUDE his.LSDSJ;
TABLEEXCLUDE his.PBCATCOL;
TABLEEXCLUDE his.PBCATEDT;
TABLEEXCLUDE his.PBCATFMT;
TABLEEXCLUDE his.PBCATTBL;
TABLEEXCLUDE his.PBCATVLD;
TABLEEXCLUDE his.STUDENT;
TABLEEXCLUDE his.TEMP_YFKC;
TABLEEXCLUDE his.TEMP_YF_JXC;
TABLEEXCLUDE his.TJ_ICNRIS_HISORDER;
TABLEEXCLUDE his.TJ_ICNRIS_HISORDER_DETAIL;
TABLEEXCLUDE his.T_HIS_BUS_JKFP_PKRKXXBF;
TABLEEXCLUDE his.T_HIS_SYS_BUS_YPDMLS;
TABLEEXCLUDE his.T_TJ_FMYGHYFB;
TABLEEXCLUDE his.T_TJ_YYQKB;
TABLEEXCLUDE his.T_TJ_ZYZLQKB;
TABLEEXCLUDE his.XNH_YPML;
TABLEEXCLUDE his.XNH_ZLML;
TABLE his.*;

2.配置投递进程
配置datapump进程，将抓取数据传到目标主机。负责TCPIP通讯。

add extract dp_nw, exttrailsource /home/ogg/ogg/dirdat/e1      
add rmttrail /home/ogg/ogg/dirdat/e2,EXTRACT dp_nw,MEGABYTES 512

配置datapump进程参数：
 edit param dp_nw

extract dp_nw
dynamicresolution
passthru
rmttrail /home/ogg/ogg/dirdat/e2
numfiles 5000
rmthost 192.160.90.250, mgrport 7809, compress
TABLE his.*;

3.目标端配置rep进程
为replicat进程创建checkpoint表：
 dblogin userid ogg,password ogg_123
add checkpointtable ogg.nw_ckpt 
edit param ./GLOBALS 
GGSCHEMA ogg 
CHECKPOINTTABLE ogg.nw_ckpt  
add replicat rep_nw,exttrail /home/ogg/ogg/dirdat/e2,checkpointtable ogg.nw_ckpt;
分析：
exttrail：表示这个进程获取数据的来源是什么地方，是我们源端rmttrail所设置过的。

22、配置目标端replicate参数：

REPLICAT rep_nw
SETENV (ORACLE_SID = orcl)
setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
USERID ogg,PASSWORD ogg_123
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE /home/ogg/ogg/dirrpt/rep_nw.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
MAP his.*, TARGET his.*;

外端：
增加抽取进程：
 add extract ext_ww, tranlog, begin now
EXTRACT added.
info all

添加源端的队列文件。
add EXTTRAIL /home/ogg/ogg/dirdat/e3, extract ext_ww,megabytes 512

edit param ext_ww

EXTRACT ext_ww
setenv (ORACLE_SID = orcl)
setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg_123
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE /home/ogg/ogg/dirrpt/ext_nw.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 3:00
exttrail /home/ogg/ogg/dirdat/e3
dynamicresolution
TRANLOGOPTIONS EXCLUDEUSER ogg
DBOPTIONS  ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 5m
FETCHOPTIONS NOUSESNAPSHOT
TABLEEXCLUDE his.CGMX;
TABLEEXCLUDE his.CRB; 
TABLEEXCLUDE his.CSKC;
TABLEEXCLUDE his.LIS_CONFIRM_LOG;
TABLEEXCLUDE his.LSDSJ;
TABLEEXCLUDE his.PBCATCOL;
TABLEEXCLUDE his.PBCATEDT;
TABLEEXCLUDE his.PBCATFMT;
TABLEEXCLUDE his.PBCATTBL;
TABLEEXCLUDE his.PBCATVLD;
TABLEEXCLUDE his.STUDENT;
TABLEEXCLUDE his.TEMP_YFKC;
TABLEEXCLUDE his.TEMP_YF_JXC;
TABLEEXCLUDE his.TJ_ICNRIS_HISORDER;
TABLEEXCLUDE his.TJ_ICNRIS_HISORDER_DETAIL;
TABLEEXCLUDE his.T_HIS_BUS_JKFP_PKRKXXBF;
TABLEEXCLUDE his.T_HIS_SYS_BUS_YPDMLS;
TABLEEXCLUDE his.T_TJ_FMYGHYFB;
TABLEEXCLUDE his.T_TJ_YYQKB;
TABLEEXCLUDE his.T_TJ_ZYZLQKB;
TABLEEXCLUDE his.XNH_YPML;
TABLEEXCLUDE his.XNH_ZLML;
TABLE his.*;

2.配置投递进程
配置datapump进程，将抓取数据传到目标主机。负责TCPIP通讯。

add extract dp_ww, exttrailsource /home/ogg/ogg/dirdat/e3      
add rmttrail /home/ogg/ogg/dirdat/e4,EXTRACT dp_ww,MEGABYTES 512

配置datapump进程参数：
 edit param dp_ww

extract dp_ww
dynamicresolution
passthru
rmttrail /home/ogg/ogg/dirdat/e4
numfiles 5000
rmthost 192.168.0.251, mgrport 7809, compress
TABLE his.*;

3.目标端配置rep进程
为replicat进程创建checkpoint表：
 dblogin userid ogg,password ogg_123
add checkpointtable ogg.ww_ckpt 
edit param ./GLOBALS 
GGSCHEMA ogg 
CHECKPOINTTABLE ogg.ww_ckpt  
add replicat rep_ww,exttrail /home/ogg/ogg/dirdat/e4,checkpointtable ogg.ww_ckpt;
分析：
exttrail：表示这个进程获取数据的来源是什么地方，是我们源端rmttrail所设置过的。

22、配置目标端replicate参数：

REPLICAT rep_ww
SETENV (ORACLE_SID = orcl)
setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
USERID ogg,PASSWORD ogg_123
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE /home/ogg/ogg/dirrpt/rep_ww.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
MAP his.*, TARGET his.*;

alter table test add constraint testPk  primary key (user_id);