備註： 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

Password:

Successfully logged into database.

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

port 7809

dynamicportlist 7810-7900

userid ,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 ,password ogg

tranlogoptions convertucs2clobs

TRANLOGOPTIONS LOGRETENTION DISABLED

TRANLOGOPTIONS DBLOGREADER

tranlogoptions altarchivelogdest primary instance sfpay2 /data/archivelog

--TRANLOGOPTIONS ASMUSER ,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 ,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 , 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

相關文章