/*********************************************************************************
* @author : Chandar
* @version : 1.0
*
* Name of the Application : SetupStreams.sql
* Creation/Modification History :
*
* Chandar 02-Mar-2003 Created
*
* Overview of Script:
* This SQL script sets up the streams for bi-directional replication between two
* databases. Replication is set up for 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 AddSite.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 sites
variable site1 varchar2(128);
variable site2 varchar2(128);
variable scn number;
---------------------------------------------------------------------------------------
-- get TNSNAME , SYS user password and streams admin user details for both the sites
---------------------------------------------------------------------------------------
PROMPT
-- TNSNAME for site 1
ACCEPT db1 PROMPT 'Enter TNS Name of first site :'
PROMPT
-- SYS user password for site 1
ACCEPT sys_pwd_db1 PROMPT 'Enter password of SYS user of first site :'
PROMPT
-- Streams administrator username for site 1
ACCEPT strm_adm_db1 PROMPT 'Enter username of streams admin of first site :'
PROMPT
-- Streams administrator password for site 1
ACCEPT strm_adm_pwd_db1 PROMPT 'Enter password of streams admin on first site :'
PROMPT
-- TNSNAME for site 2
ACCEPT db2 PROMPT 'Enter TNS Name of second site :'
PROMPT
-- SYS password for site 2
ACCEPT sys_pwd_db2 PROMPT 'Enter password of SYS user of second site :'
PROMPT
-- Streams administrator username for site 2
ACCEPT strm_adm_db2 PROMPT 'Enter username of streams admin of second site :'
PROMPT
-- Streams administrator password for site 2
ACCEPT strm_adm_pwd_db2 PROMPT 'Enter password of streams admin on second site :'
PROMPT
PROMPT Connecting as SYS user to site 1
CONN sys/&sys_pwd_db1@&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 to streams admin
GRANT EXECUTE ON DBMS_LOCK 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 site1
CONN strmuser/strmuser@&db1
-- create sample tables
PROMPT
PROMPT Creating table tableone
CREATE TABLE tableone (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT
PROMPT Creating table tabletwo
CREATE TABLE tabletwo (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT Adding supplemental logging for table tableone
ALTER TABLE tableone ADD SUPPLEMENTAL LOG GROUP tableone_log_group ( id,name) ALWAYS;
PROMPT Adding supplemental logging for table tabletwo
ALTER TABLE tabletwo ADD SUPPLEMENTAL LOG GROUP tabletwo_log_group ( id,name) ALWAYS;
-- grant all permissions on tableone and tabletwo to stream administration
PROMPT Granting permissions on tableone and tabletwo to streams administration
GRANT ALL ON strmuser.tableone TO &strm_adm_db1;
GRANT ALL ON strmuser.tabletwo TO &strm_adm_db1;
------------------------------------
-- Repeat above steps for site 2
------------------------------------
PROMPT Connecting as SYS user to site2
CONN sys/&sys_pwd_db2@&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 to streams admin
GRANT EXECUTE ON DBMS_LOCK 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 sample tables
PROMPT
PROMPT Creating table tableone
CREATE TABLE tableone (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT
PROMPT Creating table tabletwo
CREATE TABLE tabletwo (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT Adding supplemental logging for table tableone
ALTER TABLE tableone ADD SUPPLEMENTAL LOG GROUP tableone_log_group ( id,name) ALWAYS;
PROMPT Adding supplemental logging for table tabletwo
ALTER TABLE tabletwo ADD SUPPLEMENTAL LOG GROUP tabletwo_log_group ( id,name) ALWAYS;
-- grant all permissions on tableone and tabletwo to stream administration
PROMPT Granting permissions on tableone and tabletwo to streams administration
GRANT ALL ON strmuser.tableone TO &strm_adm_db2;
GRANT ALL ON strmuser.tabletwo TO &strm_adm_db2;
----------------------------------------------------------------------------------
-- Set up replication for strmuser schema from site 1 to site 2 using streams
----------------------------------------------------------------------------------
-- connect as streams admin to site 1
PROMPT Connecting as streams adimistrator to site 1
conn &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- create a database link to site 2 connecting as streams administrator
PROMPT Creating database link to site 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;
/
-- create and set up streams queue at site 1
PROMPT
PROMPT Creating streams queue at site 1
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmuser_queue_table',
queue_name => 'strmuser_queue',
queue_user => 'strmuser');
END;
/
-- Add schema propagation rules to propagate captured changes in
-- strmuser schema from site 1 to site 2
PROMPT Adding propagation rules for strmuser schema to propagate changes
PROMPT from site 1 to site 2
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'site1_to_site2',
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 rules to capture the
-- changes made to strmuser schema in site 1
PROMPT Creating capture process at site 1 and adding rules for strmuser schema.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'capture',
streams_name => 'capture_site1',
queue_name => '&strm_adm_db1..strmuser_queue',
include_dml => true,
include_ddl => true);
END;
/
-- set the instantiation SCN for tables at site 2 to current SCN of site 1
-- get the current SCN of site 1
PROMPT Getting current SCN of site 1
EXECUTE :scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
-- connect to site 2 as streams administrator
PROMPT Connecting as streams administrator to site 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- Set instantiation SCN for strmuser schema at site 2 to current
-- SCN of site 1. This will ensure that tables added to the schema in future will
-- not required instantiation
-- We need not use import/export for instantiation because tables in
-- strmuser schema do not contain any data
PROMPT
PROMPT Setting instantiation SCN for strmuser schema at site 2
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(source_schema_name => 'strmuser',
source_database_name => :site1,
instantiation_scn => :scn);
END;
/
-- Set instantiation SCN for existing tables in strmuser schema at site 2
-- to current SCN of site 1
PROMPT Setting instantiation SCN for table tableone at site 2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tableone',
source_database_name => :site1,
instantiation_scn => :scn);
END;
/
PROMPT Setting instantiation SCN for table tabletwo at site 2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tabletwo',
source_database_name => :site1,
instantiation_scn => :scn);
END;
/
-- create and set up streams queue at site 2
PROMPT Setting up streams queue at site 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 schema rules for strmuser schema to apply
-- any changes propagated from site 1
PROMPT Creating Apply process at site 2 and adding schema rules for strmuser schema
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_site1',
queue_name => '&strm_adm_db2..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
-- start the apply process at site 2
PROMPT Starting the apply process
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_site1');
END;
/
-- connect to site 1 as streams administrator
PROMPT Connecting as streams administrator to site 1
conn &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- start the capture process
PROMPT
PROMPT Starting the capture process at site 1
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_site1');
END;
/
-- make dml changes to tableone to check if streams is working
PROMPT Inserting row in tableone and tabletwo at site 1
INSERT INTO strmuser.tableone VALUES(11,'chan');
COMMIT;
INSERT INTO strmuser.tabletwo VALUES(21,'ravi');
COMMIT;
-- wait for some time so that changes are applied to site 2.
EXECUTE DBMS_LOCK.SLEEP(35);
--------------------------------------------------------------------------------------------
-- Set up bi-directional replication for strmuser schema from site 2 to site 1 using streams
--------------------------------------------------------------------------------------------
-- connect to site 2 as streams administrator
PROMPT Connecting as streams administrator to site 2
conn &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- select table data at site 2 to see if changes from site 1 are applied
PROMPT
PROMPT Selecting rows from tables at site 2 to see if changes are propagated
SELECT * FROM strmuser.tableone;
SELECT * FROM strmuser.tabletwo;
PROMPT
PROMPT Setting up bi-directional replication for strmuser schema
-- create a database link to site 1 connecting as streams administrator
PROMPT
PROMPT Creating database link from site 2 to site 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 schema propagation rules for strmuser schema to propagate captured changes
-- from site 2 to site 1
PROMPT Adding schema propagation rules for strmuser schema at site 2
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'site2_to_site1',
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 schema rules to capture the
-- changes made to strmuser schema in site 2
PROMPT Creating capture process at site 2 and adding schema rules for strmuser schema
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'capture',
streams_name => 'capture_site2',
queue_name => '&strm_adm_db2..strmuser_queue',
include_dml => true,
include_ddl => true);
END;
/
-- set the instantiation SCN for tables at site 1 to current SCN of site 2
-- get the current SCN of site 2
PROMPT Getting the current SCN of site 2
EXECUTE :scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
-- connect to site 1 as streams administrator
PROMPT Connecting as streams administrator to site 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- Set instantiation SCN for strmuser schema at site 1 to current
-- SCN of site 2. This will ensure that tables added to the schema
-- in future will not required instantiation
PROMPT
PROMPT Setting instantiation SCN for strmuser schema at site 1 to current SCN of site 2
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(source_schema_name => 'strmuser',
source_database_name => :site2,
instantiation_scn => :scn);
END;
/
-- Set instantiation SCN for existing tables in strmuser schema at site 1
-- to current SCN of site 2
PROMPT Setting instantiation SCN for tableone at site 1 to current SCN of site 2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tableone',
source_database_name => :site2,
instantiation_scn => :scn);
END;
/
PROMPT Setting instantiation SCN for tabletwo at site 1 to current SCN of site 2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tabletwo',
source_database_name => :site2,
instantiation_scn => :scn);
END;
/
-- create an apply process and add schema rules for strmuser schema to apply any
-- changes propagated from site 2
PROMPT Creating apply process at site 1 and adding schema rules for strmuser
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_site2',
queue_name => '&strm_adm_db1..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site2);
END;
/
-- start the apply process at site 1
PROMPT Starting the apply process
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_site2');
END;
/
-- connect to site 2 as streams administrator
PROMPT Connecting to site 2 as streams administrator
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2;
-- start the capture process
PROMPT
PROMPT Starting the capture process at site 2
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_site2');
END;
/
-- perform dml on tableone at site 2 to check if changes are propagated
PROMPT Inserting a row into tableone at site 2
INSERT INTO strmuser.tableone VALUES(12,'kelvin');
COMMIT;
INSERT INTO strmuser.tabletwo VALUES(22,'tom');
COMMIT;
-- wait for some time so that changes are applied to site 1.
EXECUTE DBMS_LOCK.SLEEP(35);
-- connect to site 1 as streams administrator
PROMPT Connecting to site 1 as streams administrator
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1;
PROMPT
PROMPT Selecting rows from tables at site 2 to see if changes are propagated
-- check if changes made to tableone are propagated to site 1
SELECT * FROM strmuser.tableone;
SELECT * FROM strmuser.tabletwo;
PROMPT End of Script
SPOOL OFF
|
Bookmark
