ORACLE STREAM 演示基于單表的流復制案例

??????? 主數據庫：

　　操作系統：REDHAT 5 　　IP地址：192.168.10.87
　　數據庫：Oracle 11.2.0.3
　　ORACLE_SID：DB1 　Global_name：DB1

　　從數據庫：
　　操作系統：REDHAT 5 　　IP地址：192.168.10.88
　　數據庫：Oracle 11.2.0.3
　　ORACLE_SID：DB2 　Global_name：DB2

一、配置
?1.1設置初始化參數：
?? ?SQL> 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=200M scope=spfile;
?? ?　　alter system set utl_file_dir='*' scope=spfile;
?? ?　　alter system set open_links=4 scope=spfile;
?? ?System altered.




?1.2 檢查數據庫是否為歸檔模式：
?? ?SQL> archive log list;
?? ?Database log mode?? ??????? Archive Mode
?? ?Automatic archival?? ??????? Enabled
?? ?Archive destination?? ??????? /opt/app/oracle/oradata/DB1
?? ?Oldest online log sequence???? 313
?? ?Next log sequence to archive?? 315
?? ?Current log sequence?? ??????? 315
?? ?SQL>
?? ?如果不是歸檔模式，先配置：
?? ???? startup mount;
?? ?　　alter database archivelog;
?? ?　　alter database open;

1.3 啟用追加日志
　　可以基于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;




1.4 建立stream 管理用戶

?? ?1.4.1創建主環境的Stream專用表空間
?? ?　　create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB2/stream_tbs01.dbf'
?? ???? size 100M autoextend on maxsize unlimited segment space management auto;
?? ?1.4.2將logminer的數據字典從system表空間轉移到新建的表空間，防止撐滿system表空間
?? ?　　execute dbms_logmnr_d.set_tablespace('stream_tbs');
?? ?1.4.3創建Stream管理用戶
?? ?　　create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
?? ?1.4.4授權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? /

?? ??? ?PL/SQL procedure successfully completed.

?? ??? ?SQL>

?1.5 在從環境中建立stream 管理用戶

?? ?SQL> archive log file;
?? ?SP2-0718: illegal ARCHIVE LOG option
?? ?SQL> archive log list;
?? ?Database log mode?? ??????? Archive Mode
?? ?Automatic archival?? ??????? Enabled
?? ?Archive destination?? ??????? /opt/app/oracle/oradata/DB1
?? ?Oldest online log sequence???? 39
?? ?Next log sequence to archive?? 41
?? ?Current log sequence?? ??????? 41
?? ?SQL> create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB1/stream_tbs01.dbf'
?? ?? 2? size 100M autoextend on maxsize unlimited segment space management auto;
?? ?Tablespace created.
?? ?SQL> execute dbms_logmnr_d.set_tablespace('stream_tbs');
?? ?PL/SQL procedure successfully completed.
?? ?SQL> create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
?? ?User created.
?? ?SQL> grant connect,resource,dba,aq_administrator_role 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.
?? ?SQL>

?? ?1.6 配置網絡連接
?? ??? ?主從數據庫(tnsnames.ora)中添加從數據庫的配置
?? ??? ?DB1 =
?? ??? ?? (DESCRIPTION =
?? ??? ??? ?(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.87)(PORT = 1521))
?? ??? ??? ?(CONNECT_DATA =
?? ??? ??? ?? (SERVER = DEDICATED)
?? ??? ??? ?? (SERVICE_NAME = DB1)
?? ??? ??? ?)
?? ??? ?? )

?? ??? ?DB2 =
?? ??? ?? (DESCRIPTION =
?? ??? ??? ?(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.10.88)(PORT = 1521))
?? ??? ??? ?(CONNECT_DATA =
?? ??? ??? ?? (SERVER = DEDICATED)
?? ??? ??? ?? (SERVICE_NAME = DB2)
?? ??? ??? ?)
?? ??? ?? )


?? ?1.7 創建DBlink

　　根據Oracle 10gR2 Stream官方文檔，針對主數據庫建立的數據庫鏈的名字必須和從數據庫的global_name相同。
　　如果需要修改global_name，執行“alter database rename global_name to xxx”。

　　1.7.1創建主數據庫數據庫鏈
　　#以strmadmin身份，登錄主數據庫。
　　connect strmadmin/oracle

　　#建立數據庫鏈

　　create database link db2_link connect to strmadmin identified by oracle using 'DB2';

　 　1.7.1創建從數據庫數據庫鏈

　　#以strmadmin身份，登錄從數據庫。

　　connect strmadmin/oracle

　　#建立數據庫鏈

?? ?SQL> create database link db2_link connect to strmadmin identified by oracle using 'DB2';

?? ?Database link created.

?? ?SQL> select name from v$database@db2_link;
?? ?select name from v$database@db2_link
?? ??? ??? ??? ??? ??? ??? ??? ?*
?? ?ERROR at line 1:
?? ?ORA-02085: database link DB2_LINK connects to DB1


?? ?SQL> alter system set global_names=false;

?? ?System altered.

?? ?#測試連接是否成功
?? ?SQL> select name from v$database@db2_link;

?? ?NAME
?? ?---------------------------
?? ?DB2
?? ?

1.8 創建流隊列
　　1.8.1創建Master流隊列


?? ?　　#以strmadmin身份，登錄主數據庫。
?? ??? ?SQL> connect strmadmin/oracle
?? ??? ?Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=>'db1_queue_table',
queue_name=>'db1_queue',
queue_user=>'strmadmin');
end;
/

?? ??? ?PL/SQL procedure successfully completed.

?? ?查詢隊列：
?? ?SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';

?? ?OWNER?? ??? ?QUEUE_TABLE?? ??? ??? ?NAME
?? ?---------------------------------------------------------
?? ?STRMADMIN?? ?DB1_QUEUE_TABLE?? ??? ?DB1_QUEUE
?? ?STRMADMIN?? ?DB1_QUEUE_TABLE?? ??? ?AQ$_DB1_QUEUE_TABLE_E


?? ?1.8.2創建Backup流隊列

?? ?　　#以strmadmin身份，登錄從數據庫。
?? ??? ?SQL> connect strmadmin/oracle
?? ??? ?Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=> 'db2_queue_table',
queue_name=>'db2_queue',
queue_user=>'strmadmin');
end;
/

?? ??? ?PL/SQL procedure successfully completed.

?? ??? ?SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';

?? ??? ?OWNER?????? QUEUE_TABLE??? NAME
?? ??? ?-------------------------------------------------
?? ??? ?STRMADMIN? DB2_QUEUE_TABLE?? DB2_QUEUE
?? ??? ?STRMADMIN?? DB2_QUEUE_TABLE? AQ$_DB2_QUEUE_TABLE_E



?? ?1.9 創建捕獲進程 (對單表進行同步)
?? ?　　#以strmadmin身份，登錄主數據庫。(同樣以hr用戶做示例)。
?? ?　　connect strmadmin/oracle
begin
dbms_streams_adm.add_table_rules(
table_name => 'HR.TST',
streams_type => 'capture',
streams_name =>'capture_stream',
queue_name => 'STRMADMIN.DB1_QUEUE',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;

?? ??? ?PL/SQL procedure successfully completed.


SQL> select capture_name,status from dba_capture;

CAPTURE_NAME?? ??? ?? STATUS
-------------------- ------------------------
CAPTURE_STREAM?? ???????? DISABLED



?? ?1.10.創建傳播進程
?? ??? ?connect strmadmin/oracle

begin
?? dbms_streams_adm.add_table_propagation_rules(
?? table_name =>'hr.tst',
?? streams_name => 'db1_to_db2',
?? source_queue_name =>'strmadmin.db1_queue',
?? destination_queue_name => 'strmadmin.db2_queue@db2_link',
?? include_dml => true,
?? include_ddl => true,
?? source_database =>'db1',
?? inclusion_rule => true,
?? queue_to_queue => true);
?end;
/


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_DBLINK?? ??? ???????? STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
DB1_TO_DB2?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? DB1_QUEUE?? ??? ??? ??? ???????????? DB2_QUEUE ?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? DB2_LINK?? ??? ??? ??? ???????? ENABLED

SQL>


?? ??? ?#修改propagation休眠時間為0，表示實時傳播LCR。
begin
dbms_aqadm.alter_propagation_schedule(
queue_name =>'db1_queue',
destination=>'db2_link',
destination_queue=>'db2_queue',
latency => 0
);
end;
/

?? ??? ?PL/SQL procedure successfully completed.


?? ?1.11 創建應用進程
?? ??? ?　　#以strmadmin身份，登錄從數據庫。
?? ??? ?　　connect strmadmin/oracle

begin
dbms_streams_adm.add_table_rules(
table_name => 'hr.tst',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'strmadmin.db2_queue',
include_dml => true,
include_ddl => true,
source_database =>'db1',
inclusion_rule => true);
end;
/

SQL> select apply_name,queue_name,status from dba_apply;

APPLY_NAME
--------------------------------------------------------------------------------
QUEUE_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
DB2_QUEUE
DISABLED



?? ?1.12 實例復制
?? ??? ?可以使用EXP/IMP 把用戶HR 數據導入到從庫中。
?? ??? ?因我的機子前面做了GGS，這里不再進行實例復制。
?? ??? ?exp userid= hr/hr@db1 file='/tmp/hr.dmp'
?? ??? ?imp userid=system/sa file='/tmp/hr.dmp' ignore=y fromuser=hr touser=hr


?? ??? ?因為只同步一個表，我在DB2中也建立了一個連接。并使用以下SQL 建立一個表：
?? ??? ?SQL> create table hr.tst as select * from hr.tst@db1_link;
?? ??? ?Table created.
?? ??? ?SQL> select * from hr.tst;
?? ??? ?no rows selected
?? ??? ?SQL>

?? ?1.13 直接設置SCN的方式進行實例化：

?? ?---獲取源庫互置用戶的SCN
?? ?connect strmadmin/oracle@DB1??? ?
?? ?SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;

?? ?DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
?? ?-----------------------------------------
?? ??? ??? ??? ??? ?? 4486128

?? ?---設置為目標庫互置用戶的SCN
?? ?connect strmadmin/oracle@db2????? ?
?? ?
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name??? => 'hr.tst',
source_database_name? => 'db1',
instantiation_scn???? => 4605643);
END;
/

在目標端修改SCN前后查詢：


SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
?? ??? ??? ??? ?? 4597643

SQL> BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name??? => 'hr.tst',
source_database_name? => 'db1',
instantiation_scn???? => 4597643);
END;
/? 2??? 3??? 4??? 5??? 6??? 7 ?

PL/SQL procedure successfully completed.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
?? ??? ??? ??? ?? 4597431

SQL>


從庫啟動 APPLY:

SQL> exec dbms_apply_adm.start_apply('apply_stream');

PL/SQL procedure successfully completed.

SQL> select apply_name,status from dba_apply;

APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED


主庫啟動CAPTURE

SQL> select capture_name,status from dba_capture;

CAPTURE_NAME?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? DISABLED

SQL> execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');

PL/SQL procedure successfully completed.

SQL> select capture_name,status from dba_capture;

CAPTURE_NAME?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ENABLED

SQL>



?? ?1.13? 啟動STREAM
?? ?　　#以strmadmin身份，登錄從數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　#啟動Apply進程
begin
dbms_apply_adm.start_apply(
apply_name => 'APPLY_STREAM');
end;
/
?? ?　　#以strmadmin身份，登錄主數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　#啟動Capture進程
begin
execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');
end;
/

　?? ?1.14 停止STREAM
?? ?　　#以strmadmin身份，登錄主數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　#停止Capture進程
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_db1');
end;
/

?? ?　　#以strmadmin身份，登錄從數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　#停止Apply進程
begin
dbms_apply_adm.stop_apply(
apply_name => 'APPLY_STREAM');
end;
/



?? ?1.15 清除所有配置信息
?? ?　　要清除Stream配置信息，需要先執行1.14，先停止Stream進程。

?? ?　　#以strmadmin身份，登錄主數據庫。
?? ?　　connect strmadmin/oracle

?? ?　　exec DBMS_STREAMS_ADM.remove_streams_configuration();

?? ?2#方法
?? ?exeucte dbms_streams_adm.remove_streams_queue(queue_name=>'db2_queue',cascade=>true,drop_unused_queue_table=>true);

?? ?　　#以strmadmin身份，登錄從數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　exec DBMS_STREAMS_ADM.remove_streams_configuration();
?? ?2#方法
?? ?? exec dbms_apply_adm.stop_apply(apply_name=>'apply_db2');
?? ?? exec dbms_streams_adm.remove_queue(queue_name=>'db1_queue',cascade=>true,drop_unused_queue_table=>true);
?????????? ?
?? ??? ?

二、測試數據同步

?? ?查詢狀態：

?? ?1.主庫

?? ?SQL> conn strmadmin/oracle
?? ?Connected.
?? ?SQL> select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;

?? ?CAPTURE_NAME?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? QUEUE_NAME?? ??? ??? ??? ??? ??? ?????????? STATUS?? ??? ??????? CAPTURED_SCN APPLIED_SCN
?? ?------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------ ------------ -----------
?? ?CAPTURE_STREAM?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? DB1_QUEUE?? ??? ??? ??? ??? ??? ?????????? ENABLED?? ??? ??? ???? 4480864?? ?4480864

?? ?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_DBLINK?? ??? ??? ??? ?????? STATUS
?? ?------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
?? ?DB1_TO_DB2?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? DB1_QUEUE?? ??? ??? ??? ??? ??? ?????????? DB2_QUEUE ?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? DB2_LINK?? ??? ??? ??? ??? ??? ?????? ENABLED


?? ?SQL> select apply_name,error_number,apply_captured,status from dba_apply;

?? ?APPLY_NAME
?? ?--------------------------------------------------------------------------------
?? ?ERROR_NUMBER APPLY_CAP STATUS
?? ?------------ --------- ------------------------
?? ?APPLY_STREAM
?? ??????? 26714 YES?????? ABORTED


?? ?測試：插入，修改，刪除，查看目標服務器，數據同步完成。

在源庫中刪除表HR.TST，目標端出錯：alert_db2.log?? ?
后，再目標庫中重啟APPLY進程。看到數據已同步過來了。


SQL> execute dbms_apply_adm.stop_apply(apply_name => 'APPLY_STREAM');

PL/SQL procedure successfully completed.

SQL> exec dbms_apply_adm.start_apply('apply_stream');

PL/SQL procedure successfully completed.

SQL> select apply_name,status from dba_apply;

APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED

DB2_QUEUE
?????? 26687
ORA-26687: no instantiation SCN provided for "HR"."" in source database "DB1"
2013-09-08 17:34:31


QUEUE_NAME
--------------------------------------------------------------------------------
ERROR_NUMBER
------------
ERROR_MESSAGE
--------------------------------------------------------------------------------
ERROR_CREATION_TIME
-------------------
DB2_QUEUE
?? ?? 54
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2013-09-08 17:43:11


SQL> select * from hr.tst;

?? ?ID
----------
?? ? 1

總結

以上是生活随笔為你收集整理的【ORACLE 高可用】 ORACLE STREAM 基于单表的流复制 配置 案例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。