ORACLE GoldenGate 简单配置

-- 系统环境

--------------------------------------------------------------------------------

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/11.2/db_1

export ORACLE_SID=orcl

export NLS_LANG=american_america.ZHS16GBK

export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

#export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin

export ORACLE_TERM=xterm

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export TEMP=/tmp

export TMPDIR=/tmp

umask 022

# ogg 

export OGG_HOME=/u01/app/ogghome

export PATH=$OGG_HOME:$PATH

export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH

export PATH

stty erase "^H"

--------------------------------------------------------------------------------

-- 源端目标端

create tablespace ggs_tbs datafile '/u01/app/oracle/oradata/orcl/ggs01.dbf' size 1g autoextend on maxsize 20G;

create user ggs identified by ggs default tablespace ggs_tbs temporary tablespace temp;

grant resource,connect,dba to ggs;

grant unlimited tablespace to ggs;

alter database force logging;

alter database add SUPPLEMENTAL log data;

ALTER SYSTEM SWITCH LOGFILE;

select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

alter system set log_archive_dest_1='location=/u01/archive'; 

alter system set log_archive_format = 'arc_%t_%s_%r.log' scope=spfile;

shutdown immediate

startup mount;

alter database archivelog;

alter database open;

archive log list

-- 配置支持DDL同步复制

cd $OGG_HOME

sqlplus / as sysdba

@marker_setup.sql

@ddl_setup.sql

@role_setup.sql

@ddl_enable.sql

-- 如上配置 GoldenGate 支持DDL脚本；其中有创建 Trigger；DDL跟DML 复制机制不同；DML复制是基于redo log。

-- 其DDL复制原理是基于Trigger；它扑捉DDL操作记录在中间表；Extract读取中间表DDL操作；Pump投递到目标端；目标端在应用DDL语句。

-- 11G 以前版本不支持回收站

alter system set recyclebin=off scope=both; 

-- 11.2.0.4.0版本需要设置如下参数

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

-- 创建OGG所需目录

./ggsci

create subdirs

-- 添加检查点

EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ggs.ggschkpt

exit

./ggsci

DBLOGIN USERID ggs, PASSWORD ggs

ADD CHECKPOINTTABLE

-- 源端目标端配置MGR

-- src

EDIT PARAMS MGR

PORT 7809

DYNAMICPORTLIST 7810-7820

AUTOSTART EXTRACT *

AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 2

PURGEOLDEXTRACTS /u01/app/ogg19/dirprm/ *, USECHECKPOINTS, MINKEEPDAYS 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

ACCESSRULE, PROG *, IPADDR 10.176.59.*, ALLOW

-- trc

PORT 7809

DYNAMICPORTLIST 7810-7820

AUTOSTART ER *

AUTORESTART ER *,WAITMINUTES 3,RETRIES 30

PURGEOLDEXTRACTS /u01/app/ogg19/dirprm/ *,USECHECKPOINTS,MINKEEPDAYS 7

LAGREPORTHOURS 1

LAGINFOMINUTES 5

LAGCRITICALMINUTES 10

ACCESSRULE, PROG *, IPADDR 10.176.59.*, ALLOW

-- Start Mgr

start mgr

info mgr

-- 源端抽取投递进程配置

-- Config Extract 

GGSCI (dbs1c as ogg@dbmonitor) 6> add extract ext_sjz8,tranlog,threads 1,begin now

GGSCI (dbs1c as ogg@dbmonitor) 7> add exttrail /u01/app/ogg19/dirdat/s8, extract ext_sjz8 -- MEGABYTES 5

GGSCI (dbs1c as ogg@dbmonitor) 8> edit param ext_sjz8 

extract ext_sjz8

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg!QAZ2wsx

TRANLOGOPTIONS DBLOGREADER

REPORTCOUNT EVERY 30 MINUTES, RATE

DISCARDFILE /u01/app/ogg19/dirrpt/ext_sjz8.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 3:00

EXTTRAIL /u01/app/ogg19/dirdat/s8,format release 18.1

DBOPTIONS  ALLOWUNUSEDCOLUMN

FETCHOPTIONS NOUSESNAPSHOT

FETCHOPTIONS FETCHPKUPDATECOLS

table YN01700_I.AC60;

-- start extract ext_sjz8

-- Config Pump

GGSCI (dbs1c as ogg@dbmonitor) 9> add extract dp_sjz8, exttrailsource /u01/app/ogg19/dirdat/s8

GGSCI (dbs1c as ogg@dbmonitor) 9> ADD RMTTRAIL /u01/app/ogg19/dirdat/s8, EXTRACT dp_sjz8 -- MEGABYTES 5

GGSCI (dbs1c as ogg@dbmonitor) 10> edit param dp_sjz8

EXTRACT dp_sjz8

USERID ogg, PASSWORD ogg!QAZ2wsx

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

RMTHOST 10.176.59.72, mgrport 7809, COMPRESS

PASSTHRU

RMTTRAIL /u01/app/ogg19/dirdat/s8,format release 18.1

table YN01700_I.AC60;

-- start extract dp_sjz8

-- 目标端应用进程配置

-- Config Replicat

GGSCI (dbtrg as ogg@dbmonitor) 4> add replicat rep_sjz8, exttrail /u01/app/ogg19/dirdat/s8, checkpointtable ggs.checkpoint

GGSCI (dbtrg as ogg@dbmonitor) 5> edit param rep_sjz8

REPLICAT rep_sjz8

SETENV(ORACLE_SID="orcl")

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

REPORT AT 06:00

REPORTCOUNT EVERY 30 MINUTES, RATE

REPORTROLLOVER AT 02:00

REPERROR DEFAULT, ABEND

ALLOWNOOPUPDATES

ASSUMETARGETDEFS

DISCARDFILE /u01/app/ogghome/dirrpt/rep_sjz8.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 02:00

--REPLACEBADCHAR SKIP

--SOURCECHARSET OVERRIDE ISO-8859-1

map YN01700_I.AC60,target ******.SB_AC60;

-- start replicat rep_sjz8

-- 增加配置DDL同步

-- 源端

-- 配置指定DDL复制的用户

view params ./GLOBALS

GGSCHEMA OGG

-- 停MGR，Extract进程，配置Extract配置文件；在配置文件添加一下内容：

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA,REPORT

-- 目标端

-- 停MGR，Replicat进程；　　配置Replicat配置文件；在配置文件添加一下内容：

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

-- 允许目标端触发器

-- Replicat的用户必须有Streams的管理权限

dbms_goldengate_auth.grant_admin_privilege

DBOPTIONS NOSUPPRESSTRIGGERS -- 可以用DEFERREFCONST选项来代替手动设置约束无效