在dg库上搭建ogg

备注： Oracle DG (主->备库1->备库2）级联方式

主库： 10.118.242.216 sid_name=sfpay2

备库1： 10.118.242.215 sid_name=sfpay2 => ogg 主库

ogg库1： 10.118.230.27 sid_name=sfpay3 => ogg备库1

ogg库2： 10.118.242.214 sid_name=sfpay1 => ogg备库2

ogg 方式：一个捕获进程，多个pump 进程（一对多方式）

ogg 一库多实例时，请配置好 setenv 参数（oracle_sid,nls_lang, oracle_home), 记住 tnsname.ora 对应好.

注意ogg source 端，target 端时区。

ogg extract 端不需要做checkpoint ，以及备库只能做 DML ogg 同步，DDL 不支持。

-----日志： ---------------------------------------------------------------------------

Database Language and Character Set:

NLS_LANG = "AMERICAN_AMERICA.AL32UTF8"

NLS_LANGUAGE = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "AL32UTF8"

Source Context :

SourceModule : [er.redo.ora]

SourceID : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/app/er/redo/oracl

e/redoora.c]

SourceFunction : [REDO_validate_config]

SourceLine : [5980]

ThreadBacktrace : [12] elements

: [/data/gg11.2/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7f9bedb5ad2e]]

: [/data/gg11.2/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned in

t, ...)+0x2ec) [0x7f9bedb5388c]]

: [/data/gg11.2/libgglog.so(_MSG_ERR_DDL_STANDBY_NOT_SUPPORTED(CSourceContext*, CMessag

eFactory::MessageDisposition)+0x2c) [0x7f9bedb43e16]]

: [/data/gg11.2/extract(REDO_validate_config(int, unsigned int*, int*)+0xdc9) [0x6a4ab9

]]

: [/data/gg11.2/extract(redo_log_setup()+0x34) [0x57abf4]]

: [/data/gg11.2/extract(extract_main(int, char**)+0x3bf) [0x57e0ef]]

: [/data/gg11.2/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x633

09f]]

: [/data/gg11.2/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::Multi

Threading::Thread::ThreadArgs*)+0x104) [0x6332f4]]

: [/data/gg11.2/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b)

[0x6333fb]]

: [/data/gg11.2/extract(main+0x2c) [0x57dc5c]]

: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x383e61ed5d]]

: [/data/gg11.2/extract(__gxx_personality_v0+0x3b2) [0x4f64aa]]

2015-11-25 11:33:05 ERROR OGG-00520 DDL replication is not supported for standby databases.

2015-11-25 11:33:05 ERROR OGG-01668 PROCESS ABENDING.

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

测试结果：

source 端（DG备库1）的所有数据初始话，都以 DG主库为准（scn, 数据的导出）

source 端（DG备库1）的ogg 操作，都需要在 DG 主库上执行ogg脚本，trandata log. 参数可以忽略。

1：主库执行ogg 脚本

2：因standby database 不支持 ogg 的DDL 方式，注销掉所有ddl 方式。

3： ogg source端，dg 备库1 在info trandata schema.* 时，可能为disabled 状态，但是DG主库enable 即可。

GGSCI (sfpay.datatest.MySQL02) 40> info trandata dm_ord.*

Logging of supplemental redo log data is disabled for table DM_ORD.TEMP_20151105_T.

4： source 端参数：

GGSCI (sfpay.datatest.mysql02) 46> dblogin userid ogg@ogg

Password:

Successfully logged into database.

GGSCI (sfpay.datatest.mysql02) 49> view params mgr

port 7809

dynamicportlist 7810-7900

userid ogg@ogg,password ogg

autorestart extract *,waitminutes 2,retries 7

lagreporthours 1

laginfominutes 30

lagcriticalminutes 45

purgeoldextracts ./dirdat/*,usecheckpoints,minkeepdays 5

purgeddlhistory minkeepdays 3,maxkeepdays 5,frequencyminutes 20

PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 20

GGSCI (sfpay.datatest.mysql02) 50> view params exp001

extract exp001

setenv (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/dbhome_1")

setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

dynamicresolution

gettruncates

numfiles 5000

userid ogg@ogg,password ogg

tranlogoptions convertucs2clobs

TRANLOGOPTIONS LOGRETENTION DISABLED

TRANLOGOPTIONS DBLOGREADER

tranlogoptions altarchivelogdest primary instance sfpay2 /data/archivelog

--TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle123

--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000

reportcount every 2 minutes,rate

discardfile ./dirrpt/exp001.dsc,append,megabytes 1000

warnlongtrans 2h,checkinterval 3m

exttrail ./dirdat/p1

--ddl include all

--ddloptions addtrandata,report

--add test

table dm_sypay.*;

table dm_ord.*;

GGSCI (sfpay.datatest.mysql02) 51> view params pump214

extract pump214

passthru

report at 02:00

reportrollover at 02:10

rmthost 10.118.242.214,mgrport 7809

rmttrail ./dirdat/t1

dynamicresolution

numfiles 2000

--add tables

table dm_sypay.*;

table dm_ord.*;

GGSCI (sfpay.datatest.mysql02) 52> view params pump01

extract pump01

setenv(ORACLE_SID="sfpay3")

passthru

report at 02:00

reportrollover at 02:10

rmthost 10.118.230.27,mgrport 7809

rmttrail ./dirdat/e1

dynamicresolution

numfiles 2000

--add tables

table dm_sypay.*;

table dm_ord.*;

GGSCI (sfpay.datatest.mysql02) 53> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXP001 00:00:00 unknown

EXTRACT RUNNING PUMP01 00:00:00 00:00:09

EXTRACT RUNNING PUMP214 00:00:00 00:00:09

source端添加捕获extract 进程组：

GGSCI > add extract exp001, tranlog ,begin now --(, threads 2) --添加进程组

GGSCI > add exttrail ./dirdat/p1, extract exp001, megabytes 500 --添加本地exttrial 文件。

source 端添加pump 进程组以及 target exttrail 文件：

--在启动之前，配置好target 端参数，及启动mgr 进程--

GGSCI > add extract pump214, exttrailsource ./dirdat/p1 , begin now

--添加pump 进程组

GGSCI > add rmttrail ./dirdat/t1, extract pump214 , megabytes 500

--添加远程trail 文件

GGSCI > add extract pump01, exttrailsource ./dirdat/p1 , begin now

--添加pump 进程组

GGSCI > add rmttrail ./dirdat/e1, extract pump01, megabytes 500

--添加远程trail 文件

5 ，target 端 rep214:

GGSCI (sfpay-asmtest) 18> view params ./GLOBALS

GGSCHEMA OGG

CHECKPOINTTABLE OGG.CHECKPOINT

GGSCI (sfpay-asmtest) 19> view params mgr

port 7809

dynamicportlist 7810-7900

userid ogg@ogg,password ogg

autorestart extract *,waitminutes 2,retries 7

lagreporthours 1

laginfominutes 30

lagcriticalminutes 45

purgeoldextracts ./dirdat/*,usecheckpoints,minkeepdays 5

purgeddlhistory minkeepdays 3,maxkeepdays 5,frequencyminutes 20

PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 20

GGSCI (sfpay-asmtest) 20> view params rep214

REPLICAT rep214

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg@ogg, PASSWORD ogg

DISCARDFILE ./dirrpt/rep214.dsc, PURGE, MAXBYTES 104857600

sqlexec "Alter session set constraints=deferred"

REPORT AT 01:59

reportrollover at 02:00

--handlecollisions

reperror default,abend

REPORTCOUNT EVERY 30 MINUTES, RATE

DDL INCLUDE MAPPED, EXCLUDE INSTR 'SHRINK SPACE'

ddloptions report

assumetargetdefs

checksequencevalue

allownoopupdates

dynamicresolution

numfiles 2000

--ERROR IGNORE

DDLERROR 10636 IGNORE

--20151028 add

MAP dm_sypay.*, TARGET dm_sypay.*;

MAP dm_ord.*,TARGET dm_ord.*;

pump01 同样，略过。

6，数据初始化 expdp 通过flshback_scn , DG主库。

7， target 端启动：

GGSC > add replicat rep214, exttrail ./dirdat/e1, begin now

GGSCI > start replicat ,aftercsn 6553589

8 ,测试：

9，完成。

分享名称：在dg库上搭建ogg
浏览地址：http://cqcxhl.com/article/piesso.html

重庆分公司，新征程启航

在dg库上搭建ogg

其他资讯