实施环境 RHEL6、Oracle11g、GoldenGate11.2
前提安装好rhel6和数据库oracle 11g,以及goldengate11.2
1.环境变量检查
export LD_LIBRARY_PATH=/home/oracle/ogg/:$ORACLE_HOME/lib:/usr/lib:$LD_LIBRARY_PATH
(hp_linux环境变量
export LIBPATH=/arcbak/goldengate:$ORACLE_HOME/lib:/usr/lib:$LIBPATH
)
2.创建表空间及用户
查询是否存在OGG用户及表空间
select username,default_tablespace from DBA_USERS;
查看表空间安置的目录
select tablespace_name,file_name,bytes,status from dba_data_files order by tablespace_name,file_name
示例
create tablespace goldengate_tbs datafile '/u01/oracle/oradata/orcl/ogg.dbf' size 50m;
create tablespace goldengate_tbs datafile '/u01/oracle/oradata/orcl2/ogg.dbf' size 50m;
创建用户
CREATE USER goldengate IDENTIFIED BY goldengate DEFAULT TABLESPACE goldengate_tbs;
赋予用户权限
GRANT CONNECT TO goldengate;
GRANT ALTER ANY TABLE TO goldengate;
GRANT ALTER SESSION TO goldengate;
GRANT CREATE SESSION TO goldengate;
GRANT FLASHBACK ANY TABLE TO goldengate;
GRANT SELECT ANY DICTIONARY TO goldengate;
GRANT SELECT ANY TABLE TO goldengate;
GRANT RESOURCE TO goldengate;
GRANT drop ANY TABLE TO goldengate;
GRANT SYSDBA TO goldengate;
GRANT DBA TO goldengate;
3.查询当前字符集参数
SHOW PARAMETER NLS_LANGUAGE
SHOW PARAMETER NLS_TERRITORY
select * from NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
根据查询记录当前参数为
AMERICAN.AMERICAN_ZHS16GBK
4.日志
archive log list;
如果非归档状态则开启
startup mount
alter database archivelog;
alter database open;
开启附加日志
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
ALTER DATABASE FORCE LOGGING;
create subdirs
edit param ./GLOBALS
5.指定ogg复制的用户
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.ggschkpt
dblogin userid goldengate, password goldengate
add checkpointtable goldengate.ggschkpt
6.执行脚本
源端操作
SQL>@marker_setup.sql
Enter GoldenGate schema name: goldengate
SQL> @ddl_setup.sql
Enter GoldenGate schema name: goldengate
SQL> @role_setup.sql
生成用户角色，将该角色授予GoldenGate所有抽取、复制进程中登陆数据库的用户，在这里将该角色授予goldengate数据库用户。
GRANT GGS_GGSUSER_ROLE TO goldengate;
SQL>@ddl_enable.sql
7.创建测试用户和表
create user ogg_test identified by ogg_test;
grant dba to ogg_test;
创建测试表
create table table_name
(
column1 NUMBER primary key,
column2 VARCHAR2(4000),
column3 VARCHAR2(2) default 0);
create table table_name_2
(
column1 NUMBER ,
column2 VARCHAR2(4000),
column3 VARCHAR2(2) default 0);
8.配置OGG_DDL
源端操作
ＧＧＳＣＩ〉dblogin userid goldengate,password goldengate
ＧＧＳＣＩ〉add trandata ogg_test.table_name
add trandata ogg_test.table_name_2
______________________________________________
GGSCI>edit param mgr
源端
port 7809
DYNAMICPORTLIST 7810-7839
--autostart er *
autorestart extract *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/* USECHECKPOINTS, MINKEEPDAYS 7
userid goldengate, password goldengate
purgeddlhistory minkeepdays 7, maxkeepdays 7
purgemarkerhistory minkeepdays 7, maxkeepdays 7
目标端
port 7809
DYNAMICPORTLIST 7810-7839
--autostart er *
autorestart extract *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/* USECHECKPOINTS, MINKEEPDAYS 7
--userid goldengate, password goldengate
--purgeddlhistory minkeepdays 7, maxkeepdays 7
--purgemarkerhistory minkeepdays 7, maxkeepdays 7
启动
start mgr
Manager started.
GGSCI (ogg.local) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
________________________________________________
源端
添加extfs进程，从数据库日志中抓取有效数据
RAC执行
GGSCI>add ext extfs, tranlog, begin now,threads 2
单机执行
add ext extfs, tranlog, begin now
添加本地队列fs, extfs进程捕捉到的有效数据将写入本地队列
GGSCI>add exttrail ./dirdat/fs, ext extfs, megabytes 500
（500=500M,fs自定义）
为extfs进程配置参数
GGSCI>edit param extfs
extract extfs
setenv (ORACLE_SID="当前环境oracle_sid")
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
userid goldengate, password goldengate
REPORT AT 01:59
reportrollover at 02:00
TRANLOGOPTIONS CONVERTUCS2CLOBS
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 9000 IOLATENCY 3000
--FETCHOPTIONS NOUSESNAPSHOT
--DBOPTIONS ALLOWUNUSEDCOLUMN
--GETUPDATEBEFORES
FETCHOPTIONS FETCHPKUPDATECOLS
--tranlogoptions altarchivelogdest primary instance ora9i2 /arc2, altarchivelogdest instance ora9i1 /arc1
discardfile ./dirrpt/extfs.dsc,append,megabytes 10
numfiles 6000
dynamicresolution
DDL INCLUDE mapped
ddloptions nocrossrename,report,addtrandata
warnlongtrans 2h, checkinterval 3m
exttrail ./dirdat/fs
--table list
TABLE ogg_test.*;
(--为RAC系统使用)
配置dpefs进程
添加dpefs进程，将本地队列的数据抽取出来
GGSCI>add ext dpefs, exttrailsource ./dirdat/fs
添加远端队列，将dpe进程捕捉到的数据将写入该队列
GGSCI>add rmttrail ./dirdat/fs, ext dpefs, megabytes 500
为dpe进程配置参数
（500=500m）
GGSCI>edit param dpefs
extract dpefs
setenv (ORACLE_SID="当前环境oracle_sid")
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
passthru
REPORT AT 01:59
reportrollover at 02:00
RMTHOSTOPTIONS TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
rmthost 192.168.154.132,mgrport 7809
rmttrail ./dirdat/fs
dynamicresolution
numfiles 3000
--table list
TABLE ogg_test.*;
--ddl table
table GOLDENGATE.GGS_MARKER;
____________________________________________
目标端
为生产中心传递过来的数据添加replicat进程
Add replicat repfs,exttrail ./dirdat/fs
为repfs进程配置参数文件如下
GGSCI>edit param repfs
replicat repfs
setenv (ORACLE_SID="当前环境oracle_sid ")
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
userid goldengate, password goldengate
sqlexec "Alter session set constraints=deferred"
REPORT AT 01:59
reportrollover at 02:00
ALLOWNOOPUPDATES
APPLYNOOPUPDATES
--handlecollisions
reperror default,abend
DDLERROR DEFAULT abend
--DDLSUBST 'enable' WITH 'disable' INCLUDE OBJTYPE 'trigger', OPTYPE alter
--ddloptions nocrossrename,report
discardfile ./dirrpt/repfs.dsc,append, megabytes 10
assumetargetdefs
--checksequencevalue
allownoopupdates
dynamicresolution
numfiles 6000
map ogg_test.* , target ogg_test.*;
9.源端插入测试数据，目标端查询
insert into table_name values (1,'abc','a');
update table_name set column2='ddd' where column1=1;
delete from table_name where column1=1;
欢迎关注我的公众号 扫描二维码或公众号搜索 “我的工作”
