基于用戶的流復制配置 案例

主數據庫：

　　操作系統：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_schema_rules(
SCHEMA_NAME=> 'hr',
STREAMS_TYPE=>'capture',
STREAMS_NAME=>'capture_db1',
QUEUE_NAME=>'strmadmin.db1_queue',
INCLUDE_DML=>true,
INCLUDE_DDL=>true,
SOURCE_DATABASE=> 'db1',
INCLUDE_TAGGED_LCR=>false,
INCLUSION_RULE=> true);
end;
/? ?

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

?? ??? ?SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;

?? ??? ?CAPTURE_NAMEQ??? UEUE_NAME?????????? START_SCN STATUS?? ??? ???? CAPTURE_TYPE
?? ??? ?---------- ------------------------ ---------------------------------------------
?? ??? ?CAPTURE_DB1?????? DB1_QUEUE?????????? 4010410 ENABLED?? ??? ???? LOCAL




?? ?1.10.創建傳播進程(對用戶進行同步)
?? ??? ?connect strmadmin/oracle
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'hr',
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,
include_tagged_lcr => false,
source_database => 'db1',
inclusion_rule => true,
queue_to_queue=>true);
end;
/

?? ??? ?#修改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_schema_rules(
schema_name => 'hr',
streams_type => 'apply',
streams_name => 'apply_db2',
queue_name => 'db2_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'db1',
inclusion_rule => true);
end;
/





?? ?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

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

?? ?---獲取源庫互置用戶的SCN
?? ?connect strmadmin/oracle@DB1??? ?
?? ?SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
?? ?DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
?? ?-----------------------------------------
?? ??? ??? ??? ??? ?? 4428237

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

?? ??? ?查詢狀態：
?? ??? ??? ?可以通過：
?? ??? ??? ?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_DB2
?? ??? ?DB2_QUEUE
?? ??? ?ENABLED



?? ?1.13? 啟動STREAM
?? ?　　#以strmadmin身份，登錄從數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　#啟動Apply進程
?? ?execute dbms_apply_adm.start_apply(apply_name => 'apply_db2');

?? ?　　#以strmadmin身份，登錄主數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　#啟動Capture進程
?? ?execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_DB1');


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

?? ?　　#以strmadmin身份，登錄從數據庫。
?? ?　　connect strmadmin/oracle
?? ?　　#停止Apply進程

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




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

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

?? ?　　exec DBMS_STREAMS_ADM.remove_streams_configuration();

?? ?2#方法
?? ?exec dbms_streams_adm.remove_queue(queue_name=>'DB1_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=>'DB2_QUEUE',cascade=>true,drop_unused_queue_table=>true);
?????????? ?
?? ??? ?

二、測試數據同步

?? ?查詢狀態：

?? ?1.主庫

?? ?SQL> select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;

?? ?CAPTURE_NAME? QUEUE_NAME? STATUS?? ??? ??????? CAPTURED_SCN APPLIED_SCN
?? ?-------------- ------------------------- ------------------------ ------------ -----------
?? ?CAPTURE_DB1?? DB1_QUEUE?? ?? ENABLED?? ??? ??? ???? 4423352?? ?4423352

?? ?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_DB2?? ????????????? YES?? ?? ENABLED



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




?? ?查詢狀態SQL
?? ?select owner,name from dba_queues where owner='STRMADMIN';
?? ?select capture_name,queue_name,rule_set_name,negative_rule_set_name,status from dba_capture;
?? ?select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status from dba_propagation;
?? ?select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;


?? ?select apply_name,error_number,apply_captured,status from dba_apply;
?? ?select queue_name,error_number,error_message,error_creation_time from dba_apply_error;

總結

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

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