oracle 11g streams 配置詳解

1.引言
　　Oracle Stream功能是為提高資料庫的高可用性而設計的，在Oracle 9i及之前的版本這個功能被稱為Advance Replication。Oracle Stream利用高階佇列技術，透過解析歸檔日誌，將歸檔日誌解析

成DDL及DML語句，從而實現資料庫之間的同步。這種技術可以將整個資料庫、資料庫中的物件複製到另一資料庫中，透過使用Stream的技術，對歸檔日誌的挖掘，可以在對主系統沒有任何壓力的情況下

，實現對資料庫物件級甚至整個資料庫的同步。

　　解析歸檔日誌這種技術現在應用的比較廣泛，Quest公司的shareplex軟體及DSG公司的realsync都是這樣的產品，一些公司利用這樣的產品做應用級的容災。但shareplex或是realsync都是十分昂貴

的，因此你可以嘗試用Stream這個Oracle提供的不用額外花錢的功能。Oracle Stream對生產庫的影響是非常小的，從庫可以是與主庫不同的作業系統平臺，你可以利用Oracle Stream複製幾個從庫，從

庫可用於查詢、報表、容災等不同的功能。本文不談技術細節，只是以手把手的方式一步一步的帶你把Stream的環境搭建起來，細節內容可以查聯機文件。
   
參照文件：    http://www.cnblogs.com/rootq/articles/1230415.html 作者: 楊寶秋

2.概述

　　主資料庫：

　　作業系統：Red Hat Enterprise Linux Server release 5.5

　　IP地址：192.168.1.66

　　資料庫：Oracle 11.2.0.2.0

　　ORACLE_SID：szxddb

　　Global_name：szxddb

　　從資料庫：

　　作業系統：Red Hat Enterprise Linux Server release 5.5

　　IP地址：192.168.1.92

　　資料庫：Oracle 11.2.0.2.0

　　ORACLE_SID：szxtdb

　　Global_name：szxtdb

 

3.環境準備

3.1 設定初始化引數
    使用pfile的修改init.ora檔案，使用spfile的透過alter system命令修改spile檔案。主、從資料庫分別執行如下的語句：

  　Sqlplus ‘/ as sysdba’
　　alter system set aq_tm_processes=2 scope=both;
　　alter system set global_names=true scope=both;
　　alter system set job_queue_processes=10 scope=both;
　　alter system set parallel_max_servers=20 scope=both;
　　alter system set undo_retention=3600 scope=both;
　　alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
　　alter system set streams_pool_size=25M scope=spfile;
　　alter system set utl_file_dir='*' scope=spfile;
　　alter system set open_links=4 scope=spfile;
注：以上引數設定，關於數量設定的部分，若預設值大於需要修改的值，則不用調整

SQL> alter system set aq_tm_processes=2 scope=both;

SQL> alter system set global_names=true scope=both;

SQL> alter system set undo_retention=3600 scope=both;

SQL> alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;

SQL> alter system set streams_pool_size=25m scope=spfile;

SQL> alter system set utl_file_dir='*' scope=spfile;

執行完畢後重啟資料庫

SQL> shut immediate;
SQL> startup

3.2 將資料庫置為歸檔模式

設定log_archive_dest_1到相應的位置;設定log_archive_start為TRUE，即啟用自動歸檔功能;設定log_archive_format指定歸檔日誌的命令格式。
　　sqlplus ‘/ as sysdba’
　　alter system set log_archive_dest_1='LOCATION=/data/szxddb/archivefile/' scope=spfile;
　　alter system set log_archive_start=TRUE scope=spfile; --該引數在Oracle 11g已不適用，不用調整
　　alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
　　shutdown immediate;
　　startup mount;
　　alter database archivelog;
　　alter database open;

SQL> alter system set log_archive_dest_1='LOCATION=/data/szxddb/archivefile/' scope=spfile;

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

SQL> shut immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

資料庫置為歸檔模式後，可以按如下方式檢驗一下：
SQL> archive log list

3.3 建立stream管理使用者

3.3.1 建立主環境stream管理使用者
　　#以sysdba身份登入
　　connect / as sysdba
　　#建立主環境的Stream專用表空間
　　create tablespace tbs_stream datafile '/data/szxddb/datafile/tbs_stream01.dbf'
　　size 100m autoextend on maxsize unlimited segment space management auto;
　　#將logminer的資料字典從system表空間轉移到新建的表空間，防止撐滿system表空間
　　execute dbms_logmnr_d.set_tablespace('tbs_stream');
　　#建立Stream管理使用者
　　create user strmadmin identified by strmadmin
　　default tablespace tbs_stream temporary tablespace temp;
　　#授權Stream管理使用者
　　grant connect,resource,dba,aq_administrator_role to strmadmin;

SQL> begin
  2  dbms_streams_auth.grant_admin_privilege(
  3  grantee=>'strmadmin',
  4  grant_privileges=>TRUE);
  5  end;
  6  /

3.3.2 建立從環境stream管理使用者
　　#以sysdba身份登入
　　connect / as sysdba
　　#建立Stream專用表空間,這一步也可以參見3.3.1
　　create tablespace tbs_stream datafile '/data/szxtdb/datafile/tbs_stream01.dbf'
　　size 100m autoextend on maxsize unlimited segment space management auto;
　　#同樣，將logminer的資料字典從system表空間轉移到新建的表空間，防止撐滿system表空間
　　execute dbms_logmnr_d.set_tablespace('tbs_stream');
　　#建立Stream管理使用者
　　create user strmadmin identified by strmadmin
　　default tablespace tbs_stream temporary tablespace temp;
　　#授權Stream管理使用者
　　grant connect,resource,dba,aq_administrator_role to strmadmin;

SQL> begin
  2  dbms_streams_auth.grant_admin_privilege(
  3  grantee=>'strmadmin',
  4  grant_privileges=>TRUE);
  5  end;
  6  /

3.4 配置網路連線

　　3.4.1配置主環境tnsnames.ora

　　主資料庫(tnsnames.ora)中新增從資料庫的配置。

   SZXTDB =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.92)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = szxtdb)
     )
     )

　　3.4.2配置從環境tnsnames.ora

    SZXDDB =
     (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.66)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = szxddb)
     )
   )

3.5 啟用追加日誌
　　可以基於Database級別或Table級別，啟用追加日誌(Supplemental Log)。在建立根據Schema粒度進行復制的Oracle Stream環境中，如果確認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;

　　3.6 建立DBlink

　　根據Oracle 10gR2 Stream官方文件，針對主資料庫建立的資料庫鏈的名字必須和從資料庫的global_name相同。

　　如果需要修改global_name，執行“alter database rename global_name to xxx”。

　　3.6.1建立主資料庫資料庫鏈

　　#以strmadmin身份，登入主資料庫。

　　connect strmadmin/strmadmin

　　#建立資料庫鏈

　　create database link SZXTDB connect to strmadmin identified by strmadmin using 'SZXTDB';

　　3.6.2建立從資料庫資料庫鏈

　　#以strmadmin身份，登入從資料庫。

　　connect strmadmin/strmadmin

　　#建立資料庫鏈

　　create database link SZXDDB connect to strmadmin identified by strmadmin using 'SZXDDB';

3.7 建立流佇列

　　3.7.1建立Master流佇列

　　#以strmadmin身份，登入主資料庫。
　　connect strmadmin/strmadmin
　　begin
　　dbms_streams_adm.set_up_queue(
　　queue_table => 'szxddb_queue_table',
　　queue_name => 'szxddb_queue');
　　end;
　　/

　 3.7.2建立Backup流佇列

　　#以strmadmin身份，登入從資料庫。
　　connect strmadmin/strmadmin
　　begin
　　dbms_streams_adm.set_up_queue(
　　queue_table => 'szxtdb_queue_table',
　　queue_name => 'szxtdb_queue');
　　end;
　　/

　　3.8 建立捕獲程式

　　#以strmadmin身份，登入主資料庫。提醒一下，本文件以admin使用者做示例。
　　connect strmadmin/strmadmin
　　begin
　　dbms_streams_adm.add_schema_rules(
　　schema_name => 'admin',
　　streams_type => 'capture',
　　streams_name => 'capture_szxddb',
　　queue_name => 'strmadmin.szxddb_queue',
　　include_dml => true,
　　include_ddl => true,
　　include_tagged_lcr => false,
　　source_database => null,
　　inclusion_rule => true);
　　end;
　　/

3.9 例項化複製資料庫

　　在主資料庫環境中，執行如下Shell語句。如果從庫的admin使用者不存在，建立一個admin的空使用者。

　　exp file='/data/dump_dir/admin.dmp' object_consistent=y rows=y

　　imp file='/data/dump_dir/admin.dmp' ignore=y commit=y log='/data/dump_dir/admin.log' streams_instantiation=y fromuser=admin touser=admin

3.10 建立傳播程式

　　#以strmadmin身份，登入主資料庫。
　　connect strmadmin/strmadmin
　　begin
　　dbms_streams_adm.add_schema_propagation_rules(
　　schema_name => 'admin',
　　streams_name => 'szxddb_to_szxtdb',
　　source_queue_name => 'strmadmin.szxddb_queue',
　　destination_queue_name => ,
　　include_dml => true,
　　include_ddl => true,
　　include_tagged_lcr => false,
　　source_database => 'szxddb',
　　inclusion_rule => true);
　　end;
　　/
　　#修改propagation休眠時間為0，表示實時傳播LCR。
　　begin
　　dbms_aqadm.alter_propagation_schedule(
　　queue_name => 'szxddb_queue',
　　destination => 'szxtdb',
　　latency => 0);
　　end;
　　/

報錯：
SQL> run
  1  begin
  2  dbms_aqadm.alter_propagation_schedule(
  3  queue_name => 'prod_queue',
  4  destination => 'szxtdb',
  5  latency =>0);
  6* end;
begin
*
ERROR at line 1:
ORA-24042: no propagation schedule exists for QUEUE PROD_QUEUE and DESTINATION
SZXTDB
ORA-06512: at "SYS.DBMS_PRVTAQIP", line 1421
ORA-06512: at "SYS.DBMS_AQADM", line 971
ORA-06512: at line 2

注：跳過以上報錯，繼續以下可以正常進行,後續查詢相關資料，執行以下PL/SQL可以

SQL> begin
  2  dbms_aqadm.alter_propagation_schedule(
  3  queue_name => 'prod_queue',
  4  destination => 'szxtdb',
  5  destination_queue => 'szxtdb_queue',
  6  latency => 5);
  7  end;
  8  /

PL/SQL procedure successfully completed.

 

3.11 建立應用程式
　　#以strmadmin身份，登入從資料庫。
　　connect strmadmin/strmadmin
　　begin
　　dbms_streams_adm.add_schema_rules(
　　schema_name => 'admin',
　　streams_type => 'apply',
　　streams_name => 'apply_szxtdb',
　　queue_name => 'strmadmin.szxtdb_queue',
　　include_dml => true,
　　include_ddl => true,
　　include_tagged_lcr => false,
　　source_database => 'szxddb',
　　inclusion_rule => true);
　　end;
　　/

3.12 啟動STREAM

　　#以strmadmin身份，登入從資料庫。
　　connect strmadmin/strmadmin
　　#啟動Apply程式
　　begin
　　dbms_apply_adm.start_apply(
　　apply_name => 'apply_szxtdb');
　　end;
　　/
　　#以strmadmin身份，登入主資料庫。
　　connect strmadmin/strmadmin
　　#啟動Capture程式
　　begin
　　dbms_capture_adm.start_capture(
　　capture_name => 'capture_szxddb');
　　end;
　　/

3.13 停止STREAM

　　#以strmadmin身份，登入主資料庫。
　　connect strmadmin/strmadmin
　　#停止Capture程式
　　begin
　　dbms_capture_adm.stop_capture(
　　capture_name => 'capture_szxddb');
　　end;
　　/
　　#以strmadmin身份，登入從資料庫。
　　connect strmadmin/strmadmin
　　#停止Apply程式
　　begin
　　dbms_apply_adm.stop_apply(
　　apply_name => 'apply_szxtdb');
　　end;
　　/

　3.14 清除所有配置資訊
　　要清除Stream配置資訊，需要先執行3.13，停止Stream程式。

　　#以strmadmin身份，登入主資料庫。
　　connect strmadmin/strmadmin
　　exec DBMS_STREAMS_ADM.remove_streams_configuration();
　　#以strmadmin身份，登入從資料庫。
　　connect strmadmin/strmadmin
　　exec DBMS_STREAMS_ADM.remove_streams_configuration();

　　4 測試場景

　　本文件建立了針對admin使用者的Stream 複製環境，如果沒有特別宣告，以下測試場景均以admin使用者身份執行。

　　4.1 建一張表測試

　　主資料庫

　　SQL> CREATE TABLE TTT(id NUMBER PRIMARY KEY,

　　2 name VARCHAR2(50)

　　3 )

　　4 /

　　Table created.

　　從資料庫

　　SQL> desc TTT

　　Name Null? Type

　　---------- -------- -------------

　　ID NOT NULL NUMBER

　　NAME VARCHAR2(50)

　　4.2 表中插入一行資料

　　主資料庫

　　SQL> insert into ttt values (1,'sdfsdfsdfsdf');

　　1 row created.

　　SQL> commit;

　　Commit complete.

　　SQL>

　　從資料庫

　　SQL> select * from TTT;

　　ID NAME

　　---------- --------------------

　　1 sdfsdfsdfsdf

　　4.3 變更一下表的結構，新增一列

　　主資料庫

　　SQL> ALTER TABLE TTT ADD(age NUMBER(2));

　　Table altered

　　從資料庫

　　SQL> desc TTT

　　Name Null? Type

　　----------- -------- --------------

　　ID NOT NULL NUMBER

　　NAME VARCHAR2(50)

　　AGE NUMBER(2)

　　4.4 將表換一個表空間

　　主資料庫

　　SQL> SELECT table_name,tablespace_name FROM user_tables

　　2 　　 WHERE table_name='TTT';

　　TABLE_NAME TABLESPACE_NAME

　　------------------------------ ------------------------------

　　TTT USERS

　　SQL> ALTER TABLE TTT MOVE TABLESPACE tbs_stream;

　　Table altered

　　SQL> SELECT table_name,tablespace_name FROM user_tables

　　WHERE table_name='TTT';

　　TABLE_NAME TABLESPACE_NAME

　　------------------------------ ------------------------------

　　TTT TBS_STREAM

　　從資料庫

　　SQL> SELECT table_name,tablespace_name FROM user_tables

　　WHERE table_name='TTT';

　　TABLE_NAME TABLESPACE_NAME

　　------------------------------ ------------------------------

　　TTT TBS_STREAM

 

　　4.5 表上Name列建一索引

　　主資料庫

　　SQL> CREATE INDEX ttt_name_idx ON TTT(name);

　　Index created

　　從資料庫

　　SQL> SELECT table_name, index_name FROM user_indexes WHERE table_name = 'TTT';

　　TABLE_NAME INDEX_NAME

　　------------------------------ 　　　　------------------------------

　　TTT TTT_NAME_IDX

　　TTT SYS_C005721

　　4.6 Rebuild索引測試

　　主資料庫

　　SQL> ALTER INDEX ttt_name_idx REBUILD;

　　Index altered

　　從資料庫

　　SQL> SELECT table_name, index_name FROM user_indexes WHERE table_name = 'TTT';

　　TABLE_NAME INDEX_NAME

　　------------------------------ 　　　　------------------------------

　　TTT TTT_NAME_IDX

　　TTT SYS_C005721

4.7 索引換一個表空間測試

　　主資料庫

　　SQL> ALTER INDEX ttt_name_idx REBUILD TABLESPACE tbs_stream;

　　Index altered

　　從資料庫

　　SQL> SELECT table_name,index_name,tablespace_name FROM user_indexes

　　WHERE table_name = 'TTT';

　　TABLE_NAME INDEX_NAME TABLESPACE_NAME

　　------------------------------ ------------------------------ ------------------------------

　　TTT TTT_NAME_IDX TBS_STREAM

　　TTT SYS_C005721 USERS

　　4.8 刪除索引測試

　　主資料庫

　　SQL> DROP INDEX ttt_name_idx;

　　Index dropped

　　從資料庫

　　SQL> SELECT table_name,index_name,tablespace_name FROM user_indexes

　　WHERE table_name = ‘TTT’;

　　TABLE_NAME INDEX_NAME TABLESPACE_NAME

　　------------------------------ ------------------------------ ------------------------------

　　TTT SYS_C005721 USERS

　　4.9 刪除表測試

　　主資料庫

　　SQL> DROP TABLE ttt;

　　Table dropped

　　從資料庫

　　SQL> DESC ttt;

　　Object ttt does not exist.

4.10 建一張帶有LOB型別欄位的表測試

　　主資料庫

　　SQL> CREATE TABLE tttclob(id NUMBER PRIMARY KEY, memo CLOB);

　　Table created

　　從資料庫

　　SQL> DESC tttclob;

　　Name Null? Type

　　----------- -------- --------------

　　ID NOT NULL NUMBER

　　MEMO CLOB

　　4.11 表中插入一行資料

　　主資料庫

　　SQL> INSERT INTO tttclob VALUES(1,'clob_test');

　　1 row inserted

　　SQL> commit;

　　Commit complete

　　從資料庫

　　SQL> SELECT * FROM tttclob;

　　ID MEMO

　　---------- --------------------------------------------------------------------------------

　　1 clob_test

4.12 建立Type測試

　　主資料庫

　　SQL> CREATE or REPLACE TYPE ttttype;

　　2 /

　　Type created

　　從資料庫

　　SQL> SELECT * FROM user_types WHERE type_name='TTTTYPE';

　　TYPE_NAME TYPE_OID TYPECODE ATTRIBUTES METHODS PREDEFINED INCOMPLETE FINAL INSTANTIABLE SUPERTYPE_OWNER SUPERTYPE_NAME LOCAL_ATTRIBUTES LOCAL_METHODS TYPEID

　　------------------------------ -------------------------------- ------------------------------ ---------- ---------- ---------- ---------- ----- ------------

------------------------------ ------------------------------ ---------------- ------------- --------------------------------

　　TTTTYPE 1B36AAF10DA8301DE040A8C0289A77B4 OBJECT 0 0 NO YES YES YES

　　4.13 刪除Type測試

　　主資料庫

　　SQL> DROP TYPE ttttype;

　　Type dropped

　　從資料庫

　　SQL> SELECT * FROM user_types WHERE type_name='TTTTYPE';

　　TYPE_NAME TYPE_OID TYPECODE ATTRIBUTES METHODS PREDEFINED INCOMPLETE FINAL INSTANTIABLE SUPERTYPE_OWNER SUPERTYPE_NAME LOCAL_ATTRIBUTES LOCAL_METHODS TYPEID

5 問題診斷
　　5.1 如何知道捕捉(Capture)程式是否執行正常?

　　以strmadmin身份，登入主資料庫，執行如下語句：

　　SQL> SELECT CAPTURE_NAME,

　　2 QUEUE_NAME,

　　3 RULE_SET_NAME,

　　4 　　　　 NEGATIVE_RULE_SET_NAME,

　　5 STATUS

　　6 FROM DBA_CAPTURE;

　　結果顯示如下：

　　CAPTURE_NAME QUEUE_NAME

　　------------------------------ ------------------------------

　　RULE_SET_NAME NEGATIVE_RULE_SET_NAME STATUS

　　------------------------------ ------------------------------ --------

　　CAPTURE_PROD PROD_QUEUE

　　RULESET$_14 ENABLED

　　ENABLED

　　如果STATUS狀態是ENABLED，表示Capture程式執行正常;

　　如果STATUS狀態是DISABLED，表示Capture程式處於停止狀態，只需重新啟動即可;

　　如果STATUS狀態是ABORTED，表示Capture程式非正常停止，查詢相應的ERROR_NUMBER、ERROR_MESSAGE列可以得到詳細的資訊;同時，Oracle會在跟蹤檔案中記錄該資訊。

5.2 如何知道Captured LCR是否有傳播GAP?

　　以strmadmin身份，登入主資料庫，執行如下語句：

　　SQL> SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN, APPLIED_SCN

　　2 FROM DBA_CAPTURE;

　　結果顯示如下：

　　CAPTURE_NAME QUEUE_NAME STATUS

　　------------------------------ ------------------------------ --------

　　CAPTURED_SCN APPLIED_SCN

　　------------ -----------

　　CAPTURE_PROD PROD_QUEUE ENABLED

　　17023672 17023672

　　如果APPLIED_SCN小於CAPTURED_SCN，則表示在主資料庫一端，要麼LCR沒有被dequeue，要麼Propagation程式尚未傳播到從資料庫一端。

5.3 如何知道Appy程式是否執行正常?

　　以strmadmin身份，登入從資料庫，執行如下語句：

　　SQL> SELECT apply_name, apply_captured, status FROM dba_apply;

　　結果顯示如下：

　　APPLY_NAME APPLY_ STATUS

　　---------------------- ------ ----------------

　　APPLY_H10G YES ENABLED

　　如果STATUS狀態是ENABLED，表示Apply程式執行正常;

　　如果STATUS狀態是DISABLED，表示Apply程式處於停止狀態，只需重新啟動即可;

　　如果STATUS狀態是ABORTED，表示Apply程式非正常停止，查詢相應的ERROR_NUMBER、ERROR_MESSAGE列可以得到詳細的資訊;同時，可以查詢DBA_APPLY_ERROR檢視，瞭解詳細的Apply錯誤資訊。

　　6 結篇

　　透過如上的測試可以看出stream的功能還是十分強大的，透過配置Oracle Stream可以更大的提升資料庫的可用性和安全性,如此一個好用且不用花費高昂額外費用的功能還是很值得一用的。