oracle 10g 流復制實驗-源端捕獲.docx

ORACLE10gSTREAM高級流復制技術一、 實驗環(huán)境操作系統(tǒng)：Microsoft Windows XP 版本 5.1.2600數(shù)據(jù)庫：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0二、 實驗步驟2.1主庫配置： DB_NAME=SOURCE ORACLE_SID=SOURCE 2.2從庫配置： DB_NAME=DEST ORACLE_SID=DEST 2.3主庫監(jiān)聽配置： # 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 =OK3) (ORACLE_HOME = D:ORACLEPRODUCT10.2.0DB_1) (SID_NAME=SOURCE) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521) ) ) 2.4從庫監(jiān)聽配置： # 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) (SID_NAME=DEST) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521) ) )2.5主庫網(wǎng)絡連接串# TNSNAMES.ORA NETWORK CONFIGURATION FILE: D:ORACLEPRODUCT10.2.0DB_1NETWORKADMINTNSNAMES.ORA# GENERATED BY ORACLE CONFIGURATION TOOLS.SOURCE=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =OK3) )2.6從庫網(wǎng)絡連接串# TNSNAMES.ORA NETWORK CONFIGURATION FILE: D:ORACLEPRODUCT10.2.0DB_1NETWORKADMINTNSNAMES.ORA# GENERATED BY ORACLE CONFIGURATION TOOLS.DEST=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-201006151039)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =OK4) ) 2.7啟用追加日志可以基于DATABASE級別或TABLE級別，啟用追加日志（SUPPLEMENTAL LOG）。在建立根據(jù)SCHEMA粒度進行復制的ORACLE STREAM環(huán)境中，如果確認SCHEMA下所有TABLE都有合理的主鍵(PRIMARY KEY)，則不再需要啟用追加日志。#啟用DATABASE 追加日志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官方文檔，針對主數(shù)據(jù)庫建立的數(shù)據(jù)庫鏈的名字必須和從數(shù)據(jù)庫的GLOBAL_NAME相同。如果需要修改GLOBAL_NAME，執(zhí)行ALTER DATABASE RENAME GLOBAL_NAME TO XXX;2.9創(chuàng)建主庫數(shù)據(jù)庫鏈路C:SET ORACLE_SID=SOURCE;C:SQLPLUS / AS SYSDBASQL CREATE USER STRMADMIN IDENTIFIED BY STRMADMIN ;用戶已創(chuàng)建。 SQL GRANT DBA,CONNECT,RESOURCE, ALL PRIVILEGES TO STRMADMIN;授權成功。SQL CONN STRMADMIN/STRMADMIN已連接。#以STRMADMIN身份，登錄主數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMIN#建立數(shù)據(jù)庫鏈CREATE DATABASE LINK BOOK CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING BOOK;3.0創(chuàng)建從庫數(shù)據(jù)庫鏈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;授權成功。SQL CONN STRMADMIN/STRMADMIN已連接。以STRMADMIN身份，登錄從數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMIN建立數(shù)據(jù)庫鏈CREATE DATABASE LINK CISCOSYS CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING CISCOSYS;3.1創(chuàng)建MASTER流隊列#以STRMADMIN身份，登錄主數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMIN;BEGINDBMS_STREAMS_ADM.SET_UP_QUEUE(QUEUE_TABLE = SOURCE_QUEUE_TABLE,QUEUE_NAME = SOURCE_QUEUE);END;3.2創(chuàng)建BACKUP流隊列#以STRMADMIN身份，登錄從數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMINBEGINDBMS_STREAMS_ADM.SET_UP_QUEUE(QUEUE_TABLE = DEST_QUEUE_TABLE,QUEUE_NAME = DEST_QUEUE);END;/3.3 創(chuàng)建捕獲進程以STRMADMIN身份，登錄主數(shù)據(jù)庫。提醒一下，本文檔以HR用戶做示例。CONNECT STRMADMIN/STRMADMINBEGINDBMS_STREAMS_ADM.ADD_SCHEMA_RULES(SCHEMA_NAME = HR,STREAMS_TYPE = CAPTURE,STREAMS_NAME = CAPTURE_SOURCE,QUEUE_NAME = STRMADMIN.SOURCE_QUEUE,INCLUDE_DML = TRUE,INCLUDE_DDL = TRUE,INCLUDE_TAGGED_LCR = FALSE,SOURCE_DATABASE = NULL,INCLUSION_RULE = TRUE);END;/3.4 實例化復制數(shù)據(jù)庫在主數(shù)據(jù)庫環(huán)境中，執(zhí)行如下SHELL語句。如果從庫的HR用戶不存在，建立一個HR的空用戶。EXP USERID=HR/HRSOURCE FILE=C:HR.DMP OBJECT_CONSISTENT=Y ROWS=YIMP USERID=HR/HRDEST FILE=C:HR.DMP IGNORE=Y COMMIT=Y LOG=F:HR.LOG STREAMS_INSTANTIATION=Y FROMUSER=HR TOUSER=HR3.5 創(chuàng)建傳播進程以STRMADMIN身份，登錄主數(shù)據(jù)庫。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.DEST_QUEUEDEST,INCLUDE_DML = TRUE,INCLUDE_DDL = TRUE,INCLUDE_TAGGED_LCR = FALSE,SOURCE_DATABASE = SOURCE,INCLUSION_RULE = TRUE);END;/#修改PROPAGATION休眠時間為0，表示實時傳播LCR。BEGINDBMS_AQADM.ALTER_PROPAGATION_SCHEDULE (QUEUE_NAME = SOURCE_QUEUE,DESTINATION = DEST,LATENCY = 0);END;/3.6 創(chuàng)建應用進程#以STRMADMIN身份，登錄從數(shù)據(jù)庫。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 啟動STREAM#以STRMADMIN身份，登錄從數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMIN#啟動APPLY進程BEGINDBMS_APPLY_ADM.START_APPLY (APPLY_NAME = APPLY_DEST);END;/#以STRMADMIN身份，登錄主數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMIN#啟動CAPTURE進程BEGINDBMS_CAPTURE_ADM.START_CAPTURE (CAPTURE_NAME = CAPTURE_SOURCE);END;/3.8 停止STREAM以STRMADMIN身份，登錄主數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMIN停止CAPTURE進程BEGINDBMS_CAPTURE_ADM.STOP_CAPTURE (CAPTURE_NAME = CAPTURE_SOURCE);END;/以STRMADMIN身份，登錄從數(shù)據(jù)庫。CONNECT STRMADMIN/STRMADMIN#停止APPLY進程BEGINDBMS_APPLY_ADM.STOP_APPLY (APPLY_NAME = APPLY_DEST);END;4.0 測試場景 本文檔建立了針對HR用戶的STREAM 復制環(huán)境，如果沒有特別聲明，以下測試場景均以HR用戶身份執(zhí)行。4.1 建一張表測試主數(shù)據(jù)庫SQL CREATE TABLE T1(id NUMBER PRIMARY KEY, name VARCHAR2(50);Table created.從數(shù)據(jù)庫SQL DEST T1NAME NULL TYPE- - -ID NOT NULL NUMBERNAME VARCHAR2 (50)4.2 表中插入一行數(shù)據(jù)主數(shù)據(jù)庫SQL INSERT INTO T1 VALUES (1,A);1 ROW CREATED.SQL COMMIT;COMMIT COMPLETE.SQL從數(shù)據(jù)庫SQL SELECT * FROM T1;ID NAME- -1 A5 問題診斷5.1 如何知道捕捉(Capture)進程是否運行正常？以STRMADMIN身份，登錄主數(shù)據(jù)庫，執(zhí)行如下語句：SQL SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS FROM DBA_CAPTURE; 結果顯示如下：CAPTURE_NAME QUEUE_NAME- -RULE_SET_NAME NEGATIVE_RULE_SET_NAME STATUS- - -CAPTURE_PROD PROD_QUEUERULESET$_14 ENABLEDENABLED如果STATUS狀態(tài)是ENABLED，表示CAPTURE進程運行正常；如果STATUS狀態(tài)是DISABLED，表示CAPTURE進程處于停止狀態(tài)，只需重新啟動即可；如果STATUS狀態(tài)是ABORTED，表示CAPTURE進程非正常停止，查詢相應的ERROR_NUMBER、ERROR_MESSAGE列可以得到詳細的信息；同時，ORACLE會在跟蹤文件中記錄該信息。5.2 如何知道CAPTURED LCR是否有傳播GAP？以STRMADMIN身份，登錄主數(shù)據(jù)庫，執(zhí)行如下語句：SQL SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN, APPLIED_SCN FROM DBA_CAPTURE;結果顯示如下：CAPTURE_NAME QUEUE_NAME STATUS CAPTURE_PROD PROD_QUEUE ENABLED- - - - -CAPTURED_SCN APPLIED_SCN 17023672 17023672如果APPLIED_SCN小于CAPTURED_SCN，則表示在主數(shù)據(jù)庫一端，要么LCR沒有被DEQUEUE，要么PROPAGATION進程尚未傳播到從數(shù)據(jù)庫一端。5.3 如何知道APPY進