oracle写入数据库
❶ 在oracle中怎样把一个数据库中的一张表的数据复制到另一个数据库中的一张表中
1、使用plsql连接上数据库，展开【Tables】菜单项，下面出现的就是数据库表。
❷ 镐庢牱灏唎racle鏁版嵁搴扑腑镄勪竴涓琛ㄥ煎叆鍒板彟涓涓链嶅姟鍣ㄦ暟鎹搴扑腑锛
涓昏侀氲繃鍑犱笅锻戒护锛
exp/imp
expdp/impdp
dblink
spool/sqlldr
pl/sql瀵煎嚭insert璇鍙
❸ oracle鏁版嵁镐庝箞杩佺Щ鍒癿ysql鏁版嵁搴
OGG鍏ㄧО涓篛racle GoldenGate,鏄鐢监racle瀹樻柟鎻愪緵镄勭敤浜庤В鍐冲纾鏋勬暟鎹鐜澧冧腑鏁版嵁澶嶅埗镄勪竴涓鍟嗕笟宸ュ叿銆傜浉姣斾簬鍏跺畠杩佺Щ宸ュ叿OGG镄勪紭锷垮湪浜庡彲浠ョ洿鎺ヨВ鏋愭簮绔疧racle镄剅edo log锛屽洜姝よ兘澶熷疄鐜板湪涓嶉渶瑕佸瑰师琛ㄧ粨鏋勫仛澶澶氲皟鏁寸殑鍓嶆彁涓嫔畬鎴愭暟鎹澧为噺閮ㄥ垎镄勮縼绉汇傛湰绡囨枃绔犲皢閲岖偣浠嬬粛濡备綍浣跨敤OGG瀹炵幇Oracle鍒癕ySQL鏁版嵁镄勫钩婊戣縼绉伙纴浠ュ强璁茶堪涓浜哄湪杩佺Щ杩囩▼涓镓纰板埌闂棰樼殑瑙ｅ喅鏂规堛
锛堜竴锛塐GG阃昏緫鏋舵瀯
鍙傜収涓婂浘绠鍗旷粰澶у朵粙缁崭笅OGG阃昏緫鏋舵瀯锛岃╁ぇ瀹跺筄GG鏁版嵁钖屾ヨ繃绋嬫湁涓绠鍗曚简瑙ｏ纴钖庨溃绔犺妭浼氲︾粏婕旂ず鐩稿叧杩涚▼镄勯厤缃鏂瑰纺锛屽湪OGG浣跨敤杩囩▼涓涓昏佹秹鍙娄互涓嬭繘绋嫔强鏂囦欢锛
Manager杩涚▼锛氶渶瑕佹簮绔璺熺洰镙囩钖屾椂杩愯岋纴涓昏佷綔鐢ㄦ槸鐩戞带绠＄悊鍏跺畠杩涚▼锛屾姤锻婇敊璇锛屽垎閰嶅强娓呯悊鏁版嵁瀛桦偍绌洪棿锛屽彂甯冮槇鍊兼姤锻婄瓑
Extract杩涚▼锛氲繍琛屽湪鏁版嵁搴撴簮绔锛屼富瑕佺敤浜庢崟銮锋暟鎹镄勫彉鍖栵纴璐熻矗鍏ㄩ噺銆佸为噺鏁版嵁镄勬娊鍙
Trails鏂囦欢锛氢复镞跺瓨鏀惧湪纾佺洏涓婄殑鏁版嵁鏂囦欢
Data Pump杩涚▼锛氲繍琛屽湪鏁版嵁搴撴簮绔锛屽睘浜嶦xtract杩涚▼镄勪竴涓杈呭姪杩涚▼锛屽傛灉涓嶉厤缃瓺ata Pump锛孍xtract杩涚▼浼氩皢鎶藉彇镄勬暟鎹鐩存帴鍙戦佸埌鐩镙囩镄凾rail鏂囦欢锛屽傛灉閰岖疆浜咲ata Pump锛孍xtract杩涚▼浼氩皢鏁版嵁鎶藉彇鍒版湰鍦瘪rail鏂囦欢锛岀劧钖庨氲繃Data Pump杩涚▼鍙戦佸埌鐩镙囩锛岄厤缃瓺ata Pump杩涚▼镄勪富瑕佸ソ澶勬槸鍗充娇婧愮鍒扮洰镙囩鍙戠敓缃戠粶涓鏂锛孍xtract杩涚▼渚濈劧涓崭细缁堟
Collector杩涚▼锛氭帴鏀舵簮绔浼犺緭杩囨潵镄勬暟鎹鍙桦寲锛屽苟鍐椤叆链鍦瘪rail鏂囦欢涓
Replicat杩涚▼锛氲诲彇Trail鏂囦欢涓璁板綍镄勬暟鎹鍙桦寲锛屽垱寤哄瑰簲镄凞ML璇鍙ュ苟鍦ㄧ洰镙囩锲炴斁
纭璁MySQL绔琛ㄧ粨鏋勫凡缁忓瓨鍦
MySQL鏁版嵁搴揙GG鐢ㄦ埛鍒涘缓
mysql> create user 'ogg'@'%' identified by 'ogg';
mysql> grant all on *.* to 'ogg'@'%';
#### 鎻愬墠鍒涘缓濂给gg瀛樻斁checkpoint琛ㄧ殑鏁版嵁搴
mysql> create database ogg;
shell> cd $OGG_HOME
shell> ggsci
ggsci> edit param ./GLOBALS
checkpointtable ogg.ggs_checkpoint
ggsci> dblogin sourcedb [email protected]:3306 userid ogg
ggsci> add checkpointtable ogg.ggs_checkpoint
#### 鍒囨崲镊硂gg杞浠剁洰褰曞苟镓ц実gsci杩涘叆锻戒护琛岀粓绔
shell> cd $OGG_HOME
shell> ggsci
#### 娣诲姞涓涓锲炴斁绾跨▼骞朵笌婧愮痯ump杩涚▼浼犺緭杩囨潵镄则rail鏂囦欢鍏宠仈锛屽苟浣跨敤checkpoint琛ㄧ‘淇濇暟鎹涓崭涪澶
ggsci> add replicat r_cms,exttrail /opt/ogg/dirdat/ms,checkpointtable ogg.ggs_checkpoint
#### 澧炲姞/缂栬緫锲炴斁杩涚▼閰岖疆鏂囦欢
ggsci> edit params r_cms
replicat r_cms
targetdb [email protected]:3306,userid ogg,password ogg
sourcedefs /opt/ogg/dirdef/cms.def
discardfile /opt/ogg/dirrpt/r_cms.dsc,append,megabytes 1024
HANDLECOLLISIONS
MAP cms.*,target cms.*;
#### 鍒囨崲镊硂gg杞浠剁洰褰曞苟镓ц実gsci杩涘叆锻戒护琛岀粓绔
shell> cd $OGG_HOME
shell> ggsci
#### 澧炲姞/缂栬緫鍏ㄩ噺鎶藉彇杩涚▼閰岖疆鏂囦欢
#### 鍏朵腑RMTFILE鎸囧畾鎶藉彇镄勬暟鎹鐩存帴浼犻佸埌杩灭瀵瑰簲鐩褰曚笅
#### 娉ㄦ剰锛歊MTFILE鍙傛暟鎸囧畾镄勬枃浠跺彧鏀鎸2浣嶅瓧绗︼纴濡傛灉瓒呰繃replicat鍒欐棤娉曡瘑鍒
ggsci> edit params ei_cms
SOURCEISTABLE
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_SID=cms)
SETENV (ORACLE_HOME=/data/oracle/11.2/db_1)
USERID ogg@appdb,PASSWORD ogg
RMTHOST 17X.1X.84.121,MGRPORT 7809
RMTFILE /opt/ogg/dirdat/ms,maxfiles 100,megabytes 1024,purge
TABLE cms.*;
#### 钖锷ㄥ苟镆ョ湅鎶藉彇杩涚▼姝ｅ父
shell> nohup ./extract paramfile ./dirprm/ei_cms.prm reportfile ./dirrpt/ei_cms.rpt &
## 镆ョ湅镞ュ织鏄钖︽ｅ父杩涜屽叏閲忔娊鍙
shell> tail -f ./dirrpt/ei_cms.rpt
#### 鍒囨崲镊硂gg杞浠剁洰褰曞苟镓ц実gsci杩涘叆锻戒护琛岀粓绔
shell> cd $OGG_HOME
shell> ggsci
ggsci> edit params ri_cms
SPECIALRUN
END RUNTIME
TARGETDB [email protected]:3306,USERID ogg,PASSWORD ogg
EXTFILE /opt/ogg/dirdat/ms
DISCARDFILE ./dirrpt/ri_cms.dsc,purge
MAP cms.*,TARGET cms.*;
#### 钖锷ㄥ苟镆ョ湅锲炴斁杩涚▼姝ｅ父
shell> nohup ./replicat paramfile ./dirprm/ri_cms.prm reportfile ./dirrpt/ri_cms.rpt &
#### 镆ョ湅镞ュ织鏄钖︽ｅ父杩涜屽叏閲忓洖鏀
shell> tail -f ./dirrpt/ri_cms.rpt
镙规嵁瀹为檯瀛桦偍鏁版嵁镄勯暱搴︼纴瀵硅秴闀跨殑varchar鍒楄繘琛屾敹缂╋绂
瀵逛簬镞犳硶鏀剁缉镄勫垪杞鎹㈡暟鎹绫诲瀷涓篓ext锛屼絾杩椤湪浣跨敤杩囩▼涓鍙鑳藉艰嚧涓浜涙ц兘闂棰桡绂
mysql>set global foreign_key_checks=off;
鐩镙囩涓嶅瓨鍦╠elete璇鍙ョ殑璁板綍锛屽拷鐣ヨラ梾棰桦苟涓嶈板綍鍒癫iscardfile
鐩镙囩涓㈠けupdate璁板綍
- 镟存柊镄勬槸涓婚敭鍊硷纴update杞鎹㈡垚insert
- 镟存柊镄勯敭鍊兼槸闱炰富阌锛屽拷鐣ヨラ梾棰桦苟涓嶈板綍鍒癫iscardfile
鐩镙囩閲嶅峣nsert宸插瓨鍦ㄧ殑涓婚敭鍊硷纴杩椤皢琚玶eplicat杩涚▼杞鎹涓篣PDATE鐜版湁涓婚敭鍊肩殑琛
鎶ラ敊淇℃伅
2019-03-08 06:15:22 ERROR OGG-01201 Error reported by MGR : Access denied.
鎶ラ敊铡熷洜
瑙ｅ喅锷炴硶
## 琛ㄧず璇mgr鑺傜偣鍏佽(ALLOW)10.186缃戞(IPADDR)镄勬墍链夌被鍨嬬▼搴(PROG *)杩涜岃繛鎺ヨ块梾ACCESSRULE, PROG *, IPADDR 10.186.*.*, ALLOW
鎶ラ敊淇℃伅
2019-03-15 14:49:04 ERROR OGG-01192 Trying to use RMTTASK on data types which may be written as LOB chunks (Table: 'UNIONPAYCMS.CMS_OT_CONTENT_RTF').
鎶ラ敊铡熷洜
瑙ｅ喅鏂规硶
浜屻佽縼绉绘柟妗
锛堜竴锛夌幆澧冧俊鎭
OGG鐗堟湰 OGG 12.2.0.2.2 For Oracle OGG 12.2.0.2.2 For MySQL
鏁版嵁搴撶増链 Oracle 11.2.0.4 MySQL 5.7.21
OGG_HOME /home/oracle/ogg /opt/ogg
锛堜簩锛夎〃缁撴瀯杩佺Щ
琛ㄧ粨鏋勮縼绉诲睘浜庨毦搴︿笉楂树絾鍐呭规瘆杈幂箒鐞愮殑涓姝ワ纴鎴戜滑鍦ㄨ縼绉昏〃缁撴瀯镞朵娇鐢ㄤ简涓涓鍙玸qlines镄勫紑婧愬伐鍏凤纴瀵逛簬sqlines宸ュ叿鍦∕ySQL绔鍒涘缓澶辫触鍙娄笉绗﹀悎棰勬湡镄勮〃缁撴瀯鍐嶈繘琛岀壒娈婂勭悊锛屼互姝ゆ潵鎻愰珮琛ㄧ粨鏋勮浆鎹㈢殑鏁堢巼銆
娉ㄦ剰锛歄GG鍦∣racle杩佺ЩMySQL镄勫満鏅涓嬩笉鏀鎸丏DL璇鍙ュ悓姝ワ纴锲犳よ〃缁撴瀯杩佺Щ瀹屾垚钖庡埌鏁版嵁搴揿垏鎹㈠墠灏介噺涓嶈佸啀淇鏀硅〃缁撴瀯銆
锛堜笁锛夋暟鎹杩佺Щ
鏁版嵁钖屾ョ殑镎崭綔鍧囬噰鐢∣GG宸ュ叿杩涜岋纴钥冭槛鏁版嵁鍏ㄩ噺鍜屽为噺镄勮旀帴锛孙GG闇瑕佸厛灏嗗为噺钖屾ョ殑鎶藉彇杩涚▼钖锷锛屾姄鍙栨暟鎹搴撶殑redo log锛屽緟鍏ㄩ噺鎶藉彇缁撴潫钖庡紑钖澧为噺鏁版嵁锲炴斁锛屽簲鐢ㄥ叏閲忓拰澧为噺杩欐垫湡闂翠骇鐢熺殑镞ュ织鏁版嵁锛孙GG鍙锘轰簬鍙傛暟閰岖疆杩涜岄吨澶嶆暟鎹澶勭悊锛屾墍浠ヤ娇鐢∣GG镞朵紭鍏埚皢澧为噺杩涜岄厤缃骞跺惎鐢ㄣ傛ゅ栵纴涓轰简阆垮厤链绔犺妭绡囧箙杩囬暱锛孙GG鍙傛暟灏嗕笉鍐嶈В閲婏纴链夐渶瑕佺殑链嫔弸鍙浠ユ煡鐪嫔畼鏂规彁渚涚殑Reference鏂囨。镆ヨ浠讳綍浣犱笉鐞呜В镄勫弬鏁般
1.婧愮疧GG閰岖疆
锛1锛塐racle鏁版嵁搴挞厤缃
阍埚筄racle鏁版嵁搴掳纴OGG闇瑕佹暟鎹搴揿紑钖褰掓。妯″纺鍙婂炲姞杈呭姪琛ュ厖镞ュ织銆佸己鍒惰板綍镞ュ织绛夋潵淇濋㱩OGG鍙鎶揿彇鍒板畬鏁寸殑镞ュ织淇℃伅
镆ョ湅褰揿墠鐜澧冩槸钖︽弧瓒宠佹眰锛岃緭鍑虹粨鏋滃备笅锲炬墍绀猴细
锛2锛塐racle鏁版嵁搴揙GG鐢ㄦ埛鍒涘缓
OGG闇瑕佹湁涓涓鐢ㄦ埛链夋潈闄愬规暟鎹搴撶殑鐩稿叧瀵硅薄锅氭搷浣滐纴浠ヤ笅涓烘秹鍙婄殑𨱒冮檺锛岃ョず渚嫔皢鍒涘缓涓涓鐢ㄦ埛钖嶅拰瀵嗙爜鍧囦负ogg镄凮racle鏁版嵁搴撶敤鎴峰苟鎺堜篑浠ヤ笅𨱒冮檺
锛3锛夋簮绔疧GG 绠＄悊杩涚▼(MGR)閰岖疆
锛4锛夋簮绔疧GG 琛ㄧ骇琛ュ叏镞ュ织(trandata)閰岖疆
琛ㄧ骇琛ュ叏镞ュ织闇瑕佸湪链灏忚ˉ鍏ㄦ棩蹇楁墦寮镄勬儏鍐典笅镓嶈捣浣灭敤,涔嫔墠鍙鍦ㄦ暟鎹搴撶骇寮钖浜嗘渶灏忚ˉ鍏ㄦ棩蹇(alter database add supplemental log data;)锛宺edolog璁板綍镄勪俊鎭杩树笉澶熷叏闱锛屽繀椤诲啀浣跨敤add trandata寮钖琛ㄧ骇镄勮ˉ鍏ㄦ棩蹇椾互銮峰缑蹇呰佺殑淇℃伅銆
锛5锛夋簮绔疧GG 鎶藉彇杩涚▼(extract)閰岖疆
Extract杩涚▼杩愯屽湪鏁版嵁搴撴簮绔锛岃礋璐ｄ粠婧愮鏁版嵁琛ㄦ垨镞ュ织涓鎹曡幏鏁版嵁銆侲xtract杩涚▼鍒╃敤鍏跺唴鍦ㄧ殑checkpoint链哄埗锛屽懆链熸у湴妫镆ュ苟璁板綍鍏惰诲啓镄勪綅缃锛岄氩父鏄鍐椤叆鍒版湰鍦扮殑trail鏂囦欢銆傝繖绉嶆満鍒舵槸涓轰简淇濊瘉濡傛灉Extract杩涚▼缁堟㈡垨钥呮搷浣灭郴缁熷畷链猴纴鎴戜滑閲嶅惎Extract杩涚▼钖庯纴GoldenGate鑳藉熸仮澶嶅埌浠ュ墠镄勭姸镐侊纴浠庝笂涓涓鏂镣瑰勭户缁寰涓嬭繍琛岋纴钥屼笉浼氭湁浠讳綍鏁版嵁鎹熷け銆
锛6锛夋簮绔疧GG 浼犺緭杩涚▼(pump)閰岖疆
pump杩涚▼杩愯屽湪鏁版嵁搴撴簮绔锛屽叾浣灭敤闱炲父绠鍗曘傚傛灉婧愮镄凟xtract鎶藉彇杩涚▼浣跨敤浜嗘湰鍦皌rail鏂囦欢锛岄偅涔坧ump杩涚▼灏变细鎶妕rail鏂囦欢浠ユ暟鎹鍧楃殑褰㈠纺阃氲繃TCP/IP鍗忚鍙戦佸埌鐩镙囩锛孭ump杩涚▼链璐ㄤ笂鏄疎xtract杩涚▼镄勪竴绉岖壒娈婂舰寮忥纴濡傛灉涓崭娇鐢╰rail鏂囦欢锛岄偅涔圗xtract杩涚▼鍦ㄦ娊鍙栧畬鏁版嵁钖庯纴鐩存帴鎶曢掑埌鐩镙囩銆
琛ュ厖锛歱ump杩涚▼钖锷ㄦ椂闇瑕佷笌鐩镙囩镄刴gr杩涚▼杩涜岃繛鎺ワ纴镓浠ラ渶瑕佷紭鍏埚皢鐩镙囩镄刴gr鎻愬墠閰岖疆濂斤纴钖﹀垯浼氭姤阌栾繛鎺ヨ𨰾掔粷锛屾棤娉曚紶杈撴娊鍙栫殑镞ュ织鏂囦欢鍒扮洰镙囩瀵瑰簲鐩褰曚笅
锛7锛夋簮绔疧GG 寮傛瀯mapping鏂囦欢(defgen)鐢熸垚
璇ユ枃浠惰板綍浜嗘簮搴挞渶瑕佸嶅埗镄勮〃镄勮〃缁撴瀯瀹氢箟淇℃伅锛屽湪婧愬簱鐢熸垚璇ユ枃浠跺悗闇瑕佹嫹璐濆埌鐩镙囧簱镄刣irdef鐩褰曪纴褰撶洰镙囧簱镄剅eplica杩涚▼灏嗕紶杈撹繃𨱒ョ殑鏁版嵁apply鍒扮洰镙囧簱镞堕渶瑕佽诲啓璇ユ枃浠讹纴钖屾瀯镄勬暟鎹搴扑笉闇瑕佽繘琛岃ユ搷浣溿
2.鐩镙囩疧GG閰岖疆
锛1锛夌洰镙囩疢ySQL鏁版嵁搴挞厤缃
锛2锛夌洰镙囩疧GG 绠＄悊杩涚▼(MGR)閰岖疆
鐩镙囩镄凪GR杩涚▼鍜屾簮绔閰岖疆涓镙凤纴鍙鐩存帴灏嗘簮绔閰岖疆鏂瑰纺鍦ㄧ洰镙囩閲嶅嶆墽琛屼竴娆″嵆鍙锛岃ラ儴鍒嗕笉鍦ㄨ禈杩
锛3锛夌洰镙囩疧GG 妫镆ョ偣镞ュ织琛(checkpoint)閰岖疆
checkpoint琛ㄧ敤𨱒ヤ缭闅滀竴涓浜嫔姟镓ц屽畬鎴愬悗锛屽湪MySQL鏁版嵁搴扑粠链変竴寮犺〃璁板綍褰揿墠镄勬棩蹇楀洖鏀剧偣锛屼笌MySQL澶嶅埗璁板綍binlog镄凣TID鎴杙osition镣圭被浼笺
#### 鍒囨崲镊硂gg杞浠剁洰褰曞苟镓ц実gsci杩涘叆锻戒护琛岀粓绔
锛4锛夌洰镙囩疧GG 锲炴斁绾跨▼(replicat)閰岖疆
Replicat杩涚▼杩愯屽湪鐩镙囩锛屾槸鏁版嵁鎶曢掔殑链钖庝竴绔欙纴璐熻矗璇诲彇鐩镙囩疶rail鏂囦欢涓镄勫唴瀹癸纴骞跺皢瑙ｆ瀽鍏惰В鏋愪负DML璇鍙ワ纴铹跺悗搴旂敤鍒扮洰镙囨暟鎹搴扑腑銆
娉ㄦ剰锛歳eplicat杩涚▼鍙闇閰岖疆瀹屾垚锛屾棤闇钖锷锛屽緟鍏ㄩ噺鎶藉彇瀹屾垚钖庡啀钖锷ㄣ
镊虫ゆ簮绔鐜澧冮厤缃瀹屾垚
寰呭叏閲忔暟鎹鎶藉彇瀹屾瘯钖庡惎锷ㄧ洰镙囩锲炴斁杩涚▼鍗冲彲瀹屾垚鏁版嵁鍑嗗疄镞跺悓姝ャ
3.鍏ㄩ噺钖屾ラ厤缃
鍏ㄩ噺鏁版嵁钖屾ヤ负涓娆℃ф搷浣滐纴褰揙GG杞浠堕儴缃插畬鎴愬强澧为噺鎶藉彇杩涚▼閰岖疆骞跺惎锷ㄥ悗锛屽彲閰岖疆1涓鐗规畩镄别xtract杩涚▼浠庤〃涓鎶藉彇鏁版嵁锛屽皢鎶藉彇镄勬暟鎹淇濆瓨鍒扮洰镙囩鐢熸垚鏂囦欢锛岀洰镙囩钖屾椂钖锷ㄤ竴涓鍗曟¤繍琛岀殑replicat锲炴斁杩涚▼灏嗘暟鎹瑙ｆ瀽骞跺洖鏀捐呖鐩镙囨暟鎹搴扑腑銆
锛1锛夋簮绔疧GG 鍏ㄩ噺鎶藉彇杩涚▼(extract)閰岖疆
锛2锛夌洰镙囩疧GG 鍏ㄩ噺锲炴斁杩涚▼(replicat)閰岖疆
涓夈佹暟鎹镙￠獙
鏁版嵁镙￠獙鏄鏁版嵁杩佺Щ杩囩▼涓蹇呬笉鍙灏戠殑鐜鑺傦纴链绔犺妭鎻愪緵缁椤嚑涓鏁版嵁镙￠獙镄勬濊矾鍏卞ぇ瀹跺弬鏁帮纴镙￠獙鏂瑰纺鍙浠ョ敱浠ヤ笅鍑犱釜瑙掑害铡诲疄鐜帮细
1.阃氲繃OGG镞ュ织镆ョ湅鍏ㄩ噺銆佸为噺杩囩▼涓璬iscards璁板綍鏄钖︿负0𨱒ュ垽鏂鏄钖︿涪澶辨暟鎹锛
2.阃氲繃瀵规簮绔銆佺洰镙囩镄勮〃镓ц宑ount鍒ゆ柇鏁版嵁閲忔槸钖︿竴镊达绂
3.缂栧啓绫讳技浜巅t-table-checksum镙￠獙铡熺悊镄勭▼搴忥纴瀹炵幇琛岀骇鍒涓镊存ф牎楠岋纴杩欑嶆柟寮忎紭缂虹偣鐗瑰埆鏄庢樉锛屼紭镣规槸鑳藉熷畬鍏ㄥ嗳纭瀵规暟鎹鍐呭硅繘琛屾牎楠岋纴缂虹偣鏄闇瑕侀亶铡嗘疮涓琛屾暟鎹锛屾牎楠屾垚链杈冮珮锛
4.鐩稿规姌涓镄勬暟鎹镙￠獙鏂瑰纺鏄阃氲繃涓氩姟瑙掑害锛屾彁鍓岖紪鍐椤ソ鏁板崄涓杩斿洖缁撴灉杈冨揩镄凷QL锛屼粠涓氩姟瑙掑害鎶芥牱镙￠獙銆
锲涖佽縼绉婚梾棰桦勭悊
链绔犺妭灏呜茶堪杩佺Щ杩囩▼涓纰板埌镄勪竴浜涢梾棰桦强鐩稿簲镄勮В鍐虫柟寮忋
锛堜竴锛塎ySQL闄愬埗
鍦∣racle鍒癕ySQL镄勮〃缁撴瀯杩佺Щ杩囩▼涓涓昏佺板埌浠ヤ笅涓や釜闄愬埗锛
1. Oracle绔镄勮〃缁撴瀯锲犱负链鍒濊捐′笉涓ヨ皑锛屽瓨鍦ㄥぇ閲忕殑鍒椾娇鐢╲archar(4000)鏁版嵁绫诲瀷锛屽艰嚧杩佺Щ鍒癕ySQL钖庤秴鍑鸿岄檺鍒讹纴琛ㄧ粨鏋勬棤娉曞垱寤恒傜敱浜崄ySQL链韬鏁版嵁缁撴瀯镄勯檺鍒讹纴涓涓16K镄勬暟鎹椤垫渶灏戣佸瓨鍌ㄤ袱琛屾暟鎹锛屽洜姝ゅ崟琛屾暟鎹涓嶈兘瓒呰繃65,535 bytes锛屽洜姝ら拡瀵硅繖绉嶆儏鍐垫湁涓ょ嶈В鍐虫柟寮忥细
2. 涓庣涓镣圭被浼硷纴鍦↖nnodb瀛桦偍寮曟搸涓锛岀储寮曞墠缂闀垮害闄愬埗鏄767 bytes锛岃嫢浣跨敤DYNAMIC銆丆OMPRESSED琛屾牸寮忎笖寮钖痠nnodblargeprefix镄勫満鏅涓嬶纴杩欎釜闄愬埗鏄3072 bytes锛屽嵆浣跨敤utf8mb4瀛楃﹂泦镞讹纴链澶氩彧鑳藉箆archar(768)镄勫垪鍒涘缓绱㈠紩锛
3. 浣跨敤ogg鍏ㄩ噺鍒濆嫔寲钖屾ユ椂锛岃嫢瀛桦湪澶栭敭绾︽潫锛屾壒閲忓煎叆镞剁敱浜庡悇琛ㄧ殑鎻掑叆椤哄簭涓嶅敮涓锛屽彲鑳藉瓙琛ㄥ厛鎻掑叆鏁版嵁钥屼富琛ㄨ缮链鎻掑叆锛屽艰嚧鎶ラ敊瀛愯〃渚濊禆镄勮板綍涓嶅瓨鍦锛屽洜姝ゅ缓璁鏁版嵁杩佺Щ阒舵电佺敤涓诲栭敭绾︽潫锛屽緟杩佺Щ缁撴潫钖庡啀镓揿紑銆
锛堜簩锛夊叏閲忎笌澧为噺琛旀帴
HANDLECOLLISIONS鍙傛暟鏄瀹炵幇OGG鍏ㄩ噺鏁版嵁涓庡为噺鏁版嵁琛旀帴镄勫叧阌锛屽叾瀹炵幇铡熺悊鏄鍦ㄥ叏閲忔娊鍙栧墠鍏埚紑钖澧为噺鎶藉彇杩涚▼锛屾姄铡诲叏閲忓簲鐢ㄦ湡闂翠骇鐢熺殑redo log锛屽綋鍏ㄩ噺搴旂敤瀹屾垚钖庯纴寮钖澧为噺锲炴斁杩涚▼锛屽簲鐢ㄥ叏閲忔湡闂寸殑澧为噺鏁版嵁銆备娇鐢ㄨュ弬鏁板悗澧为噺锲炴斁DML璇鍙ユ椂涓昏佹湁浠ヤ笅鍦烘櫙鍙婂勭悊阃昏緫锛
锛堜笁锛塐GG鐗堟湰阃夋嫨
鍦∣GG鐗堟湰阃夋嫨涓婃垜浠涔熸牴鎹鐢ㄦ埛镄勫満鏅澶氭℃洿鎹浜哋GG鐗堟湰锛屾渶鍒濆洜涓哄㈡埛镄凮racle 鏁版嵁搴撶増链涓11.2.0.4锛屽洜姝ゆ垜浠鍦ㄩ夋嫨OGG鐗堟湰镞朵紭鍏堥夋嫨浣跨敤浜11鐗堟湰锛屼絾鏄浣跨敤杩囩▼涓鍙戠幇锛屾疮娆℃暟鎹鎶藉彇鐢熸垚镄则rail鏂囦欢杈惧埌2G宸﹀彸镞讹纴OGG鎶ラ敊杩炴帴涓鏂锛屾煡鐪婻MTFILE鍙傛暟璇︾粏璇存槑浜呜В鍒皌rail鏂囦欢榛樿ら檺鍒朵负2G锛屽悗𨱒ユ垜浠镟挎崲OGG鐗堟湰涓12.3锛屼娇鐢∕AXFILES鍙傛暟鎺у埗鐢熸垚澶氢釜鎸囧畾澶у皬镄则rail鏂囦欢锛屽洖鏀炬椂Replicat杩涚▼涔熻兘镊锷ㄨ疆杞璇诲彇Trail鏂囦欢锛屾渶缁堣В鍐宠ラ梾棰樸备絾鏄濡傛灉涓嶅垢Oracle鐜澧冧娇鐢ㄤ简Linux 5鐗堟湰镄勭郴缁燂纴闾ｄ箞浣犵殑OGG闇瑕佸啀闄崭竴涓灏忕増链锛屾渶楂桦彧鑳戒娇鐢∣GG 12.2銆
锛埚洓锛夋棤涓婚敭琛ㄥ勭悊
鍦ㄨ縼绉昏繃绋嬩腑杩樼板埌涓涓姣旇缉闅炬闷镄勯梾棰桦氨鏄褰揿墠Oracle绔瀛桦湪澶ч噺琛ㄦ病链変富阌銆傚湪MySQL涓镄勮〃娌℃湁涓婚敭杩椤嚑涔庢槸涓嶈鍏佽哥殑锛屽洜涓哄緢瀹规槗瀵艰嚧镐ц兘闂棰桦拰涓讳粠寤惰繜銆傚悓镞跺湪OGG杩佺Щ杩囩▼涓琛ㄦ病链変富阌涔熶细浜х敓涓浜涢殣鎭ｏ纴姣斿傚逛簬娌℃湁涓婚敭镄勮〃锛孙GG榛樿ゆ槸灏呜繖涓涓琛屾暟鎹涓镓链夌殑鍒楁嫾鍑戣捣𨱒ヤ綔涓哄敮涓阌锛屼絾瀹为檯杩樻槸鍙鑳藉瓨鍦ㄩ吨澶嶆暟鎹瀵艰嚧鏁版嵁钖屾ュ纾甯革纴Oracle瀹樻柟瀵规や篃鎻愪緵浜嗕竴涓瑙ｅ喅鏂规堬纴阃氲繃瀵规棤涓婚敭琛ㄦ坊锷燝UID鍒楁潵浣滀负琛屽敮涓镙囩ず锛屽叿浣撴搷浣沧柟寮忓彲浠ユ悳绱MOS鏂囨。ID 1271578.1杩涜屾煡鐪嬨
锛堜簲锛塐GG瀹夊叏瑙勫垯
阌栾淇℃伅钖涔夋簮绔鎶ラ敊琛ㄧず涓鸿ユ娊鍙栬繘绋嬮渶瑕佸拰鐩镙囩镄刴gr杩涚▼阃氲锛屼絾鏄琚𨰾掔粷锛屽叿浣撴搷浣滀负锛氭簮绔镄别xtract杩涚▼闇瑕佷笌鐩镙囩痬gr杩涜屾矡阃氾纴杩灭▼灏嗙洰镙囩殑replicat杩涜屽惎锷锛岀敱浜庡畨鍏ㄦх幇鍦ㄨ岃𨰾掔粷杩炴帴銆
鍦∣racle OGG 11鐗堟湰钖庯纴澧炲姞浜嗘柊鐗规у畨鍏ㄦц佹眰锛屽傛灉闇瑕佽繙绋嫔惎锷ㄧ洰镙囩镄剅eplicat杩涚▼锛岄渶瑕佸湪mgr鑺傜偣澧炲姞璁块梾鎺у埗鍙傛暟鍏佽歌繙绋嬭皟鐢
鍦ㄦ簮绔鍜岀洰镙囩镄刴gr鑺傜偣涓婂垎鍒澧炲姞璁块梾鎺у埗瑙勫垯骞堕吨钖
锛埚叚锛夋暟鎹鎶藉彇鏂瑰纺
镙规嵁瀹樻柟鏂囨。璇存槑锛屽綋鍓岖洿鎺ラ氲繃Oracle鏁版嵁搴撴娊鍙栨暟鎹鍐椤埌MySQL杩欑峣nitial-load鏂瑰纺锛屼笉鏀鎸丩OBs鏁版嵁绫诲瀷锛岃岃〃 UNIONPAYCMS.CMSOTCONTENT_RTF 鍒椤寘钖浜咰LOB瀛楁碉纴镞犳硶杩涜屼紶杈,骞朵笖璇ユ柟寮忎笉鏀鎸佽秴杩4k镄勫瓧娈垫暟鎹绫诲瀷
灏嗘娊鍙栬繘绋嬩腑镄凴MTTASK鏀逛负RMTFILE鍙傛暟 瀹樻柟寤鸿灏嗘暟鎹鍏堟娊鍙栨垚鏂囦欢锛屽啀锘轰簬鏂囦欢鏁版嵁瑙ｆ瀽杩涜屽埯濮嫔寲瀵煎叆
❹ 姹傚啓涓娈靛悜oracle鏁版嵁搴撴彃鍏ユ柊鏁版嵁镄剆ql璇鍙
浣犺寸殑钬滃簱钖嵝濇槸鎸噊racle閲岀殑鏁版嵁搴揿悕绉帮纴杩樻槸鎸囱〃镓鍦ㄧ殑鐢ㄦ埛锛
锏婚檰浣犵殑oracle鐢ㄦ埛锛
ID甯屾湜镊澧为暱锛岄偅涔埚缓绔嬩竴涓狲equence搴忓垪鍗冲彲銆
鍏埚缓绔嫔簭鍒:
create sequence seq_name -------鍒涘缓搴忓垪钖嶅瓧涓 seq_name,镊宸卞畾涔
minvalue 1 ----- 链灏忓间负 1锛屽彲镊瀹氢箟
maxvalue 99999 ------- 链澶у间负 99999锛屽彲镊瀹氢箟
start with 1 ------ 寮濮嫔 1锛屽彲镊瀹氢箟
increment by 1 -----姣忔″为暱鍊 1锛屽彲镊瀹氢箟
cache 20; ----缂揿瓨澶у皬 20锛屽彲镊瀹氢箟
杩欓噷鐢ㄨ〃钖峵ablename 浠ｆ浛浣犺寸殑DB锛屼綘镟挎崲灏卞彲浠
鎻掑叆锛
insert into tablename(id, user, pass, sex, sn) values(seq_name.nextval,'user','pass','sex','sn' );
commit;
淇鏀癸细
update tablename set sex='鐢' where user=钬桦紶涓';
commit;
-------set钖庨溃瑕佹洿鏂版垚浠涔堬纴鍜寃here钖庨溃镄勬浔浠舵牴鎹闇瑕佽嚜宸卞啓銆傛渶锘烘湰镄凷QL浜嗐
鍒犻櫎锛
delete from tablename where user='寮犱笁';
commit;
-----------鍏蜂綋𨱒′欢镊宸卞啓銆
鎴戣夊缑浣犺缮鏄鐪嬩笅链锘烘湰镄凷QL鏁欐潗钖
❺ 缃戦〉html婧愪唬镰佸啓鍏oracle鏁版嵁搴揿备綍阆垮紩鍙烽梾棰
1銆侀栧厛瑕佹墦寮缃戦〉html婧愪唬镰佸啓鍏oracle鏁版嵁搴撱
2銆佸叾娆″啓涓镟挎崲镄勭被锛屽啓鏁版嵁搴撴椂镟挎崲瀹冦
3銆佹渶钖庡嵆鍙阆垮紩鍙烽梾棰樸