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璇鍙ュ苟鍦ㄧ洰鏍囩鍥炴斁

浜屻佽縼縐繪柟妗

錛堜竴錛夌幆澧冧俊鎮

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鏁版嵁搴撻厤緗

• 紜璁MySQL絝琛ㄧ粨鏋勫凡緇忓瓨鍦

• MySQL鏁版嵁搴揙GG鐢ㄦ埛鍒涘緩

• mysql> create user 'ogg'@'%' identified by 'ogg';

• mysql> grant all on *.* to 'ogg'@'%';

• #### 鎻愬墠鍒涘緩濂給gg瀛樻斁checkpoint琛ㄧ殑鏁版嵁搴

• mysql> create database ogg;

錛2錛夌洰鏍囩疧GG 綆＄悊榪涚▼(MGR)閰嶇疆

鐩鏍囩鐨凪GR榪涚▼鍜屾簮絝閰嶇疆涓鏍鳳紝鍙鐩存帴灝嗘簮絝閰嶇疆鏂瑰紡鍦ㄧ洰鏍囩閲嶅嶆墽琛屼竴嬈″嵆鍙錛岃ラ儴鍒嗕笉鍦ㄨ禈榪

錛3錛夌洰鏍囩疧GG 媯鏌ョ偣鏃ュ織琛(checkpoint)閰嶇疆

checkpoint琛ㄧ敤鏉ヤ繚闅滀竴涓浜嬪姟鎵ц屽畬鎴愬悗錛屽湪MySQL鏁版嵁搴撲粠鏈変竴寮犺〃璁板綍褰撳墠鐨勬棩蹇楀洖鏀劇偣錛屼笌MySQL澶嶅埗璁板綍binlog鐨凣TID鎴杙osition鐐圭被浼箋

#### 鍒囨崲鑷硂gg杞浠剁洰褰曞苟鎵ц実gsci榪涘叆鍛戒護琛岀粓絝

• 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

錛4錛夌洰鏍囩疧GG 鍥炴斁綰跨▼(replicat)閰嶇疆

Replicat榪涚▼榪愯屽湪鐩鏍囩錛屾槸鏁版嵁鎶曢掔殑鏈鍚庝竴絝欙紝璐熻矗璇誨彇鐩鏍囩疶rail鏂囦歡涓鐨勫唴瀹癸紝騫跺皢瑙ｆ瀽鍏惰В鏋愪負DML璇鍙ワ紝鐒跺悗搴旂敤鍒扮洰鏍囨暟鎹搴撲腑銆

• #### 鍒囨崲鑷硂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.*;

娉ㄦ剰錛歳eplicat榪涚▼鍙闇閰嶇疆瀹屾垚錛屾棤闇鍚鍔錛屽緟鍏ㄩ噺鎶藉彇瀹屾垚鍚庡啀鍚鍔ㄣ

鑷蟲ゆ簮絝鐜澧冮厤緗瀹屾垚

寰呭叏閲忔暟鎹鎶藉彇瀹屾瘯鍚庡惎鍔ㄧ洰鏍囩鍥炴斁榪涚▼鍗沖彲瀹屾垚鏁版嵁鍑嗗疄鏃跺悓姝ャ

3.鍏ㄩ噺鍚屾ラ厤緗

鍏ㄩ噺鏁版嵁鍚屾ヤ負涓嬈℃ф搷浣滐紝褰揙GG杞浠墮儴緗插畬鎴愬強澧為噺鎶藉彇榪涚▼閰嶇疆騫跺惎鍔ㄥ悗錛屽彲閰嶇疆1涓鐗規畩鐨別xtract榪涚▼浠庤〃涓鎶藉彇鏁版嵁錛屽皢鎶藉彇鐨勬暟鎹淇濆瓨鍒扮洰鏍囩鐢熸垚鏂囦歡錛岀洰鏍囩鍚屾椂鍚鍔ㄤ竴涓鍗曟¤繍琛岀殑replicat鍥炴斁榪涚▼灝嗘暟鎹瑙ｆ瀽騫跺洖鏀捐嚦鐩鏍囨暟鎹搴撲腑銆

錛1錛夋簮絝疧GG 鍏ㄩ噺鎶藉彇榪涚▼(extract)閰嶇疆

• #### 鍒囨崲鑷硂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

錛2錛夌洰鏍囩疧GG 鍏ㄩ噺鍥炴斁榪涚▼(replicat)閰嶇疆

• #### 鍒囨崲鑷硂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

涓夈佹暟鎹鏍￠獙

鏁版嵁鏍￠獙鏄鏁版嵁榪佺Щ榪囩▼涓蹇呬笉鍙灝戠殑鐜鑺傦紝鏈絝犺妭鎻愪緵緇欏嚑涓鏁版嵁鏍￠獙鐨勬濊礬鍏卞ぇ瀹跺弬鏁幫紝鏍￠獙鏂瑰紡鍙浠ョ敱浠ヤ笅鍑犱釜瑙掑害鍘誨疄鐜幫細

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錛屽洜姝ら拡瀵硅繖縐嶆儏鍐墊湁涓ょ嶈В鍐蟲柟寮忥細

• 鏍規嵁瀹為檯瀛樺偍鏁版嵁鐨勯暱搴︼紝瀵硅秴闀跨殑varchar鍒楄繘琛屾敹緙╋紱

• 瀵逛簬鏃犳硶鏀剁緝鐨勫垪杞鎹㈡暟鎹綾誨瀷涓簍ext錛屼絾榪欏湪浣跨敤榪囩▼涓鍙鑳藉艱嚧涓浜涙ц兘闂棰橈紱

2. 涓庣涓鐐圭被浼礆紝鍦↖nnodb瀛樺偍寮曟搸涓錛岀儲寮曞墠緙闀垮害闄愬埗鏄767 bytes錛岃嫢浣跨敤DYNAMIC銆丆OMPRESSED琛屾牸寮忎笖寮鍚痠nnodblargeprefix鐨勫満鏅涓嬶紝榪欎釜闄愬埗鏄3072 bytes錛屽嵆浣跨敤utf8mb4瀛楃﹂泦鏃訛紝鏈澶氬彧鑳藉箆archar(768)鐨勫垪鍒涘緩緔㈠紩錛

3. 浣跨敤ogg鍏ㄩ噺鍒濆嬪寲鍚屾ユ椂錛岃嫢瀛樺湪澶栭敭綰︽潫錛屾壒閲忓煎叆鏃剁敱浜庡悇琛ㄧ殑鎻掑叆欏哄簭涓嶅敮涓錛屽彲鑳藉瓙琛ㄥ厛鎻掑叆鏁版嵁鑰屼富琛ㄨ繕鏈鎻掑叆錛屽艱嚧鎶ラ敊瀛愯〃渚濊禆鐨勮板綍涓嶅瓨鍦錛屽洜姝ゅ緩璁鏁版嵁榪佺Щ闃舵電佺敤涓誨栭敭綰︽潫錛屽緟榪佺Щ緇撴潫鍚庡啀鎵撳紑銆

• mysql>set global foreign_key_checks=off;




錛堜簩錛夊叏閲忎笌澧為噺琛旀帴

HANDLECOLLISIONS鍙傛暟鏄瀹炵幇OGG鍏ㄩ噺鏁版嵁涓庡為噺鏁版嵁琛旀帴鐨勫叧閿錛屽叾瀹炵幇鍘熺悊鏄鍦ㄥ叏閲忔娊鍙栧墠鍏堝紑鍚澧為噺鎶藉彇榪涚▼錛屾姄鍘誨叏閲忓簲鐢ㄦ湡闂翠駭鐢熺殑redo log錛屽綋鍏ㄩ噺搴旂敤瀹屾垚鍚庯紝寮鍚澧為噺鍥炴斁榪涚▼錛屽簲鐢ㄥ叏閲忔湡闂寸殑澧為噺鏁版嵁銆備嬌鐢ㄨュ弬鏁板悗澧為噺鍥炴斁DML璇鍙ユ椂涓昏佹湁浠ヤ笅鍦烘櫙鍙婂勭悊閫昏緫錛

• 鐩鏍囩涓嶅瓨鍦╠elete璇鍙ョ殑璁板綍錛屽拷鐣ヨラ棶棰樺苟涓嶈板綍鍒癲iscardfile

• 鐩鏍囩涓㈠けupdate璁板綍

  - 鏇存柊鐨勬槸涓婚敭鍊礆紝update杞鎹㈡垚insert

  - 鏇存柊鐨勯敭鍊兼槸闈炰富閿錛屽拷鐣ヨラ棶棰樺苟涓嶈板綍鍒癲iscardfile

• 鐩鏍囩閲嶅嶢nsert宸插瓨鍦ㄧ殑涓婚敭鍊礆紝榪欏皢琚玶eplicat榪涚▼杞鎹涓篣PDATE鐜版湁涓婚敭鍊肩殑琛

錛堜笁錛塐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瀹夊叏瑙勫垯

• 鎶ラ敊淇℃伅
  

• 2019-03-08 06:15:22 ERROR OGG-01201 Error reported by MGR : Access denied.




閿欒淇℃伅鍚涔夋簮絝鎶ラ敊琛ㄧず涓鴻ユ娊鍙栬繘紼嬮渶瑕佸拰鐩鏍囩鐨刴gr榪涚▼閫氳錛屼絾鏄琚鎷掔粷錛屽叿浣撴搷浣滀負錛氭簮絝鐨別xtract榪涚▼闇瑕佷笌鐩鏍囩痬gr榪涜屾矡閫氾紝榪滅▼灝嗙洰鏍囩殑replicat榪涜屽惎鍔錛岀敱浜庡畨鍏ㄦх幇鍦ㄨ岃鎷掔粷榪炴帴銆

• 鎶ラ敊鍘熷洜

鍦∣racle OGG 11鐗堟湰鍚庯紝澧炲姞浜嗘柊鐗規у畨鍏ㄦц佹眰錛屽傛灉闇瑕佽繙紼嬪惎鍔ㄧ洰鏍囩鐨剅eplicat榪涚▼錛岄渶瑕佸湪mgr鑺傜偣澧炲姞璁塊棶鎺у埗鍙傛暟鍏佽歌繙紼嬭皟鐢

• 瑙ｅ喅鍔炴硶
  

鍦ㄦ簮絝鍜岀洰鏍囩鐨刴gr鑺傜偣涓婂垎鍒澧炲姞璁塊棶鎺у埗瑙勫垯騫墮噸鍚

• ## 琛ㄧず璇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').




• 鎶ラ敊鍘熷洜

鏍規嵁瀹樻柟鏂囨。璇存槑錛屽綋鍓嶇洿鎺ラ氳繃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銆佹渶鍚庡嵆鍙閬垮紩鍙烽棶棰樸