前段時間，試驗學習部署了一套ogg(oracle godengate)，用于從mssql抽取資料到oracle；因為要同步的表較多，ogg部署起來很複雜，又涉及跨平台，對oggdirector，oggstudio之類也不太熟悉，一時半刻也指望不上。抱着先解決問題的思路（手工搞），部署了數項後發現ogg部署其實還挺簡單的，動手用python寫了一個小項目，使用python根據配置要求自動生成 ogg部署腳本，

配置檔案1 conf/configdb.ini

‘’’

#抽取\傳輸（源端）

[extract]

os=windows

dbtype=mssql

host=sourip

mgrport=7809

db=SQL_219

username=userxxx1

password=passwordxxx1

#複制（目标端）

[replicat]

os=linux

dbtype=oracle

host=destip

mgrport=7809

db=ORCL_14PDB1

username=userxxx2

password=passwordxxx2

#生成配置的存放目錄（可使用相對目錄或者絕對目錄）

[path]

deploy_path=./ogg/deploy

dirdef_path=./ogg/dirdef

dirprm_source=./ogg/dirprm_source

dirprm_target=./ogg/dirprm_target

‘’’

2. 配置檔案2 conf/configgroup.ini

‘’’

#複制組

[tablegroup]

trail=a

trailname=or01

#table映射

[tablemap]

dbo.table1=admin.table1

dbo.table2=admin.table2

‘’’

生成抽取ogg配置資訊 defgroup.py

‘’’

coding=utf-8

import os

import logging

import datetime

import shutil

import config

import exportfile

def main():

logger = logging.getLogger(“config”);

logger.setLevel(logging.DEBUG);

try:

os.curdir

if not os.path.exists(’./log’):

os.mkdir(’./log’);

if not os.path.exists(’./conf/bak’):

os.mkdir(’./conf/bak’);

except:

None;

fh = logging.FileHandler("./log/config.log", mode=‘a’);

fh.setLevel(logging.DEBUG);

formatter = logging.Formatter(’%(asctime)s-%(levelname)s-%(message)s’);

fh.setFormatter(formatter);

logger.addHandler(fh);

logger.info("開始生成程序組配置...");

nowTime=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')


#一、GetConfig
(src_os,src_dbtype,src_host,src_port,src_db,src_user,src_pwd,tgt_os,tgt_dbtype,tgt_host,tgt_port,tgt_db,tgt_user,tgt_pwd,deploy_path,dirdef_path,dirprm_source,dirprm_target,trailpath) = config.GetConfigdb(logger);
(defgen_table,map_table,trandata_table,truncate_table,trail,trailname) = config.GetConfigGroup(logger);
logger.info(trailname);

#--序列名
et = 'e' + trail[0]
rt = 'r' + trail[0]


#--建立目錄
if not os.path.exists(deploy_path):
	os.makedirs(deploy_path);
if not os.path.exists(dirprm_source):
	os.makedirs(dirprm_source);
if not os.path.exists(dirprm_target):
	os.makedirs(dirprm_target);
if not os.path.exists(dirdef_path):
	os.makedirs(dirdef_path);
		

#二、生成prm資訊
#-- 1.defgen
dirdef_file = 'dbo.' + et + '.def'
defgen_name = 'defgentable_' + et
defgen_file = defgen_name + '.prm'
defgen = ''
defgen = defgen + '-- 表定義\n'
defgen = defgen + '-- ' + nowTime + '\n'
defgen = defgen + 'DEFSFILE ./dirdef/' + dirdef_file + '\n'
defgen = defgen + 'SOURCEDB ' + src_db + ', USERID ' + src_user + ', PASSWORD ' + src_pwd + '\n'
defgen = defgen + defgen_table + '\n'
print (defgen);

#-- 2.einit
einit_name = 'einit_' + et
rinit_name = 'rinit_' + et
einit_file = einit_name + '.prm'
einit = ''
einit = einit + '-- 初始化抽取(直接推送)\n'
einit = einit + '-- ' + nowTime + '\n'
einit = einit + 'EXTRACT ' + einit_name + '\n'
einit = einit + 'SOURCEDB ' + src_db + ', USERID ' + src_user + ', PASSWORD ' + src_pwd + '\n'
einit = einit + 'RMTHOST ' + tgt_host + ', MGRPORT ' + tgt_port + '' + '\n'
einit = einit + 'RMTTASK REPLICAT, GROUP ' + rinit_name + '\n'
einit = einit + defgen_table + '\n'
print (einit);

#-- 3.rinit
rinit_file = rinit_name + '.prm'
rinit = ''
rinit = rinit + '-- 初始化日志應用(自動啟動)\n'
rinit = rinit + '-- ' + nowTime + '\n'
rinit = rinit + 'REPLICAT ' + rinit_name + '\n'
rinit = rinit + 'USERID ' + tgt_user + '@' + tgt_db + ', PASSWORD ' + tgt_pwd + '\n'
rinit = rinit + 'DISCARDFILE ./dirrpt/' + rinit_name + '.dsc, PURGE' + '\n'
rinit = rinit + 'SOURCEDEFS ./dirdef/' + dirdef_file + '\n'
rinit = rinit + map_table + '\n'
print (rinit);

#-- 4.inext
inext_name = 'inext_' + et
inext_file = inext_name + '.prm'
inext = ''
inext = inext + '-- 初始化抽取(中間檔案)\n'
inext = inext + '-- ' + nowTime + '\n'
inext = inext + 'SOURCEISTABLE' + '\n'
inext = inext + 'SOURCEDB ' + src_db + ', USERID ' + src_user + ', PASSWORD ' + src_pwd + '\n'
inext = inext + 'RMTHOST ' + tgt_host + ', MGRPORT ' + tgt_port + '\n'
inext = inext + 'RMTFILE ./dirdat/init/' + et + '\n'
inext = inext + defgen_table + '\n'
print (inext);

#-- 5.inload
inload_name = 'inload_' + et
inload_file = inload_name + '.prm'
inload = ''
inload = inload + '-- 初始化日志應用(人工啟動)\n'
inload = inload + '-- ' + nowTime + '\n'
inload = inload + 'SPECIALRUN' + '\n'
inload = inload + 'END RUNTIME' + '\n'
inload = inload + 'USERID ' + tgt_user + '@' + tgt_db + ', PASSWORD ' + tgt_pwd + '\n'
inload = inload + 'EXTFILE ./dirdat/init/' + et + '\n'
inload = inload + 'SOURCEDEFS ./dirdef/' + dirdef_file + '\n'
inload = inload + map_table + '\n'
print (inload);

#-- 6.ems
ems_name = 'ems_' + trailname
ems_file = ems_name + '.prm'
ems = ''
ems = ems + '-- 抽取程序\n'
ems = ems + '-- ' + nowTime + '\n'
ems = ems + 'EXTRACT ' + ems_name + '\n'
ems = ems + 'SETENV (NLS_LANG = "SIMPLIFIED CHINESE.ZHS16GBK" )' + '\n'
ems = ems + 'SOURCEDB ' + src_db + ', USERID ' + src_user + ', PASSWORD ' + src_pwd + '\n'
ems = ems + 'EXTTRAIL ./dirdat/' + trailpath + '/' + et + '\n'
ems = ems + '-- GETTRUNCATES' + '\n'
ems = ems + 'TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT' + '\n'
#ems = ems + 'REPORTFILE ./dirrpt/' + ems_name + '.rpt, APPEND, MEGABYTES 1024' + '\n'
ems = ems + 'REPORT AT 01:00' + '\n'
ems = ems + 'REPORTROLLOVER AT 02:00' + '\n'
ems = ems + 'REPORTCOUNT EVERY 30 MINUTES, RATE' + '\n'
ems = ems + 'DISCARDFILE ./dirrpt/' + ems_name + '.dsc, APPEND, MEGABYTES 1024' + '\n'
ems = ems + 'DISCARDROLLOVER AT 3:00' + '\n'
ems = ems + defgen_table + '\n'
print (ems);

#-- 7.pms
pms_name = 'pms_' + trailname
pms_file = pms_name + '.prm'
pms = ''
pms = pms + '-- 傳輸程序\n'
pms = pms + '-- ' + nowTime + '\n'
pms = pms + 'EXTRACT ' + pms_name + '\n'
pms = pms + 'PASSTHRU' + '\n'
pms = pms + 'RMTHOST ' + tgt_host + ', MGRPORT ' + tgt_port + ', COMPRESS' + '\n'
pms = pms + 'RMTTRAIL ./dirdat/' + trailpath + '/' + rt + '\n'
pms = pms + 'REPORT AT 01:00' + '\n'
pms = pms + 'REPORTROLLOVER AT 02:00' + '\n'
pms = pms + 'REPORTCOUNT EVERY 30 MINUTES, RATE' + '\n'
pms = pms + 'DISCARDFILE ./dirrpt/' + pms_name + '.dsc, APPEND, MEGABYTES 1024' + '\n'
pms = pms + 'DISCARDROLLOVER AT 3:00' + '\n'
pms = pms + defgen_table + '\n'
print (pms);

#-- 8.rms
rms_name = 'rms_' + trailname
rms_file = rms_name + '.prm'
rms = ''
rms = rms + '-- 日志應用程序\n'
rms = rms + '-- ' + nowTime + '\n'
rms = rms + 'REPLICAT ' + rms_name + '\n'
rms = rms + 'SETENV (NLS_ + '\n'
rms = rms + 'SETENV (ORACLE_SID="pdb1.petrochina.com")' + '\n'
rms = rms + 'SETENV (ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1/")' + '\n'
rms = rms + 'USERID ' + tgt_user + '@' + tgt_db + ', PASSWORD ' + tgt_pwd + '\n'
rms = rms + 'SOURCEDEFS ./dirdef/' + dirdef_file + '\n'
#rms = rms + 'REPORTFILE ./dirrpt/' + rms_name + '.rpt, APPEND, MEGABYTES 1024' + '\n'
rms = rms + 'REPORTAT AT 01:00' + '\n'
rms = rms + 'REPORTROLLOVERAT 02:00' + '\n'
rms = rms + 'REPORTCOUNT EVERY 30 MINUTES, RATE' + '\n'
rms = rms + 'DISCARDFILE ./dirrpt/' + rms_name + '.dsc, APPEND, MEGABYTES 1024' + '\n'
rms = rms + 'DISCARDROLLOVER AT 3:00' + '\n'
rms = rms + 'REPERROR DEFAULT, DISCARD' + '\n'
rms = rms + map_table + '\n'
print (rms);

#-- 9.生成實施配置指令
install_file = 'deploy_' + trailname + '.sql'
install = '' 
install = install + '-- ogg程序組簡要實施步驟及指令' + '\n'
install = install + '-- ' + nowTime + '\n'
install = install + '-- ' + src_dbtype + '2' + tgt_dbtype + '\n'
install = install + '-- trail:' + et + '\n'
install = install + '-- trailname:' + trailname + '\n'
install = install + '-- 根據需要自行調整參數！' + '\n' + '\n'


install = install + '\n'
install = install + '-- 一、生成表定義' + '\n'
install = install + '-- 1.源端生成表定義檔案 cmd\shell>' + '\n'
install = install + 'defgen PARAMFILE ./dirprm/' + defgen_file + '\n' + '\n'


install = install + '\n'
install = install + '-- 二、複制上述（5+3+1*2=10個）配置檔案到對應目錄 shell>' + '\n'
install = install + 'scp ./dirprm_source/' + defgen_file + ' [email protected]' + src_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + 'scp ./dirprm_source/' + einit_file + ' [email protected]' + src_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + 'scp ./dirprm_source/' + inext_file + ' [email protected]' + src_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + 'scp ./dirprm_source/' + ems_file + ' [email protected]' + src_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + 'scp ./dirprm_source/' + pms_file + ' [email protected]' + src_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + '\n'
install = install + 'scp ./dirprm_target/' + rinit_file + ' [email protected]' + tgt_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + 'scp ./dirprm_target/' + inload_file + ' [email protected]' + tgt_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + 'scp ./dirprm_target/' + rms_file + ' [email protected]' + tgt_host + ':${GG_HOME}/dirprm/' + '\n'
install = install + '\n'
install = install + 'scp ./dirdef/' + dirdef_file + ' [email protected]' + src_host + ':${GG_HOME}/dirdef/' + '\n'
install = install + 'scp ./dirdef/' + dirdef_file + ' [email protected]' + tgt_host + ':${GG_HOME}/dirdef/' + '\n' + '\n'


install = install + '\n'
install = install + '-- 三、初始化抽取(選擇1個方案即可)' + '\n'
install = install + '-- 初始化目标表(選用)' + '\n'
install = install + '/*' + '\n'
install = install + truncate_table
install = install + '*/' + '\n' + '\n'

install = install + '-- （一）直接初始化(推薦)' + '\n'
install = install + '-- 1.源端增加EXTRACT初始化程序組 ggsci>' + '\n'
install = install + 'ADD EXTRACT ' + einit_name + ', SOURCEISTABLE' + '\n' + '\n'

install = install + '-- 2.目标端增加REPLICAT初始化程序組 ggsci>' + '\n'
install = install + 'ADD REPLICAT ' + rinit_name + ', SPECIALRUN' + '\n' + '\n'

install = install + '-- 3.源端啟動初始化 ggsci>' + '\n'
install = install + 'START EXTRACT ' + einit_name + '\n' + '\n'

install = install + '-- 初始化監控 ggsci>' + '\n'
install = install + 'view report ' + einit_name + '\n'
install = install + 'view report ' + rinit_name + '\n' + '\n'

install = install + '-- （二）間接初始化(中間檔案)' + '\n'
install = install + '-- 1.源端提取初始化資料(生成中間檔案并上傳到目标端) cmd\shell>' + '\n'
install = install + 'extract PARAMFILE ./dirprm/' + inext_file + ' REPORTFILE ./dirrpt/' + inext_name +'.rpt' + '\n' + '\n'

install = install + '-- 2.目标端檢查中間檔案' + '\n'
install = install + 'll ${GG_HOME}/dirdat/init/' + '\n' + '\n'

install = install + '-- 3.目标端應用初始化資料 cmd\shell>' + '\n'
install = install + 'replicat PARAMFILE ./dirprm/' + inload_file + ' REPORTFILE ./dirrpt/' + inload_name +'.rpt' + '\n' + '\n'


install = install + '\n'
install = install + '-- 四、配置同步抽取' + '\n'
install = install + '-- 1.源端登入 cmd\shell>' + '\n'
install = install + 'DBLOGIN SOURCEDB ' + src_db + ', USERID ' + src_user + ', PASSWORD ' + src_pwd + '\n' + '\n'

install = install + '-- 2.添加抽取事務表 ggsci>' + '\n'
install = install + trandata_table + '\n'

install = install + '-- 3.添加抽取和傳輸程序 ggsci>' + '\n'
install = install + 'ADD EXTRACT ' + ems_name + ', tranlog, BEGIN NOW' + '\n'
install = install + 'ADD EXTTRAIL ./dirdat/' + trailpath + '/' + et + ', EXTRACT ' + ems_name + ', MEGABYTES 128' + '\n' + '\n'

install = install + 'ADD EXTRACT ' + pms_name + ', EXTTRAILSOURCE ./dirdat/' + trailpath + '/' + et + ', BEGIN NOW' + '\n'
install = install + 'ADD RMTTRAIL ./dirdat/' + trailpath + '/' + rt + ', EXTRACT ' + pms_name + ', MEGABYTES 128' + '\n' + '\n'

install = install + '-- 4.目标端登入 cmd\shell>' + '\n'
install = install + 'DBLOGIN USERID ' + tgt_user + '@' + tgt_db + ', PASSWORD ' + tgt_pwd + '\n' + '\n'

install = install + '-- 5.添加日志應用程序 ggsci>' + '\n'
install = install + 'ADD REPLICAT ' + rms_name + ', EXTTRAIL ./dirdat/' + trailpath + '/' + rt + ', CHECKPOINTTABLE ggadmin.chkpt' + '\n' + '\n'


install = install + '\n'
install = install + '-- 五、啟動同步程序' + '\n'
install = install + '-- 1.源端啟動抽取和傳輸程序 ggsci>' + '\n'
install = install + 'START EXTRACT ' + ems_name + '' + '\n'
install = install + 'START EXTRACT ' + pms_name + '' + '\n' + '\n'

install = install + '-- 2.目标端啟動日志應用程序 ggsci>' + '\n'
install = install + 'START REPLICAT ' + rms_name + '\n' + '\n'
print(install)


#三.導出配置檔案
exportfile.ExportFile(dirprm_source, defgen_file, defgen)

exportfile.ExportFile(dirprm_source, einit_file, einit)
exportfile.ExportFile(dirprm_target, rinit_file, rinit)
exportfile.ExportFile(dirprm_source, inext_file, inext)
exportfile.ExportFile(dirprm_target, inload_file, inload)

exportfile.ExportFile(dirprm_source, ems_file, ems)
exportfile.ExportFile(dirprm_source, pms_file, pms)
exportfile.ExportFile(dirprm_target, rms_file, rms)

exportfile.ExportFile(deploy_path, install_file, install)


#四、備份配置
shutil.copy("./conf/configdb.ini","./conf/bak/" + et + "_configdb.ini")
shutil.copy("./conf/configgroup.ini","./conf/bak/" + et + "_configgroup.ini")

logger.info("程序組配置成功完成!");

if name == “main”:

main();

‘’’

不知怎麼回事，還是不會markdown，或者是其對大段代碼支援不好，markdown上看起來挺淩亂的，

完整資源請移步 https://github.com/qing7416/python.git ，查閱 defogg 目錄

自動生成ogg部署配置腳本coding=utf-8

coding=utf-8

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List