11. Setting up a simple GoldenGate replication confguration between two single node databases

本篇博文以一个实验对文章的操作进行实际模拟：

源端：

IP: 192.168.77.10
HOSTNAME: source
ORACLE: 11.2.0.4
OGG: 11.2.1.0.33
SCHEMA: scott

目标端：

IP: 192.168.77.11
HOSTNAME: target
ORACLE: 11.2.0.4
OGG: 11.2.1.0.33
SCHEMA: scott

预部署：

参照《CentOS6u9 Oracle 11g 静默安装手工建库统一配置方案》部署搭建实验环境，并做以下修改：

源端修改：

hostname source
sed -i "s/^HOSTNAME=.*$/HOSTNAME=$(hostname)/g" /etc/sysconfig/network
echo "$(grep -E '127|::1' /etc/hosts)">/etc/hosts
echo "$(ifconfig eth0|grep inet|awk -F'[ :]' '{print $13}') $(hostname)">>/etc/hosts

目标端修改：

hostname target
sed -i "s/^HOSTNAME=.*$/HOSTNAME=$(hostname)/g" /etc/sysconfig/network
echo "$(grep -E '127|::1' /etc/hosts)">/etc/hosts
echo "$(ifconfig eth0|grep inet|awk -F'[ :]' '{print $13}') $(hostname)">>/etc/hosts

源端和目标端均修改：

su - oracle
rm -rf $ORACLE_HOME/network/admin/listener.ora
cat >$ORACLE_HOME/network/admin/listener.ora<<EOF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = $ORACLE_SID)
      (ORACLE_HOME = $ORACLE_HOME)
      (SID_NAME = $ORACLE_SID)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = $(hostname -i))(PORT = 1521))
  )
ADR_BASE_LISTENER = $ORACLE_BASE
EOF
lsnrctl start
echo 'startup;'|sqlplus / as sysdba

源端和目标端均装入scott业务账号：

su - oracle
sqlplus / as sysdba
@?/rdbms/admin/utlsampl.sql

源端和目标端均部署安装OGG二进制软件包：

mkdir /ggs
chown oracle: /ggs
su - oracle
cd /ggs
unzip /tmp/"Oracle GoldenGate 11.2.1.0.33 for Oracle 11g on Linux x86-64.zip"
rm -rf O*
tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar
rm -rf fbo_ggs_Linux_x64_ora11g_64bit.tar
echo 'create subdirs'|./ggsci

源端和目标端均开启库级和表级附加日志：

su - oracle
sqlplus / as sysdba
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
alter system archive log current;
exit
cd /ggs
./ggsci
DBLOGIN USERID system PASSWORD oracle
ADD TRANDATA scott.*
INFO TRANDATA scott.*
exit

OGG部署：

1° 源库创建数据库用户：

su - oracle
sqlplus / as sysdba
create tablespace GGS datafile '/oradata/orcl/ggs01.dbf' size 50m
autoextend on next 64m maxsize 30G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER ggs IDENTIFIED BY ggs DEFAULT TABLESPACE GGS;
GRANT CREATE SESSION, ALTER SESSION to ggs;
GRANT ALTER SYSTEM TO ggs;
GRANT CONNECT, RESOURCE to ggs;
GRANT SELECT ANY DICTIONARY to ggs;
GRANT FLASHBACK ANY TABLE to ggs;
GRANT SELECT ON DBA_CLUSTERS TO ggs;
GRANT EXECUTE ON DBMS_FLASHBACK TO ggs;
GRANT SELECT ANY TRANSACTION To ggs;
GRANT SELECT ANY TABLE TO ggs;
-- GRANT SELECT ON SCOTT.DEPT to ggs;
-- GRANT SELECT ON SCOTT.EMP to ggs;
-- GRANT SELECT ON SCOTT.BONUS to ggs;
-- GRANT SELECT ON SCOTT.SALGRADE to ggs;
exit

2° 源端配置MGR进程：

su - oracle
cd /ggs/dirprm
cat >mgr.prm<<EOF
PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4
PURGEOLDEXTRACTS /ggs/dirdat/st*, USECHECKPOINTS, MINKEEPHOURS 2
EOF
cd /ggs
echo 'START MGR'|./ggsci
echo 'STATUS MGR'|./ggsci

3° 源端配置Classic Capture进程配置文件：

su - oracle
cd /ggs/dirprm
cat >EGGTEST1.prm<<EOF
EXTRACT EGGTEST1
USERID ggs, PASSWORD ggs
EXTTRAIL /ggs/dirdat/st
TABLE scott.*;
EOF

4° 源端配置Datapump进程配置文件：

su - oracle
cd /ggs/dirprm
cat >PGGTEST1.prm<<EOF
EXTRACT PGGTEST1
USERID ggs, PASSWORD ggs
RMTHOST 192.168.77.11, MGRPORT 8809
RMTTRAIL /ggs/dirdat/rt
TABLE scott.*;
EOF

5° Classic Capture和Datapump进程配置：

su - oracle
cd /ggs
./ggsci
ADD EXTRACT EGGTEST1, TRANLOG, BEGIN NOW
ADD EXTTRAIL /ggs/dirdat/st, EXTRACT EGGTEST1
ADD EXTRACT PGGTEST1, EXTTRAILSOURCE /ggs/dirdat/st
ADD RMTTRAIL /ggs/dirdat/rt, EXTRACT PGGTEST1
exit

6° 目标库创建数据库用户：

su - oracle
sqlplus / as sysdba
create tablespace GGS datafile '/oradata/orcl/ggs01.dbf' size 50m
autoextend on next 64m maxsize 30G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER ggs IDENTIFIED BY ggs DEFAULT TABLESPACE GGS;
ALTER USER ggs QUOTA UNLIMITED ON ggs;
GRANT CREATE SESSION, ALTER SESSION to ggs;
GRANT CONNECT, RESOURCE to ggs;
GRANT SELECT ANY DICTIONARY to ggs;
GRANT CREATE TABLE TO ggs;
GRANT SELECT ANY TABLE TO ggs;
GRANT INSERT ANY TABLE TO ggs;
GRANT UPDATE ANY TABLE TO ggs;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.DEPT to ggs;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.EMP to ggs;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.SALGRADE to ggs;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON SCOTT.BONUS to ggs;
exit

7° 目标端配置MGR进程：

su - oracle
cd /ggs/dirprm
cat >mgr.prm<<EOF
PORT 8809
DYNAMICPORTLIST 8810-8820, 8830
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /ggs/dirdat/rt*, USECHECKPOINTS, MINKEEPHOURS 2
EOF
cd /ggs
echo 'START MGR'|./ggsci
echo 'STATUS MGR'|./ggsci

8° 目标端创建检查点表：

./ggsci
DBLOGIN, USERID ggs PASSWORD ggs
ADD CHECKPOINTTABLE ggs.rggtest1_ckpt
exit

9° 目标端配置Replicat进程配置文件：

su - oracle
cd /ggs/dirprm
cat >RGGTEST1.prm<<EOF
REPLICAT RGGTEST1
USERID ggs, PASSWORD ggs
DISCARDFILE /ggs/dirrpt/RGGTEST1.dsc, append, MEGABYTES 500
ASSUMETARGETDEFS
MAP SCOTT.*, TARGET SCOTT.*;
EOF

10° 目标端添加Replicat进程：

su - oracle
cd /ggs
./ggsci
ADD REPLICAT RGGTEST1, EXTTRAIL /ggs/dirdat/rt, CHECKPOINTTABLE ggs.rggtest1_ckpt
exit

11° 源端启动抽取和发送进程：

cd /ggs
# 在Oracle 11.2.0.4 之后需要打开ENABLE_GOLDENGATE_REPLICATION参数
sqlplus / as sysdba
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
exit
echo 'START EXTRACT EGGTEST1'|./ggsci
echo 'STATUS EXTRACT EGGTEST1'|./ggsci
echo 'START EXTRACT PGGTEST1'|./ggsci
echo 'STATUS EXTRACT PGGTEST1'|./ggsci

12° 目标端启动应用进程：

cd /ggs
# 在Oracle 11.2.0.4 之后需要打开ENABLE_GOLDENGATE_REPLICATION参数
sqlplus / as sysdba
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
exit
echo 'START REPLICAT RGGTEST1'|./ggsci
echo 'STATUS REPLICAT RGGTEST1'|./ggsci

13° DML同步验证：

# 源库：
su - oracle
sqlplus scott/tiger
insert into SALGRADE select * from SALGRADE;
commit;
select count(*) from SALGRADE;
exit
# 目标库
su - oracle
echo 'select count(*) from SALGRADE;'|sqlplus scott/tiger

[TOC]