LINUX环境OGG同步测试

因为刚换工作所以开始有点忙，刚闲下来就把以前的案例又重新测试给大家分享一下。本文主要记录了测试的过程命令，虽然结果没截图，但是结果是ok的而且经过多次测试。如果本文中有问题的地方欢迎留言指出

环境描述

源主机                                                 目的主机操作系统：RatHat Linux 6.5 x64         操作系统：RatHat Linux 6.5 x64主机名：source.zhan                         主机名：target.zhanIP地址：192.168.214.52                     IP地址：192.168.214.53数据库版本：11.2.0.4 x64                   数据库版本：11.2.0.4 x64数据库SID：zhankys                          数据库SID：zhankytOGG版本：12.1.2.1                          OGG版本：12.1.2.1

环境准备(源目的相同)

创建目录赋权

--赋权归档目录mkdir -p /u01/archivelogchown -R oracle:oinstall /u01chmod -R 775 /u01--赋权软件安装包目录mkdir -p /u01/zkychown -R oracle:oinstall /u01chmod -R 775 /u01--创建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

数据库准备(源目的相同)

--登录数据库sqlplus / as sysdba--创建ogg账户create tablespace ogg_tablespace datafile '/u01/app/oracle/oradata/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=/u01/archivelog' 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)    )  )ZHANKYT =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.214.53)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ZHANKYT)    )  )

初始化数据(源目的相同)

初始化数据准备(源目的相同)（初始化就是保持两边数据库的基础数据一样。ps：这里因为测试等后面有时间在从新弄一份模拟真实环境初始化）

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 /u01/zky/unzip 121210_ggs_Linux_x64_shiphome.zipcd fbo_ggs_Linux_x64_shiphome/Disk1/./runInstaller

配置OGG参数

创建ogg目录(源目的相同)

cd /ogg./ggscicreate subdirs

创建checkpoint表(源目的相同)（如果有多实例的时候需要在dblogin的时候@库名：dblogin userid goldengate@库名,password goldengate）

{dblogin userid goldengate,password goldengateadd checkpointtable goldengate.ggs_checkpoint}

设置globals(源目的相同)

edit params ./globals{CHECKPOINTTABLE goldengate.ggs_checkpointUNLOCKEDTRAILFILES}

配置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 userid goldengate,password goldengateadd trandata goldengate.tcustmeradd trandata goldengate.tcustord

配置extract（源）

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

配置pump（源）

add extract p_cs,exttrailsource ./dirdat/cs,begin nowadd rmttrail ./dirdat/cs,extract p_cs,megabytes 5edit param p_cs{EXTRACT p_csSETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")GETENV (NLS_LANG)userid goldengate,password goldengateNOPASSTHRU RMTHOST 192.168.214.53,MGRPORT 7809,TIMEOUT 120RMTTRAIL ./dirdat/cs,format RELEASE 12.1DISCARDFILE ./dirrpt/p_cs.dsc,PURGEtable goldengate.tcustmer;table goldengate.tcustord;}start p_cs

配置replicat（目的）

add replicat r_cs,exttrail ./dirdat/cs,checkpointtable goldengate.ggs_checkpointedit param r_cs{REPLICAT b_r_29SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")GETENV (NLS_LANG)userid goldengate,password goldengateHANDLECOLLISIONSASSUMETARGETDEFSREPERROR DEFAULT,DISCARDDBOPTIONS NOSUPPRESSTRIGGERSDISCARDFILE ./dirrpt/b_r_29.dsc,PURGEMAP goldengate.TCUSTMER, TARGET goldengate.TCUSTMER;MAP goldengate.TCUSTORD, TARGET goldengate.TCUSTORD;}start r_cs

校验结果

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;