MyException - 我的异常网
当前位置:我的异常网» 数据库 » oracle鏁版嵁搴撳悓姝ユ妧鏈

oracle鏁版嵁搴撳悓姝ユ妧鏈

www.MyException.Cn  网友分享于:2015-02-11  浏览:0次
oracle鏁版嵁搴撳悓姝ユ妧鏈?

oracle鏁版嵁搴撳悓姝ユ妧鏈?/span>

楂樼骇澶嶅埗

聽 浠€涔堟槸澶嶅埗锛熺畝鍗曞湴璇村鍒跺氨鏄湪鐢变袱涓垨鑰呭涓暟鎹簱绯荤粺鏋勬垚鐨勪竴涓垎甯冨紡鏁版嵁搴撶幆澧冧腑鎷疯礉鏁?/span>

聽 鎹殑杩囩▼銆?/span>

聽聽楂樼骇澶嶅埗锛屾槸鍦ㄧ粍鎴愬垎甯冨紡鏁版嵁搴撶郴缁熺殑澶氫釜鏁版嵁搴撲腑澶嶅埗鍜岀淮鎶ゆ暟鎹簱瀵硅薄鐨勮繃绋嬨€?/span>

聽聽聽 Oracle 楂樼骇澶嶅埗鍏佽搴旂敤绋嬪簭鏇存柊鏁版嵁搴撶殑浠讳綍鍓湰锛屽苟灏嗚繖浜涙洿鏀硅嚜鍔ㄤ紶閫掑埌鍏朵粬鏁版嵁搴擄紝

聽聽聽 鍚屾椂纭繚鍏ㄥ眬浜嬪姟澶勭悊鐨勪竴鑷存€у拰鏁版嵁瀹屾暣鎬с€?/span>

聽聽鍚屾澶嶅埗锛屽鍒舵暟鎹湪浠讳綍鏃堕棿鍦ㄤ换浣曞鍒惰妭鐐瑰潎淇濇寔涓€鑷淬€?/span>

聽聽聽 濡傛灉澶嶅埗鐜涓殑浠讳綍涓€涓妭鐐圭殑澶嶅埗鏁版嵁鍙戠敓浜嗘洿鏂版搷浣滐紝

聽聽聽 杩欑鍙樺寲浼氱珛鍒诲弽鏄犲埌鍏朵粬鎵€鏈夌殑澶嶅埗鑺傜偣銆?/span>

聽聽聽 杩欑鎶€鏈€傜敤浜庨偅浜涘浜庡疄鏃舵€ц姹傝緝楂樼殑鍟嗕笟搴旂敤涓€?/span>

聽聽寮傛澶嶅埗锛屾墍鏈夊鍒惰妭鐐圭殑鏁版嵁鍦ㄤ竴瀹氭椂闂村唴鏄笉鍚屾鐨勩€?/span>

聽聽聽 濡傛灉澶嶅埗鐜涓殑鍏朵腑鐨勪竴涓妭鐐圭殑澶嶅埗鏁版嵁鍙戠敓浜嗘洿鏂版搷浣滐紝

聽聽聽 杩欑鏀瑰彉灏嗗湪涓嶅悓鐨勪簨鍔′腑琚紶鎾拰搴旂敤鍒板叾浠栨墍鏈夊鍒惰妭鐐广€?/span>

聽聽聽 杩欎簺涓嶅悓鐨勪簨鍔¢棿鍙互闂撮殧鍑犵锛屽嚑鍒嗙锛屽嚑灏忔椂锛屼篃鍙互鏄嚑澶╀箣鍚庛€?/span>

聽聽聽 澶嶅埗鑺傜偣涔嬮棿鐨勬暟鎹复鏃舵槸涓嶅悓姝ョ殑锛屼絾浼犳挱鏈€缁堝皢淇濊瘉鎵€鏈夊鍒惰妭鐐归棿鐨勬暟鎹竴鑷淬€?/span>

涓€銆佸疄鐜版暟鎹簱澶嶅埗鐨勫墠鎻愭潯浠?/span>

1銆佹暟鎹簱鏀寔楂樼骇澶嶅埗鍔熻兘

聽 鎮ㄥ彲浠ョ敤system韬唤鐧诲綍涓や釜鏁版嵁搴擄紙鎵€鏈夌殑鏁版嵁搴撻兘闇€瑕佸仛涓嬮潰鐨勮缃級锛屾煡鐪?span>v$option瑙嗗浘锛?/span>

聽 濡傛灉鍏朵腑Advanced replication涓?span>TRUE锛屽垯鏀寔楂樼骇澶嶅埗鍔熻兘锛涘惁鍒欎笉鏀寔銆?

聽 鏌ヨ璇彞锛?/span>

聽 select * from v$option where PARAMETER='Advanced replication';

2銆佹暟鎹簱鍒濆鍖栧弬鏁拌姹?/span>

鈶犮€?span>db_domain = test.com.cn

聽 鎵€鏈夋暟鎹簱鐨?span>db_domain鍚嶇О搴旇鐩稿悓锛屽彧鏈?span>db_name涓嶅悓銆?/span>

聽 鎸囨槑鏁版嵁搴撶殑鍩熷悕(榛樿鐨勬槸WORLD)锛岃繖閲屽彲浠ョ敤鎮ㄥ叕鍙哥殑鍩熷悕銆?/span>

鈶°€?span>global_names = true

聽 鏌ヨ璇彞锛?/span>

聽 show parameter global_names;

聽 淇敼璇彞锛?/span>

聽 alter system set global_names=true;

鈶€佹湁璺熸暟鎹簱job鎵ц鏈夊叧鐨勫弬鏁?/span>

聽 job_queue_processes = 1

聽 show parameter job_queue_processes;

聽 alter system set job_queue_processes = 20;

聽 job_queue_interval = 60

聽 alter system set "_job_queue_interval"=60 scope=spfile;

聽 /* 娉ㄦ剰scope=spfile鐨勫弬鏁伴兘闇€瑕侀噸鍚疄渚嬫柟鑳界敓鏁?span> */

绗竴琛屽畾涔?span>SNP杩涚▼鐨勫惎鍔ㄤ釜鏁颁负n銆傜郴缁熺己鐪佸€间负0锛屾甯稿畾涔夎寖鍥翠负0锝?span>36锛屾牴鎹换鍔$殑澶氬皯锛屽彲浠ラ厤缃笉鍚岀殑鏁板€笺€?/span>

绗簩琛屽畾涔夌郴缁熸瘡闅?span>N绉掑敜閱掕杩涚▼涓€娆°€傜郴缁熺己鐪佸€间负60绉掞紝姝e父鑼冨洿涓?span>1锝?span>3600绉掋€備簨瀹炰笂锛岃杩涚▼鎵ц瀹屽綋鍓嶄换鍔″悗锛屽氨杩涘叆鐫$湢鐘舵€侊紝鐫$湢涓€娈垫椂闂村悗锛岀敱绯荤粺鐨勬€绘帶璐熻矗灏嗗叾鍞ら啋銆?/span>

濡傛灉淇敼浜嗕互涓婅繖鍑犱釜鍙傛暟锛岄渶瑕侀噸鏂板惎鍔ㄦ暟鎹簱浠ヤ娇鍙傛暟鐢熸晥銆?/span>

浜屻€佸疄鐜版暟鎹簱鍚屾澶嶅埗鐨勬楠?span>(216澶嶅埗鏁版嵁鍒?span>217)

聽 鍋囪鍦?span>Internet涓婃垜浠湁涓や釜鏁版嵁搴擄細涓€涓彨orcl216锛屼竴涓彨orcl217銆?/span>

聽 鍏蜂綋閰嶇疆瑙佷笅琛細

聽 鏁版嵁搴撳悕 orcl216 orcl217

聽 鏁版嵁搴撳煙鍚?test.com.cn test.com.cn

聽 鏁版嵁搴?span>sid鍙?orcl216 orcl217

聽 Listener绔彛鍙?span> 1521 1521

聽 鏈嶅姟鍣?span>ip鍦板潃 192.168.1.216 192.168.1.217

1銆佺‘璁や袱鍙版暟鎹簱涔嬮棿鍙互浜掔浉璁块棶锛屽湪tnsnames.ora閲岃缃暟鎹簱杩炴帴瀛楃涓层€?/span>

鈶犮€佷緥濡傦細orcl216杩欒竟鐨勬暟鎹簱杩炴帴瀛楃涓叉槸浠ヤ笅鐨勬牸寮?/span>

聽 orcl217=

聽聽聽 (DESCRIPTION =

聽聽聽聽聽 (ADDRESS_LIST =

聽聽聽聽聽聽聽 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.217)(PORT = 1521))

聽聽聽聽聽 )

聽聽聽聽聽 (CONNECT_DATA =

聽聽聽聽聽聽聽 (SERVICE_NAME = orcl217.test.com.cn)

聽聽聽聽聽 )

聽 )

杩愯$tnsping orcl217

鍑虹幇浠ヤ笅鎻愮ず绗︼細

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.200)(PORT=1521))

OK锛?span>n姣锛?/span>

琛ㄦ槑orcl216鏁版嵁搴撳彲浠ヨ闂?span>orcl217鏁版嵁搴撱€?/span>

鈶°€佸湪orcl217閭h竟涔熷悓鏍烽厤缃紝纭$tnsping orcl216鏄€氱殑銆?

2銆佹敼鏁版嵁搴撳叏灞€鍚嶇О锛屽缓鍏叡鐨勬暟鎹簱閾炬帴銆?/span>

鈶犮€佺敤system韬唤鐧诲綍orcl216鏁版嵁搴?/span>

SQL>alter database rename global_name to orcl216.test.com.cn;

鐢?span>system韬唤鐧诲綍orcl217鏁版嵁搴擄細

SQL>alter database rename global_name to orcl217.test.com.cn;

鈶°€佺敤system韬唤鐧诲綍orcl216鏁版嵁搴?/span>

SQL>create public database link orcl217.test.com.cn using 'orcl217';

娴嬭瘯鏁版嵁搴撳叏灞€鍚嶇О鍜屽叕鍏辩殑鏁版嵁搴撻摼鎺?/span>

SQL>select * from global_name@orcl217.test.com.cn;

杩斿洖缁撴灉涓?span>orcl217.test.com.cn灏卞浜嗐€?

鐢?span>system韬唤鐧诲綍orcl217鏁版嵁搴擄細

SQL>create public database link orcl216.test.com.cn using 'orcl216';

娴嬭瘯鏁版嵁搴撳叏灞€鍚嶇О鍜屽叕鍏辩殑鏁版嵁搴撻摼鎺?/span>

SQL>select * from global_name@orcl216.test.com.cn;

杩斿洖缁撴灉涓?span>orcl216.test.com.cn灏卞浜嗐€?

3銆佸缓绔嬬鐞嗘暟鎹簱澶嶅埗鐨勭敤鎴?span>repadmin锛屽苟璧嬫潈銆?

鍒犻櫎鐢ㄦ埛璇彞锛?/span>

drop user repadmin cascade;锛堝鐢ㄧ殑锛?/span>

鈶犮€佺敤system韬唤鐧诲綍orcl216鏁版嵁搴?/span>

SQL>create user repadmin identified by repadmin default tablespace users temporary聽tablespace temp;

SQL>execute dbms_defer_sys.register_propagator('repadmin');

SQL>grant execute any procedure to repadmin;

SQL>execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');

SQL>execute聽聽 dbms_repcat_admin.grant_admin_any_schema(username=>'"REPADMIN"');

SQL>grant comment any table to repadmin;

SQL>grant lock any table to repadmin;

SQL>grant select any dictionary to repadmin;

鈶°€佸悓鏍风敤system韬唤鐧诲綍orcl217鏁版嵁搴擄紝杩愯浠ヤ笂鐨勫懡浠わ紝

绠$悊鏁版嵁搴撳鍒剁殑鐢ㄦ埛repadmin锛屽苟璧嬫潈銆?/span>

璇存槑锛?span>repadmin鐢ㄦ埛鍚嶅拰瀵嗙爜鍙互鏍规嵁鐢ㄦ埛鐨勯渶姹傝嚜鐢卞懡鍚嶃€?

4銆佸湪鏁版嵁搴撳鍒剁殑鐢ㄦ埛repadmin涓嬪垱寤虹鏈夌殑鏁版嵁搴撻摼鎺ャ€?

鈶犮€佺敤repadmin韬唤鐧诲綍orcl216鏁版嵁搴?/span>

SQL>create database link orcl217.test.com.cn connect to repadmin identified by repadmin;

娴嬭瘯杩欎釜绉佹湁鐨勬暟鎹簱閾炬帴锛?/span>

SQL>select * from global_name@orcl217.test.com.cn;

杩斿洖缁撴灉涓?span>orcl217.test.com.cn灏卞浜嗐€?/span>

鈶°€佺敤repadmin韬唤鐧诲綍orcl217鏁版嵁搴?/span>

SQL>create database link orcl216.test.com.cn connect to repadmin identified by repadmin;

娴嬭瘯杩欎釜绉佹湁鐨勬暟鎹簱閾炬帴

SQL>select * from global_name@orcl216.test.com.cn;

杩斿洖缁撴灉涓?span>orcl216.test.com.cn灏卞浜嗐€?

5銆佸垱寤烘垨閫夋嫨瀹炵幇鏁版嵁搴撳鍒剁殑鐢ㄦ埛鍜屽璞★紝缁欑敤鎴疯祴鏉冿紝鏁版嵁搴撳璞″繀椤绘湁涓诲叧閿瓧銆?/span>

鍋囪鎴戜滑鐢?span>ORACLE閲屼妇渚嬬敤鐨?span>reporacle鐢ㄦ埛锛?span>dept琛ㄣ€?/span>

鈶犮€佺敤sys韬唤鐧诲綍orcl216鏁版嵁搴擄紝鍒涘缓reporacle鐢ㄦ埛骞惰祴鏉?/span>

SQL>create user reporacle identified by reporacle default tablespace users temporary聽tablespace temp;

SQL>grant connect, resource to reporacle;

SQL>grant execute on sys.dbms_defer to reporacle;

鈶°€佺敤reporacle韬唤鐧诲綍orcl216鏁版嵁搴擄紝鍒涘缓琛?span>dept

SQL>create table dept

聽聽(deptno number(2) primary key,

聽聽dname varchar2(14),

聽聽loc varchar2(13) );

鈶€佸鏋滄暟鎹簱瀵硅薄娌℃湁涓诲叧閿瓧锛屽彲浠ヨ繍琛屼互涓?span>SQL鍛戒护娣诲姞锛?/span>

SQL>alter table dept add (constraint dept_deptno_pk primary key (deptno));

鈶c€佸湪orcl216鏁版嵁搴?span>reporacle鐢ㄦ埛涓嬪垱寤轰富鍏抽敭瀛楃殑搴忓垪鍙凤紝

鑼冨洿閬垮厤鍜?span>orcl217鐨勫啿绐併€?/span>

SQL> create sequence dept_no increment by 1 start with 1 maxvalue 44 cycle nocache;

(璇存槑锛?span>maxvalue 44鍙互鏍规嵁搴旂敤绋嬪簭鍙婅〃缁撴瀯涓诲叧閿瓧瀹氫箟鐨勪綅鏁伴渶瑕佽€屽畾)

鈶ゃ€佸湪orcl216鏁版嵁搴?span>reporacle鐢ㄦ埛涓嬫彃鍏ュ垵濮嬪寲鏁版嵁

SQL>insert into dept values (dept_no.nextval,'accounting','new york');

SQL>insert into dept values (dept_no.nextval,'research','dallas');

SQL>commit;

鈶ャ€佸湪orcl217鏁版嵁搴撻偅杈瑰悓鏍疯繍琛屼互涓娾憼锛屸憽锛屸憿

鈶︺€佸湪orcl217鏁版嵁搴?span>reporacle鐢ㄦ埛涓嬪垱寤轰富鍏抽敭瀛楃殑搴忓垪鍙凤紝鑼冨洿閬垮厤鍜?span>orcl216鐨勫啿绐併€?/span>

SQL> create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache;

鈶с€佸湪orcl217鏁版嵁搴?span>scott鐢ㄦ埛涓嬫彃鍏ュ垵濮嬪寲鏁版嵁

SQL>insert into dept values (dept_no.nextval,'sales','chicago');

SQL>insert into dept values (dept_no.nextval,'operations','boston');

SQL>commit;

6銆佸垱寤鸿澶嶅埗鐨勭粍reporacle_mg锛屽姞鍏ユ暟鎹簱瀵硅薄锛屼骇鐢熷璞$殑澶嶅埗鏀寔

鈶犮€佺敤repadmin韬唤鐧诲綍orcl216鏁版嵁搴擄紝鍒涘缓涓诲鍒剁粍reporacle_mg

SQL> execute dbms_repcat.create_master_repgroup('reporacle_mg');

璇存槑锛?span>reporacle_mg缁勫悕鍙互鏍规嵁鐢ㄦ埛鐨勯渶姹傝嚜鐢卞懡鍚嶃€?/span>

鈶°€佸湪澶嶅埗缁?span>reporacle_mg閲屽姞鍏ユ暟鎹簱瀵硅薄

SQL>execute dbms_repcat.create_master_repobject(sname=>'reporacle',oname=>'dept', type=>'table',use_existing_object=>true,gname=>'reporacle_mg');

聽 SQL>Commit;

鍙傛暟璇存槑锛?/span>

sname 瀹炵幇鏁版嵁搴撳鍒剁殑鐢ㄦ埛鍚嶇О

oname 瀹炵幇鏁版嵁搴撳鍒剁殑鏁版嵁搴撳璞″悕绉?/span>

(琛ㄥ悕闀垮害鍦?span>27涓瓧鑺傚唴锛岀▼搴忓寘鍚嶉暱搴﹀湪24涓瓧鑺傚唴)

type 瀹炵幇鏁版嵁搴撳鍒剁殑鏁版嵁搴撳璞$被鍒?/span>

(鏀寔鐨勭被鍒細琛紝绱㈠紩锛屽悓涔夎瘝锛岃Е鍙戝櫒锛岃鍥撅紝杩囩▼锛屽嚱鏁帮紝绋嬪簭鍖咃紝绋嬪簭鍖呬綋)

use_existing_object true琛ㄧず鐢ㄤ富澶嶅埗鑺傜偣宸茬粡瀛樺湪鐨勬暟鎹簱瀵硅薄

gname 涓诲鍒剁粍鍚?/span>

鈶€佸鏁版嵁搴撳璞′骇鐢熷鍒舵敮鎸?/span>

SQL>execute dbms_repcat.generate_replication_support('reporacle','dept','table');

(璇存槑锛氫骇鐢熸敮鎸?span>reporacle鐢ㄦ埛涓?span>dept琛ㄥ鍒剁殑鏁版嵁搴撹Е鍙戝櫒鍜岀▼搴忓寘)

娉ㄦ剰锛氬鏋滄湁澶氫釜琛ㄩ渶瑕佸悓姝ワ紝閲嶅鎵ц鈶★紝鈶㈡楠ゃ€?/span>

涓嬮潰鑳藉璇存槑鍦ㄨ缃畬鎴愬悓姝ュ悗濡備綍杩藉姞澶嶅埗瀵硅薄锛堣〃锛夈€?/span>

鈶c€佺‘璁ゅ鍒剁殑缁勫拰瀵硅薄宸茬粡鍔犲叆鏁版嵁搴撶殑鏁版嵁瀛楀吀

SQL>select gname, master, status from dba_repgroup;

SQL>select * from dba_repobject;

7銆佸垱寤轰富澶嶅埗鑺傜偣

鈶犮€佺敤repadmin韬唤鐧诲綍orcl216鏁版嵁搴擄紝鍒涘缓涓诲鍒惰妭鐐?/span>

SQL>execute dbms_repcat.add_master_database(gname=>'REPORACLE_MG', master=>'ORCL217.TEST.COM.CN', use_existing_objects=>true, copy_rows=>false, propagation_mode=>'ASYNCHRONOUS');

鍙傛暟璇存槑锛?/span>

gname 涓诲鍒剁粍鍚?/span>

master 鍔犲叆涓诲鍒惰妭鐐圭殑鍙︿竴涓暟鎹簱

use_existing_object true琛ㄧず鐢ㄤ富澶嶅埗鑺傜偣宸茬粡瀛樺湪鐨勬暟鎹簱瀵硅薄

copy_rows false琛ㄧず绗竴娆″紑濮嬪鍒舵椂涓嶇敤鍜屼富澶嶅埗鑺傜偣淇濇寔涓€鑷?/span>

propagation_mode 寮傛鍦版墽琛?/span>

鈶°€佺‘璁ゅ鍒剁殑浠诲姟闃熷垪宸茬粡鍔犲叆鏁版嵁搴撶殑鏁版嵁瀛楀吀

SQL>select * from user_jobs;

8銆佷娇鍚屾缁勭殑鐘舵€佺敱鍋滈】(quiesced )鏀逛负姝e父(normal)

鈶犮€佺敤repadmin韬唤鐧诲綍orcl216鏁版嵁搴擄紝杩愯浠ヤ笅鍛戒护

SQL> execute dbms_repcat.resume_master_activity('reporacle_mg',true);

鈶°€佺‘璁ゅ悓姝ョ粍鐨勭姸鎬佷负姝e父(normal)

SQL> select gname, master, status from dba_repgroup;

9銆佸垱寤哄鍒舵暟鎹簱鐨勬椂闂磋〃锛屾垜浠亣璁剧敤鍥哄畾鐨勬椂闂磋〃锛?span>1鍒嗛挓澶嶅埗涓€娆°€?/span>

鈶犮€佺敤repadmin韬唤鐧诲綍orcl216鏁版嵁搴擄紝杩愯浠ヤ笅鍛戒护

SQL>begin

dbms_defer_sys.schedule_push (

destination => 'orcl217.test.com.cn',

interval => 'sysdate + 1/1440',

next_date => sysdate);

end;

/

銆€

SQL>begin

dbms_defer_sys.schedule_purge (

next_date => sysdate,

interval => 'sysdate + 1/1440',

delay_seconds => 0,

rollback_segment => '');

end;

/

銆€

鈶°€佺敤repadmin韬唤鐧诲綍orcl217鏁版嵁搴擄紝杩愯浠ヤ笅鍛戒护

SQL>begin

dbms_defer_sys.schedule_push (

destination => ' orcl216.test.com.cn ',

interval => 'sysdate + 1/1440',

next_date => sysdate);

end;

/

銆€

SQL>begin

dbms_defer_sys.schedule_purge (

next_date => sysdate,

interval => 'sysdate + 1/1440',

delay_seconds => 0,

rollback_segment => '');

end;

/

10銆佹坊鍔犳垨淇敼涓よ竟鏁版嵁搴撶殑璁板綍锛岃窡韪鍒惰繃绋?/span>

濡傛灉浣犳兂绔嬪埢鐪嬪埌娣诲姞鎴栦慨鏀瑰悗鏁版嵁搴撶殑璁板綍鐨勫彉鍖栵紝

鍙互鍦?span>216搴?span>repadmin鐢ㄦ埛涓嬫壘鍒?span>push鐨?span>job_number锛岀劧鍚庤繍琛岋細

SQL>exec dbms_job.run(job_number);

璇存槑锛?span>job_number 涓虹敤select job,this_date,next_date,what from user_jobs;鍛戒护鏌ュ嚭鐨?span>job缂栧彿銆?/span>

涓夈€佸紓甯告儏鍐电殑澶勭悊

1銆佹鏌ュ鍒跺伐浣滄甯稿惁锛屽彲浠ュ湪repadmin 鐢ㄦ埛涓嬫煡璇?span>user_jobs

SQL>select job,this_date,next_date,what, broken from user_jobs;

姝e父鐨勭姸鎬佹湁涓ょ锛?/span>

浠诲姟闂测€斺€?span>this_date涓虹┖锛?span>next_date涓哄綋鍓嶆椂闂村悗鐨勪竴涓椂闂村€?/span>

浠诲姟蹇欌€斺€?span>this_date涓嶄负绌猴紝next_date涓哄綋鍓嶆椂闂村悗鐨勪竴涓椂闂村€?/span>

寮傚父鐘舵€佷篃鏈変袱绉嶏細

浠诲姟姝婚攣鈥斺€?span>next_date涓哄綋鍓嶆椂闂村墠鐨勪竴涓椂闂村€?/span>

浠诲姟姝婚攣鈥斺€?span>next_date涓洪潪甯稿ぇ鐨勪竴涓椂闂村€硷紝渚嬪锛?span>4001-01-01

杩欏彲鑳藉洜涓虹綉缁滀腑鏂収鎴愮殑姝婚攣

瑙i櫎姝婚攣鐨勫姙娉曪細

$ps 鈥揺f|grep orale

鎵惧埌姝婚攣鐨勫埛鏂板揩鐓х殑杩涚▼鍙?span>ora_snp*锛岀敤kill 鈥?span>9 鍛戒护鍒犻櫎姝よ繘绋?/span>

鐒跺悗杩涘叆repadmin 鐢ㄦ埛SQL>鎿嶄綔绗︿笅锛岃繍琛屽懡浠わ細

SQL>exec dbms_job.run(job_number);

璇存槑锛?span>job_number 涓虹敤select job,this_date,next_date,what from user_jobs;鍛戒护鏌ュ嚭鐨?span>job缂栧彿銆?

2銆佸鍔犳垨鍑忓皯澶嶅埗缁勭殑澶嶅埗瀵硅薄

鈶犮€佸仠姝富鏁版嵁搴撹妭鐐圭殑澶嶅埗鍔ㄤ綔锛屼娇鍚屾缁勭殑鐘舵€佺敱姝e父(normal)鏀逛负鍋滈】(quiesced )

鐢?span>repadmin韬唤鐧诲綍orcl216鏁版嵁搴擄紝杩愯浠ヤ笅鍛戒护

纭鍚屾缁勭殑鐘舵€?/span>

SQL> select gname, master, status from dba_repgroup;

鍋滄鏈嶅姟

SQL>execute dbms_repcat.suspend_master_activity('reporacle_mg');

鈶°€佸湪澶嶅埗缁?span>reporacle_mg閲屽姞鍏ユ暟鎹簱瀵硅薄锛屼繚璇佹暟鎹簱瀵硅薄蹇呴』鏈変富鍏抽敭瀛椼€?/span>

SQL> execute dbms_repcat.create_master_repobject(sname=>'reporacle',oname=>'dept1', type=>'TABLE',use_existing_object=>true,gname=>'REPORACLE_MG',copy_rows=>true);

SQL> Commit;

瀵瑰姞鍏ョ殑鏁版嵁搴撳璞′骇鐢熷鍒舵敮鎸?/span>

SQL> execute dbms_repcat.generate_replication_support('reporacle','dept1','TABLE');

鈶€佸垹闄ゅ湪澶嶅埗缁?span>reporacle_mg閲岀殑鏁版嵁搴撳璞°€?/span>

SQL>execute dbms_repcat.drop_master_repobject ('reporacle','dept1','table');

鈶c€侀噸鏂颁娇鍚屾缁勭殑鐘舵€佺敱鍋滈】(quiesced )鏀逛负姝e父(normal)銆?/span>

SQL> execute dbms_repcat.resume_master_activity('REPORACLE_MG',true);

纭澶嶅埗鐨勭粍鍜屽璞″凡缁忓姞鍏ユ暟鎹簱鐨勬暟鎹瓧鍏?/span>

select * from dba_repobject;

濡傛灉闇€瑕佽拷鍔犱竴鍙板垎甯冩湇鍔″櫒鍚?span>orcl217鍚屾鏁版嵁

闇€瑕佸仛濡備笅鎿嶄綔銆?/span>

鏂拌拷鍔犵殑鏈嶅姟鍣ㄥ叿浣撻厤缃涓嬭〃锛?/span>

鏁版嵁搴撳悕 orcl214

鏁版嵁搴撳煙鍚?span> test.com.cn test.com.cn

鏁版嵁搴?span>sid鍙?span> orcl214

Listener绔彛鍙?span> 1521

鏈嶅姟鍣?span>ip鍦板潃 192.168.1.214

1銆佺‘璁や袱鍙版暟鎹簱涔嬮棿鍙互浜掔浉璁块棶锛屽湪tnsnames.ora閲岃缃暟鎹簱杩炴帴瀛楃涓层€?/span>

鈶犮€佷緥濡傦細orcl214杩欒竟鐨勬暟鎹簱杩炴帴瀛楃涓叉槸浠ヤ笅鐨勬牸寮?/span>

orcl217=

(DESCRIPTION =

聽聽聽 (ADDRESS_LIST =

聽聽聽聽聽 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.217)(PORT = 1521))

聽聽聽 )

聽聽聽 (CONNECT_DATA =

聽聽聽聽聽 (SERVICE_NAME = orcl217.test.com.cn)

聽聽聽 )

)

杩愯$tnsping orcl217

鍑虹幇浠ヤ笅鎻愮ず绗︼細

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.200)(PORT=1521))

OK锛?span>n姣锛?/span>

琛ㄦ槑orcl214鏁版嵁搴撳彲浠ヨ闂?span>orcl217鏁版嵁搴撱€?/span>

鈶°€佸湪orcl217閭h竟涔熷悓鏍烽厤缃紝纭$tnsping orcl214鏄€氱殑銆?/span>

2銆佹敼鏁版嵁搴撳叏灞€鍚嶇О锛屽缓鍏叡鐨勬暟鎹簱閾炬帴銆?/span>

鈶犮€佺敤system韬唤鐧诲綍orcl214鏁版嵁搴?/span>

SQL>alter database rename global_name to orcl214.test.com.cn;

鈶°€佺敤system韬唤鐧诲綍orcl214鏁版嵁搴?/span>

SQL>create public database link orcl217.test.com.cn using 'orcl217';

娴嬭瘯鏁版嵁搴撳叏灞€鍚嶇О鍜屽叕鍏辩殑鏁版嵁搴撻摼鎺?/span>

SQL>select * from global_name@orcl217.test.com.cn;

杩斿洖缁撴灉涓?span>orcl217.test.com.cn灏卞浜嗐€?/span>

鐢?span>system韬唤鐧诲綍orcl217鏁版嵁搴擄細

SQL>create public database link orcl214.test.com.cn using 'orcl214';

娴嬭瘯鏁版嵁搴撳叏灞€鍚嶇О鍜屽叕鍏辩殑鏁版嵁搴撻摼鎺?/span>

SQL>select * from global_name@orcl214.test.com.cn;

杩斿洖缁撴灉涓?span>orcl214.test.com.cn灏卞浜嗐€?/span>

3銆佸缓绔嬬鐞嗘暟鎹簱澶嶅埗鐨勭敤鎴?span>repadmin锛屽苟璧嬫潈銆?/span>

鍒犻櫎鐢ㄦ埛璇彞锛?/span>

drop user repadmin cascade;

鈶犮€佺敤system韬唤鐧诲綍orcl214鏁版嵁搴?/span>

SQL>create user repadmin identified by repadmin default tablespace users temporary tablespace temp;

SQL>execute dbms_defer_sys.register_propagator('repadmin');

SQL>grant execute any procedure to repadmin;

SQL>execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');

SQL>execute聽聽 dbms_repcat_admin.grant_admin_any_schema(username=>'"REPADMIN"');

SQL>grant comment any table to repadmin;

SQL>grant lock any table to repadmin;

SQL>grant select any dictionary to repadmin;

4銆佸湪鏁版嵁搴撳鍒剁殑鐢ㄦ埛repadmin涓嬪垱寤虹鏈夌殑鏁版嵁搴撻摼鎺ャ€?/span>

鈶犮€佺敤repadmin韬唤鐧诲綍orcl214鏁版嵁搴?/span>

SQL>create database link orcl217.test.com.cn connect to repadmin identified by repadmin;

娴嬭瘯杩欎釜绉佹湁鐨勬暟鎹簱閾炬帴锛?/span>

SQL>select * from global_name@orcl217.test.com.cn;

杩斿洖缁撴灉涓?span>orcl217.test.com.cn灏卞浜嗐€?/span>

鈶°€佺敤repadmin韬唤鐧诲綍orcl217鏁版嵁搴?/span>

SQL>create database link orcl214.test.com.cn connect to repadmin identified by repadmin;

娴嬭瘯杩欎釜绉佹湁鐨勬暟鎹簱閾炬帴

SQL>select * from global_name@orcl214.test.com.cn;

杩斿洖缁撴灉涓?span>orcl214.test.com.cn灏卞浜嗐€?/span>

5銆佸垱寤烘垨閫夋嫨瀹炵幇鏁版嵁搴撳鍒剁殑鐢ㄦ埛鍜屽璞★紝缁欑敤鎴疯祴鏉冿紝鏁版嵁搴撳璞″繀椤绘湁涓诲叧閿瓧銆?/span>

鍋囪鎴戜滑鐢?span>ORACLE閲屼妇渚嬬敤鐨?span>reporacle鐢ㄦ埛锛?span>dept琛ㄣ€?/span>

鈶犮€佺敤sys韬唤鐧诲綍orcl214鏁版嵁搴擄紝鍒涘缓reporacle鐢ㄦ埛骞惰祴鏉?/span>

SQL>create user reporacle identified by reporacle default tablespace users temporary tablespace temp;

SQL>grant connect, resource to reporacle;

SQL>grant execute on sys.dbms_defer to reporacle;

鈶°€佺敤reporacle韬唤鐧诲綍orcl214鏁版嵁搴擄紝鍒涘缓琛?span>dept

SQL>create table dept

(deptno number(2) primary key,

dname varchar2(14),

loc varchar2(13) );

6銆佸垱寤鸿澶嶅埗鐨勭粍reporacle_mg1锛屽姞鍏ユ暟鎹簱瀵硅薄锛屼骇鐢熷璞$殑澶嶅埗鏀寔

娉ㄦ剰锛氳繖閲岀殑澶嶅埗鐨勭粍鐨勫悕瀛椾笉鑳藉拰涓婁竴涓浉鍚岋紝杩欓噷鏄?span>reporacle_mg1銆?/span>

鈶犮€佺敤repadmin韬唤鐧诲綍orcl214鏁版嵁搴擄紝鍒涘缓涓诲鍒剁粍reporacle_mg1

SQL> execute dbms_repcat.create_master_repgroup('reporacle_mg1');

璇存槑锛?span>reporacle_mg1缁勫悕鍙互鏍规嵁鐢ㄦ埛鐨勯渶姹傝嚜鐢卞懡鍚嶃€?/span>

鈶°€佸湪澶嶅埗缁?span>reporacle_mg1閲屽姞鍏ユ暟鎹簱瀵硅薄

SQL>execute dbms_repcat.create_master_repobject(sname=>'reporacle',oname=>'dept', type=>'table',use_existing_object=>true,gname=>'reporacle_mg1');

Commit;

鈶€佸鏁版嵁搴撳璞′骇鐢熷鍒舵敮鎸?/span>

SQL>execute dbms_repcat.generate_replication_support('reporacle','dept','table');

(璇存槑锛氫骇鐢熸敮鎸?span>reporacle鐢ㄦ埛涓?span>dept琛ㄥ鍒剁殑鏁版嵁搴撹Е鍙戝櫒鍜岀▼搴忓寘)

鈶c€佺‘璁ゅ鍒剁殑缁勫拰瀵硅薄宸茬粡鍔犲叆鏁版嵁搴撶殑鏁版嵁瀛楀吀

SQL>select gname, master, status from dba_repgroup;

SQL>select * from dba_repobject;

7銆佸垱寤轰富澶嶅埗鑺傜偣

鈶犮€佺敤repadmin韬唤鐧诲綍orcl214鏁版嵁搴擄紝鍒涘缓涓诲鍒惰妭鐐?/span>

SQL>execute dbms_repcat.add_master_database(gname=>'REPORACLE_MG1', master=>'ORCL217.TEST.COM.CN', use_existing_objects=>true, copy_rows=>true, propagation_mode=>'ASYNCHRONOUS');

鈶°€佺‘璁ゅ鍒剁殑浠诲姟闃熷垪宸茬粡鍔犲叆鏁版嵁搴撶殑鏁版嵁瀛楀吀

SQL>select * from user_jobs;

8銆佷娇鍚屾缁勭殑鐘舵€佺敱鍋滈】(quiesced)鏀逛负姝e父(normal)

鈶犮€佺敤repadmin韬唤鐧诲綍orcl214鏁版嵁搴擄紝杩愯浠ヤ笅鍛戒护

SQL> execute dbms_repcat.resume_master_activity('reporacle_mg1',true);

鈶°€佺‘璁ゅ悓姝ョ粍鐨勭姸鎬佷负姝e父(normal)

SQL> select gname, master, status from dba_repgroup;

9銆佸垱寤哄鍒舵暟鎹簱鐨勬椂闂磋〃锛屾垜浠亣璁剧敤鍥哄畾鐨勬椂闂磋〃锛?span>1鍒嗛挓澶嶅埗涓€娆°€?/span>

鈶犮€佺敤repadmin韬唤鐧诲綍orcl214鏁版嵁搴擄紝杩愯浠ヤ笅鍛戒护

SQL>begin

dbms_defer_sys.schedule_push (

destination => 'orcl217.test.com.cn',

interval => 'sysdate + 1/1440',

next_date => sysdate);

end;

/

銆€

SQL>begin

dbms_defer_sys.schedule_purge (

next_date => sysdate,

interval => 'sysdate + 1/1440',

delay_seconds => 0,

rollback_segment => '');

end;

/

銆€

鈶°€佺敤repadmin韬唤鐧诲綍orcl217鏁版嵁搴擄紝杩愯浠ヤ笅鍛戒护

SQL>begin

dbms_defer_sys.schedule_push (

destination => ' orcl214.test.com.cn ',

interval => 'sysdate + 1/1440',

next_date => sysdate);

end;

/

銆€

SQL>begin

dbms_defer_sys.schedule_purge (

next_date => sysdate,

interval => 'sysdate + 1/1440',

delay_seconds => 0,

rollback_segment => '');

end;

/

10銆佹坊鍔犳垨淇敼涓よ竟鏁版嵁搴撶殑璁板綍锛岃窡韪鍒惰繃绋?/span>

濡傛灉浣犳兂绔嬪埢鐪嬪埌娣诲姞鎴栦慨鏀瑰悗鏁版嵁搴撶殑璁板綍鐨勫彉鍖栵紝鍙互鍦?span>214搴?span>repadmin鐢ㄦ埛涓嬫壘鍒?span>push鐨?span>job_number锛岀劧鍚庤繍琛岋細

SQL>exec dbms_job.run(job_number);

璇存槑锛?span>job_number 涓虹敤select job,this_date,next_date,what from user_jobs;鍛戒护鏌ュ嚭鐨?span>job缂栧彿銆?/span>

文章评论

亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
鲜为人知的编程真相
鲜为人知的编程真相
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
老程序员的下场
老程序员的下场
10个调试和排错的小建议
10个调试和排错的小建议
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
编程语言是女人
编程语言是女人
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
程序员都该阅读的书
程序员都该阅读的书
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
总结2014中国互联网十大段子
总结2014中国互联网十大段子
中美印日四国程序员比较
中美印日四国程序员比较
程序员的鄙视链
程序员的鄙视链
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
一个程序员的时间管理
一个程序员的时间管理
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
那些争议最大的编程观点
那些争议最大的编程观点
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
旅行,写作,编程
旅行,写作,编程
我的丈夫是个程序员
我的丈夫是个程序员
 程序员的样子
程序员的样子
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
每天工作4小时的程序员
每天工作4小时的程序员
程序员和编码员之间的区别
程序员和编码员之间的区别
我是如何打败拖延症的
我是如何打败拖延症的
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
如何成为一名黑客
如何成为一名黑客
程序员应该关注的一些事儿
程序员应该关注的一些事儿
为什么程序员都是夜猫子
为什么程序员都是夜猫子
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
漫画:程序员的工作
漫画:程序员的工作
Java程序员必看电影
Java程序员必看电影
程序员必看的十大电影
程序员必看的十大电影
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有