//
you're reading...
Oracle Streams

Oracle streams – Section1

Oracle Stream – Section1

Oracle Streams Overview

Oracle Streams provides a way of sharing information, different from other data sharing mode, Streams even allows data transfer between different types of databases.

Oracle Streams to achieve this by its replication process – capturing, transmission and applying. These three steps to transfer the specified information to the designated location, capture the message, message management, and respect between different databases. Compare with the traditional solutions, it is more powerful in functionality and scalability. Oracle Streams feature for distributed enterprise applications, data warehousing, high-availability solutions, and so on.

Once you master proficient enough, you can feel Streams powerful customization, such as control what information is captured, the information flow between the database, how do deal with injected database, and when to close the stream.

By some custom configuration, Streams automatically capture, application and management, such as DML/DDL trigger to modify the message. You even self-defined information into the stream, Streams will automatically transmit the information to a database or other related applications.

What can be done in Oracle Streams

  • Data Replication, Oracle Streams can captured the DML and DDL operation by capture process, and then spread to other databases through the propagate process, then copy the data by apply process. Each step is customizable.
  • Data Protection, the most effective data protection strategy is redundant, Streams apparently able to achieve this, because the main function is to
    replicate data. But note that, because of the way streams to achieve replication is logical, so if you want to replace dataguard with streams ……………. not completely impossible.
    However, you need a good design. Streams and logical standby very imagination, through the analysis of primary redolog, if you have some understanding of oracle dataguard feature, you must be able to understand
    Streams.
  • Data Warehouse Loading, Data loading is a special case of data replication. Data warehouse systems also need to be updated, such as add or modify data synchronization, streams precisely to meet this point, so streams can be applied to a data warehouse system.
  • Database Availability During Upgrade and Maintenance Operations, With the help of streams feature, you can even avoid as much as possible to reduce database downtime when upgrading or maintenance operations. And because the streams implementation is logical, therefore almost ignore the span upgrade version of cross-platform across character sets.

Oracle Streams is a more flexible feature, if when you touch the oracle long enough, then it must be able to understand, for the oracle, the flexibility often means complex.

Table level replication through Oracle Streams

Modify the initialization parameters

alter system set aq_tm_processes=2 scope=both;
alter system set compatible='11.2.0.0.0' scope=spfile;
alter system set global_names=true scope=both;
alter system set undo_retention=3600 scope=both;
alter system set job_queue_processes=10 scope=both;
alter system set parallel_max_servers=20 scope=both;
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
alter system set streams_pool_size=200M scope=spfile;
alter system set open_links=4 scope=spfile;
  • global_names, has to be true,global_name=db_name+db_domain
  • job_queue_processes, must great than 2
  • compatible, must greate than 10.2.0.In the target database, this parameter should not lower than the parameter in the sourrce database.
  • streams_pool_size, to be recommanded not less than 200M. If the SGA_TARGET has been set,this parameter can also be automatically adjusted

Both source and target database must be in archivelog mode.

Database links must be created against both source and target database.

Create the user and environment for Oracle Streams

sm1 is the source database, sm2 is the target database. Oracle database version 11g R2.

In the source database sm1

[oracle@testdb01 dbs]$ sqlplus sys/oracle123@sm1.testdb01.newzonetech.com as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 15:31:16 2016
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL> CREATE TABLESPACE streams_tbs DATAFILE '+TESTDBDATA' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL>
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
	
SQL> GRANT DBA TO strmadmin;

In the target database sm2

[oracle@testdb01 dbs]$ sqlplus sys/oracle123@sm2.testdb01.newzonetech.com as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 15:31:16 2016
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL> CREATE TABLESPACE streams_tbs DATAFILE '+TESTDBDATA' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL>
CREATE USER strmadmin IDENTIFIED BY strmadmin
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
	
SQL> GRANT DBA TO strmadmin;

In the source database, create the database link against the target database

SQL> create database link sm2.testdb01.newzonetech.com connect to strmadmin identified by strmadmin using 'sm2.testdb01.newzonetech.com';
SQL> exec dbms_streams_adm.set_up_queue();

In the target database, create the database link against the source database

SQL> create database link sm1.testdb01.newzonetech.com connect to strmadmin identified by strmadmin using 'sm1.testdb01.newzonetech.com';
SQL> exec dbms_streams_adm.set_up_queue();

Configurate the replication process

Create a capture rule in the source database – sm1

SQL>
begin
  dbms_streams_adm.add_table_rules(
  table_name => "scott.emp",
  streams_type => "capture",
  streams_name => "capture_stream",
  queue_name => "stradmin.streams_queue",
  include_dml => true,
  include_ddl => true,
  inclusion_rule => true);
end;
/

Create propagation rules in the source databas – sm1

SQL> 
begin
  dbms_streams_adm.add_table_propagation_rules(
  table_name => "scott.emp",
  streams_name => "sour_to_targ",
  source_queue_name => "stradmin.streams_queue",
  destination_queue_name => "stradmin.streams_queue@sm2.testdb01.newzonetech.com",
  include_dml => true,
  include_ddl => true,
  source_database => "sm1.testdb01.newzonetech.com",
  inclusion_rule => true,
  queue_to_queue => true);
end;
/

Create the apply rule in target database.

SQL> 
begin
  dbms_streams_adm.add_table_rules(
  table_name => "scott.emp",
  streams_type => "apply",
  streams_name => "apply_stream",
  queue_name => "stradmin.streams_queue",
  include_dml => true,
  include_ddl => true,
  source_database => "sm1.testdb01.newzonetech.com",
  inclusion_rule => true);
end;
/
SQL> 
create table scott.EMP
(
  EMPNO    NUMBER(4) not null primary key,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
);

SQL> insert into scott.emp select * from scott.emp@sm1.testdb01.newzonetech.com;

SQL> commit;

In target database

SQL> 
DECLARE
  iscn  NUMBER;
  BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@sm2.testdb01.newzonetech.com(
  source_object_name    => "scott.emp",
  source_database_name  => "sm1.testdb01.newzonetech.com",
  instantiation_scn     => iscn);
END;
/
SQL> exec dbms_apply_adm.start_apply(¨apply_stream¨);

SQL> select apply_name, status from dba_apply;

APPLY_NAME        STATUS
----------------- --------
APPLY_STREAM      ENABLED

In the source database

SQL> exec dbms_capture_adm.start_capture("capture_stream");

SQL> select capture_name, status from dba_capture;

CAPTURE_NAME      STATUS
----------------- --------
CAPTURE_STREAM    ENABLE

Test the replication

SQL> select * from scott.emp where empno=7499;

EMPNO ENAME JOB      MGR       HIREDATE    COMM   DEPTNO
----- ----- -------- --------- ----------- -----  -------
7499  ALLEN SALESMAN  7698     08/08/1995  300    30

SQL> update scott.emp set deptno=40 where empno=7499;

SQL> commit;

SQL> select * from scott.emp where empno=7499;

EMPNO ENAME JOB      MGR       HIREDATE    COMM   DEPTNO
----- ----- -------- --------- ----------- -----  -------
7499  ALLEN SALESMAN  7698     08/08/1995  300    40

SQL> alter table scott.emp add tmpcol varchar2(10);

SQL> desc scott.emp;

 NAM            NULL      TYPE
 --------------- -------- --------------
 EMPNO           NOT NULL NUMBER(4)
 ENAME                    VARCHAR2(10)
 JOB                      VARCHAR2(9)
 MGR                      NUMBER(4)
 HIREDATE                 DATE
 COMM                     NUMBER(7,2)
 DEPTNO                   NUMBER(2)
 TMPCOL                   VARCHAR2(10)
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: