ogg 初始化

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 export ORACLE_SID=ogg11g . $OGG_DIR/ogg11g.env export OGG_BIN=/ogg11gdb/ogg break; break;; "}1' setogg.sh > 1
awk '/None/{print " "hdbuat") echo export ORACLE_SID=hdbuat . $OGG_DIR/hdbuat.env export OGG_BIN=/hdbuatdb/ogg 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