ORALCE GOLDENGATE容灾标准配置总结--实施笔记

ORALCE GOLDENGATE容灾标准配置流程

基于oracle database的实施笔记

(注：“--"符号仅为描述方便，不作为参数一部分）

捕获端初始化

开启数据库附加日志

SQL>alter database force logging;
SQL>alter database add supplemental log data;

创建表空间和用户

SQL>create tablespace ogg datafile '+data' size 1m autoextend on next 1m;
SQL>create user ogg identified by ***** default tablespace ogg quota unlimited on ogg;

授权

SQL>grant connect to ogg;
SQL>grant resource to ogg;
SQL>grant alter session to ogg;
SQL>grant alter system to ogg;
SQL>grant alter any table to ogg;
SQL>grant select any table to ogg;
SQL>grant select any transaction to ogg;
SQL>grant select any dictionary to ogg;
SQL>grant FLASHBACK ANY TABLE to ogg;
SQL>grant SELECT on dba_clusters to ogg;         --run as sysdba
SQL>grant EXECUTE on DBMS_FLASHBACK to ogg;          --run as sysdba
SQL>exec dbms_goldengate_auth.grant_admin_privilege('ogg');  --(Integrated Capture 11.2.0.3 later)
SQL>exec dbms_streams_auth.grant_admin_privilege('ogg'); --(Integrated Capture 11.2.0.3 before)  --run as sysdba

DDL支持

sqlplus / as sysdba
SQL>@marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>@ddl_enable.sql
SQL>GRANT GGS_GGSUSER_ROLE TO ogg;
SQL>GRANT EXECUTE ON UTL_FILE TO ogg;
--性能工具
SQL>@ddl_pin ogg
--编辑OGG全局参数
ggsci>EDIT PARAMS ./GLOBALS 
ggschema ogg

创建subdirs

GGSCI>create subdirs

mgr进程参数

GGSCI>edit params mgr
port 7809
--dynamicportlist 7840-7880
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45

加密登陆DB的用户密码

ggsci>Encrypt Password ***** EncryptKey Default
ggsci>Encrypt Password ***** EncryptKey Default --option for visiting asm

extract进程公共参数

GGSCI>edit params obey_etest
setenv (ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.zhs16gbk)
userid ogg@scan:1521/<dbname>,password AACAAAAAAAAAAAJAYBSCBJOFBJBHEGUDSBMHKBNJMHN***** BLOWFISH ENCRYPTKEY DEFAULT
TRANLOGOPTIONS asmuser sys@<hostname>2-vip:1521/+asm,asmpassword AACAAAAAAAAAAAEAUIOJPDSGHGHA***** BLOWFISH ENCRYPTKEY DEFAULT
dynamicresolution
REPORT AT 20:00
CACHEMGR CACHESIZE 256MB
REPORTCOUNT EVERY 10 MINUTES, RATE
REPORTROLLOVER AT 00:00 ON monday
TRANLOGOPTIONS excludeuser ogg
DBOPTIONS ALLOWUNUSEDCOLUMN,LOBBUFSIZE 8192000
TRANLOGOPTIONS LOGRETENTION SR
TRANLOGOPTIONS PURGEORPHANEDTRANSACTIONS
--TRANLOGOPTIONS dblogreader    --ASM API With 10.2.0.5 or 11.2.0.2 later
WARNLONGTRANS 1H, CHECKINTERVAL 5m
TABLEEXCLUDE *.plan_table
TABLEEXCLUDE *.MLOG$*

pump进程公共参数

GGSCI>edit params obey_ptest
setenv (ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.zhs16gbk)
userid ogg@scan:1521/<dbname>,password AACAAAAAAAAAAAJAYBSCBJOFBJBHEGUDSBMHKBNJMHN***** BLOWFISH ENCRYPTKEY DEFAULT
REPORT AT 20:00
CACHEMGR CACHESIZE 256MB
REPORTCOUNT EVERY 10 MINUTES, RATE
REPORTROLLOVER AT 00:00 ON monday
passthru

---

复制端初始化

激活OGG特性

SQL>alter system set enable_goldengate_replication=true; --only for 11.2.0.4 and later

创建表空间和授权

SQL>CREATE TABLESPACE ogg DATAFILE SIZE 1M autoextend on next 1m;
SQL>CREATE USER ogg IDENTIFIED BY ***** DEFAULT TABLESPACE ogg;

SQL>GRANT CONNECT TO ogg;
SQL>GRANT RESOURCE TO ogg;
SQL>GRANT ALTER ANY table TO ogg;
SQL>GRANT ALTER SESSION TO ogg;
SQL>GRANT CREATE SESSION TO ogg;
SQL>GRANT flashback ANY table TO ogg;
SQL>GRANT SELECT ANY dictionary TO ogg;
SQL>GRANT SELECT ANY TRANSACTION TO ogg;
SQL>GRANT SELECT ANY TABLE TO ogg;
SQL>GRANT INSERT ANY TABLE TO ogg;
SQL>GRANT UPDATE ANY TABLE TO ogg;
SQL>GRANT DELETE ANY TABLE TO ogg;
SQL>GRANT CREATE ANY INDEX TO ogg;
SQL>GRANT LOCK ANY TABLE TO ogg;
SQL>GRANT EXECUTE ON DBMS_FLASHBACK to ogg;
SQL>grant execute on DBMS_XSTREAM_GG to ogg; --for SUPPRESSTRIGGERS参数
SQL>grant create database link to ogg;
SQL>exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ogg');
SQL>grant dba to ogg; --for DDL replicat

创建subdirs

GGSCI>create subdirs

mgr进程参数

GGSCI>edit params mgr
port 7809
dynamicportlist 7840-7880
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45

创建检查点表

ggsci>dblogin userid ogg@<hosname>:1521/<dbname>,password *****
ggsci>ADD CHECKPOINTTABLE ogg.chktable

加密登陆DB用户密码

ggsci>Encrypt Password ***** EncryptKey Default

replicat进程公共参数

GGSCI>edit params obey_rtest
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.zhs16gbk)     --必须与源trail同
setenv (ORACLE_SID=test)
userid ogg@<hostname>:1521/<dbname>,password AACAAAAAAAAAAAKACDQAVETJQCDIXDZEJGJIVDFCRDE***** BLOWFISH ENCRYPTKEY DEFAULT
DBOPTIONS DEFERREFCONST,SUPPRESSTRIGGERS
REPORT AT 20:00
CACHEMGR CACHESIZE 1G
REPORTCOUNT EVERY 10 MINUTES, RATE
REPORTROLLOVER AT 00:00 ON monday
allownoopupdates
NOALLOWDUPTARGETMAP --禁止重复map

---

进程实施样例

添加表级附加日志

ggsci>dblogin userid ogg@scan:1521/<dbname>,password *****
ggsci>add trandata schema1.*
--for integrated capture,see doc for conditions
ggsci>ADD SCHEMATRANDATA schema

验证附加日志遗漏

select owner, table_name from dba_tables  where owner in ('SCHEMA1')
minus
select owner, table_name from DBA_LOG_GROUPS  where owner in ('SCHEMA1')

捕获进程etest参数

ggsci>edit params etest
extract etest
obey dirprm/obey_etest.prm
discardfile dirdat/etest.dsc,append,megabytes 1024
discardrollover at 3:00
exttrail dirdat/ea,megabytes 1024
table schema1.*;
ddl include mapped EVENTACTIONS(log)
DDLOPTIONS ADDTRANDATA,GETAPPLOPS,IGNOREREPLICATES,REPORT
GETTRUNCATES

设置rman日志保留

GGSCI>dblogin userid ogg@scan:1521/<dbname>,password *****
GGSCI>register extract etest,LOGRETENTION
--集成捕获模式
GGSCI>register extract etest,database

GGSCI>ADD EXTRACT etest,tranlog,begin now[,threads n --for rac]
GGSCI>add exttrail dirdat/ea,extract etest,megabytes 1024

启动捕获进程etest

ggsci>start etest

pump进程ptest参数（注：ER同机则不需要）

--注意，实际传输端口为大于7809的动态端口，除非manual启动server collector
ggsci>edit params ptest
extract ptest
obey dirprm/obey_ptest.prm
rmthost <remost_hostname>,mgrport 7809,compress,TCPBUFSIZE 4194304,TCPFLUSHBYTES 8388608
rmttrail dirdat/pa,megabytes 1024
table *.*;

add pump进程ptest

GGSCI>ADD EXTRACT ptest, EXTTRAILSOURCE dirdat/ea,extseqno 0,extrba 0
GGSCI>add rmttrail dirdat/pa,extract ptest,megabytes 1024

启动pump进程ptest

ggsci>start ptest

自启进程和trail文件保留策略，DDL history保留策略

ggsci>edit params mgr
userid ogg@scan:1521/<dbname>,password AACAAAAAAAAAAAJAYBSCBJOFBJBHEGUDSBMHKBNJMHN***** BLOWFISH ENCRYPTKEY DEFAULT
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30
PURGEOLDEXTRACTS dirdat/ea*, USECHECKPOINTS, MINKEEPDAYS 2
AUTOSTART extract etest
AUTOSTART extract ptest
AUTORESTART extract etest, RETRIES 5, WAITMINUTES 10,RESETMINUTES 60
AUTORESTART extract ptest, RETRIES 5, WAITMINUTES 10,RESETMINUTES 60

mgr失败自起

crontab -e
0,10,20,30,40,50 * * * * ksh /bakup/ogg/mon_ogg.sh
   
vi mon_ogg.sh
#!/bin/ksh
. ~oracle/.profile
OGG_HOME=/backup/ogg
LOGFILE=$OGG_HOME/mon_ogg.log
LIBPATH=$LIBPATH:$OGG_HOME
STATUS=$(echo "info all"|$OGG_HOME/ggsci|grep MANAGER|awk '{print $2}')
if [ $STATUS = RUNNING ];then
        printf "MANAGER is running\n"
else
        printf "[$(date)]:MANAGER $STATUS,starting it.">>$LOGFILE
        echo "start mgr "|$OGG_HOME/ggsci>>$LOGFILE ;printf "\n"
fi

---

复制进程rtest参数

ggsci>edit prams rtest
replicat rtest
obey dirprm/obey_rtest.prm
discardfile dirdat/rtest.dsc,append,megabytes 1024
discardrollover at 3:00
assumetargetdefs
mapexclude schema1.*_tmp
mapexclude schema1.*test*
map schema1.test_table,target schema1.test_table,FILTER(@GETENV("TRANSACTION","CSN")>9371472706);
map schema1.*,target schema1.*;
ddl &
      include mapped                                          --复制map范围内的所有DDL对象
    --include mapped objtype 'table' EVENTACTIONS(log),&      --仅复制table类型的DDL
    --include mapped objtype 'index' EVENTACTIONS(log)
ddloptions report                                             --仅复制index类型的DDL
ddlerror 24344 discard      --编译错误处理
GETTRUNCATES

add复制进程rtest

ggsci>ADD REPLICAT rtest, EXTTRAIL dirdat/pa,CHECKPOINTTABLE ogg.chktable

初始化目标数据库（略）

1. 建议使用expdp或rman初始化

2. 联机初始化使用scn来保证一致点

3. 注意目标库的表空间名字和大小预估

启动复制进程rtest

ggsci>start rtest,aftercsn 9363398128  --注意scn号