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

第五步，啟用追加日誌：
可以基於Database級別或Table級別，啟用追加日誌（Supplemental Log）。在建立根據Schema粒度進行復制的Oracle Stream環境中，如果確認Schema下所有Table都有合理的主鍵(Primary Key)，則不再需要啟用追加日誌。

 
#啟用Database 追加日誌
alter database add supplemental log data;
 
#啟用Table追加日誌
alter table add supplement log group log_group_name(table_column_name) always;
 
第六步，建立DBlink：
主庫：
SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link storm connect to strmadmin identified by strmadmin using 'storm';
Database link created.
 
備庫：
SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link ora connect to strmadmin identified by strmadmin using 'ora';
Database link created.
 
第七步，以strmadmin使用者登陸資料庫建立流佇列：
主庫：
SQL> show user;
USER is "STRMADMIN"
SQL> begin
  2  dbms_streams_adm.set_up_queue(
  3  queue_table => 'ora_queue_table',
  4  queue_name => 'ora_queue');
  5  end;
  6  /
PL/SQL procedure successfully completed.
 
備庫：
SQL> show user;
USER is "STRMADMIN"
SQL> begin
  2  dbms_streams_adm.set_up_queue(
  3  queue_table => 'storm_queue_table',
  4  queue_name => 'storm_queue');
  5  end;
  6  /
PL/SQL procedure successfully completed.
 
第八步，構造實驗用使用者：
主庫：
SQL> conn / as sysdba
Connected.
SQL> alter user scott account unlock;
User altered.
SQL> grant connect, resource to scott;
Grant succeeded.
SQL> conn scott/tiger
ERROR:
ORA-28001: the password has expired

Changing password for scott
New password:
Retype new password:
Password changed
Connected.
SQL> create table penguin(id number);
Table created.
SQL> insert into penguin values (1);
1 row created.
SQL> insert into penguin values (2);
1 row created.
SQL> select * from penguin;
        ID
----------
         1
         2
備庫：
SQL> alter user scott account unlock;
User altered.
SQL> grant connect, resource to scott;
Grant succeeded.
 
第九步，建立捕獲程式：
connect strmadmin/strmadmin
SQL> begin
  2  dbms_streams_adm.add_schema_rules(
  3  schema_name => 'scott',
  4  streams_type => 'capture',
  5  streams_name => 'capture_ora',
  6  queue_name => 'strmadmin.ora_queue',
  7  include_dml => true,
  8  include_ddl => true,
  9  include_tagged_lcr => false,
 10  source_database => null,
 11  inclusion_rule => true);
 12  end;
 13  /
PL/SQL procedure successfully completed.
 
第十步，匯入匯出資料：
主庫下匯出檔案：
oracle@hpvm5:/home/db/oracle$exp userid=scott/tiger file='/arch/scott.dmp' object_consistent=y rows=y
Export: Release 10.2.0.1.0 - Production on Tue Jan 19 15:34:55 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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                        PENGUIN          2 rows exported
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
 
將匯出的檔案vscott.dmp遠端上傳到備庫：
oracle@hpvm5:/home/db/oracle$ftp 128.199.38.27
Connected to 128.199.38.27.
220 hpvm1 FTP server (Revision 1.1 Version wuftpd-2.6.1(PHNE_34698) Fri Nov 10 10:21:03 GMT 2006) ready.
Name (128.199.38.27:root): root
331 Password required for root.
Password:
230 User root logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> cd /arch
250 CWD command successful.
ftp> lcd /arch
Local directory now /arch
ftp> put scott.dmp
200 PORT command successful.
150 Opening BINARY mode data connection for scott.dmp.
226 Transfer complete.
8192 bytes sent in 0.00 seconds (63492.06 Kbytes/s)
ftp> bye
221-You have transferred 8192 bytes in 1 files.
221-Total traffic for this session was 8687 bytes in 1 transfers.
221-Thank you for using the FTP service on hpvm1.
221 Goodbye.
 
備庫下匯入檔案scott.dmp：
$ imp userid=scott/tiger file='/arch/scott.dmp' ignore=y commit=y streams_instantiation=y fromuser=scott touser=scott
$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 19 15:37:54 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 * from penguin;
        ID
----------
         1
         2
 
第十一步，主庫下建立傳播程式：
SQL> show user;
USER is "STRMADMIN"
SQL> begin
  2  dbms_streams_adm.add_schema_propagation_rules(
  3  schema_name => 'scott',
  4  streams_name => 'ora_to_storm',
  5  source_queue_name => 'strmadmin.ora_queue',
  6  destination_queue_name => 'strmadmin.storm_queue@storm