1、ogg程序replicat进程部署文档OGG程序replicat进程部署文档一、准备工作需求:现需要在oracle数据库服务器上重新配置新的ogg的replicat进程接收源端传输的数据文件。1、检查ogg程序运行情况,停止replicat进程停止所有replicat进程 stop RQF_WP*mgr程序保持开启,源端传输数据文件过来能够接收文件。2、检查ogg程序配置文件 检查ogg全局变量参数文件view params ./GLOBALGGSCHEMA oggCHECKPOINTTABLE ogg.ckpt检查mgr配置文件view params mgrport 7809dynamicp
2、ortlist 7815-8000autorestart er *, retries 5, waitminutes 3purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 5 LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45检查没有问题后配置replicat进程和初始化数据二、配置replicat进程1、配置replicat进程参数文件由于这次同步的表比较多,所以总共部署了5个replicat进程配置RQF_WP1进程:replicat RQF_WP1SETENV (ORACL
3、E_SID=wpracdb1)SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)USERID ogg , PASSWORD AACAAAAAAAAAAAOABHCDPGBEKGVDABKFLGLIRCUJZDMANIIG, BLOWFISH, ENCRYPTKEY DEFAULTREPORT AT 01:59REPORTROLLOVER AT 02:00CACHEMGR, CACHESIZE 256MBREPERROR DEFAULT,ABENDDISCARDFILE ./dirrpt/RQF_WP1.dsc,APPEND,MEGABYTES 100DISC
4、ARDROLLOVER AT 06:00GETTRUNCATESNUMFILES 3000EOFDELAYCSECS 30ASSUMETARGETDEFSALLOWNOOPUPDATESDYNAMICRESOLUTIONGETUPDATEBEFORESNOCOMPRESSDELETESmap GS_CXTJ.DJ_ZRR,target oggsjjg.DJ_ZRR;map HX_CS_ZDY.CS_ZS_SWJGGKDZB,target oggsjjg.CS_ZS_SWJGGKDZB;map HX_DJ.DJ_NSRCKZHZHXX,target oggsjjg.DJ_NSRCKZHZHXX;
5、map HX_DJ.DJ_NSRCKZHZHXX_YGZ,target oggsjjg.DJ_NSRCKZHZHXX_YGZ;map HX_DJ.DJ_NSRXX,target oggsjjg.DJ_NSRXX;map HX_DJ.DJ_NSRXX_KZ,target oggsjjg.DJ_NSRXX_KZ;map HX_DJ.DJ_NSRXX_KZ_LS,target oggsjjg.DJ_NSRXX_KZ_LS;map HX_DJ.DJ_NSRXX_KZ_YGZ,target oggsjjg.DJ_NSRXX_KZ_YGZ;map HX_DJ.DJ_NSRXX_YGZ,target ogg
6、sjjg.DJ_NSRXX_YGZ;map HX_DJ.DJ_PZJGXX,target oggsjjg.DJ_PZJGXX;配置RQF_WP2进程:replicat RQF_WP2SETENV (ORACLE_SID=wpracdb1)SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)USERID ogg , PASSWORD AACAAAAAAAAAAAOABHCDPGBEKGVDABKFLGLIRCUJZDMANIIG, BLOWFISH, ENCRYPTKEY DEFAULTREPORT AT 01:59REPORTROLLOVER AT 02:00
7、CACHEMGR, CACHESIZE 256MBREPERROR DEFAULT,ABENDDISCARDFILE ./dirrpt/RQF_WP2.dsc,APPEND,MEGABYTES 100DISCARDROLLOVER AT 06:00GETTRUNCATESNUMFILES 3000EOFDELAYCSECS 30ASSUMETARGETDEFSALLOWNOOPUPDATESDYNAMICRESOLUTIONGETUPDATEBEFORESNOCOMPRESSDELETESmap HX_DJ.DJ_WTXYS_BWTDZNSRXX,target oggsjjg.DJ_WTXYS
8、_BWTDZNSRXX;map HX_DJ.DJ_WTXYSXX,target oggsjjg.DJ_WTXYSXX;map HX_DM_QG.DM_FP_FPLB,target oggsjjg.DM_FP_FPLB;map HX_DM_QG.DM_FP_FPZL,target oggsjjg.DM_FP_FPZL;map HX_DM_QG.DM_GY_HY,target oggsjjg.DM_GY_HY;map HX_DM_QG.DM_GY_NSRZT,target oggsjjg.DM_GY_NSRZT;map HX_DM_QG.DM_GY_ZSPM,target oggsjjg.DM_G
9、Y_ZSPM;map HX_DM_ZDY.DM_FP_FP,target oggsjjg.DM_FP_FP;map HX_DM_ZDY.DM_GY_SWJG,target oggsjjg.DM_GY_SWJG;map HX_DM_ZDY.DM_GY_SWRY,target oggsjjg.DM_GY_SWRY;配置RQF_WP3进程:replicat RQF_WP3SETENV (ORACLE_SID=wpracdb1)SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)USERID ogg , PASSWORD AACAAAAAAAAAAAOABHCDPGB
10、EKGVDABKFLGLIRCUJZDMANIIG, BLOWFISH, ENCRYPTKEY DEFAULTREPORT AT 01:59REPORTROLLOVER AT 02:00CACHEMGR, CACHESIZE 256MBREPERROR DEFAULT,ABENDDISCARDFILE ./dirrpt/RQF_WP3.dsc,APPEND,MEGABYTES 100DISCARDROLLOVER AT 06:00GETTRUNCATESNUMFILES 3000EOFDELAYCSECS 30ASSUMETARGETDEFSALLOWNOOPUPDATESDYNAMICRES
11、OLUTIONGETUPDATEBEFORESNOCOMPRESSDELETESmap HX_DM_ZDY.DM_GY_YHYYWD,target oggsjjg.DM_GY_YHYYWD;map HX_FP.FP_DBCKD_MX,target oggsjjg.FP_DBCKD_MX;map HX_FP.FP_DK_FPKJXX,target oggsjjg.FP_DK_FPKJXX;map HX_FP.FP_DK_SQ,target oggsjjg.FP_DK_SQ;map HX_FP.FP_DK_TYJDFP,target oggsjjg.FP_DK_TYJDFP;map HX_FP.F
12、P_DK_YSYSKMX,target oggsjjg.FP_DK_YSYSKMX;map HX_FP.FP_DK_ZZSFP,target oggsjjg.FP_DK_ZZSFP;map HX_FP.FP_DK_ZZSFP_HLMX,target oggsjjg.FP_DK_ZZSFP_HLMX;map HX_FP.FP_DK_ZZS_GHFNSRXX,target oggsjjg.FP_DK_ZZS_GHFNSRXX;map HX_FP.FP_FPKF,target oggsjjg.FP_FPKF;map HX_FP.FP_LY,target oggsjjg.FP_LY;配置RQF_WP4
13、进程:replicat RQF_WP4SETENV (ORACLE_SID=wpracdb1)SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)USERID ogg , PASSWORD AACAAAAAAAAAAAOABHCDPGBEKGVDABKFLGLIRCUJZDMANIIG, BLOWFISH, ENCRYPTKEY DEFAULTREPORT AT 01:59REPORTROLLOVER AT 02:00CACHEMGR, CACHESIZE 256MBREPERROR DEFAULT,ABENDDISCARDFILE ./dirrpt/RQF_
14、WP4.dsc,APPEND,MEGABYTES 100DISCARDROLLOVER AT 06:00GETTRUNCATESNUMFILES 3000EOFDELAYCSECS 30ASSUMETARGETDEFSALLOWNOOPUPDATESDYNAMICRESOLUTIONGETUPDATEBEFORESNOCOMPRESSDELETESmap HX_FP.FP_LY_MX,target oggsjjg.FP_LY_MX;map HX_FP.FP_NSRFPJC,target oggsjjg.FP_NSRFPJC;map HX_FP.FP_PZHDXX,target oggsjjg.
15、FP_PZHDXX;map HX_FP.FP_PZHDXXZB,target oggsjjg.FP_PZHDXXZB;map HX_FP.FP_SWJGKFKC,target oggsjjg.FP_SWJGKFKC;map HX_FP.FP_TP,target oggsjjg.FP_TP;map HX_FP.FP_TP_MX,target oggsjjg.FP_TP_MX;map HX_FP.FP_WLFP_KJXX,target oggsjjg.FP_WLFP_KJXX;map HX_FP.FP_WLFP_PZHDXX,target oggsjjg.FP_WLFP_PZHDXX;map HX
16、_FP.FP_WLFP_SFZRDXXB,target oggsjjg.FP_WLFP_SFZRDXXB;配置RQF_WP5进程:replicat RQF_WP5SETENV (ORACLE_SID=wpracdb1)SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)USERID ogg , PASSWORD AACAAAAAAAAAAAOABHCDPGBEKGVDABKFLGLIRCUJZDMANIIG, BLOWFISH, ENCRYPTKEY DEFAULTREPORT AT 01:59REPORTROLLOVER AT 02:00CACHEMGR,
17、CACHESIZE 256MBREPERROR DEFAULT,ABENDDISCARDFILE ./dirrpt/RQF_WP5.dsc,APPEND,MEGABYTES 100DISCARDROLLOVER AT 06:00GETTRUNCATESNUMFILES 3000EOFDELAYCSECS 30ASSUMETARGETDEFSALLOWNOOPUPDATESDYNAMICRESOLUTIONGETUPDATEBEFORESNOCOMPRESSDELETESmap HX_FP.FP_YZJH,target oggsjjg.FP_YZJH;map HX_FP.FP_YZJHFP,ta
18、rget oggsjjg.FP_YZJHFP;map HX_FP.FP_YZJHFP_MX,target oggsjjg.FP_YZJHFP_MX;map HX_FP.FP_YZRKXX,target oggsjjg.FP_YZRKXX;map HX_FP.FP_YZYBDWMCFPBAXX,target oggsjjg.FP_YZYBDWMCFPBAXX;map HX_QX.DM_QX_SWRYSF,target oggsjjg.DM_QX_SWRYSF;map HX_RD.RD_NSRZGXX_JGB,target oggsjjg.RD_NSRZGXX_JGB;map HX_RD.RD_N
19、SRZGXX_JGB_YGZ,target oggsjjg.RD_NSRZGXX_JGB_YGZ;map HX_RD.RD_SFZRDXXB,target oggsjjg.RD_SFZRDXXB;map HX_RD.RD_SFZRDXXB_YGZ,target oggsjjg.RD_SFZRDXXB_YGZ;map HX_SB.SB_SBXX,target oggsjjg.SB_SBXX;map HX_ZS.ZS_HD_DQDEHD_JG,target oggsjjg.ZS_HD_DQDEHD_JG;map HX_ZS.ZS_HD_DQDEHD_JG_YGZ,target oggsjjg.ZS
20、_HD_DQDEHD_JG_YGZ;map HX_ZS.ZS_YJSF,target oggsjjg.ZS_YJSF;注:在配置replicat进程时ogg用户密码是加密了,加密的方法如下:encrypt password AACAAAAAAAAAAA,encryptkey default在ggsci上运行,其中AACAAAAAAAAAAA为ogg用户的密码2、重新添加replicat进程由于传送数据文件名的格式发生了变化,故需要删除以前进程,重新创建进程登录数据库dblogin userid ogg,password AACAAAAAAAAAAA删除历史进程delete replicat R
21、QF_WP1delete replicat RQF_WP2delete replicat RQF_WP3delete replicat RQF_WP4添加新进程(新的数据文件名格式为zt开头)add replicat RQF_WP1,exttrail /app/ogg/dirdat/zt,checkpointtable ogg.ckptadd replicat RQF_WP2,exttrail /app/ogg/dirdat/zt,checkpointtable ogg.ckptadd replicat RQF_WP3,exttrail /app/ogg/dirdat/zt,checkpoin
22、ttable ogg.ckptadd replicat RQF_WP4,exttrail /app/ogg/dirdat/zt,checkpointtable ogg.ckpt历史进程只有4个,现部署了5个进程,所以还需添加一个进程add replicat RQF_WP5,exttrail /app/ogg/dirdat/zt,checkpointtable ogg.ckpt三、初始化数据1、在数据库服务器上创建新的用户名存放数据 创建表空间文件,导入的数据量比较大,故需要多创建几个表空间文件 Create tablespace OGGSJJG_DATA datafile +ORA_DATA/
23、wpracdb/datafile/oggsjjg_data_01.dbf size 20Gautoextend on next 500M maxsize 31G; alter tablespace OGGSJJG_DATA add datafile+ORA_DATA/wpracdb/datafile/oggsjjg_data_02.dbf size 20Gautoextend on next 500M maxsize 31G; alter tablespace OGGSJJG_DATA add datafile+ORA_DATA/wpracdb/datafile/oggsjjg_data_03
24、.dbf size 20Gautoextend on next 500M maxsize 31G; 创建新用户 create user oggsjjg identified by oggsjjg tablespace OGGSJJG_DATA; 创建目录及授权 create directory DP_DATA as /app/ogg/dirdat/;grant read,write on directory DP_DATA to oggsjjg;2、以oracle用户登录服务器,导入初始化数据 impdp oggsjjg/oggsjjg directory=DP_DATA dumpfile=y
25、j1001.dmp logfile=impdp_date +%Y%m%d%H.log remap_schema=GS_CXTJ:OGGSJJG,HX_CS_ZDY:OGGSJJG,HX_DJ:OGGSJJG,HX_DM_QG:OGGSJJG,HX_DM_ZDY:OGGSJJG,HX_FP:OGGSJJG,HX_QX:OGGSJJG,HX_RD:OGGSJJG,HX_SB:OGGSJJG,HX_ZS:OGGSJJG remap_tablespace=TS_GS_CXTJ_DATA:OGGSJJG_DATA,TS_HX_DJ_DATA:OGGSJJG_DATA,TS_HX_DM_DATA:OGGS
26、JJG_DATA,TS_HX_FPFX_DATA:OGGSJJG_DATA,TS_HX_SB_DATA:OGGSJJG_DATA,TS_HX_ZS_DATA:OGGSJJG_DATA,TS_HX_DJ_IDX:OGGSJJG_DATA,TS_HX_DM_IDX:OGGSJJG_DATA,TS_HX_FPFX_IDX:OGGSJJG_DATA,TS_HX_SB_IDX:OGGSJJG_DATA,TS_HX_ZS_IDX:OGGSJJG_DATA table_exists_action=replace cluster=no parallel=16;3、数据导入完成后,检查数据统计oggsjjg用户
27、下导入表的条数和源库中表的条数进行比对统计源库的表名如下HX_DJ.DJ_NSRCKZHZHXXHX_DJ.DJ_NSRXX_KZHX_DJ.DJ_NSRXX_KZ_LSHX_DJ.DJ_NSRXXHX_DJ.DJ_PZJGXXHX_DJ.DJ_WTXYSXXHX_QX.DM_QX_SWRYSFHX_FP.FP_DK_FPKJXXHX_FP.FP_DK_YSYSKMXHX_FP.FP_DK_SQHX_FP.FP_DK_ZZSFPHX_FP.FP_FPKFHX_FP.FP_LYHX_FP.FP_LY_MXHX_FP.FP_PZHDXXZBHX_RD.RD_SFZRDXXBHX_SB.SB_SBX
28、XHX_ZS.ZS_YJSFHX_DM_ZDY.DM_FP_FPHX_DM_QG.DM_FP_FPLBHX_DM_QG.DM_FP_FPZLHX_DM_QG.DM_GY_HYHX_DM_QG.DM_GY_NSRZTHX_DM_ZDY.DM_GY_SWJGHX_DM_ZDY.DM_GY_SWRYHX_DM_ZDY.DM_GY_YHYYWDHX_DM_QG.DM_GY_ZSPMHX_FP.FP_DK_TYJDFPHX_FP.FP_DK_ZZS_GHFNSRXXHX_FP.FP_DK_ZZSFP_HLMXHX_FP.FP_NSRFPJCHX_FP.FP_PZHDXXHX_FP.FP_SWJGKFKC
29、HX_FP.FP_TPHX_FP.FP_TP_MXHX_RD.RD_NSRZGXX_JGBHX_ZS.ZS_HD_DQDEHD_JGHX_RD.RD_SFZRDXXB_YGZHX_RD.RD_NSRZGXX_JGB_YGZHX_DM_QG.DM_FP_FPZLHX_DM_ZDY.DM_FP_FPHX_FP.FP_WLFP_KJXXHX_DJ.DJ_NSRCKZHZHXX_YGZHX_DJ.DJ_NSRXX_KZ_YGZHX_DJ.DJ_NSRXX_YGZHX_ZS.ZS_HD_DQDEHD_JG_YGZHX_FP.FP_DBCKD_MXHX_FP.FP_SWJGKFKCHX_FP.FP_YZJ
30、HFPHX_FP.FP_YZJHFP_MXHX_FP.FP_YZJHHX_FP.FP_YZRKXXHX_FP.FP_YZYBDWMCFPBAXXHX_FP.FP_WLFP_PZHDXXHX_FP.FP_WLFP_SFZRDXXBHX_DJ.DJ_WTXYS_BWTDZNSRXXHX_CS_ZDY.CS_ZS_SWJGGKDZB四、开启replicat进程 因源端忘记修改目标端IP地址导致ogg数据文件传送到其他主机,故需要将文件手工复制到本机来:scp oracle98.12.10.215:/app/oracle/ogg/dirdat/zt00028* /app/ogg/dirdatscp o
31、racle98.12.10.215:/app/oracle/ogg/dirdat/zt000290 /app/ogg/dirdat 修改replicat进程复制的起始文件alter replicat RQF_WP1,extseqno 000284,extrba 0alter replicat RQF_WP2,extseqno 000284,extrba 0alter replicat RQF_WP3,extseqno 000284,extrba 0alter replicat RQF_WP4,extseqno 000284,extrba 0alter replicat RQF_WP5,extseqno 000284,extrba 0 开启replicat进程,注意需要加上scn号start RQF_WP1, aftercsn 151*184start RQF_WP2,