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);