/************************************************************************************
* @author : Chandar
* @version : 1.0
*
* Name of the Application : StreamsSetup.sql
* Creation/Modification History :
*
* Chandar 02-Feb-2003 Created
*
* Overview of Script:
* This SQL script sets up streams in a hub and spoke configuration to update all
* the databases in the environment. This configuration uses apply forwarding at the
* hub. Each spoke sends redo to hub with a NULL tag. The hub's apply engine sets the
* bit for the spoke depending on which database(spoke) the change came from and
* propagates it to all but the original spoke where the change came from. The
* changes generated at hub itself are forwarded to spokes with a NULL tag.
*
* Replication is set up for the table named tabone in strmuser schema created by
* the script in all 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.
**************************************************************************************/
SET VERIFY OFF
SET ECHO OFF
SPOOL streams_bitmap.log
--define variables to store global names of two databases
variable spk1 varchar2(128);
variable spk2 varchar2(128);
variable hubdb varchar2(128);
variable scn number;
-----------------------------------------------------------------------------------
-- get TNSNAME , SYS password and streams admin user details for all the databases
-----------------------------------------------------------------------------------
PROMPT
-- TNSNAME for database 1(hub)
ACCEPT hub PROMPT 'Enter TNS Name of hub database :'
PROMPT
-- SYS user password for hub
ACCEPT sys_pwd_hub PROMPT 'Enter password for SYS user of hub database :'
PROMPT
-- Streams administrator username for hub database
ACCEPT strm_adm_hub PROMPT 'Enter username for streams admin of hub database :'
PROMPT
-- Streams administrator password for hub database
ACCEPT strm_adm_pwd_hub PROMPT 'Enter password for streams admin on hub database :'
PROMPT
-- TNSNAME for database 2(spoke 1)
ACCEPT spoke1 PROMPT 'Enter TNS Name of spoke 1 database :'
PROMPT
-- SYS password for spoke 1 database
ACCEPT sys_pwd_spk1 PROMPT 'Enter password for SYS user of spoke 1 database :'
PROMPT
-- Streams administrator username for spoke 1 database
ACCEPT strm_adm_spk1 PROMPT 'Enter username for streams admin of spoke 1 database :'
PROMPT
-- Streams administrator password for spoke1 database
ACCEPT strm_adm_pwd_spk1 PROMPT 'Enter password for streams admin of spoke 1 database :'
PROMPT
-- TNSNAME for database 3(spoke 2)
ACCEPT spoke2 PROMPT 'Enter TNS Name of spoke 2 database :'
PROMPT
-- SYS password for spoke 1 database
ACCEPT sys_pwd_spk2 PROMPT 'Enter password for SYS user of spoke 2 database :'
PROMPT
-- Streams administrator username for spoke 2 database
ACCEPT strm_adm_spk2 PROMPT 'Enter username for streams admin of spoke 2 database :'
PROMPT
-- Streams administrator password for database 2
ACCEPT strm_adm_pwd_spk2 PROMPT 'Enter password for streams admin of spoke 2 database :'
PROMPT Connecting as SYS user to hub database
CONNECT sys/&sys_pwd_hub@&hub AS SYSDBA
PROMPT
PROMPT Getting global name of hub database
EXECUTE SELECT GLOBAL_NAME INTO :hubdb FROM GLOBAL_NAME;
PROMPT Creating user 'strmuser' and granting it CONNECT and RESOURCE role
GRANT CONNECT, RESOURCE TO strmuser IDENTIFIED BY strmuser;
PROMPT Connecting as strmuser to hub database
CONN strmuser/strmuser@&hub
PROMPT Creating table tabone at hub
CREATE TABLE tabone (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT Granting all permissions on tabone to streams administrator
GRANT ALL ON strmuser.tabone TO &strm_adm_hub;
PROMPT Connecting as SYS user to spoke1 database
CONNECT sys/&sys_pwd_spk1@&spoke1 AS SYSDBA
PROMPT
PROMPT Getting global name of spoke1 database
EXECUTE SELECT GLOBAL_NAME INTO :spk1 FROM GLOBAL_NAME;
PROMPT Creating user 'strmuser' and granting it CONNECT and RESOURCE role
GRANT CONNECT, RESOURCE TO strmuser IDENTIFIED BY strmuser;
PROMPT Connecting as strmuser to spoke1 database
CONNECT strmuser/strmuser@&spoke1
PROMPT Creating table tabone at spoke1
CREATE TABLE tabone (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT Granting all permissions on tabone to streams administrator
GRANT ALL ON strmuser.tabone TO &strm_adm_spk1;
PROMPT Connecting as SYS user to spoke2 database
CONNECT sys/&sys_pwd_spk2@&spoke2 AS SYSDBA
EXECUTE SELECT GLOBAL_NAME INTO :spk2 FROM GLOBAL_NAME;
PROMPT Creating user 'strmuser' and granting it CONNECT and RESOURCE role
GRANT CONNECT, RESOURCE TO strmuser IDENTIFIED BY strmuser;
PROMPT Connecting as strmuser to spoke2 database
CONNECT strmuser/strmuser@&spoke2
PROMPT Creating table tabone at spoke2
CREATE TABLE tabone (id NUMBER(5) PRIMARY KEY, name VARCHAR2(50));
PROMPT Granting all permissions on tabone to streams administrator
GRANT ALL ON strmuser.tabone TO &strm_adm_spk2;
PROMPT Connecting as streams administrator to hub database
CONNECT &strm_adm_hub/&strm_adm_pwd_hub@&hub
PROMPT
PROMPT Creating database link to spoke1 database
DECLARE
sql_command VARCHAR2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:spk1|| ' CONNECT TO'||
' &strm_adm_spk1 IDENTIFIED BY &strm_adm_pwd_spk1 USING ''&spoke1''';
EXECUTE IMMEDIATE sql_command;
END;
/
PROMPT Creating database link to spoke2 database
DECLARE
sql_command VARCHAR2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:spk2|| ' CONNECT TO'||
' &strm_adm_spk2 IDENTIFIED BY &strm_adm_pwd_spk2 USING ''&spoke2''';
EXECUTE IMMEDIATE sql_command;
END;
/
PROMPT Setting up streams queue at hub database
-- create streams queue at hub database
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmuser_queue_table',
queue_name => 'strmuser_queue',
queue_user => 'strmuser');
END;
/
PROMPT Connecting as streams administrator to spoke1 database
CONNECT &strm_adm_spk1/&strm_adm_pwd_spk1@&spoke1
PROMPT
PROMPT Creating database link to hub database
DECLARE
sql_command VARCHAR2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:hubdb|| ' CONNECT TO'||
' &strm_adm_hub IDENTIFIED BY &strm_adm_pwd_hub USING ''&hub''';
EXECUTE IMMEDIATE sql_command;
END;
/
PROMPT Setting up streams queue at spoke1 database
-- create streams queue at spoke1 database
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmuser_queue_table',
queue_name => 'strmuser_queue',
queue_user => 'strmuser');
END;
/
PROMPT Connecting as streams administrator to spoke2 database
CONNECT &strm_adm_spk2/&strm_adm_pwd_spk2@&spoke2
PROMPT Creating database link to hub database
DECLARE
sql_command VARCHAR2(200);
BEGIN
sql_command :='CREATE DATABASE LINK ' ||:hubdb|| ' CONNECT TO'||
'&strm_adm_hub IDENTIFIED BY &strm_adm_pwd_hub USING ''&hub''';
EXECUTE IMMEDIATE sql_command;
END;
/
PROMPT Setting up streams queue at spoke2 database
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmuser_queue_table',
queue_name => 'strmuser_queue',
queue_user => 'strmuser');
END;
/
PROMPT Connecting as streams administrator to hub database
CONNECT &strm_adm_hub/&strm_adm_pwd_hub@&hub
PROMPT
PROMPT Create Rules for propagating LCRs from hub to spoke1
BEGIN
-- create ruleset
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => '&strm_adm_hub..prop_spoke1_ruleset',
evaluation_context => 'sys.streams$_evaluation_context');
-- create DML rule for propagating LCRs to spoke1 database
-- propagate LCRs with tag not equal to 1 because bit 1
-- identifies spoke1 database i.e. LCR has come
-- from spoke1 database
DBMS_RULE_ADM.CREATE_RULE (
rule_name => 'prop_spoke1_rule1',
condition => ':dml.get_object_owner() = ''STRMUSER'' AND '||
':dml.get_tag()!=HEXTORAW(''1'') AND '||
':dml.get_source_database_name()='''||:hubdb ||'''');
-- create DDL rule for propagating LCRs to spoke1 database
-- propagate LCRs with tag not equal to 1 because bit 1 identifies
-- spoke1 database i.e. LCR has come
-- from spoke1 database
DBMS_RULE_ADM.CREATE_RULE (
rule_name => 'prop_spoke1_rule2',
condition => ':ddl.get_object_owner() = ''STRMUSER'' AND '||
':ddl.get_tag()!=HEXTORAW(''1'') AND '||
':ddl.get_source_database_name()='''||:hubdb ||'''');
-- Add rule to ruleset
DBMS_RULE_ADM.ADD_RULE (
rule_set_name => '&strm_adm_hub..prop_spoke1_ruleset',
rule_name => 'prop_spoke1_rule1' );
DBMS_RULE_ADM.ADD_RULE (
rule_set_name => '&strm_adm_hub..prop_spoke1_ruleset',
rule_name => 'prop_spoke1_rule2' );
END;
/
PROMPT Creating propagation process to propagate changes from hub to spoke1
using above rules
-- create propagation process at hub using above ruleset
BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION
(
propagation_name => 'hub_to_spoke1_prop' ,
source_queue => '&strm_adm_hub..strmuser_queue' ,
destination_queue => '&strm_adm_spk1..strmuser_queue' ,
destination_dblink => :spk1,
rule_set_name => '&strm_adm_hub..prop_spoke1_ruleset'
) ;
END ;
/
PROMPT Adding propagation rules to propagate DML and DDL changes from hub to spoke1
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'hub_to_spoke1_prop',
source_queue_name => '&strm_adm_hub..strmuser_queue',
destination_queue_name => '&strm_adm_spk1..strmuser_queue@'||:spk1,
include_dml => true,
include_ddl => true,
source_database => :hubdb);
END;
/
PROMPT Create Rules and Ruleset for propagating LCRs from hub to spoke2
-- Create Rules for propagation to spoke2 database
BEGIN
-- create a ruleset
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => '&strm_adm_hub..prop_spoke2_ruleset',
evaluation_context => 'sys.streams$_evaluation_context');
-- create DML rule for propagating LCRs to spoke2 database
-- propagate LCRs with tag not equal to 2 because bit 2 identifies
-- spoke2 database i.e. LCR has come
-- from spoke2 database
DBMS_RULE_ADM.CREATE_RULE (
rule_name => 'prop_spoke2_rule1',
condition => ':dml.get_object_owner() = ''STRMUSER'' AND '||
':dml.get_tag()!=HEXTORAW(''2'') AND '||
':dml.get_source_database_name() = '''||:hubdb||'''');
-- create DDL rule for propagating LCRs to spoke2 database
-- propagate LCRs with tag not equal to 2 because bit 2 identifies
-- spoke2 database i.e. LCR has come
-- from spoke2 database
DBMS_RULE_ADM.CREATE_RULE (
rule_name => 'prop_spoke2_rule2',
condition => ':ddl.get_object_owner() = ''STRMUSER'' AND '||
':ddl.get_tag()!=HEXTORAW(''2'') AND '||
':ddl.get_source_database_name() = '''||:hubdb||'''');
-- Add rules to ruleset
DBMS_RULE_ADM.ADD_RULE (
rule_set_name => '&strm_adm_hub..prop_spoke2_ruleset',
rule_name => 'prop_spoke2_rule1' );
DBMS_RULE_ADM.ADD_RULE (
rule_set_name => '&strm_adm_hub..prop_spoke2_ruleset',
rule_name => 'prop_spoke2_rule2' );
END;
/
PROMPT Creating propagation process to propagate changes from hub to spoke2
using above ruleset
BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION
(
propagation_name => 'hub_to_spoke2_prop' ,
source_queue => '&strm_adm_hub..strmuser_queue' ,
destination_queue => '&strm_adm_spk2..strmuser_queue' ,
destination_dblink => :spk2,
rule_set_name => '&strm_adm_hub..prop_spoke2_ruleset'
) ;
END ;
/
PROMPT Adding propagation rules to propagate DML and DDL changes from hub to spoke2
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'hub_to_spoke2_prop',
source_queue_name => '&strm_adm_hub..strmuser_queue',
destination_queue_name => '&strm_adm_spk2..strmuser_queue@'||:spk2,
include_dml => true,
include_ddl => true,
source_database => :hubdb);
END;
/
PROMPT Connecting as streams administrator to spoke1 database
CONNECT &strm_adm_spk1/&strm_adm_pwd_spk1@&spoke1
-- Create propagation process for propagation LCRs from spoke1 to hub database
PROMPT Creating propagation process for propagating LCRs from spoke1 to hub
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'spoke1_to_hub_prop',
source_queue_name => '&strm_adm_spk1..strmuser_queue',
destination_queue_name => '&strm_adm_hub..strmuser_queue@'||:hubdb,
include_dml => true,
include_ddl => true,
source_database => :spk1);
END;
/
PROMPT Connecting as streams administrator to spoke2 database
CONNECT &strm_adm_spk2/&strm_adm_pwd_spk2@&spoke2
-- Create propagation process for propagation LCRs from spoke2 to hub database
PROMPT Creating propagation process for propagating LCRs from spoke2 to hub
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'strmuser',
streams_name => 'spoke2_to_hub_prop',
source_queue_name => '&strm_adm_spk2..strmuser_queue',
destination_queue_name => '&strm_adm_hub..strmuser_queue@'||:hubdb,
include_dml => true,
include_ddl => true,
source_database => :spk2);
END;
/
PROMPT Add supplemental logging for all tables
PROMPT Connecting as strmuser to hub database
CONNECT strmuser/strmuser@&hub
PROMPT
-- Add supplemental logging on all tables in strmuser schema
PROMPT Adding supplemental logging for table tabone on hub database
ALTER TABLE tabone ADD SUPPLEMENTAL LOG GROUP tabone_log_group ( name,id) ALWAYS;
PROMPT Connecting as strmuser to spoke1 database
CONNECT strmuser/strmuser@&spoke1
PROMPT
PROMPT Adding supplemental logging for table tabone on spoke1 database
ALTER TABLE tabone ADD SUPPLEMENTAL LOG GROUP tabone_log_group ( name,id) ALWAYS;
PROMPT Connecting as strmuser to spoke2 database
CONNECT strmuser/strmuser@&spoke2
PROMPT
-- Add supplemental logging on all tables in strmuser schema
PROMPT Adding supplemental logging for table tabone on spoke2 database
ALTER TABLE tabone ADD SUPPLEMENTAL LOG GROUP tabone_log_group ( name,id) ALWAYS;
PROMPT Connecting as streams admin to hub database
CONNECT &strm_adm_hub/&strm_adm_pwd_hub@&hub
PROMPT
PROMPT Creating capture process to capture changes in strmuser
schema in hub database
-- create capture process at hub to capture changes in strmuser schema.
-- set include_tagged_lcr to true so that all the changes with redo entry
-- having NULL or non NULL tags are captured.
-- If this parameter is false, only changes having NULL value for
--redo entry tag are captured.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'capture',
streams_name => 'capture_hub',
queue_name => '&strm_adm_hub..strmuser_queue',
include_tagged_lcr => TRUE,
include_dml => true,
include_ddl => true,
source_database => :hubdb );
END;
/
-- Create Apply process at hub for applying LCRs propagated from spoke 1
PROMPT Creating Apply process at hub for applying LCRs propagated from spoke 1
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_spoke1',
queue_name => '&strm_adm_hub..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :spk1 );
END;
/
-- Alter the apply process to set redo tags to '1' after applying LCRs.
PROMPT Altering the apply process to set redo tags to '1' after
applying LCRs propagated from spoke 1.
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name =>'apply_spoke1',
apply_tag => HEXTORAW('1'));
END;
/
-- Create Apply process at hub for applying LCRs propagated from spoke2
PROMPT Creating Apply process at hub for applying LCRs propagated from spoke2
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_spoke2',
queue_name => '&strm_adm_hub..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :spk2 );
END;
/
-- Alter the apply process to set redo tags to '2' after applying LCRs.
PROMPT Altering the apply process to set redo tags to '2' after applying LCRs
propagated from spoke 2.
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name =>'apply_spoke2',
apply_tag => HEXTORAW('2'));
END;
/
---------------------------------------------------------------------
-- Setup spoke1
-- Set include_tagged_lcr to false for capture and
-- true for apply
---------------------------------------------------------------------
PROMPT Connecting as streams administrator to spoke1 database
CONNECT &strm_adm_spk1/&strm_adm_pwd_spk1@&spoke1
PROMPT
PROMPT Creating capture process to capture changes in strmuser schema
-- create capture process at spoke1. include_tagged_lcr is left to default false
-- value so that DML changes with redo tag having NULL values only are captured.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'capture',
streams_name => 'capture_spoke1',
queue_name => '&strm_adm_spk1..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :spk1 );
END;
/
PROMPT Creating apply process to apply LCRs propagated from hub database
-- create apply process at spoke 1
-- set include_tagged_lcr to true. Redo with value of NULL and non NULL both are applied.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_hub',
queue_name => '&strm_adm_spk1..strmuser_queue',
include_tagged_lcr => true,
include_dml => true,
include_ddl => true,
source_database => :hubdb );
END;
/
---------------------------------------------------------------------
-- Setup spoke2
-- Set include_tagged_lcr to false for capture and
-- true for apply
---------------------------------------------------------------------
PROMPT Connecting as streams administrator to spoke2 database
CONNECT &strm_adm_spk2/&strm_adm_pwd_spk2@&spoke2
PROMPT Creating capture process to capture changes in strmuser schema
-- create capture process for strmuser schema at spoke 2
-- include_tagged_lcr is left to default false value so that only DML
-- changes with redo tag having NULL values are captured.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'capture',
streams_name => 'capture_spoke2',
queue_name => '&strm_adm_spk2..strmuser_queue',
include_dml => true,
include_ddl => true,
source_database => :spk2 );
END;
/
PROMPT Creating apply process at spoke 2 to apply LCRs propagated from hub database
-- create apply process at spoke 2
-- set include_tagged_lcr to true. Redo with value of NULL and non NULL both are applied.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'strmuser',
streams_type => 'apply',
streams_name => 'apply_hub',
queue_name => '&strm_adm_spk2..strmuser_queue',
include_tagged_lcr => true,
include_dml => true,
include_ddl => true,
source_database => :hubdb);
END;
/
-- Set instantiation SCN for schema and tables to be replicated on all sites
PROMPT Setting instantiation SCN for strmuser schema and table tabone on all databases
PROMPT Connecting as streams administrator to hub database
CONNECT &strm_adm_hub/&strm_adm_pwd_hub@&hub
PROMPT
PROMPT Getting the current SCN of hub database
EXEC :scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
PROMPT Connecting as streams administrator to spoke1 database
CONNECT &strm_adm_spk1/&strm_adm_pwd_spk1@&spoke1
PROMPT
PROMPT Setting instatiation SCN for strmuser schema at spoke1 to current SCN of hub database
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN (source_schema_name =>'strmuser',
source_database_name =>:hubdb,
instantiation_scn =>:scn);
END;
/
PROMPT Setting instatiation SCN for tabone at spoke1 to current SCN of hub database
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (source_object_name =>'strmuser.tabone',
source_database_name =>:hubdb,
instantiation_scn =>:scn);
END;
/
PROMPT Connecting as streams administrator to spoke2 database
CONNECT &strm_adm_spk2/&strm_adm_pwd_spk2@&spoke2
PROMPT
PROMPT Setting instatiation SCN for strmuser schema at spoke2 to current SCN of hub database
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN (source_schema_name =>'strmuser',
source_database_name =>:hubdb,
instantiation_scn =>:scn);
END;
/
PROMPT Setting instatiation SCN for tabone at spoke2 to current SCN of hub database
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (source_object_name =>'strmuser.tabone',
source_database_name =>:hubdb ,
instantiation_scn =>:scn);
END;
/
PROMPT Connecting as streams administrator to spoke1 database
CONNECT &strm_adm_spk1/&strm_adm_pwd_spk1@&spoke1
PROMPT
PROMPT Getting the current SCN of spoke1 database
EXEC :scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
PROMPT Connecting as streams administrator to hub database
CONNECT &strm_adm_hub/&strm_adm_pwd_hub@&hub
PROMPT
PROMPT Setting instatiation SCN for strmuser schema at hub to current SCN of spoke1 database
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN (source_schema_name =>'strmuser',
source_database_name =>:spk1,
instantiation_scn =>:scn);
END;
/
PROMPT Setting instatiation SCN for tabone at hub to current SCN of spoke1 database
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (source_object_name =>'strmuser.tabone',
source_database_name =>:spk1,
instantiation_scn =>:scn);
END;
/
PROMPT Connecting as streams administrator to spoke2 database
CONNECT &strm_adm_spk2/&strm_adm_pwd_spk2@&spoke2
PROMPT
PROMPT Getting the current SCN of spoke2 database
EXEC :scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
PROMPT Connecting as streams administrator to hub database
CONNECT &strm_adm_hub/&strm_adm_pwd_hub@&hub
PROMPT
PROMPT Setting instatiation SCN for strmuser schema at hub to current SCN of spoke2 database
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN (source_schema_name =>'strmuser',
source_database_name =>:spk2,
instantiation_scn =>:scn);
END;
/
PROMPT Setting instatiation SCN for tabone at hub to current SCN of spoke2 database
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (source_object_name =>'strmuser.tabone',
source_database_name =>:spk2,
instantiation_scn =>:scn);
END;
/
PROMPT Starting the apply process for spoke1 at hub database
EXEC DBMS_APPLY_ADM.START_APPLY('apply_spoke1');
PROMPT Starting the apply process for spoke2 at hub database
EXEC DBMS_APPLY_ADM.START_APPLY('apply_spoke2');
PROMPT Connecting as streams administrator to spoke1 database
CONNECT &strm_adm_spk1/&strm_adm_pwd_spk1@&spoke1
PROMPT
PROMPT Starting the apply process for hub at spoke1 database
EXEC DBMS_APPLY_ADM.START_APPLY('apply_hub');
PROMPT Connecting as streams administrator to spoke2 database
CONNECT &strm_adm_spk2/&strm_adm_pwd_spk2@&spoke2
PROMPT
PROMPT Starting the apply process for hub at spoke2 database
EXEC DBMS_APPLY_ADM.START_APPLY('apply_hub');
PROMPT Starting the capture process at spoke 2 database
EXEC DBMS_CAPTURE_ADM.START_CAPTURE ('capture_spoke2');
PROMPT Connecting as streams administrator to spoke1 database
CONNECT &strm_adm_spk1/&strm_adm_pwd_spk1@&spoke1
PROMPT
PROMPT Starting the capture process at spoke1 database
EXEC DBMS_CAPTURE_ADM.START_CAPTURE ('capture_spoke1');
PROMPT Connecting as streams administrator to hub database
CONNECT &strm_adm_hub/&strm_adm_pwd_hub@&hub
PROMPT
PROMPT Starting the capture process at hub database
EXEC DBMS_CAPTURE_ADM.START_CAPTURE ( 'capture_hub' );
PROMPT End of script
SPOOL OFF
|
Bookmark
