一次完整的单向dml复制OGG配置

前两天帮客户完成了一个系统的OGG单向复制的配置，文章主要记录了开启数据库归档、OGG进程参数配置，数据初始化三个部分。

源端需要的信息有DBA权限的OGG账户和密码，主机IP和服务器名。

-------------------------------------开启数据库归档模式-----------------------------------------

$sqlplus /  as sysdba
>select name from v$database;

$srvctl stop database -d <db_unique_name>

$srvctl start database -d <db_unique_name> -o mount

$sqlplus /  as sysdba
>alter database archivelog;
>exit

$srvctl stop database -d <db_unique_name>
$srvctl start database -d <db_unique_name>

>archive log list;
Database log mode      Archive Mode
Automatic archival       Enabled
Archive destination       +SJDWARCH
Oldest online log sequence     20179
Next log sequence to archive   20184
Current log sequence       20184
------------------

----开始数据库最小追加日志
>alter database add supplemental log data;
>select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES

------------------------------------新建OGG进程及参数配置----------------------------

1.OGG抽取进程参数配置

GGSCI>edit param extshfk

EXTRACT EXTSHFK
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") 
USERID goldengate@***, PASSWORD ********
GETTRUNCATES
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES,RATE
REPORTROLLOVER AT 02:00
NUMFILES 5000
DISCARDFILE ./dirrpt/EXTSHFK.dsc,APPEND,MEGABYTES 1024
DISCARDROLLOVER AT 02:00
WARNLONGTRANS 2h,CHECKINTERVAL 3m
EXTTRAIL ./dirdat/fk
TRANLOGOPTIONS DBLOGREADER 
CACHEMGR CACHESIZE 2048MB, CACHEDIRECTORY ./dirtmp 
DYNAMICRESOLUTION  

table S****ER.ES_TRA****_SNAP;
table S***ER.ES_****NAP;
table SG****R.ES_CON****NAP;

.........

2.OGG投递进程参数配置

GGSCI>edit param dpeshfk

EXTRACT DPESHFK
RMTHOST 10.131.**.***,MGRPORT ****,COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL /goldengate/dirdat/fk
DYNAMICRESOLUTION

table S****ER.ES_TRA****_SNAP;
table S***ER.ES_****NAP;
table SG****R.ES_CON****NAP;

.........

3.追加日志add trandata-
>./ggsci
>dblogin USERID ******@***, PASSWORD **********


>delete trandata SG****ER.E***NAP
delete trandata S***ER.ES_****NAP
delete trandata SG****R.ES_CON****NAP
...............

>add trandata SG****NER.ES****NAP
add trandata SG****R.ES_****NAP
add trandata SG*****R.ES_C*****NAP
...............

4.结构定义文件
GGSCI> edit params defgen

DEFSFILE ./dirdef/defgen.def
USERID goldengate@***, PASSWORD *******
table S****ER.ES_TRA****_SNAP;
table S***ER.ES_****NAP;
table SG****R.ES_CON****NAP;

..............

--在Goldengate安装目录下调用defgen工具
$./defgen paramfile ./dirprm/defgen.prm

5.OGG复制进程参数配置

GGSCI>edit param repshfk

REPLICAT REPSHFK
SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
USERID ogg@***,PASSWORD AACAAAAAAAAAAAJABFBHTJKIVGSASHYFRCDDPELASDJETACE,ENCRYPTKEY DEFAULT
REPORT AT 01:30
REPORTCOUNT EVERY 30 MINUTES,RATE
REPORTROLLOVER AT 01:40
REPERROR DEFAULT,ABEND
NUMFILES 5000 
GROUPTRANSOPS 10000 
SOURCEDEFS ./dirdef/fk.def
--HANDLECOLLISIONS 
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repshfk.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 01:50
GETTRUNCATES  --这里可捕获源端的truncate操作

map SG***ER.NS****RAGE,target CACHE**.SG***ER.NS****RAGE, COLMAP (USEDEFAULTS, target_write_time = @DATENOW(), source_change_time = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
......................

............



6.进程添加---------------------
GGSCI>add extract extshfk,tranlog,threads 2,begin now  --rac情况下加threads n 这个选项
GGSCI>add exttrail ./dirdat/ss ,extract extshfk,megabytes 512

GGSCI>add extract dpeshfk,exttrailsource ./dirdat/ss 
GGSCI>add rmttrail /goldengate/dirdat/fk,extract dpeshfk

GGSCI>add replicat repshfk,exttrail /goldengate/dirdat/fk,checkpointtable ogg.CHECKPOINT_XX

----先启动抽取和投递进程,产生trail文件并在目标端检查OGG通道是否正常

GGSCI>start extshfk

GGSCI>start dpeshfk

----在目标端Goldengate主目录下的 ./dirdat目录检查是否有ss000000.文件投递过来

-----------------------------------------数据初始化-------------------------------------

----创建DBlink
-- 目标端Create database link 
--drop public database link impfklink
create public database link impfklink
  connect to *** identified by ***
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.131.**.***)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ****)
    )
  )';


----检查dblink是否创建正常
>select * from tab@impjclink;

----在源端查询一条SCN号作为数据初始化的时间节点

>select to_char(current_csn) from v$database;

----13581061333734

  

----dblink初始化
 impdp ****/***** directory=EXPDIR network_link=impfklink parallel=8 remap_schema=S***ER:CACHE*** \
remap_tablespace= TBS**:CACHE**,TBS_***_IDX:CACHEIDX*** \
PARFILE='/goldengate/expdir/exp_sh/par/impdpfk.par' \
table_exists_action=replace \
flashback_scn=13581061333734  exclude=grant

----数据导完后启动复制进程

GGSCI>start repshfk ,aftercsn 13581061333734  

--注：aftercsn非笔误