/*************************************************************************************
* @author : Chandar
* @version : 1.0
*
* Name of the Application : AddSite.sql
* Creation/Modification History :
*
* Chandar 02-Mar-2003 Created
*
* Overview of Script:
* This SQL script adds another site to existing streams environment for
* n-way replication of a schema.
* Replication is set up for the strmuser schema.
* The initial streams setup is done by SetupStreams.sql script.
* Note:
* 1. 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.
*
* 2. Setup the streams for replication for the first time using SetupStreams.sql .
***************************************************************************************/
SET VERIFY OFF
SET ECHO OFF
SPOOL add_site.log
--define variables to store global names of all sites
variable site1 varchar2(128);
variable site2 varchar2(128);
variable site3 varchar2(128);
variable scn number;
--------------------------------------------------------------------
-- get TNSNAME and streams admin user details for both the databases
--------------------------------------------------------------------
PROMPT
-- TNSNAME for site 1
ACCEPT db1 PROMPT 'Enter TNS Name 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
-- 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 of second site :'
PROMPT
-- TNSNAME for site 3
ACCEPT db3 PROMPT 'Enter TNS Name of third site :'
PROMPT
-- SYS password for site 3
ACCEPT sys_pwd_db3 PROMPT 'Enter password of SYS user of third site :'
PROMPT
-- Streams administrator username for site 3
ACCEPT strm_adm_db3 PROMPT 'Enter username of streams admin of third site :'
PROMPT
-- Streams administrator password for site 3
ACCEPT strm_adm_pwd_db3 PROMPT 'Enter password of streams admin of third site :'
-- connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- Store global name in site1 variable
PROMPT
PROMPT Get the global name of site 1
EXECUTE SELECT global_name INTO :site1 FROM global_name;
-- connect as streams administrator to site 2
PROMPT Connecting as streams administrator to site 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- Store global name in site2 variable
PROMPT
PROMPT Getting global name of site 2
EXECUTE SELECT global_name INTO :site2 FROM global_name;
-- connect as streams admin to site 3
PROMPT Connecting as sys user to site 3
conn sys/&sys_pwd_db3@&db3 as sysdba
PROMPT Getting global name of site 3
EXECUTE SELECT global_name INTO :site3 FROM global_name;
-- create a user name strmuser and grant necessary privileges
PROMPT Creating user named strmuser
GRANT CONNECT, RESOURCE TO strmuser IDENTIFIED BY strmuser;
-- connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT
-- create a database link from site 1 to site 3 connecting as streams administrator
PROMPT Creating database link from site 1 to site 3
DECLARE
sql_command VARCHAR2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:site3|| ' CONNECT TO &strm_adm_db3
IDENTIFIED BY &strm_adm_pwd_db3 USING ''&db3''';
EXECUTE IMMEDIATE sql_command;
END;
/
-- Add schema propagation rules to propagate captured changes in strmuser schema
-- from site 1 to site 3
PROMPT Adding propagation rules for strmuser schema from site 1 to site 3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'site1_to_site3',
source_queue_name => '&strm_adm_db1..strmuser_queue',
destination_queue_name => '&strm_adm_db3..strmuser_queue@'||:site3,
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
-- connect as streams administrator to site 2
PROMPT Connecting as streams administrator to site 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
PROMPT
-- create a database link from site 2 to site 3 connecting as streams administrator
PROMPT Creating database link from site 2 to site 3
DECLARE
sql_command VARCHAR2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:site3|| ' CONNECT TO &strm_adm_db3
IDENTIFIED BY &strm_adm_pwd_db3 USING ''&db3''';
EXECUTE IMMEDIATE sql_command;
END;
/
-- Add schema propagation rules for strmuser schema to propagate captured changes
-- from site 2 to site 3
PROMPT Adding schema propagation rules for strmuser schema from site 2 to site 3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'site2_to_site3',
source_queue_name => '&strm_adm_db2..strmuser_queue',
destination_queue_name => '&strm_adm_db3..strmuser_queue@'||:site3,
include_dml => true,
include_ddl => true,
source_database => :site2);
END;
/
-- connect as streams admin to site 3
PROMPT Connecting as streams adimistrator to site 3
conn &strm_adm_db3/&strm_adm_pwd_db3@&db3
-- create and set up streams queue at site 3
PROMPT
PROMPT Creating streams queue at site 3
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmuser_queue_table',
queue_name => 'strmuser_queue',
queue_user => 'strmuser');
END;
/
-- create a database link to site 1 connecting as streams administrator
PROMPT Creating database link 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;
/
-- 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;
/
-- Add schema propagation rules to propagate captured changes in strmuser schema
-- from site 3 to site 1
PROMPT Adding propagation rules for strmuser schema from site 3 to site 1
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'site3_to_site1',
source_queue_name => '&strm_adm_db3..strmuser_queue',
destination_queue_name => '&strm_adm_db1..strmuser_queue@'||:site1,
include_dml => true,
include_ddl => true,
source_database => :site3);
END;
/
-- Add schema propagation rules to propagate captured changes in strmuser schema
-- from site 3 to site 2
PROMPT Adding propagation rules for strmuser schema from site 3 to site 2
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'site3_to_site2',
source_queue_name => '&strm_adm_db3..strmuser_queue',
destination_queue_name => '&strm_adm_db2..strmuser_queue@'||:site2,
include_dml => true,
include_ddl => true,
source_database => :site3);
END;
/
-- Configure site 1 and site 2 to apply changes from site 3. Apply should be
-- left in the DISABLED state.
-- connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT
-- create an apply process and add schema rules for strmuser schema to apply
-- any changes propagated from site 3
PROMPT Creating apply process at site 1 to apply changes in strmuser schema
PROMPT propagated from site 3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_site3',
queue_name => '&strm_adm_db1..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site3);
END;
/
-- connect as streams administrator to site 2
PROMPT Connecting as streams administrator to site 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- create an apply process and add schema rules for strmuser schema to apply
-- any changes propagated from site 3
PROMPT Creating apply process at site 2 to apply changes in strmuser schema
PROMPT propagated from site 3
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_site3',
queue_name => '&strm_adm_db2..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site3);
END;
/
-- create apply engines at site 3
-- connect as streams administrator to site 3
PROMPT Connecting as streams administrator to site 3
CONN &strm_adm_db3/&strm_adm_pwd_db3@&db3
PROMPT
-- 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 3 to apply changes in strmuser schema
PROMPT propagated from site 1
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_site1',
queue_name => '&strm_adm_db3..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
PROMPT Creating apply process at site 3 to apply changes in strmuser schema
PROMPT propagated from site 2
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_site2',
queue_name => '&strm_adm_db3..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site2);
END;
/
-- create a capture process and add rules to capture the
-- changes made to strmuser schema at site 3
PROMPT Creating capture process at site 3 and adding rules for strmuser schema.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'capture',
streams_name => 'capture_site3',
queue_name => '&strm_adm_db3..strmuser_queue',
include_dml => true,
include_ddl => true);
END;
/
-- Export strmuser schema from site 1 and import it at site 3
-- connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT
-- Export strmuser schema from site 1
-- Disable apply engine at site 1 that applies changes from site 2
PROMPT Stopping the apply process that applies changes from site2 at site 1
EXECUTE DBMS_APPLY_ADM.STOP_APPLY('apply_site2');
PROMPT Preparing the strmuser schema tables for instantiation at site 1
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(schema_name =>'strmuser');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name =>'strmuser.tableone');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name =>'strmuser.tabletwo');
END;
/
PROMPT Exporting strmuser schema objects at site 1
HOST exp USERID=strmuser/strmuser@&db1 FILE=strmuser.dmp OBJECT_CONSISTENT=y ROWS=y
PROMPT Importing strmuser schema objects at site 3
HOST imp USERID=strmuser/strmuser@&db3 FILE=strmuser.dmp COMMIT=y LOG=import.log
STREAMS_INSTANTIATION=y GRANTS=n
PROMPT Getting applied message number from dba_apply_progress at site 1
EXECUTE SELECT APPLIED_MESSAGE_NUMBER INTO :scn FROM
DBA_APPLY_PROGRESS WHERE APPLY_NAME='APPLY_SITE2';
-- connect as streams administrator to database 3
PROMPT Connecting as streams administrator to database 3
CONN &strm_adm_db3/&strm_adm_pwd_db3@&db3
PROMPT
-- Set instantiation SCN for tables in strmuser at site 3 as applied_message_number
-- from site 1 for tables from site 2
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name =>'strmuser',
source_database_name =>:site2,
instantiation_scn =>:scn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name =>'strmuser.tableone',
source_database_name =>:site2,
instantiation_scn =>:scn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name =>'strmuser.tabletwo',
source_database_name =>:site2,
instantiation_scn =>:scn);
END;
/
-- setting instantiation SCN for tables at site 1 and site 2 as current SCN of site 3
PROMPT Getting the current SCN of site 3
EXECUTE :scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
-- connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT
PROMPT Setting instantiation SCN for tables in strmuser schema at site 1 to current
PROMPT SCN of site 3
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tableone',
source_database_name => :site3,
instantiation_scn => :scn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tabletwo',
source_database_name => :site3,
instantiation_scn => :scn);
END;
/
-- connect as streams administrator to site 2
PROMPT Connecting as streams administrator to site 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
PROMPT
PROMPT Setting instantiation SCN for tables in strmuser schema at site 2 to current
PROMPT SCN of site 3
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tableone',
source_database_name => :site3,
instantiation_scn => :scn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tabletwo',
source_database_name => :site3,
instantiation_scn => :scn);
END;
/
PROMPT Connecting as strmuser to site 3
CONN strmuser/strmuser@&db3
PROMPT
PROMPT Granting privileges on all tables to streams admin of site 3
GRANT ALL ON tableone TO &strm_adm_db3;
GRANT ALL ON tabletwo TO &strm_adm_db3;
-- connect as streams administrator to database 3
PROMPT Connecting as streams administrator to database 3
CONN &strm_adm_db3/&strm_adm_pwd_db3@&db3
PROMPT
-- start the apply processes at site 3
PROMPT Starting the apply processes at site 3
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_site1');
END;
/
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_site2');
END;
/
-- start the capture process
PROMPT
PROMPT Starting the capture process at site 3
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_site3');
END;
/
-- connect as streams administrator to site 1
PROMPT Connecting as streams administrator to site 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT
-- start the apply process at site 1
PROMPT Starting the apply processes at site 1
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_site2');
END;
/
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_site3');
END;
/
-- connect as streams administrator to site 2
PROMPT Connecting as streams administrator to site 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
PROMPT
PROMPT Starting the apply processes at site 2
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_site3');
END;
/
PROMPT End of Script
SPOOL OFF
|
Bookmark
