本機到本機的配置: 源端 10.1.83.144:1521/SIBP_GSDY HNSIB_GSDY目標端 10.1.83.144:1521/SIBP_GS HNSIB_GS進程名規劃 :OBB05 源端抽取進程及文件首碼:BG01_DY DY 目標端恢復進程:OBR01 //環境變數設置#for ...
本機到本機的配置:
源端 10.1.83.144:1521/SIBP_GSDY HNSIB_GSDY
目標端 10.1.83.144:1521/SIBP_GS HNSIB_GS
進程名規劃 :OBB05
源端抽取進程及文件首碼:BG01_DY DY
目標端恢復進程:OBR01
//環境變數設置
#for ogg
export GG_HOME=/ogg/data
export PATH=$JAVA_HOME/bin:$GG_HOME:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$GG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib
export NLS_LANG="american_america.ZHS16GBK"
alias ggsci='cd $GG_HOME; $GG_HOME/ggsci'
alias gglog='cd $GG_HOME;tail -n50 -f $GG_HOME/ggserr.log'
alias ggserr='cd $GG_HOME;tail -n50 -f $GG_HOME/ggserr.log'
alias e1log='cd $GG_HOME/dirrpt;tail -n50 -f $GG_HOME/dirrpt/E1.rpt'
alias ggdir='cd $GG_HOME'
alias dirrpt='cd $GG_HOME/dirrpt'
alias dirdat='cd $GG_HOME/dirdat'
alias dirout='cd $GG_HOME/dirout'
//資料庫設置
Select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
alter database add supplemental log data;
Alter database force logging;
alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
Alter database force logging;
SQL> show parameter enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true scope=both; --RAC的所有實例也需要設置
//tns設置
SIBP_GSDY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scanvip05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SIBP_GSDY)
)
)
SIBP_GS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scanvip05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SIBP_GS)
)
)
//源端
create user c##ggadm identified by ggadm;
grant connect,resource,dba to c##ggadm container=all;
--DROP USER C##GGADM CASCADE
exec dbms_goldengate_auth.grant_admin_privilege('c##ggadm');
exec dbms_goldengate_auth.grant_admin_privilege('c##ggadm',container=>'all');
//目的端,分別connect到每個PDB
SQL> alter session set container = SIBP_GS ;
SQL> create user ggadm identified by ggadm container=current ;
SQL> grant connect,resource,dba to ggadm ;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('GGADM',container=>'SIBP_GS');
GGSCI > add credentialstore
GGSCI > ALTER CREDENTIALSTORE ADD USER ggadm@SIBP_GS PASSWORD ggadm ALIAS ggadm1
GGSCI (node1) 8> ALTER CREDENTIALSTORE ADD USER c##ggadm@SIBCGSC1 PASSWORD ggadm ALIAS ggadm
Credential store altered.
--ALTER CREDENTIALSTORE delete USER c##ggadm@SIBP_GS ALIAS ggadm1
//以下兩條摘自文檔,但不完全包含所需的許可權,比如CREATE SESSION就沒有
exec dbms_goldengate_auth.grant_admin_privilege('c##ggadm');
exec dbms_goldengate_auth.grant_admin_privilege('c##ggadm',container=>'all');
------------------------------------------------------------------------------------extract
info credentialstore
add credentialstore
DBLOGIN USERIDALIAS ggadm
DBLOGIN USERIDALIAS ggadm1
DBLOGIN USERID ggadm, PASSWORD ggadm
add schematrandata SIBP_GSDY.HNSIB_GSDY
info schematrandata HNSIB_GSDY
info trandata *.*
REGISTER EXTRACT BG01_DY DATABASE CONTAINER (SIBP_GSDY)
SCN 2088952.
ADD EXTRACT BG01_DY, INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/DY, EXTRACT BG01_DY
edit params BG01_DY
EXTRACT BG01_DY
USERIDALIAS ggadm
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
GETTRUNCATES
EXTTRAIL ./dirdat/DY
DISCARDFILE ./dirrpt/DY.dsc, APPEND, MEGABYTES 4000
WARNLONGTRANS 1H, CHECKINTERVAL 5M
LOGALLSUPCOLS
NOCOMPRESSUPDATES
UPDATERECORDFORMAT FULL
REPORTCOUNT EVERY 2 SECONDS, RATE
--SOURCECATALOG SIBP_GSDY
TABLE SIBP_GSDY.HNSIB_GSDY.AA20 ;
TABLE SIBP_GSDY.HNSIB_GSDY.AA50 ;
-------------------pump
空
---------------------R
DBLOGIN USERIDALIAS ggadm1
add checkpointtable ggadm.checkpoint
ADD REPLICAT OBR01 INTEGRATED EXTTRAIL ./dirdat/DY
edit params OBR01
REPLICAT OBR01
USERIDALIAS ggadm1
ASSUMETARGETDEFS
REPERROR (DEFAULT, ABEND)
--REPERROR (1403, DISCARD)
--REPERROR (-1,IGNORE)
DISCARDFILE ./dirrpt/GS.dsc, append, megabytes 400
--For DDL :
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS INTEGRATEDPARAMS(parallelism 2)
Gettruncates
ALLOWNOOPUPDATES
numfiles 3000
REPORTCOUNT EVERY 2 SECONDS, RATE
--MAP scott.*,TARGET scott.*;
--SOURCECATALOG SIBP_GSDY
--MAP SIBP_GSDY.HNSIB_GSDY.*, TARGET SIBP_GS.HNSIB_GS.*;
MAP SIBP_GSDY.HNSIB_GSDY.AA20 ,TARGET HNSIB_GS.AA20 ;
MAP SIBP_GSDY.HNSIB_GSDY.AA50 ,TARGET HNSIB_GS.AA50 ;