第三步，创建用于管理stream的用户和对应的表空间并赋予用户相应权限：
主库：
SQL> create tablespace tbs_stream datafile '/home/db/oracle/10g/oradata/tbs_stream01.dbf'
2 size 100m autoextend on maxsize unlimited segment space management auto;
Tablespace created.

#将logminer的数据字典从system表空间转移到新建的表空间，防止撑满system表空间
execute dbms_logmnr_d.set_tablespace('tbs_stream');

#创建Stream管理用户
create user strmadmin identified by strmadmin
default tablespace tbs_stream temporary tablespace temp;

#授权Stream管理用户
grant connect,resource,dba,aq_administrator_role to strmadmin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
/

PL/SQL procedure successfully completed.

备库(这里只需要在实例storm1上操作即可，之后所有提到备库的操作都是在实例storm1上进行):
SQL> create tablespace tbs_stream datafile '/dev/vgdata/rstream'
2 size 100m autoextend on maxsize unlimited segment space management auto;
Tablespace created.
NOTE:因为ORACLE RAC数据库是以裸设备方式构建的，所以在建表空间的时候要指定到对应的裸设备，这和主库上建表空间所指定的路径略有不同。

SQL> execute dbms_logmnr_d.set_tablespace('tbs_stream');
PL/SQL procedure successfully completed.

SQL> create user strmadmin identified by strmadmin
2 default tablespace tbs_stream temporary tablespace temp;
User created.

SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;
Grant succeeded.

SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee => 'strmadmin',
4 grant_privileges => true);
5 end;
6 /
PL/SQL procedure successfully completed.

第四步，修改主库以及备库的tnsnames.ora文件，让两库能够互访：
主库：
STORM =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 128.199.38.27)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 128.199.38.28)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = storm)
    )
)
ORA =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpvm5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora)
    )
)

备库：
STORM =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpvm1_vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpvm2_vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = storm)
    )
)
LISTENERS_STORM =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpvm1_vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpvm2_vip)(PORT = 1521))
)
STORM2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpvm2_vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = storm)
      (INSTANCE_NAME = storm2)
    )
)
STORM1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpvm1_vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = storm)
      (INSTANCE_NAME = storm1)
    )
)
ORA =
(DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 128.199.38.32)(PORT = 1521))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = ora)
      )
   )

网络连接测试：
oracle@hpvm5:/home/db/oracle/10g/network/admin$sqlplus system/oracle@storm
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 19 14:57:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
storm1

$ sqlplus system/oracle@ora
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 19 15:14:52 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ora

ITPub博客

单实例和RAC打造的ORACLE STREAM（三）

最新文章