/*********************************************************************************
* @author : Chandar
* @version : 1.0
*
* Name of the Application : SetupStreams.sql
* Creation/Modification History :
*
* Chandar 02-Feb-2003 Created
*
* Overview of Script:
* This SQL scripts sets up the streams for bi-directional replication between two
* databases. Replication is set up for the table named tabone in strmuser schema
* created by the script in both the databases.
* Ensure that you have created a streams administrator before executing this script.
* The script StreamsAdminConfig.sql can be used to create a streams administrator
* and configure it.
* After running this script you can use AddTable.sql script to add another active
* table to streams environment.
***********************************************************************************/
SET VERIFY OFF
SET ECHO OFF
SPOOL streams_setup.log
--define variables to store global names of two databases
variable site1 varchar2(128);
variable site2 varchar2(128);
variable scn number;
-----------------------------------------------------------------------------------
-- get TNSNAME , SYS password and streams admin user details for both the databases
-----------------------------------------------------------------------------------
PROMPT
-- TNSNAME for database 1
ACCEPT db1 PROMPT 'Enter TNS Name of first database :'
PROMPT
-- SYS password for database 1
ACCEPT syspwddb1 PROMPT 'Enter password for sys user of first database :'
PROMPT
-- Streams administrator username for database 1
ACCEPT strm_adm_db1 PROMPT 'Enter username for streams admin of first database :'
PROMPT
-- Streams administrator password for database 1
ACCEPT strm_adm_pwd_db1 PROMPT 'Enter password for streams admin on first database :'
PROMPT
-- TNSNAME for database 2
ACCEPT db2 PROMPT 'Enter TNS Name of second database :'
PROMPT
-- SYS password for database 2
ACCEPT syspwddb2 PROMPT 'Enter password for sys user of second database :'
PROMPT
-- Streams administrator username for database 2
ACCEPT strm_adm_db2 PROMPT 'Enter username for streams admin of second database :'
PROMPT
-- Streams administrator password for database 2
ACCEPT strm_adm_pwd_db2 PROMPT 'Enter password for streams admin on second database :'
PROMPT
PROMPT Connecting as SYS user to database 1
CONN sys/&syspwddb1@&db1 AS SYSDBA;
-- Store global name in site1 variable
EXECUTE SELECT global_name INTO :site1 FROM global_name;
PROMPT Granting execute privileges on dbms_lock and dbms_pipe to streams admin
GRANT EXECUTE ON DBMS_LOCK TO &strm_adm_db1;
GRANT EXECUTE ON DBMS_PIPE to &strm_adm_db1;
-- create a user name strmuser and grant necessary privileges
PROMPT Creating user named strmuser
GRANT CONNECT, RESOURCE TO strmuser IDENTIFIED BY strmuser;
PROMPT Connecting as strmuser to database1
CONN strmuser/strmuser@&db1
-- create a sample table named tabone for which the replication will be set up
PROMPT
PROMPT Creating table tabone
CREATE TABLE tabone (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
-- grant all permissions on tabone to stream administration
PROMPT Adding supplemetal logging for table tabone
ALTER TABLE tabone ADD SUPPLEMENTAL LOG GROUP tabone_log_group ( id,name) ALWAYS;
PROMPT Granting permissions on table tabone to streams administration
GRANT ALL ON strmuser.tabone TO &strm_adm_db1;
------------------------------------
-- Repeat above steps for database 2
------------------------------------
PROMPT Connecting as SYS user to database2
CONN sys/&syspwddb2@&db2 AS SYSDBA;
-- Store global name in site2 variable
EXECUTE SELECT global_name INTO :site2 FROM global_name;
PROMPT Granting execute privileges on dbms_lock and dbms_pipe to streams admin
GRANT EXECUTE ON DBMS_LOCK TO &strm_adm_db2;
GRANT EXECUTE ON DBMS_PIPE to &strm_adm_db2;
-- create a user name strmuser and grant necessary privileges
PROMPT Creating user named strmuser
GRANT CONNECT, RESOURCE TO strmuser IDENTIFIED BY strmuser;
PROMPT Connecting as strmuser
CONN strmuser/strmuser@&db2
-- create a sample table named tabone for which the replication will be set up
PROMPT
PROMPT Creating table tabone
CREATE TABLE tabone (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT Adding supplemetal logging for table tabone
ALTER TABLE tabone ADD SUPPLEMENTAL LOG GROUP tabone_log_group ( id,name) ALWAYS;
-- grant all permissions on tabone to stream administration
PROMPT Granting all permissions on tabone to streams administrator
GRANT ALL ON strmuser.tabone TO &strm_adm_db2;
----------------------------------------------------------------------------------
-- Set up replication for table tabone from database 1 to database 2 using streams
----------------------------------------------------------------------------------
-- connect as streams admin to database 1
PROMPT Connecting as streams adimistrator to database 1
conn &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- create and set up streams queue at database 1
PROMPT
PROMPT Creating streams queue
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmuser_queue_table',
queue_name => 'strmuser_queue',
queue_user => 'strmuser');
END;
/
-- Add table propagation rules for table tabone to propagate captured changes
-- from database 1 to database 2
PROMPT Adding propagation rules for table tabone
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmuser.tabone',
streams_name => 'db1_to_db2_prop',
source_queue_name => '&strm_adm_db1..strmuser_queue',
destination_queue_name => '&strm_adm_db2..strmuser_queue@'||:site2,
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
-- create a capture process and add table rules for table tabone to capture the
-- changes made to tabone in database 1
PROMPT Creating capture process at database 1 and adding table rules for table tabone.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabone',
streams_type => 'capture',
streams_name => 'capture_db1',
queue_name => '&strm_adm_db1..strmuser_queue',
include_dml => true,
include_ddl => true);
END;
/
-- create a database link to database 2 connecting as streams administrator
PROMPT Creating database link to database 2
DECLARE
sql_command VARCHAR2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:site2|| ' CONNECT TO'||
'&strm_adm_db2 IDENTIFIED BY &strm_adm_pwd_db2 USING ''&db2''';
EXECUTE IMMEDIATE sql_command;
END;
/
-- get the current SCN of database 1
PROMPT Getting current SCN of database 1
EXECUTE :scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
conn &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- Set table instantiation SCN for table tabone at database 2 to current
-- SCN of database 1
-- We need not use import/export for instantiation because table tabone
-- does not contain any data
PROMPT
PROMPT Setting instantiation SCN for table tabone at database 2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tabone',
source_database_name => :site1,
instantiation_scn => :scn);
END;
/
-- create and set up streams queue at database 2
PROMPT Setting up streams queue at database 2
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmuser_queue_table',
queue_name => 'strmuser_queue',
queue_user => 'strmuser');
END;
/
-- create an apply process and add table rules for table tabone to apply
-- any changes propagated from database 1
PROMPT Creating Apply process at database 2 and adding table rules for table tabone
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabone',
streams_type => 'apply',
streams_name => 'apply_db2',
queue_name => '&strm_adm_db2..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
-- start the apply process at database 2
PROMPT Starting the apply process
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_db2');
END;
/
-- connect to database 1 as streams administrator
PROMPT Connecting as streams administrator to database 1
conn &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- start the capture process
PROMPT
PROMPT Starting the capture process at database 1
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_db1');
END;
/
-- make dml changes to tabone to check if streams is working
PROMPT Inserting row in tabone at database 1
INSERT INTO strmuser.tabone VALUES(11,'chan');
COMMIT;
-- wait for some time so that changes are applied to database 2.
EXECUTE DBMS_LOCK.SLEEP(35);
-----------------------------------------------------------------------------------
-- Set up replication for table tabone from database 2 to database 1 using streams
-----------------------------------------------------------------------------------
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
conn &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- select table tabone to see if changes from database 1 are applied
PROMPT
PROMPT Selecting rows from tabone at database 2 to see if changes are propagated
select * from strmuser.tabone;
PROMPT
PROMPT Setting up bi-directional replication of table tabone
-- create a database link to database 1 connecting as streams administrator
PROMPT
PROMPT Creating database link from database 2 to database 1
DECLARE
sql_command varchar2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:site1|| ' CONNECT TO'||
'&strm_adm_db1 IDENTIFIED BY &strm_adm_pwd_db1 USING ''&db1''';
EXECUTE IMMEDIATE sql_command;
END;
/
-- Add table propagation rules for table tabone to propagate capture changes
-- from database 2 to database 1
PROMPT Adding table propagation rules for tabone at database 2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name =>'strmuser.tabone',
streams_name => 'db2_to_db1_prop',
source_queue_name => '&strm_adm_db2..strmuser_queue',
destination_queue_name => '&strm_adm_db1..strmuser_queue@'||:site1,
include_dml => true,
include_ddl => true,
source_database => :site2);
END;
/
-- create a capture process and add table rules for table tabone to
-- capture the changes made to tabone in database 2
PROMPT Creating capture process at database 2 and adding table rules
for table tabone
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabone',
streams_type => 'capture',
streams_name => 'capture_db2',
queue_name => '&strm_adm_db2..strmuser_queue',
include_dml => true,
include_ddl => true);
END;
/
-- get the current SCN of database 2
PROMPT Getting the current SCN of database 2
EXECUTE :scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
-- connect to database 1 as streams administrator
PROMPT Connecting as streams administrator to database 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- Set table instantiation SCN for table tabone at database 1 to current
-- SCN of database 2
PROMPT
PROMPT Setting instantiation SCN for tabone at database 2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tabone',
source_database_name => :site2,
instantiation_scn => :scn);
END;
/
-- create an apply process and add table rules for table tabone to apply
-- any changes propagated from database 2
PROMPT Creating apply process at database 1 and adding table rules for tabone
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabone',
streams_type => 'apply',
streams_name => 'apply_db1',
queue_name => '&strm_adm_db1..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site2);
END;
/
-- start the apply process
PROMPT Starting the apply process
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_db1');
END;
/
-- connect to database 2 as streams administrator
PROMPT Connecting to database 2 as streams administrator
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2;
-- start the capture process
PROMPT
PROMPT Starting the capture process at database 2
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_db2');
END;
/
-- perform dml on tabone at database 2 to check if changes are propagated
PROMPT Inserting a row into tabone at database 2
INSERT INTO strmuser.tabone VALUES(12,'kelvin');
COMMIT;
-- wait for some time so that changes are applied to database 1.
EXECUTE DBMS_LOCK.SLEEP(35);
-- connect to database 1 as streams administrator
PROMPT Connecting as streams administrator to database 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT Checking if the changes made at database 2 are applied at database 1
SELECT * FROM strmuser.tabone;
SET ECHO OFF
SPOOL OFF
PROMPT End of Script
|
Bookmark