Ogg安装配置
安装ogg(安装前确保/ogg目录为空)
前期配置KEYCOLS
1、修改用户环境变量，将以下行加入到.bash_profile文件下：
export OGG_HOME=/data/ogg
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
2、在源和目标库上给OGG分配用户，并执行OGG的配置脚本。
1.源和目标库创建表空间、用户的创建，用户权限的授予。
create tablespace ts_ogg datafile ‘/data/ogg/ogg_01.dbf’ size 100M autoextend on next 8M;
create user oggadmin identified by oggadmin default tablespace ts_ogg quota unlimited on ts_ogg;
2.源库上授权
grant connect,resource, CREATE SESSION to oggadmin ;
exec dbms_goldengate_auth.grant_admin_privilege(‘oggadmin’) ;
Grant select any transaction to oggadmin ;
grant execute on utl_file to oggadmin ;
GRANT SELECT ANY DICTIONARY to oggadmin;
GRANT SELECT any table TO oggadmin;
grant connect, resource, unlimited tablespace to oggadmin;
grant connect,resource to oggadmin;
grant select any dictionary,select any table to oggadmin;
grant alter any table to oggadmin;
grant flashback any table to oggadmin;
grant execute on DBMS_FLAHBACK TO oggadmin;
3.目标库授权：
grant connect, resource, unlimited tablespace to oggadmin;
grant execute on utl_file to oggadmin ;
grant connect,resource, CREATE SESSION to oggadmin ;
exec dbms_goldengate_auth.grant_admin_privilege(‘oggadmin’) ;
grant CREATE TABLE, INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE to oggadmin ;
GRANT SELECT any table TO oggadmin ;
GRANT SELECT ANY DICTIONARY to oggadmin;
grant insert any table to oggadmin;
grant delete any table to oggadmin;
grant update any table to oggadmin;
3、源和目标执行OGG的配置脚本(启用ddl同步，如果不需要ddl可跳过本步骤), 在出现输入提示的时候，输入oggadmin用户。
cd /data/ogg# sqlplus / as sysdba
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to oggadmin;
SQL> @ddl_enable.sql
4、安装性能优化工具 (可选项)
SQL>@?/rdbms/admin/dbmspool.sql
SQL> @ddl_pin oggadmin
5、在源和目标库上设置enable_goldengate_replication初始化参数为true。
alter system set enable_goldengate_replication = true scope=both ;
alter system set recyclebin=off scope=spfile ;
6、在源库上启用归档，强制日志与补充日志数据。
SQL> shutdown immediate ;
SQL> startup mount ;
SQL> alter database archivelog ;
SQL> alter database open ;
SQL> alter database force logging;
SQL> alter database add supplemental log data; --最小附加日志
alter table
SQL> – alter database add supplemental log data (primary key) columns ; 最好在表级设置
SQL> select log_mode,force_logging,supplemental_log_data_min from v$database;
输出结果如下：
LOG_MODE FOR SUPPLEME
ARCHIVELOG YES YES
查看是否是force logging模式：
select force_logging from v$database;
7、切换日志：
alter system switch logfile;
源库上OGG的配置
（1）配置mgr进程
cd /data/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
./ggsci
GGSCI (ygmeshis.o-film.com.cn) 1> create subdirs
GGSCI (ygmesdb.o-film.com.cn) 2> edit params mgr
PORT 7809
autostart extract *
AUTORESTART EXTRACT ,RETRIES 3, WAITMINUTES 3
PURGEOLDEXTRACTS /tmp2/dirdat/,usecheckpoints, minkeepdays 1 ----ext数据保留1天
GGSCI (ygmesdb.o-film.com.cn) 3>stop mgr
GGSCI (ygmesdb.o-film.com.cn) 1>start mgr
Manager is running (IP port ygmesdb.o-film.com.cn.7809, Process ID 18833).
（2）配置extract进程（每个table最后要加;）。
GGSCI (ygmesdb.o-film.com.cn) 10> edit params ext1
extract ext1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid oggadmin,password oggadmin
tranlogoptions EXCLUDEUSER oggadminexttrail /data/ogg/dirdat/et --本地队列文件生成位置
ddl include all
table MESWIP1.;
–也可配置以下参数，针对dml同步个性需求
GETUPDATES|IGNOREUPDATES：
是否复制UPDATE操作，缺省复制
GETDELETES|IGNOREDELETES：
是否复制DELETE操作，缺省复制
GETINSERTS|IGNOREINSERTS：
是否复制INSERT操作，缺省复制
----------添加以下参数DBLOGREADER直接访问asm库（10.2.0.5及11.2.0.2以上版本）
TRANLOGOPTIONS BUFSIZE 1024000
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 1024000
GGSCI (ygmesdb.o-film.com.cn) 6> add extract ext1,tranlog,begin now-单节点步骤，如果添加失败，需要先登陆数据库dblogin
userid oggadmin,password oggadmin
EXTRACT added.
查看表的同步信息：
GGSCI (ygmesdb.o-film.com.cn) 6> info trandata sh.
GGSCI (ygmesdb.o-film.com.cn) 6> add extract ext1,tranlog, THREADS 2,begin now,TRANLOG, THREADS 2 --rac步骤增加THREADS
参数，后面跟数据库实例个数。
EXTRACT added.
GGSCI (ygmesdb.o-film.com.cn) 8> add exttrail /data/ogg/dirdat/et,extract ext1
EXTTRAIL added.
GGSCI (ygmesdb.o-film.com.cn) 9> add rmttrail /data/ogg/dirdat/et,extract ext1 --配置pump进程跳过
TARGETEXTTRAIL already exists.
GGSCI (ygmesdb.o-film.com.cn) 11> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
(3)配置datapump进程：
GGSCI> edit param pump1
extract pump1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid oggadmin,password oggadmin
rmthost 192.168.242.53,mgrport 7809
rmttrail /data/ogg/dirdat/rp
PASSTHRU|NOPASSTHRU 禁止extract进程与数据库交互，PASSTHRU模式适用于data pump传输进程
gettruncates – 是否复制TRUNCATE操作，缺省不复制；
table MESWIP1.*;
直通模式用在两边表名、列名一致，可以直接映射的情况，不需要额外配置；普通模式可以配置表名列名自定义映射，可以加FILTER、transformation等，需要
配置一个数据定义文件（data-definitions file）。
GGSCI> ADD EXTRACT pump1, EXTTRAILSOURCE /data/ogg/dirdat/et , BEGIN now --用add extract指定本地trail文件
然后修改原先为提取进程配置远端队列位置：
GGSCI> delete rmttrail /data/ogg/dirdat/et extract ext1
GGSCI> add rmttrail /data/ogg/dirdat/rp extract pump1 --用add rmttrail指定远端trail文件
GGSCI (ygmesdb.o-film.com.cn) 11> start extract pump1
GGSCI (ygmesdb.o-film.com.cn) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:03
（3）增加所需同步的表或用户。
dblogin userid oggadmin,password oggadmin
add trandata MM_PS.T_MCOUPONSINFO_HIS ----取消 delete trandata .*
add trandata coss3.per_test,nokey,cols(sampletime, objectid) --无主键指定字段补全的示例— ADD SCHEMATRANDATA
Info trandata MM_PS.T_MCOUPONSINFO_HIS --查看表同步信息
目标库的配置
当ogg系统目录与文件目录不在一个根目录下，用于指定文件存放的目录为/data1/dirdat。
EDIT PARAMS ./GLOBALS
ALLOWOUTPUTDIR /data1/dirdat
（1）mgr进程的配置。
cd /data/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
./ggsci
GGSCI (ygmeshis.o-film.com.cn) 1> create subdirs
GGSCI (ygmeshis.o-film.com.cn) 1> edit params mgr
PORT 7809
dynamicportlist 7809-7819
AUTOSTART REPLICAT *
AUTORESTART REPLICAT , WAITMINUTES 2, RETRIES 3
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 5------------文件保留5天
GGSCI (ygmeshis.o-film.com.cn) 2> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER …
Request processed.
Manager stopped.
GGSCI (ygmeshis.o-film.com.cn) 3> start mgr
Manager started.
（2）replicat 进程的配置。
GGSCI (ygmeshis.o-film.com.cn) 4> edit params ./GLOBALS
GGSCHEMA oggadmin
CHECKPOINTTABLE oggadmin.checkpoint
GGSCI (ygmeshis.o-film.com.cn) 5> dblogin userid oggadmin,password oggadmin
Successfully logged into database.
GGSCI (ygmeshis.o-film.com.cn as oggadmin@YGHIS) 7> add checkpointtable oggadmin.checkpoint
Successfully created checkpoint table oggadmin.checkpoint.
GGSCI (ygmeshis.o-film.com.cn as oggadmin@YGHIS) 9>edit params rep1
–Replicat group –
replicat rep1
–source and target definitions
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
– 两端数据结构一致使用此参数；
HANDLECOLLISIONS
–忽略处理冲突参数，源端extract要配置FETCHOPTIONS FETCHPKUPDATECOLS配合使用
–target database login –
userid oggadmin, password oggadmin
–file for dicarded transaction –
discardfile /data/ogg/rep1_discard.txt, append, megabytes 10
–ddl support DDL
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP ------设置过滤、忽略DDL的错误
–Specify table mapping —map MESWIP1., target WIPHIS1.;
map MESWIP1.SWIP_ENTITY_TRX, target MESWIP1.SWIP_ENTITY_TRX,KEYCOLS(trx_key);–如果该表没有主键，则通过KEYCOLS关键字指定
一个列作为伪主键列，从而提高性能。
GGSCI (ygmeshis.o-film.com.cn as oggadmin@YGHIS) 8>add replicat rep1 exttrail /data/ogg/dirdat/rp,checkpointtable
oggadmin.checkpoint
REPLICAT added.
alter session set container=CDB$ROOT;
----------19C目标数据库设置------------------
ORA12CR2PDB创建相关表
创建管理用户
create user oggadmin identified by oggadmin;
赋予相关权限
赋予
grant set container to oggadmin container=all;
grant dba to oggadmin container=all;
alter system set enable_goldengate_replication=true;
exec dbms_goldengate_auth.grant_admin_privilege(‘oggadmin’,‘APPLY’,container=>‘CCMQIS’);
查看权限
select privilege from dba_sys_privs where grantee = ‘oggadmin’;
配置pdb连接
在tns加入
CCMQIS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.33)(PORT = 1521))
) (
CONNECT_DATA =
(SERVICE_NAME = CCMQIS)
)
)
配置mgr管理进程
mgr进程的配置。
cd /ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
./ggsci
GGSCI (ygmeshis.o-film.com.cn) 1> create subdirs
GGSCI (ygmeshis.o-film.com.cn) 1> edit params mgr
PORT 7809
dynamicportlist 7809-7819
AUTOSTART REPLICAT *
AUTORESTART REPLICAT , WAITMINUTES 2, RETRIES 3
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 5------------文件保留5天
GGSCI (ygmeshis.o-film.com.cn) 2> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER …Request processed.
Manager stopped.
GGSCI (ygmeshis.o-film.com.cn) 3> start mgr
Manager started.
（2）replicat 进程的配置。
GGSCI (ygmeshis.o-film.com.cn) 4> edit params ./GLOBALS
GGSCHEMA oggadmin
CHECKPOINTTABLE oggadmin.checkpoint
创新检查点表
GGSCI (prod) 11> dblogin userid oggadmin@CCMQIS password oggadmin
GGSCI (ygmeshis.o-film.com.cn as oggadmin@YGHIS) 7> add checkpointtable oggadmin.checkpoint
add replicat rep1, exttrail /ogg/dirdat/rp,begin now, checkpointtable oggadmin.checkpoint
edit params rep1
REPLICAT rep1
SETENV (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
SETENV(ORACLE_SID=‘CCMQIS’)
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID oggadmin@CCMQIS, password oggadmin
discardfile /ogg/rep1_discard.txt, append, megabytes 1024
ASSUMETARGETDEFS
HANDLECOLLISIONS
tableexclude OFG_QIS.SUPPLIER_SUPPLIER_ADMIT;
MAP OFG_QIS.temp1, TARGET OFG_QIS.temp1;
开启相关
GGSCI (prod) 11>start *
查看状态
GGSCI (prod) 11>info all
----------------OGG配置完-----------------
初始化目标数据库
源数据导出:
mkdir /home/oracle/dmpfile
create directory expdir as ‘/home/oracle/dmpfile’;
grant read,write on directory expdir to public;
select dbms_flashback.get_system_change_number from dual; --或者 select current_scn from v$database;
expdp system/passwd schemas=meswip1 directory=expdir dumpfile=meswip1_0323.dmp logfile=meswip1_0323.log
flashback_scn=4593281
目标数据库导入
mkdir /home/oracle/dmpfile
create directory expdir as ‘/home/oracle/dmpfile’;
grant read,write on directory expdir to public;
impdp system/passwd directory=expdir dumpfile=meswip1_0323.dmp logfile=meswip1_imp_0323.log schemas=meswip1
REMAP_TABLESPACE=TS_BRMS_D:TS_REPX_D,TS_BRMX_D:TS_REPX_D,TS_WIPS_D:TS_REPX_D,TS_WIPX_D:TS_REPX_D,TS_BRMS_I:TS_REPX_I,TS_BRMX_I:TS_REPX_
创建相关表唯一索引，
目标库在impdp导入之后rep1状态为ABENDED状态(或者导入前将rep1进行停止掉)：
GGSCI (sdy as oggadmin@sdy) 205> info all
Program Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
REPLICAT ABENDED REP1 00:00:05 00:00:36
在目标库上启动replicat进程进行重新同步，防止出现数据丢失。
GGSCI (ygmeshis.o-film.com.cn) 1> start replicat rep1,aftercsn 1055880
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (ygmeshis.o-film.com.cn) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:08
失效参数HANDLECOLLISIONS
在初始化数据后，建议注释HANDLECOLLISIONS参数，并重启rep进程，防止数据丢失。
GGSCI (ygmeshis.o-film.com.cn) 1> send rep1, NOHANDLECOLLISIONS
GGSCI (ygmeshis.o-film.com.cn) 1> edit param rep1
–HANDLECOLLISIONS
GGSCI (ygmeshis.o-film.com.cn) 1> start rep1
测试ddl同步：
源端：
create table MESWIP1.test0323 (id number primary key,att1 varchar2(30)) ;
insert into MESWIP1.test0323 values(1,‘afdf’);
insert into MESWIP1.test0323 values(2,‘rgh’);
目标端：
SQL> select * from MESWIP1.test0323 ;
ID ATT1
1 afdf
2 rgh
删除日志设置
在源端和目标端修改mgr配置添加行：
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 5
源端和目标端的日志前缀最好不一样，否则容易出现有一边不能删除的情况。
问题处理
1、extract进程报错Virtual memory allocation error
参考oracle文档1321902.1，修改以下两个参数后重启extract服务即可。
修改系统参数sysctl w vm.max_map_count=131060
及修改extract参数cachemgr cachesize 2GB
重置/重启extract进程
alter extract ext1 begin now
alter extract pump1 begin now
start ext1
start pump1
2、修改rep进程、pump进程、ext进程后，需要重置/重新使用进程，例如重置rep进程：
dblogin userid oggadmin,password oggadmin
delete extract ext11,tranlog,begin now
add extract ext11,tranlog,begin now
add exttrail /tmp2/dirdat/eb,extract ext11
---------------delete replicat rep1 exttrail /data/ogg/dirdat/rd,checkpointtable oggadmin.checkpoint
delete checkpointtable oggadmin.checkpoint
add checkpointtable oggadmin.checkpoint
add replicat rep1 exttrail /data/ogg/dirdat/rd,checkpointtable oggadmin.checkpoint
3、数据丢失导致rep进程停下：
目标端错误日志如下：
2018-12-22 11:26:51 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep7.prm: Repositioning to rba
494214024 in seqno 396.
2018-12-22 11:26:51 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep7.prm: SQL error 1403 mapping
PO.PO_DISTRIBUTIONS_ALL to OGG.PO_DISTRIBUTIONS_ALL OCI Error ORA-01403: no data found, SQL <UPDATE
“OGG”.“PO_DISTRIBUTIONS_ALL” x SET x.“LAST_UPDATE_DATE” = :a10,x.“LAST_UPDATED_BY” = :a11,x.“LAST_UPDATE_LOGIN” =
:a12,x.“QUANTITY_DELIVERED” = :a13,x.“REQUEST_ID” = :a14,x.“PROGRAM_APPLICATION_ID” = :a15,x.“PROGRAM_ID” =
:a16,x.“PROGRAM_UPDATE_DATE” = :a17 WHERE x.“PO_DISTRIBUTION_ID” = :b0>.
2018-12-22 11:26:51 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep7.prm: Error mapping from
PO.PO_DISTRIBUTIONS_ALL to OGG.PO_DISTRIBUTIONS_ALL.
2018-12-22 11:26:51 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep7.prm: PROCESS ABENDING.
查看文件rep7_discard.txt
有问题的行在PO_DISTRIBUTION_ID = 1784425
insert into ogg.PO_DISTRIBUTIONS_ALL select * from po.PO_DISTRIBUTIONS_ALL@OF_ERP where PO_DISTRIBUTION_ID=‘1784425’;
手工插入丢失的行记录，重启rep7进程，恢复正常。
Ext进程报错OGG01028
20190220 11:27:39 ERROR OGG01028 Oracle GoldenGate Capture for Oracle, ext3.prm: partial record at
sequence 49312, block 1898744 block byte 0, rba 972156928.
参考2159577.1，在ext进程添加参数
TRANLOGOPTIONS _ENABLESTREAMLINEDDBLOGREADER
新增同步表
1、停止抽取进程、传输进程、复制进程,需要在目标端mgr配置中注销自动启动REPLICAT，防止配置过程中启动REPLICAT导致不一致情况。
stop e
stop p*
stop r*
2、修改抽取进程、传输进程配置，新增表同步
extract:
table MM_PS.T_MCOUPONSINFO_HIS;
pump:
table MM_PS.T_MCOUPONSINFO_HIS;
3、源端增加表附加日志，并启动抽取、传输进程
dblogin userid oggadmin,password oggadmin
add trandata MM_PS.T_MCOUPONSINFO_HIS
add trandata coss3.per_test,nokey,cols(sampletime, objectid) --无主键指定字段补全的示例
alter table
start e*
start p*
4、源端数据库获取当前最新scn（如果表不大且变化量小，可通过dblink方式远程创建保障主备数据一致，创建完后执行8、9步骤）
select current_scn from v$database;
10930121916224
5、源端数据库按照指定的scn导出表数据
exp mmdb03/*** direct=y rows=y buffer=64000000 flashback_scn=10930121916224 constraints=n grants=n triggers=n
STATISTICS=none file=/ogghome/oggexp/ogg.dmp log=/ogghome/oggexp/ogg.log tables=MM_PS.T_MCOUPONSINFO_HIS
6、将导出的数据导入到目标端数据库
imp mmdb03/*** file=/ogghome/oggexp/ogg.dmp log=/ogghome/oggexp/ogg.log full=y ignore=y buffer=640000007、目标端数据库禁掉新增表上的trigger与有级联删除的约束
select owner,table_name,TRIGGER_NAME from dba_triggers where table_name =‘T_MCOUPONSINFO_HIS’;
8、修改复制进程配置，增加复制表map关系，用filter指定复制scn(通过dblink方式可不指定scn)
map BOM.CST_STANDARD_COSTS , target ogg.CST_STANDARD_COSTS , filter ( @getenv(‘TRANSACTION’, ‘CSN’) >
12594684281625);
–map MESWIP1.CWIP_EOL_FAI_CONFIG, target WIPHIS1.CWIP_EOL_FAI_CONFIG,filter (@G
ETENV (“transaction”, “csn”) > 12584853331903);
–对于这条语句，如果使用的11g的ogg，需要使用双引号"transaction"，“csn”，如果使用的是12c的ogg使用单引号
–否则报错：OGG-01298 Oracle GoldenGate Delivery for Oracle, rep_t1.prm: Column function diagnostic
message: could not find column “transaction”.
9、启动复制进程
start r*
10、检查表数据同步情况
11、查看运行报告：
view report <进程名称>
12、删除repliact进程filter选项内容
当replicat进程将追加部分的数据开始同步并与源库持平以后,将replicat进程中的filter clause删除并重启replicat进程
重置入库进程，重新从0号trail文件开始读取：
alter replicat RJ2_SDLT,extseqno 0,extrba 0
重置入库进程，从新从某个scn号开始读取：
start replicat rep1,aftercsn 1055880
重置抽取进程，本地文件序列号从0开始生成：
alter extract EJ2_CQLT, extseqno 0,extrba 0
1、通过dblink创建表：
CREATE TABLE table_to AS SELECT * FROM table_sorc@HRUSER;
2、向表中的字段插入数据：
INSERT INTO table_name (column1,column2…) VALUES(value1,value2,…)
3、将表中的列删除值：
delete from表名 where 字段=某值
查看MESWIP1用户下哪些表没有开启附加日志：
select table_name from dba_tables where owner = ‘MESWIP1’
minus
select distinct
g.table_name table_name
from dba_log_groups g, dba_log_group_columns c
where g.log_group_name = c.log_group_name(+)
and g.table_name = c.table_name(+)
–and g.table_name in
and g.owner = ‘MESWIP1’;
针对分区表数据重新同步步骤：
–1、查看哪个分区的数据不一致：
select count(*) from MES.CWIP_FPC_SN_LOG partition(TRX202004)
–2、truncate表中的某个分区：
alter table MES.CWIP_FPC_SN_LOG truncate partition TRX202004;
–3、如果分区表中有全局索引需要先重建全局索引：
alter index CCMMESPRD.WIP_COMPONENTUNIT_HIS rebuild parallel 16;
alter index CCMMESPRD.WIP_COMPONENTUNIT_HIS noparallel;–4、基于scn和时间向数据不一致的分区插入数据：
insert into MES.CWIP_FPC_SN_LOG select * from MES.CWIP_FPC_SN_LOG@TO_FPM as of scn ‘12745756650377’ where TRX_DATE >=
to_date(‘2020-04-01 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
–5、关闭并行：
alter index INDEX_NAME noparallel;
