ADD RMTTRAIL /ggs/dirdat/tr000003
ADD RMTTRAIL ./mt, EXTRACT extcdc1
ADD RMTTRAIL ./dirdat/tr, EXTRACT extcdc1
ADD RMTTRAIL ./dirdat/dt, EXTRACT extcdc1
----------------------------------------------
DELETE RMTTRAIL ./dirdat/dt
DELETE replicat reptrsf
ADD EXTTRAIL dirdat/aa, EXTRACT extcdc1
ADD EXTRACT datapump, EXTTRAILSOURCE dirdat/aa , begin now
ADD RMTTRAIL ./dirdat/dt, EXTRACT DATAPUMP
extract extcdc1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg@orcl,password ogg
rmthost 192.168.133.129,mgrport 7809
rmttrail ./dirdat/dt
table yxuser1.test1;
ADD EXTTRAIL ./dirdat/aa, EXTRACT EXTCDC1
-----------------------------zhuanhuan 源端-------------------------------
---抽取进程
add extract Datatrsf,tranlog,begin now,threads 1
ADD EXTTRAIL ./dirdat/df, EXTRACT Datatrsf
edit params datatrsf
extract datatrsf
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg@orcl,password ogg
--rmthost 192.168.133.129,mgrport 7809
EXTTRAIL ./dirdat/df
table yxuser2.test2;
---投递进程
ADD EXTRACT trsfpump, EXTTRAILSOURCE ./dirdat/df , begin now
ADD RMTTRAIL ./dirdat/df, EXTRACT trsfpump
edit params trsfpump
extract trsfpump
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
--userid ogg@orcl,password ogg
rmthost 192.168.133.129,mgrport 7809
rmttrail ./dirdat/df
table yxuser2.test2;
---生成表定义文件
edit params defgen
DEFSFILE ./dirdef/oltp.def
userid ogg@orcl,password ogg
table yxuser2.test2;
./defgen paramfile dirprm/defgen.prm
----------目标端mobiaoduan-------------------------------
dblogin userid ogg,password oracle
add checkpointtable ogg.ggschkpttrsf
add replicat reptrsf,exttrail ./dirdat/df,checkpointtable ogg.ggschkpttrsf
edit params reptrsf
REPLICAT reptrsf
sourcedefs ./dirdef/oltp.def
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
USERID ogg@orcl,PASSWORD ogg
DISCARDFILE ./dirrpt/reptrsf_gg2.dsc,PURGE
MAP yxuser2.test2, TARGET ssyxuser.sstable2, &
SQLEXEC (SPNAME ssyxuser.LOOKUP_ACCOUNT, &
ID lookup1, PARAMS (dqcode_in = name3)), &
COLMAP (USEDEFAULTS, &
ssid = ID, &
sname1 = @STREXT(name1,0,12), &
sname2 =@GETVAL (lookup1.dqcode_out), &
sname3 = name4, &
saddcol = @COMPUTE (ID + 100) );
-------创建存储过程------
CREATE OR REPLACE PROCEDURE LOOKUP_ACCOUNT(dqcode_in IN VARCHAR2, dqcode_out OUT VARCHAR2)
as
BEGIN
SELECT dqcode2
INTO dqcode_out
FROM SSYXUSER.dqmapping
WHERE dqcode1 = dqcode_in;
END;
-----测试存储过程-----
declare
realname number;
begin
lookup_account(101,realname);
DBMS_OUTPUT.PUT_LINE(REALNAME);
END;
--------------------------测试----------------
REPLICAT reptrsf
sourcedefs ./dirdef/oltp.def
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
USERID ogg@orcl,PASSWORD ogg
DISCARDFILE ./dirrpt/reptrsf_gg2.dsc,PURGE
MAP yxuser2.test2, TARGET ssyxuser.sstable2, &
SQLEXEC (id lookup, &
QUERY " SELECT dqcode2 into desc_param FROM ssyxuser.dqmapping " &
" WHERE dqcode1 = :dqcode_in " , &
PARAMS (dqcode_in = name3)), &
COLMAP (USEDEFAULTS, &
ssid = ID, &
sname1 = @STREXT(name1,0,12), &
sname2 = lookup.desc_param , &
sname3 = name4, &
saddcol = @COMPUTE (ID + 100) );
REPLICAT reptrsf
sourcedefs ./dirdef/oltp.def
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
USERID ogg@orcl,PASSWORD ogg
DISCARDFILE ./dirrpt/reptrsf_gg2.dsc,PURGE
MAP yxuser2.test2, TARGET ssyxuser.sstable2, &
SQLEXEC (SPNAME ssyxuser.LOOKUP_ACCOUNT, ID lookup1, PARAMS (dqcode_in = ID)), &
COLMAP (USEDEFAULTS, &
ssid = ID, &
sname1 = @STREXT(name1,0,12), &
sname2 =@GETVAL (lookup1.dqcode_out), &
sname3 = name4, &
saddcol = @COMPUTE (ID + 100) );