| ogg

ogg 常用命令

初始化

ogg软件安装

1
2
3
4
5
6
7
8
mkdir /goldengate
chown oracle:dba /goldengate
cd /goldengate
tar xvf ggs.tar
cd /home/oracle
ln -s /goldengate ggs
vi .bash_profile
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

初始化目录

1
2
3
4
5
6
7
8
9
10
11
create subdirs
add checkpointtable ggs.ggs_checkpoint

alter system set enable_goldengate_replication=true scope=both
create user ggs identified by xxx;
grant connect,resource to ggs;
grant unlimited tablespace to ggs;
grant execute on utl_file to ggs;
@marker_setup.sql;
@role_setup.sql;
grant ggs_ggsUSER_ROLE TO ggs;

源端开启归档、增加trandata ,配置投递进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
startup mount 
alter database archivelog;
alter database force logging;
alter database add supplemental log data;

dblogin userid ggs
add trandata tabname..

##新建extract（抽取进程）
edit  param  eds_gs1
extract  eds_gs1
SETENV (ORACLE_SID="sid")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD xxx
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
EXTTRAIL ./dirdat/gs
NUMFILES 3000
EOFDELAYCSECS 30
GETTRUNCATES
TRANLOGOPTIONS DBLOGREADER
DYNAMICRESOLUTION
BR BRINTERVAL 2H , BRDIR BR
GETUPDATEBEFORES
NOCOMPRESSDELETES
WARNLONGTRANS 3H, CHECKINTERVAL 3M
 table  user.table1;

add extract eds_gs1,tranlog,threads 2,begin now
add exttrail ./dirdat/xa ,extract eds_gs1

##新建pump（投递进程）
edit param pds_gs1
extract pds_gs1
SETENV (ORACLE_SID="sid")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,xxx
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
FLUSHCSECS 30
NUMFILES 3000
EOFDELAYCSECS 30
RMTHOST  xxx.xxx.xxx.xxx,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
RMTTRAIL ./dirdat/gs
GETTRUNCATES
PASSTHRU
DYNAMICRESOLUTION
GETUPDATEBEFORES
NOCOMPRESSDELETES
TABLEEXCLUDE *.DBMS_TABCOMP_TEMP*;
table  user.table1;

add extract pds_gs1 ,exttrailsource ./dirdat/xa ,begin now
add rmttrail ./dirdat/zj,extract pds_gs1

目标端新建replicat进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
edit param RJC_SB01
replicat  RJC_SB01 
SETENV (ORACLE_SID="sid")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ggs , PASSWORD xxx
sqlexec "Alter session set commit_write=nowait"
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
REPERROR DEFAULT,ABEND
DISCARDFILE ./dirrpt/RJC_SB01.dsc,append,megabytes 500
DISCARDROLLOVER AT 06:00
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
GETUPDATEBEFORES
NOCOMPRESSDELETES
NUMFILES 3000
EOFDELAYCSECS 30
GETTRUNCATES
 map table1,target table1;

add replicat  RJC_SB01 ,exttrail ./dirdat/jc,checkpointtable ggs.ggs_checkpoint

日常管理

进程队列号查看、修改；查看进程执行情况、replicat拆分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
info xxx,showch
alter replicat xxx extseqno 0,extrba  0
start  xxxx atcsn 15993004743819
alter extract xxx,begin now 
alter extract xxx,etrollover
filter (@GETENV ("transaction","csn") > 15993004743819);
filter (@STRNUM(sjgsdq)= "1111");
fiter(@STRFIND(sjgsdq,"1111")>0);
edit param xxx
view report xxx

##ogg 初始化
stop replicat
expdp  \' / as sysdba \'  tables=tab01 cluster=no dumpfile=tab01.dmp directory=dp_dir logfile=tab01.log     flashback_scn=1001000101
impdp  \' / as sysdba \'    cluster=no dumpfile=tab01.dmp  directory=dp_dir  logfile=tab01.log   table_exists_action=replace 
#replicat增加filter
MAP tab01,target tab01,filter (@GETENV ("transaction","csn") > 1001000101);
start replicat
#expdp 数据同步
expdp  \' / as sysdba \'  cluster=no dumpfile=DMP_%U.dmp directory=dp_dir logfile=DMP.log  compression=all parallel=10 flashback_scn=16490079948579 parfile=ql.par
expdp  \' / as sysdba \'   schemas=user01      ESTIMATE_ONLY=Y NOLOGFILE=Y
alter session enable parallel dml
INSERT /*+APPEND parallel(16)*/  INTO user01.tab01 NOLOGGING   SELECT /*+PARALLEL(16)*/ *  from user01@dblink;

##replicat拆分
stop replicat 
#记录队列号、RBA号
info replicat 
#新建replicat,添加进程
add replicat rcx_sj02 ,exttrail ./dirdat/yc,checkpointtable ggs.ggs_checkpoint
#指定新建replicat队列文件、RBA
alter replicat rcx_sj02 extseqon 59860,extrba 39022343
start *

logdump

1
2
3
4
5
6
7
8
9
10
11
12
13
ghdr on
detail on
detail data
usertoken on
ggstoken on 
open dirdat/xr055993
pos  1441
sfh (查看下一条正常记录)
filter include filename  tablename
filter include string   '68F7DCC1630173453B6C04237F16FC3C' 
filter match all
show filter
scanforendtrans
ESC