ORACLE10g Stream使用者級複製配置
使用stream管理員使用者，源庫操作如下：
SQL> conn
Connected.
SQL> begin
2 for cur_pro in (select propagation_name from dba_propagation) loop
3 dbms_propagation_adm.drop_propagation(cur_pro.propagation_name);
4 end loop;
5 dbms_streams_adm.REMOVE_STREAMS_CONFIGURATION;
6 end;
7 /
PL/SQL procedure successfully completed.
檢視日誌檔案資訊，部分如下：
Thu Apr 3 16:52:25 2014
Streams CAPTURE C001 with pid=24, OS id=2291 stopped
Thu Apr 3 16:52:33 2014
ARC1: STARTING ARCH PROCESSES
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC2 started with pid=24, OS id=3305
Streams Apply Server P001 pid=27 OS id=2297 stopped
Streams Apply Reader P000 pid=26 OS id=2295 stopped
Streams Apply Server P000 pid=26 OS id=2295 stopped
Streams Apply Server P001 pid=27 OS id=2297 stopped
Thu Apr 3 16:52:37 2014
Streams APPLY A001 with pid=25, OS id=2293 stopped
Thu Apr 3 16:53:31 2014
Shutting down archive processes
Thu Apr 3 16:53:36 2014
ARCH shutting down
ARC2: Archival stopped
檢視stream相關的表的佇列資訊，如下：
SQL> select apply_name,queue_name,queue_owner,status from dba_apply;
no rows selected
SQL> select CAPTURE_NAME,QUEUE_OWNER,STATUS,CAPTURE_USER from dba_capture;
no rows selected
使用stream管理員，目標庫操作如下：
SQL> conn ;
Connected.
SQL> begin
2 for cur_pro in (select propagation_name from dba_propagation) loop
3 dbms_propagation_adm.drop_propagation(cur_pro.propagation_name);
4 end loop;
5 dbms_streams_adm.REMOVE_STREAMS_CONFIGURATION;
6 end;
7 /
PL/SQL procedure successfully completed.
檢視日誌檔案資訊，部分如下：
Thu Apr 3 17:08:46 2014
Streams CAPTURE C001 with pid=25, OS id=2454 stopped
Thu Apr 3 17:08:53 2014
ARC1: STARTING ARCH PROCESSES
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC2 started with pid=25, OS id=3342
Streams Apply Server P001 pid=28 OS id=2460 stopped
Streams Apply Reader P000 pid=27 OS id=2458 stopped
Streams Apply Server P001 pid=28 OS id=2460 stopped
Streams Apply Server P000 pid=27 OS id=2458 stopped
Thu Apr 3 17:08:57 2014
Streams APPLY A001 with pid=26, OS id=2456 stopped
Thu Apr 3 17:09:36 2014
Shutting down archive processes
Thu Apr 3 17:09:41 2014
ARCH shutting down
ARC2: Archival stopped
2.源庫和目標庫初始化引數設定
在源庫：
alter system set aq_tm_processes=1 scope=spfile;
alter system set job_queue_processes=2 scope=spfile;
alter system set global_names=true scope=spfile;
alter database rename global_name to myorcl.net;
alter system set streams_pool_size=52m scope=spfile;
在目標資料庫：
alter system set aq_tm_processes=1 scope=spfile;
alter system set job_queue_processes=2 scope=spfile;
alter system set global_names=true scope=spfile;
alter database rename global_name to orcl.net;
alter system set streams_pool_size=50m scope=spfile;
由於之前做了表級複製，現在只需驗證配置資訊是否正確。
3.在源庫和目標庫配置tnsnames.ora，如下：
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myorcl)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
4.源庫和目標庫複製管理員的建立
不能使用sys和system作為流管理員，流管理員不能使用system表空間作為預設表空間；
在源庫驗證操作如下：
SQL> select username from dba_users where username like '%STREAM%'; --之前做表級複製時建立的stream管理員
USERNAME
------------------------------
STREAMADMIN
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME like '%STREAM%';
TABLESPACE_NAME STATUS
------------------------------ ---------
STREAMTBS ONLINE
在目標庫驗證操作如下：
SQL> select username from dba_users where username like '%STREAM%';
USERNAME
------------------------------
STREAMADMIN
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME like '%STREAM%';
TABLESPACE_NAME STATUS
------------------------------ ---------
STREAMTBS ONLINE
5.源庫和目標庫建立互連的資料庫連線
在源庫驗證操作如下：
SQL> conn
Connected.
SQL> col owner for a15;
SQL> col db_link for a15;
SQL> col username for a15;
SQL> col host for a15;
SQL> select owner,db_link,username,host from dba_db_links;
OWNER DB_LINK USERNAME HOST
--------------- --------------- --------------- ---------------
STREAMADMIN ORCL.NET STREAMADMIN standby
SQL> select * from ;
D
-
X
在目標庫驗證操作如下：
SQL> conn ;
Connected.
SQL> col owner for a15;
SQL> col db_link for a15;
SQL> col username for a15;
SQL> col host for a15;
SQL> select owner,db_link,username,host from dba_db_links;
OWNER DB_LINK USERNAME HOST
--------------- --------------- --------------- ---------------
STREAMADMIN MYORCL.NET STREAMADMIN primary
SQL> select * from ;
D
-
X
6.檢視源庫和目標庫是否處於歸檔模式
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/my_arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
7.源庫和目標庫建立directory
在源庫操作如下：
SQL> conn ;
Connected.
SQL> create directory dir_myorcl as ' /u01/app/oradata/myorclstr_dir';
Directory created.
在目標庫操作如下：
SQL> conn
Connected.
SQL> create directory dir_orcl as ' /u01/app/oradata/orclstr_dir ';
Directory created.
檢視建立的directory的語句如下：
源庫：
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ --------------------------------------------------------------------------------
SYS DIR_MYORCL /u01/app/oradata/myorclstr_dir
SYS DATA_PUMP_DIR /u01/app/oracle/rdbms/log/
SYS XMLDIR /u01/app/oracle/demo/schema/order_entry/
SYS MEDIA_DIR /u01/app/oracle/demo/schema/product_media/
SYS LOG_FILE_DIR /u01/app/oracle/demo/schema/log/
SYS WORK_DIR /ade/aime_ship_10gR2_050630.0022/oracle/work
SYS DATA_FILE_DIR /u01/app/oracle/demo/schema/sales_history/
SYS SUBDIR /u01/app/oracle/demo/schema/order_entry//2002/Sep
SYS ADMIN_DIR /ade/aime_ship_10gR2_050630.0022/oracle/md/admin
9 rows selected.
目標庫：
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- --------------- --------------------------------------------------
SYS ADMIN_DIR /ade/aime_ship_10gR2_050630.0022/oracle/md/admin
SYS DATA_PUMP_DIR /u01/app/oracle/rdbms/log/
SYS WORK_DIR /ade/aime_ship_10gR2_050630.0022/oracle/work
SYS DIR_ORCL /u01/app/oradata/orclstr_dir
8.在源庫中建立測試用的表空間及使用者
SQL> create user huyong identified by huyong default tablespace streamtbs quota unlimited on streamtbs;
SQL> grant connect,resource to huyong;
SQL> conn huyong/huyong;
SQL> create table test01(id int,name varchar2(20));
9.在源庫執行MAINTAIN_schemas過程
SQL> connect ;
Connected.
SQL> begin
2 dbms_streams_adm.maintain_schemas(
3 schema_names => 'huyong',
4 source_directory_object =>' myorclstr_dir ' ,
5 destination_directory_object => ' orclstr_dir ',
6 source_database => 'myorcl.net',
7 destination_database => 'orcl.net',
8 perform_actions => true,
9 bi_directional => true,
10 include_ddl => true,
11 instantiation => dbms_streams_adm.instantiation_schema_network);
12 end;
13 /
PL/SQL procedure successfully completed.
注：bi_directional：為false是單向複製，為true時是雙向複製; 在源庫執行maintain_schemas時,目標庫幾乎什麼都不用做,stream環境已經配置好了如果想複製其它的表只用再執行maintain_tables過程即可。
批註：在此操作過程中注意檢視日誌檔案：
tail -f /u01/app/admin/myorcl/bdump/tail -f alert_myorcl.log 和 tail -f /u01/app/admin/orcl/bdump/alert_orcl.log
10.驗證
1)目標庫檢視是否存在huyong使用者，如下：
SQL> select username from dba_users where username like '%HUYONG%';
USERNAME
---------------
HUYONG
2)檢視目標庫huyong使用者下是否存在test01表，如下：
SQL> conn huyong/huyong;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST01 TABLE
3)在源庫的huyong使用者下的test01表中插入資料，檢視目標庫是否同步，如下：
源庫操作如下：
SQL> conn huyong/huyong
Connected.
SQL> insert into test01 values (1,'huyong');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test01;
ID NAME
---------- --------------------
1 huyong
目標庫操作如下：
SQL> select * from test01;
ID NAME
---------- --------------------
1 huyong
4)在目標庫對錶test01進行更新操作，如下：
目標庫操作如下：
SQL> update test01 set name='xiaoru' where id=1;
1 row updated.
SQL> commit;
Commit complete.
源庫查詢是否更新，操作如下：
SQL> select * from test01;
ID NAME
---------- --------------------
1 xiaoru
5)源庫huyong使用者下新增新表，檢視目標庫huyong使用者是否有更新，操作如下：
源庫huyong使用者操作如下：
SQL> create table test02 as select * from test01;
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST01 TABLE
TEST02 TABLE
目標庫huyong使用者查詢是否有新表新增，如下：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST01 TABLE
TEST02 TABLE
6)目標庫huyong使用者下刪除表test
目標庫huyong使用者下操作如下：
SQL> drop table test02 purge;
Table dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST01 TABLE
源庫huyong使用者檢視test02表是否存在，操作如下：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST01 TABLE
總結：由以上驗證結果可知，已經成功配置了stream使用者級之間的同步操作。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29634949/viewspace-1172540/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE10g Stream表級複製配置Oracle
- oracle stream之schema級複製Oracle
- 資料複製_Stream
- MySQL 5.5級聯複製配置流程MySql
- Oracle10g新特性之stream流配置Oracle
- mysql複製--主從複製配置MySql
- Oracle 10g stream 一對多複製Oracle 10g
- MySQL 8 複製（五）——配置GTID複製MySql
- 一次通過stream複製解決資料單向複製的案例
- Oracle Stream(3)--Stream與高階複製和邏輯Dataguard的比較Oracle
- MySQL 8 複製（九）——組複製聯機配置MySql
- GoldenGate單向複製配置(支援DDL複製)Go
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- DM7資料複製之模式級複製模式
- mysql主主複製（雙主複製）配置步驟MySql
- MySql 主從複製配置MySql
- 為Oracle配置DDL複製Oracle
- goldengate配置DDL複製Go
- MySQL主從複製配置MySql
- 複製使用者結構
- MYSQL主從複製製作配置方案MySql
- GoldenGate配置（三）之DDL複製配置Go
- DM7資料複製之資料庫級複製資料庫
- Oracle Stream配置詳細步驟(使用者模式)Oracle模式
- MySQL主主複製（雙主複製）配置過程介紹MySql
- 【Mongo】單節點升級為複製集再升級為分片加複製集Go
- mysql5.5.20複製配置MySql
- MYSQL主從複製配置（整理）MySql
- MySQL複製3--配置MasterMySqlAST
- Redis 4.0主從複製配置Redis
- MySQL 5.7 延遲複製配置MySql
- goldengate 單向複製配置Go
- redhat 5.5 配置 mysql AB複製RedhatMySql
- Mysql 5.6庫級表級複製的搭建MySql
- MySQL入門--MySQL複製技術之主從從級聯複製MySql
- GoldenGate配置（二）之雙向複製配置Go
- GoldenGate配置（一）之單向複製配置Go
- 5-5配置Mysql複製 基於日誌點的複製MySql