Create a Streams Replication Configuration using the Streams Wizard

Completed the Preinstallation Tasks module

Completed the Installing the Oracle9i Database module

Documentation:

Start the Database Configuration Assistant to create the STRM database.

Start > Programs > Oracle - OraHome92 > Configuration and Migration Tools > Database Configuration Assistant

Click Next.

Select Create a database then click Next.

Select General Purpose then click Next.

Enter the following values. After entering the values, click Next.

Global Database Name: strm.world

SID: strm

Select Dedicated Server Mode, then click Next.

Select Custom. Some of the values for Custom are in Bytes and some are in M Bytes. Change all values to M Bytes. Use the following values.

Shared Pool:  24 M Bytes
Buffer Cache:  8 M Bytes
Java Pool:    16 M Bytes
Large Pool:    8 M Bytes
PGA:          10 M Bytes

After making the changes click on the Archive tab.

Click No, you do not want the recommend values.

Check both Archive Log Mode and Automatic archival. Change the filename format to the following:

Log Archive Filename Format: arc_%s.log

Click Finish.

Verify the Summary then click OK.

Database creation begins.

After creating the database, you will be prompted to set the password for both the SYS and SYSTEM users. Use the password oracle for both.

SYS Password:            oracle
Confirm SYS Password:    oracle
SYSTEM Password:         oracle
Confirm SYSTEM Password: oracle

Start and stop the OracleOraHome92Agent service. From the commnd prompt type:

agentctl stop
agentctl start

To discover the new database in Enterprise Manager the agent must be bounced which has been done and the node refreshed. Select Navigator > Refresh All Nodes. Select Finish and OK when discover is complete.

Select Start > Programs > Oracle - OraHome92 > Enterprise Manager Console. Log in as sysman/sysman to your Oracle Management Server.

From the Console, select the Configuration pulldown then select Preferences. Click the Preferred Credentials tab. Select your primary database (orcl.world) verify the settiting

Target Name: orcl.world
Username: sys
Password: oracle
Confirm:  oracle
Role:     SYSDBA

Select the newly create STRM database (strm.world) and set its credentials:

Target Name: strm.world
Username: sys
Password: oracle
Confirm:  oracle
Role:     SYSDBA

Slide down to the target type NODE and select the name of your NODE (compter name). Set the username and password for the node.

Target Name: <Your Node Name>
Username: ntoem
Password: ntoem
Confirm:  ntoem

Select Start > Programs > Oracle - OraHome92 > Enterprise Manager Console. Log in as sysman/sysman to your Oracle Management Server.

Right click on Network > Jobs and select Create Job...

When creating a job the job must be named and a type of job selected. Enter the name and target type as shown below:

Job Name:         Test Job
Target Type:      Node
Available Tagets: <select your node>

After entering the job name and changing the target type, select your node from the list of available tartgets then clik Add. The node will then appear in the list of selected targets.

Next select the Tasks tab.

From the list of available tasks select Run OS Command then click ADD. Select the Parameters tab.

On the Command line type:

dir

Click Submit.

The job can now be seen in the Active pane. Click on the History tab. Once the job terminates it will appear in the History pane.

If the NTOEM user and Preferred Credentials are configured correctly the status of the job in the History pane will be Complete. If the status is not complete please verify the steps in the Postinstallation module and the Preferred Credentials above.

In SQL*Plus connect to ORCL as SYS. Run script demo_setup_source.sql to create the DEMO user and populate the schema.

@demo_setup_source.sql

In a seperate SQL*Plus window connect to STRM as SYS. Run script demo_setup.sql to create the DEMO user. In the target database the schema being replicated must already exist. In the script the DEMO user is being created, however no objects are placed in the schema.

@demo_setup_target.sql

Start Enterprise Manager console and connect as sysman/sysman.

Select Network > Databases > orcl.world > Distributed > Streams. From the right hand window click on Setup Streams.

Click Next.

Enter strmadmin as the password for the user STRMADMIN. Verify the box next to 'Create a new Streams administrator' is checked. Click Next.

Choose strm.world from the drop down menu for the name of the Destination Database. Enter strmadmin as the password for the user STRMADMIN. Verify the box next to 'Create a new Streams administrator' is checked. Enter oracle for the SYS password. Click Next.

We want to capture both DML and DDL changes to the DEMO schema. Select DEMO from the Available Objects, then click the arrow (>) for it to appear in the Selected objects. Check the box next to 'Capture, propagate, and apply data definition language (DDL) changes.' Click Next.

Review the Summary and click Finish.

A warning will appear indicating the scripts the wizard executes have not been saved. We want to save the scripts. Click Yes.

Click OK to save the scripts in the default location: D:\oracle\ora92\sysman\report. The scripts are prefixed with the database global name.

When the wizard processing completes, click Close.

As part of the installation a job is executed to export the DEMO schema from the source database (orcl) and import into the target database (strm). Lets view the output of the job to verify it was successful.

Select Network > Jobs. Click on the History tab. Double click on the name that begins 'Export_orcl'. Select the listing with a 'Completed' status and click on Show Output.

After viewing the output click Close then Cancel.

Navigate to Network > Database > orcl.world > Distributed > Streams. Under Capture and Propagate sub trees are the new processes.

Take time to investigate the various tabs available for processes under capture and propagate.

The capture process created by the Streams Wizard is an Oracle background process with a system-generated ruleset name and rules for the ORDERS. The Streams Wizard automatically generates a Streams name for the capture process by prepending the Streams Administrator schema name to the word _CAPTURE. In this exercise, the Streams name for capture is STRMADMIN_CAPTURE If you use the Streams Wizard to add another table to this configuration, the new rules will be added to the ruleset for the STRMADMIN_CAPTURE process by default.

From the operating system point of view, the identifier for the capture background process is CPxx, where xx indicates the number of unique capture processes that have been configured on the database. Trace files generated for the capture process will include the OS process name (ie, ora_cp01_1245.trc)

After a capture process is initially configured and started on a database, it will continue to run. The state of the process will persist across database shutdown and startup. If the capture process was running when the database was shutdown, then it will automatically start when the database startup is performed. If the capture process was disabled or aborted before the shutdown, then it will remain in that state.

You can stop and start the capture process at any time. Stopping and starting the capture process will reset the statistics available for monitoring capture. These statistics are dynamic views into the capture process and are only available when the capture process is running. During a restart of the capture process, capture automatically determines where to start processing in the redo logs, based on knowledge of downstream apply site information and logminer checkpoints.

Some of the key statistics for monitoring the capture process are :
Total messages enqueued - Total number of messages from the redo log that satisfied rules in the capture rule set. This statistic shows the total number of messages that have been successfully staged into the Streams queue by the running capture process.

Capture message create time - Creation time of the most recent message scanned in the redo log. This statistic shows the creation time of the most recent message scanned from the redo logs. The creation time of a message is the time when a DML or DDL operation is performed at the site.

Enqueue message create time - Creation time of the last enqueued message. This statistic shows the creation time of the most recent message that was successfully staged into the Streams queue.

The apply process created by the Streams Wizard is an Oracle background process with a system-generated ruleset name and rules for the ORDERS. The Streams Wizard automatically generates a Streams name for the apply process by prepending the Streams Administrator schema name to the global name of the source database. In this exercise, the Streams name for apply is STRMADMIN_ORCL_US_ORACLE_COM If you use the Streams Wizard to add another table to this configuration, the new rules will be added to the ruleset for the STRMADMIN_ORCL_US_ORACLE_COM process by default.

From the operating system point of view, the identifier for the apply background process is APxx, where xx indicates the number of unique apply processes that have been configured on the database. Each Streams APPLY configuration actually consists of a minimum of 3 processes. In addition to the Apxx process, 2 or more parallel execution server processes (p000, p001, …) are configured. The APPLY reader(p000) is one of these server processes. It reads from the queue and passes the LCRs to the coordinator process (ap01). The coordinator process builds up the LCRs into transactions and passes them to one or more apply servers (p001,p002) depending on the setting of the apply parameter, PARALLELISM. Trace files generated for the apply process will include the OS process name (ie, ora_ap01_1247.trc, ora_p000_1248.trc, ora_p001_1250.trc)

After an apply process is initially configured and started on a database, it will continue to run, unless the process becomes disabled or aborted. The state of the process will persist across database shutdown and startup. If the apply process was running when the database was shutdown, then it will automatically start upon database startup. You can stop and start the apply process at any time. Stopping and starting the apply process will reset the statistics available for monitoring the apply processes. These statistics are dynamic views into the apply process and are only available when the apply process is running. During a restart of the apply process, capture automatically determines where to start processing.

By default, an apply process becomes disabled when an unresolved error is encountered during the apply process. The message that caused the error is placed in the Streams Error queue for later handling and disables itself. To allow the apply process to continue after an unresolved error has been encountered, change the Disable on Error parameter to N.

Click on the process which starts 'STRMADMIN_ORCL' and click the Parameter tab. Change the value of DISABLE_ON_ERROR to N, then click Apply.

Click on the Statistics tab. At the bottom of the frame is a Refresh button. The frame contains statistics on applying messages.

Take time to investigate the various other tabs.

There are 3 sets of statistics for monitoring the apply process.

Coordinator statistics:
Transactions applied - Number of transactions applied. This statistic shows the overall work of the apply engine.
Transactions received - Number of transactions received from the reader process. Each transaction can consist of one or more messages.
Transactions assigned- Number of transactions assigned to the apply servers

Reader statistics:
Total messages dequeued - Total number of messages dequeued from the Streams queue that satisified the rules in the apply rule set.

Dequeued message create time - Creation time of the message dequeued from the Streams queue. The creation time of a message is the time when a DML or DDL operation is performed at the source site.

Server Statistics: (one row per parallel execution server)
Total assigned - Total number of transactions assigned to this server
Total Messages Applied - Total number of messages applied by this server

Select Network > Databases > strm.world > Distributed > Streams. From the right hand window click on Setup Streams.

Click Next.

Enter strmadmin as the password for the user STRMADMIN. Click Next.

Choose orcl.world from the drop down menu for the name of the Destination Database. Enter strmadmin as the password for the user STRMADMIN. Uncheck the box next to 'Create a new Streams administrator'. The STRMADMIN user has already been created for the site. Click Next.

We want to capture both DML and DDL changes to the DEMO schema. Select DEMO from the Available Objects, then click the arrow (>) for it to appear in the Selected objects. Check the box next to 'Capture, propagate, and apply data definition language (DDL) changes.' Since the objects in the schema were exported from ORCL and imported into STRM during the setup of ORCL, the data itself (rows in the tables) have not changed from the initial configuration and thus does not need to be done again. The export and import tasks are still performed to set the Streams metadata necessary for the apply process, the instantiation scn from the STRM database. Uncheck the box in front of 'Copy existing data to the destination...'. Click Next.

Review the Summary and click Finish.

A warning will appear indicating the scripts the wizard executes have not been saved. We want to save the scripts. Click Yes.

Click OK to save the scripts in the default location: D:\oracle\ora92\sysman\report. The scripts are prefixed with the database global name.

When the wizard processing completes, click Close.

Navigate to Network > Database > strm.world > Distributed > Streams. Under Capture and Propagate sub trees are the new processes.

Take time to investigate the various tabs available for processes under capture and propagate. The Statistics tab contains statistics on changes captured and can be refreshed with the Refresh button at the botton of the frame.

Navigate to Network > Database > orcl.world > Distributed > Streams. Under the Apply sub tree is a new processes.

Click on the process which starts 'STRMADMIN_ORCL' and click the Parameter tab. Change the value of DISABLE_ON_ERROR to N, then click Apply.

Click on the Statistics tab. At the bottom of the frame is a Refresh buttom. The frame contains statistics on applying messages.

Take time to investigate the various other tabs

In SQL*Plus connect to ORCL as DEMO and run script demo_proc.sql.

@demo_proc.sql

The Order Entry application submits an order message into the Streams queue. In the next few steps, we will describe the steps to create the apply process with a message handler that will identify and retrieve the order messages from our application. The script ap_msghandler.sql performs the tasks described in steps 3 thru 6 below. But first, let's run the script @ap_msghandler.

In SQL*PLUS connect to ORACLE as STRMADMIN and run AP_msghandler.sql.

@ap_msghandler.sql

We first construct a PL/SQL procedure that takes the messages staged into the Streams queue by the Order Entry application of the type ORDER_EVENT_TYP as defined in the DEMO schema and converts them into a SQL insert statement into the DEMO.ORDERS table, as shown in the procedure apply_msghdlr_proc owned by the DEMO schema.

We will need to identify the messages to be selected for the apply process. To do this, we will create a set of rules (apply_msghdlr_rs is the rule set name) and add a rule to that rule set that selects only messages of the user-defined type, DEMO.ORDER_EVENT_TYP. To simplify the rule construction, we create an evaluation context for the rule set. The evaluation context allows us to define a table alias (tab) for the table 'STRMADMIN.STREAMS_QUEUE' within the rule definition.

Now that we have both the PL/SQL procedure and the ruleset defined, we can create the apply process. The apply process is given a name that we choose, apply_w_msghdlr. We also specify the rule set name (apply_msghdlr_rs) and the message handler (apply_msghdlr_proc ). By default, the apply process is configured with an apply tag, so that changes made by the apply process are ignored by the capture process when it is scanning the redo log. In this case, we DO want the changes that are made by the apply process to be captured. You will notice that we alter the apply process to remove the apply tag by setting the parameter remove_apply_tag to true.

The following code is a portion of ap_msghandler.sql from step 2, which creates the apply process with message handler.

/* create the apply process with the message handler */

begin

   dbms_apply_adm.create_apply (

      queue_name => 'streams_queue',
      apply_name => 'apply_w_msghdlr',
      rule_set_name => 'apply_msghdlr_rs',
      message_handler => 'demo.apply_msg_hdlr_proc');
end;
/

begin
   dbms_apply_adm.alter_apply(
      apply_name=>'apply_w_msghdlr',
      remove_apply_tag=>true);
end;
/

You can use the GUI to start the process, or execute the start_apply api.

Code sample from ap_msghandler.sql in step 2.

/* start the apply procedure */

begin

   dbms_apply_adm.start_apply (apply_name => 'apply_w_msghdlr');
end;
/

Configure Explicit Enqueue/Dequeue permissions Use SQL*PLUS to do this and use the script demo_grants_source.sql This script is run as STRMADMIN and sets up the privileges for demo to enq/deq into the STRMADMIN queue, as well as explicit secure queue privileges for DEMO to DEMO_AGENT and EXPLICIT_DQ agents.

@demo_grants_source.sql

Now we are ready to customize the apply process for the replicated DEMO.ORDERS table. You will recall that we want to maintain the data in the ORDERS table, and also requeue the LCR for another application. You will notice in the orders_dml_handler procedure that we call another procedure to enqueue the LCR into the Streams queue for use by another application. Towards the end of the procedure, you will notice a line "lcr.execute(true)". This is the command to perform the changes specified in the LCR on the specified table. A DML handler procedure can be written to apply the LCR as is, or in a modified state, or even to not apply the change.

Customization of apply processes that maintain tables is done by specifying a DML handler for the table. To configure a DML handler, you specify the table name as the object and the type as 'TABLE', the procedure name to be performed, and the type of operation (Insert/Update/Delete) for which this handler should be used. If the handler is to be used for all operations, then the procedure should be called 3 times, once each for INSERT, UPDATE, and DELETE.

@ap_dmlhandler.sql

Whenever a DML Handler is configured, any columns needed by the handler must be unconditionally logged at the source site. A supplemental log group is specified on a table and when configured with the ALWAYS clause meets this requirement. Unconditional supplemental logging of a log group will cause the before and after image of each column in the group to be logged everytime an update is made to a row.

This needs to be setup for both the ORCL and STRM instances.

connect sys/oracle@orcl.world as sysdba;

ALTER TABLE demo.orders 
   ADD SUPPLEMENTAL LOG 
   GROUP log_group_orders (part_number, status, delivery_date) ALWAYS;

connect sys/oracle@strm.world as sysdba;

ALTER TABLE demo.orders 
   ADD SUPPLEMENTAL LOG 
   GROUP log_group_orders (part_number, status, delivery_date) ALWAYS;

Use the submit_orders script to simulate the ORDER ENTRY application. This script expects a part number as input and will enqueue a message into the ORDER_ENTRY database (ORCL). The apply with a message handler will extract the message and turn it into an INSERT statement and commit it to the DEMO.ORDERS table. Enter a value of 1111.

@submit_order.sql

You may notice that the Total messages enqueued number increased by 2 after the refresh. The total messages enqueued for captured LCRs includes both the DML changes and the commits.

Navigate to Network > Database > orcl.world > Distributed > Streams > Capture. Click on the capture process then click on the Statistics tab. Notice the value for Total Messages Enqueued and the Enqueue Time. If the time is not current click on the Refresh button until the time is current.

Navigate to Network > Database > strm.world > Distributed > Streams > Apply. Click on the apply process then the Statistics tab. Refresh this frame until current.

Connect to the STRM instance as DEMO and SELECT from the ORDERS table, to verify the row was pushed across.

connect demo/demo@strm.world;

SELECT *
FROM   orders;

Now that the inserted row from ORCL appears at the STRM site, we can run the shipping application, accept_orders.sql, to ACCEPT the orders and schedule the delivery date. This script updates all PENDING rows to a status of Accepted and sets the delivery date 3 days from now.

From the STRM instance as DEMO execute the script accept_orders.sql to accept the order.

@accept_orders.sql

From the Order Entry (ORCL) instance connect as DEMO and SELECT from the ORDERS table to verify the updated row is pushed accoss.

connect demo/demo@strm.world;

SELECT *
FROM   orders;

We have seen the updated row appear at the ORCL site. Recall that we customized the apply so that the logical change record (LCR) was re-enqueued into the Streams queue for the notify application. Use the script dequeue to extract the messages from the queue.

Dequeue accepted entries. Run script dequeue.sql as DEMO on the ORACLE instance.

@dequeue.sql

Streams also can replicate DDL commands, such as CREATE TABLE, or ALTER TABLE... add column commands.

Create a new table in the ORCL instance as DEMO. The table will need to have a primary key.

connect demo/demo@orcl.world;

CREATE TABLE emp (
   emp_no   NUMBER PRIMARY KEY,
   emp_name VARCHAR2(20));

From the STRM instance as DEMO describe the table and insert a row. A commit is required for the row to be pushed.

connect demo/demo@strm.world;
set echo on

describe emp;

INSERT INTO emp VALUES (1234,'Streams');

COMMIT;

In the ORCL instance SELECT from the EMP table to verify the row was pushed.

connect demo/demo@orcl.world;

SELECT *
FROM   emp;

Create a Streams Replication Configuration using the Streams Wizard