Streams Configuration in two systems (ONE
WAY REPLICATION)
(Source-.destination)
The replication is done on
scotts emp table
Step 1: Create two databases
Create source database srcdb
Create destination database destdb
Step 2: Check weather both databases are in
archive log mode are not( for both the databases)
Sql>archive log list;
(Or) Sql>select log_mode from
v$database;
If the database is not in archive log mode then
make the databases in archive log mode
Step 3: Following parameters should be set in the
spfiles of both databases:
CONN /AS SYSDBA
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
SHUTDOWN IMMEDIATE;
STARTUP;
We need to shut down the database so that this
parameter takes effect.
Step 4: Create Stream Administrator Account
You need to create a stream administrator
account and grant all required privileges as
shown below:
CONN /AS SYSDBA
CREATE USER streamadmin IDENTIFIED BY abc123
DEFAULT TABLESPACE users QUOTA UNLIMITED ON
users;
Grant all required privileges:
GRANT CONNECT, RESOURCE,
SELECT_CATALOG_ROLE TO streamadmin;
GRANT EXECUTE ON DBMS_AQADM TO
streamadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO
streamadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO
streamadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO
streamadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO
streamadmin;
GRANT EXECUTE ON DBMS_FLASHBACK TO
streamadmin;
Execute the following stored procedure in both
databases
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
Privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
Grantee => 'streamadmin',
grant_option => FALSE);
END;
Steps 5: Configure listener.ora on destdb and
tnsnames.ora on srcdb
Listener.ora
SID_LIST_LIDESTDB =
(SID_LIST =
(SID_DESC =
(SID_NAME = destdb)
(ORACLE_HOME =
/home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc) ) )
LIDESTDB=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.21)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC0)) ) )
Tnsnames.ora
TODESTDB=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.21)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = destdb) ) )
Step 6: Create database link on source
database(srcdb)
Create database link destdb connect to
streamadmin identified by abc123 using ‘TODESTDB’
The name of the link should be the destination
database name ie (dastdb) and should connect by using
tnsnames netservice name on source database ie
(TODESTDB)
Check if dblink is working or not from source
since the replication is from source to destination
Sql>select sysdate from
dual@destdb-->dblink name
It should pull up the date from destination
database
Step 7: Create a Queue on both source database
(srcdb) and destination database ( destdb)
Before creating a queue we need to grant
scott.emp to streamadmin from sysdba to srcdb
Conn / as sysdba
Sql>grant all on scott.emp to streamadmin;
Now we wil setup a queue on both source database
(srcdb) and destination database ( destdb) as streamadmin
Conn streamadmin/abc123
begin
dbms_streams_adm.set_up_queue(
queue_table => 'streamadmin.streams_queue_table',
queue_name => 'streamadmin.streams_queue',
storage_clause => 'TABLESPACE USERS',
queue_user => 'streamadmin');
end;
/
Step 8: Set up supplemental logging on source
database(srcdb)
Conn / as sysdba
Sql>alter table
set scott.emp add supplemental log group babu_pk(empno) always;
Step 9: configure propagation at source
database(srcdb) as streamadmin
Conn streamadmin/abc123;
begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'scott.emp',
streams_name => 'my_stream',
source_queue_name =>
'streamadmin.streams_queue',
destination_queue_name =>
'streamadmin.streams_queue@destdb',.dblink
include_dml=> true,
include_ddl=> true,
source_database => 'srcdb');
end;
/
Step 10: Configure capture process at source
database (srcdb) as streamadmin
Conn streamadmin/abc123;
begin
dbms_streams_adm.add_table_rules(
table_name => 'scott.emp',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name=>'streamadmin.streams_queue',
include_dml => true,
include_ddl => true);
end;
/
Step 11: Installation of SCN at source
database(srcdb) as streamadmin
Conn streamadmin/abc123
declare
v_scn number;
begin
v_scn:=dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@orcl(
source_object_name => 'scott.emp',
source_database_name =>'catdb',
instantiation_scn => v_scn);
end;
/
Step 11: configure Apply process at destination
database (destdb) as streamadmin
begin
dbms_streams_adm.add_table_rules(
table_name =>'scott.emp',
streams_type => 'apply',
streams_name => 'apply_simp',
queue_name => 'streamadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'srcdb');
end;
/
Step 12: Set apply process at destination database
(destdb) as streamadmin
begin
dbms_apply_adm.set_parameter(
apply_name => 'apply_simp',
parameter => 'disable_on_error',
value => 'n');
end;
/
Step 13: Start capture process at source database
(srcdb) as streamadmin
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_simp');
end;
/
Step 14: Start propagation process at source
database (srcdb) as streamadmin (optional)
Just check it since this process will be already
in start state we usually get an error that its already started..
begin
dbms_propagation_adm.start_propagation(
propagation_name => 'my_stream');
end;
/
Step 15 : Start apply process at destination database
(destdb) as streamadmin
begin
dbms_apply_adm.start_apply(
apply_name => 'apply_simp');
end;
/
