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 (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 (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

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

-- 添加檢查點(源端和目标端配置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

assumetargetdefs

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 

setenv (nls_lang = american_america.al32utf8)

dboptions allownologging

rmthost 192.168.1.3, mgrport 7809

table hr.t;

-- 目标端裝載程序

ggsci (10gasm) 35> add replicat tr_init, specialrun

ggsci (10gasm) 34> edit param tr_init

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)

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

-- 2、支援 ddl

discardfile /u01/app/ogg/dirrpt/ext1.dsc, append, megabytes 1024 -- 10m

warnlongtrans 2h, checkinterval 3m

dynamicresolution

dboptions allowunusedcolumn

--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

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配置示例

-- 源端配置投遞程序（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

rmthost 192.168.227.122, mgrport 7809, compress

passthru

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

-- 目标端配置 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")

report at 06:00

reportrollover at 02:00

reperror default, abend

allownoopupdates

-- ddl

-- 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

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 (dbtrg) 1> start mgr

ggsci (dbtrg) 2> info all

ggsci (dbtrg) 3> start rep1  

-- 源端

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.