LINUX环境OGG同步测试

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

环境描述

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

环境准备(源目的相同)

创建目录赋权

--赋权归档目录
mkdir -p /u01/archivelog
chown -R oracle:oinstall /u01
chmod -R 775 /u01
--赋权软件安装包目录
mkdir -p /u01/zky
chown -R oracle:oinstall /u01
chmod -R 775 /u01
--创建OGG安装目录
mkdir /ogg
chown -R oracle:oinstall /ogg
chmod -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 immediate
start mount
alter 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 - oracle
cd /u01/zky/
unzip 121210_ggs_Linux_x64_shiphome.zip
cd fbo_ggs_Linux_x64_shiphome/Disk1/
./runInstaller

配置OGG参数

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

cd /ogg
./ggsci
create subdirs

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

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

设置globals(源目的相同)

edit params ./globals
{
CHECKPOINTTABLE goldengate.ggs_checkpoint
UNLOCKEDTRAILFILES
}

配置MGR(源目的相同)

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

配置需要trandata的对象（源）

dblogin userid goldengate,password goldengate
add trandata goldengate.tcustmer
add trandata goldengate.tcustord

配置extract（源）

add extract e_cs,tranlog,begin now
add exttrail ./dirdat/cs,extract e_cs,megabytes 5 
edit param e_cs 
{
EXTRACT e_cs
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
GETENV (NLS_LANG)
userid goldengate,password goldengate
EXTTRAIL ./dirdat/cs,FORMAT RELEASE 12.1
DISCARDFILE ./dirrpt/e_cs.dsc,PURGE
--NOCOMPRESSDELETES
NOCOMPRESSUPDATES
GETUPDATEBEFORES
GETUPDATEAFTERS
TRANLOGOPTIONS LOGRETENTION disabled
WARNLONGTRANS 30m,CHECKINTERVAL 3m

table goldengate.tcustmer;
table goldengate.tcustord;
}
start e_cs

配置pump（源）

add extract p_cs,exttrailsource ./dirdat/cs,begin now
add rmttrail ./dirdat/cs,extract p_cs,megabytes 5
edit param p_cs
{
EXTRACT p_cs
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
GETENV (NLS_LANG)
userid goldengate,password goldengate
NOPASSTHRU 
RMTHOST 192.168.214.53,MGRPORT 7809,TIMEOUT 120
RMTTRAIL ./dirdat/cs,format RELEASE 12.1
DISCARDFILE ./dirrpt/p_cs.dsc,PURGE

table goldengate.tcustmer;
table goldengate.tcustord;
}
start p_cs

配置replicat（目的）

add replicat r_cs,exttrail ./dirdat/cs,checkpointtable goldengate.ggs_checkpoint
edit param r_cs
{
REPLICAT b_r_29
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
GETENV (NLS_LANG)
userid goldengate,password goldengate
HANDLECOLLISIONS
ASSUMETARGETDEFS
REPERROR DEFAULT,DISCARD
DBOPTIONS NOSUPPRESSTRIGGERS
DISCARDFILE ./dirrpt/b_r_29.dsc,PURGE

MAP 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;