oracle 10g 流復(fù)制實(shí)驗(yàn)-源端捕獲

1、.：.；ORACLE10gSTREAM高級(jí)流復(fù)制技術(shù)實(shí)驗(yàn)環(huán)境操作系統(tǒng)：Microsoft Windows XP 版本 5.1.2600數(shù)據(jù)庫(kù)：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0實(shí)驗(yàn)步驟2.1主庫(kù)配置： DB_NAME=SOURCE ORACLE_SID=SOURCE 2.2從庫(kù)配置： DB_NAME=DEST ORACLE_SID=DEST 2.3主庫(kù)監(jiān)聽(tīng)配置： # LISTENER.ORA NETWORK CONFIGURATION FILE: D:ORACLEPRODUCT10.2.0DB_1NETWORKAD

2、MINLISTENER.ORA# GENERATED BY ORACLE CONFIGURATION TOOLS.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME =OK3) (ORACLE_HOME = D:ORACLEPRODUCT10.2.0DB_1) (SID_NAME=SOURCE) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) (ADDRESS = (PROTOCOL = T

3、CP)(HOST = PC-202106151039)(PORT = 1521) ) ) 2.4從庫(kù)監(jiān)聽(tīng)配置： # LISTENER.ORA NETWORK CONFIGURATION FILE: D:ORACLEPRODUCT10.2.0DB_1NETWORKADMINLISTENER.ORA# GENERATED BY ORACLE CONFIGURATION TOOLS.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME =OK4) (ORACLE_HOME = D:ORACLEPRODUCT10.2.0DB_1) (SI

4、D_NAME=DEST) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) (ADDRESS = (PROTOCOL = TCP)(HOST = PC-202106151039)(PORT = 1521) ) )2.5主庫(kù)網(wǎng)絡(luò)銜接串# TNSNAMES.ORA NETWORK CONFIGURATION FILE: D:ORACLEPRODUCT10.2.0DB_1NETWORKADMINTNSNAMES.ORA# GENERATED BY ORACLE C

5、ONFIGURATION TOOLS.SOURCE=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-202106151039)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =OK3) )2.6從庫(kù)網(wǎng)絡(luò)銜接串# TNSNAMES.ORA NETWORK CONFIGURATION FILE: D:ORACLEPRODUCT10.2.0DB_1NETWORKADMINTNSNAMES.ORA# GENERATED BY ORACLE CONFIGURATIO

6、N TOOLS.DEST=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-202106151039)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =OK4) ) 2.7啟用追加日志可以基于DATABASE級(jí)別或TABLE級(jí)別，啟用追加日志SUPPLEMENTAL LOG。在建立根據(jù)SCHEMA粒度進(jìn)展復(fù)制的ORACLE STREAM環(huán)境中，假設(shè)確認(rèn)SCHEMA下一切TABLE都有合理的主鍵(PRIMARY KEY)，那么不再需求啟用追加日志。#啟用DAT

7、ABASE 追加日志ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;#啟用TABLE追加日志ALTER TABLE ADD SUPPLEMENT LOG GROUP LOG_GROUP_NAME(TABLE_COLUMN_NAME) ALWAYS;2.8創(chuàng)建DBLINK根據(jù)ORACLE 10GR2 STREAM官方文檔，針對(duì)主數(shù)據(jù)庫(kù)建立的數(shù)據(jù)庫(kù)鏈的名字必需和從數(shù)據(jù)庫(kù)的GLOBAL_NAME一樣。假設(shè)需求修正GLOBAL_NAME，執(zhí)行ALTER DATABASE RENAME GLOBAL_NAME TO XXX;2.9創(chuàng)建主庫(kù)數(shù)據(jù)庫(kù)鏈路C:SET ORACL

8、E_SID=SOURCE;C:SQLPLUS / AS SYSDBASQL CREATE USER STRMADMIN IDENTIFIED BY STRMADMIN ;用戶已創(chuàng)建。 SQL GRANT DBA,CONNECT,RESOURCE, ALL PRIVILEGES TO STRMADMIN;授權(quán)勝利。SQL CONN STRMADMIN/STRMADMIN已銜接。#以STRMADMIN身份，登錄主數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADMIN#建立數(shù)據(jù)庫(kù)鏈CREATE DATABASE LINK BOOK CONNECT TO STRMADMIN IDENTIFIE

9、D BY STRMADMIN USING BOOK;3.0創(chuàng)建從庫(kù)數(shù)據(jù)庫(kù)鏈C:SET ORACLE_SID=DEST;C:SQLPLUS / AS SYSDBASQL CREATE USER STRMADMIN IDENTIFIED BY STRMADMIN ;用戶已創(chuàng)建。 SQL GRANT DBA,CONNECT,RESOURCE, ALL PRIVILEGES TO STRMADMIN;授權(quán)勝利。SQL CONN STRMADMIN/STRMADMIN已銜接。以STRMADMIN身份，登錄從數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADMIN建立數(shù)據(jù)庫(kù)鏈CREATE DATAB

10、ASE LINK CISCOSYS CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING CISCOSYS;3.1創(chuàng)建MASTER流隊(duì)列#以STRMADMIN身份，登錄主數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADMIN;BEGINDBMS_STREAMS_ADM.SET_UP_QUEUE(QUEUE_TABLE = SOURCE_QUEUE_TABLE,QUEUE_NAME = SOURCE_QUEUE);END;3.2創(chuàng)建BACKUP流隊(duì)列#以STRMADMIN身份，登錄從數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADM

11、INBEGINDBMS_STREAMS_ADM.SET_UP_QUEUE(QUEUE_TABLE = DEST_QUEUE_TABLE,QUEUE_NAME = DEST_QUEUE);END;/3.3 創(chuàng)建捕獲進(jìn)程以STRMADMIN身份，登錄主數(shù)據(jù)庫(kù)。提示一下，本文檔以HR用戶做例如。CONNECT STRMADMIN/STRMADMINBEGINDBMS_STREAMS_ADM.ADD_SCHEMA_RULES(SCHEMA_NAME = HR,STREAMS_TYPE = CAPTURE,STREAMS_NAME = CAPTURE_SOURCE,QUEUE_NAME = STRMAD

12、MIN.SOURCE_QUEUE,INCLUDE_DML = TRUE,INCLUDE_DDL = TRUE,INCLUDE_TAGGED_LCR = FALSE,SOURCE_DATABASE = NULL,INCLUSION_RULE = TRUE);END;/3.4 實(shí)例化復(fù)制數(shù)據(jù)庫(kù)在主數(shù)據(jù)庫(kù)環(huán)境中，執(zhí)行如下SHELL語(yǔ)句。假設(shè)從庫(kù)的HR用戶不存在，建立一個(gè)HR的空用戶。EXP USERID=HR/HRSOURCE FILE=C:HR.DMP OBJECT_CONSISTENT=Y ROWS=YIMP USERID=HR/HRDEST FILE=C:HR.DMP IGNORE=Y COM

13、MIT=Y LOG=F:HR.LOG STREAMS_INSTANTIATION=Y FROMUSER=HR TOUSER=HR3.5 創(chuàng)建傳播進(jìn)程以STRMADMIN身份，登錄主數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADMIN/BEGINDBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (SCHEMA_NAME = HR,STREAMS_NAME = SOURCE_TO_DEST,SOURCE_QUEUE_NAME = STRMADMIN.SOURCE_QUEUE,DESTINATION_QUEUE_NAME = STRMADMIN.D

14、EST_QUEUEDEST,INCLUDE_DML = TRUE,INCLUDE_DDL = TRUE,INCLUDE_TAGGED_LCR = FALSE,SOURCE_DATABASE = SOURCE,INCLUSION_RULE = TRUE);END;/#修正PROPAGATION休眠時(shí)間為0，表示實(shí)時(shí)傳播LCR。BEGINDBMS_AQADM.ALTER_PROPAGATION_SCHEDULE (QUEUE_NAME = SOURCE_QUEUE,DESTINATION = DEST,LATENCY = 0);END;/3.6 創(chuàng)建運(yùn)用進(jìn)程#以STRMADMIN身份，登錄從數(shù)據(jù)庫(kù)

15、。CONNECT STRMADMIN/STRMADMIN/BEGINDBMS_STREAMS_ADM.ADD_SCHEMA_RULES (SCHEMA_NAME = HR,STREAMS_TYPE = APPLY,STREAMS_NAME = APPLY_DEST,QUEUE_NAME = STRMADMIN.DEST_QUEUE,INCLUDE_DML = TRUE,INCLUDE_DDL = TRUE,INCLUDE_TAGGED_LCR = FALSE,SOURCE_DATABASE = SOURCE,INCLUSION_RULE = TRUE);END;/3.7 啟動(dòng)STREAM#以S

16、TRMADMIN身份，登錄從數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADMIN#啟動(dòng)APPLY進(jìn)程BEGINDBMS_APPLY_ADM.START_APPLY (APPLY_NAME = APPLY_DEST);END;/#以STRMADMIN身份，登錄主數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADMIN#啟動(dòng)CAPTURE進(jìn)程BEGINDBMS_CAPTURE_ADM.START_CAPTURE (CAPTURE_NAME = CAPTURE_SOURCE);END;/3.8 停頓STREAM以STRMADMIN身份，登錄主數(shù)據(jù)庫(kù)。CONNECT STRMADMIN

17、/STRMADMIN停頓CAPTURE進(jìn)程BEGINDBMS_CAPTURE_ADM.STOP_CAPTURE (CAPTURE_NAME = CAPTURE_SOURCE);END;/以STRMADMIN身份，登錄從數(shù)據(jù)庫(kù)。CONNECT STRMADMIN/STRMADMIN#停頓APPLY進(jìn)程BEGINDBMS_APPLY_ADM.STOP_APPLY (APPLY_NAME = APPLY_DEST);END;4.0 測(cè)試場(chǎng)景 本文檔建立了針對(duì)HR用戶的STREAM 復(fù)制環(huán)境，假設(shè)沒(méi)有特別聲明，以下測(cè)試場(chǎng)景均以HR用戶身份執(zhí)行。4.1 建一張表測(cè)試主數(shù)據(jù)庫(kù)SQL CREATE TABL

18、E T1(id NUMBER PRIMARY KEY, name VARCHAR2(50);Table created.從數(shù)據(jù)庫(kù)SQL DEST T1NAME NULL TYPE- - -ID NOT NULL NUMBERNAME VARCHAR2 (50)4.2 表中插入一行數(shù)據(jù)主數(shù)據(jù)庫(kù)SQL INSERT INTO T1 VALUES (1,A);1 ROW CREATED.SQL COMMIT;COMMIT COMPLETE.SQL從數(shù)據(jù)庫(kù)SQL SELECT * FROM T1;ID NAME- -1 A5 問(wèn)題診斷5.1 如何知道捕捉(Capture)進(jìn)程能否運(yùn)轉(zhuǎn)正常？以STRMA

19、DMIN身份，登錄主數(shù)據(jù)庫(kù)，執(zhí)行如下語(yǔ)句：SQL SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS FROM DBA_CAPTURE; 結(jié)果顯示如下：CAPTURE_NAME QUEUE_NAME- -RULE_SET_NAME NEGATIVE_RULE_SET_NAME STATUS- - -CAPTURE_PROD PROD_QUEUERULESET$_14 ENABLEDENABLED假設(shè)STATUS形狀是ENABLED，表示CAPTURE進(jìn)程運(yùn)轉(zhuǎn)正常；假設(shè)STATUS形狀是DI

20、SABLED，表示CAPTURE進(jìn)程處于停頓形狀，只需重新啟動(dòng)即可；假設(shè)STATUS形狀是ABORTED，表示CAPTURE進(jìn)程非正常停頓，查詢相應(yīng)的ERROR_NUMBER、ERROR_MESSAGE列可以得到詳細(xì)的信息；同時(shí)，ORACLE會(huì)在跟蹤文件中記錄該信息。5.2 如何知道CAPTURED LCR能否有傳播GAP？以STRMADMIN身份，登錄主數(shù)據(jù)庫(kù)，執(zhí)行如下語(yǔ)句：SQL SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN, APPLIED_SCN FROM DBA_CAPTURE;結(jié)果顯示如下：CAPTURE_NAME QUEUE_NAME STATUS CAPTURE_PROD PROD_QUEUE ENABLED- - - - -CAPTURED_SCN APPLIED_SCN 17023672 17023672假設(shè)APPLIED_SCN小于CAPTURED_SCN，那么表示在主數(shù)據(jù)庫(kù)一端，要么LCR沒(méi)有被DEQUEUE，要么PROPAGATION進(jìn)程尚未傳播到從數(shù)據(jù)庫(kù)一端。5.3 如何知道APPY進(jìn)