oracle 11g streams 配置问题

醉红颜梦惜

　主数据库：
　　操作系统：Red Hat Enterprise Linux  5.8
　　IP地址：10.80.1.40
　　数据库：Oracle 11.2.0.3.0
　　ORACLE_SID：devdb
　　Global_name：devdb

　从数据库：
　　操作系统：Red Hat Enterprise Linux  5.8
　　IP地址：10.80.1.50
　　数据库：Oracle 11.2.0.3.0
　　ORACLE_SID：orcl
　　Global_name：orcl

目标：使用oracle streams 将10.80.1.40数据库上用户seal的数据同步到目标数据库10.80.1.50上面，我用的是owner级downstream方法来配置的。

1.设置stream需要的初始化参数：
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=300M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;
alter system set "_job_queue_interval"=1 scope=spfile;
alter system set log_archive_format='arch%t_%s_%r.arc' scope=spfile;

2.主库上创建strmadmin用户专用表空间
create tablespace  streams_tbs  datafile  '+ORACLE_DATA/devdb/datafile/streams_tbs01.dbf '
size 300m autoextend on maxsize unlimited segment space management auto;

主库上创建Stream管理用户:
create user strmadmin identified by strmadmin
default tablespace  streams_tbs;

主库上授权Stream管理用户:
grant connect,resource,DBA,aq_administrator_role to strmadmin;

SQL> begin
  2  dbms_streams_auth.grant_admin_privilege(
  3  grantee=>'strmadmin',
  4  grant_privileges=>TRUE);
  5  end;
  6  /

3.在10.80.1.50上面的strmadmin用户和表空间配置同主库类似。

4.主库配置网络连接
ORCL =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.80.1.50)(PORT = 1521))
   (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = orcl)
   )
   )

5.从库配置网络连接
DEVDB =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.80.1.40)(PORT = 1521))
   (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = devdb)
   )
   )

6.用strmadmin用户登录，建立dblink
主库：create database link ORCL  connect to strmadmin identified by strmadmin using 'ORCL';
从库：create database link DEVDB  connect to strmadmin identified by strmadmin using 'DEVDB';
在10.80.1.40上 select * from gloal_name@orcl 查询结果为 ORCL
在10.80.1.50上 select * from gloal_name@devdb  查询结果为 DEVDB
dblink 配置成功。

7.在从库10.80.1.50设置

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/home/oracle/standby_arch  VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)' SCOPE=SPFILE;

8.在主从库上同时配置
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(devdb,orcl)' SCOPE=SPFILE;

9.在主库查询redo情况
SQL> select THREAD#, GROUP#, BYTES/1024/1024 from V$LOG;

THREAD#    GROUP# BYTES/1024/1024
---------- ---------- ---------------
      1       1             50
      1       2             50
      1       3             50

10.在从库创建standby redo时，大小要和source site的redo大小一致，数量上要比source site的redo多一个

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/home/oracle/data/orcl/redo04.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/home/oracle/data/orcl/redo05.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/home/oracle/data/orcl/redo06.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
('/home/oracle/data/orcl/redo07.log') SIZE 50M;

11.在downstream site上检查一下是否建立成功:

SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;

GROUP# THREAD#  SEQUENCE# ARCHIV STATUS
---------- ---------- ---------- ------ --------------------
      4       0       0 YES UNASSIGNED
      5       0       0 YES UNASSIGNED
      6       0       0 YES UNASSIGNED
      7       0       0 YES UNASSIGNED

12.在主库上配置参数:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl  LGWR SYNC NOREGISTER
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orcl'
SCOPE=SPFILE;

13. 在 10.80.1.50上：
1.创建队列
conn strmadmin/strmadmin

   begin
　　dbms_streams_adm.set_up_queue(
　　queue_table => 'strmadmin.DOWNSTREAM_Q_TABLE',
　　queue_name => 'strmadmin.DOWNSTREAM_Q',
   queue_user => 'STRMADMIN');
　　end;
　　/
pl/sql completed successfully

2.创建应用进程
conn strmadmin/strmadmin
begin
　　dbms_apply_adm.create_apply(
　　queue_name => 'strmadmin.DOWNSTREAM_Q',
　　apply_name => 'DOWNSTREAM_APPLY',
　　apply_captured => TRUE );
　　end;
　　/
pl/sql completed successfully

3.创建捕获进程
conn strmadmin/strmadmin
begin
　　dbms_capture_adm.create_capture(
　　queue_name => 'strmadmin.DOWNSTREAM_Q',
　　capture_name => 'DOWNSTREAM_CAPTURE',
　　rule_set_name => NULL,
      start_scn => NULL,
      source_database => 'devdb',
   use_database_link => true,
   first_scn => NULL,
   logfile_assignment => 'implicit');　　
　 end;
　　/

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00352: Unable to access another database 'devdb'
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_UTILITY@devdb' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 453
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 289
ORA-06512: at line 2

在从库10.80.1.50上面创建捕获进程时报错，这里报错后面也配不下去了，麻烦大家帮我看看是怎么回事？给下详细的排错步骤和相关的命令！谢谢

图文精华

oracle 11g streams 配置问题