OOG：一臺(tái)服務(wù)器兩個(gè)實(shí)例之間的OGG

這里必須報(bào)怨，提出這個(gè)測(cè)試的兄弟做的時(shí)候出問(wèn)題了，然后我說(shuō)做出來(lái)給他。但是他現(xiàn)在網(wǎng)吧打游戲mmp?。?！

測(cè)試目的及思路

本文主要測(cè)試目的：驗(yàn)證一臺(tái)多實(shí)例的服務(wù)器上是否可以通過(guò)OGG來(lái)實(shí)習(xí)實(shí)例之間的同步（一臺(tái)主機(jī)上兩個(gè)實(shí)例之間表數(shù)據(jù)通過(guò)OGG同步）
主要思路：一般我們OGG的思路是抽取、傳送、應(yīng)用。雖然OGG有很多種應(yīng)用方式，但大多都是多臺(tái)服務(wù)器，畢竟很少遇到一臺(tái)服務(wù)器兩個(gè)庫(kù)之間做ogg。當(dāng)我兄弟問(wèn)我這個(gè)環(huán)境是否可以通過(guò)OGG同步，我第一反應(yīng)是可以，大概實(shí)現(xiàn)方式就是抽取出來(lái)放在本地直接應(yīng)用就好了。（經(jīng)過(guò)測(cè)試的確可以）

環(huán)境描述

操作系統(tǒng)：RatHat Linux 6.5 x64
主機(jī)名：source.zhan
IP地址：192.168.214.52
數(shù)據(jù)庫(kù)版本：11.2.0.4 x64
數(shù)據(jù)庫(kù)SID：zhankys（源）、zhankyd（目的）
OGG版本：12.1.2.1

環(huán)境準(zhǔn)備

創(chuàng)建所需目錄并授權(quán)

--賦權(quán)給安裝包
mkdir -p /soft
chown -R oracle:oinstall /soft
chmod -R 775 /soft
--歸檔日志路徑
mkdir -p /archivelog/zhankys
mkdir -p /archivelog/zhankyd
chown -R oracle:oinstall /archivelog
chmod -R 775 /archivelog
--創(chuàng)建OGG安裝目錄
mkdir /ogg
chown -R oracle:oinstall /ogg
chmod -R 775 /ogg
--設(shè)置OGG環(huán)境變量
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib'>>/home/oracle/.bash_profile

數(shù)據(jù)庫(kù)準(zhǔn)備(zhankys)

--登錄數(shù)據(jù)庫(kù)
export ORACLE_SID=zhankys
sqlplus / as sysdba
--創(chuàng)建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;
--查看歸檔、強(qiáng)制日志模式、數(shù)據(jù)庫(kù)級(jí)別的補(bǔ)充日志是否開(kāi)啟（注意歸檔存放目錄）
archive log list;
select force_logging,supplemental_log_data_min from v$database;
show parameter enable_goldengate_replication;

--開(kāi)啟歸檔
shutdow immediate
start mount
alter database archivelog;
alter system set log_archive_dest_1='location=/archivelog/zhankys' scope =both;
alter database open;
--開(kāi)啟數(shù)據(jù)庫(kù)強(qiáng)制日志模式、數(shù)據(jù)庫(kù)級(jí)別的補(bǔ)充日志
alter database force logging;
alter database add supplemental log data;
--修改允許使用ogg的參數(shù)（針對(duì)11.2.0.4庫(kù)）
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

數(shù)據(jù)庫(kù)準(zhǔn)備(zhankyd)

--登錄數(shù)據(jù)庫(kù)
export ORACLE_SID=zhankyd
sqlplus / as sysdba
--創(chuàng)建ogg賬戶
--創(chuàng)建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;
--查看歸檔、強(qiáng)制日志模式、數(shù)據(jù)庫(kù)級(jí)別的補(bǔ)充日志是否開(kāi)啟（注意歸檔存放目錄）
archive log list;
select force_logging,supplemental_log_data_min from v$database;
show parameter enable_goldengate_replication;

--開(kāi)啟歸檔
shutdow immediate
start mount
alter database archivelog;
alter system set log_archive_dest_1='location=/archivelog/zhankyd' scope =both;
alter database open;
--開(kāi)啟數(shù)據(jù)庫(kù)強(qiáng)制日志模式、數(shù)據(jù)庫(kù)級(jí)別的補(bǔ)充日志
alter database force logging;
alter database add supplemental log data;
--修改允許使用ogg的參數(shù)（針對(duì)11.2.0.4庫(kù)）
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)
    )
  )
}

初始化數(shù)據(jù)準(zhǔn)備（兩個(gè)庫(kù)都建）

connect goldengate/goldengate
--創(chuàng)建表
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 /soft
unzip 121210_ggs_Linux_x64_shiphome.zip
cd fbo_ggs_Linux_x64_shiphome/Disk1/
./runInstaller

配置OGG參數(shù)

創(chuàng)建ogg目錄

cd /ogg
./ggsci
create subdirs

創(chuàng)建checkpoint表（注意dblogin到兩個(gè)庫(kù)都創(chuàng)建）

--源主機(jī)創(chuàng)建checkpoint表
{
dblogin userid goldengate@zhankys,password goldengate
add checkpointtable goldengate.ggs_checkpoint
}
--源實(shí)例創(chuàng)建完后exit在登錄目的庫(kù)建checkpoint表
{
dblogin userid goldengate@zhankyd,password goldengate
add checkpointtable goldengate.ggs_checkpoint
}

設(shè)置globals

edit params ./globals
{
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的對(duì)象（跟蹤的表為源庫(kù)，注意dblogin登錄是否源庫(kù)）

dblogin userid goldengate@zhankys,password goldengate
add trandata goldengate.tcustmer
add trandata goldengate.tcustord
info trandata goldengate.*

配置extract（注意userid指定抽取的庫(kù)）

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

table goldengate.tcustmer;
table goldengate.tcustord;
}
start e_single
info e_single

配置replicat（注意userid指定應(yīng)用的庫(kù)）

dblogin userid goldengate@zhankyd,password goldengate
add replicat r_single,exttrail ./dirdat/single,checkpointtable goldengate.ggs_checkpoint
edit param r_single
{
REPLICAT r_single
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
GETENV (NLS_LANG)
USERID goldengate@zhankyd,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_single
info r_single

校驗(yàn)結(jié)果語(yǔ)句

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;