OOG：一台服务器两个实例之间的OGG

这里必须报怨，提出这个测试的兄弟做的时候出问题了，然后我说做出来给他。但是他现在网吧打游戏mmp！！！

测试目的及思路

本文主要测试目的：验证一台多实例的服务器上是否可以通过OGG来实习实例之间的同步（一台主机上两个实例之间表数据通过OGG同步）
主要思路：一般我们OGG的思路是抽取、传送、应用。虽然OGG有很多种应用方式，但大多都是多台服务器，毕竟很少遇到一台服务器两个库之间做ogg。当我兄弟问我这个环境是否可以通过OGG同步，我第一反应是可以，大概实现方式就是抽取出来放在本地直接应用就好了。（经过测试的确可以）

环境描述

操作系统：RatHat Linux 6.5 x64主机名：source.zhanIP地址：192.168.214.52数据库版本：11.2.0.4 x64数据库SID：zhankys（源）、zhankyd（目的）OGG版本：12.1.2.1

环境准备

创建所需目录并授权

--赋权给安装包mkdir -p /softchown -R oracle:oinstall /softchmod -R 775 /soft--归档日志路径mkdir -p /archivelog/zhankysmkdir -p /archivelog/zhankydchown -R oracle:oinstall /archivelogchmod -R 775 /archivelog--创建OGG安装目录mkdir /oggchown -R oracle:oinstall /oggchmod -R 775 /ogg--设置OGG环境变量echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib'>>/home/oracle/.bash_profile

数据库准备(zhankys)

--登录数据库export ORACLE_SID=zhankyssqlplus / as sysdba--创建ogg账户create tablespace ogg_tablespace datafile '/u01/app/oracle/oradata/zhankys/ogg01.dbf' size 10m autoextend on next 5m;create user goldengate identified by goldengate default tablespace ogg_tablespace;grant dba to goldengate;--查看归档、强制日志模式、数据库级别的补充日志是否开启（注意归档存放目录）archive log list;select force_logging,supplemental_log_data_min from v$database;show parameter enable_goldengate_replication;--开启归档shutdow immediatestart mountalter database archivelog;alter system set log_archive_dest_1='location=/archivelog/zhankys' scope =both;alter database open;--开启数据库强制日志模式、数据库级别的补充日志alter database force logging;alter database add supplemental log data;--修改允许使用ogg的参数（针对11.2.0.4库）ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

数据库准备(zhankyd)

--登录数据库export ORACLE_SID=zhankydsqlplus / as sysdba--创建ogg账户--创建ogg账户create tablespace ogg_tablespace datafile '/u01/app/oracle/oradata/zhankyd/ogg01.dbf' size 10m autoextend on next 5m;create user goldengate identified by goldengate default tablespace ogg_tablespace;grant dba to goldengate;--查看归档、强制日志模式、数据库级别的补充日志是否开启（注意归档存放目录）archive log list;select force_logging,supplemental_log_data_min from v$database;show parameter enable_goldengate_replication;--开启归档shutdow immediatestart mountalter database archivelog;alter system set log_archive_dest_1='location=/archivelog/zhankyd' scope =both;alter database open;--开启数据库强制日志模式、数据库级别的补充日志alter database force logging;alter database add supplemental log data;--修改允许使用ogg的参数（针对11.2.0.4库）ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

更改tnsname(源目的相同)

vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora {ZHANKYS =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.214.52)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ZHANKYS)    )  )ZHANKYD =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.214.52)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ZHANKYD)    )  )}

初始化数据准备（两个库都建）

connect goldengate/goldengate--创建表DROP TABLE tcustmer;CREATE TABLE tcustmer ( cust_code VARCHAR(4) NOT NULL, name VARCHAR(30),city VARCHAR(20), state CHAR(2), PRIMARY KEY (cust_code));DROP TABLE tcustord;CREATE TABLE tcustord ( cust_code VARCHAR(4) NOT NULL, product_code VARCHAR(8) NOT NULL,order_id INTEGER NOT NULL, product_price DECIMAL(8,2), product_amount INTEGER,transaction_id INTEGER, PRIMARY KEY (cust_code, product_code, order_id) );select * from goldengate.tcustmer;select * from goldengate.tcustord;

安装OGG

OGG软件安装

xhost +su - oraclecd /softunzip 121210_ggs_Linux_x64_shiphome.zipcd fbo_ggs_Linux_x64_shiphome/Disk1/./runInstaller

配置OGG参数

创建ogg目录

cd /ogg./ggscicreate subdirs

创建checkpoint表（注意dblogin到两个库都创建）

--源主机创建checkpoint表{dblogin userid goldengate@zhankys,password goldengateadd checkpointtable goldengate.ggs_checkpoint}--源实例创建完后exit在登录目的库建checkpoint表{dblogin userid goldengate@zhankyd,password goldengateadd checkpointtable goldengate.ggs_checkpoint}

设置globals

edit params ./globals{UNLOCKEDTRAILFILES}

配置MGR

edit params mgr{PORT 7809AUTOSTART ER *AUTORESTART ER *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 60LAGREPORTHOURS 1LAGINFOMINUTES 3LAGCRITICALMINUTES 10PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 3}start mgr

配置需要trandata的对象（跟踪的表为源库，注意dblogin登录是否源库）

dblogin userid goldengate@zhankys,password goldengateadd trandata goldengate.tcustmeradd trandata goldengate.tcustordinfo trandata goldengate.*

配置extract（注意userid指定抽取的库）

add extract e_single,tranlog,begin nowadd exttrail ./dirdat/single,extract b_e_29,megabytes 5 edit param e_single {EXTRACT e_singleSETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")GETENV (NLS_LANG)userid goldengate@zhankys,password goldengateEXTTRAIL ./dirdat/single,FORMAT RELEASE 12.1DISCARDFILE ./dirrpt/e_single.dsc,PURGE--NOCOMPRESSDELETESNOCOMPRESSUPDATESGETUPDATEBEFORESGETUPDATEAFTERSTRANLOGOPTIONS LOGRETENTION disabledWARNLONGTRANS 30m,CHECKINTERVAL 3mtable goldengate.tcustmer;table goldengate.tcustord;}start e_singleinfo e_single

配置replicat（注意userid指定应用的库）

dblogin userid goldengate@zhankyd,password goldengateadd replicat r_single,exttrail ./dirdat/single,checkpointtable goldengate.ggs_checkpointedit param r_single{REPLICAT r_singleSETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")GETENV (NLS_LANG)USERID goldengate@zhankyd,PASSWORD goldengateHANDLECOLLISIONSASSUMETARGETDEFSREPERROR DEFAULT,DISCARDDBOPTIONS NOSUPPRESSTRIGGERSDISCARDFILE ./dirrpt/b_r_29.dsc,PURGEMAP goldengate.TCUSTMER, TARGET goldengate.TCUSTMER;MAP goldengate.TCUSTORD, TARGET goldengate.TCUSTORD;}start r_singleinfo r_single

校验结果语句

select * from goldengate.TCUSTMER;select * from goldengate.TCUSTORD;INSERT INTO goldengate.tcustmer VALUES ('ZZZ','BG SOFTWARE CO.','SEATTLE','WZ');INSERT INTO goldengate.tcustord VALUES ('ZZZ','CAR',144,17520,3,100);COMMIT;INSERT INTO goldengate.tcustmer VALUES ('ZqZZ','BqG SOFTWARE CO.','SEATTLE','WZ');INSERT INTO goldengate.tcustord VALUES ('ZqZZ','CAR',144,17520,3,100);COMMIT;INSERT INTO goldengate.tcustmer VALUES ('ZbZ','BzG SOFTWARE CO.','SEATTLE','WZ');INSERT INTO goldengate.tcustord VALUES ('ZbZ','CAR',144,17520,3,100);COMMIT;INSERT INTO goldengate.tcustmer VALUES ('ZghZ','BG SOFTWARE CO.','SEATTLE','WZ');INSERT INTO goldengate.tcustord VALUES ('ZghZ','CAR',144,17520,3,100);COMMIT;delete goldengate.tcustmer where cust_code='ZZZ';delete goldengate.tcustord where cust_code='ZZZ';delete goldengate.tcustmer where cust_code='ZqZZ';delete goldengate.tcustord where cust_code='ZqZZ';delete goldengate.tcustmer where cust_code='ZbZ';delete goldengate.tcustord where cust_code='ZbZ';delete goldengate.tcustmer where cust_code='ZghZ';delete goldengate.tcustord where cust_code='ZghZ';commit;