一.环境信息
1.主机信息
源主机:109.115.101.50, HPUX, 10.2.0.5 64bit
目标主机:109.115.101.61, HPUX, 10.2.0.5 64bit
2.软件信息
Oracle GoldenGate 11.2.1.0.1 for Oracle 10g on HPUX
二.环境需求
1.内存需求
每个extract和replicat进程需要25M~55M内存
2.空间需求
建议100G~200G
3.网络需求
- 尽可能使用较快的网络环境,并配置冗余。
- 使用TCP/IP服务,开通GoldenGate所需端口的防火墙
- 配置/etc/hosts文件
4.数据库权限
(1).源端用户
CONNECT
unlimited tablespace
FLASHBACK ANY TABLE
SELECT ANY DICTIONARY
SELECT ANY TABLE
alter any table
RESOURCE
execute on dbms_flashback
execute on utl_file
drop any table
alter any table
delete any table
ALTER SESSION
--DDL DBA
(2).目标端用户
CONNECT
unlimited tablespace
SELECT ANY DICTIONARY
SELECT ANY TABLE
alter any table
RESOURCE
execute on utl_file
delete any table
drop any table
alter any table
ALTER SESSION
lock any table
insert any table
update any table
三.安装OGG
1.创建目录
mkdir -p /oracle/ggs
chown -R oracle:dba /oracle/ggs
2.配置环境变量
参考:
export PATH=/oracle/ggs:$PATH
export SHLIB_PATH=/oracle/ggs:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32
3.安装
(1).解压到OGG目录
cd install_dir
unzip Oracle GoldenGate 11.2.1.0.1 for Oracle 10g on HPUX.zip
tar -xvf ggs_HPUX_ia64_ora10g_64bit.tar
(2).创建目录
$ ggsci
GGSCI (LEDTEST2) 1> create subdirs
Creating subdirectories under current directory /home/oracle
Parameter files /home/oracle/dirprm: created
Report files /home/oracle/dirrpt: created
Checkpoint files /home/oracle/dirchk: created
Process status files /home/oracle/dirpcs: created
SQL script files /home/oracle/dirsql: created
Database definitions files /home/oracle/dirdef: created
Extract data files /home/oracle/dirdat: created
Temporary files /home/oracle/dirtmp: created
Stdout files /home/oracle/dirout: created
四.OGG 运行环境准备
1.源端务必要打开归档模式,目标端可以不需要
2.源数据库打开补充日志
SQL> alter database add supplemental log data;
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG
----------------
YES
3.源端开启force_logging
SQL> alter database force logging;
SQL> select force_logging from v$database;
FORCE_
------
YES
4.关闭回收站功能(需要重启)
SQL> alter system set recyclebin=off scope=spfile;
SQL> show parameter recyclebin
5.源库和目标库网络通讯正常
配置/etc/hosts 和 TNS
6.创建专用的goldengate用户用来同步数据
(1).建立专用表空间
CREATE TABLESPACE ogg_tbs DATAFILE
'/oracle/product/10.2.0/oradata/test2/ogg01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
(2).创建用户并赋予权限
源库
create user goldengate identified by "goldengate1234" default tablespace ogg_tbs account unlock;
grant CONNECT to goldengate;
grant unlimited tablespace to goldengate;
grant FLASHBACK ANY TABLE to goldengate;
grant SELECT ANY DICTIONARY to goldengate;
grant SELECT ANY TABLE to goldengate;
grant alter any table to goldengate;
grant RESOURCE to goldengate;
grant execute on sys.dbms_flashback to goldengate;
grant execute on sys.utl_file to goldengate;
grant drop any table to goldengate;
grant alter any table to goldengate;
grant delete any table to goldengate;
grant ALTER SESSION to goldengate;
目标库
create user goldengate identified by "goldengate1234" default tablespace ogg_tbs account unlock;
grant CONNECT to goldengate;
grant unlimited tablespace to goldengate;
grant SELECT ANY DICTIONARY to goldengate;
grant SELECT ANY TABLE to goldengate;
grant alter any table to goldengate;
grant RESOURCE to goldengate;
grant execute on utl_file to goldengate;
grant delete any table to goldengate;
grant drop any table to goldengate;
grant alter any table to goldengate;
grant ALTER SESSION to goldengate;
grant lock any table to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
五.OGG源端配置
1.配置管理进程mgr
GGSCI (LEDTEST) 3> edit params mgr
port 7809 #指定端口
autostart er * #mgr启动时自动启动哪些进程
autorestart er *,waitminutes 3,retries 15 #自动重启策略
purgeoldextracts ./dirdat/*,usecheckpoints,minkeepdays 7 #自定义定期删除过期队列策略
2.配置检查点
GGSCI (LEDTEST) 4> edit params ./GLOBALS
checkpointtable goldengate.checkpoint
GGSCI (LEDTEST) 6> dblogin userid goldengate,password goldengate1234
Successfully logged into database.
GGSCI (LEDTEST) 7> add checkpointtable goldengate.checkpoint
Successfully created checkpoint table goldengate.checkpoint.
3.增加补充日志
GGSCI (LEDTEST) 9> ADD SCHEMATRANDATA SCHDEV
2018-05-11 16:26:08 ERROR OGG-01783 Cannot verify existence of table function that is required to enable schema level supplemental logging, failed to find function.
* 以上报错需要进行DB patch,参考:
DB Patches Needed to Support SCHEMATRANDATA in OGG (Oracle GoldenGate) (文档 ID 1426440.1)
GGSCI (LEDTEST) 13> ADD TRANDATA RPTDEV.*
2018-05-12 11:23:59 WARNING OGG-00869 No unique key is defined for table 'WP_CAPA_PROD_TYPE_REWORK_INFO'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table RPTDEV.WP_CAPA_PROD_TYPE_REWORK_INFO.
* 当表没有主键也没有唯一索引时,OGG会认为表的所有列当做KEY
4.启动mgr
GGSCI (LEDTEST) 7> start mgr
Manager started.
GGSCI (LEDTEST) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
5.在源端配置进程extract
命名规则
RPTDEV(同步schema): erptdev(extract),prptdev(pump),rrptdev(replicate)
(1).进程命名
RPTDEV: erptdev,prptdev,rrptdev
LXY:elxy,plxy,rlxy
(2).创建目录
mkdir -p ./dirdat/erptdev #存放队列
mkdir -p ./dirrpt/erptdev #存放报告
mkdir -p ./dirdat/elxy
mkdir -p ./dirrpt/elxy
(3).编辑参数文件
GGSCI (LEDTEST) 9> edit params erptdev
extract erptdev #不能超过8个字符
userid goldengate,password goldengate1234 #用户名密码
exttrail ./dirdat/erptdev/ex #指定写入到本地的哪个队列
tranlogoptions excludeuser goldengate #排除OGG用户
tranlogoptions convertucs2clobs #支持大字段,ogg12已废弃,默认支持大字段
warnlongtrans 12h,checkinterval 30m #长事务检查在ggserr.log中写入,每30min检查超过12h的事务
discardfile ./dirrpt/erptdev/erptdev.dsc,append,megabytes 200 #定义报错输出文件
TABLE RPTDEV.*; #同步对象
(4).设置环境变量
setenv(ORACLE_HOME="/oracle/ORA10/10.2.0")
setenv(ORACLE_SID="ORA10")
setenv(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
(5).添加抽取进程
GGSCI (LEDTEST) 10> add extract erptdev,tranlog,begin now
EXTRACT added.
(6).配置抽取队列文件
GGSCI (LEDTEST) 11> add exttrail ./dirdat/erptdev/ex,extract erptdev,megabytes 200
EXTTRAIL added.
(7).启动
GGSCI (LEDTEST) 12> start erptdev
Sending START request to MANAGER ...
EXTRACT ERPTDEV starting
GGSCI (LEDTEST) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ERPTDEV 00:04:42 00:00:03
6.在源端配置pump进程
(1).编辑参数文件
GGSCI (LEDTEST) 14> edit params prptdev
extract prptdev
userid goldengate,password goldengate1234
PASSTHRU #禁止extract与数据库交互,适合于pump传输进程
RMTHOST 109.115.101.61,MGRPORT 7809 #指定目标MGR信息
rmttrail ./dirdat/rrptdev/re #指定写入到远程目标端哪个队列
discardfile ./dirrpt/prptdev/prptdev.dsc,append,megabytes 200 #本地目录
TABLE RPTDEV.*;
(2).增加pump进程(指定本地trail文件)
GGSCI (LEDTEST) 15> add extract prptdev,exttrailsource ./dirdat/erptdev/ex
EXTRACT added.
(3).增加rmttail文件
GGSCI (LEDTEST) 17> add rmttrail ./dirdat/rrptdev/re,extract prptdev ,megabytes 200
RMTTRAIL added.
*创建pump完成后先不要启动,等目标端MGR启动后,再启动
GGSCI (LEDTEST) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ERPTDEV 00:00:00 00:00:09
EXTRACT STOPPED PRPTDEV 00:00:00 00:04:08
(4).创建目录
mkdir -p ./dirrpt/plxy
六.OGG目标端配置
1.准备
(1).创建目录
mkdir -p ./dirdat/rrptdev
mkdir -p ./dirrpt/rrptdev
mkdir -p ./dirdat/rlxy
mkdir -p ./dirrpt/rlxy
(2).创建同步用户专用TBS和user
CREATE TABLESPACE rptdev_ogg DATAFILE
'/oracle/product/10.2.0/oradata/test2/rptdev_ogg01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
CREATE TABLESPACE lxy_ogg DATAFILE
'/oracle/product/10.2.0/oradata/test2/lxy_ogg01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create user rptdev_ogg identified by rptdev1234 default tablespace rptdev_ogg account unlock;
create user lxy_ogg identified by lxy1234 default tablespace lxy_ogg account unlock;
grant dba to rptdev_ogg;
grant dba to lxy_ogg;
(3).同步表结构
expdp system/oracle directory=dump2 dumpfile=lxy_ogg.dmp logfile=lxy_ogg.log schemas=lxy content=metadata_only
impdp system/oracle directory=dump2 dumpfile=lxy_ogg.dmp REMAP_SCHEMA=LXY:LXY_OGG parfile=imp_test.txt
(4).disable 目标库所有的trigger,cascading delete,check,job
2.配置目标端OGG
(1).配置目标端mgr
GGSCI (LEDTEST2) 1> edit params mgr
port 7809
autostart er *
autorestart er *,waitminutes 3,retries 15
purgeoldextracts ./dirdat/*,usecheckpoints,minkeepdays 7
GGSCI (LEDTEST2) 10> start mgr
Manager started.
GGSCI (LEDTEST2) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
(2).配置检查点
GGSCI (LEDTEST2) 13> edit params ./GLOBALS
checkpointtable goldengate.checkpoint1
GGSCI (LEDTEST2) 14> dblogin userid goldengate,password goldengate1234
Successfully logged into database.
GGSCI (LEDTEST2) 15> add checkpointtable goldengate.checkpoint1
Successfully created checkpoint table goldengate.checkpoint1.
(3).配置复制进程
GGSCI (LEDTEST2) 16> edit params rrptdev
replicat rrptdev
userid goldengate ,password goldengate1234
handlecollisions #自动过滤重复时间内的数据冲突,用于不能停机执行初始化。
assumetargetdefs #两端数据结构一致才用这个数据
discardfile ./dirrpt/rrptdev/rrptdev01.dsc ,append,megabytes 200
map RPTDEV.*,target RPTDEV_OGG.*; #映射同步对象
(4).添加复制进程
add replicat rrptdev exttrail ./dirdat/rrptdev/re,checkpointtable goldengate.checkpoint1
(5).开启复制进程
GGSCI (LEDTEST2) 23> start rlxy
Sending START request to MANAGER ...
REPLICAT RLXY starting
GGSCI (LEDTEST2) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RLXY 00:00:00 00:00:04
REPLICAT STOPPED RRPTDEV 00:00:00 00:06:01
(6).启动源端的pump进程
(7).测试数据同步成功
* replicate report中发生警告
2018-05-15 08:37:45 WARNING OGG-03504 NLS_LANG character set ZHS16GBK on the target is different from the source database character set UTF8. Replicat
ion may not be valid if the source data has an incompatible character for the target NLS_LANG character set.
解决方法: 设置源库和目标库 NLS_LANG 为相同字符集
setenv(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
* 增加参数解决OGG向syslog.log写入大量日志的问题
edit params ./GLOBALS
SYSLOG {[ALL | NONE] | [, INFO] [, WARN] [, ERROR]}
七.OGG 配置DDL功能
*支持两个系统之间的复制,主备,双活,两边的对象结构相同,必须使用assumetargetdefs参数,较大数据库不建议开启此功能
1.OGG 支持的DDL复制的对象
cluster,functions,indexes,packages,procedures,roles,sequences,synonyms,tables,tablespaces,triggers,types,views,materialized views,users
2.同步范围
(1).mapped 对table,map指定的内容有效
(2).unmapped 对table,map指定的内容无效
(3).ddl include all 全部包含
3.配置(源端&目标端)
(1).用户的默认表空间不能是system
(2).关闭数据库的回收站,并清空
alter system set recyclebin=off scope=spfile;
purge dba_recyclebin;
(3).停止所有相关OGG进程
(4).修改权限
grant execute on UTL_FILE to goldengate;
grant restricted session to goldengate;
grant create table,create sequence to goldengate;
(5).指明支持DDL的对象存放在哪个schema下
GGSCI> edit params ./GLOBALS
GGSCHEMA goldengate
(6).执行相关脚本
cd ogg_install_dir
sqlplus / as sysdba
SQL>@marker_setup.sql
SQL>@ddl_setup.sql
SQL>@role_setup.sql
SQL>@ddl_enable.sql
SQL>@$ORACLE_HOME/rdbms/admin/dbmspool.sql
SQL>@ddl_pin.sql goldengate
(7).修改参数,增加DDL复制参数
GGSCI (LEDTEST) 53> edit params elxy
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT
GGSCI (LEDTEST2) 52> edit params rlxy
DDL INCLUDE ALL
DDLerror default ignore retryop
(8).启动相关进程
start rlxy
start elxy
start plxy
(9).DDL测试
SQL> create table GJBSHAOYE(id number primary key,name varchar2(20));
Table created.
SQL> insert into GJBSHAOYE values(1,'haha');
1 row created.
SQL> insert into GJBSHAOYE values(2,'jweio');
1 row created.
SQL> commit;
(10).DDL其他功能
开启与关闭
@ddl_disable.sql
@ddl_enable.sql
清空ddl trace文件 ggs_ddl_trace.log
@ddl_cleartrace.sql
ddl其他参数:
optype alert #复制ddl命令类型
objtype "table" #复制ddl对象类型
objname "user.tab*" #复制ddl操作对象名
include mapped object "*"; #包含对象
exclude mapped object "user.table_name" #排除对象
(11).DDL 环境重新配置
- 停止所有的OGG 进程
- @ddl_disable.sql
- @ddl_remove.sql
- @marker_remove.sql
- @marker_setup.sql
- 去掉参数中所有的ddl相关参数
- 开启所有OGG进程
八.配置密码安全与加密
OGG为了避免在参数文件,以及登录dblogin时,密码使用明文的方式。 因此提供了一些加密方法。
1.加密方式
密码加密
trail文件加密
TCP/IP网络加密
这里只建议使用密码加密,其他方式加密/解密的过程会造成性能浪费。
2.配置
(1).生成AES-256算法key
./keygen 256 1
0x2BFB241F6EFEA1447DB93063559FAEA618F3AD8A571914407DB6D74412A190E5
(2).新建文件ENCKEYS
OGG_KEY 0x2BFB241F6EFEA1447DB93063559FAEA618F3AD8A571914407DB6D74412A190E5
(3).GGSCI (LEDTEST) 7> encrypt password goldengate1234 aes256 encryptkey OGG_KEY
Encrypted password: AADAAAAAAAAAAAOACHRDECVHQEZAYECCSFJBGIWBNFTGRIDEZGEJBISDWIUHIJMCKFZAZAFDZIDJPCZGAIDCAEEDHJHACFUJUGWCIIZCUERBTCRF
Algorithm used: AES256
(4).测试dblogin
GGSCI (LEDTEST) 10> dblogin userid goldengate password AADAAAAAAAAAAAOACHRDECVHQEZAYECCSFJBGIWBNFTGRIDEZGEJBISDWIUHIJMCKFZAZAFDZIDJPCZGAIDCAEEDHJHACFUJUGWCIIZCUERBTCRF aes256 encryptkey OGG_KEY
Successfully logged into database.
(5).替换参数文件密码明文
userid goldengate,password AADAAAAAAAAAAAOACHRDECVHQEZAYECCSFJBGIWBNFTGRIDEZGEJBISDWIUHIJMCKFZAZAFDZIDJPCZGAIDCAEEDHJHACFUJUGWCIIZCUERBTCRF aes256 encryptkey OGG_KEY
(6).测试
点击(此处)折叠或打开
- 2018-05-16 11:26:18 INFO OGG-03035 Operating system character set identified as hp-roman8. Locale: en_US_POSIX, LC_ALL:.
- extract elxy
- userid goldengate,password **************************************************************************************************************** aes256 encry
- ptkey OGG_KEY
九.生产数据的初始化(OGG自带方法)
* 该方式建议数据库较小时使用。
1.停止所有OGG进程
2.源端配置初始化抽取进程
GGSCI (LEDTEST) 35> add extract einit,sourceistable
EXTRACT added.
#sourceistable声明为一个初始化进程
GGSCI (LEDTEST) 36> edit params einit
extract einit
userid goldengate , password goldengate1234
rmthost 109.115.101.61,mgrport 7809
rmtfile ./dirdat/initout/ld,maxfiles 500,megabytes 200,purge
tranlogoptions convertucs2clobs
table LXY.*;
3.在目标端配置同步进程
(1).创建目录
mkdir -p ./dirdat/initout
(2).添加同步进程
GGSCI (LEDTEST2) 4> add replicat rinit,specialrun
REPLICAT added.
#specialrun 声明是一个初始化同步进程
(3).配置参数文件
GGSCI (LEDTEST2) 5> edit params rinit
replicat rinit
specialrun
userid goldengate,password goldengate1234
assumetargetdefs
extfile ./dirdat/initout/ld
map LXY.*,target LXY_OGG.*;
4.只启动抽取进程即可
GGSCI (LEDTEST) 39> start einit
Sending START request to MANAGER ...
EXTRACT EINIT starting
报错1:
2018-05-16 15:39:05 ERROR OGG-01960 Failed to validate table LXY.DATA_TEMP_TRX_SPC. The table is created with the NOLOGGING option, which is not sup
ported. Extract may not be able to capture data from it.
solution:
(1).Add the following parameter after USERID parameter
DBOPTIONS ALLOWNOLOGGING
(2).Change the table/partition created with NOLOGGING option to LOGGING option and resync the source and target tables
查看状态:
GGSCI (LEDTEST) 56> info einit
EXTRACT EINIT Last Started 2018-05-16 16:16 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table LXY.H2
2018-05-16 16:17:46 Record 132221
Task SOURCEISTABLE
GGSCI (LEDTEST) 58> info einit detail
EXTRACT EINIT Last Started 2018-05-16 16:16 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table LXY.UNRECOVER
2018-05-16 16:19:02 Record 66605
Task SOURCEISTABLE
Extract Source Begin End
Database 2018-05-16 16:16 2018-05-16 16:19
Database * Initialized * First Record
Current directory /oracle/ggs
Report file /oracle/ggs/dirrpt/EINIT.rpt
Parameter file /oracle/ggs/dirprm/einit.prm
Checkpoint file /oracle/ggs/dirchk/EINIT.cpe
Process file /oracle/ggs/dirpcs/EINIT.pce
Stdout file /oracle/ggs/dirout/EINIT.out
Error log /oracle/ggs/ggserr.log
初始化完毕!
5.删除初始化进程
GGSCI (LEDTEST) 77> delete extract einit
GGSCI (LEDTEST2) 37> delete replicat rinit
十.项目验收测试
1.DML测试
(1).普通表
insert into GJBSHAOYE values(4,'GJB');
update GJBSHAOYE set name='666' where id=2;
delete from GJBSHAOYE where id=3;
commit;
(2).分区表
create table pp_test(
id number,
name varchar2(30),
age varchar2(10))
partition by range(age)
(
partition p1 values less than(20),
partition p2 values less than(40),
partition p3 values less than(60),
partition p_other values less than(maxvalue)
)
enable row movement;
测1:
insert into pp_test values(01,'111',18);
insert into pp_test values(02,'222',28);
insert into pp_test values(03,'333',58);
insert into pp_test values(04,'444',68);
commit;
测2:
update pp_test set age=59 where id=02;
commit;
测3:
delete from pp_test where id=02;
commit;
2.DDL测试
(1).创建普通表/分区表
(2).truncate table
truncate table gjbshaoye;
alter table pp_test truncate partition P2;
(3).drop table
drop table H1;
(4).column操作
alter table CHA_WQ add (tel varchar2(11));
alter table CHA_WQ rename column ALARMID to ALARMID2;
alter table CHA_WQ drop column IP_ADDRESS;
(5).constraints
alter table gjbshaoye add constraints c_id check (id>0);
alter table gjbshaoye modify constraints c_id novalidate;
alter table gjbshaoye drop constraints c_id;
(6).index
create index idx1 on gjbshaoye(name);
alter index idx1 rebuild tablespace users;
drop index idx1;
(7).view
(8).sequence
双向复制时不能使用sequence,单向情况下,使用序列cache的话,源和目标不同步
create sequence seq1 start with 0 increment by 1 minvalue 0 maxvalue 100;
select seq1.nextval id from dual;
select seq1.currval id from dual;
(9).function
(10).procedure
create procedure p_test is
begin
insert into GJBSHAOYE values(44,'fweffwegeg');
commit;
end;
OGG_KEY 0x2BFB241F6EFEA1447DB93063559FAEA618F3AD8A571914407DB6D74412A190E5
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15412087/viewspace-2154645/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15412087/viewspace-2154645/