Oracle Stream 配置 環境：
OS:Redhat Linux
Oracle 10.2.0.4 source:TEST
destnation:STANDBY
== 準備==
1. 歸檔日志配置：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog ---以下沒有專門說明的都是sys使用者--- 2.在源stream1上配置Supplemental loging： 在源上：
SQL> alter database add supplemental log data; Database altered. 3.兩台機器上修改global_name: stream1:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to TEST.com; stream2:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to STANDBY.com; 4.修改初始化參數：
參考官方文檔确定需要來檢查和設定參數
5. 建立stream管理使用者并表空間，配置權限，source和dest：
5.1 為strmadmin使用者建立獨立表空間
TEST: SQL> CREATE TABLESPACE streams_tbs DATAFILE '/oradata/TEST/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created. STANDBY:
SQL> CREATE TABLESPACE streams_tbs DATAFILE '/oradata/STANDBY/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; Tablespace created. 5.2 source和dest建立相同使用者和權限：
SQL> CREATE USER strmadmin IDENTIFIED BY strmadminpw
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs; User created. SQL> GRANT DBA TO strmadmin; Grant succeeded. SQL> BEGIN
2 DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
3 grantee => 'strmadmin',
4 grant_privileges => true);
5 END;
6 / PL/SQL procedure successfully completed.
6. 配置listener.ora和tnsnames.ora保證： TEST 上： sqlplus strmadmin/[email protected]
STANDBY 上： sqlplus strmadmin/[email protected]
能夠連通 ---如果沒有說明以下都是strmadmin/strmadminpw 使用者---
7. 建立DB link:
單向複制之需要一個database link即source 到dest:TEST-->STANDBY TEST上：
sqlplus strmadmin/strmadminpw
SQL> CREATE DATABASE LINK STANDBY.com CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'STANDBY'; Database link created.
測試：
SQL> select * from [email protected]; GLOBAL_NAME
-----------------------------------------
STANDBY.com ==開始stream的配置=== 8. source和dest 建立隊列： TEST,STANDBY都要建立
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); PL/SQL procedure successfully completed.
上面指令會建立一個隊列預設名：streams_queue，隊清單預設是： STREAMS_QUEUE_TABLE
隊列存儲的object類型是 anaydata
可以用查詢dba_queues,dba_queue_tables來檢查：
SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN'; OWNER QUEUE_TABLE NAME
---------------- ---------------------------------- ---------------------------------------------
STRMADMIN STREAMS_QUEUE_TABLE STREAMS_QUEUE
STRMADMIN STREAMS_QUEUE_TABLE AQ$_STREAMS_QUEUE_TABLE_E SQL> select owner,queue_table,object_type from dba_queue_tables where owner='STRMADMIN'; OWNER QUEUE_TABLE OBJECT_TYPE
----------------- --------------------------------- -------------------------------
STRMADMIN STREAMS_QUEUE_TABLE SYS.ANYDATA 9. 在source: TEST上建立Stream propagation： SQL> BEGIN
1 DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
2 schema_name => 'scott',
3 streams_name => 'TEST_to_STANDBY',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => '[email protected] ',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'TEST.com',
9 inclusion_rule => true,
10 queue_to_queue => true);
11 END;
12 / PL/SQL procedure successfully completed. 可以通過dba_propagations檢視結果：
SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation; PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBL STATUS
-------------------------------- ------------------------------ --------------------------------- --------------- --------
STREAM1_TO_STREAM2 STREAMS_QUEUE STREAMS_QUEUE STANDBY.COM ENABLED
9. 在source: TEST上建立Capture程序： SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
3 schema_name => 'scott',
4 streams_type => 'capture',
5 streams_name => 'capture_TEST',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 inclusion_rule => true);
10 END;
11 / PL/SQL procedure successfully completed. 可将hr改為scott 可以通過dba_capture檢視：
SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture; CAPTURE_NAME QUEUE_NAME START_SCN STATUS CAPTURE_TY
---------------------------- -------------------------- --------------- ------------ ----------
CAPTURE_STREAM1 STREAMS_QUEUE 504733 DISABLED LOCAL SQL> select * from ALL_CAPTURE_PREPARED_SCHEMAS;
SCHEMA_NAME TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
-------------------- --------------- -------------- -------------- ------------- --------
HR 12-JUN-08 IMPLICIT IMPLICIT IMPLICIT NO 10.将TEST上的scott schema資料導到STANDBY上：
在stream2上： 可先将scott使用者删除 drop user scott cascade;
sqlplus strmadmin/strmadminpw
SQL> create public database link TEST.com connect to strmadmin identified by strmadminpw using 'TEST'; Database link created. SQL> select * from [email protected]; GLOBAL_NAME
------------------------------------------------------
TEST 導資料：
[[email protected] admin]$ impdp strmadmin/strmadminpw network_link=TEST.com schemas=scott Import: Release 10.2.0.2.0 - Production on Thursday, 12 June, 2008 17:47:59 Copyright (c) 2003, 2005, Oracle. All rights reserved.
.....
......
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:50:32 遇到的問題：在導資料時發現導完後兩邊的表不一緻，後發現在STANDBY從庫上有scott使用者及相關聯的表，可使用指令drop user scott cascade 删除scott使用者，再進行導資料即可解決此問題。 11. 在TEST上設定STANDBY上scott schema的Instantiation SCN sqlplus strmadmin/strmadminpw
SQL> DECLARE
2 iscn NUMBER;
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 [email protected](
6 source_schema_name => 'scott',
7 source_database_name => 'TEST.com',
8 instantiation_scn => iscn,
9 recursive => true);
10 END;
11 / PL/SQL procedure successfully completed.
12. 在STANDBY上建立apply程序apply_STANDBY： SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
3 schema_name => '',
4 streams_type => 'apply',
5 streams_name => 'apply_STANDBY',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'TEST.com',
10 inclusion_rule => true);
11 END;
12 /
PL/SQL procedure successfully completed. 可以通過：
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server
檢視狀态 SQL> select apply_name,queue_name,status from dba_apply; APPLY_NAME QUEUE_NAME STATUS
--------------------- ------------------------ --------
APPLY_STREAM2 STREAMS_QUEUE DISABLED ==啟動== 13.啟動capture和apply: 13.1 STANDBY上啟動 Apply Process CONNECT strmadmin/strmadminpw
SQL> BEGIN
2 DBMS_APPLY_ADM.SET_PARAMETER(
3 apply_name => 'apply_STANDBY',
4 parameter => 'disable_on_error',
5 value => 'n');
6 END;
7 / PL/SQL procedure successfully completed. SQL> BEGIN
2 DBMS_APPLY_ADM.START_APPLY(
3 apply_name => 'apply_STANDBY');
4 END;
5 / PL/SQL procedure successfully completed. SQL> select apply_name,queue_name,status from dba_apply; APPLY_NAME QUEUE_NAME STATUS
------------------------------ -------------------------- --------
APPLY_STREAM2 STREAMS_QUEUE ENABLED 這時候alert log有：
Thu Jun 12 18:00:36 2008
Streams APPLY A001 started with pid=25, OS id=30819
Streams Apply Reader started P000 with pid=26 OS id=30821
Streams Apply Server started P001 with pid=27 OS id=30823 如果有問題，沒有能啟動就查dba_apply的error_messages列
13.2 TEST 上啟動capture process: sqlplus strmadmin/strmadminpw
SQL> BEGIN
2 DBMS_CAPTURE_ADM.START_CAPTURE(
3 capture_name => 'capture_stream1');
4 END;
5 / PL/SQL procedure successfully completed. SQL> select capture_name,status from dba_capture; CAPTURE_NAME STATUS
------------------------------ ------------
CAPTURE_STREAM1 ENABLED alert 日志有：
Thu Jun 12 18:04:46 2008
Streams CAPTURE C001 started with pid=27, OS id=11884
Thu Jun 12 18:04:49 2008
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 1
LOGMINER: Memory Size = 10M, Checkpoint interval = 10M
LOGMINER: session# = 1, builder process P001 started with pid=32 OS id=11966
LOGMINER: session# = 1, reader process P000 started with pid=29 OS id=11964
LOGMINER: session# = 1, preparer process P002 started with pid=33 OS id=11968
Thu Jun 12 18:04:53 2008
LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf
Thu Jun 12 18:06:40 2008
Thread 1 advanced to log sequence 15
Current log# 2 seq# 15 mem# 0: /u01/oradata/stream1/redo02.log
Thu Jun 12 18:06:53 2008
Thread 1 advanced to log sequence 16
Current log# 3 seq# 16 mem# 0: /u01/oradata/stream1/redo03.log
...
LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf
Thu Jun 12 18:07:34 2008
LOGMINER: End mining logfile: /u01/oradata/stream1_arch/1_13_657197948.dbf
Thu Jun 12 18:07:34 2008
LOGMINER: Begin mining logfile: /u01/oradata/stream1_arch/1_14_657197948.dbf 資訊，說明已經開始mining logfile. ===測試=== 14.測試
在TEST上：
SQL> conn scott/tiger
SQL> select employee_id,salary from employees where employee_id=197; EMPLOYEE_ID SALARY
-------------- ----------
197 3000 SQL> update employees set salary=salary+1 where employee_id=197; 1 row updated. SQL> commit; Commit complete.
STANDBY 上檢查結果(可能有延遲)：
SQL> conn scott/tiger
Connected.
SQL> select employee_id,salary from employees where employee_id=197; EMPLOYEE_ID SALARY
----------------- ----------
197 3001 這個時間間隔跟性能及其它情況有關，包括
capture mining logfile 的速度
propagation 的間隔(預設3秒)及傳輸時間
apply log 的速度 問題診斷
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錯誤資訊。
![實驗樓sql進階之成績管理系統的資料操作(window)[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)