Goldengate简单搭建

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

SYSTEM
UNDOTBS1
SYSAUX
TEMP
UNDOTBS2
USERS
GOLDENGATE

7 rows selected.

二
SQL> create user ogg identified by a default tablespace goldengate temporary tablespace temp;

User created.

三
SQL> grant connect to ogg;

Grant succeeded.

SQL> grant alter any table to ogg;

Grant succeeded.

SQL> grant dba to ogg;

Grant succeeded.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> grant resource to ogg;

Grant succeeded.

四、检查RAC节点间同步
时间不同步，不单单是ogg，集群可能由于时间不一致导致出错，ogg的错误可能是抽取进程中断。

五
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA/test/archivelog
Oldest online log sequence 54
Next log sequence to archive 55
Current log sequence 55

六
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

七：添加最小附加日志及FORCE_LOGGING
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

NO

SQL> alter database force logging;

Database altered.

SQL>
SQL> alter database add SUPPLEMENTAL log data;

Database altered.
注：打开补充日志最好在夜里业务很少的时候进行。如果是RAC需要在每个节点上都执行。
完成后建议执行一次归档操作：

alter system archive log current;

开始安装Goldengate
ogg_p15967917_112105_for_oracle_10g_Linux-x86-64.zip

GGSCI (node1) 3> create subdirs

Creating subdirectories under current directory /ogg

Parameter files /ogg/dirprm: already exists
Report files /ogg/dirrpt: created
Checkpoint files /ogg/dirchk: created
Process status files /ogg/dirpcs: created
SQL script files /ogg/dirsql: created
Database definitions files /ogg/dirdef: created
Extract data files /ogg/dirdat: created
Temporary files /ogg/dirtmp: created
Stdout files /ogg/dirout: created

编辑MGR参数（源端和目标端同时配置–一模一样）
GGSCI (node1) 1> edit param mgr

port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints,minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

start mgr

GGSCI (node1) 3> dblogin userid ogg,PASSWORD a
Successfully logged into database.

GGSCI (node1) 4> add trandata test.*

Logging of supplemental redo data enabled for table TEST.T1.

2016-01-19 20:43:22 WARNING OGG-00869 No unique key is defined for table ‘T2’. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo log data is already enabled for table TEST.T2.

抽取进程
GGSCI (node1) 9> add extract ext_app,tranlog,threads 2,begin now
EXTRACT added.

GGSCI (node1) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED EXT_APP 00:00:00 00:00:04

GGSCI (node1) 11> add exttrail ./dirdat/r1 , extract ext_app,megabytes 100
EXTTRAIL added.

GGSCI (node1) 12> edit param ext_app

EXTRACT ext_app
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
TRANLOGOPTIONS ASMUSER sys@asm,asmpassword oracle
userid ogg,password a
REPORTCOUNT EVERY 1 MINUTES,RATE
numfiles 500
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS EXCLUDEUSER ogg
table test.*;

start ext_app
–启动抽取进程（这个暂时不要起，等ogg目标端把数据都恢复的差不多了再起，那样就可以快点）
–如10T数据500G的归档(RMAN)，每天产生100G归档，那么现把10T的数据和500G的归档恢复完，看这段时间产生了多少归档，
–比如总共用了3天，那么还可以接着追300G的归档，在最后一次备份归档的之前记录下SCN号再去备归档，然后再做恢复。

–这样避免exttrail文件产生大量数据，ogg恢复应该也比不上recover（毕竟走网络）

SQL> select CHECKPOINT_CHANGE# ,CURRENT_SCN from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN

---

1651897 1673978

这里就是ogg数据的初始化。
。。。。
。。。。
。。。。
。。。。

传递进程创建
GGSCI (node1) 31> add extract pmp_app exttrailsource ./dirdat/r1
EXTRACT added.

GGSCI (node1) 32>

GGSCI (node1) 32> add rmttrail ./dirdat/t1,EXTRACT pmp_app,MEGABYTES 100
RMTTRAIL added.

GGSCI (node1) 33> edit param pmp_app

extract PMP_APP
dynamicresolution
passthru
rmthost 192.168.56.10,mgrport 7839,compress
rmttrail ./dirdat/t1
numfiles 500
table test.*;

目标端的replicate配置
GGSCI (bsr.com) 3> dblogin userid ogg,password a
Successfully logged into database.

–添加检查点表(强列建议为每个Replicat进程创建checkpointtable)
GGSCI (bsr.com) 4> add checkpointtable ogg.rpt_app_ckpt

Successfully created checkpoint table ogg.rpt_app_ckpt.

–注意这里的trail要和源端的rmttrail一致
GGSCI (bsr.com) 6> add replicat rpt_app,exttrail ./dirdat/t1,checkpointtable ogg.rpt_app_ckpt
REPLICAT added.

GGSCI (bsr.com) 7> edit param rpt_app

userid ogg,password a
replicat rpt_app
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password a
REPORTCOUNT EVERY 30 MINUTES,RATE
REPERROR DEFAULT,ABEND
numfiles 5000
assumetargetdefs
DISCARDFILE ./dirrpt/rep_app.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
map TEST.,target TEST.;

–起replicat进程
GGSCI (bsr.com) 9> start rpt_app,aftercsn 1852153

Sending START request to MANAGER …
REPLICAT RPT_APP starting

GGSCI (bsr.com) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RPT_APP 00:00:00 00:00:00

附注:
GGSCI (bsr.com) 10> delete replicat RPT_APP
Deleted REPLICAT RPT_APP.

–对于replicat有checkpoint表在ogg用户上，所以建议到ogg用户下删除相关表
如:checkpointtable RPT_APP_CKPT2
则对应的表有两张
RPT_APP_CKPT2_LOX TABLE
RPT_APP_CKPT2 TABLE