Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4918173
  • 博文数量: 1000
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 13062
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(1000)

文章存档

2019年(90)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2012-03-20 14:13:49

环境:
OS:Red Hat Linux As 5
DB:10.2.0.4
 
主库:HostA
备库:HostB
Oracle包dbms_streams_adm中提供了global级(视图dba_streams_global_rules),schema级(dba_streams_schema_rules),table级(dba_streams_table_rules)的流复制,以下是针对某个schema而实现的流复制.
 
1.HostA和HostB必须在归档模式
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/duplicate/
Oldest online log sequence     19
Next log sequence to archive   21
Current log sequence           21

 
------ 以下操作需要在sys下执行--------
2.HostA 配置supplemental log
SQL>alter database add supplemental log data;
 
3.HostA和HostB 配置相应参数
SQL> alter system set job_queue_processes=4 scope=both;
SQL> alter system set aq_tm_processes=4 scope=both;
SQL> alter system set global_names=true scope=both;

 
4.HostA和HostB配置global_name
HostA:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to hxl.com;
HostB:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to standby.com;
 
5.HostA和HostB创建表空间以及相应用户
HostA:
SQL>create tablespace streams_tbs datafile '/u01/app/oracle/oradata/oracl/streams_tbs01.dbf' size 1024M;
SQL>create user strmadmin identified by strmadmin
    default tablespace streams_tbs
    quota unlimited on streams_tbs;
SQL>grant dba,select_catalog_role to strmadmin;
 
HostB:
SQL>create tablespace streams_tbs datafile '/u02/app/oracle/oradata/streams_tbs01.dbf' size 1024M;
SQL>create user strmadmin identified by strmadmin
    default tablespace streams_tbs
    quota unlimited on streams_tbs;
SQL>grant dba,select_catalog_role to strmadmin;
 
6.HostA和HostB上给用户strmadmin赋予权限
begin
    dbms_streams_auth.grant_admin_privilege(
      grantee          => 'strmadmin',
      grant_privileges => true);
 end;

 
7.配置listener.ora和tnsnames.ora
HostA:tnsping STANDBY
HostB:tnsping TEST
确保以上能够连通.

-------- 以下操作在strmadmin用户下执行---------
8.HostA 创建dblink
SQL>create database link standby.com connect to strmadmin identified by strmadmin using 'standby';
注意这里dblink的名必须是HostB配置的global_name.
 
9.HostA和HostB上创建队列
begin
  dbms_streams_adm.set_up_queue();
end;
 
默认创建queue为streams_queue, queue_table为 streams_queue_table.
相关系统视图:dba_queues,dba_queue_tables.
 
10.HostA 上创建Stream propagation
Begin
  Dbms_Streams_Adm.Add_Schema_Propagation_Rules
  (Schema_Name            => 'scott',
   Streams_Name           => 'TEST_to_STANDBY',
   Source_Queue_Name      => 'strmadmin.streams_queue',
   Destination_Queue_Name => 'strmadmin.streams_queue@STANDBY.com',
   Include_Dml            => True,
   Include_Ddl            => True,
   Source_Database        => 'hxl.com',
   Inclusion_Rule         => True,
   Queue_To_Queue         => True
   );
End;
 
相关视图:dba_propagation
 
11.HostA上创建Capture进程
BEGIN
   dbms_streams_adm.add_schema_rules
   (
     schema_name     => 'scott',
     streams_type    => 'capture',
     streams_name    => 'capture_test',
     queue_name      => 'strmadmin.streams_queue',
     include_dml     => true,
     include_ddl     => true,
     inclusion_rule  => true
    );
 END;
 
相关视图:dba_capture,dba_rules,dba_rule_set_rules
 
12.将HostA上的scott schema数据导到HostB上
HostB:
SQL>drop user scott cascade;
SQL> create public database link hxl.com connect to strmadmin identified by strmadmin using 'TEST';
 
$impdp strmadmin/strmadmin network_link=hxl.com schemas=scott;
 
13.在 HostA 上设置STANDBY上scott schema的Instantiation SCN;
DECLARE
    iscn NUMBER;
    BEGIN
    iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
    DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@STANDBY.com
   (
    source_schema_name    => 'scott',
    source_database_name => 'hxl.com',
    instantiation_scn     => iscn,
    recursive             => true
   );
END;
 
14.在HostB上创建apply进程apply_STANDBY:
BEGIN
   dbms_streams_adm.add_schema_rules(
      schema_name     => 'scott',
      streams_type    => 'apply',
      streams_name    => 'apply_STANDBY',
      queue_name      => 'strmadmin.streams_queue',
      include_dml     => true,
      include_ddl     => true,
      source_database => 'hxl.com',
      inclusion_rule => true);
END;
 
15.在HostB上启动capture和apply
BEGIN
    DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name => 'apply_STANDBY',
    parameter   => 'disable_on_error',
    value       => 'n');
END;

BEGIN
    DBMS_APPLY_ADM.START_APPLY(
     apply_name => 'apply_STANDBY');
END;
 
16 在HostA上启动capture process
BEGIN
    DBMS_CAPTURE_ADM.START_CAPTURE(
      capture_name =>'capture_test');
END;
 
17.测试
HostA:
SQL>update emp set ENAME='HXLLI' where empno=7902;
SQL>commit;
HostB:
SQL> select empno,ENAME from emp where empno=7902;
     EMPNO ENAME
---------- ----------
      7902 HXLLI
 
Streams配置和管理相关package

dbms_steams_adm
dbms_capture_adm
dbms_propagation_adm
dbms_apply_adm
dbms_rule_adm
 
Streams主要相关数据字典
 
主库:
dba_capture;
dba_capture_prepared_tables;
dba_capture_parameters;
dba_capture_extra_attributes;
dba_capture_prepared_schemas;
 
dba_streams_administrator
dba_propagation
dba_registered_archived_log
 
dba_rules
dba_rule_sets
dba_rule_set_rules
v$streams_capture
v$streams_pool_advice
v$streams_transaction
v$propagation_receiver
v$propagation_sender
v$streams_apply_coordinator
v$streams_apply_reader
v$streams_apply_server
 
v$buffered_publishers
 
备库:
dba_apply
dba_apply_confict_columns
dba_apply_dml_handlers
dba_apply_enqueue
dba_apply_error
dba_apply_execute
dba_apply_parameters
dba_apply_progress
 
v$buffered_publishers
 
排错:
主库和备库都重启后发现之前创建的流复制不起作用了,问题找了很久,最后发现是PROPAGATION因为之前的错误而停止了,重新启动该PROPAGATION,问题解决.
 
1.主库的capture没问题
SQL> SELECT status FROM DBA_CAPTURE t Where t.capture_name = 'CAPTURE_TEST';
STATUS
--------
ENABLED
2.备库的apply也没问题
SQL> select status from dba_apply t Where t.apply_name ='APPLY_STANDBY';
STATUS
--------
ENABLED
 
3.发现主库的PROPAGATION失效
SQL> SELECT status FROM DBA_PROPAGATION t Where t.propagation_name = 'TEST_TO_STANDBY';
STATUS
--------
DISENABLED
 
4.重启动PROPAGATION,主库的数据就能传输到备库了.
Begin
  Dbms_Propagation_Adm.Start_Propagation(Propagation_Name => 'TEST_TO_STANDBY');
End;
 
 
-- The End --
 
 
 
 
 
 
阅读(3055) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册