/*************************************************************************************
* @author : Chandar
* @version : 1.0
*
* Name of the Application : AddTable.sql
* Creation/Modification History :
*
* Chandar 02-Feb-2003 Created
*
* Overview of Script:
* This SQL scripts adds another table to existing streams environment for
* bi-directional replication between two databases without quiescing the exisiting
* processes. Replication is set up for the table named tabtwo in strmuser schema.
* The initial streams setup is done by StreamsSetup.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_table.log
--define variables to store global names of two databases
variable site1 varchar2(128);
variable site2 varchar2(128);
variable scn number;
--------------------------------------------------------------------
-- get TNSNAME and streams admin user details for both the databases
--------------------------------------------------------------------
PROMPT
-- TNSNAME for database 1
ACCEPT db1 PROMPT 'Enter TNS Name 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
-- 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 :'
------------------------------------------------------
--create table tabtwo in strmuser schema in database 1
------------------------------------------------------
PROMPT Connecting as strmuser to database 1
CONN strmuser/strmuser@&db1
-- create a sample table named tabtwo for which the replication will be set up
PROMPT
PROMPT Creating table tabtwo
CREATE TABLE tabtwo (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
-- grant all permissions on tabtwo to streams administrator
PROMPT Granting all permission on tabtwo to streams administrator
GRANT ALL ON strmuser.tabtwo TO &strm_adm_db1;
PROMPT Inserting sample rows in tabtwo table
INSERT INTO tabtwo VALUES(1,'Mark');
INSERT INTO tabtwo VALUES(2,'Viru');
COMMIT;
PROMPT Adding supplemetal logging for table tabtwo
ALTER TABLE tabtwo ADD SUPPLEMENTAL LOG GROUP tabtwo_log_group ( id,name) ALWAYS;
-- connect as streams administrator to database 2
PROMPT Connecting as streams administrator to database 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- Store global name in site2 variable
PROMPT
PROMPT Getting global name of database 2
EXECUTE SELECT global_name INTO :site2 FROM global_name;
PROMPT Executing wait.sql to load wait_until procedure
@@wait.sql
-- connect as streams administrator to database 1
PROMPT Connecting as streams administrator to database 1
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
-- Store global name in site1 variable
PROMPT
PROMPT Get the global name of database 1
EXECUTE SELECT global_name INTO :site1 FROM global_name;
PROMPT Executing wait.sql to load wait_until procedure
@@wait.sql
--set up a new temporary queue
PROMPT Creating a temporary queue at database 1
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'temp_queue_table',
queue_name => 'temp_queue',
queue_user => 'strmuser');
END;
/
-- create a new capture process and add table rules for table tabtwo to capture the
-- changes made to tabtwo in database 1
PROMPT Creating a temporary capture process at database 1 and adding table rules for tabtwo
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabtwo',
streams_type => 'capture',
streams_name => 'tempcapture',
queue_name => '&strm_adm_db1..temp_queue',
include_dml => true,
include_ddl => true);
END;
/
-- Add table propagation rules for table tabtwo to a new propagation process
-- to propagate captured changes from database 1 to database2
PROMPT Adding propagation rules for tabtwo to temporary propagation process at database 1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmuser.tabtwo',
streams_name => 'tempprop',
source_queue_name => '&strm_adm_db1..temp_queue',
destination_queue_name => '&strm_adm_db2..temp_queue@'||:site2,
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- create a new temporary queue
PROMPT Creating a temporary queue at database 2
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'temp_queue_table',
queue_name => 'temp_queue',
queue_user => 'strmuser');
END;
/
-- create a new apply process and add table rules for table tabtwo to apply
-- any changes propagated from database 1
PROMPT Creating a temporary apply process at database 2 and adding table rules
for tabtwo
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabtwo',
streams_type => 'apply',
streams_name => 'tempapply',
queue_name => '&strm_adm_db2..temp_queue',
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
PROMPT Table tabtwo has to be exported from database 1 and imported to database 2
PROMP so that the two databases are in sync and tabtwo is instantiated at database 2
PROMPT
HOST exp USERID=strmuser/strmuser@&db1 FILE=strmuser.dmp TABLES=tabtwo
OBJECT_CONSISTENT=y ROWS=y
HOST imp USERID=strmuser/strmuser@&db2 FILE=strmuser.dmp IGNORE=y COMMIT=y
LOG=import.log STREAMS_INSTANTIATION=y
GRANT ALL ON strmuser.tabtwo TO &strm_adm_db1;
PROMPT Connecting as strmuser to database 2
CONN strmuser/strmuser@&db2
-- grant all permissions on tabtwo to streams administration
PROMPT Granting all permission on tabtwo to streams administrator
GRANT ALL ON strmuser.tabtwo TO &strm_adm_db2;
-----------------------------------------------------
-- set up bi-directional replication for table tabtwo
------------------------------------------------------
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
PROMPT
-- add table rules for table tabtwo to original capture process to capture the
-- changes made to tabtwo in database 2
PROMPT Adding table rules for tabtwo to original capture process at database 2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabtwo',
streams_type => 'capture',
streams_name => 'capture_db2',
queue_name => '&strm_adm_db2..strmuser_queue',
include_dml => true,
include_ddl => true);
END;
/
-- Add table propagation rules for table tabtwo to original propagation
-- process to propagate capture changes from database 2 to database 1
PROMPT Adding table propagation rules for tabtwo to original propagation
process at database 2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmuser.tabtwo',
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;
/
-- 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 Adding table rule for tabtwo at database 1 to original apply process
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabtwo',
streams_type => 'apply',
streams_name => 'apply_db1',
queue_name => '&strm_adm_db1..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site2);
END;
/
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
PROMPT
-- get the current SCN of database 2
PROMPT Getting 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 tabtwo at database 1 to current
-- SCN of database 2
PROMPT
PROMPT Setting instatiation SCN for tabtwo at database 1
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'strmuser.tabtwo',
source_database_name => :site2,
instantiation_scn =>:scn);
END;
/
-----------------------------
--End of bi-directional setup
-----------------------------
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
PROMPT
-- start the new temporary apply process at database 2
PROMPT Starting the apply process
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'tempapply');
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 new capture process at database 1
PROMPT
PROMPT Starting the temporary capture process at database 1
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'tempcapture');
END;
/
-- make dml changes to tabtwo to check if new streams processes are working
PROMPT Inserting row in tabtwo at database 1 to see if changes are captured and propagated
INSERT INTO strmuser.tabtwo VALUES (3,'Stuart');
COMMIT;
-- now add table rules for table tabtwo to original capture process to capture the
-- changes made to tabtwo in database 1
PROMPT Adding table capture rules for tabtwo to original cature process at database 1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabtwo',
streams_type => 'capture',
streams_name => 'capture_db1',
queue_name => '&strm_adm_db1..strmuser_queue',
include_dml => true,
include_ddl => true);
END;
/
-- Add table propagation rules for table tabtwo to original propagation process
-- to propagate captured changes from database 1 to database 2
PROMPT Adding table propagation rules for tabtwo to original propagation process
at database 1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmuser.tabtwo',
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;
/
-----------------------------------------------------------------------
-- Get a shared lock on tabtwo at database 1 and obtain the current scn.
-- The shared lock is needed so that we can be sure there are no
-- transactions in progress during the time we get the current scn.
-- If not, we may end up losing some LCRs.
-----------------------------------------------------------------------
PROMPT Getting a shared lock on table tabtwo
LOCK TABLE strmuser.tabtwo IN SHARE MODE;
PROMPT Get current SCN of database 1 and set it as max SCN for temporary capture
process at database 1 and original apply process at database 2
-- This has to be done in different session because SET_PARAMETER procedure does
-- an implicit commit which will release the lock on tabtwo table in this session.
PROMPT
PROMPT This is to be done in a different session. The current session will
PROMPT hang and wait for a message from new session to contiue further
PROMPT
PROMPT Connect to SQL PLUS from a new command window as any user to database 1 and
execute script SetParams.sql located at /scripts directory.
When this script completes, it sends message to this session to continue.
PROMPT
PROMPT Waiting till new session sends a message to continue. Do not close this window.
EXECUTE wait_until('s1','s2','1.2');
PROMPT Recevied message to continue
-- release lock on tabtwo at database 1 using commit
PROMPT Releasing the shared lock of table tabtwo at database 1
COMMIT;
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
--Perform some DML so that SCN increases
PROMPT Performing DML on database 2 so that SCN increases
INSERT INTO strmuser.tabone VALUES(53,'Joe');
COMMIT;
INSERT INTO strmuser.tabone VALUES(54,'John');
COMMIT;
INSERT INTO strmuser.tabone VALUES(55,'Jacob');
COMMIT;
INSERT INTO strmuser.tabone VALUES(56,'Jim');
COMMIT;
INSERT INTO strmuser.tabone VALUES(57,'Saurav');
COMMIT;
UPDATE strmuser.tabone SET name='Carl' WHERE name='John';
COMMIT;
UPDATE strmuser.tabone SET name='Greg' WHERE name='Joe';
COMMIT;
UPDATE strmuser.tabone SET name='Sam' WHERE name='Jacob';
COMMIT;
UPDATE strmuser.tabone SET name='Kashish' WHERE name='Jim';
COMMIT;
UPDATE strmuser.tabone SET name='Molly' WHERE name='Saurav';
COMMIT;
PROMPT Waiting for original apply process to get disabled because of reaching maximum SCN
EXECUTE DBMS_LOCK.SLEEP(60);
-- When original apply process is disabled because maximum SCN has reached,
-- add table rules for table tabtwo to apply any changes propagated from database 1
PROMPT When original apply process at database 2 is disabled
PROMPT Adding table rules for tabtwo to original apply process at database 2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmuser.tabtwo',
streams_type => 'apply',
streams_name => 'apply_db2',
queue_name => '&strm_adm_db2..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :site1);
END;
/
-- connect to database 1 as streams administrator
PROMPT Connecting to database 1 as streams administrator
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT
-- stop the new capture process
PROMPT Stopping the temporary capture process
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name =>' tempcapture');
END;
/
-- drop the new capture process
PROMPT Dropping the temporary capture process
BEGIN
DBMS_CAPTURE_ADM.DROP_CAPTURE(
capture_name =>' tempcapture');
END;
/
-- drop the new propagation process
PROMPT Dropping the temporary propagation process
BEGIN
DBMS_PROPAGATION_ADM.DROP_PROPAGATION(
propagation_name => 'tempprop');
END;
/
-- stop the new temporary queue
PROMPT Stopping the temporary queue at database 1
BEGIN
DBMS_AQADM.STOP_QUEUE(
queue_name => 'temp_queue');
END;
/
-- drop the new temporary queue
PROMPT Dropping the temporary queue at database 1
BEGIN
DBMS_AQADM.DROP_QUEUE(
queue_name => 'temp_queue');
END;
/
-- drop the queue table of new temporary queue
PROMPT Dropping the queue table for temporary queue at database 1
BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE('temp_queue_table');
END;
/
-- connect to database 2 as streams administrator
PROMPT Connecting as streams administrator to database 2
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
-- stop the new apply process
PROMPT
PROMPT Stopping the temporary apply process
BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'tempapply');
END;
/
-- drop the new apply process
PROMPT Dropping the temporary apply process
BEGIN
DBMS_APPLY_ADM.DROP_APPLY(
apply_name => 'tempapply');
END;
/
-- stop the temporary queue at database 2
PROMPT Stopping the temporary queue at database 2
BEGIN
DBMS_AQADM.STOP_QUEUE(
queue_name => 'temp_queue');
END;
/
-- drop the temporary queue at database 2
PROMPT Dropping the temporary queue at database 2
BEGIN
DBMS_AQADM.DROP_QUEUE(
queue_name => 'temp_queue');
END;
/
-- drop the queue table of new temporary queue
PROMPT Dropping the temporary queue table at database 2
BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE('temp_queue_table');
END;
/
PROMPT
-- set the maximum_scn parameter for original apply process to infinite
PROMPT Setting the maximum SCN for original apply process at database 2 to infinite
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER('apply_db2','maximum_scn','infinite');
END;
/
-- start the original apply process
PROMPT Starting the original apply process at database 2
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_db2');
END;
/
PROMPT Performing DML on tabtwo at database 2 to check if setup is working
INSERT INTO strmuser.tabtwo VALUES(61,'shrini');
COMMIT;
PROMPT Wait for some time so that changes are applied to database 1
EXECUTE DBMS_LOCK.SLEEP(30);
-- connect to database 1 as streams administrator
PROMPT Connecting to database 1 as streams administrator
CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1
PROMPT
PROMPT Checking if changes on tabtwo from database 2 are applied at database 1
SELECT * FROM strmuser.tabtwo;
PROMPT Performing DML on tabtwo at database 1
INSERT INTO strmuser.tabtwo VALUES (62,'rajat');
PROMPT Wait for some time so that changes are applied to database 2.
EXECUTE DBMS_LOCK.SLEEP(30);
COMMIT;
-- connect to database 2 as streams administrator
PROMPT Connecting to database 2 as streams administrator
CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2
PROMPT
PROMPT Checking if changes on tabtwo from database 1 are applied at database 2
SELECT * FROM strmuser.tabtwo;
PROMPT End of Script
SPOOL OFF
|
Bookmark
