第三步，建立用於管理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

單例項和RAC打造的ORACLE STREAM（三）

相關文章