GoldenGate同步初始化

1、查询表是否存在
检查要同步的表是否均存在于源端数据库
2、查询是否都存在附加日志
select * from t_gg_zmy_all t  where exists (select 1 from dba_tables b where t.owner=b.owner and t.tab_name=b.table_name)
minus
select owner,table_name from dba_log_groups;
3、如果存在表没有打开附加日志，则登录到goldengate打开附加日志
select 'add trandata '||owner||'.'||tab_name from t_gg_zmy_all t  where exists (select 1 from dba_tables b where t.owner=b.owner and t.tab_name=b.table_name)
minus
select owner,table_name from dba_log_groups;
$ ggsci
GGSCI (S7_C_YZ_YZSJK) 1> dblogin userid goldengate, password AACAAAAAAAAAAAJAGHJCVAFFQHOJOBCGQBWHBEBDCESJTEIA , encryptkey default
Successfully logged into database.
GGSCI (S7_C_YZ_YZSJK) 2> add trandata COMMON.PC_PS_FIXFEEDISC
Logging of supplemental redo data enabled for table COMMON.PC_PS_EBOXDISC.
4、再次检查是否全部都添加附加日志
SQL> select * from t_gg_zmy_all t  where exists (select 1 from dba_tables b where t.owner=b.owner and t.tab_name=b.table_name)
  2  minus
  3  select owner,table_name from dba_log_groups;
no rows selected
5、编辑抽取进程的参数文件
GGSCI (S7_C_YZ_YZSJK) 1> edit params extzmygc
extract extzmygc
SETENV (NLS_LANG="American_America.ZHS16GBK")
userid goldengate, password AACAAAAAAAAAAAJAGHJCVAFFQHOJOBCGQBWHBEBDCESJTEIA , encryptkey default
REPORT AT 6:00
STATOPTIONS RESETREPORTSTATS
STATOPTIONS REPORTFETCH
gettruncates
BR BRINTERVAL 20M
TRANLOGOPTIONS LOGRETENTION DISABLED
discardfile ./dirrpt/extzmygc.dsc, append, megabytes 1000
discardrollover at 6:00
warnlongtrans 3h, checkinterval 10m
fetchoptions nousesnapshot
tranlogoptions rawdeviceoffset 0
tranlogoptions altarchivedlogformat threadid 1 log%t_%s_%r.arc
tranlogoptions altarchivedlogformat threadid 2 log%t_%s_%r.arc
tranlogoptions altarchivelogdest instance fsdb1 /oracle/arch_fs1/logs, altarchivelogdest instance fsdb2 /oracle/arch_fs2/logs
threadoptions  maxcommitpropagationdelay 90000 iolatency 180000
threadoptions  inqueuesize  2000
threadoptions  outqueuesize 1000
exttrail ./dirdat/extzmygc/ss, megabytes 100
numfiles 1000
dynamicresolution
TABLE COMMON.CS_SMP_AWARDSET;
TABLE COMMON.CH_PW_GSUBJECT;
……
6、添加抽取进程
GGSCI (S7_C_YZ_YZSJK) 1> add extract extzmygc,tranlog,begin now,threads 2
EXTRACT added.
GGSCI (S7_C_YZ_YZSJK) 2> add exttrail ./dirdat/extzmygc/ss,extract extzmygc,megabytes 100
EXTTRAIL added.
GGSCI (S7_C_YZ_YZSJK) 3> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXTZMYGC    00:00:00      00:00:10    
7、启动抽取进程
GGSCI (S7_C_YZ_YZSJK) 7> start EXTZMYGC
Sending START request to MANAGER ...
EXTRACT EXTZMYGC starting
GGSCI (S7_C_YZ_YZSJK) 10> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTZMYGC    00:02:57      00:00:08    
----------------以下是目标端操作--------------------------------
8、目标端安装配置goldengate
tar -xvf ggs_AIX_ppc_ora11g_64bit.tar
HWCRMBP1:/ggs/ggs11>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
AIX 5L, ppc, 64bit (optimized), Oracle 11g on Oct  5 2011 00:37:03
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (HWCRMBP1) 1> create subdirs
Creating subdirectories under current directory /ggs/ggs11
Parameter files                /ggs/ggs11/dirprm: created
Report files                   /ggs/ggs11/dirrpt: created
Checkpoint files               /ggs/ggs11/dirchk: created
Process status files           /ggs/ggs11/dirpcs: created
SQL script files               /ggs/ggs11/dirsql: created
Database definitions files     /ggs/ggs11/dirdef: created
Extract data files             /ggs/ggs11/dirdat: created
Temporary files                /ggs/ggs11/dirtmp: created
Veridata files                 /ggs/ggs11/dirver: created
Veridata Lock files            /ggs/ggs11/dirver/lock: created
Veridata Out-Of-Sync files     /ggs/ggs11/dirver/oos: created
Veridata Out-Of-Sync XML files /ggs/ggs11/dirver/oosxml: created
Veridata Parameter files       /ggs/ggs11/dirver/params: created
Veridata Report files          /ggs/ggs11/dirver/report: created
Veridata Status files          /ggs/ggs11/dirver/status: created
Veridata Trace files           /ggs/ggs11/dirver/trace: created
Stdout files                   /ggs/ggs11/dirout: created
9、配置目标目标端管理进程
----usecheckpoints这里需要配置全局的checkpoint表，后面会配置
GGSCI (HWCRMBP1) 1>edit params mgr
port 7809
autorestart  er *
purgeoldextracts ./dirdat/*/*, usecheckpoints, minkeephours 16
LAGREPORTMINUTES 1
LAGINFOMINUTES 1
LAGCRITICALMINUTES 1
10、在oracle数据库创建goldengate用户
SQL> create user goldengate identified by Ora02036;
SQL> grant dba,connect to goldengate;
Grant succeeded.
SQL> conn goldengate/Ora02036
Connected.
11、启动管理进程
GGSCI (HWCRMBP1) 3> start mgr
GGSCI (HWCRMBP1) 4> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING     
12、获取加密密码
GGSCI (HWCRMBP1) 5> ENCRYPT PASSWORD Ora02036
No key specified, using default key...
Encrypted password:  AACAAAAAAAAAAAIAUGQABGSDTEQJFJWG
GGSCI (HWCRMBP1) 6>        
13、测试goldengate用户是否可以连接
GGSCI (HWCRMBP1) 7> DBLOGIN USERID goldengate, PASSWORD AACAAAAAAAAAAAIAUGQABGSDTEQJFJWG ,ENCRYPTKEY default
Successfully logged into database.   
14、在gg的安装跟目录添加checkpoit表
cat GLOBALS
CHECKPOINTTABLE goldengate.ggschkpt
15、在oracle的goldengate用户里面添加checkpoit表
GGSCI (HWCRMBP1) 4> DBLOGIN USERID goldengate, PASSWORD AACAAAAAAAAAAAIAUGQABGSDTEQJFJWG ,ENCRYPTKEY default
Successfully logged into database.
GGSCI (HWCRMBP1) 5> add CHECKPOINTTABLE goldengate.ggschkpt
Successfully created checkpoint table GOLDENGATE.GGSCHKPT.
GGSCI (HWCRMBP1) 6>                         
----------------以上是目标端操作----------------------------
16、在源端编辑投递进程
GGSCI (S7_C_YZ_YZSJK) 8> edit params dppzmygc
extract dppzmygc
rmthost 10.245.158.3, mgrport 7809          ---目标端ip及端口
rmttrail /ggs/ggs11/dirdat/dppzmygc/ss      ---目标端队列文件
passthru
dynamicresolution
table    COMMON.*;
table    ZJZW.*;
table    ZJYY.*;
table    MMZW.*;
table    MMYY.*;
table    YJZW.*;
table    YJYY.*;
17、在源端添加投递进程
GGSCI (S7_C_YZ_YZSJK) 2> add extract dppzmygc , exttrailsource  ./dirdat/extzmygc/ss
EXTRACT added.
GGSCI (S7_C_YZ_YZSJK) 6> add rmttrail  /ggs/ggs11/dirdat/dppzmygc/ss, ext dppzmygc, megabytes 100
RMTTRAIL added.
GGSCI (S7_C_YZ_YZSJK) 7>start dppzmygc
-------错误操作后被delete掉---------------------
----GGSCI (S7_C_YZ_YZSJK) 3> add rmttrail  ./dirdat/dppzmygc/ss, ext dppzmygc, megabytes 100
----RMTTRAIL added.
----GGSCI (S7_C_YZ_YZSJK) 4> delete rmttrail  ./dirdat/dppzmygc/ss
----Deleting extract trail ./dirdat/dppzmygc/ss for extract DPPZMYGC

GGSCI (S7_C_YZ_YZSJK) 8> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     ABENDED     DPPZMYGC    00:00:00      00:36:33    
EXTRACT     RUNNING     EXTZMYGC    00:00:01      00:00:09    
GGSCI (S7_C_YZ_YZSJK) 9> info DPPZMYGC
EXTRACT    DPPZMYGC  Initialized   2012-09-13 16:26   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:36:40 ago)
Log Read Checkpoint  File ./dirdat/extzmygc/ss000000
                     First Record  RBA 0
GGSCI (S7_C_YZ_YZSJK) 10> info DPPZMYGC,detail
EXTRACT    DPPZMYGC  Initialized   2012-09-13 16:26   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:36:47 ago)
Log Read Checkpoint  File ./dirdat/extzmygc/ss000000
                     First Record  RBA 0
  Target Extract Trails:
  Remote Trail Name                                Seqno        RBA     Max MB
  /ggs/ggs11/dirdat/dppzmygc/ss                        0          0        100
  Extract Source                          Begin             End             
  ./dirdat/extzmygc/ss000000              * Initialized *   First Record    
  ./dirdat/extzmygc/ss000000              * Initialized *   First Record    
  ./dirdat/extzmygc/ss000000              * Initialized *   First Record    
  ./dirdat/extzmygc/ss000000              * Initialized *   First Record    
Current directory    /ggs/ggs11
Report file          /ggs/ggs11/dirrpt/DPPZMYGC.rpt
Parameter file       /ggs/ggs11/dirprm/dppzmygc.prm
Checkpoint file      /ggs/ggs11/dirchk/DPPZMYGC.cpe
Process file         /ggs/ggs11/dirpcs/DPPZMYGC.pce
Stdout file          /ggs/ggs11/dirout/DPPZMYGC.out
Error log            /ggs/ggs11/ggserr.log
-------------------以下是在目标端操作--------------------------
18、准备目标端复制进程(详细见repzmygc.txt)
replicat repzmygc
SETENV (ORACLE_HOME="/oracle/oracle/products/11.2/db")
SETENV (ORACLE_SID="gddb")
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid goldengate, password AACAAAAAAAAAAAIAUGQABGSDTEQJFJWG, encryptkey default
gettruncates
sqlexec "alter session set constraints=deferred"
REPORT AT 6:00
STATOPTIONS RESETREPORTSTATS
reperror (default,discard)
reperror (01650, abend)
reperror (01652, abend)
reperror (01653, abend)
reperror (01654, abend)
reperror (01655, abend)
reperror (01680, abend)
reperror (01683, abend)
reperror (01688, abend)
reperror (01691, abend)
reperror (01692, abend)
reperror (30036, abend)
reperror (01400, abend)
reperror (14400, abend)
reperror (02290, abend)
reperror (02291, abend)
reperror (02292, abend)
reperror (02293, abend)
reperror (03114, abend)
reperror (01003, abend)
discardfile ./dirrpt/repzmygc.dsc, append, megabytes 1000
discardrollover at 6:00
numfiles 100
assumetargetdefs
dynamicresolution
allownoopupdates
grouptransops 1000
batchsql batchesperqueue 100, opsperbatch 8000
MAP COMMON.BLANKCARD_DRIVE, TARGET OLD_COMMON.BLANKCARD_DRIVE;
……
19、添加目标端复制进程
add replicat repzmygc,exttrail /ggs/ggs11/dirdat/dppzmygc/ss
-------------------以上是在目标端操作--------------------------
20、在源端启动投递
如果源端投递进程没起，则启动
GGSCI (S7_C_YZ_YZSJK) 1>start dppzmygc
21、导出导入数据，按指定的SCN
SQL>select current_scn from v$database;
目标库导入数据时检查触发器和外键，将其DISABLE
22、在目标端启动复制进程
GGSCI (HWCRMBP1) 1>start REPLICAT REPZMYCM, AFTERCSN 11519343329755
23、检查
GGSCI (S7_C_YZ_YZSJK) 1>info all
------------------end--------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24930246/viewspace-1067683/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24930246/viewspace-1067683/