Oracle Ogg 11 12 18 版本配置测试

-- Ogg 18 11 12 18 版本配置测试

-- 准备工作

create tablespace ogg logging datafile '/u01/app/oracle/oradata/orcl/ogg.dbf' size 20m autoextend on uniform size 2m;

create tablespace ogg logging datafile '+DATA' size 20m autoextend on uniform size 2m;

create user ogg identified by ogg default tablespace ogg temporary tablespace temp quota unlimited on ogg;

grant unlimited tablespace to ogg;

grant connect, resource, dba to ogg;

grant create session, alter session to ogg;

grant alter system to ogg;

grant select any dictionary to ogg;

grant flashback any table to ogg;

grant select any table, insert any table, update any table, delete any table, drop any table to ogg;

grant create table, create sequence to ogg;

grant select on dba_clusters to ogg;

grant select on v_$database to ogg;

grant select on sys.logmnr_buildlog to ogg;

grant select any transaction to ogg;

grant lock any table to ogg;

grant execute on dbms_flashback to ogg;

grant execute on dbms_logmnr_d to ogg;

grant execute on dbms_capture_adm to ogg;

grant execute on dbms_streams to ogg;

grant execute on utl_file to ogg;

exec dbms_streams_auth.grant_admin_privilege('ogg');

-- 开启归档模式、附加日志和强制日志模式

select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui from v$database;

select log_mode, supplemental_log_data_min, force_logging from v$database;

alter system set enable_goldengate_replication=true scope=both;

alter system set log_archive_dest_1='location=/arch2/archivelog_1';

shutdown immediate;            

startup mount;            

alter database archivelog;                   

alter database open;  

                  

alter database force logging;                

alter database add supplemental log data;

alter database drop supplemental log data;

select table_name,logging from dba_tables where table_name='tablename';

-- 关闭回收站

show parameter recyclebin

alter system set recyclebin=off scope=spfile;

alter session set recyclebin=off;

$ cd $OGG_HOME

SQL> @marker_setup

SQL> @ddl_setup

SQL> @role_setup

SQL> grant ggs_ggsuser_role to ogg;

SQL> @ddl_enable

SQL> @ddl_disable.sql

SQL> @ddl_status.sql -- 查看状态

SQL> @ddl_remove.sql

SQL> @marker_remove.sql

SQL> @marker_status -- 验证脚本安装

SQL> @?/rdbms/admin/dbmspool.sql

SQL> @ddl_pin.sql ogg;

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 配置模式为用户级别的附加日志

$ ggsci

GGSCI (dbsrc) 1> dblogin userid ogg, password ogg

GGSCI (dbsrc as ogg@dbmonitor) 2> add schematrandata ahern

-- 表级别的附加日志 源端配置即可

$ ggsci

GGSCI (dbsrc) 1> dblogin userid ogg, password ogg

GGSCI (dbsrc as ogg@dbmonitor) 2>  add trandata ahern.test_oggsync

GGSCI (dbsrc as ogg@dbmonitor) 2>  info trandata ahern.test_oggsync

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 配置Manager进程(源端目标端都需要配置管理进程)

GGSCI (dbsrc) 1> create subdirs

$ ggsci

GGSCI (dbsrc) 1> info mgr

-- 源端：

GGSCI (dbsrc) 2> edit param mgr

PORT 7809

DYNAMICPORTLIST  7810-7850

AUTOSTART EXTRACT *

AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 2

PURGEOLDEXTRACTS /u01/app/ogg/dirdat/ *, USECHECKPOINTS, MINKEEPDAYS 5

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

ACCESSRULE, PROG *, IPADDR 192.168.227.*, ALLOW

-- ACCESSRULE, PROG *, IPADDR 192.168.125.*, ALLOW -- (12c 新特性)

-- 目标端：

GGSCI (dbtrg) 1> edit param mgr

PORT 7809

DYNAMICPORTLIST  7810-7850

AUTOSTART REPLICAT *

AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 2

USERID ogg, PASSWORD "ogg"

PURGEOLDEXTRACTS /u01/app/ogg/dirdat/ *, USECHECKPOINTS, MINKEEPDAYS 10

PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

ACCESSRULE, PROG *, IPADDR 192.168.227.*, ALLOW

-- 添加检查点(源端和目标端配置OGG的检查点)

GGSCI (dbtrg) 1> dblogin userid ogg, password ogg

GGSCI (dbtrg as ogg@dbmonitor) 2> add checkpointtable ogg.checkpoint

GGSCI (dbtrg as ogg@dbmonitor) 3> edit param /u01/app/ogg/GLOBALS

GGSCHEMA ogg

CHECKPOINTTABLE ogg.checkpoint

-- 附

PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYHOURS 30

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYHOURS 30

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 数据初始化配置

-- 方式一

dblogin userid ogg, password ogg

-- 源端：

$ ./ggsci

GGSCI (db) 1> start mgr

GGSCI (db) 2> info mgr

GGSCI (db) 3> add extract sr_init,sourceistable

GGSCI (db) 4> edit params sr_init

EXTRACT sr_init

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

RMTHOST 192.168.227.122, MGRPORT 7809

RMTTASK REPLICAT, GROUP tr_init

TABLE ahern.*;

-- 目标端：

[oracle@db1 ogg]$ ./ggsci

GGSCI (db1) 1> start mgr

GGSCI (db1) 2> info mgr

GGSCI (db1) 3> add replicat tr_init,specialrun

GGSCI (db1) 4> edit params tr_init

REPLICAT tr_init

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ogg

DISCARDFILE ./dirrpt/tr_init.dsc, PURGE

MAP ahern.*, TARGET ahern_tr.*;

-- 配置完启动上述两个进程：

GGSCI (db) 5> start extract sr_init

GGSCI (db1) 5> start extract tr_init

-- 查看报告：

-- 源端 view report sr_init 直到初始化结束

-- 源端：

GGSCI (db1) 5> view report sr_init

... ...

2019-06-06 20:06:13  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, extinit.prm:  EXTRACT EXTINIT started.

2019-06-06 20:06:41  INFO    OGG-00991  Oracle GoldenGate Capture for Oracle, extinit.prm:  EXTRACT EXTINIT stopped normally.

-- 目标端：

GGSCI (db1) 5> view report tr_init

-- 查看目标端数据加载情况：

$ sqlplus / as sysdba

SQL> select * from ahern.emp

-----------------------------------------------------------------------------------

-- 使用OGG进程进行初始化数据

-- 开始初始化数据的时候要满足下面的条件：

1.disable掉目标段表的外键约束

2.disable掉目标端表的触发器

3.删除目标段表的索引，加快初始化速度

4.目标端表结构创建完成

-- 源端配置初始化抽取进程

GGSCI (db11) 73> ADD EXTRACT sr_init, SOURCEISTABLE 

GGSCI (db11) 72> edit params sr_init 

Extract sr_init

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

DBOPTIONS ALLOWNOLOGGING

RmtHost 192.168.1.3, mgrport 7809

RmtTask Replicat, Group tr_init

Table HR.T;

 

-- 目标端装载进程

GGSCI (10gasm) 35> ADD REPLICAT tr_init, SPECIALRUN

GGSCI (10gasm) 34> edit param tr_init

Replicat tr_init

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

AssumeTargetDefs

Map HR.T, Target HR.T;

-- 只需要启动源段校验即可

GGSCI (db11) 74> view report sr_init

-- 方式二

set line 180

col OWNER form a10

col DIRECTORY_NAME form a30

col DIRECTORY_PATH form a85

select * from dba_directories;

create directory EXP_DIR as '/home/oracle/pump';

grant read,write on directory EXP_DIR to public;

select current_scn from v$database;

1763290

expdp system/oracle schemas=ahern directory=EXP_DIR dumpfile=ahern.dmp logfile=ahern.log flashback_scn=1763290 Version=11.2 parallel=2

impdp system/oracle schemas=ahern directory=EXP_DIR dumpfile=ahern.dmp logfile=ahern.log remap_schema=ahern:ahern remap_tablespace=USERS:USERS parallel=2

start replicat rep1,aftercsn 1763290

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 源端配置抽取进程

-- 1、不支持 DDL

GGSCI (dbs1c as ogg@dbmonitor) 6> add extract ext1,tranlog,threads 1,begin now

GGSCI (dbs1c as ogg@dbmonitor) 7> add exttrail /u01/app/ogg/dirdat/t1, extract ext1

GGSCI (dbs1c as ogg@dbmonitor) 8> edit param ext1 

extract ext1

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS DBLOGREADER

REPORTCOUNT EVERY 30 MINUTES, RATE

DISCARDFILE /u01/app/ogg/dirrpt/ext1.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 3:00

EXTTRAIL /u01/app/ogg/dirdat/t1

DBOPTIONS  ALLOWUNUSEDCOLUMN

FETCHOPTIONS NOUSESNAPSHOT

FETCHOPTIONS FETCHPKUPDATECOLS

table ahern.*;

-- 2、支持 DDL

EXTRACT ext1

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

REPORTCOUNT EVERY 30 MINUTES, RATE

DISCARDFILE /u01/app/ogg/dirrpt/ext1.dsc, APPEND, MEGABYTES 1024 -- 10m

DISCARDROLLOVER AT 3:00

WARNLONGTRANS 2h, CHECKINTERVAL 3m

TRANLOGOPTIONS DBLOGREADER

EXTTRAIL /u01/app/ogg/dirdat/t1

DYNAMICRESOLUTION

DBOPTIONS ALLOWUNUSEDCOLUMN

FETCHOPTIONS NOUSESNAPSHOT

--TRANLOGOPTIONS CONVERTUCS2CLOBS

TRANLOGOPTIONS EXCLUDEUSER ggate

--TRANLOGOPTIONS altarchivelogdest  instance ora10 /.../

--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000

--DDL area

DDL &

INCLUDE MAPPED OBJTYPE 'TABLE' &

INCLUDE MAPPED OBJTYPE 'INDEX' &

INCLUDE MAPPED OBJTYPE 'SEQUENCE' &

INCLUDE MAPPED OBJTYPE 'VIEW' &

INCLUDE MAPPED OBJTYPE 'PROCEDURE' &

INCLUDE MAPPED OBJTYPE 'FUNCTION' &

INCLUDE MAPPED OBJTYPE 'PACKAGE' &

EXCLUDE OPTYPE COMMENT

DDLOPTIONS  addtrandata REPORT

--add objects

table ahern.*;

SEQUENCE ahern.*;

-- 附 ext DDL

ext

DDL INCLUDE MAPPED

DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10                      

DDLOPTIONS REPORT

REPORTROLLOVER AT 6:00

REPORTCOUNT EVERY 1 HOURS,RATE

DDL INCLUDE MAPPED, OBJTYPE 'TABLE'

DDLOPTIONS REPORT, ADDTRANDATA

DDL INCLUDE ALL  

--DDLERROR RESTARTSKIP 100000 SKIPTRIGGERERROR 100000  

DDLOPTIONS ADDTRANDATA,REPORT 

-- 附简单DDL配置示例

extract ext1

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS DBLOGREADER

REPORTCOUNT EVERY 30 MINUTES, RATE

DISCARDFILE /u01/app/ogg/dirrpt/ext1.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 3:00

EXTTRAIL /u01/app/ogg/dirdat/t1

DDL INCLUDE ALL  

DDLOPTIONS ADDTRANDATA,REPORT 

DBOPTIONS ALLOWUNUSEDCOLUMN

FETCHOPTIONS NOUSESNAPSHOT

FETCHOPTIONS FETCHPKUPDATECOLS

table ahern.*;

SEQUENCE ahern.*;

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 源端配置投递进程（Data Pump）

GGSCI (dbs1c as ogg@dbmonitor) 9> add extract pump1, exttrailsource /u01/app/ogg/dirdat/t1

GGSCI (dbs1c as ogg@dbmonitor) 9> ADD EXTTRAIL /u01/app/ogg/dirdat/t1, EXTRACT pump1

GGSCI (dbs1c as ogg@dbmonitor) 10> edit param pump1

EXTRACT pump1

USERID ogg, PASSWORD ogg

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

RMTHOST 192.168.227.122, mgrport 7809, COMPRESS

PASSTHRU

RMTTRAIL /u01/app/ogg/dirdat/t1,format release 12.1

table ahern.*;

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 目标端配置 Replicat 进程

add replicat rep1 integrated exttrail /u01/app/ogg/dirdat/t1 , checkpointtable ogg.checkpoint -- 11

GGSCI (dbtrg as ogg@dbmonitor) 4> add replicat rep1, exttrail /u01/app/ogg/dirdat/t1, checkpointtable ogg.checkpoint -- 12

GGSCI (dbtrg as ogg@dbmonitor) 5> edit param rep1

REPLICAT rep1

SETENV(ORACLE_SID="ogg2")

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

REPORT AT 06:00

REPORTCOUNT EVERY 30 MINUTES, RATE

REPORTROLLOVER AT 02:00

REPERROR DEFAULT, ABEND

ALLOWNOOPUPDATES

ASSUMETARGETDEFS

-- DDL

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

-- DDLERROR DEFAULT IGNORE RETRYOP ?

-- 以下两个DDL参数不建议使用 

-- DDLERROR <error> IGNORE

-- DDLERROR <error1> IGNORE

-- DDL END

SOURCECHARSET PASSTHRU -- 12

DISCARDFILE /u01/app/ogg/dirrpt/rep1.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 02:00

map ahern.*,target ahern.*;

-- 附 rep DDL

DDL INCLUDE MAPPED -- ALL

DDLOPTIONS REPORT

DDLERROR 24344 IGNORE

DDLERROR 1435 IGNORE

DDLERROR DEFAULT IGNORE RETRYOP

ddlerror default ignore retryop maxretries 3 retrydelay 5

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 附录

-- 初始化数据

DML操作包括INSERT、UPDATE、DELETE、SELECT操作，而在这些操作中UPDATE、DELETE操作Redo只记录了变更的数据列以及行ID(ROWID)，

Gos1enGate抽取数据后将其转换为自己的格式发送到目标端。在同步开始前目标端没有初始化数据(目标端为空数据)，

那么事物产生的UPDATE、DELETE DML操作发送到目标端，目标端Gos1enGate Replicat进程会因为找不到数据而报错

从而导致Replicat进程崩溃停止(ABENDED)，所以这就需要我们在同步前初始化数据，初始化完后再同步，这样可以降低错误率。

同步数据的方式可以通过DBLINK、EXP/IMP、数据泵或者表空间迁移等方式同步。

-- 启动Gos1enGate

数据初始化后，分别启动目标端MGR进程、Replicat进程，源端MGR进程、主抽取进程(Primary Extract)、Data Pump进程(Secondly Extract)

注：因为在mgr都相应的配置了extract进程和replicat进程的自启动，所以在mgr进程启动后会自动启动extract进程和replicat进程

-- 目标端

$ ggsci

GGSCI (dbtrg) 1> start mgr

GGSCI (dbtrg) 2> info all

GGSCI (dbtrg) 3> start rep1  

-- 源端

$ ggsci

GGSCI (dbs1c) 1> start mgr

GGSCI (dbs1c) 2> info all

GGSCI (dbs1c) 3> start ext_sjz1

GGSCI (dbs1c) 4> start dp_sjz1

-- 查看相关统计数据

GGSCI (dbs1c) 1> stats dp_sjz1,daily

GGSCI (dbs1c) 2> stats rep1,daily

-- 启动失败查看日志

-- 日志文件存储路径：Gos1enGate安装目录下/ogg/product/ogg_home/ggserr.log

$ vi $OGG_HOME/ggserr.log

-- 同步测试

测试的时候分别测试insert、delete、update操作

-- 源端进行数据插入操作查看源端extract（提取）进程状态

GGSCI (dbs1c) 5> stats ext_sjz1

GGSCI (dbs1c) 6> stats dp_sjz1

-- 目标端查看replicat（复制）进程状态

GGSCI (dbtrg) 4> stats rep1

-- 目标端数据查询验证

-- 查看replicat 复制是否完成

GGSCI (dbtrg) 4> SEND REPLICAT rep1 status

-- 附：

开启归档，最小附加日志和强制归档之后，ogg可以同步insert，delete等部分操作，

但遇到update时将会挂掉，报错说找不到相关记录，因此，还需要再对每一个table都在源端开启表级日志传输

drop tablespace sys_tablespace including contents and datafiles;

ALTER USER dbo_tyshdb QUOTA UNLIMITED ON sys_tablespace;

-- 附：注意事项

1、字符集问题

如果源库字符集为 AL32UTF8 目标库为 ZHS16GBK

则在配置OGG过程中应该把源端和目标端字符集配置一致，也就是说源端为 AL32UTF8，则目标端OGG也配置为 AL32UTF8。

如果配置不一致将会导致插入乱码。

2、数据库中查询列数据是否有重复示例

-- 查单个字段：

SELECT TEST_NAME,COUNT(*) FROM T_TEST GROUP BY TEST_NAME HAVING COUNT(*) > 1;

-- 查组合字段：

SELECT TEST_NAME1,TEST_NAME2,COUNT(*) FROM T_TEST GROUP BY TEST_NAME1,TEST_NAME2 HAVING COUNT(*) > 1;

3、重置trial文件，在进行重置前一定要保证rep全部应用完，否则数据不一致

alter extract ext_sjz1 etrollover

alter extract DP_SJZ1,thread 1,extseqno 4,extrba 0

ALTER REPLICAT rep1, EXTSEQNO 7, EXTRBA 0

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 改变抽取应用检查点

-- 请确保已经 掌握 ogg 各个进程的读检查点和写检查点的详细含义

BEGIN {NOW | yyyy-mm-dd[:hh:mi:[ss[.cccccc]]] | EOF | SEQNO <sequence number>}

--改动抽取进程的開始抽取点：（即：myext的读检查点）

alter extract myext BEGIN yyyy-mm-dd:hh:mi:ss

alter extract myext BEGIN now

 

--改动myext的写检查点：

ALTER EXTRACT myext, etrollover

--改动传输mydp进程的读检查点

ALTER EXTRACT mydp, EXTSEQNO 1234, EXTRBA 0

--改动传输mydp进程的写检查点

alter extract mydp etrollover

--改动myrep的读检查点

ALTER replicat myrep, EXTSEQNO 1234, EXTRBA 0

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 1.手动调整Capture进程开始工作点

-- 变更-进程从2015-03-1012:12:12开始抽取事务。

GGSCI >alter extract, begin 2015-03-10 12:12:12

-- 变更-进程从Oracle数据库SCN为778899时开始抽取事务。

GGSCI >alter extract ext_001, scn 778899

-- 2.手动调整Capture进程写入Trail文件Sequence号

-- 变更-Capture进程写Trail文件到当前Sequence+1中，RBA从0开始。

GGSCI > alter ext_001 etrollover

-- 3.手动调整Data Pump进程读Trail文件检查点位置

GGSCI > alter dpe_001, extseqno 5, extrba 0

-- 4.手动调整Data Pump进程写Trail文件检查点位置，当前Sequence+1中，RBA从0开始。

GGSCI > alter extract dpe_001 etrollover

-- 5.手动调整Replicat进程读Trail文件检查点位置

GGSCI > stop rep_001

Sending STOP requestto REPLICAT REP_001 ...

Request processed.

------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

-- 附异常问题

2019-05-14T14:50:47.142+0800  WARNING OGG-06600  Oracle GoldenGate Capture for Oracle, pump1.prm:  

The remote peer for remote trail /u01/app/ogg/dirdat/t1 does not support 9 digit seqlen feature. Continuing with 6 digit seqlen

-- 解决方案

SOLUTION

You can lower the source to use 6 digit trail sequences by:

1. Create a text file called GLOBALS in the source <OGG_HOME>

2. Add a line containing: TRAIL_SEQLEN_6D

Then restart MGR and recreate your EXTRACT and PUMP.

------------------------------------------------------------------------------------------------------------------------------------------------