Oracle9i Streams Release 2 (9.2) Part Number A96571-01 |
|
This chapter illustrates some of the replication environments that can be constructed using Streams.
This chapter contains these topics:
This example illustrates using Streams to replicate data between four databases. The environment is heterogeneous because three of the databases are Oracle databases and one is a Sybase database. DML and DDL changes made to tables in the hr
schema at the dbs1.net
Oracle database are captured and propagated to the other two Oracle databases. Only DML changes are captured and propagated to the dbs4.net
database, because an apply process cannot apply DDL changes to a non-Oracle database. Changes to the hr
schema occur only at dbs1.net
. The hr
schema is read-only at the other databases in the environment.
This example contains these parts:
Figure 19-1 provides an overview of the environment.
As illustrated in Figure 19-1, dbs1.net
contains the following tables in the hr
schema:
This example uses directed networks, which means that captured changes at a source database are propagated to another database through one or more intermediate databases. Here, the dbs1.net
database propagates changes to the dbs3.net
database through the intermediate database dbs2.net
. Also, the dbs1.net
database propagates changes to the dbs2.net
database, which applies the changes directly to the dbs4.net
database through a gateway.
Some of the databases in the environment do not have certain tables. If the database is not an intermediate database for a table and the database does not contain the table, then changes to the table do not need to be propagated to that database. For example, the departments
, employees
, job_history
, and jobs
tables do not exist at dbs3.net
. Therefore, dbs2.net
does not propagate changes to these tables to dbs3.net
.
In this example, Streams is used to perform the following series of actions:
hr
schema and enqueues them into a queue at the dbs1.net
database. In this example, changes to only four of the seven tables are propagated to destination databases, but in the example that illustrates "Adding Objects to an Existing Streams Replication Environment", the remaining tables in the hr
schema are added to a destination database.dbs1.net
database propagates these changes in the form of messages to a queue at dbs2.net
.dbs2.net
, DML changes to the jobs
table are transformed into DML changes for the assignments
table (which is a direct mapping of jobs
) and then applied. Changes to other tables in the hr
schema are not applied at dbs2.net
.dbs3.net
receives changes from the queue at dbs2.net
that originated in countries
, locations
, and regions
tables at dbs1.net
, these changes are propagated from dbs2.net
to dbs3.net
. This configuration is an example of directed networks.dbs3.net
applies changes to the countries
, locations
, and regions
tables.dbs4.net
, a Sybase database, receives changes from the queue at dbs2.net
to the jobs
table that originated at dbs1.net
, these changes are applied remotely from dbs2.net
using the dbs4.net
database link through a gateway. This configuration is an example of heterogeneous support.The following prerequisites must be completed before you begin the example in this section.
GLOBAL_NAMES
: This parameter must be set to true
at each database that is participating in your Streams environment.JOB_QUEUE_PROCESSES
: This parameter must be set to at least 2
at each database that is propagating events in your Streams environment. It should be set to the same value as the maximum number of jobs that can run simultaneously plus one. In this example, dbs1.net
and dbs2.net
propagate events. So, JOB_QUEUE_PROCESSES
must be set to at least 2
at these databases.COMPATIBLE
: This parameter must be set to 9.2.0
or higher.LOG_PARALLELISM
: This parameter must be set to 1
at each database that captures events. In this example, this parameter must be set to 1
at dbs1.net
.
See Also:
"Setting Initialization Parameters Relevant to Streams" for information about other initialization parameters that are important in a Streams environment |
ARCHIVELOG
mode. In this example, changes are captured at dbs1.net
, and so dbs1.net
must be running in ARCHIVELOG
mode.
See Also:
Oracle9i Database Administrator's Guide for information about running a database in |
dbs2.net
to communicate with the Sybase database dbs4.net
.
dbs4.net
, set up the hr
user and create the jobs
table. The table shape should match the jobs
table on dbs1.net
.
dbs4.net
, add data to the jobs
table to make it consistent with the jobs
table at the Oracle database dbs1.net
. Record the SCN that corresponds to the point in time when the data was copied from the dbs1.net
database to the dbs4.net
database.
strmadmin
) at each database and prompts you for the tablespace you want to use for this user's data. Before you start this example, either create a new tablespace or identify an existing tablespace for the Streams administrator to use at each database. The Streams administrator should not use the SYSTEM
tablespace.Complete the following steps to set up users and create queues and database links for a Streams replication environment that includes three Oracle databases and one Sybase database.
/************************* BEGINNING OF SCRIPT ******************************
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL streams_setup_single.out /*
Connect to dbs1.net
as the hr
user.
*/ CONNECT hr/hr@dbs1.net /*
Convert the hr.countries
table from an index-organized table to a regular table. Currently, the capture process cannot capture changes to index-organized tables.
*/ ALTER TABLE countries RENAME TO countries_orig; CREATE TABLE hr.countries( country_id CHAR(2) CONSTRAINT country_id_nn_noiot NOT NULL, country_name VARCHAR2(40), region_id NUMBER, CONSTRAINT country_c_id_pk_noiot PRIMARY KEY (country_id)); ALTER TABLE hr.countries ADD (CONSTRAINT countr_reg_fk_noiot FOREIGN KEY (region_id) REFERENCES regions(region_id)) ; INSERT INTO COUNTRIES (SELECT * FROM hr.countries_orig); ALTER TABLE locations DROP CONSTRAINT loc_c_id_fk; ALTER TABLE locations ADD (CONSTRAINT loc_c_id_fk FOREIGN KEY (country_id) REFERENCES countries(country_id)); /*
Connect to dbs1.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@dbs1.net AS SYSDBA /*
Create the Streams administrator named strmadmin
and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You may choose a different name for this user.
*/ GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; ACCEPT streams_tbs PROMPT 'Enter the tablespace for the Streams administrator on dbs1.net: ' ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs QUOTA UNLIMITED ON &streams_tbs; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / /*
Connect as the Streams administrator at the database where you want to capture changes. In this example, that database is dbs1.net
.
*/ CONNECT strmadmin/strmadminpw@dbs1.net /*
Run the SET_UP_QUEUE
procedure to create a queue named streams_queue
at dbs1.net
. This queue will function as the Streams queue by holding the captured changes that will be propagated to other databases.
Running the SET_UP_QUEUE
procedure performs the following actions:
streams_queue_table
. This queue table is owned by the Streams administrator (strmadmin
) and uses the default storage of this user.streams_queue
owned by the Streams administrator (strmadmin
).*/ EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); /*
Create the database link from the database where changes are captured to the database where changes are propagated. In this example, the database where changes are captured is dbs1.net
, and these changes are propagated to dbs2.net
.
*/ CREATE DATABASE LINK dbs2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'dbs2.net'; /*
Connect to dbs2.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@dbs2.net AS SYSDBA /*
Create the Streams administrator named strmadmin
and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You may choose a different name for this user.
*/ GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; ACCEPT streams_tbs PROMPT 'Enter the tablespace for the Streams administrator on dbs2.net: ' ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs QUOTA UNLIMITED ON &streams_tbs; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / /*
Connect as the Streams administrator at dbs2.net
.
*/ CONNECT strmadmin/strmadminpw@dbs2.net /*
Run the SET_UP_QUEUE
procedure to create a queue named streams_queue
at dbs2.net
. This queue will function as the Streams queue by holding the changes that will be applied at this database and the changes that will be propagated to other databases.
Running the SET_UP_QUEUE
procedure performs the following actions:
streams_queue_table
. This queue table is owned by the Streams administrator (strmadmin
) and uses the default storage of this user.streams_queue
owned by the Streams administrator (strmadmin
).*/ EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); /*
Create the database links to the databases where changes are propagated. In this example, database dbs2.net
propagates changes to dbs3.net
, which is another Oracle database, and to dbs4.net
, which is a Sybase database. Notice that the database link to the Sybase database connects to the owner of the tables, not to the Streams administrator. This database link can connect to any user at dbs4.net
that has privileges to change the hr.jobs
table at that database.
*/ CREATE DATABASE LINK dbs3.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'dbs3.net'; CREATE DATABASE LINK dbs4.net CONNECT TO "hr" IDENTIFIED BY "hrpass" USING 'dbs4.net'; /*
This example illustrates a transformation in which changes to the hr.jobs
table at dbs1.net
are transformed into changes to the hr.assignments
table at dbs2.net
. You must create the hr.assignments
table on dbs2.net
for the transformation portion of this example to work properly.
Connect as hr at dbs2.net
.
*/ CONNECT hr/hr@dbs2.net /*
Create the hr.assignments
table in the dbs2.net
database.
*/ CREATE TABLE hr.assignments AS SELECT * FROM hr.jobs; ALTER TABLE hr.assignments ADD PRIMARY KEY (job_id); /*
Connect to dbs3.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@dbs3.net AS SYSDBA /*
Create the Streams administrator named strmadmin
and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You may choose a different name for this user.
*/ GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; ACCEPT streams_tbs PROMPT 'Enter the tablespace for the Streams administrator on dbs3.net: ' ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs QUOTA UNLIMITED ON &streams_tbs; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / /*
Connect as the Streams administrator at dbs3.net
.
*/ CONNECT strmadmin/strmadminpw@dbs3.net /*
Run the SET_UP_QUEUE
procedure to create a queue named streams_queue
at dbs3.net
. This queue will function as the Streams queue by holding the changes that will be applied at this database.
Running the SET_UP_QUEUE
procedure performs the following actions:
streams_queue_table
. This queue table is owned by the Streams administrator (strmadmin
) and uses the default storage of this user.streams_queue
owned by the Streams administrator (strmadmin
).*/ EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); /*
This example illustrates instantiating tables in the hr
schema by exporting them from dbs1.net
and importing them into dbs3.net
. You must delete these tables at dbs3.net
for the instantiation portion of this example to work properly.
Connect as hr
at dbs3.net
.
*/ CONNECT hr/hr@dbs3.net /*
Drop all tables in the hr
schema in the dbs3.net
database.
*/ DROP TABLE hr.countries CASCADE CONSTRAINTS; DROP TABLE hr.departments CASCADE CONSTRAINTS; DROP TABLE hr.employees CASCADE CONSTRAINTS; DROP TABLE hr.job_history CASCADE CONSTRAINTS; DROP TABLE hr.jobs CASCADE CONSTRAINTS; DROP TABLE hr.locations CASCADE CONSTRAINTS; DROP TABLE hr.regions CASCADE CONSTRAINTS; /*
Check the streams_setup_single.out
spool file to ensure that all actions finished successfully after this script is completed.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
This example illustrates two ways to accomplish the replication of the tables in the hr
schema using Streams.
DBMS_STREAMS_ADM
package to create a capture process, propagation jobs, and apply processes, as well as the rule sets associated with these processes and jobs. Using the DBMS_STREAMS_ADM
package is the simplest way to configure a Streams environment.DBMS_CAPTURE_ADM
package to create a capture process, the DBMS_PROPAGATION_ADM
package to create propagation jobs, and the DBMS_APPLY_ADM
package to create apply processes. Also, this example uses the DBMS_RULES_ADM
package to create and populate the rule sets associated with these processes and jobs. Using these packages, instead of the DBMS_STREAMS_ADM
package, provides more configuration options and flexibility.
Complete the following steps to specify the capture, propagation, and apply definitions using primarily the DBMS_STEAMS_ADM
package.
/************************* BEGINNING OF SCRIPT ******************************
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL streams_share_schema1.out /*
By default, the LogMiner tables are in the SYSTEM
tablespace, but the SYSTEM
tablespace may not have enough space for these tables once a capture process starts to capture changes. Therefore, you must create an alternate tablespace for the LogMiner tables.
Connect to dbs1.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@dbs1.net AS SYSDBA /*
Create an alternate tablespace for the LogMiner tables.
*/ ACCEPT tspace_name DEFAULT 'logmnrts' PROMPT 'Enter the name of the tablespace (for example, logmnrts): ' ACCEPT db_file_directory DEFAULT '' PROMPT 'Enter the complete path to the datafile directory (for example, /usr/oracle/dbs): ' ACCEPT db_file_name DEFAULT 'logmnrts.dbf' PROMPT 'Enter the name of the datafile (for example, logmnrts.dbf): ' CREATE TABLESPACE &tspace_name DATAFILE '&db_file_directory/&db_file_name' SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('&tspace_name'); /*
Supplemental logging places additional information in the redo log for changes made to tables. The apply process needs this extra information to perform certain operations, such as unique row identification and conflict resolution. Because dbs1.net
is the only database where changes are captured in this environment, it is the only database where you must specify supplemental logging for the tables in the hr
schema.
Specify an unconditional supplemental log group for all primary key columns in the hr
schema.
*/ ALTER TABLE hr.countries ADD SUPPLEMENTAL LOG GROUP log_group_countries_pk (country_id) ALWAYS; ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP log_group_departments_pk (department_id) ALWAYS; ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG GROUP log_group_employees_pk (employee_id) ALWAYS; ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs_pk (job_id) ALWAYS; ALTER TABLE hr.job_history ADD SUPPLEMENTAL LOG GROUP log_group_job_history_pk (employee_id, start_date) ALWAYS; ALTER TABLE hr.locations ADD SUPPLEMENTAL LOG GROUP log_group_locations_pk (location_id) ALWAYS; ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG GROUP log_group_regions_pk (region_id) ALWAYS; ALTER SYSTEM ARCHIVE LOG CURRENT; /*
Connect to dbs1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs1.net /*
Configure and schedule propagation of DML and DDL changes in the hr
schema from the queue at dbs1.net
to the queue at dbs2.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'dbs1_to_dbs2', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs2.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / /*
Configure the capture process to capture changes to the entire hr
schema at dbs1.net
. This step specifies that changes to the tables in the specified schema are captured by the capture process and enqueued into the specified queue.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'capture', streams_name => 'capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true); END; / /*
In this example, the hr.jobs
table already exists at dbs2.net
and dbs4.net
. At dbs2.net
, this table is named assignments
, but it has the same shape and data as the jobs
table at dbs1.net
. Also, in this example, dbs4.net
is a Sybase database. All of the other tables in the Streams environment are instantiated at the other databases using Export/Import.
Because the hr.jobs
table already exists at dbs2.net
and dbs4.net
, this example uses the GET_SYSTEM_CHANGE_NUMBER
function in the DBMS_FLASHBACK
package at dbs1.net
to obtain the current SCN for the database. This SCN is used at dbs2.net
to run the SET_TABLE_INSTANTIATION_SCN
procedure in the DBMS_APPLY_ADM
package. Running this procedure twice sets the instantiation SCN for the hr.jobs
table at dbs2.net
and dbs4.net
.
The SET_TABLE_INSTANTIATION_SCN
procedure controls which LCRs for a table are ignored by an apply process and which LCRs for a table are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR.
In this example, both of the apply processes at dbs2.net
will apply transactions to the hr.jobs
table with SCNs that were committed after SCN obtained in this step.
*/ DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET( source_object_name => 'hr.jobs', source_database_name => 'dbs1.net', instantiation_scn => iscn); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET( source_object_name => 'hr.jobs', source_database_name => 'dbs1.net', instantiation_scn => iscn, apply_database_link => 'dbs4.net'); END; /
/*
Open a different window and export the tables at dbs1.net
that will be instantiated at dbs3.net
. Make sure you set the OBJECT_CONSISTENT
export parameter to y
when you run the export command. Also, make sure no DDL changes are made to the objects being exported during the export.
The following is an example export command:
exp userid=hr/hr FILE=hr_instant1.dmp TABLES=countries,locations,regions OBJECT_CONSISTENT=y
See Also:
Oracle9i Database Utilities for information about performing an export |
*/ PAUSE Press <RETURN> to continue when the export is complete in the other window that you opened. /*
Transfer the export dump file hr_instant1.dmp
to the destination database. In this example, the destination database is dbs3.net
.
You can use binary FTP or some other method to transfer the export dump file to the destination database. You may need to open a different window to transfer the file.
*/ PAUSE Press <RETURN> to continue after transferring the dump file. /*
In a different window, connect to the computer that runs the dbs3.net
database and import the export dump file hr_instant1.dmp
to instantiate the countries
, locations
, and regions
tables in the dbs3.net
database. You can use telnet or remote login to connect to the computer that runs dbs3.net
.
When you run the import command, make sure you set the STREAMS_INSTANTIATION
import parameter to y
. This parameter ensures that the import records export SCN information for each object imported.
The following is an example import command:
imp userid=hr/hr FILE=hr_instant1.dmp IGNORE=y FULL=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y
See Also:
Oracle9i Database Utilities for information about performing an import |
*/ PAUSE Press <RETURN> to continue after the import is complete at dbs3.net. /*
Connect to dbs3.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs3.net /*
Configure dbs3.net
to apply changes to the countries
table, locations
table, and regions
table.
*/ BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.countries', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.locations', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.regions', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / /*
In this example, the hr
user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr
already has the necessary privileges to change these database objects, and it is convenient to make hr
the apply user.
When the apply process was created in the previous step, the Streams administrator strmadmin
was specified as the apply user by default, because strmadmin
ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin
as the apply user, but then you must grant strmadmin
privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it may be more convenient to use the Streams administrator as the apply user.
*/ BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply', apply_user => 'hr'); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by the apply process
*/ DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / /*
Set the disable_on_error
parameter to n
so that the apply process will not be disabled if it encounters an error, and start the apply process at dbs3.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply'); END; / /*
Connect to dbs2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs2.net /*
Configure and schedule propagation from the queue at dbs2.net
to the queue at dbs3.net
. You must specify this propagation for each table that will apply changes at dbs3.net
. This configuration is an example of directed networks because the changes at dbs2.net
originated at dbs1.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.countries', streams_name => 'dbs2_to_dbs3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs3.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.locations', streams_name => 'dbs2_to_dbs3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs3.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.regions', streams_name => 'dbs2_to_dbs3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs3.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / /*
Connect to dbs2.net
as the hr
user.
*/ CONNECT hr/hr@dbs2.net /*
Create the transformation function that transforms row changes resulting from DML statements to the jobs
table from dbs1.net
into row changes to the assignments
table on dbs2.net
.
The following function transforms every row LCR for the jobs
table into a row LCR for the assignments
table.
*/ CREATE OR REPLACE FUNCTION hr.to_assignments_trans_dml( p_in_data in SYS.AnyData) RETURN SYS.AnyData IS out_data SYS.LCR$_ROW_RECORD; tc pls_integer; BEGIN -- Typecast AnyData to LCR$_ROW_RECORD tc := p_in_data.GetObject(out_data); IF out_data.get_object_name() = 'JOBS' THEN -- Transform the in_data into the out_data out_data.set_object_name('ASSIGNMENTS'); END IF; -- Convert to AnyData RETURN SYS.AnyData.ConvertObject(out_data); END; / /*
Connect to dbs2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs2.net /*
Configure dbs2.net
to apply changes to the assignments
table. Remember that the assignments
table receives changes from the jobs
table at dbs1.net
.
*/ DECLARE to_assignments_rulename_dml VARCHAR2(30); dummy_rule VARCHAR2(30); action_ctx_dml SYS.RE$NV_LIST; ac_name VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION'; BEGIN -- DML changes to the jobs table from dbs1.net are applied to the assignments -- table. The to_assignments_rulename_dml variable is an out parameter -- in this call. DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.jobs', -- jobs, not assignments, specified streams_type => 'apply', streams_name => 'apply_dbs2', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => false, source_database => 'dbs1.net', dml_rule_name => to_assignments_rulename_dml, ddl_rule_name => dummy_rule); -- Specify the name-value pair in the action context action_ctx_dml := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY()); action_ctx_dml.ADD_PAIR( ac_name, SYS.ANYDATA.CONVERTVARCHAR2('hr.to_assignments_trans_dml')); -- Modify the rule for jobs to use the transformation. DBMS_RULE_ADM.ALTER_RULE( rule_name => to_assignments_rulename_dml, action_context => action_ctx_dml); END; / /*
In this example, the hr
user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr
already has the necessary privileges to change these database objects, and it is convenient to make hr
the apply user.
When the apply process was created in the previous step, the Streams administrator strmadmin
was specified as the apply user by default, because strmadmin
ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin
as the apply user, but then you must grant strmadmin
privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it may be more convenient to use the Streams administrator as the apply user.
*/ BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply_dbs2', apply_user => 'hr'); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by the apply process
*/ DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY_DBS2'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / /*
Set the disable_on_error
parameter to n
so that the apply process will not be disabled if it encounters an error, and start the apply process for local apply at dbs2.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_dbs2', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_dbs2'); END; / /*
Configure the apply process for dbs4.net
, which is a Sybase database. The dbs2.net
database is acting as a gateway to dbs4.net
. Therefore, the apply process for dbs4.net
must be configured at dbs2.net
. The apply process cannot apply DDL changes to non-Oracle databases. Therefore, the include_ddl
parameter is set to false
when the ADD_TABLE_RULES
procedure is run.
*/ BEGIN DBMS_APPLY_ADM.CREATE_APPLY( queue_name => 'strmadmin.streams_queue', apply_name => 'apply_dbs4', apply_database_link => 'dbs4.net', apply_captured => true); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.jobs', streams_type => 'apply', streams_name => 'apply_dbs4', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => false, source_database => 'dbs1.net'); END; / /*
Set the disable_on_error
parameter to n
so that the apply process will not be disabled if it encounters an error, and start the remote apply for Sybase using database link dbs4.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_dbs4', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_dbs4'); END; / /*
Connect to dbs1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs1.net /*
Start the capture process at dbs1.net
.
*/ BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'capture'); END; / /*
Check the streams_share_schema1.out
spool file to ensure that all actions finished successfully after this script is completed.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
Complete the following steps to use a more flexible approach for specifying the capture, propagation, and apply definitions. This approach does not use the DBMS_STREAMS_ADM
package. Instead, it uses the following packages:
DBMS_CAPTURE_ADM
package to configure capture processesDBMS_PROPAGATION_ADM
package to configure propagation jobsDBMS_APPLY_ADM
package to configure apply processesDBMS_RULES_ADM
package to specify capture, propagation, and apply rules and rule sets
This example includes the following steps:
/************************* BEGINNING OF SCRIPT ******************************
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL streams_share_schema2.out /*
By default, the LogMiner tables are in the SYSTEM
tablespace, but the SYSTEM
tablespace may not have enough space for these tables once a capture process starts to capture changes. Therefore, you must create an alternate tablespace for the LogMiner tables.
Connect to dbs1.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@dbs1.net AS SYSDBA /*
Create an alternate tablespace for the LogMiner tables.
*/ ACCEPT tspace_name DEFAULT 'logmnrts' PROMPT 'Enter the name of the tablespace (for example, logmnrts): ' ACCEPT db_file_directory DEFAULT '' PROMPT 'Enter the complete path to the datafile directory (for example, /usr/oracle/dbs): ' ACCEPT db_file_name DEFAULT 'logmnrts.dbf' PROMPT 'Enter the name of the datafile (for example, logmnrts.dbf): ' CREATE TABLESPACE &tspace_name DATAFILE '&db_file_directory/&db_file_name' SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('&tspace_name'); /*
Supplemental logging places additional information in the redo log for changes made to tables. The apply process needs this extra information to perform certain operations, such as unique row identification and conflict resolution. Because dbs1.net
is the only database where changes are captured in this environment, it is the only database where you must specify supplemental logging for the tables in the hr
schema.
Specify an unconditional supplemental log group for all primary key columns in the hr
schema.
*/ ALTER TABLE hr.countries ADD SUPPLEMENTAL LOG GROUP log_group_countries_pk (country_id) ALWAYS; ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP log_group_departments_pk (department_id) ALWAYS; ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG GROUP log_group_employees_pk (employee_id) ALWAYS; ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs_pk (job_id) ALWAYS; ALTER TABLE hr.job_history ADD SUPPLEMENTAL LOG GROUP log_group_job_history_pk (employee_id, start_date) ALWAYS; ALTER TABLE hr.locations ADD SUPPLEMENTAL LOG GROUP log_group_locations_pk (location_id) ALWAYS; ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG GROUP log_group_regions_pk (region_id) ALWAYS; ALTER SYSTEM ARCHIVE LOG CURRENT; /*
Connect to dbs1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs1.net /*
Configure and schedule propagation from the queue at dbs1.net
to the queue at dbs2.net
. This configuration specifies that the propagation job propagates all changes to the hr
schema. You have the option of omitting the rule set specification, but then everything in the queue will be propagated, which may not be desired if, in the future, multiple capture processes will use the streams_queue
.
*/ BEGIN -- Create the rule set DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.propagation_dbs1_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); -- Create rules for all modifications to the hr schema DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_hr_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_hr_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Add rules to rule set DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_hr_dml', rule_set_name => 'strmadmin.propagation_dbs1_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_hr_ddl', rule_set_name => 'strmadmin.propagation_dbs1_rules'); -- Create the propagation job DBMS_PROPAGATION_ADM.CREATE_PROPAGATION( propagation_name => 'dbs1_to_dbs2', source_queue => 'strmadmin.streams_queue', destination_queue => 'strmadmin.streams_queue', destination_dblink => 'dbs2.net', rule_set_name => 'strmadmin.propagation_dbs1_rules'); END; / /*
Create a capture process and rules to capture the entire hr
schema at dbs1.net
.
*/ BEGIN -- Create the rule set DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.demo_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); -- Create rules that specify the entire hr schema DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.schema_hr_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.schema_hr_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Add the rules to the rule set DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.schema_hr_dml', rule_set_name => 'strmadmin.demo_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.schema_hr_ddl', rule_set_name => 'strmadmin.demo_rules'); -- Create a capture process that uses the rule set DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name => 'strmadmin.streams_queue', capture_name => 'capture', rule_set_name => 'strmadmin.demo_rules'); END; / /*
While still connected as the Streams administrator at dbs1.net
, prepare the hr
schema at dbs1.net
for instantiation at dbs3.net
. This step marks the lowest SCN of the tables in the schema for instantiation. SCNs subsequent to the lowest SCN can be used for instantiation.
Note: This step is not required in the "Simple Configuration for Sharing Data from a Single Database". In that example, when the |
*/ BEGIN DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION( schema_name => 'hr'); END; / /*
In this example, the hr.jobs
table already exists at dbs2.net
and dbs4.net
. At dbs2.net
, this table is named assignments
, but it has the same shape and data as the jobs
table at dbs1.net
. Also, in this example, dbs4.net
is a Sybase database. All of the other tables in the Streams environment are instantiated at the other databases using Export/Import.
Because the hr.jobs
table already exists at dbs2.net
and dbs4.net
, this example uses the GET_SYSTEM_CHANGE_NUMBER
function in the DBMS_FLASHBACK
package at dbs1.net
to obtain the current SCN for the database. This SCN is used at dbs2.net
to run the SET_TABLE_INSTANTIATION_SCN
procedure in the DBMS_APPLY_ADM
package. Running this procedure twice sets the instantiation SCN for the hr.jobs
table at dbs2.net
and dbs4.net
.
The SET_TABLE_INSTANTIATION_SCN
procedure controls which LCRs for a table are ignored by an apply process and which LCRs for a table are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR.
In this example, both of the apply processes at dbs2.net
will apply transactions to the hr.jobs
table with SCNs that were committed after SCN obtained in this step.
*/ DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET( source_object_name => 'hr.jobs', source_database_name => 'dbs1.net', instantiation_scn => iscn); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBS2.NET( source_object_name => 'hr.jobs', source_database_name => 'dbs1.net', instantiation_scn => iscn, apply_database_link => 'dbs4.net'); END; /
/*
Open a different window and export the tables at dbs1.net
that will be instantiated at dbs3.net
. Make sure you set the OBJECT_CONSISTENT
export parameter to y
when you run the export command. Also, make sure no DDL changes are made to the objects being exported during the export.
The following is an example export command:
exp userid=hr/hr FILE=hr_instant1.dmp TABLES=countries,locations,regions OBJECT_CONSISTENT=y
See Also:
Oracle9i Database Utilities for information about performing an export |
*/ PAUSE Press <RETURN> to continue when the export is complete in the other window that you opened. /*
Transfer the export dump file hr_instant1.dmp
to the destination database. In this example, the destination database is dbs3.net
.
You can use binary FTP or some other method to transfer the export dump file to the destination database. You may need to open a different window to transfer the file.
*/ PAUSE Press <RETURN> to continue after transferring the dump file. /*
In a different window, connect to the computer that runs the dbs3.net
database and import the export dump file hr_instant1.dmp
to instantiate the countries
, locations
, and regions
tables in the dbs3.net
database. You can use telnet or remote login to connect to the computer that runs dbs3.net
.
When you run the import command, make sure you set the STREAMS_INSTANTIATION
import parameter to y
. This parameter ensures that the import records export SCN information for each object imported.
The following is an example import command:
imp userid=hr/hr FILE=hr_instant1.dmp IGNORE=y FULL=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y
See Also:
Oracle9i Database Utilities for information about performing an import |
*/ PAUSE Press <RETURN> to continue after the import is complete at dbs3.net. /*
Connect to dbs3.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs3.net /*
Configure dbs3.net
to apply DML and DDL changes to the countries
table, locations
table, and regions
table.
*/ BEGIN -- Create the rule set DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.apply_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); -- Rules for hr.countries DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_countries_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''COUNTRIES'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_countries_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.get_object_name() = ''COUNTRIES'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Rules for hr.locations DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_locations_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''LOCATIONS'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_locations_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.get_object_name() = ''LOCATIONS'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Rules for hr.regions DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_regions_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''REGIONS'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_regions_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.get_object_name() = ''REGIONS'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Add rules to rule set DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_countries_dml', rule_set_name => 'strmadmin.apply_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_countries_ddl', rule_set_name => 'strmadmin.apply_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_locations_dml', rule_set_name => 'strmadmin.apply_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_locations_ddl', rule_set_name => 'strmadmin.apply_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_regions_dml', rule_set_name => 'strmadmin.apply_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_regions_ddl', rule_set_name => 'strmadmin.apply_rules'); -- Create the apply process DBMS_APPLY_ADM.CREATE_APPLY( queue_name => 'strmadmin.streams_queue', apply_name => 'apply', rule_set_name => 'strmadmin.apply_rules', apply_user => 'hr', apply_captured => true); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by the apply process
*/ BEGIN DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => 'strmadmin.apply_rules', grantee => 'hr'); END; / /*
Set the disable_on_error
parameter to n
so that the apply process will not be disabled if it encounters an error, and start the apply process at dbs3.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply'); END; / /*
Connect to dbs2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs2.net /*
Configure and schedule propagation from the queue at dbs2.net
to the queue at dbs3.net
. This configuration is an example of directed networks because the changes at dbs2.net
originated at dbs1.net
.
*/ BEGIN -- Create the rule set DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.propagation_dbs3_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); -- Create rules for all modifications to the countries table DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_countries_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''COUNTRIES'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_countries_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.get_object_name() = ''COUNTRIES'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Create rules for all modifications to the locations table DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_locations_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''LOCATIONS'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_locations_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.get_object_name() = ''LOCATIONS'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Create rules for all modifications to the regions table DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_regions_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''REGIONS'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_regions_ddl', condition => ' :ddl.get_object_owner() = ''HR'' AND ' || ' :ddl.get_object_name() = ''REGIONS'' AND ' || ' :ddl.is_null_tag() = ''Y'' AND ' || ' :ddl.get_source_database_name() = ''DBS1.NET'' '); -- Add rules to rule set DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_countries_dml', rule_set_name => 'strmadmin.propagation_dbs3_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_countries_ddl', rule_set_name => 'strmadmin.propagation_dbs3_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_locations_dml', rule_set_name => 'strmadmin.propagation_dbs3_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_locations_ddl', rule_set_name => 'strmadmin.propagation_dbs3_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_regions_dml', rule_set_name => 'strmadmin.propagation_dbs3_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_regions_ddl', rule_set_name => 'strmadmin.propagation_dbs3_rules'); -- Create the propagation job DBMS_PROPAGATION_ADM.CREATE_PROPAGATION( propagation_name => 'dbs2_to_dbs3', source_queue => 'strmadmin.streams_queue', destination_queue => 'strmadmin.streams_queue', destination_dblink => 'dbs3.net', rule_set_name => 'strmadmin.propagation_dbs3_rules'); END; / /*
Connect to dbs2.net
as the hr
user.
*/ CONNECT hr/hr@dbs2.net /*
Create the transformation function that transforms row changes resulting from DML statements to the jobs
table from dbs1.net
into row changes to the assignments
table on dbs2.net
.
The following function transforms every row LCR for the jobs
table into a row LCR for the assignments
table.
*/ CREATE OR REPLACE FUNCTION hr.to_assignments_trans_dml( p_in_data in SYS.AnyData) RETURN SYS.AnyData IS out_data SYS.LCR$_ROW_RECORD; tc pls_integer; BEGIN -- Typecast AnyData to LCR$_ROW_RECORD tc := p_in_data.GetObject(out_data); IF out_data.get_object_name() = 'JOBS' THEN -- Transform the in_data into the out_data out_data.set_object_name('ASSIGNMENTS'); END IF; -- Convert to AnyData RETURN SYS.AnyData.ConvertObject(out_data); END; / /*
Connect to dbs2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs2.net /*
Configure dbs2.net
to apply changes to the local assignments
table. Remember that the assignments
table receives changes from the jobs
table at dbs1.net
.
*/ DECLARE action_ctx_dml SYS.RE$NV_LIST; action_ctx_ddl SYS.RE$NV_LIST; ac_name VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION'; BEGIN -- Specify the name-value pair in the action context action_ctx_dml := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY()); action_ctx_dml.ADD_PAIR( ac_name, SYS.ANYDATA.CONVERTVARCHAR2('hr.to_assignments_trans_dml')); -- Create the rule set strmadmin.apply_rules DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.apply_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); -- Create a rule that transforms all DML changes to the jobs table into -- DML changes for assignments table DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_jobs_dml', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''JOBS'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' ', action_context => action_ctx_dml); -- Add the rule to the rule set DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_jobs_dml', rule_set_name => 'strmadmin.apply_rules'); -- Create an apply process that uses the rule set DBMS_APPLY_ADM.CREATE_APPLY( queue_name => 'strmadmin.streams_queue', apply_name => 'apply_dbs2', rule_set_name => 'strmadmin.apply_rules', apply_user => 'hr', apply_captured => true); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by the apply process
*/ BEGIN DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => 'strmadmin.apply_rules', grantee => 'hr'); END; / /*
Set the disable_on_error
parameter to n
so that the apply process will not be disabled if it encounters an error, and start the apply process for local apply at dbs2.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_dbs2', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_dbs2'); END; / /*
Configure dbs2.net
to apply DML changes to the jobs
table at dbs4.net
, which is a Sybase database. Remember that these changes originated at dbs1.net
.
*/ BEGIN -- Create the rule set DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.apply_dbs4_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); -- Create rule strmadmin.all_jobs_remote for all modifications -- to the jobs table DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.all_jobs_remote', condition => ' :dml.get_object_owner() = ''HR'' AND ' || ' :dml.get_object_name() = ''JOBS'' AND ' || ' :dml.is_null_tag() = ''Y'' AND ' || ' :dml.get_source_database_name() = ''DBS1.NET'' '); -- Add the rule to the rule set DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.all_jobs_remote', rule_set_name => 'strmadmin.apply_dbs4_rules'); -- Create an apply process that uses the rule set DBMS_APPLY_ADM.CREATE_APPLY( queue_name => 'strmadmin.streams_queue', apply_name => 'apply_dbs4', rule_set_name => 'strmadmin.apply_dbs4_rules', apply_database_link => 'dbs4.net', apply_captured => true); END; / /*
Set the disable_on_error
parameter to n
so that the apply process will not be disabled if it encounters an error, and start the remote apply for Sybase using database link dbs4.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_dbs4', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_dbs4'); END; / /*
Connect to dbs1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs1.net /*
Start the capture process at dbs1.net
.
*/ BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'capture'); END; / /*
Check the streams_share_schema2.out
spool file to ensure that all actions finished successfully after this script is completed.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
This example extends the Streams environment configured in the previous sections by adding replicated objects to an existing database. To complete this example, you must have completed the tasks in one of the previous examples in this chapter.
This example will add the following tables to the hr
schema in the dbs3.net
database:
When you complete this example, Streams processes changes to these tables with the following series of actions:
dbs1.net
and enqueues them at dbs1.net
.dbs1.net
to the queue at dbs2.net
.dbs2.net
to the queue at dbs3.net
.dbs3.net
applies the changes at dbs3.net
.When you complete this example, the hr
schema at the dbs3.net
database will have all of its original tables, because the countries
, locations
, and regions
tables were instantiated at dbs3.net
in the previous section.
Figure 19-2 provides an overview of the environment with the added tables.
Complete the following steps to replicate these tables to the dbs3.net
database.
/************************* BEGINNING OF SCRIPT ******************************
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL streams_addobjs.out /*
Until you finish adding objects to dbs3.net
, you must ensure that the apply process that will apply changes for the added objects does not try to apply changes for these objects. You can do this by stopping the capture process at the source database. Or, you can do this by stopping propagation of changes from dbs2.net
to dbs3.net
. Yet another alternative is to stop the apply process at dbs3.net
. This example stops the apply process at dbs3.net
.
Connect to dbs3.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs3.net /*
Stop the apply process at dbs3.net
.
*/ BEGIN DBMS_APPLY_ADM.STOP_APPLY( apply_name => 'apply'); END; / /*
Configure the apply process at dbs3.net
to apply changes to the tables you are adding.
*/ BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.departments', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.employees', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.job_history', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.jobs', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / /*
Connect to dbs2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs2.net /*
Add the tables to the rules for propagation from the queue at dbs2.net
to the queue at dbs3.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.departments', streams_name => 'dbs2_to_dbs3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs3.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.employees', streams_name => 'dbs2_to_dbs3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs3.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.job_history', streams_name => 'dbs2_to_dbs3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs3.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.jobs', streams_name => 'dbs2_to_dbs3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs3.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / /*
Connect to dbs1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs1.net /*
Prepare the tables for instantiation. These tables will be instantiated at dbs3.net
. This step marks the lowest SCN of the tables for instantiation. SCNs subsequent to the lowest SCN can be used for instantiation. Also, this preparation is necessary so that the Streams data dictionary for the relevant propagation jobs and the apply process at dbs3.net
contain information about these tables.
*/ BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name => 'hr.departments'); END; / BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name => 'hr.employees'); END; / BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name => 'hr.job_history'); END; / BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name => 'hr.jobs'); END; / /*
Open a different window and export the tables at dbs1.net
that will be instantiated at dbs3.net
. Make sure you set the OBJECT_CONSISTENT
export parameter to y
when you run the export command. Also, make sure no DDL changes are made to the objects being exported during the export.
The following is an example export command:
exp userid=hr/hr FILE=hr_instant2.dmp TABLES=departments,employees,job_history,jobs OBJECT_CONSISTENT=y
See Also:
Oracle9i Database Utilities for information about performing an export |
*/ PAUSE Press <RETURN> to continue when the export is complete in the other window that you opened. /*
Transfer the export dump file hr_instant2.dmp
to the destination database. In this example, the destination database is dbs3.net
.
You can use binary FTP or some other method to transfer the export dump file to the destination database. You may need to open a different window to transfer the file.
*/ PAUSE Press <RETURN> to continue after transferring the dump file. /*
In a different window, connect to the computer that runs the dbs3.net
database and import the export dump file hr_instant2.dmp
to instantiate the tables in the dbs3.net
database. You can use telnet or remote login to connect to the computer that runs dbs3.net
.
When you run the import command, make sure you set the STREAMS_INSTANTIATION
import parameter to y
. This parameter ensures that the import records export SCN information for each object imported.
The following is an example import command:
imp userid=hr/hr FILE=hr_instant2.dmp IGNORE=y FULL=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y
See Also:
Oracle9i Database Utilities for information about performing an import |
*/ PAUSE Press <RETURN> to continue after the import is complete at dbs3.net. /*
Start the apply process at dbs3.net
. This apply process was stopped in Step 2.
Connect to dbs3.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs3.net /*
Start the apply process at dbs3.net
.
*/ BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply'); END; / /*
Check the streams_addobjs.out
spool file to ensure that all actions finished successfully after this script is completed.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
This example extends the Streams environment configured in the previous sections by adding an additional database to the existing configuration. In this example, an existing Oracle database named dbs5.net
is added to receive changes to the entire hr
schema from the queue at dbs2.net
. Figure 19-3 provides an overview of the environment with the added database.
To complete this example, you must meet the following prerequisites:
dbs5.net
database must exist.dbs2.net
and dbs5.net
databases must be able to communicate with each other through Oracle Net.strmadmin
) at each database and prompts you for the tablespace you want to use for this user's data. Before you start this example, either create a new tablespace or identify an existing tablespace for the Streams administrator to use at each database. The Streams administrator should not use the SYSTEM
tablespace.Complete the following steps to add dbs5.net
to the Streams environment.
/************************* BEGINNING OF SCRIPT ******************************
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL streams_adddb.out /*
This example illustrates instantiating the tables in the hr
schema by exporting them from dbs1.net
and importing them into dbs5.net
. You must delete these tables at dbs5.net
for the instantiation portion of this example to work properly.
Connect as hr
at dbs5.net
.
*/ CONNECT hr/hr@dbs5.net /*
Drop all tables in the hr
schema in the dbs5.net
database.
*/ DROP TABLE hr.countries CASCADE CONSTRAINTS; DROP TABLE hr.departments CASCADE CONSTRAINTS; DROP TABLE hr.employees CASCADE CONSTRAINTS; DROP TABLE hr.job_history CASCADE CONSTRAINTS; DROP TABLE hr.jobs CASCADE CONSTRAINTS; DROP TABLE hr.locations CASCADE CONSTRAINTS; DROP TABLE hr.regions CASCADE CONSTRAINTS; /*
Connect to dbs5.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@dbs5.net AS SYSDBA /*
Create the Streams administrator named strmadmin
and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You may choose a different name for this user.
*/ GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; ACCEPT streams_tbs PROMPT 'Enter the tablespace for the Streams administrator on dbs5.net: ' ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs QUOTA UNLIMITED ON &streams_tbs; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / /*
Connect as the Streams administrator at the database you are adding. In this example, that database is dbs5.net
.
*/ CONNECT strmadmin/strmadminpw@dbs5.net /*
Run the SET_UP_QUEUE
procedure to create a queue named streams_queue
at dbs5.net
. This queue will function as the Streams queue by holding the changes that will be applied at this database.
Running the SET_UP_QUEUE
procedure performs the following actions:
streams_queue_table
. This queue table is owned by the Streams administrator (strmadmin
) and uses the default storage of this user.streams_queue
owned by the Streams administrator (strmadmin
).*/ EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); /*
While still connected as the Streams administrator at dbs5.net
, configure the apply process to apply changes to the hr
schema.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'apply', streams_name => 'apply', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / /*
In this example, the hr
user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr
already has the necessary privileges to change these database objects, and it is convenient to make hr
the apply user.
When the apply process was created in the previous step, the Streams administrator strmadmin
was specified as the apply user by default, because strmadmin
ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin
as the apply user, but then you must grant strmadmin
privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it may be more convenient to use the Streams administrator as the apply user.
*/ BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply', apply_user => 'hr'); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by the apply process
*/ DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / /*
Connect to dbs2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs2.net /*
Create the database links to the databases where changes are propagated. In this example, database dbs2.net
propagates changes to dbs5.net
.
*/ CREATE DATABASE LINK dbs5.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'dbs5.net'; /*
While still connected as the Streams administrator at dbs2.net
, Configure and schedule propagation from the queue at dbs2.net
to the queue at dbs5.net
. Remember, changes to the hr
schema originated at dbs1.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'dbs2_to_dbs5', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@dbs5.net', include_dml => true, include_ddl => true, source_database => 'dbs1.net'); END; / /*
Connect to dbs1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@dbs1.net /*
Prepare the hr
schema for instantiation. These tables in this schema will be instantiated at dbs5.net
. This preparation is necessary so that the Streams data dictionary for the relevant propagation jobs and the apply process at dbs5.net
contain information about the hr
schema and the objects in the schema.
*/ BEGIN DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION( schema_name => 'hr'); END; / /*
Open a different window and export the schema at dbs1.net
that will be instantiated at dbs5.net
. Make sure you set the OBJECT_CONSISTENT
export parameter to y
when you run the export command. Also, make sure no DDL changes are made to the objects being exported during the export.
The following is an example export command:
exp hr/hr FILE=hr_schema.dmp OWNER=hr OBJECT_CONSISTENT=y
See Also:
Oracle9i Database Utilities for information about performing an export |
*/ PAUSE Press <RETURN> to continue when the export is complete in the other window that you opened. /*
Transfer the export dump file hr_schema.dmp
to the destination database. In this example, the destination database is dbs5.net
.
You can use binary FTP or some other method to transfer the export dump file to the destination database. You may need to open a different window to transfer the file.
*/ PAUSE Press <RETURN> to continue after transferring the dump file. /*
In a different window, connect to the computer that runs the dbs5.net
database and import the export dump file hr_schema.dmp
to instantiate the tables in the dbs5.net
database. You can use telnet or remote login to connect to the computer that runs dbs5.net
.
When you run the import command, make sure you set the STREAMS_INSTANTIATION
import parameter to y
. This parameter ensures that the import records export SCN information for each object imported.
The following is an example import command:
imp hr/hr FILE=hr_schema.dmp FROMUSER=hr IGNORE=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y
See Also:
Oracle9i Database Utilities for information about performing an import |
*/ PAUSE Press <RETURN> to continue after the import is complete at dbs5.net. /*
Connect as the Streams administrator at dbs5.net
.
*/ CONNECT strmadmin/strmadminpw@dbs5.net /*
Set the disable_on_error
parameter to n
so that the apply process will not be disabled if it encounters an error, and start apply process at dbs5.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply'); END; / /*
Check the streams_adddb.out
spool file to ensure that all actions finished successfully after this script is completed.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
This example illustrates using Streams to replicate data for a schema among three Oracle databases. DML and DDL changes made to tables in the hr
schema are captured at all databases in the environment and propagated to each of the other databases in the environment.
This example contains these parts:
Figure 19-4 provides an overview of the environment.
As illustrated in Figure 19-4, all of the databases will contain the hr
schema when the example is complete. However, at the beginning of the example, the hr
schema exists only at mult1.net
. During the example, you instantiate the hr
schema at mult2.net
and mult3.net
.
In this example, Streams is used to perform the following series of actions:
hr
schema and enqueues them into a local queue.hr
schema received from the other databases in the environment.This example uses only one queue for each database, but you can use multiple queues for each database if you want to separate changes from different source databases. In addition, this example avoids sending changes back to their source database by using the default apply tag for the apply processes. When you create an apply process, the changes applied by the apply process have redo entries with a tag of '00'
(double zero) by default. These changes are not recaptured because, by default, rules created by the DBMS_STREAMS_ADM
package have an is_null_tag()='Y'
condition by default, and this condition ensures that each capture process captures a change in a redo entry only if the tag for the redo entry is NULL
.
See Also:
Oracle9i Streams for more information about tags |
The following prerequisites must be completed before you begin the example in this section.
GLOBAL_NAMES
: This parameter must be set to true
at each database that is participating in your Streams environment. Make sure the global names of the databases are mult1.net
, mult2.net
, and mult3.net
.JOB_QUEUE_PROCESSES
: This parameter must be set to at least 2
at each database that is propagating changes. It should be set to the same value as the maximum number of jobs that can run simultaneously plus one.COMPATIBLE
: This parameter must be set to 9.2.0
or higher.LOG_PARALLELISM
: This parameter must be set to 1
at each database that captures events. In this example, this parameter must be set to 1
at each database.
See Also:
"Setting Initialization Parameters Relevant to Streams" for information about other initialization parameters that are important in a Streams environment |
ARCHIVELOG
mode. In this example, all databases are capturing changes, and so all databases must be running in ARCHIVELOG
mode.
See Also:
Oracle9i Database Administrator's Guide for information about running a database in |
This section illustrates how to set up users and create queues and database links for a Streams replication environment that includes three Oracle databases. The remaining parts of this example depend on the users and queues that you configure in this section.
Complete the following steps to set up the users and to create the streams_queue
at all of the databases.
/************************* BEGINNING OF SCRIPT ******************************
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL streams_setup_mult.out /*
Connect to mult1.net
as the hr
user.
*/ CONNECT hr/hr@mult1.net /*
Convert the hr.countries
table from an index-organized table to a regular table. Currently, the capture process cannot capture changes to index-organized tables.
*/ ALTER TABLE countries RENAME TO countries_orig; CREATE TABLE hr.countries( country_id CHAR(2) CONSTRAINT country_id_nn_noiot NOT NULL, country_name VARCHAR2(40), region_id NUMBER, CONSTRAINT country_c_id_pk_noiot PRIMARY KEY (country_id)); ALTER TABLE hr.countries ADD (CONSTRAINT countr_reg_fk_noiot FOREIGN KEY (region_id) REFERENCES regions(region_id)); INSERT INTO COUNTRIES (SELECT * FROM hr.countries_orig); ALTER TABLE locations DROP CONSTRAINT loc_c_id_fk; ALTER TABLE locations ADD (CONSTRAINT loc_c_id_fk FOREIGN KEY (country_id) REFERENCES countries(country_id)); /*
By default, the LogMiner tables are in the SYSTEM
tablespace, but the SYSTEM
tablespace may not have enough space for these tables once a capture process starts to capture changes. Therefore, you must create an alternate tablespace for the LogMiner tables.
Connect to mult1.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@mult1.net AS SYSDBA /*
Create an alternate tablespace for the LogMiner tables.
*/ ACCEPT tspace_name DEFAULT 'logmnrts' PROMPT 'Enter the name of the tablespace (for example, logmnrts): ' ACCEPT db_file_directory DEFAULT '' PROMPT 'Enter the complete path to the datafile directory (for example, /usr/oracle/dbs): ' ACCEPT db_file_name DEFAULT 'logmnrts.dbf' PROMPT 'Enter the name of the datafile (for example, logmnrts.dbf): ' CREATE TABLESPACE &tspace_name DATAFILE '&db_file_directory/&db_file_name' SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('&tspace_name'); /*
Create the Streams administrator named strmadmin
and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You may choose a different name for this user.
*/ GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; ACCEPT streams_tbs PROMPT 'Enter the tablespace for the Streams administrator on mult1.net: ' ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs QUOTA UNLIMITED ON &streams_tbs; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / /*
Connect as the Streams administrator at mult1.net
.
*/ CONNECT strmadmin/strmadminpw@mult1.net /*
Run the SET_UP_QUEUE
procedure to create a queue named streams_queue
at mult1.net
. This queue will function as the Streams queue by holding the changes that will be applied at this database and the changes that will be propagated to other databases.
Running the SET_UP_QUEUE
procedure performs the following actions:
streams_queue_table
. This queue table is owned by the Streams administrator (strmadmin
) and uses the default storage of this user.streams_queue
owned by the Streams administrator (strmadmin
).*/ EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); /*
Create database links from the current database to the other databases in the environment.
*/ CREATE DATABASE LINK mult2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'mult2.net'; CREATE DATABASE LINK mult3.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'mult3.net'; /*
This example will configure the tables in the hr
schema for conflict resolution based on the latest time for a transaction.
Connect to mult1.net
as the hr
user.
*/ CONNECT hr/hr@mult1.net /*
Add a time
column to each table in the hr
schema.
*/ ALTER TABLE hr.countries ADD (time TIMESTAMP WITH TIME ZONE); ALTER TABLE hr.departments ADD (time TIMESTAMP WITH TIME ZONE); ALTER TABLE hr.employees ADD (time TIMESTAMP WITH TIME ZONE); ALTER TABLE hr.job_history ADD (time TIMESTAMP WITH TIME ZONE); ALTER TABLE hr.jobs ADD (time TIMESTAMP WITH TIME ZONE); ALTER TABLE hr.locations ADD (time TIMESTAMP WITH TIME ZONE); ALTER TABLE hr.regions ADD (time TIMESTAMP WITH TIME ZONE); /*
Create a trigger for each table in the hr
schema to insert the time of a transaction for each row inserted or updated by the transaction.
*/ CREATE OR REPLACE TRIGGER hr.insert_time_countries BEFORE INSERT OR UPDATE ON hr.countries FOR EACH ROW BEGIN -- Consider time synchronization problems. The previous update to this -- row may have originated from a site with a clock time ahead of the -- local clock time. IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; END IF; END; / CREATE OR REPLACE TRIGGER hr.insert_time_departments BEFORE INSERT OR UPDATE ON hr.departments FOR EACH ROW BEGIN IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; END IF; END; / CREATE OR REPLACE TRIGGER hr.insert_time_employees BEFORE INSERT OR UPDATE ON hr.employees FOR EACH ROW BEGIN IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; END IF; END; / CREATE OR REPLACE TRIGGER hr.insert_time_job_history BEFORE INSERT OR UPDATE ON hr.job_history FOR EACH ROW BEGIN IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; END IF; END; / CREATE OR REPLACE TRIGGER hr.insert_time_jobs BEFORE INSERT OR UPDATE ON hr.jobs FOR EACH ROW BEGIN IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; END IF; END; / CREATE OR REPLACE TRIGGER hr.insert_time_locations BEFORE INSERT OR UPDATE ON hr.locations FOR EACH ROW BEGIN IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; END IF; END; / CREATE OR REPLACE TRIGGER hr.insert_time_regions BEFORE INSERT OR UPDATE ON hr.regions FOR EACH ROW BEGIN IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP; ELSE :NEW.TIME := :OLD.TIME + 1 / 86400; END IF; END; / /*
By default, the LogMiner tables are in the SYSTEM
tablespace, but the SYSTEM
tablespace may not have enough space for these tables once a capture process starts to capture changes. Therefore, you must create an alternate tablespace for the LogMiner tables.
Connect to mult2.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@mult2.net AS SYSDBA /*
Create an alternate tablespace for the LogMiner tables.
*/ ACCEPT tspace_name DEFAULT 'logmnrts' PROMPT 'Enter the name of the tablespace (for example, logmnrts): ' ACCEPT db_file_directory DEFAULT '' PROMPT 'Enter the complete path to the datafile directory (for example, /usr/oracle/dbs): ' ACCEPT db_file_name DEFAULT 'logmnrts.dbf' PROMPT 'Enter the name of the datafile (for example, logmnrts.dbf): ' CREATE TABLESPACE &tspace_name DATAFILE '&db_file_directory/&db_file_name' SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('&tspace_name'); /*
Create the Streams administrator named strmadmin
and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You may choose a different name for this user.
*/ GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; ACCEPT streams_tbs PROMPT 'Enter the tablespace for the Streams administrator on mult2.net: ' ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs QUOTA UNLIMITED ON &streams_tbs; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / /*
Connect as the Streams administrator at mult2.net
.
*/ CONNECT strmadmin/strmadminpw@mult2.net /*
Run the SET_UP_QUEUE
procedure to create a queue named streams_queue
at mult2.net
. This queue will function as the Streams queue by holding the changes that will be applied at this database and the changes that will be propagated to other databases.
Running the SET_UP_QUEUE
procedure performs the following actions:
streams_queue_table
. This queue table is owned by the Streams administrator (strmadmin
) and uses the default storage of this user.streams_queue
owned by the Streams administrator (strmadmin
).*/ EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); /*
Create database links from the current database to the other databases in the environment.
*/ CREATE DATABASE LINK mult1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'mult1.net'; CREATE DATABASE LINK mult3.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'mult3.net'; /*
This example illustrates instantiating the tables in the hr
schema at mult2.net
by exporting these tables from mult1.net
and importing them into mult2.net
. You must drop the tables in the hr
schema at mult2.net
for the instantiation portion of this example to work properly.
Connect as hr
at mult2.net
.
*/ CONNECT hr/hr@mult2.net /*
Drop all tables in the hr
schema in the mult2.net
database.
*/ DROP TABLE hr.countries CASCADE CONSTRAINTS; DROP TABLE hr.departments CASCADE CONSTRAINTS; DROP TABLE hr.employees CASCADE CONSTRAINTS; DROP TABLE hr.job_history CASCADE CONSTRAINTS; DROP TABLE hr.jobs CASCADE CONSTRAINTS; DROP TABLE hr.locations CASCADE CONSTRAINTS; DROP TABLE hr.regions CASCADE CONSTRAINTS; /*
By default, the LogMiner tables are in the SYSTEM
tablespace, but the SYSTEM
tablespace may not have enough space for these tables once a capture process starts to capture changes. Therefore, you must create an alternate tablespace for the LogMiner tables.
Connect to mult3.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@mult3.net AS SYSDBA /*
Create an alternate tablespace for the LogMiner tables.
*/ ACCEPT tspace_name DEFAULT 'logmnrts' PROMPT 'Enter the name of the tablespace (for example, logmnrts): ' ACCEPT db_file_directory DEFAULT '' PROMPT 'Enter the complete path to the datafile directory (for example, /usr/oracle/dbs): ' ACCEPT db_file_name DEFAULT 'logmnrts.dbf' PROMPT 'Enter the name of the datafile (for example, logmnrts.dbf): ' CREATE TABLESPACE &tspace_name DATAFILE '&db_file_directory/&db_file_name' SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('&tspace_name'); /*
Create the Streams administrator named strmadmin
and grant this user the necessary privileges. These privileges enable the user to manage queues, execute subprograms in packages related to Streams, create rule sets, create rules, and monitor the Streams environment by querying data dictionary views and queue tables. You may choose a different name for this user.
*/ GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; ACCEPT streams_tbs PROMPT 'Enter the tablespace for the Streams administrator on mult3.net: ' ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs QUOTA UNLIMITED ON &streams_tbs; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / /*
Connect as the Streams administrator at mult3.net
.
*/ CONNECT strmadmin/strmadminpw@mult3.net /*
Run the SET_UP_QUEUE
procedure to create a queue named streams_queue
at mult3.net
. This queue will function as the Streams queue by holding the changes that will be applied at this database and the changes that will be propagated to other databases.
Running the SET_UP_QUEUE
procedure performs the following actions:
streams_queue_table
. This queue table is owned by the Streams administrator (strmadmin
) and uses the default storage of this user.streams_queue
owned by the Streams administrator (strmadmin
).*/ EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); /*
Create database links from the current database to the other databases in the environment.
*/ CREATE DATABASE LINK mult1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'mult1.net'; CREATE DATABASE LINK mult2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'mult2.net'; /*
This example illustrates instantiating the tables in the hr
schema at mult3.net
by exporting these tables from mult1.net
and importing them into mult3.net
. You must drop the tables in the hr
schema at mult3.net
for the instantiation portion of this example to work properly.
Connect as hr
at mult3.net
.
*/ CONNECT hr/hr@mult3.net /*
Drop all tables in the hr
schema in the mult3.net
database.
*/ DROP TABLE hr.countries CASCADE CONSTRAINTS; DROP TABLE hr.departments CASCADE CONSTRAINTS; DROP TABLE hr.employees CASCADE CONSTRAINTS; DROP TABLE hr.job_history CASCADE CONSTRAINTS; DROP TABLE hr.jobs CASCADE CONSTRAINTS; DROP TABLE hr.locations CASCADE CONSTRAINTS; DROP TABLE hr.regions CASCADE CONSTRAINTS; /*
Check the streams_setup_mult.out
spool file to ensure that all actions finished successfully after this script is completed.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
Complete the following steps to configure a Streams environment that shares information from multiple databases.
/************************* BEGINNING OF SCRIPT ******************************
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL streams_mult.out /*
Connect to mult1.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@mult1.net AS SYSDBA /*
Specify an unconditional supplemental log group that includes the primary key for each table and the column list for each table, as specified in "Configure Latest Time Conflict Resolution at mult1.net".
*/ ALTER TABLE hr.countries ADD SUPPLEMENTAL LOG GROUP log_group_countries (country_id, country_name, region_id, time) ALWAYS; ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP log_group_departments (department_id, department_name, manager_id, location_id, time) ALWAYS; ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG GROUP log_group_employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, time) ALWAYS; ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs (job_id, job_title, min_salary, max_salary, time) ALWAYS; ALTER TABLE hr.job_history ADD SUPPLEMENTAL LOG GROUP log_group_job_history (employee_id, start_date, end_date, job_id, department_id, time) ALWAYS; ALTER TABLE hr.locations ADD SUPPLEMENTAL LOG GROUP log_group_locations (location_id, street_address, postal_code, city, state_province, country_id, time) ALWAYS; ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG GROUP log_group_regions (region_id, region_name, time) ALWAYS; ALTER SYSTEM ARCHIVE LOG CURRENT; /*
Connect to mult1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult1.net /*
Create the capture process to capture changes to the entire hr
schema at mult1.net
. After this step is complete, users can modify tables in the hr
schema at mult1.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'capture', streams_name => 'capture_hr', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true); END; / /*
Configure mult1.net
to apply changes to the hr
schema at mult2.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'apply', streams_name => 'apply_from_mult2', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'mult2.net'); END; / /*
Configure mult1.net
to apply changes to the hr
schema at mult3.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'apply', streams_name => 'apply_from_mult3', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'mult3.net'); END; / /*
In this example, the hr
user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr
already has the necessary privileges to change these database objects, and it is convenient to make hr
the apply user.
When the apply process was created in the previous step, the Streams administrator strmadmin
was specified as the apply user by default, because strmadmin
ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin
as the apply user, but then you must grant strmadmin
privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it may be more convenient to use the Streams administrator as the apply user.
*/ BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply_from_mult2', apply_user => 'hr'); END; / BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply_from_mult3', apply_user => 'hr'); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by each apply process
*/ DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY_FROM_MULT2'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY_FROM_MULT3'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / /*
Specify an update conflict handler for each table in the hr
schema. For each table, designate the time
column as the resolution column for a MAXIMUM
conflict handler. When an update conflict occurs, such an update conflict handler applies the transaction with the latest (or greater) time and discards the transaction with the earlier (or lesser) time. The column lists for each table do not include the primary key because this example assumes that primary key values are never updated.
*/ DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'country_name'; cols(2) := 'region_id'; cols(3) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.countries', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'department_name'; cols(2) := 'manager_id'; cols(3) := 'location_id'; cols(4) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.departments', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'first_name'; cols(2) := 'last_name'; cols(3) := 'email'; cols(4) := 'phone_number'; cols(5) := 'hire_date'; cols(6) := 'job_id'; cols(7) := 'salary'; cols(8) := 'commission_pct'; cols(9) := 'manager_id'; cols(10) := 'department_id'; cols(11) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.employees', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'job_title'; cols(2) := 'min_salary'; cols(3) := 'max_salary'; cols(4) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.jobs', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'employee_id'; cols(2) := 'start_date'; cols(3) := 'end_date'; cols(4) := 'job_id'; cols(5) := 'department_id'; cols(6) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.job_history', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'street_address'; cols(2) := 'postal_code'; cols(3) := 'city'; cols(4) := 'state_province'; cols(5) := 'country_id'; cols(6) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.locations', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'region_name'; cols(2) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.regions', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / /*
Configure and schedule propagation of DML and DDL changes in the hr
schema from the queue at mult1.net
to the queue at mult2.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'mult1_to_mult2', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@mult2.net', include_dml => true, include_ddl => true, source_database => 'mult1.net'); END; / /*
Configure and schedule propagation of DML and DDL changes in the hr
schema from the queue at mult1.net
to the queue at mult3.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'mult1_to_mult3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@mult3.net', include_dml => true, include_ddl => true, source_database => 'mult1.net'); END; / /*
Connect to mult2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult2.net /*
Create the capture process to capture changes to the entire hr
schema at mult2.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'capture', streams_name => 'capture_hr', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true); END; / /*
In this example, the hr
schema already exists at all of the databases. The tables in the schema exist only at mult1.net
until they are instantiated at mult2.net
and mult3.net
in Step 21. The instantiation is done using an export of the tables from mult1.net
. These export/import operations set the schema instantiation SCNs for mult1.net
at mult2.net
and mult3.net
automatically.
However, the instantiation SCNs for mult2.net
and mult3.net
are not set automatically at the other sites in the environment. This step sets the schema instantiation SCN for mult2.net
manually at mult1.net
and mult3.net
. The current SCN at mult2.net
is obtained by using the GET_SYSTEM_CHANGE_NUMBER
function in the DBMS_FLASHBACK
package at mult2.net
. This SCN is used at mult1.net
and mult3.net
to run the SET_SCHEMA_INSTANTIATION_SCN
procedure in the DBMS_APPLY_ADM
package.
The SET_SCHEMA_INSTANTIATION_SCN
procedure controls which DDL LCRs for a schema are ignored by an apply process and which DDL LCRs for a schema are applied by an apply process. If the commit SCN of a DDL LCR for a database object in a schema from a source database is less than or equal to the instantiation SCN for that database object at some destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.
Because you are running the SET_SCHEMA_INSTANTIATION_SCN
procedure before the tables are instantiated at mult2.net
, and because the local capture process is configured already, you do not need to run the SET_TABLE_INSTANTIATION_SCN
for each table after the instantiation. In this example, an apply process at both mult1.net
and mult3.net
will apply transactions to the tables in the hr
schema with SCNs that were committed after the SCN obtained in this step.
*/ DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT1.NET( source_schema_name => 'hr', source_database_name => 'mult2.net', instantiation_scn => iscn); DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT3.NET( source_schema_name => 'hr', source_database_name => 'mult2.net', instantiation_scn => iscn); END; /
/*
Configure mult2.net
to apply changes to the hr
schema at mult1.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'apply', streams_name => 'apply_from_mult1', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'mult1.net'); END; / /*
Configure mult2.net
to apply changes to the hr
schema at mult3.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'apply', streams_name => 'apply_from_mult3', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'mult3.net'); END; / /*
In this example, the hr
user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr
already has the necessary privileges to change these database objects, and it is convenient to make hr
the apply user.
When the apply process was created in the previous step, the Streams administrator strmadmin
was specified as the apply user by default, because strmadmin
ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin
as the apply user, but then you must grant strmadmin
privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it may be more convenient to use the Streams administrator as the apply user.
*/ BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply_from_mult1', apply_user => 'hr'); END; / BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply_from_mult3', apply_user => 'hr'); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by each apply process
*/ DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY_FROM_MULT1'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY_FROM_MULT3'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / /*
Configure and schedule propagation of DML and DDL changes in the hr
schema from the queue at mult2.net
to the queue at mult1.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'mult2_to_mult1', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@mult1.net', include_dml => true, include_ddl => true, source_database => 'mult2.net'); END; / /*
Configure and schedule propagation of DML and DDL changes in the hr
schema from the queue at mult2.net
to the queue at mult3.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'mult2_to_mult3', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@mult3.net', include_dml => true, include_ddl => true, source_database => 'mult2.net'); END; / /*
Connect to mult3.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult3.net /*
Create the capture process to capture changes to the entire hr
schema at mult3.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'capture', streams_name => 'capture_hr', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true); END; / /*
In this example, the hr
schema already exists at all of the databases. The tables in the schema exist only at mult1.net
until they are instantiated at mult2.net
and mult3.net
in Step 21. The instantiation is done using an export of the tables from mult1.net
. These export/import operations set the schema instantiation SCNs for mult1.net
at mult2.net
and mult3.net
automatically.
However, the instantiation SCNs for mult2.net
and mult3.net
are not set automatically at the other sites in the environment. This step sets the schema instantiation SCN for mult3.net
manually at mult1.net
and mult2.net
. The current SCN at mult3.net
is obtained by using the GET_SYSTEM_CHANGE_NUMBER
function in the DBMS_FLASHBACK
package at mult3.net
. This SCN is used at mult1.net
and mult2.net
to run the SET_SCHEMA_INSTANTIATION_SCN
procedure in the DBMS_APPLY_ADM
package.
The SET_SCHEMA_INSTANTIATION_SCN
procedure controls which DDL LCRs for a schema are ignored by an apply process and which DDL LCRs for a schema are applied by an apply process. If the commit SCN of a DDL LCR for a database object in a schema from a source database is less than or equal to the instantiation SCN for that database object at some destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.
Because you are running the SET_SCHEMA_INSTANTIATION_SCN
procedure before the tables are instantiated at mult3.net
, and because the local capture process is configured already, you do not need to run the SET_TABLE_INSTANTIATION_SCN
for each table after the instantiation. In this example, an apply process at both mult1.net
and mult2.net
will apply transactions to the tables in the hr
schema with SCNs that were committed after the SCN obtained in this step.
*/ DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT1.NET( source_schema_name => 'hr', source_database_name => 'mult3.net', instantiation_scn => iscn); DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@MULT2.NET( source_schema_name => 'hr', source_database_name => 'mult3.net', instantiation_scn => iscn); END; / /*
Configure mult3.net
to apply changes to the hr
schema at mult1.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'apply', streams_name => 'apply_from_mult1', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'mult1.net'); END; / /*
Configure mult3.net
to apply changes to the hr
schema at mult2.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'hr', streams_type => 'apply', streams_name => 'apply_from_mult2', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'mult2.net'); END; / /*
In this example, the hr
user owns all of the database objects for which changes are applied by the apply process at this database. Therefore, hr
already has the necessary privileges to change these database objects, and it is convenient to make hr
the apply user.
When the apply process was created in the previous step, the Streams administrator strmadmin
was specified as the apply user by default, because strmadmin
ran the procedure that created the apply process. Instead of specifying hr as the apply user, you could retain strmadmin
as the apply user, but then you must grant strmadmin
privileges on all of the database objects for which changes are applied and privileges to execute all user procedures used by the apply process. In an environment where an apply process applies changes to database objects in multiple schemas, it may be more convenient to use the Streams administrator as the apply user.
*/ BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply_from_mult1', apply_user => 'hr'); END; / BEGIN DBMS_APPLY_ADM.ALTER_APPLY( apply_name => 'apply_from_mult2', apply_user => 'hr'); END; / /*
Because the hr
user was specified as the apply user in the previous step, the hr
user requires execute privilege on the rule set used by each apply process
*/ DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY_FROM_MULT1'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / DECLARE rs_name VARCHAR2(64); -- Variable to hold rule set name BEGIN SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME INTO rs_name FROM DBA_APPLY WHERE APPLY_NAME='APPLY_FROM_MULT2'; DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET, object_name => rs_name, grantee => 'hr'); END; / /*
Configure and schedule propagation of DML and DDL changes in the hr
schema from the queue at mult3.net
to the queue at mult1.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'mult3_to_mult1', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@mult1.net', include_dml => true, include_ddl => true, source_database => 'mult3.net'); END; / /*
Configure and schedule propagation of DML and DDL changes in the hr
schema from the queue at mult3.net
to the queue at mult2.net
.
*/ BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'mult3_to_mult2', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@mult2.net', include_dml => true, include_ddl => true, source_database => 'mult3.net'); END; / /*
Open a different window and export the schema at mult1.net
that will be instantiated at mult2.net
and mult3.net
. Make sure you set the OBJECT_CONSISTENT
export parameter to y
when you run the export command. Also, make sure no DDL changes are made to the objects being exported during the export.
The following is an example export command:
exp hr/hr FILE=hr_schema.dmp OWNER=hr OBJECT_CONSISTENT=y
See Also:
Oracle9i Database Utilities for information about performing an export |
*/ PAUSE Press <RETURN> to continue when the export is complete in the other window that you opened. /*
Transfer the export dump file hr_schema.dmp
to the destination databases. In this example, the destination databases are mult2.net
and mult3.net
.
You can use binary FTP or some other method to transfer the export dump file to the destination database. You may need to open a different window to transfer the file.
*/ PAUSE Press <RETURN> to continue after transferring the dump file to all of the other databases in the environment. /*
In a different window, connect to the computer that runs the mult2.net
database and import the export dump file hr_schema.dmp
to instantiate the tables in the mult2.net
database. You can use telnet or remote login to connect to the computer that runs mult2.net
.
When you run the import command, make sure you set the STREAMS_INSTANTIATION
import parameter to y
. This parameter ensures that the import records export SCN information for each object imported.
Also, make sure no changes are made to the tables in the schema being imported at the destination database (mult2.net
) until the import is complete and the capture process is created.
The following is an example import command:
imp hr/hr FILE=hr_schema.dmp FROMUSER=hr IGNORE=y COMMIT=y LOG=import.log STREAMS_INSTANTIATION=y
See Also:
Oracle9i Database Utilities for information about performing an import |
*/ PAUSE Press <RETURN> to continue after the import is complete at mult2.net. /*
In a different window, connect to the computer that runs the mult3.net
database and import the export dump file hr_schema.dmp
to instantiate the tables in the mult3.net
database.
After you connect to mult3.net
, perform the import in the same way that you did for mult2.net
.
*/ PAUSE Press <RETURN> to continue after the import is complete at mult3.net. /*
Attention: Make sure no changes are made to the imported tables at |
Connect to mult2.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@mult2.net AS SYSDBA /*
Set the session tag to a non-NULL
value so that the supplemental logging changes are not captured by the capture process at mult2.net
.
*/ EXEC DBMS_STREAMS.SET_TAG(tag => HEXTORAW('01')); /*
Specify an unconditional supplemental log group that includes the primary key for each table and the column list for each table, as specified in "Configure Latest Time Conflict Resolution at mult2.net". After this step is complete, users can modify tables in the hr
schema at mult2.net
.
*/ ALTER TABLE hr.countries ADD SUPPLEMENTAL LOG GROUP log_group_countries (country_id, country_name, region_id, time) ALWAYS; ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP log_group_departments (department_id, department_name, manager_id, location_id, time) ALWAYS; ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG GROUP log_group_employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, time) ALWAYS; ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs (job_id, job_title, min_salary, max_salary, time) ALWAYS; ALTER TABLE hr.job_history ADD SUPPLEMENTAL LOG GROUP log_group_job_history (employee_id, start_date, end_date, job_id, department_id, time) ALWAYS; ALTER TABLE hr.locations ADD SUPPLEMENTAL LOG GROUP log_group_locations (location_id, street_address, postal_code, city, state_province, country_id, time) ALWAYS; ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG GROUP log_group_regions (region_id, region_name, time) ALWAYS; ALTER SYSTEM ARCHIVE LOG CURRENT; /*
Set the session tag back to NULL
at mult2.net
.
*/ EXEC DBMS_STREAMS.SET_TAG(tag => NULL); /*
Connect to mult3.net
as SYS
user.
*/ CONNECT SYS/CHANGE_ON_INSTALL@mult3.net AS SYSDBA /*
Set the session tag to a non-NULL
value so that the supplemental logging changes are not captured by the capture process at mult3.net
.
*/ EXEC DBMS_STREAMS.SET_TAG(tag => HEXTORAW('01')); /*
Specify an unconditional supplemental log group that includes the primary key for each table and the column list for each table, as specified in "Configure Latest Time Conflict Resolution at mult3.net". After this step is complete, users can modify tables in the hr
schema at mult3.net
.
*/ ALTER TABLE hr.countries ADD SUPPLEMENTAL LOG GROUP log_group_countries (country_id, country_name, region_id, time) ALWAYS; ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP log_group_departments (department_id, department_name, manager_id, location_id, time) ALWAYS; ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG GROUP log_group_employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id, time) ALWAYS; ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs (job_id, job_title, min_salary, max_salary, time) ALWAYS; ALTER TABLE hr.job_history ADD SUPPLEMENTAL LOG GROUP log_group_job_history (employee_id, start_date, end_date, job_id, department_id, time) ALWAYS; ALTER TABLE hr.locations ADD SUPPLEMENTAL LOG GROUP log_group_locations (location_id, street_address, postal_code, city, state_province, country_id, time) ALWAYS; ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG GROUP log_group_regions (region_id, region_name, time) ALWAYS; ALTER SYSTEM ARCHIVE LOG CURRENT; /*
Set the session tag back to NULL
at mult2.net
.
*/ EXEC DBMS_STREAMS.SET_TAG(tag => NULL); /*
Connect to mult2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult2.net /*
Specify an update conflict handler for each table in the hr
schema. For each table, designate the time
column as the resolution column for a MAXIMUM
conflict handler. When an update conflict occurs, such an update conflict handler applies the transaction with the latest (or greater) time and discards the transaction with the earlier (or lesser) time.
*/ DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'country_name'; cols(2) := 'region_id'; cols(3) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.countries', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'department_name'; cols(2) := 'manager_id'; cols(3) := 'location_id'; cols(4) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.departments', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'first_name'; cols(2) := 'last_name'; cols(3) := 'email'; cols(4) := 'phone_number'; cols(5) := 'hire_date'; cols(6) := 'job_id'; cols(7) := 'salary'; cols(8) := 'commission_pct'; cols(9) := 'manager_id'; cols(10) := 'department_id'; cols(11) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.employees', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'job_title'; cols(2) := 'min_salary'; cols(3) := 'max_salary'; cols(4) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.jobs', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'employee_id'; cols(2) := 'start_date'; cols(3) := 'end_date'; cols(4) := 'job_id'; cols(5) := 'department_id'; cols(6) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.job_history', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'street_address'; cols(2) := 'postal_code'; cols(3) := 'city'; cols(4) := 'state_province'; cols(5) := 'country_id'; cols(6) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.locations', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'region_name'; cols(2) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.regions', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / /*
Set the disable_on_error
parameter to n
for both apply processes so that they will not be not disabled if they encounter an error, and start both of the apply processes at mult2.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_from_mult1', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_from_mult1'); END; / BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_from_mult3', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_from_mult3'); END; / /*
Connect to mult3.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult3.net /*
Specify an update conflict handler for each table in the hr
schema. For each table, designate the time
column as the resolution column for a MAXIMUM
conflict handler. When an update conflict occurs, such an update conflict handler applies the transaction with the latest (or greater) time and discards the transaction with the earlier (or lesser) time.
*/ DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'country_name'; cols(2) := 'region_id'; cols(3) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.countries', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'department_name'; cols(2) := 'manager_id'; cols(3) := 'location_id'; cols(4) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.departments', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'first_name'; cols(2) := 'last_name'; cols(3) := 'email'; cols(4) := 'phone_number'; cols(5) := 'hire_date'; cols(6) := 'job_id'; cols(7) := 'salary'; cols(8) := 'commission_pct'; cols(9) := 'manager_id'; cols(10) := 'department_id'; cols(11) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.employees', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'job_title'; cols(2) := 'min_salary'; cols(3) := 'max_salary'; cols(4) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.jobs', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'employee_id'; cols(2) := 'start_date'; cols(3) := 'end_date'; cols(4) := 'job_id'; cols(5) := 'department_id'; cols(6) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.job_history', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'street_address'; cols(2) := 'postal_code'; cols(3) := 'city'; cols(4) := 'state_province'; cols(5) := 'country_id'; cols(6) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.locations', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / DECLARE cols DBMS_UTILITY.NAME_ARRAY; BEGIN cols(1) := 'region_name'; cols(2) := 'time'; DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER( object_name => 'hr.regions', method_name => 'MAXIMUM', resolution_column => 'time', column_list => cols); END; / /*
Set the disable_on_error
parameter to n
for both apply processes so that they will not be disabled if they encounter an error, and start both of the apply processes at mult3.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_from_mult1', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_from_mult1'); END; / BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_from_mult2', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_from_mult2'); END; / /*
Connect to mult1.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult1.net /*
Set the disable_on_error
parameter to n
for both apply processes so that they will not be disabled if they encounter an error, and start both of the apply processes at mult1.net
.
*/ BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_from_mult2', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_from_mult2'); END; / BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_from_mult3', parameter => 'disable_on_error', value => 'n'); END; / BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_from_mult3'); END; / /*
Start the capture process at mult1.net
.
*/ BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'capture_hr'); END; / /*
Connect to mult2.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult2.net /*
Start the capture process at mult2.net
.
*/ BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'capture_hr'); END; / /*
Connect to mult3.net
as the strmadmin
user.
*/ CONNECT strmadmin/strmadminpw@mult3.net /*
Start the capture process at mult3.net
.
*/ BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'capture_hr'); END; / SET ECHO OFF /*
Check the streams_mult.out
spool file to ensure that all actions finished successfully after this script is completed.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
|
Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|