Chinaunix首页 | 论坛 | 博客
  • 博客访问: 5697905
  • 博文数量: 745
  • 博客积分: 10075
  • 博客等级: 上将
  • 技术积分: 7716
  • 用 户 组: 普通用户
  • 注册时间: 2005-04-29 12:09
文章分类

全部博文(745)

文章存档

2019年(1)

2016年(1)

2010年(31)

2009年(88)

2008年(129)

2007年(155)

2006年(197)

2005年(143)

分类: Oracle

2010-01-19 22:50:22

第五步,启用追加日志:
可以基于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.
 
第十步,导入导出数据:
主库下导出文件:
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.
 

 
阅读(2020) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~