Oracle 10g Stream用户级复制配置

1.之前做了Oracle 10g Stream表级复制的配置（http://www.linuxidc.com/Linux/2014-05/102522.htm），在已经存在复制环境的情况下，再配置其他类型的复制前需要清除已经存在的复制环境，否则复制会导致失败。
使用stream管理员用户，源库操作如下：

SQL> conn streamadmin/oracle@primary
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 selectedSQL> select CAPTURE_NAME,QUEUE_OWNER,STATUS,CAPTURE_USER from dba_capture;no rows selected使用stream管理员，目标库操作如下：
SQL> conn streamadmin/oracle@standby;
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
------------------------------
STREAMADMINSQL> 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
------------------------------
STREAMADMINSQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME like "%STREAM%";

TABLESPACE_NAME STATUS
------------------------------ ---------
STREAMTBS ONLINE5.源库和目标库创建互连的数据库连接
在源库验证操作如下：
SQL> conn streamadmin/oracle@primary
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 standbySQL> select * from dual@orcl.net;D
-
X
在目标库验证操作如下：
SQL> conn streamadmin/oracle@standby;
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 primarySQL> select * from dual@myorcl.net;D
-
X6.查看源库和目标库是否处于归档模式
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更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-05/102523p2.htmOracle Streams技术介绍&搭建 http://www.linuxidc.com/Linux/2014-01/95544.htm单实例到单实例Oracle Stream搭建 http://www.linuxidc.com/Linux/2012-11/74788.htmOracle 10gR2 Streams删除所有配置 http://www.linuxidc.com/Linux/2012-07/64147.htmOracle 单表流复制 Stream http://www.linuxidc.com/Linux/2011-12/49676.htmOracle简单Stream一个用户单向复制配置 http://www.linuxidc.com/Linux/2009-11/22890.htm

Oracle 10g Stream表级复制配置Oracle 10.2.0.1.0 误删除数据文件后登录提示ORA-01033相关资讯 Oracle Stream Oracle 10g配置

Oracle双向Stream配置实践（02月24日）
Redhat Linux 6.2下Oracle 10g安装（03/29/2014 14:37:29）
单实例到单实例Oracle Stream搭建（11/25/2012 07:53:19）

Oracle 10g Stream表级复制配置（05/31/2014 15:36:14）
Pro*C 环境配置（RedHat +Oracle （08/16/2013 06:27:14）

本文评论查看全部评论（0）