1.安装环境说明
os: redhat linux 5.5 64bit
db:Oracle 11.2.0.3 64bit
模式:采用goldengate datapump 模式的搭建
2.配置ORACLE_HOME 和 ORACLE_SID
如果系统上只有一个单实例,那么可以在操作系统级别设置ORACLE_HOME和ORACLE_SID变量。
如果有多个instance,那么可以再Extrace和Replicat group 中设置setenv变量,这种session级别的设置会覆盖操作系统的设置。
如果有多个实例,那么可以分别配置同步的Extrace groups,那么参数文件可以按照如下进行配置:
group 1:
extract ora9a
setenv (ORACLE_HOME="/u01/app/product")
setenv("ORACLE_SID="oraa")
useridalias tiger1
rmhost sysb
rmttrail /u01/ggs/dirdat/rt
table hr.emp;
table hr.salary;
group 2:
extract orab
setenv (ORACLE_HOME="/u01/app/product")
setenv("ORACLE_SID="orab")
useridalias tiger1
rmhost sysb
rmttrail /u01/ggs/dirdat/st
table fin.sales;
table fin.cust;
3.设置library路径
ogg使用共享的library.所以在linux上运行ogg之前,必须先配置好library路径。
假设ogg的安装目录是/u01/ggs,那么在/home/oracle/.bash_profile文件里添加如下内容:
export PATH=/u01/ggs:$PATH
export LD_LIBRARY_PATH=/u01/ggs:$LD_LIBRARY_PATH
4.安装ogg
在ogg11中,直接解压缩安装文件就行了,到了ogg12c的版本,ogg使用oui来进行安装。
[oracle@ogg2 Disk1]$ pwd
/u01/software/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@ogg2 Disk1]$ ls
install response runInstaller stage
[oracle@ogg2 Disk1]$
运行RunInstaller:
/u01/software/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@ogg2 Disk1]$ ls
install response runInstaller stage
[oracle@ogg2 Disk1]$
运行RunInstaller:
如果数据库版本是12c,就选择12c;如果数据库是11g,就选择11g的。
software location 选择ogg的解压安装目录
进入ogg安装目录,运行如下命令:
[oracle@ogg1 ggs]$ ldd --version
[oracle@ogg1 ggs]$ldd ggsci --查看需要的安装包
librt.so.1 => /lib64/librt.so.1 (0x0000003dafe00000)
libdl.so.2 => /lib64/libdl.so.2 (0x0000003daf200000)
libgglog.so => /u01/zw/libgglog.so (0x00002b9d080f3000)
libggrepo.so => /u01/zw/libggrepo.so (0x00002b9d0849c000)
libdb-5.2.so => /u01/zw/libdb-5.2.so (0x00002b9d08707000)
libggperf.so => /u01/zw/libggperf.so (0x00002b9d089a2000)
libicui18n.so.48 => /u01/zw/libicui18n.so.48 (0x00002b9d08bd1000)
libicuuc.so.48 => /u01/zw/libicuuc.so.48 (0x00002b9d08edf000)
libicudata.so.48 => /u01/zw/libicudata.so.48 (0x00002b9d0915d000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003daf600000)
libxerces-c.so.28 => /u01/zw/libxerces-c.so.28 (0x00002b9d0a823000)
libantlr3c.so => /u01/zw/libantlr3c.so (0x00002b9d0ad3a000)
libnnz11.so => not found
libclntsh.so.11.1 =>not found
libggnnzitp.so => not found
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x0000003dc1000000)
libm.so.6 => /lib64/libm.so.6 (0x0000003daee00000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000003dbd400000)
libc.so.6 => /lib64/libc.so.6 (0x0000003daea00000)
/lib64/ld-linux-x86-64.so.2 (0x0000003dae600000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003db2e00000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002b9d0e305000)
少包了,解决办法。
[oracle@ogg02 ggs]$ find /u01 -name libons.so 或 find / -name libons.so
[oracle@ogg02 ggs]$ ln -s /u01/app/oracle/product/11.2.0.3/db_1/lib/libons.so
假设OGG的安装目录是/u01/ggs,那么在/home/oracle/.bash_profile文件里添加如下内容:
export PATH=/u01/ggs:$PATH
export LD_LIBRARY_PATH=/u01/ggs:$LD_LIBRARY_PATH
--source 使修改生效:
[oracle@ogg1 u01]$ source ~/.bash_profile
cd /u01/ggs
ggsci
info manager
在 Oracle 11g中,安装完后,需要ggsci执行create subdirs创建ogg的目录,到了12c,就不用创建了。
5.开启归档模式、强制日志、附加日志
5.1 开规档
SQL> alter system set log_archive_dest_1='location=/oradata/arch' scope=both;
SQL> alter database archivelog;
SQL> archive log list;
5.2 添加附加日志
SQL> alter database add supplemental log data;
5.3 强制日志
SQL> alter database force logging;
--确认修改:
SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
6.启用DDL支持
ogg可以配置支持ddl的同步,对ddl的支持是通过创建一些table来保存这些ddl的信息,并且有一些限制。
6.1.禁用recycel bin
SQL> alter system set recyclebin=off scope=spfile;
6.2. 创建存放DDL 信息的user并赋权
创建GG的表空间:(source和target端都要创建)
SQL> create tablespace ggs datafile '/oradata/ggs01.dbf' size 200m autoextend off;
这里的表空间不能自动扩展。ogg11g可以使用默认的user表空间,12c需要独立的表空间,否则报错。
SQL> create user ggs identified by ggs default tablespace ggs temporary tablespace temp;
SQL> grant connect,resource to ggs;
SQL> grant execute on utl_file to ggs;
退出oracle的所有session,然后用sysdba权限执行以下脚本
注意:要先进入/u01/ggs,再执行脚本
pwd
/u01/ggs
sqlplus / as sysdba
6.3执行脚本
<1>创建DDL标记表
SQL> @/u01/pjj/marker_setup.sql; (这里会提示要输入ggs用户)
<2>将在数据库中创建捕获DDL语句的Trigger等必要组件 (这里会提示要输入ggs用户)
SQL> @/u01/pjj/ddl_setup.sql;
注意:(1)执行时必须断开GGSCI连接,否则报错
(2)执行的时候不用选择操作类型了。在11g中需要选择initialsetup选项。
<3>创建GGS_GGSUSER_ROLE角色 这里会提示要输入ggs用户)
SQL> @/u01/pjj/role_setup.sql;
<4> 授予给extract group参数中定义的userid用户
SQL> grant GGS_GGSUSER_ROLE to ggs;
<5> 启用ddl捕获触发器:
SQL> @/u01/pjj/ddl_enable.sql;
还有两个可选的脚本,用来提高ddl复制性能:
@?/rdbms/admin/dbmspool ---创建DBMS_SHARED_POOL包
@/u01/ggs/ddl_pin.sql
--通过DBMS_SHARED_POOL.keep存储过程将DDLReplication相关对象keep在共享池中,保证这些对象不要reload,提升性能。
注意,在oracle 11.2.0.4 中需要配置;
SQL> alter system set enable_goldengate_replication=true;
System altered.
7.检查点表配置
goldengate 的检查点表示 目标端 的一个可选的配置,默认情况下,goldengate不会设置检查点表机制,而依耐于文件系统的保证,相对于后者,检查点表机制更可靠,且便于日常维护。
建议在首次配置目标库的时候,在全局变量中设置检查点表,并创建,这样做的话,以后所有的replicat进程默认情况下均会使用这个检查点表。
配置检查点表后,goldengate日常维护的操作区别在于,所有replicate进程的添加删除前均需要从ggsci中登录数据。
配置步骤如下:
1. 登录ggsci界面,在./GLOBALS文件里添加checkpoint表名
checkpointtable owner.table --指定的检查点记录表
2.连上db,创建checkpoint表
dblogin sourcedb dbname,userid db_user,password pw
add checkpointtable ggs.checkpoint --生成一个检查点记录表
这里不做配置,直接在后面测试中配置。
8.OGG单向复制测试
注意三点: (1)目标库的用户名和对象名称可以和源端不同,关键在于配置文件中能够正确匹配。
(2)配置源端和目标端tnsnames,保持互联互通。
(3)对于采用oralce asm的实例,还需要配置监听,使得goldengate能够连接到对应的asm实例上,访问redo日志。
可以通过lsnrctl status 命令查看监听器状态,看看能否找到asm实例的信息,默认情况下asm实例已经注册,信息类似
service "+ASM" has 1 instance(s)
8.1在source和target database 上创建测试用户
--source database
SQL> create user sender identified by oracle default tablespace users temporary tablespace temp; --创建用户
User created.
SQL> grant connect,resource,dba to sender;
Grant succeeded.
2. --target database
SQL> create user receiver identified by oracle default tablespace users temporary tablespace temp; --创建用户
User created.
SQL> grant connect,resource,dba to receiver;
Grant succeeded.
8.2 配置SourceDB 的复制队列
GGSCI (ogg02) 10> dblogin userid ggs, password ggs
Successfully logged into database.
Successfully logged into database.
GGSCI (ogg02) 11> add extract ext1,tranlog, begin now
EXTRACT added.
GGSCI (ogg02) 12> add exttrail /u01/pjj/dirdat/lt, extract ext1
EXTTRAIL added.
修改抽取进程ext1参数:
GGSCI (ogg1) 13> edit params ext1
extract ext1
userid ggs, password ggs
exttrail /u01/pjj/dirdat/lt
ddl include mapped objname sender.*;
table sender.*;
8.2.1 增加Pump 进程
GGSCI (ogg1) 8> add extract dpump,exttrailsource /u01/pjj/dirdat/lt
EXTRACT added.
--这里指定我们localtrail 的位置。
EXTRACT added.
--这里指定我们localtrail 的位置。
GGSCI (ogg02) 18> add rmttrail /u01/pjj/dirdat/rt, extract dpump,megabytes 10 --trail文件的大小
RMTTRAIL added.
RMTTRAIL added.
配置Data Pump 参数:
GGSCI (gg1) 56> view params dpump
extract dpump
userid ggs@ogg1, password ggs
dynamicresolution
passthru
rmthost 192.168.1.62, mgrport 7809
rmttrail /u01/ggs/dirdat/rt
table sender.*;
配置Data Pump 参数:
-----------------------------------------以这个为准
GGSCI (ogg02) 27> edit params dpump
extract dpumpuserid ggs, password ggs
dynamicresolution
passthru
rmthost 192.168.1.62, mgrport 7809 --target
rmttrail /u01/zt/dirdat/rt --target
table sender.*;
GGSCI (ogg02) 27> edit params dpump --查看一下
extract dpump
userid ggs, password ggs
dynamicresolution
passthru
rmthost 192.168.56.23, mgrport 7809
rmttrail /u01/pjj/dirdat/rt
table sender.*;
extract dpump
userid ggs, password ggs
dynamicresolution
passthru
rmthost 192.168.56.23, mgrport 7809
rmttrail /u01/pjj/dirdat/rt
table sender.*;
9. target 端
9.1 添加checkpointtable
GGSCI (ogg2) 1> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (ogg2) 2> add checkpointtable ggs.checkpoint
Successfully created checkpoint table ggs.checkpoint.
Successfully logged into database.
GGSCI (ogg2) 2> add checkpointtable ggs.checkpoint
Successfully created checkpoint table ggs.checkpoint.
9.2添加target replicat进程
GGSCI (ogg04) 1> add replicat rep1,exttrail /u01/pjj/dirdat/rt, checkpointtable ggs.checkpoint
REPLICAT added.
GGSCI (ogg04) 2> edit params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggs,password ggs
discardfile /u01/pjj/dirdat/rep1_discard.txt,append, megabytes 10
DDL
map sender.*, target receiver.*;
ASSUMETARGETDEFS
userid ggs,password ggs
discardfile /u01/pjj/dirdat/rep1_discard.txt,append, megabytes 10
DDL
map sender.*, target receiver.*;
10.启动source端
GGSCI (ogg02) 30> info all --查看一下
Program Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
EXTRACT STOPPED DPUMP 00:00:00 00:20:49
EXTRACT STOPPED EXT1 00:00:00 00:26:57
EXTRACT STOPPED EXT2 00:00:00 00:25:27
1.启动extract进程
GGSCI (ogg02) 31> start ext2
Sending START request to MANAGER ...
EXTRACT EXT2 starting
EXTRACT EXT2 starting
GGSCI (ogg02) 32> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPUMP 00:00:00 00:20:58
EXTRACT STOPPED EXT1 00:00:00 00:27:06
EXTRACT RUNNING EXT2 00:25:35 00:00:00 running说明启动了
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPUMP 00:00:00 00:20:58
EXTRACT STOPPED EXT1 00:00:00 00:27:06
EXTRACT RUNNING EXT2 00:25:35 00:00:00 running说明启动了
2.启动datapump进程
GGSCI (ogg02) 33> start extract dpump
Sending START request to MANAGER ...
EXTRACT DPUMP starting
Sending START request to MANAGER ...
EXTRACT DPUMP starting
GGSCI (ogg02) 34> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:24:11
EXTRACT STOPPED EXT1 00:00:00 00:30:19
EXTRACT RUNNING EXT2 00:00:00 00:00:01
Target DB
GGSCI (ogg2) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:11:40
GGSCI (ogg2) 21> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (ogg2) 29> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
测试Data 复制
--Source DB:
SQL> conn sender/oracle;
Connected.
SQL> create table p1 as select * from sys.dba_users;
Table created.
SQL> select count(1) from p1;
COUNT(1)
----------
32
--Target DB:
SQL> conn receiver/oracle;
Connected.
SQL> select count(1) from p1;
COUNT(1)
----------
32
SQL> select count(1) from p1;
COUNT(1)
----------
32
GGSCI (ogg2) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:11:40
GGSCI (ogg2) 21> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (ogg2) 29> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
测试Data 复制
--Source DB:
SQL> conn sender/oracle;
Connected.
SQL> create table p1 as select * from sys.dba_users;
Table created.
SQL> select count(1) from p1;
COUNT(1)
----------
32
--Target DB:
SQL> conn receiver/oracle;
Connected.
SQL> select count(1) from p1;
COUNT(1)
----------
32
SQL> select count(1) from p1;
COUNT(1)
----------
32