192.168.27.33

test11g

hdb11g

trandata: 同步delete,update 使用

config 文件：同步表使用

进程根据SCN号和RBA和主键同步

##目的：数据定时同步，从源库 test11g同步到目标库 hdb11g

                    testDATA.TEST 同步到  MCPDATA.TEST

                    testDATA/testdatapr

                    

##source和target端均操作：

source:

cd /testdb11g/ogg

target:

cd /testhdb11g/ogg

source:

PORT 7809

target:

PORT 7810

（/u01/gg11/ggserr.log

sqlplus testDATA/testdatapr

alter user gguser identified by gguser;

create user gguser identified by gguser default tablespace SUPPORT;

grant  resource, connect, dba to gguser;

 create table t1 as select * from user_objects;    

 alter table t1 add constraint pk_t1 primary key(object_id);  

 

 

  create table t1 as select * from user_objects where 1=2;

  alter table t1 add constraint pk_t1 primary key(object_id);  

step 1:

一.3.5.1  开启hr用户下所有表的附加日志  

dblogin userid ggusr@ogg1, password lhr

add trandata hr.*

--extract einig1

--setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

--userid gguser,password gguser

--rmthost 192.168.27.33,mgrport 7810

--rmttask replicat,group rinig1

--extract einig1

--setenv (ORACLE_SID=test11g)

--setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)

--userid gguser,password gguser

--rmthost 192.168.27.33,mgrport 7810

--rmttask replicat,group rinig1

--table testdata.t1;

--replicat rinig1

--setenv (ORACLE_SID=hdb11g)

--setenv (ORACLE_HOME=/opt/oracle11g/product/11.2)

--setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)

--assumetargetdefs

--userid gguser,password gguser

--discardfile ./dirrpt/rinig1.dsc,purge

--map testdata.*,target testdata.*;

######process eora_test   添加并配置extract进程

 add extract eora_test,tranlog,begin now

 

extract eora_test

setenv (ORACLE_SID=test11g)

setenv (ORACLE_HOME=/opt/oracle11g/product/11.2)

setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)

userid gguser,password gguser

exttrail ./dirdat/mc

table testdata.*;

添加trail文件

add exttrail ./dirdat/mc,extract eora_test,megabytes 100

 

start extract eora_test

 

状态:

info extract eora_hr

 

 

#####process  pora_test 添加并启动pump进程

 edit params pora_test

extract pora_test

setenv (ORACLE_SID=test11g)

setenv (ORACLE_HOME=/opt/oracle11g/product/11.2)

setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)

passthru

rmthost 192.168.27.33,mgrport 7810

rmttrail ./dirdat/pa

table testdata.*;

 add extract pora_test,exttrailsource ./dirdat/mc   

 add rmttrail ./dirdat/pa,extract pora_test,megabytes 100    

 

 start extract pora_test

 

 

######target   process :rora_test  在target端添加检查表，配置replicat进程

 

 edit params ./GLOBALS  

 

 dblogin userid gguser@hdb11g, password gguser

 

 add checkpointtable gguser.CHECKPOINTTABLE

 

 

 二、 添加并启动replicat进程

 

 edit params rora_test

 

replicat rora_test

setenv (ORACLE_SID=hdb11g)

setenv (ORACLE_HOME=/opt/oracle11g/product/11.2)

setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)

userid gguser,password gguser

handlecollisions  

assumetargetdefs  

discardfile ./dirrpt/rora_test.dsc,purge  

map testdata.* ,target testdata.*;

 add replicat rora_test,exttrail ./dirdat/pa

 

 

状态检查：

GGSCI (rhel6_lhr) 4> info all

 

 

sampe 1:

1.prepar

--/ogg11gdb/ogg

--/hdbuatdb/ogg

--ogg version:11.2.1.0.23 18709404 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140519.1509

cd $HOME/utility/setup

##instance 1

cp hdb11g.env hdbuat.env

echo "export OGG_BIN=/hdbuatdb/ogg" > hdbuat.env

echo "export GGS_HOME=/hdbuatdb/ogg" >> hdbuat.env

echo "export OGG_SYS_HOST_NAME=192.168.27.195" >> hdbuat.env

echo "alias data='cd \$OGG_BIN'" >> hdbuat.env

# User defined variables

echo "alias ogg='cd \$OGG_BIN'" >> hdbuat.env

##instance 2

cp hdb11g.env ogg11g.env

echo "export OGG_BIN=/ogg11gdb/ogg" > ogg11g.env

echo "export GGS_HOME=/ogg11gdb/ogg" >> ogg11g.env

echo "export OGG_SYS_HOST_NAME=192.168.27.190" >> ogg11g.env

echo "alias data='cd \$OGG_BIN'" >> ogg11g.env

# User defined variables

echo "alias ogg='cd \$OGG_BIN'" >> ogg11g.env

##instance 1 2

--在None的前一行　打印新行。

awk '/None/{print " \"ogg11g\") echo \n export ORACLE_SID=ogg11g \n . $OGG_DIR/ogg11g.env \n export OGG_BIN=/ogg11gdb/ogg \n break; break;; "}1' setogg.sh > 1

awk '/None/{print " \"hdbuat\") echo \n export ORACLE_SID=hdbuat \n . $OGG_DIR/hdbuat.env \n export OGG_BIN=/hdbuatdb/ogg \n break; break;; "}1' 1 > setogg.sh

##instance 2

sqlplus / as sysdba << eof

alter database add supplemental log data ;

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

create tablespace TS_OGG datafile '/ogg11gdb/data/ts_ogg01.dbf' size 50M ;

create user ggusr identified by ggusr_12 default tablespace TS_OGG;

grant resource, connect, select any table,select any dictionary,ALTER ANY TABLE,dba to ggusr;

eof

##instance 1

sqlplus / as sysdba << eof

alter database add supplemental log data ;

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

create tablespace TS_OGG datafile '/hdbuatdb/data/ts_ogg01.dbf' size 50M ;

create user ggusr identified by ggusr_12 default tablespace TS_OGG;

grant resource, connect, select any table,select any dictionary,ALTER ANY TABLE to ggusr;

eof

###instance 2

sqlplus / as sysdba << eof

create user hr identified by hrdata_12 default tablespace TS_OGG;

grant resource, connect, select_catalog_role to hr;

create table hr.t1 as select * from dba_objects where 1=2;

alter table hr.t1 add constraint pk_t1 primary key(object_id);

eof

 

cp $OGG_BIN/dirprm/mgr.prm $OGG_BIN/dirprm/mgr.prm.bak

echo "port 12100" > $OGG_BIN/dirprm/mgr.prm

ogg

(mkdir dirrpt

mkdir dirpcs

mkdir dirchk

mkdir dirtmp

mkdir dirdat)

ggsci << eof

create subdirs

start mgr

info all

info mgr

eof

 

### instance 1

sqlplus / as sysdba << eof

create user hr identified by hrdata_12 default tablespace TS_OGG;

grant resource, connect, select_catalog_role,select any dictionary,select any table to hr;

create table hr.t1 as select * from dba_objects;

alter table hr.t1 add constraint pk_t1 primary key(object_id);

eof

cp $OGG_BIN/dirprm/mgr.prm $OGG_BIN/dirprm/mgr.prm.bak

echo "port 7810" > $OGG_BIN/dirprm/mgr.prm

 

echo "extract e_test" > $OGG_BIN/dirprm/e_test.prm

echo "setenv (ORACLE_SID=hdbuat)" >> $OGG_BIN/dirprm/e_test.prm

echo "setenv (ORACLE_HOME=/opt/oracle11g/product/11.2)" >> $OGG_BIN/dirprm/e_test.prm

echo "setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)" >> $OGG_BIN/dirprm/e_test.prm

echo "userid ggusr,password ggusr_12" >> $OGG_BIN/dirprm/e_test.prm

echo "exttrail ./dirdat/mc" >> $OGG_BIN/dirprm/e_test.prm

echo "table hr.t1; " >> $OGG_BIN/dirprm/e_test.prm

 

echo "extract p_test" > $OGG_BIN/dirprm/p_test.prm

echo "setenv (ORACLE_SID=hdbuat)" >> $OGG_BIN/dirprm/p_test.prm

echo "setenv (ORACLE_HOME=/opt/oracle11g/product/11.2)" >> $OGG_BIN/dirprm/p_test.prm

echo "setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)" >> $OGG_BIN/dirprm/p_test.prm

echo "passthru" >> $OGG_BIN/dirprm/p_test.prm

echo "rmthost 192.168.27.195,mgrport 12100" >> $OGG_BIN/dirprm/p_test.prm

echo "rmttrail ./dirdat/pa" >> $OGG_BIN/dirprm/p_test.prm

echo "table hr.t1; " >> $OGG_BIN/dirprm/p_test.prm

ogg

ggsci << eof

create subdirs

start mgr

info all

info mgr

dblogin userid ggusr, password ggusr_12

add trandata hr.*

add extract e_test,tranlog,begin now

add exttrail ./dirdat/mc,extract e_test,megabytes 100

start extract e_test

eof

ogg

ggsci << eof

add extract p_test,exttrailsource ./dirdat/mc

add rmttrail ./dirdat/pa,extract p_test,megabytes 100

start extract p_test

eof

#### instance 2

ogg

cp ./GLOBALS ./GLOBALS.bak

echo "GGSCHEMA GGUSR" > ./GLOBALS

echo "checkpointtable CHECKPOINTTABLE" >> ./GLOBALS

echo "replicat r_test" > $OGG_BIN/dirprm/r_test.prm

echo "setenv (ORACLE_SID=ogg11g)" >> $OGG_BIN/dirprm/r_test.prm

echo "setenv (ORACLE_HOME=/opt/oracle11g/product/11.2)" >> $OGG_BIN/dirprm/r_test.prm

echo "setenv (NLS_LANG=AMERICAN_AMERICA.UTF8)" >> $OGG_BIN/dirprm/r_test.prm

echo "userid ggusr,password ggusr_12" >> $OGG_BIN/dirprm/r_test.prm

echo "handlecollisions" >> $OGG_BIN/dirprm/r_test.prm

echo "assumetargetdefs" >> $OGG_BIN/dirprm/r_test.prm

echo "discardfile ./dirrpt/r_test.dsc,purge" >> $OGG_BIN/dirprm/r_test.prm

echo "map hr.*,target hr.*; " >> $OGG_BIN/dirprm/r_test.prm

ggsci << eof

dblogin userid ggusr, password ggusr_12

add checkpointtable GGUSR.CHECKPOINTTABLE

add replicat r_test,exttrail ./dirdat/pa

start R_TEST

eof

 

测试１：

###begin sync data firstly　use exp/imp and filter

### instance 2

ogg

ggsci << eof

stop R_TEST

eof

### instance 1

###add config

extract:

table hr.t1;

pump:

table hr.t1;

ogg

ggsci << eof

stop E_TEST

stop P_TEST

dblogin userid ggusr, password ggusr_12

add trandata hr.t1

start E_TEST

start P_TEST

eof

sqlplus / as sysdba << eof

select count(*) from hr.t1;

select current_scn from v\$database;

create or replace directory datapump as '/tmp';

grant read,write on directory datapump to public;

eof

expdp system/oracle123 directory=datapump dumpfile=test.dmp schemas=hr flashback_scn=93919213

 

### instance 2

impdp system/ngn12_system directory=datapump dumpfile=test.dmp table_exists_action=replace

sqlplus / as sysdba << eof

select count(*) from hr.t1;

select owner,table_name,TRIGGER_NAME from dba_triggers where table_name ='T1';

eof

###add config

map hr.t1, target hr.t1, filter ( @getenv("TRANSACTION", "CSN") > 93919213);

ogg

ggsci << eof

start R_TEST

STATS * latest,totalsonly *.*

eof

### instance 1

##add a values

sqlplus / as sysdba << eof

insert into hr.t1(object_id) values ( 20000);

commit;

select * from hr.t1 where object_id=20000;

eof

##delete values

sqlplus / as sysdba << eof

delete from hr.t1 where object_id=20000;

commit;

select * from hr.t1 where object_id=20000;

eof

测试２：

#######simualate ORA-00001

##instance 2

ogg

ggsci << eof

stop R_TEST

eof

###modify config

nohandlecollisions

###instance 1: add two same values

sqlplus / as sysdba << eof

select owner,table_name,CONSTRAINT_NAME from dba_constraints where table_name ='T1';

alter table hr.t1 disable constraint PK_T1;

insert into hr.t1(object_id) values ( 20000);

insert into hr.t1(object_id) values ( 20000);

select * from hr.t1 where object_id=20000;

commit;

eof

###instance 2 will report ORA-00001

workaourd:

step 1:

######modify config from nohandlecollisions to handlecollisions

handlecollisions

###instance 2 , it will show one row insert ok, one row insert ignore

sqlplus / as sysdba << eof

select owner,table_name,CONSTRAINT_NAME from dba_constraints where table_name ='T1';

select count(*) from hr.t1 where object_id=20000;

commit;

eof

step 2:

instance 2######modify config from handlecollisions to nohandlecollisions

ogg

ggsci << eof

stop R_TEST

start R_TEST

STATS * latest,totalsonly *.*

eof