#启用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.
第十步,导入导出数据:
主库下导出文件:
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远程上传到备库:
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 => ,
7 include_dml => true,
8 include_ddl => true,
9 include_tagged_lcr => false,
10 source_database => 'ora',
11 inclusion_rule => true);
12 end;
13 /
PL/SQL procedure successfully completed.
设置即时传播:
SQL> begin
2 dbms_aqadm.alter_propagation_schedule(
3 queue_name => 'ora_queue',
4 destination => 'storm',
5 latency => 0);
6 end;
7 /
PL/SQL procedure successfully completed.