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...