Oracle 10g stream 一對多複製

Oracle 流是一種資料共享的通用機制，可以被用於許多處理的基礎，包括訊息、複製和資料倉儲的 ETL 過程。它是高階佇列、LogMinor、作業排程等已存在技術的擴充套件。這裡做一個簡單的複製示例來說明其用法。試驗環境：

windows server 2003 sp1；10g 10.1.0.2
源庫：SID：db1 IP：10.1.8.201
目的庫：SID：db2 IP：10.1.9.49

源、目的庫的 tnsnames.ora 配置
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.201)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = db1)
(SERVER = DEDICATED)
)
)

DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.9.49)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db2)
(SERVER = DEDICATED)
)
)



1. 基本體系結構

流處理分為捕捉、傳輸、應用三個主要程式。

捕捉程式是一個可選的後臺程式。它從重做日誌中捕獲 DDL 和 DML 的變化，並且把它們封裝成邏輯改變記錄（LCRs）。預設的捕捉機制可以立即獲得使用者定義的事件。
傳輸程式把 LCRs 儲存到 SYS.AnyData 資料型別的佇列中。LCRs 在不同資料庫中的源和目的傳輸區域之間傳播。傳播使用工作佇列來排程。
應用程式是一個可選的後臺程式。它調出 LCRs 直接應用，或者為使用者自定義訊息作為引數傳遞給使用者自定義包。

捕獲和應用程式可以被用於表、模式、資料庫等級別，各級別的規則決定了其不同的行為。

2. 例項設定

CONN sys/password@DB1 AS SYSDBA
create pfile from spfile;
-- 編輯 initsid.ora 檔案，增加以下引數：
-- AQ_TM_PROCESSES=1;
-- GLOBAL_NAMES=TRUE;

SHUTDOWN IMMEDIATE;
create spfile from pfile;
STARTUP mount;
alter database archivelog; -- 改為歸檔模式
alter database open;

3. 流管理員設定

CONN sys/password@DB1 AS SYSDBA

CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT DBA, CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin;

GRANT ALL ON scott.dept TO strmadmin;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
END;
/

CONNECT strmadmin/strmadmin@DB1
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); -- 建立流佇列

CREATE DATABASE LINK db2 CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'DB2'; -- 建立資料庫連線

在目的庫（DB2）重複上面2、3步驟。

4. LogMinor 表空間設定

CONN sys/password@DB1 AS SYSDBA

CREATE TABLESPACE logmnr_ts DATAFILE 'E:ORACLEPRODUCT10.1.0ORADATADB1logmnr01.dbf' SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_ts');

5. SUPPLEMENTAL 日誌

CONN sys/password@DB1 AS SYSDBA
ALTER TABLE scott.dept ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (deptno) ALWAYS;

6. 配置傳播程式

CONNECT strmadmin/strmadmin@DB1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'scott.dept',
streams_name => 'db1_to_db2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@db2',
include_dml => true,
include_ddl => true,
source_database => 'db1');
END;
/

-- 檢查傳播程式作業
SELECT job,TO_CHAR(last_date, 'DD-Mon-YYYY HH24:MI:SS') last_date,TO_CHAR(next_date, 'DD-Mon-YYYY HH24:MI:SS') next_date,what FROM dba_jobs;

7. 配置捕捉程式

CONNECT strmadmin/strmadmin@DB1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'scott.dept',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true);
END;
/

8. 配置初始 SCN
在應用程式工作前必須在目的表中配置源表的 SCN。如果目的表已經存在，可用 exp/imp 後設資料完成。

exp userid=scott/tiger@db1 FILE=dept_instant.dmp TABLES=dept OBJECT_CONSISTENT=y ROWS=n
imp userid=scott/tiger@db2 FILE=dept_instant.dmp IGNORE=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y

因為在遷移後設資料時 SUPPLEMENTAL 日誌也被遷移。因為捕捉程式還沒有啟動，所以可以刪除這些日誌：

CONN sys/password@DB2 AS SYSDBA
ALTER TABLE scott.dept DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;

用 DBMS_APPLY_ADM 包設定 SCN

CONNECT strmadmin/strmadmin@db1
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DB2(
source_object_name => 'scott.dept',
source_database_name => 'db1',
instantiation_scn => v_scn);
END;
/

9. 配置應用程式

CONNECT strmadmin/strmadmin@DB2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'scott.dept',
streams_type => 'apply',
streams_name => 'apply_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'db1');
END;
/

10. 啟動應用程式

CONNECT strmadmin/strmadmin@DB2
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_simp',
parameter => 'disable_on_error',
value => 'n');

DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_simp');
END;
/

11. 啟動捕捉程式

CONNECT strmadmin/strmadmin@DB1
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_simp');
END;
/

12. 測試

-- 測試 DML

CONNECT scott/tiger@db1
INSERT INTO dept (deptno, dname, loc) VALUES (99, 'Test Dept', 'UK');
COMMIT;

SELECT * FROM dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
99 Test Dept UK

5 rows selected.

CONNECT scott/tiger@db2
SELECT * FROM dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
99 Test Dept UK

5 rows selected.

-- 測試 DDL

CONNECT scott/tiger@db1
ALTER TABLE dept ADD (new_col NUMBER(10))
/
DESC dept

Name Null? Type
---------------------------- -------- --------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
NEW_COL NUMBER(10)

CONNECT scott/tiger@db2
DESC dept

Name Null? Type
---------------------------- -------- --------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
NEW_COL NUMBER(10)

-- 可以用下列語句檢視流的內容
CONNECT strmadmin/strmadmin@DB1

SELECT s.user_data.getTypeName() FROM streams_queue_table s;

SET SERVEROUTPUT ON
DECLARE
v_anydata SYS.ANYDATA;
v_lcr SYS.LCR$_ROW_RECORD;
v_row_list SYS.LCR$_ROW_LIST;
v_result PLS_INTEGER;
BEGIN

SELECT user_data
INTO v_anydata
FROM strmadmin.streams_queue_table
WHERE rownum < 2;

v_result := ANYDATA.GetObject(
self => v_anydata,
obj => v_lcr);

DBMS_OUTPUT.PUT_LINE('Command Type : ' || v_lcr.Get_Command_Type);
DBMS_OUTPUT.PUT_LINE('Object Owner : ' || v_lcr.Get_Object_Owner);
DBMS_OUTPUT.PUT_LINE('Object Name : ' || v_lcr.Get_Object_Name);
DBMS_OUTPUT.PUT_LINE('Source Database Name : ' || v_lcr.Get_Source_Database_Name);
END;
/

13. 清理

-- 可以使用下面的語句識別並刪除所有規則
conn / as sysdba

BEGIN
FOR cur_rec IN (SELECT rule_owner, rule_name FROM dba_rules) LOOP
DBMS_RULE_ADM.DROP_RULE(
rule_name => cur_rec.rule_owner || '.' || cur_rec.rule_name,
force => TRUE);
END LOOP;
END;
/

-- 識別、停止並刪除所有捕捉程式和應用程式
conn / as sysdba

BEGIN
FOR cur_rec IN (SELECT capture_name FROM dba_capture) LOOP
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => cur_rec.capture_name);
DBMS_CAPTURE_ADM.DROP_CAPTURE(
capture_name => cur_rec.capture_name);
END LOOP;

FOR cur_rec IN (SELECT apply_name FROM dba_apply) LOOP
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => cur_rec.apply_name);
DBMS_APPLY_ADM.DROP_APPLY(
apply_name => cur_rec.apply_name);
END LOOP;
END;
/

-- 使用下面的語句徹底刪除相關物件的流資訊
BEGIN
DBMS_STREAMS_ADM.PURGE_SOURCE_CATALOG(
source_database => 'db1',
source_object_name => 'scott.dept',
source_object_type => 'TABLE');
END;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-757990/，如需轉載，請註明出處，否則將追究法律責任。