oracle 9i stream配置備記

--prepare(2 servers)
conn / as sysdba
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=25M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;
-----------------------------------------------------------
--restart db(2 servers)
shutdown immediate
startup
------------------------------------------------------------
--create user:strmadmin (2 servers)
create tablespace tbs_stream datafile '/ora_data/tbs_stream01.dbf' size 100m autoextend on maxsize unlimited segment space management auto;
? 將logminer的資料字典從system表空間轉移到新建的表空間，防止撐滿system表空間
SQL> execute dbms_logmnr_d.set_tablespace('tbs_stream');
? 建立Stream管理使用者
SQL> create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp;
? 授權Stream管理使用者
SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;
SQL> execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('strmadmin');
-----------------------------------------------------------------
-- use supplemental log
? 啟用Database 追加日誌 （主、從資料庫上操作）
SQL> alter database add supplemental log data;
? 檢查是否被啟用
SQL> Select supplemental_log_data_min from v$database;
-----------------------------------------------------------------
--create db tns
BJDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zuoh)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = bjdb)
    )
  )

hbDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.31.27)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hbdb)
    )
  )
 
------------------------------------------------------------------
--create dblink
 以strmadmin身份，登入主資料庫。
　　 SQL> connect strmadmin/strmadmin
 建立資料庫鏈
　  SQL>　create database link hbdb connect to strmadmin identified by strmadmin using 'hbdb';
3.6.2建立從資料庫資料庫鏈
 以strmadmin身份，登入從資料庫。
　　SQL> connect strmadmin/strmadmin
 建立資料庫鏈
SQL>　create database link bjdb connect to strmadmin identified by strmadmin using 'bjdb';

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

--create streams
conn
begin
   dbms_streams_adm.set_up_queue(queue_table =>'bjdbc_queue_table',queue_name =>'bjdbc_queue');
   dbms_streams_adm.set_up_queue(queue_table =>'bjdbr_queue_table',queue_name =>'bjdbr_queue');
end;
   /
conn
begin
   dbms_streams_adm.set_up_queue(queue_table =>'hbdbc_queue_table',queue_name =>'hbdbc_queue');
   dbms_streams_adm.set_up_queue(queue_table =>'hbdbr_queue_table',queue_name =>'hbdbr_queue');
end;
   /
---------------------------
conn
begin
  dbms_streams_adm.add_schema_rules(
  schema_name => 'aaa',
  streams_type => 'capture',
 streams_name => 'capture_bjdb',
  queue_name => 'strmadmin.bjdbc_queue',
  include_dml => true,
  include_ddl => true,
  include_tagged_lcr => false,
 source_database => null,
 inclusion_rule => true);
 end;
 /

begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'aaa',
streams_name => 'bjdb_to_hbdb',
source_queue_name => 'strmadmin.bjdbc_queue',
destination_queue_name => ,
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'bjdb',
inclusion_rule => true);
end;
/
begin
  dbms_aqadm.alter_propagation_schedule(
  queue_name => 'bjdbc_queue',
  destination => 'hbdb',
  latency => 0);
end;
/
 begin
  dbms_streams_adm.add_schema_rules(
  schema_name =>'aaa',
  streams_type =>'apply',
  streams_name =>'apply_bjdb',
  queue_name =>'strmadmin.bjdbr_queue',
  include_dml => true,
  include_ddl => true,
  include_tagged_lcr => false,
 source_database => 'hbdb',
 inclusion_rule => true);
end;
  /

---------------------------------------------------------------------
conn
begin
  dbms_streams_adm.add_schema_rules(
  schema_name => 'aaa',
  streams_type => 'capture',
 streams_name => 'capture_hbdb',
  queue_name => 'strmadmin.hbdbc_queue',
  include_dml => true,
  include_ddl => true,
  include_tagged_lcr => false,
 source_database => null,
 inclusion_rule => true);
 end;
 /
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'aaa',
streams_name => 'hbdb_to_bjdb',
source_queue_name => 'strmadmin.hbdbc_queue',
destination_queue_name => ,
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'hbdb',
inclusion_rule => true);
end;
/

declare
iscn number;
begin
iscn:=dbms_flashback.get_system_change_number();
(
  source_schema_name => 'aaa',
  source_database_name => 'hbdb',
  instantiation_scn => iscn);
end;
/
 begin
  dbms_streams_adm.add_schema_rules(
  schema_name =>'aaa',
  streams_type =>'apply',
  streams_name =>'apply_hbdb',
  queue_name =>'strmadmin.hbdbr_queue',
  include_dml => true,
  include_ddl => true,
  include_tagged_lcr => false,
 source_database => 'bjdb',
 inclusion_rule => true);
end;
  /
---------------------------------------------------------
--sync  on bjdb server
exp file='/ora_data/aaa.dmp' object_consistent=y rows=y
imp file='/ora_data/aaa.dmp' ignore=y commit=y log='/ora_data/aaa.log' streams_instantiation=y fromuser=aaa touser=aaa

--sync on hbdb server
exp file='/ora_data/aaa.dmp' object_consistent=y rows=y
imp file='/ora_data/aaa.dmp' ignore=y commit=y log='/ora_data/aaa.log' streams_instantiation=y fromuser=aaa touser=aaa

--or
declare
iscn number;
begin
iscn:=dbms_flashback.get_system_change_number();
=> 'aaa',source_database_name => 'bjdb',instantiation_scn => iscn);
end;

----------------------------------
--start 2 dbs  sync
conn
begin
dbms_apply_adm.start_apply('apply_hbdb');
end;
/
-------------------------------------------------
conn
begin
dbms_capture_adm.start_capture('capture_bjdb');
dbms_propagation_adm.start_propagation('bjdb_to_hbdb');
dbms_apply_adm.start_apply('apply_bjdb');
end;
/
----------------------------------------------------------
conn
begin
dbms_capture_adm.start_capture('capture_hbdb');
dbms_propagation_adm.start_propagation('hbdb_to_bjdb');
end;
/

-----------------------------------------------------
--test sql
select mdn, imsi from mdn_imsi;
insert into mdn_imsi (mdn,imsi) values ('bbbbb','bbbbb');