由于业务需要需要同步数据Oracle到MySQL使用工具OGG
修改环境变量vi .bash_profile ---需要添加环境变量，否则会报错（./ggsci: error whileloading shared libraries: libnnz10.so: cannot open shared object file: No suchfile or directory.）export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/home/oracle/ogg 下载安装包，并解压到相应目录cd ogg/./ggsci ----进入ogg目录，输入./ggsci 进入ogg管理界面$./ggsciGGSCI (ora203) 1> create subdirs -----创建sub目录 Creating subdirectories under current directory /home/oracle/oggParameter files /home/oracle/ogg/dirprm: already existsReport files /home/oracle/ogg/dirrpt: createdCheckpoint files /home/oracle/ogg/dirchk: createdProcess status files /home/oracle/ogg/dirpcs: createdSQL script files /home/oracle/ogg/dirsql: createdDatabase definitions files /home/oracle/ogg/dirdef: createdExtract data files /home/oracle/ogg/dirdat: createdTemporary files /home/oracle/ogg/dirtmp: createdStdout files /home/oracle/ogg/dirout: created源端建立用户,并赋权限：create tablespace GGS_TBS datafile '/oradata/ora203/ggs_tbs01.dbf' size 30G autoextend on;create user ggs identified by ggs default tablespace GGS_TBS;grant dba to ggs;源端打开辅助日志alter database force logging; ----切换到强制记录日志模式alter database add supplemental log data; ----打开辅助日志select supplemental_log_data_min from v$database; SUPPLEMENTAL_LOG_DATA_MI ------------------------ YES各参数文件配置配置MANAGER 主库、目标库配置：源端edit params mgr Port 7809--DYNAMICPORTLIST 7840-7939 ----添加端口号--AUTOSTART ER *--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3--PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3--LAGREPORTHOURS 1--LAGINFOMINUTES 30--LAGCRITICALMINUTES 45目标端edit params mgrPort 7809--DYNAMICPORTLIST 7840-7939--AUTOSTART ER *--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3--AUTORESTART replicat *,RETRIES 5,WAITMINUTES 2,RESETMINUTES 10--PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3--LAGREPORTHOURS 1--LAGINFOMINUTES 30--LAGCRITICALMINUTES 45设置完成后，两端要 start mgr各重要目录作用--dirdat 存放Trail与Extract文件--dirdef 存放defgen工具生成的源和目标库定义文件--dirprm 存放参数文件--dirrpt 存放进程报告--dirpcs 存放进程状态文件--dirsql 存放sql脚本文件--dirtmp 存放当事务超过已分配内存，默认存储于此配置抽取、初始化，定义文件（源端）cd ogg/dirprm ----cd到参数文件目录下vi exta.prm ----抽取进程EXTRACT extaUSERID ggs, PASSWORD ggsTRANLOGOPTIONS EXCLUDEUSER ggsRMTHOST 10.3.45.237, MGRPORT 7809 ----目标端ip或通信ipRMTTRAIL /home/mysql/ogg/dirdat/ta ----为了使用方便，进程名字可以统一为表a（exta），表b（extb），TABLE wwb.emp; ----用户.表名（小写）vi inita.prm ----初始化进程EXTRACT initaUSERID ggs, PASSWORD ggsRMTHOST 10.3.45.237, MGRPORT 7809, COMPRESSRMTTASK REPLICAT, GROUP inita TABLE wwb.emp; ----用户.表名（小写）vi da.prm ----定义参数文件defsfile /home/oracle/ogg/dirdef/da.def ----为了使用方便，进程名字可以统一为表a（exta），表b（extb），对应源端表编号userid ggs,password ggsTABLE wwb.emp; ----用户.表名（小写）vi.ida.prm ----定义参数文件defsfile /home/oracle/ogg/dirdef/ida.defuserid ggs,password ggsTABLE wwb.emp;两个定义参数文件编辑完成后，要生成定义文件，并拷贝到目标端的 ogg/dirdef 目录下/home/oracle/ogg/defgen PARAMFILE /home/oracle/ogg/dirprm/ida.prm ------生成定义文件语句,注意路径。生成后把ida.def和da.def拷贝到目标端ogg/dirdef 下/home/oracle/ogg/defgen PARAMFILE /home/oracle/ogg/dirprm/da.prm配置应用checkpoint表、进程、初始化（目标端）cd ogg/./ggsciGGSCI > edit param ./GLOBALS ----添加checkpoint表GGSCHEMA wwbCHECKPOINTTABLE wwb.checkpoint ----net3g指同步表的ownerGGSCI (standby) 5> dblogin userid ogg Password: Successfully logged into database.GGSCI (standby) 6> add checkpointtable net3g.checkpoint ----然后创建checkpoint表cd ogg/cd dirprm/vi repa.prm ----应用进程replicat repasourcedefs /home/mysql/ogg/dirdef/da.def ----对应源端表编号SOURCEDB net3g,userid root,password a ----net3g指的是要同步表的owner reperror default, discarddiscardfile ./dirrpt/repa.dsc,APPEND,MEGABYTES 1000MAP wwb.EMP, TARGET wwb.emp; ----注意大小写！！！！！MAP 后接 用户.表名（大写），空格用户.表名（小写）;vi inita.prm ----初始化进程replicat initasourcedefs /home/mysql/ogg/dirdef/ida.defSOURCEDB net3g,userid root,password areperror default, discarddiscardfile ./dirrpt/inita.dsc,APPEND,MEGABYTES 1000MAP wwb.EMP, TARGET wwb.emp; ----注意大小写！！！！如上5、创建存放Trail与Extract文件目录mkdir -p /home/oracle/ogg/dirdat/ta mkdir -p /home/mysql/ogg/dirdat/ta 添加进程 源数据库./ggsci ----在安装ogg目录下执行dblogin userid ggs, password ggs ----在ggsci下，登录到oracle数据库add trandata net3g.td_order_relations ----捕获表的信息add extract initk, sourceistable ----配置Extract初始化提取进程,增加一个extract组（initd）；后面的意思是一次性抽取表中所有的信息。（完成后，在目标数据库add replicat...）目标数据库./ggsci ----在安装ogg目录下执行dblogin sourcedb net3g, userid root, password a ----在ggsci下，登录到mysql数据库add replicat initk, specialrun ----配置replicat应用进程，增加一个replicat组（initd）源端添加extractadd extract extk,tranlog,begin nowadd exttrail /home/oracle/ogg/dirdat/tk,extract extk,megabytes 100add rmttrail /home/mysql/ogg/dirdat/tk,extract extk,megabytes 100创建replicat：add replicat repk,exttrail /home/mysql/ogg/dirdat/tk,checkpointtable net3g.checkpoint至此，配置及进程全部完成！！！源端./ggsciGGSCI > info all ----查看进程是否添加完成Program Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT stop EXTA 00:00:00 00:00:00 首先GGSCI > start exta ----开始抽取进程GGSCI > info all ----查看进程是否启动Program Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EXTA 00:00:00 00:00:06 其次GGSCI > start inita ----开始初始化进程GGSCI > info initaEXTRACT INITA Last Started 2015-09-24 11:24 Status RUNNINGCheckpoint Lag Not AvailableLog Read Checkpoint Table NET3G.TD_FTPPK_LOG 2015-09-24 11:34:49 Record 1Task SOURCEISTABLE目标端GGSCI > info all ----查看目标端进程是否添加完成GGSCI (ora204) 1> info inita待初始化进程停止后，开启目标端repa应用#######OGG中登陆MYSQL报错解决GGSCI (bys2.oel.com) 2> dblogin sourcedb oggtest,userid rootPassword: 2013-08-30 12:03:14 WARNING OGG-00769 MySQL Login failed: . SQL error (2002). Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).ERROR: Failed to connect to MySQL database engine for HOST localhost, DATABASE , USER root, PORT 3306.GGSCI (bys2.oel.com) 3> exit[oracle@bys2 mysqlogg]$ su -Password: [root@bys2 ~]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock[root@bys2 ~]# exitlogout[oracle@bys2 mysqlogg]$ ./ggsciOracle GoldenGate Command Interpreter for MySQLVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (bys2.oel.com) 1> dblogin sourcedb oggtest,userid rootPassword: Successfully logged into database.
OGG常用命令总结
stats：查看进程抽取数据情况，用于检测数据丢失问题。info *：用于检测进程读取文件情况信息。info all：查看所有增量抽取进程以及增量推送进程info all,task：用于查看全量抽取进程stop *：停止进程stop *!：强行停止进程kill：杀掉无法停止的进程start *：启动进程 ( extract 、 replicat )start/stop mgr：开启/停止 mgr进程view params *：查看进程所需的配置信息edit params *：编辑进程所需的配置信息info exittrail *：查看进程创建时的信息view report *：查看进程运行时log信息info credentialstore： 查看配置用户信息，用于用户名、存储密码，为了安全。注意：CredentialSore是 ogg 12c 新特性add credentialstore：添加配置用户信息文件。delete credentialstore：删除用户配置文件。alter credentialstore add user xx* password xx* alias xx* ：分别为 用户名 、密码 、别名alter credentialstore delete user xx* : 删除一个用户信息dblogin userid xx*,password xx*：数据库登陆，明文dblogin useridalias xx*：数据库登陆，使用用户的别名alias登陆alter * ,extseqno 0, extrba 0：extseqno 设置读取文件的位置，extrba 设置读取文件内容位置add extract e*, tranlog, begin now：创建增量进程—>配置相应的配置文件add exttrail ./dirdat/xx*,extract e_gessdb,megabytes 100：配置进程add extract p*,exttrailsource ./dirdat/xx*：创建推送进程—>配置相应的配置文件add extract i*, sourceistable：创建全量进程—>配置相应的配置文件