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)
)
)
网络连接测试:
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
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