天天看點

Oracle Stream 配置

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錯誤資訊。