21
Simple Single Source Replication Example

This chapter illustrates an example of a simple single source replication environment that can be constructed using Streams.

This chapter contains these topics:

Overview of the Simple Single Source Replication Example

The example in this chapter illustrates using Streams to replicate data in one table between two databases. A capture process captures data manipulation language (DML) and data definition language (DDL) changes made to the jobs table in the hr schema at the str1.net Oracle database, and a propagation propagates these changes to the str2.net Oracle database. Then, an apply process applies these changes at the str2.net database. This example assumes that the hr.jobs table is read-only at the str2.net database.

Figure 21-1 provides an overview of the environment.

Figure 21-1 Simple Example That Shares Data From a Single Source Database

Text description of strms035.gif follows

Text description of the illustration strms035.gif

Prerequisites

The following prerequisites must be completed before you begin the example in this chapter.

Set the following initialization parameters to the values indicated:
- AQ_TM_PROCESSES: This parameter establishes queue monitor processes. Values from 1 to 10 specify the number of queue monitor processes created to monitor the messages. If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor processes are not created. In this example, AQ_TM_PROCESSES should be set to at least 1 at each database.
  
  Setting the parameter to 1 or more starts the specified number of queue monitor processes. These queue monitor processes are responsible for managing time-based operations of messages such as delay and expiration, cleaning up retained messages after the specified retention time, and cleaning up consumed messages if the retention time is 0.
- 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, str1.net propagates events. So, JOB_QUEUE_PROCESSES must be set to at least 2 at str1.net.
- COMPATIBLE: This parameter must be set to 9.2.0 or higher at each database that is participating in your Streams environment.
- 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 str1.net.
  
  See Also:
  "Setting Initialization Parameters Relevant to Streams" for information about other initialization parameters that are important in a Streams environment
Any database producing changes that will be captured must be running in ARCHIVELOG mode. In this example, changes are produced at str1.net, and so str1.net must be running in ARCHIVELOG mode.

See Also:
Oracle9i Database Administrator's Guide for information about running a database in ARCHIVELOG mode
Configure your network and Oracle Net so that the str1.net database can communicate with the str2.net database.

See Also:
Oracle9i Net Services Administrator's Guide
This example creates a new user to function as the Streams administrator (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.

Set Up Users and Create Queues and Database Links

Complete the following steps to set up users and create queues and database links for a Streams replication environment that includes two Oracle databases.

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

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_simple.out

/*

Step 2 Set Up Users at str1.net

Connect to str1.net as SYS user.

*/
 
CONNECT SYS/CHANGE_ON_INSTALL@str1.net AS SYSDBA

/*

Note:

To ensure security, use a password other than strmadminpw for the Streams administrator.
The SELECT_CATALOG_ROLE is not required for the Streams administrator. It is granted in this example so that the Streams administrator can monitor the environment easily.
If you plan to use the Streams tool in Oracle Enterprise Manager, then grant the Streams administrator SELECT ANY DICTIONARY privilege, in addition to the privileges shown in this step.
The ACCEPT command must appear on a single line in the script.

See Also:

"Configuring a Streams Administrator"

*/

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE 
  TO strmadmin IDENTIFIED BY strmadminpw;

ACCEPT streams_tbs PROMPT 'Enter Streams administrator tablespace on str1.net: '

ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs
                     QUOTA UNLIMITED ON &streams_tbs;

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;
/

Step 3 Create the Streams Queue at str1.net

Connect as the Streams administrator at the database where you want to capture changes. In this example, that database is str1.net.

*/

CONNECT strmadmin/strmadminpw@str1.net

/*

Run the SET_UP_QUEUE procedure to create a queue named streams_queue at str1.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:

Creates a queue table named streams_queue_table. This queue table is owned by the Streams administrator (strmadmin) and uses the default storage of this user.
Creates a queue named streams_queue owned by the Streams administrator (strmadmin).
Starts the queue.

*/

EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

/*

Step 4 Create the Database Link at str1.net

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 str1.net, and these changes are propagated to str2.net.

*/

CREATE DATABASE LINK str2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
   USING 'str2.net';

/*

Step 5 Set Up Users at str2.net

Connect to str2.net as SYS user.

*/
 
CONNECT SYS/CHANGE_ON_INSTALL@str2.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. In this example, the Streams administrator will be the apply user for the apply process and must be able to apply changes to the hr.jobs table at str2.net. Therefore, the Streams administrator is granted ALL privileges on this table. You may choose a different name for the Streams administrator.

Note:

To ensure security, use a password other than strmadminpw for the Streams administrator.
The SELECT_CATALOG_ROLE is not required for the Streams administrator. It is granted in this example so that the Streams administrator can monitor the environment easily.
If you plan to use the Streams tool in Oracle Enterprise Manager, then grant the Streams administrator SELECT ANY DICTIONARY privilege, in addition to the privileges shown in this step.
The ACCEPT command must appear on a single line in the script.

See Also:

"Configuring a Streams Administrator"

*/
 
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE 
  TO strmadmin IDENTIFIED BY strmadminpw;

GRANT ALL ON hr.jobs TO strmadmin;

ACCEPT streams_tbs PROMPT 'Enter Streams administrator tablespace on str2.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_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;
/

Step 6 Set Up the Streams Queue at str2.net

Connect as the Streams administrator at str2.net.

*/

CONNECT strmadmin/strmadminpw@str2.net

/*

Run the SET_UP_QUEUE procedure to create a queue named streams_queue at str2.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:

Creates a queue table named streams_queue_table. This queue table is owned by the Streams administrator (strmadmin) and uses the default storage of this user.
Creates a queue named streams_queue owned by the Streams administrator (strmadmin).
Starts the queue.

*/

EXEC  DBMS_STREAMS_ADM.SET_UP_QUEUE();

/*

Step 7 Check the Spool Results

Check the streams_setup_simple.out spool file to ensure that all actions finished successfully after this script is completed.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Configure Capture, Propagation, and Apply for Changes to One Table

Complete the following steps to specify the capture, propagation, and apply definitions for the hr.jobs table using the DBMS_STEAMS_ADM package.

Note:

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

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_jobs.out

/*

Step 2 Create an Alternate Tablespace for the LogMiner Tables at str1.net

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 str1.net as SYS user.

*/
 
CONNECT SYS/CHANGE_ON_INSTALL@str1.net AS SYSDBA

/*

Create an alternate tablespace for the LogMiner tables.

Note:

Each ACCEPT command must appear on a single line in the script.

*/

ACCEPT tspace_name DEFAULT 'logmnrts' PROMPT 'Enter tablespace name (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');

/*

Step 3 Specify Supplemental Logging at str1.net

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 str1.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 hr.jobs table.

The following statement specifies an unconditional supplemental log group for the primary key column in the hr.jobs table.

See Also:

*/

ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs_pk
  (job_id) ALWAYS;

/*

Step 4 Configure Propagation at str1.net

Connect to str1.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@str1.net

/*

Configure and schedule propagation of DML and DDL changes to the hr.jobs table from the queue at str1.net to the queue at str2.net.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.jobs', 
    streams_name            => 'str1_to_str2', 
    source_queue_name       => 'strmadmin.streams_queue',
    destination_queue_name  => 'strmadmin.streams_queue@str2.net',
    include_dml             =>  true,
    include_ddl             =>  true,
    source_database         => 'str1.net');
END;
/

/*

Step 5 Configure the Capture Process at str1.net

Configure the capture process to capture changes to the hr.jobs table at str1.net. This step specifies that changes to this table are captured by the capture process and enqueued into the specified queue.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name     => 'hr.jobs',   
    streams_type   => 'capture',
    streams_name   => 'capture_simp',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    =>  true,
    include_ddl    =>  true);
END;
/

/*

Step 6 Instantiate the hr.jobs Table at str2.net

This example assumes that the hr.jobs table exists at both the str1.net database and the str2.net database, and that this table is identical at both databases. In this case, you can instantiate the table at the str2.net database by performing a metadata only export of the table at str1.net and then importing the resulting export dump file at str2.net. Performing this metadata only export/import records the instantiation SCN for the hr.jobs table at str2.net, which is required before an apply process at str2.net can apply changes to the table.

Open a different window and export the hr.jobs table at str1.net that will be instantiated at str2.net. Make sure you set the OBJECT_CONSISTENT export parameter to y and the ROWS export parameter to n when you run the export command. Also, make sure no DML or DDL changes are made to the hr.jobs table during the export.

The following is an example export command:

exp userid=hr/hr FILE=jobs_instant.dmp TABLES=jobs OBJECT_CONSISTENT=y ROWS=n

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 jobs_instant.dmp to the destination database. In this example, the destination database is str2.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 str2.net database and import the export dump file jobs_instant.dmp to instantiate the jobs table in the str2.net database. You can use telnet or remote login to connect to the computer that runs str2.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=jobs_instant.dmp 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 str2.net.

Step 7 Drop the Supplemental Log Group for hr.jobs at str2.net

When you instantiated that hr.jobs table at str2.net, the supplemental log group from str1.net for the table was retained. This log group is not needed at str2.net because no capture process captures changes to this table at str2.net. You can remove the log group to avoid extraneous information in the redo log at str2.net. Connect to str2.net as the hr user.

*/
 
CONNECT hr/hr@str2.net

/*

Drop the supplemental log group at str2.net.

*/

ALTER TABLE hr.jobs DROP SUPPLEMENTAL LOG GROUP log_group_jobs_pk;

/*

Step 8 Configure the Apply Process at str2.net

Connect to str2.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@str2.net

/*

Configure str2.net to apply changes to the hr.jobs table.

*/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.jobs',
    streams_type    => 'apply', 
    streams_name    => 'apply_simp',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     =>  true,
    include_ddl     =>  true,
    source_database => 'str1.net');
END;
/

/*

Step 9 Start the Apply Process at str2.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 the apply process at str2.net.

*/

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_simp', 
    parameter   => 'disable_on_error', 
    value       => 'n');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_simp');
END;
/

/*

Step 10 Start the Capture Process at str1.net

Connect to str1.net as the strmadmin user.

*/
 
CONNECT strmadmin/strmadminpw@str1.net

/*

Start the capture process at str1.net.

*/

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_simp');
END;
/

/*

Step 11 Check the Spool Results

Check the streams_share_jobs.out spool file to ensure that all actions finished successfully after this script is completed.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Make Changes to the hr.jobs Table and View Results

Complete the following steps to make DML and DDL changes to the hr.jobs table at str1.net and then confirm that the changes were captured at str1.net, propagated from str1.net to str2.net, and applied to the hr.jobs table at str2.net.

Step 1 Make Changes to hr.jobs at str1.net

Make the following changes to the hr.jobs table.

CONNECT hr/hr@str1.net

UPDATE hr.jobs SET max_salary=9545 WHERE job_id='PR_REP';
COMMIT;

ALTER TABLE hr.jobs ADD(duties VARCHAR2(4000));

Step 2 Query and Describe the hr.jobs Table at str2.net

After some time passes to allow for capture, propagation, and apply of the changes performed in the previous step, run the following query to confirm that the UPDATE change was propagated and applied at str2.net:

CONNECT hr/hr@str2.net

SELECT * FROM hr.jobs WHERE job_id='PR_REP';

The value in the max_salary column should be 9545.

Next, describe the hr.jobs table to confirm that the ALTER TABLE change was propagated and applied at str2.net:

DESC hr.jobs

The duties column should be the last column.

21 Simple Single Source Replication Example

Overview of the Simple Single Source Replication Example

Figure 21-1 Simple Example That Shares Data From a Single Source Database

Prerequisites

Set Up Users and Create Queues and Database Links

Step 1 Show Output and Spool Results

Step 2 Set Up Users at str1.net

Step 3 Create the Streams Queue at str1.net

Step 4 Create the Database Link at str1.net

Step 5 Set Up Users at str2.net

Step 6 Set Up the Streams Queue at str2.net

Step 7 Check the Spool Results

Configure Capture, Propagation, and Apply for Changes to One Table

Step 1 Show Output and Spool Results

Step 2 Create an Alternate Tablespace for the LogMiner Tables at str1.net

Step 3 Specify Supplemental Logging at str1.net

Step 4 Configure Propagation at str1.net

Step 5 Configure the Capture Process at str1.net

Step 6 Instantiate the hr.jobs Table at str2.net

Step 7 Drop the Supplemental Log Group for hr.jobs at str2.net

Step 8 Configure the Apply Process at str2.net

Step 9 Start the Apply Process at str2.net

Step 10 Start the Capture Process at str1.net

Step 11 Check the Spool Results

Make Changes to the hr.jobs Table and View Results

Step 1 Make Changes to hr.jobs at str1.net

Step 2 Query and Describe the hr.jobs Table at str2.net

21
Simple Single Source Replication Example