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;
/
0 comments:
Post a Comment