Simple Streams Implementaion

SQL> conn system@knbdb
Connected.
SQL>
SQL> select name from v$database;

NAME
---------
KNBDB

SQL>
SQL> create table scott.hr (eno int constraint hr_pk primary key ,ename varchar2(22))
2 /

Table created.

SQL> conn strmadmin@knbdb
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


SQL>
SQL> conn system@rdb
Connected.
SQL>
SQL> create table scott.hr (eno int constraint hr_pk primary key ,ename varchar2(22))
2 /

Table created.

SQL> conn strmadmin@knbdb
Connected.
SQL>

SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
3 table_name => 'scott.hr',
4 streams_name => 'knbdb',
5 source_queue_name => 'strmadmin.streams_queue',
6 destination_queue_name => 'strmadmin.streams_queue@rdb',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'knbdb',
10 inclusion_rule => true,
11 queue_to_queue => true);
12* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> alter table scott.hr add supplemental log data (primary key,unique) columns;

Table altered.

SQL>
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'scott.hr',
4 streams_type => 'capture',
5 streams_name => 'capture_streams',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 inclusion_rule => true);
10 END;
11 /

PL/SQL procedure successfully completed.

SQL>

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@rdb(
6 source_object_name => 'scott.hr',
7 source_database_name => 'knbdb',
8 instantiation_scn => iscn);
9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object
STRADMIN.DBMS_APPLY_ADM@RDB
ORA-00604: error occurred at recursive SQL level 1
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from RDB


SQL> select * from dual@rdb;
select * from dual@rdb
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from RDB


SQL> select name from v$database;

NAME
---------
KNBDB

SQL> create database link rdb connect to strmadmin identified by admin using 'RDB';

Database link created.

SQL> select * from dual@rdb;

D
-
X

SQL> DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@rdb(
6 source_object_name => 'scott.hr',
7 source_database_name => 'knbdb',
8 instantiation_scn => iscn);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> ;
1 DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@rdb(
6 source_object_name => 'scott.hr',
7 source_database_name => 'knbdb',
8 instantiation_scn => iscn);
9* END;
SQL> /

PL/SQL procedure successfully completed.

SQL>

SQL> conn strmadmin@rdb
Connected.
SQL>
SQL>
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> create database link knbdb connect to strmadmin identified by admin using 'knbdb';

Database link created.

SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'scott.hr',
4 streams_type => 'apply',
5 streams_name => 'apply_simp',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'knbdb',
10 inclusion_rule => true);
11 END;
12 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_APPLY_ADM.SET_PARAMETER(
3 apply_name => 'apply_simp',
4 parameter => 'disable_on_error',
5 value => 'n');
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_APPLY_ADM.START_APPLY(
3 apply_name => 'apply_simp');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> conn strmadmin@knbdb
Connected.
SQL>
SQL>
SQL> BEGIN
2 DBMS_CAPTURE_ADM.START_CAPTURE(
3 capture_name => 'capture_streams');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> spool off

SQL>
SQL> conn scott@knbdb
Connected.
SQL>
SQL> select * from hr;

no rows selected

SQL>
SQL> insert into hr values (101,'babu');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr;

ENO ENAME
---------- ----------------------
101 babu

SQL>
SQL> conn hr@rdb
ERROR:
ORA-01005: null password given; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn scott@rdb
ERROR:
ORA-28000: the account is locked


SQL> system@rdb
SP2-0042: unknown command "system@rdb" - rest of line ignored.
SQL> conn system@rdb
Connected.
SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified by tiger;

User altered.

SQL> conn scott@rdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME
---------- ----------------------
101 babu

SQL>
SQL> insert into hr values (102,'taj');

1 row created.

SQL> commit;

Commit complete.

SQL> udpate hr set ename='bAbu-taj' where eno=101;
SP2-0734: unknown command beginning "udpate hr ..." - rest of line ignored.
SQL>
SQL> update hr set ename='babu-taj' where eno=101;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table hr add column sal int;
alter table hr add column sal int
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> alter table hr add sal int;

Table altered.

SQL> select * from hr;

ENO ENAME SAL
---------- ---------------------- ----------
101 babu-taj
102 taj

SQL> show user
USER is "SCOTT"
SQL>
SQL> conn scott@knbdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME
---------- ----------------------
101 babu

SQL>
SQL> alter table hr add (sal number (4));

Table altered.

SQL> conn scott@rdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME SAL
---------- ---------------------- ----------
101 babu-taj
102 taj

SQL> conn scott@knbdb
Connected.
SQL> alter table hr add (sal1 number (4));

Table altered.

SQL> conn scott@rdb
Connected.
SQL>
SQL> select * from hr;

ENO ENAME SAL SAL1
---------- ---------------------- ---------- ----------
101 babu-taj
102 taj

SQL> spool off

As per my understanding; This is explain how replication working between oracle database; Even the above example will help you read-only replication/streams concept.

Feel free your comment about my understanding...

Streams Setup between oracle database.

Os : Windows XP
Version: 10.2.0.1
Database Mode: ArchiveLog

As per my understanding from oracle documentation; I configured this streams setup between two oracle databases.

Pre-Request Configuration:

Source Database Name : KnbDb
Downstream (Remote) database name: RDB

Before implementing streams you should configure the below database parameter in source and remote database.

SQL> show parameter db_unique

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string knbdb

SQL> show parameter global

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=(D:\ArchiveLog\KnbDb)


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable

SQL> show parameter archive

NAME TYPE VALUE
------------------------------------ ----------- ----------------------
archive_lag_target integer 1800
log_archive_config string dg_config=(knbdb,rdb)


SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

PS: my database configured automatic shared management; It’s automatically take care streams pool size

Database Configuration

Once completed your pre-request configuration; three are few steps you need to configure.

1. Create Steams Tablespace

CREATE TABLESPACE Streams DATAFILE '/usr/oracle/dbs/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

2. Create Steams User

CREATE USER strmadmin IDENTIFIED BY admin
DEFAULT TABLESPACE streams
QUOTA UNLIMITED ON streams;

GRANT DBA TO strmadmin;

3. Grant Admin Privilege

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/


CREATE DIRECTORY admin_dir AS '/usr/admin';

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => false,
file_name => 'grant_strms_privs.sql',
directory_name => 'admin_dir');
END;
/


Run the GRANT_ADMIN_PRIVILEGE procedure to generate a script named grant_strms_privs.sql and place this script in the /usr/admin directory on your computer system:

.
4. Execute the script in SQL*Plus:

SET ECHO ON
SPOOL grant_strms_privs.out
@/usr/admin/grant_strms_privs.sql
SPOOL OFF


PS: As per your environment repeat the above configuration from downstream and source database.

Network Configuration

Source Database:

1. Connect Strmadmin User

2. Create database link which pointing to downstream database

Remote database:

1. Connect Strmadmin User

2. Create database link which pointing to source database

Script:

Create database link <> Connect to <> identified by <> using <>

Streams Archtechture…

What is Oracle Stream?

What is Oracle Stream?

Oracle Streams enables information sharing. Using Oracle Streams, each unit of shared information is called a message, and you can share these messages in a stream. The stream can propagate information within a database or from one database to another. The stream routes specified information to specified destinations. The result is a feature that provides greater functionality and flexibility than traditional solutions for capturing and managing messages, and sharing the messages with other databases and applications. Streams provides the capabilities needed to build and operate distributed enterprises and applications, data warehouses, and high availability solutions. You can use all of the capabilities of Oracle Streams at the same time. If your needs change, then you can implement a new capability of Streams without sacrificing existing capabilities.

Streams Information Flow



What Can Streams Do?

The following sections provide an overview of what Streams can do.

· Capture Messages at a Database

· Stage Messages in a Queue

· Propagate Messages from One Queue to Another

· Consume Messages

· Other Capabilities of Streams

A capture process can capture database events, such as changes made to tables, schemas, or an entire database. Such changes are recorded in the redo log for a database, and a capture process captures changes from the redo log and formats each captured change into a message called a logical change record (LCR). The rules used by a capture process determine which changes it captures, and these captured changes are called captured messages.

Etc…

What Are the Uses of Streams?

The following sections briefly describe some of the reasons for using Streams. In some cases, Streams components provide infrastructure for various features of Oracle.
• Message Queuing
• Data Replication
• Event Management and Notification
• Data Warehouse Loading
• Data Protection
• Database Availability During Upgrade and Maintenance Operations