oracle10gr2-rac+dg基礎上配置downstream(表級別單向捕獲)

skuary發表於2011-11-17

1 引言
Oracle Stream功能是為提高資料庫的高可用性而設計的,在Oracle 9i及之前的版本這個功能被稱為Advance Replication.....閒話太多,直入主題:

2 概述

目前的生產庫架構為:線上主庫是oracle10g的rac,資料庫版本:10.2.0.5.0,有一個物理data guard,資料庫版本:10.2.0.5.0,現在由於業務需求,需單獨的
建立一個stream資料庫,用來作為酒窖獨立資料庫,目的實時同步線上資料庫的某些表,最終的解決方案:
downstream real-time
單向異地實時捕獲的stream

主資料庫(RAC):

作業系統:linux 2.6.18-194.el5

IP地址:10.1.x.x,10.1.x.x,10.1.x.x

資料庫:Oracle 10.2.0.5.0

ORACLE_SID:axx

Global_name:axx

從資料庫:

作業系統:linux 2.6.18-194.el5

IP地址:10.1.x.x

資料庫:Oracle 10.2.0.5.0

ORACLE_SID:cxx

Global_name:cxx

3 環境準備

3.1 設定初始化引數    

使用pfile的修改init.ora檔案,使用spfile的通過alter system命令修改spile檔案。主、從資料庫分別執行如下的語句:
                                                                                                                                                                                                      
  Sqlplus  '/ as sysdba'                                                                                
    alter system set aq_tm_processes=2 scope=both sid='*';                                                         
    alter system set global_names=true scope=both sid='*';                                                         
    alter system set job_queue_processes=10 scope=both sid='*';                                                    
    alter system set parallel_max_servers=20 scope=both sid='*';                                                   
    alter system set undo_retention=3600 scope=both sid='*';                                                       
    alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile sid='*';                                 
    alter system set streams_pool_size=200M scope=spfile sid='*';                                                   
    alter system set utl_file_dir='*' scope=spfile sid='*';                                                        
    alter system set open_links=4 scope=spfile sid='*';                                                                                                                                                                   
                                                                                                           
  執行完畢後重啟資料庫。                                                                                 
 
 3.2 將資料庫置為歸檔模式                                                                                                                                                                                                                       
   設定log_archive_dest_1到相應的位置;(設定log_archive_start為TRUE,即啟用自動歸檔功能--oracle10g來說,該引數是一個過期引數,可以忽略);
設定log_archive_format指定歸檔日誌的命令格式。
 --   alter system reset  log_archive_start scope=spfile sid='*'; 過期引數
      sqlplus  '/ as sysdba' 
      alter system set LOG_ARCHIVE_CONFIG ='dg_config=(axx,bxx,cxx)';   ---主從+data guard
      LOG_ARCHIVE_DEST_2引數對應原來的data guard,不需要修改!
      alter system set LOG_ARCHIVE_DEST_3 = 'service=cxx lgwr sync noregister VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=cxx';  --主                                                   
      alter system set log_archive_dest_1='location=/../..' scope=spfile;    ---主從(本地歸檔日誌存放位置)                   
      alter system set log_archive_format=' arch%t_%s_%r.arc' scope=spfile;   ---主從
      alter system set standby_archive_dest='/../..' scope=spfile;   ---從(rac資料庫的歸檔日誌存放位置)
      ---以下步驟可以省略,如果已經是歸檔模式了(線上生產庫應該很少有不是歸檔模式的吧)
      shutdown immediate;                                                      
      startup mount;                                                           
      alter database archivelog;                                               
      alter database open; 
     
 3.3 建立stream 管理使用者                                                                                                            
                                                                               
   3.3.1 建立主環境stream管理使用者                                             
                                                                                                                                               
   #以sysdba身份登入                                                          
   connect / as sysdba                                                        
   #建立主環境的Stream專用表空間                                              
create tablespace tbs_stream datafile '/oracle/oradata/UAT/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 strmadminpassword  default tablespace tbs_stream temporary tablespace temp;                   
   #授權Stream管理使用者                                                        
   grant connect,resource,dba,aq_administrator_role to strmadmin;             
   begin                                                                      
dbms_streams_auth.grant_admin_privilege(                                   
grantee => 'strmadmin',                                                   
grant_privileges => true);                                                 
end;                                                                       
/                                                                          
 
 3.3.2 建立從環境stream管理使用者                                                                                                                                     
                                                                     
   #以sysdba身份登入                                                                
   connect / as sysdba                                                              
   #建立Stream專用表空間,我的從庫用了ASM,這一步也可以參見3.3.1                    
   create tablespace tbs_stream datafile '/oracle/oradata/UAT/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 strmadminpassword                                    
   default tablespace tbs_stream temporary tablespace temp;                         
   #授權Stream管理使用者                                                              
   grant connect,resource,dba,aq_administrator_role to strmadmin;                   
   begin                                                                      
dbms_streams_auth.grant_admin_privilege(                                   
grantee => 'strmadmin',                                                   
grant_privileges => true);                                                 
end;                                                                       
/                                                                            
   
3.4 配置網路連線                                                                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                                           
  3.4.1配置主環境tnsnames.ora                                                                                                                                                                      
                                                                                                                                                                                                     
  主資料庫(tnsnames.ora)中新增從資料庫的配置(3個節點都加上)。                                                                                                                                                     
                                                                                                                                                                                                     
                                                                                                                                                                                    
  cxx =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.x.x)(PORT = 1515))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cxx)
    )
  )
                                                                                                                                                                                                    
  3.4.2配置從環境tnsnames.ora                                                                                                                                                                      
                                                                                                                                                                                                     
                                                                                                                                                                                     
  從資料庫(tnsnames.ora)中新增主資料庫的配置。                                                                                                                                                     

#如下配置的前提是host檔案已經新增過域名解析

axx =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = axx1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = axx2-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = axx3-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = axx)
    )
  )

                                                                                                                                                                                                
                                                                                                                                                                                                  
 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”。

  select * from global_name;                                                                                                                      
                                                                                                                                                                                                     
  3.6.1建立主資料庫資料庫鏈                                                                                                                                                                        
                                                                                                                                                                                                     
  #以strmadmin身份,登入主資料庫。                                                                                                                                                                 
                                                                                                                                                                                                     
  connect strmadmin/strmadminpassword
                                                                                                                                                                                                     
  #建立資料庫鏈 
   
    create database link cxx connect to strmadmin identified by strmadminpassword using 'cxx';                                                                                                                                                                                    
                                                                                                                                                                                                     
   --測試
          
     select * from dual@cxx;                                                                                                
                                                                                                                                                                                                     
  3.6.2建立從資料庫資料庫鏈                                                                                                                                                                        
                                                                                                                                                                                                     
  #以strmadmin身份,登入從資料庫。                                                                                                                                                                 
                                                                                                                                                                                                     
  connect strmadmin/strmadminpassword
                                                                                                                                                                                                     
  #建立資料庫鏈                                                                                                                                                                                    
                                                                                                                                                                                                     
  create database link axx connect to strmadmin identified by strmadminpassword using 'axx';  
                                                                                                                                                                                          
   --測試
          
     select * from dual@axx;

 3.7 由於是下游實時捕獲,需要在從庫建立standby redo logfile,對應主庫-rac的每一個例項都要建立相應的standby redo log日誌組,
且要比主庫的日誌組多一組

alter database add standby logfile thread1 group 14 ('/../../../stdbyredo141.log','/../../../stdbyredo142.log') size xxm;
alter database add standby logfile thread1 group 15 ('/../../../stdbyredo151.log','/../../../stdbyredo152.log') size xxm;
.....
alter database add standby logfile thread3 group 49 ('/../../../stdbyredo491.log','/../../../stdbyredo492.log') size xxm;
 
----------到此為止,所有的準備工作基本做完了,然後rac和從庫都重新啟動一下吧

重啟之後,從庫可能會報錯:
ORA-16009: remote archive log destination must be a STANDBY database
直接在rac庫執行:
alter system set LOG_ARCHIVE_DEST_STATE_3 = 'ENABLE' scope=both sid='*';
這個位置啟用後,主庫切下日誌試試,我的做法一般都是主從都開著視窗實時動態檢視告警日誌,然後分別執行相關操作!
 
 
  3.9 在從庫執行maintain_tables包(strmadmin使用者)

DECLARE
tbls DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tbls(1) :='need_stream_user.table1';
tbls(2) :='need_stream_user.table2';
...
tbls(n) :='need_stream_user.tablen';
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
    table_names                  => tbls,
    source_directory_object      => 'source_directory',
    destination_directory_object => 'destination_directory',
    source_database              => 'axx',
    destination_database         => 'cxx',
    perform_actions              => true, --實際執行帶有network的匯入操作
    dump_file_name               => 'export_tables_kasaur.dmp',
    log_file                     => 'export_tables_expdp_kasaur.log',
    script_name                  => 'configure_rep_kasaur.sql',
    script_directory_object      => 'destination_directory',
    bi_directional               => false,
    include_ddl                  => true,   --包含源庫的ddl操作
    instantiation                => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
END;
/

---由於執行這個包的時候,會同時把相關表的資料同時匯入到從庫,應該會需要一定時間,

待執行完之後,從庫會產生propagation、capture和apply三個程式,正常情況下,這三個程式狀態應該都是enable的,可用如下命令檢視:
select propagation_name as name,status,ERROR_MESSAGE from dba_propagation
union all
select CAPTURE_NAME,status,ERROR_MESSAGE from dba_capture
union all
SELECT apply_name,status,error_message from dba_apply;
---------------------------------------------------------------
PROPAGATION$_461 ENABLED 
axx$CAP ENABLED 
APPLY$_axx_576 ENABLED 


 之後在從庫修改capture程式屬性,啟用實時捕獲
 
 BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'axx$CAP',
parameter => 'downstream_real_time_mine',
value => 'y');
END;
/
---如果執行上述操作報錯,可先停止capture程式,同時停止實時捕獲(雖然沒有啟動起來,但是做下停止操作,再進行後續操作會很好):
--停捕獲程式
begin
dbms_capture_adm.stop_capture(
capture_name => 'axx$CAP');
end;
/
--停實時捕獲
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'axx$CAP',
parameter => 'downstream_real_time_mine',
value => 'n');
END;
/
----之後再開啟應該就沒什麼問題了
begin
dbms_capture_adm.start_capture(
capture_name => 'axx$CAP');
end;
/

BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'axx$CAP',
parameter => 'downstream_real_time_mine',
value => 'y');
END;
/

------------
我這裡實際情況是隻實時同步了幾十張表,當資料同步完了之後,主站資料變更導致從庫的apply程式abort掉了,查詢錯誤資訊,發現是從庫的某個
trigger導致的,之後把trigger刪了,重新啟動apply程式就ok了。

由於是表級別的單項複製,不可避免的會產生update衝突,正式上線之前最好先解決掉,具體操作腳步如下:
select 'DECLARE
   cols DBMS_UTILITY.NAME_ARRAY;
 BEGIN
   cols(1) := ''' || column_name || ''';
 DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(object_name => ''need_stream_user.' ||
        table_name ||
        ''', method_name => ''OVERWRITE'',resolution_column => ''' ||
        column_name || ''',column_list => cols);
 END;
/'
  from all_tab_columns
 where table_name in ('table1_name','table2_name'..'tablen_name') and wner='need_stream_user';

--該操作是產生需要解決衝突的相關表的執行指令碼的語句,生成之後,在從庫執行指令碼就ok了!


   4.0 在源庫歸檔當前日誌,使得capture程式切換到standby redo logfile中捕獲資料
 
    alter system archive log current;

此後在目標庫的alert中可以發現如下記錄,表明已經開始實時捕獲了:


LOGMINER: Begin mining logfile for session 41 thread 3 sequence 5847, /../../../stdredo331.log


   4.1 相關檢查處理
 
select propagation_name,destination_dblink,status,ERROR_MESSAGE from dba_propagation;
select error_message from dba_recoverable_script_errors;
select queue,msg_id,MSG_STATE from capture_queue_table;
select OBJECT_OWNER,OBJECT_NAME,METHOD_NAME,RESOLUTION_COLUMN,COLUMN_NAME from dba_apply_conflict_columns;
select * from v$streams_capture;
select schema,qname,destination,schedule_disabled,failures,last_error_msg
from dba_queue_schedules;
SELECT
queue_schema, queue_name, unbrowsed_msgs, overspilled_msgs,
memory_usage, publisher_state
FROM V$BUFFERED_PUBLISHERS;
exec DBMS_CAPTURE_ADM.ABORT_TABLE_INSTANTIATION(table_name  => 'need_stream_user.table_name');  --刪掉不需要同步的表
...
...多的是,網上找吧

   4.2 清除stream配置

在從庫執行(只是我的操作):
begin
dbms_propagation_adm.stop_propagation(
propagation_name => 'PROPAGATION$_461');
end;
/
begin
dbms_capture_adm.stop_capture(
capture_name => 'axx$CAP');
end;
/  
 
begin
dbms_apply_adm.stop_apply(
apply_name => 'APPLY$_axx_576');
end;
/

exec DBMS_STREAMS_ADM.remove_streams_configuration();

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

後記:線上庫stream執行到現在有2天了,今天早上過來發現capture程式莫名abort掉了,查詢日誌,報錯資訊如下:
ORA-01341: LogMiner out-of-memory
ORA-01280: Fatal LogMiner Error.

記憶體不夠,直接增加記憶體:
exec dbms_capture_adm.set_parameter('axx$CAP','_SGA_SIZE','100');
然後重新起起來,執行沒5分鐘,propagation程式丟擲警告:
ORA-25307: Enqueue rate too high, flow control enabled
但是狀態依然是enable的,大概意思是入隊速度太快了,導致應用跟不上,不過一段時間後又會自動應用起來,之後就沒過多的過問,不知道
是不是oracle的bug,有待查證!

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25618347/viewspace-711122/,如需轉載,請註明出處,否則將追究法律責任。

相關文章