12 Managing Oracle Streams Replication

Automatic Split and Merge

You can set two capture process parameters, split_threshold and merge_theshold, so that Oracle Streams performs split and merge operations automatically. When these parameters are set to specify automatic split and merge, an Oracle Scheduler job monitors the streams flowing from the capture process. When an Oracle Scheduler job identifies a problem with a stream, the job splits the problem stream off from the other streams flowing from the capture process. When a split operation is complete, a new Oracle Scheduler merge job monitors the split stream. When the problem is corrected, this job merges the stream back with the other streams.

When the split_threshold capture process parameter is set to INFINITE, automatic splitting is disabled. When the split_threshold parameter is not set to INFINITE, automatic splitting is enabled. Automatic splitting only occurs when communication with an apply process has been lost for the number of seconds specified in the split_threshold parameter. For example, communication with an apply process is lost when an apply process becomes disabled or a destination database goes down. Automatic splitting does not occur when one stream is processing changes slower than other streams.

When a stream is split, a cloned capture process is created. The cloned capture process might be enabled or disabled after the split depending on whether the configuration uses the combined capture and apply optimization:

• If the apply process is part of a combined capture and apply optimization, then the cloned capture process is enabled. The cloned capture process does not capture any changes until the apply process is enabled and communication is established with the apply process.

• If the apply process is not part of a combined capture and apply optimization, then the cloned capture process is disabled so that LCRs do not build up in a queue. When the apply process is enabled and the cloned stream can flow, you can start the cloned capture process manually.

The split stream is merged back with the original streams automatically when the difference, in seconds, between CAPTURE_MESSAGE_CREATE_TIME in the GV$STREAMS_CAPTURE view of the cloned capture process and the original capture process is less than or equal to the value specified for the merge_threshold capture process parameter. The CAPTURE_MESSAGE_CREATE_TIME records the time when a captured change was recorded in the redo log. If the difference is greater than the value specified by this capture process parameter, then automatic merge does not begin, and the value is recorded in the LAG column of the DBA_STREAMS_SPLIT_MERGE view.

When the capture process and the apply process for a stream run in different database instances, automatic split and merge is always possible for the stream. When a capture process and apply process for a stream run on the same database instance, automatic split and merge is possible only when all of the following conditions are met:

• The capture process and apply process use the same queue.

• The apply process has no errors in its error queue.

• The apply process is not an XStream outbound server.

• The apply process is stopped.

• No messages have spilled from the buffered queue to the hard disk.

Manual Split and Automatic Merge

When you split streams manually with the SPLIT_STREAMS procedure, the auto_merge_threshold procedure parameter gives you the option of automatically merging the stream back to the original capture process when the problem at the destination is corrected. After the apply process for the problem stream is accepting changes, you can start the cloned capture process and wait for the cloned capture process to catch up to the original capture process. When the cloned capture process nearly catches up, the auto_merge_threshold parameter setting determines whether the split stream is merged automatically or manually:

• When auto_merge_threshold is set to a positive number, the SPLIT_STREAMS procedure creates an Oracle Scheduler job with a schedule. The job runs the MERGE_STREAMS_JOB procedure and specifies a merge threshold equal to the value specified in the auto_merge_threshold parameter. You can modify the schedule for a job after it is created.

  In this case, the split stream is merged back with the original streams automatically when the difference, in seconds, between CAPTURE_MESSAGE_CREATE_TIME in the GV$STREAMS_CAPTURE view of the cloned capture process and the original capture process is less than or equal to the value specified for the auto_merge_threshold parameter. The CAPTURE_MESSAGE_CREATE_TIME records the time when a captured change was recorded in the redo log.

• When auto_merge_threshold is set to NULL or 0 (zero), the split stream is not merged back with the original streams automatically. To merge the split stream with the original streams, run the MERGE_STREAMS_JOB or MERGE_STREAMS procedure manually.

Manual Split and Merge With Generated Scripts

The SPLIT_STREAMS and MERGE_STREAMS procedures can perform actions directly or generate a script that performs the actions when the script is run. Using a procedure to perform actions directly is simpler than running a script, and the split or merge operation is performed immediately. However, you might choose to generate a script for the following reasons:

• You want to review the actions performed by the procedure before splitting or merging streams.

• You want to modify the script to customize its actions.

For example, you might choose to modify the script if you want to change the rules in the rule set for the cloned capture process. In some Oracle Streams replication environments, only a subset of the changes made to the source database are sent to each destination database, and each destination database might receive a different subset of the changes. In such an environment, you can modify the rule set for the cloned capture process so that it only captures changes that are propagated by the cloned propagation.

The perform_actions parameter in each procedure controls whether the procedure performs actions directly:

• To split or merge streams directly when you run one of these procedures, set the perform_actions parameter to TRUE. The default value for this parameter is TRUE.

• To generate a script when you run one of these procedures, set the perform_actions parameter to FALSE, and use the script_name and script_directory_object parameters to specify the name and location of the script.

Splitting and Merging an Oracle Streams Destination Automatically

Before reviewing this example, see the following sections:

• "Automatic Split and Merge" for conceptual information

• "Examples That Split and Merge Oracle Streams" for assumptions about the Oracle Streams replication environment in this example

Complete the following steps to split and merge a stream automatically:

1. In SQL*Plus, connect as the Oracle Streams administrator to the database with the capture process.

   See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

2. Ensure that the following parameters are set properly for the strms_capture capture process to enable automatic split and merge:

   • split_threshold: Ensure that this parameter is not set to INFINITE. The default setting for this parameter is 1800 seconds.

   • merge_threshold: Ensure that this parameter is not set to a negative value. The default setting for this parameter is 60 seconds.

   To check the settings for these parameters, query the DBA_CAPTURE_PARAMETERS view. See Oracle Streams Concepts and Administration for instructions.

3. If you must reset one or both of the capture process parameters described in Step 2, then use Oracle Enterprise Manager Cloud Control or the SET_PARAMETER procedure in the DBMS_CAPTURE_ADM package to reset the parameters. See Oracle Streams Concepts and Administration for instructions about using the SET_PARAMETER procedure.

4. Monitor the DBA_STREAMS_SPLIT_MERGE view periodically to check whether an automatic split and merge operation is in process.

   When an automatic split occurs, certain components, such as the capture process, queue, and propagation, are cloned, and each is given a system-generated name. The DBA_STREAMS_SPLIT_MERGE view contains the name of each cloned component, and other information about the split and merge operation.

   Query the DBA_STREAMS_SPLIT_MERGE view to determine whether a stream has been split off from the original capture process:

   COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original|Capture|Process' FORMAT A10
   COLUMN ACTION_TYPE HEADING 'Action|Type' FORMAT A7
   COLUMN STATUS_UPDATE_TIME HEADING 'Status|Update|Time' FORMAT A15
   COLUMN STATUS HEADING 'Status' FORMAT A16
   COLUMN JOB_NEXT_RUN_DATE HEADING 'Next Job|Run Date' FORMAT A20
    
   SELECT ORIGINAL_CAPTURE_NAME,
          ACTION_TYPE,
          STATUS_UPDATE_TIME, 
          STATUS, 
          JOB_NEXT_RUN_DATE 
     FROM DBA_STREAMS_SPLIT_MERGE 
     ORDER BY STATUS_UPDATE_TIME DESC;
   

   If a stream has been split off from the original capture process, then your output looks similar to the following:

   Original           Status
   Capture    Action  Update                           Next Job
   Process    Type    Time            Status           Run Date
   ---------- ------- --------------- ---------------- --------------------
   DB$CAP     MERGE   01-APR-09 06.49 NOTHING TO MERGE 01-APR-09 06.54.29.0
                      .29.204804 AM                    00000 AM -07:00
   DB$CAP     SPLIT   01-APR-09 06.49 SPLIT DONE       01-APR-09 06.47.59.0
                      .17.389146 AM                    00000 AM -07:00
   

   This output shows that an automatic split was performed. The merge job was run at 01-APR-09 06.49.29.204804 AM, but the status shows NOTHING TO MERGE because the split stream is not ready to merge yet. The SPLIT DONE status indicates that the stream was split off at the following date and time: 01-APR-09 06.49.17.389146 AM.

5. After an automatic split is performed, correct the problem with the destination. The problem is corrected when the apply process at the destination database can accept changes from the cloned capture process. An Oracle Scheduler job performs an automatic merge when the problem is corrected.

6. If the cloned capture process is disabled, then start the cloned capture process. The cloned capture process is disabled only if the stream is not a combined capture and apply optimization. See Oracle Streams Concepts and Administration for instructions for starting a capture process.

The cloned capture process captures changes that satisfy its rule sets. These changes are sent to the apply process.

During this time, an Oracle Scheduler job runs the MERGE_STREAMS_JOB procedure according to its schedule. The MERGE_STREAMS_JOB procedure queries the CAPTURE_MESSAGE_CREATE_TIME in the GV$STREAMS_CAPTURE view. When the difference between CAPTURE_MESSAGE_CREATE_TIME of the cloned capture process and the original capture process is less than or equal to the value of the merge_threshold capture process parameter, the MERGE_STREAMS_JOB procedure determines that the streams are ready to merge. The MERGE_STREAMS_JOB procedure runs the MERGE_STREAMS procedure automatically to merge the streams back together.

The LAG column in the DBA_STREAMS_SPLIT_MERGE view tracks the time in seconds that the cloned capture process lags behind the original capture process. The following query displays the lag time:

COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original Capture Process' FORMAT A25
COLUMN CLONED_CAPTURE_NAME HEADING 'Cloned Capture Process' FORMAT A25
COLUMN LAG HEADING 'Lag' FORMAT 999999999999999
 
SELECT ORIGINAL_CAPTURE_NAME,
       CLONED_CAPTURE_NAME,
       LAG
 FROM DBA_STREAMS_SPLIT_MERGE
 WHERE ACTION_TYPE = 'MERGE';

Your output looks similar to the following:

Original Capture Process  Cloned Capture Process                 Lag
------------------------- ------------------------- ----------------
DB$CAP                    CLONED$_DB$CAP_5                      2048

This output shows that there is a lag of 2,048 seconds between the CAPTURE_MESSAGE_CREATE_TIME values for the original capture process and the cloned capture process. When the cloned capture process is within the threshold, the merge job can start the MERGE_STREAMS procedure. By default, the merge threshold is 60 seconds.

The MERGE_STREAMS procedure performs the following actions:

• Stops the cloned capture process.

• Re-creates the original propagation called strms_prop_a.

• Drops the cloned propagation.

• Drops the cloned capture process.

• Drops the cloned queue.

Repeat the query in Step 4 periodically to monitor the split and merge operation. After the merge operation is complete, the output for this query is similar to the following:

Original           Status
Capture    Action  Update                           Next Job
Process    Type    Time            Status           Run Date
---------- ------- --------------- ---------------- --------------------
DB$CAP     MERGE   01-APR-09 07.32 NOTHING TO MERGE 01-APR-09 07.37.04.0
                   .04.820795 AM                    00000 AM -07:00
DB$CAP     MONITOR 01-APR-09 07.32 MERGE DONE       01-APR-09 07.36.20.0
                   .04.434925 AM                    00000 AM -07:00
DB$CAP     SPLIT   01-APR-09 06.49 SPLIT DONE       01-APR-09 06.47.59.0
                   .17.389146 AM                    00000 AM -07:00

This output shows that the split stream was merged back into the original capture process at the following date an time: 01-APR-09 07.32.04.434925 AM. The next status shows NOTHING TO MERGE because there are no remaining split streams.

After the streams are merged, the Oracle Streams replication environment has the same components as it had before the split and merge operation. Information about the completed split and merge operation is stored in the DBA_STREAMS_SPLIT_MERGE_HIST for future reference.

Splitting an Oracle Streams Destination Manually and Merging It Automatically

Before reviewing this example, see the following sections:

• "Manual Split and Automatic Merge" for conceptual information

• "Examples That Split and Merge Oracle Streams" for assumptions about the Oracle Streams replication environment in this example

The example in this section splits the stream manually and merges it automatically. That is, the perform_actions parameter is set to TRUE in the SPLIT_STREAMS procedure. Also, the example merges the streams automatically at the appropriate time because the auto_merge_threshold parameter is to set a positive number (60) in the SPLIT_STREAMS procedure.

Complete the following steps to split streams directly and merge streams automatically:

1. In SQL*Plus, connect as the Oracle Streams administrator to the database with the capture process.

   See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

2. Run the following procedure to split the stream flowing through propagation strms_prop_a from the other propagations flowing from the strms_capture capture process:

   DECLARE
       schedule_name  VARCHAR2(30);
       job_name       VARCHAR2(30);
   BEGIN
       schedule_name := 'merge_job1_schedule';
       job_name      := 'merge_job1';
     DBMS_STREAMS_ADM.SPLIT_STREAMS(
       propagation_name        => 'strms_prop_a',
       cloned_propagation_name => 'cloned_prop_a',
       cloned_queue_name       => 'cloned_queue',
       cloned_capture_name     => 'cloned_capture',
       perform_actions         => TRUE,
       auto_merge_threshold    => 60,
       schedule_name           => schedule_name,
       merge_job_name          => job_name);
   END;
   /
   

   Running this procedure performs the following actions:

   • Creates a new queue called cloned_queue.

   • Creates a new propagation called cloned_prop_a that propagates messages from the cloned_queue queue to the existing destination queue used by the strms_prop_a propagation. The cloned propagation cloned_prop_a uses the same rule set as the original propagation strms_prop_a.

   • Stops the capture process strms_capture.

   • Queries the acknowledge SCN for the original propagation strms_prop_a. The acknowledged SCN is the last SCN acknowledged by the apply process that applies the changes sent by the propagation. The ACKED_SCN value in the DBA_PROPAGATION view shows the acknowledged SCN for a propagation.

   • Creates a new capture process called cloned_capture. The start SCN for cloned_capture is set to the value of the acknowledged SCN for the strms_prop_a propagation. The cloned capture process cloned_capture uses the same rule set as the original capture process strms_capture.

   • Drops the original propagation strms_prop_a.

   • Starts the original capture process strms_capture with the start SCN set to the value of the acknowledged SCN for the strms_prop_a propagation.

   • Creates an Oracle Scheduler job named merge_job1 with a schedule named merge_job1_schedule. Both the job and the schedule are owned by the user who ran the SPLIT_STREAMS procedure. The schedule starts to run when the SPLIT_STREAMS procedure completes. The system defines the initial schedule, but you can modify it in the same way that you would modify any Oracle Scheduler job. See Oracle Database Administrator's Guide for instructions.

3. Correct the problem with the destination of cloned_prop_a. The problem is corrected when the apply process at the destination database can accept changes from the cloned capture process.

4. While connected as the Oracle Streams administrator, start the cloned capture process by running the following procedure:

   exec DBMS_CAPTURE_ADM.START_CAPTURE('cloned_capture');
   

After the cloned capture process cloned_capture starts running, it captures changes that satisfy its rule sets from the acknowledged SCN forward. These changes are propagated by the cloned_prop_a propagation and processed by the apply process at the destination database.

During this time, the Oracle Scheduler job runs the MERGE_STREAMS_JOB procedure according to its schedule. The MERGE_STREAMS_JOB procedure queries the CAPTURE_MESSAGE_CREATE_TIME in the GV$STREAMS_CAPTURE view. When the difference between CAPTURE_MESSAGE_CREATE_TIME of the cloned capture process cloned_capture and the original capture process strms_capture is less than or equal 60 seconds, the MERGE_STREAMS_JOB procedure determines that the streams are ready to merge. The MERGE_STREAMS_JOB procedure runs the MERGE_STREAMS procedure automatically to merge the streams back together.

The following query displays the CAPTURE_MESSAGE_CREATE_TIME for the original capture process and cloned capture process:

COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A17
COLUMN STATE HEADING 'State' FORMAT A20
COLUMN CREATE_MESSAGE HEADING 'Last Message|Create Time'
 
SELECT CAPTURE_NAME,
 STATE,
 TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_MESSAGE
 FROM V$STREAMS_CAPTURE;

Your output looks similar to the following:

Capture                                Last Message
Name              State                Create Time
----------------- -------------------- -----------------
DB$CAP            CAPTURING CHANGES    07:22:55 04/01/09
CLONED$_DB$CAP_5  CAPTURING CHANGES    06:50:39 04/01/09

This output shows that there is more than a 30 minute difference between the CAPTURE_MESSAGE_CREATE_TIME values for the original capture process and the cloned capture process. When the cloned capture process is within the threshold, the merge job can start the MERGE_STREAMS procedure. By default, the merge threshold is 60 seconds.

The MERGE_STREAMS procedure performs the following actions:

• Stops the cloned capture process cloned_capture.

• Re-creates the propagation called strms_prop_a.

• Drops the cloned propagation cloned_prop_a.

• Drops the cloned capture process cloned_capture.

• Drops the cloned queue cloned_queue.

After the streams are merged, the Oracle Streams replication environment has the same components as it had before the split and merge operation. Information about the completed split and merge operation is stored in the DBA_STREAMS_SPLIT_MERGE_HIST for future reference.

Splitting and Merging an Oracle Streams Destination Manually With Scripts

Before reviewing this example, see the following sections:

• "Manual Split and Merge With Generated Scripts" for conceptual information

• "Examples That Split and Merge Oracle Streams" for assumptions about the Oracle Streams replication environment in this example

The example in this section splits and merges streams by generating and running scripts. That is, the perform_actions parameter is set to FALSE in the SPLIT_STREAMS procedure. Also, the example merges the streams manually at the appropriate time because the auto_merge_threshold parameter is set to NULL in the SPLIT_STREAMS procedure.

Complete the following steps to use scripts to split and merge streams:

1. In SQL*Plus, connect as the Oracle Streams administrator to the database with the capture process.

   See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

2. If it does not already exist, then create a directory object named db_dir to hold the scripts generated by the procedures:

   CREATE DIRECTORY db_dir AS '/usr/db_files';
   

3. Run the following procedure to generate a script to split the streams:

   DECLARE
       schedule_name  VARCHAR2(30);
       job_name       VARCHAR2(30);
   BEGIN
     DBMS_STREAMS_ADM.SPLIT_STREAMS(
       propagation_name        => 'strms_prop_a',
       cloned_propagation_name => 'cloned_prop_a',
       cloned_queue_name       => 'cloned_queue',
       cloned_capture_name     => 'cloned_capture',
       perform_actions         => FALSE,
       script_name             => 'split.sql',
       script_directory_object => 'db_dir',
       auto_merge_threshold    => NULL,
       schedule_name           => schedule_name,
       merge_job_name          => job_name);
   END;
   /
   

   Running this procedure generates the split.sql script. The script contains the actions that will split the stream flowing through propagation strms_prop_a from the other propagations flowing from the strms_capture capture process.

4. Go to the directory used by the db_dir directory object, and open the split.sql script with a text editor.

5. Examine the script and make modifications, if necessary.

6. Save and close the script.

7. While connected as the Oracle Streams administrator in SQL*Plus, run the script:

   @/usr/db_files/split.sql
   

   Running the script performs the following actions:

   • Runs the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package to create a queue called cloned_queue.

   • Runs the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to create a propagation called cloned_prop_a. This new propagation propagates messages from the cloned_queue queue to the existing destination queue used by the strms_prop_a propagation. The cloned propagation cloned_prop_a uses the same rule set as the original propagation strms_prop_a.

     The CREATE_PROPAGATION procedure sets the original_propagation_name parameter to strms_prop_a and the auto_merge_threshold parameter to NULL.

   • Runs the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop the capture process strms_capture.

   • Queries the acknowledge SCN for the original propagation strms_prop_a. The acknowledged SCN is the last SCN acknowledged by the apply process that applies the changes sent by the propagation. The ACKED_SCN value in the DBA_PROPAGATION view shows the acknowledged SCN for a propagation.

   • Runs the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a capture process called cloned_capture. The start SCN for cloned_capture is set to the value of the acknowledged SCN for the strms_prop_a propagation. The cloned capture process cloned_capture uses the same rule set as the original capture process strms_capture.

   • Runs the DROP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to drop the original propagation strms_prop_a.

   • Runs the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package to start the original capture process strms_capture with the start SCN set to the value of the acknowledged SCN for the strms_prop_a propagation.

8. Correct the problem with the destination of cloned_prop_a. The problem is corrected when the apply process at the destination database can accept changes from the cloned capture process.

9. While connected as the Oracle Streams administrator, start the cloned capture process by running the following procedure:

   exec DBMS_CAPTURE_ADM.START_CAPTURE('cloned_capture');
   

10. Monitor the Oracle Streams replication environment until the cloned capture process catches up to, or nearly catches up to, the original capture process. Specifically, query the CAPTURE_MESSAGE_CREATION_TIME column in the GV$STREAMS_CAPTURE view for each capture process.

    Run the following query to check the CAPTURE_MESSAGE_CREATE_TIME for each capture process periodically:

    SELECT CAPTURE_NAME,
           TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') 
       FROM GV$STREAMS_CAPTURE;
    

    Do not move on to the next step until the difference between CAPTURE_MESSAGE_CREATE_TIME of the cloned capture process cloned_capture and the original capture process strms_capture is relatively small.

11. Run the following procedure to generate a script to merge the streams:

    BEGIN
      DBMS_STREAMS_ADM.MERGE_STREAMS(
        cloned_propagation_name => 'cloned_prop_a',
        perform_actions         => FALSE,
        script_name             => 'merge.sql',
        script_directory_object => 'db_dir');
    END;
    /
    

    Running this procedure generates the merge.sql script. The script contains the actions that will merge the stream flowing through propagation cloned_prop_a with the other propagations flowing from the strms_capture capture process.

12. Go to the directory used by the db_dir directory object, and open the merge.sql script with a text editor.

13. Examine the script and make modifications, if necessary.

14. Save and close the script.

15. While connected as the Oracle Streams administrator in SQL*Plus, run the script:

    @/usr/db_files/merge.sql
    

    Running the script performs the following actions:

    • Runs the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop the cloned capture process cloned_capture.

    • Runs the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to stop the original capture process strms_capture.

    • Runs the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to re-create the propagation called strms_prop_a.

    • Starts the original capture process strms_capture from the lower SCN value of these two SCN values:

      • The acknowledged SCN of the cloned propagation cloned_prop_a.

      • The lowest acknowledged SCN of the other propagations that propagate changes captured by the original capture process (propagations strms_prop_b and strms_prop_c in this example).

      When the strms_capture capture process is started, it might recapture changes that it already captured, or it might capture changes that were already captured by the cloned capture process cloned_capture. In either case, the relevant apply processes will discard any duplicate changes they receive.

    • Runs the DROP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to drop the cloned propagation cloned_prop_a.

    • Runs the DROP_CAPTURE procedure in the DBMS_CAPTURE_ADM package to drop the cloned capture process cloned_capture.

    • Runs the REMOVE_QUEUE procedure in the DBMS_STREAMS_ADM package to drop the cloned queue cloned_queue.

After the script runs successfully, the streams are merged, and the Oracle Streams replication environment has the same components as it had before the split and merge operation. Information about the completed split and merge operation is stored in the DBA_STREAMS_SPLIT_MERGE_HIST for future reference.

Resetting the Start SCN for the Existing Capture Process to Perform Recovery

If you decide to reset the start SCN for the existing capture process to perform point-in-time recovery, then complete the following steps:

1. If the destination database is also a source database in a multiple-source Oracle Streams environment, then complete the actions described in "Performing Point-in-Time Recovery in a Multiple-Source Environment".

2. Drop the propagation that propagates changes from the source queue at the source database to the destination queue at the destination database. Use the DROP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to drop the propagation.

   If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the propagation at each intermediate database in the path to the destination database, including the propagation at the source database.

   Do not drop the rule sets used by the propagations you drop.

   If the existing capture process is a downstream capture process that is configured at the destination database, then the downstream capture process is recovered to the same point-in-time as the destination database when you perform point-in-time recovery in Step 3. In this case, the remaining steps in this section after Step 3 are not required. Ensure that the required redo log files are available to the capture process.

   Note:

   You must drop the appropriate propagation(s). Disabling them is not sufficient. You will re-create the propagation(s) in Step 7, and dropping them now ensures that only LCRs created after resetting the start SCN for the capture process are propagated.

   See Also:

   Oracle Streams Concepts and Administration for more information about directed networks

3. Perform the point-in-time recovery at the destination database.

4. Query for the oldest message number (oldest SCN) from the source database for the apply process at the destination database. Make a note of the results of the query. The oldest message number is the earliest system change number (SCN) that might need to be applied.

   The following query at a destination database lists the oldest message number for each apply process:

   SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
   

5. Stop the existing capture process using the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

6. Reset the start SCN of the existing capture process.

   To reset the start SCN for an existing capture process, run the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package and set the start_scn parameter to the value you recorded from the query in Step 4. For example, to reset the start SCN for a capture process named strm01_capture to the value 829381993, run the following ALTER_CAPTURE procedure:

   BEGIN
     DBMS_CAPTURE_ADM.ALTER_CAPTURE(
       capture_name  =>  'strm01_capture',
       start_scn     =>  829381993);
   END;
   /
   

7. If you are not using directed networks between the source database and destination database, then create a new propagation to propagate changes from the source queue to the destination queue using the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package. Specify any rule sets used by the original propagation when you create the propagation.

   If you are using directed networks, and there are intermediate databases between the source database and destination database, then create a new propagation at each intermediate database in the path to the destination database, including the propagation at the source database.

8. Start the existing capture process using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

Creating a New Capture Process to Perform Recovery

If you decide to create a capture process to perform point-in-time recovery, then complete the following steps:

1. If the destination database is also a source database in a multiple-source Oracle Streams environment, then complete the actions described in "Performing Point-in-Time Recovery in a Multiple-Source Environment".

2. If you are not using directed networks between the source database and destination database, then drop the propagation that propagates changes from the source queue at the source database to the destination queue at the destination database. Use the DROP_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to drop the propagation.

   If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the propagation that propagates LCRs between the last intermediate database and the destination database. You do not need to drop the propagations at the other intermediate databases nor at the source database.

   Note:

   You must drop the appropriate propagation. Disabling it is not sufficient.

   See Also:

   Oracle Streams Concepts and Administration for more information about directed networks

3. Perform the point-in-time recovery at the destination database.

4. Query for the oldest message number (oldest SCN) from the source database for the apply process at the destination database. Make a note of the results of the query. The oldest message number is the earliest system change number (SCN) that might need to be applied.

   The following query at a destination database lists the oldest message number for each apply process:

   SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
   

5. Create a queue at the source database to be used by the capture process using the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package.

   If you are using directed networks, and there are intermediate databases between the source database and destination database, then create a queue at each intermediate database in the path to the destination database, including the new queue at the source database. Do not create a new queue at the destination database.

6. If you are not using directed networks between the source database and destination database, then create a new propagation to propagate changes from the source queue created in Step 5 to the destination queue using the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package. Specify any rule sets used by the original propagation when you create the propagation.

   If you are using directed networks, and there are intermediate databases between the source database and destination database, then create a propagation at each intermediate database in the path to the destination database, including the propagation from the source database to the first intermediate database. These propagations propagate changes captured by the capture process you will create in Step 7 between the queues created in Step 5.

7. Create a new capture process at the source database using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package. Set the source_queue parameter to the local queue you created in Step 5 and the start_scn parameter to the value you recorded from the query in Step 4. Also, specify any rule sets used by the original capture process. If the rule sets used by the original capture process instruct the capture process to capture changes that should not be sent to the destination database that was recovered, then you can create and use smaller, customized rule sets that share some rules with the original rule sets.

8. Start the capture process you created in Step 7 using the START_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

9. When the oldest message number of the apply process at the recovered database is approaching the capture number of the original capture process at the source database, stop the original capture process using the STOP_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

   At the destination database, you can use the following query to determine the oldest message number from the source database for the apply process:

   SELECT APPLY_NAME, OLDEST_MESSAGE_NUMBER FROM DBA_APPLY_PROGRESS;
   

   At the source database, you can use the following query to determine the capture number of the original capture process:

   SELECT CAPTURE_NAME, CAPTURE_MESSAGE_NUMBER FROM V$STREAMS_CAPTURE;
   

10. When the oldest message number of the apply process at the recovered database is beyond the capture number of the original capture process at the source database, drop the new capture process created in Step 7.

11. If you are not using directed networks between the source database and destination database, then drop the new propagation created in Step 6.

    If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the new propagation at each intermediate database in the path to the destination database, including the new propagation at the source database.

12. If you are not using directed networks between the source database and destination database, then remove the queue created in Step 5.

    If you are using directed networks, and there are intermediate databases between the source database and destination database, then drop the new queue at each intermediate database in the path to the destination database, including the new queue at the source database. Do not drop the queue at the destination database.

13. If you are not using directed networks between the source database and destination database, then create a propagation that propagates changes from the original source queue at the source database to the destination queue at the destination database. Use the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to create the propagation. Specify any rule sets used by the original propagation when you create the propagation.

    If you are using directed networks, and there are intermediate databases between the source database and destination database, then re-create the propagation from the last intermediate database to the destination database. You dropped this propagation in Step 2.

14. Start the capture process you stopped in Step 9.

All of the steps after Step 8 can be deferred to a later time, or they can be done as soon as the condition described in Step 9 is met.