<< Oracle高可用>>部分书面作业 - 第十二课 Oracle 流技术(streams)

1.说明流和ogg在机制上的差别。

收集数据
– streams--Oracle专有后台进程
– GG ---外部程序(extract)

内存分配
– Streams ---SGA(stream pool)
– GG --OS memory

事务 – 数据收集阶段(源数据库)
– Streams---所有的事务(commit,uncommited)
– GG--提交的事务

数据应用阶段(目标数据库)
– Sreams--过滤掉uncommited数据
– GG--收到的数据都是提交过的,直接应用。

事务的顺序
– Streams---SCN的顺序
– GG---commit先后顺序

--EOF--

2.你认为OGG和流分别应用在那些场景？举个现实中的案例。

OGG收费, 但是可应用在异构场合; 流免费, 但是只能应用在Oracle-Oracle场合.

现实案例: 进行Oracle-SQL Sever或者Oracle-Mysql等异构复制时就只能选择收费的OGG了.

--EOF--

3.演示基于单表的流复制案例。

============================================================

src:
启用归档
startup mount
alter database archivelog;
alter database add supplemental log data;
alter system set global_names=true scope=both;
 
创建表空间和用户
CREATE TABLESPACE streams_tbs DATAFILE '/opt/ora10g/oradata/strm1/streams_tbs.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
create user dave identified by dave;
grant dba to dave,strmadmin;

connect dave/dave@strm1;
create table t(id int primary key);

CONNECT strmadmin/strmadmin@strm1;
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
How to CHANGE the global database domain name (Doc ID 462024.1)
UPDATE GLOBAL_NAME SET GLOBAL_NAME='STRM1';
commit; -- bounce db
CREATE DATABASE LINK strm2 CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'strm2';
drop DATABASE LINK strm2;

Configure Propagation at strm1
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'dave.t',
    streams_name            => 'strm1_to_strm2',
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@strm2',
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'strm1',
    inclusion_rule          => true,
    queue_to_queue          => true);
END;
/


Configure the Capture Process at strm1
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'dave.t',   
    streams_type   => 'capture',
    streams_name   => 'capture_simp',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    => true,
    include_ddl    => true,
    inclusion_rule => true);
END;
/

================================================================
tgt:
启用归档
startup mount
alter database archivelog;
alter database add supplemental log data;
alter system set global_names=true scope=both;
 
创建表空间和用户
CREATE TABLESPACE streams_tbs DATAFILE '/opt/ora10g/oradata/strm2/streams_tbs.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
create user dave identified by dave;
grant dba to dave,strmadmin;

connect dave/dave@strm2;
create table t(id int primary key);

CONNECT strmadmin/strmadmin@strm2;
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
CREATE DATABASE LINK strm1 CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'strm1';
UPDATE GLOBAL_NAME SET GLOBAL_NAME='STRM2';
commit; -- bounce db
drop DATABASE LINK strm1;

Set the Instantiation SCN for the dave.t Table at strm2
conn strmadmin/strmadmin@strm1;
DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@strm2(
    source_object_name    => 'dave.t',
    source_database_name  => 'strm1',
    instantiation_scn     => iscn);
END;
/


Configure the Apply Process at strm2
CONNECT strmadmin/strmadmin@strm2
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'dave.t',
    streams_type    => 'apply',
    streams_name    => 'apply_simp',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => true,
    include_ddl     => true,
    source_database => 'strm1',
    inclusion_rule  => true);
END;
/

============================================================
Start Apply and Capture process

Start the Apply Process at strm2
exec DBMS_APPLY_ADM.START_APPLY(apply_name  => 'apply_simp');


Start the Apply Capture at strm1
exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name  => 'capture_simp');

============================================================
test:
conn dave/dave@strm1;
insert into t values(1);
commit;

conn dave/dave@strm2;
select * from t;

参考文档:

18 Simple Single-Source Replication Example
http://docs.oracle.com/cd/B19306_01/server.102/b14228/repsimpdemo.htm#CJFGJAIH

--EOF--

4.演示基于用户的流复制案例。

复制方向: src(strm1) -> tgt (strm1)

src:
启用归档
startup mount
alter database archivelog;
alter database add supplemental log data;
alter system set global_names=true scope=both;
 
创建表空间和用户
CREATE TABLESPACE streams_tbs DATAFILE '/opt/ora10g/oradata/strm1/streams_tbs.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
create user dave identified by dave;
grant dba to dave,strmadmin;
 
创建DBLink
conn strmadmin/strmadmin;
--UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'strm1';
--commit;
alter database rename global_name to STRM1;
Capture aborts with an ORA-1361 error after changing database global_name (Doc ID 548982.1)
create database link strm2 connect to strmadmin identified by strmadmin using 'strm2';
select * from global_name@strm2;
drop database link STRM2.REGRESS.RDBMS.DEV.US.ORACLE.COM;
 
在源数据库上创建Source 队列
connect  strmadmin/strmadmin@strm1;
BEGIN                                                                 
DBMS_STREAMS_ADM.SET_UP_QUEUE(  
queue_table => 'SOURCE_QUEUE_TABLE',--队列表
queue_name  => 'SOURCE_QUEUE', --队列  
queue_user  => 'strmadmin'); --队列用户
END;                                    
/
移除队列：
exec dbms_streams_adm.remove_queue(
queue_name => 'streams_queue',
cascade => true,
drop_unused_queue_table => true);.  
可以用查询dba_queues,dba_queue_tables来检查：
select owner,queue_table,name from dba_queues where owner='STRMADMIN';
select owner,queue_table,object_type from dba_queue_tables where owner='STRMADMIN';
 
在源数据库上创建capture 进程
conn strmadmin/strmadmin@strm1;
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name     => 'dave',    -- 用户名,如dave， 非sys或者system
streams_type      => 'capture',
streams_name     => 'capture_stream',
queue_name       => 'strmadmin.SOURCE_QUEUE',
include_dml       => true,
include_ddl        => true,
source_database => 'strm1',
include_tagged_lcr => false,
inclusion_rule     => true);
END;
/
select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;
select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE,ERROR_NUMBER from dba_capture;
select * from  ALL_CAPTURE_PREPARED_SCHEMAS;
exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_stream');
exec DBMS_CAPTURE_ADM.STop_CAPTURE(capture_name => 'capture_stream');
exec DBMS_CAPTURE_ADM.drop_CAPTURE(capture_name => 'capture_stream');
 
在源数据库上创建传播进程
conn strmadmin/strmadmin@strm1;
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name                => 'dave',  
streams_name               => 'source_to_target',  
source_queue_name          => 'strmadmin.SOURCE_QUEUE',
destination_queue_name    => 'strmadmin.TARGET_QUEUE@strm2', --此队列待创建
include_dml               => true,
include_ddl               => true,
source_database           => 'strm1',
inclusion_rule            => true,
queue_to_queue            => true);
END;
/
select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS,ERROR_MESSAGE,ERROR_DATE from dba_propagation;
select count(0) from dba_propagation;
exec dbms_propagation_adm.start_propagation('source_to_target');
exec dbms_propagation_adm.stop_propagation('source_to_target');
exec dbms_propagation_adm.drop_propagation('source_to_target');
 
#修改propagation休眠时间为0，表示实时传播LCR。  
begin  
dbms_aqadm.alter_propagation_schedule(  
queue_name => 'SOURCE_QUEUE',  
destination => 'strm2',  
latency => 0,
destination_queue => 'TARGET_QUEUE');  
end;
/
 
 
===========================================================
tgt:
启用归档
startup mount
alter database archivelog;
alter database add supplemental log data;
alter system set global_names=true scope=both;
 
创建表空间和用户
CREATE TABLESPACE streams_tbs DATAFILE '/opt/ora10g/oradata/strm2/streams_tbs.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
create user dave identified by dave;
grant dba to dave,strmadmin;
 
创建DBLink
conn strmadmin/strmadmin;
--UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'strm2';
--commit;
alter database rename global_name to STRM1;
create database link strm1 connect to strmadmin identified by strmadmin using 'strm1';
select * from global_name@strm1;
select name from v$database@strm1;
drop database link STRM1;
drop database link STRM1.REGRESS.RDBMS.DEV.US.ORACLE.COM;
 
在目标数据库strm2上创建接收队列  
connect  strmadmin/strmadmin@strm2;
BEGIN                                                                                                                           
DBMS_STREAMS_ADM.SET_UP_QUEUE(  
queue_table => 'TARGET_QUEUE_TABLE',             --队列表  
queue_name  => 'TARGET_QUEUE',                --队列  
queue_user  => 'strmadmin');                  --队列用户   
END;                                    
/
select owner,queue_table,name from dba_queues where owner='STRMADMIN';
 
在目标数据库strm2上创建Apply进程
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name      => 'SYSTEM',
streams_type    => 'apply',  
streams_name    => 'target_apply_stream',
queue_name      => 'strmadmin.TARGET_QUEUE',
include_dml     => true,
include_ddl     => true,
include_tagged_lcr => false,
source_database => 'strm1',
inclusion_rule => true);
END;
/
可以通过：
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server
查看状态
select apply_name,queue_name,status,error_number from dba_apply;
 
 
===========================================================
测试:
直接设置SCN的方式进行实例化：
---获取源库互置用户的SCN
connect strmadmin/strmadmin@strm1      
set serveroutput on
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN                                                                                                                    
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();  
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);  
END;                                                       
/      
 
---设置为目标库互置用户的SCN
connect strmadmin/strmadmin@strm2
declare
l_src_scn number;
BEGIN
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()  
into l_src_scn from dual@strm1;
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => 'dave',  
source_database_name => 'strm1',
instantiation_scn => l_src_scn);
END;                                                                                                                      
/
 
在目标数据库上启动Apply进程
connect strmadmin/strmadmin@strm2  
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'target_apply_stream', parameter
 => 'disable_on_error', VALUE => 'n');
END;
/
exec DBMS_APPLY_ADM.START_APPLY(apply_name => 'target_apply_stream');
exec DBMS_APPLY_ADM.Stop_APPLY(apply_name => 'target_apply_stream');
select apply_name,queue_name,status,ERROR_NUMBER from dba_apply;
 
在源数据库上启动capture
connect strmadmin/strmadmin@strm1
exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_stream');
exec DBMS_CAPTURE_ADM.STop_CAPTURE(capture_name => 'capture_stream');
查看状态：
select capture_name,status,ERROR_NUMBER from dba_capture;
 
test:
conn dave/dave@strm1;
create table t(id int primary key);
insert into t values(1);
commit;
conn dave/dave@strm2;
select * from t;

未解决问题:

http://f.dataguru.cn/thread-175719-1-1.html

--EOF--