本机到本机的配置：
源端 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 ;