oracle 10g user stream

==準備==
1.歸檔日誌配置：
sql> archive log list;
database log mode archive mode
automatic archival enabled
archive destination /u01/oradata/stream1_arch --i don't modify it

database log mode archive mode
automatic archival enabled
archive destination /u01/oradata/stream2_arch

---以下沒有專門說明的都是sys使用者---

2.在源stream1上配置supplemental loging：

在源上：
sql> alter database add supplemental log data;

database altered.

3.兩臺機器上修改global_name:

db1:
sql> alter system set global_names=true scope=both;
sql> alter database rename global_name to db1.com; --i use the defualt

db2:
sql> alter system set global_names=true scope=both;
sql> alter database rename global_name to db2.com; --

4.修改初始化引數：
參考官方文件確定需要來檢查和設定引數

5.建立stream管理使用者並表空間，配置許可權，source和dest：
5.1 為strmadmin使用者建立獨立表空間

db1:
create tablespace streams_tbs datafile 'streams_tbs.dbf' size 25m reuse autoextend on maxsize unlimited;

db2:
create tablespace streams_tbs datafile 'streams_tbs.dbf' size 25m reuse autoextend on maxsize unlimited;

5.2 source和dest建立相同使用者和許可權：
create user strmadmin identified by strmadminpw default tablespace streams_tbs quota unlimited on streams_tbs;

grant dba to strmadmin;

begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
/

6.配置tnsnames.ora,

db1上：sqlplus
db2上：sqlplus
都沒有問題就ok

---如果沒有說明以下都是strmadmin/strmadminpw 使用者---

7.建立db link:
單向複製之需要一個database link即source 到dest:db1--db2

db1上：
sqlplus strmadmin/strmadminpw
create database link db2 connect to strmadmin identified by strmadminpw using 'db2';

==開始stream的配置===
8. source和dest 建立佇列：

db1,db2都要建立
exec dbms_streams_adm.set_up_queue();

上面命令會建立一個佇列預設名：streams_queue，佇列表預設是：streams_queue_table 佇列儲存的object型別是anaydata

可以用查詢dba_queues,dba_queue_tables來檢查：
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

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: db1上建立stream propagation：

begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'hr',
streams_name => 'stream1_to_stream2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => ,
include_dml => true,
include_ddl => true,
source_database => 'db1',
inclusion_rule => true,
queue_to_queue => true);
end;
/

可以透過dba_propagations檢視結果：
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 stream2.com enabled

9.在source: db1上建立capture程式：

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'hr',
streams_type => 'capture',
streams_name => 'capture_stream1',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
/

可以透過dba_capture檢視：
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

select * from all_capture_prepared_schemas;

schema_name timestamp suppleme suppleme suppleme suppleme
-------------------- --------------- -------------- -------------- ------------- --------
hr 12-jun-08 implicit implicit implicit no

10.將stream1上的hr schema資料導到stream2上：
db2上：
sqlplus system/system
create public database link stream1.com connect to system identified by system using 'stream1';
select * from ;
導資料：
impdp system/sys network_link=stream1.com schemas=hr

11. 在db1上設定db2上hr schema的instantiation scn

sqlplus strmadmin/strmadminpw
declare
iscn number;
begin
iscn := dbms_flashback.get_system_change_number();
(
source_schema_name => 'hr',
source_database_name => 'db1',
instantiation_scn => iscn,
recursive => true);
end;
/

12.在db2上建立apply程式apply_stream2：

begin
dbms_streams_adm.add_schema_rules(
schema_name => 'hr',
streams_type => 'apply',
streams_name => 'apply_stream2',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'db1',
inclusion_rule => true);
end;
/

pl/sql procedure successfully completed.

可以透過：
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server
檢視狀態

select apply_name,queue_name,status from dba_apply;

apply_name queue_name status
--------------------- ------------------------ --------
apply_stream2 streams_queue disabled

==啟動==

13.啟動capture和apply:

13.1 db2上啟動 apply process

connect strmadmin/strmadminpw
begin
dbms_apply_adm.set_parameter(
apply_name => 'apply_stream2',
parameter => 'disable_on_error',
value => 'n');
end;
/

pl/sql procedure successfully completed.

begin
dbms_apply_adm.start_apply(
apply_name => 'apply_stream2');
end;
/

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 stream1上啟動capture process:

sqlplus strmadmin/strmadminpw
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_stream1');
end;
/

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

===測試===
http://blog.chinaunix.net/uid-20478213-id-1942074.html (參考之)

[@more@]