9 Managing Capture, Propagation, and Apply

Specifying Table Supplemental Logging Using Unconditional Log Groups

The following sections describe creating an unconditional log group:

• Specifying an Unconditional Supplemental Log Group for Primary Key Column(s)

• Specifying an Unconditional Supplemental Log Group for All Table Columns

• Specifying an Unconditional Supplemental Log Group that Includes Selected Columns

ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

ALTER TABLE hr.regions ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP log_group_dep_pk
  (department_id, manager_id) ALWAYS;

Specifying Table Supplemental Logging Using Conditional Log Groups

The following sections describe creating a conditional log group:

• Specifying a Conditional Log Group Using the ADD SUPPLEMENTAL LOG DATA Clause

• Specifying a Conditional Log Group Using the ADD SUPPLEMENTAL LOG GROUP Clause

ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA 
  (UNIQUE, FOREIGN KEY) COLUMNS;

ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG GROUP log_group_jobs_cr 
  (min_salary, max_salary);

Dropping a Supplemental Log Group

To drop a conditional or unconditional supplemental log group, use the DROP SUPPLEMENTAL LOG GROUP clause in the ALTER TABLE statement. For example, to drop a supplemental log group named log_group_jobs_cr, run the following statement:

ALTER TABLE hr.jobs DROP SUPPLEMENTAL LOG GROUP log_group_jobs_cr;

Specifying Database Supplemental Logging of Key Columns

You also have the option of specifying supplemental logging for all primary key, unique key, bitmap index, and foreign key columns in a source database. You might choose this option if you configure a capture process to capture changes to an entire database. To specify supplemental logging for all primary key, unique key, bitmap index, and foreign key columns in a source database, issue the following SQL statement:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA 
   (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

If your primary key, unique key, bitmap index, and foreign key columns are the same at all source and destination databases, then running this command at the source database provides the supplemental logging needed for primary key, unique key, bitmap index, and foreign key columns at all destination databases. When you specify the PRIMARY KEY option, all columns of a row's primary key are placed in the redo log file any time the table is modified (unconditional logging). When you specify the UNIQUE option, any columns in a row's unique key and bitmap index are placed in the redo log file if any column belonging to the unique key or bitmap index is modified (conditional logging). When you specify the FOREIGN KEY option, all columns of a row's foreign key are placed in the redo log file if any column belonging to the foreign key is modified (conditional logging).

You can omit one or more of these options. For example, if you do not want to supplementally log all of the foreign key columns in the database, then you can omit the FOREIGN KEY option, as in the following example:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA 
   (PRIMARY KEY, UNIQUE) COLUMNS;

In additional to PRIMARY KEY, UNIQUE, and FOREIGN KEY, you can also use the ALL option. The ALL option specifies that, when a row is changed, all the columns of that row (except for LOB, LONG, LONG RAW, user-defined type, and Oracle-supplied type columns) are placed in the redo log file (unconditional logging).

Supplemental logging statements are cumulative. If you issue two consecutive ALTER DATABASE ADD SUPPLEMENTAL LOG DATA commands, each with a different identification key, then both keys are supplementally logged.

Dropping Database Supplemental Logging of Key Columns

To drop supplemental logging for all primary key, unique key, bitmap index, and foreign key columns in a source database, issue the ALTER DATABASE DROP SUPPLEMENTAL LOG DATA statement. To drop database supplemental logging for all primary key, unique key, bitmap index, and foreign key columns, issue the following SQL statement:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA 
  (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

Setting Substitute Key Columns for a Table

When an apply process applies changes to a table, substitute key columns can either replace the primary key columns for a table that has a primary key or act as the primary key columns for a table that does not have a primary key. Set the substitute key columns for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. This setting applies to all of the apply processes that apply local changes to the database.

For example, to set the substitute key columns for the hr.employees table to the first_name, last_name, and hire_date columns, replacing the employee_id column, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_KEY_COLUMNS(
    object_name         => 'hr.employees',
    column_list         => 'first_name,last_name,hire_date');
END;
/

Removing the Substitute Key Columns for a Table

You remove the substitute key columns for a table by specifying NULL for the column_list or column_table parameter in the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. If the table has a primary key, then the table's primary key is used by any apply process for local changes to the database after you remove the substitute primary key.

For example, to remove the substitute key columns for the hr.employees table, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_KEY_COLUMNS(
    object_name  => 'hr.employees',
    column_list  => NULL);
END;
/

Creating a DML Handler

A DML handler must have the following signature:

PROCEDURE user_procedure (
   parameter_name   IN  ANYDATA);

Here, user_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is an ANYDATA encapsulation of a row LCR.

The following restrictions apply to the user procedure:

• Do not execute COMMIT or ROLLBACK statements. Doing so can endanger the consistency of the transaction that contains the LCR.

• If you are manipulating a row using the EXECUTE member procedure for the row LCR, then do not attempt to manipulate more than one row in a row operation. You must construct and execute manually any DML statements that manipulate more than one row.

• If the command type is UPDATE or DELETE, then row operations resubmitted using the EXECUTE member procedure for the LCR must include the entire key in the list of old values. The key is the primary key or the smallest unique key that has at least one NOT NULL column, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure. If there is no specified key, then the key consists of all non LOB, non LONG, and non LONG RAW columns.

• If the command type is INSERT, then row operations resubmitted using the EXECUTE member procedure for the LCR should include the entire key in the list of new values. Otherwise, duplicate rows are possible. The key is the primary key or the smallest unique key that has at least one NOT NULL column, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure. If there is no specified key, then the key consists of all non LOB, non LONG, and non LONG RAW columns.

A DML handler can be used for any customized processing of row LCRs. For example, the handler can modify an LCR and then execute it using the EXECUTE member procedure for the LCR. When you execute a row LCR in a DML handler, the apply process applies the LCR without calling the DML handler again.

You can also use a DML handler for recording the history of DML changes. For example, a DML handler can insert information about an LCR it processes into a table and then apply the LCR using the EXECUTE member procedure. To create such a DML handler, first create a table to hold the history information:

CREATE TABLE strmadmin.history_row_lcrs(
  timestamp             DATE,
  source_database_name  VARCHAR2(128),
  command_type          VARCHAR2(30),
  object_owner          VARCHAR2(32),
  object_name           VARCHAR2(32),
  tag                   RAW(10),
  transaction_id        VARCHAR2(10),
  scn                   NUMBER,
  commit_scn            NUMBER,
  old_values            SYS.LCR$_ROW_LIST,
  new_values            SYS.LCR$_ROW_LIST)
    NESTED TABLE old_values STORE AS old_values_ntab
    NESTED TABLE new_values STORE AS new_values_ntab;

CREATE OR REPLACE PROCEDURE history_dml(in_any IN ANYDATA)  
 IS
  lcr   SYS.LCR$_ROW_RECORD;
  rc    PLS_INTEGER;
 BEGIN
  -- Access the LCR
  rc := in_any.GETOBJECT(lcr);
  -- Insert information about the LCR into the history_row_lcrs table
  INSERT INTO strmadmin.history_row_lcrs VALUES 
   (SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(),   
    lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_TAG(), 
    lcr.GET_TRANSACTION_ID(), lcr.GET_SCN(), lcr.GET_COMMIT_SCN,
    lcr.GET_VALUES('old'), lcr.GET_VALUES('new', 'n'));
  --  Apply row LCR
  lcr.EXECUTE(TRUE);
END;
/

Setting a DML Handler

A DML handler processes each row LCR dequeued by any apply process that contains a specific operation on a specific table. You can specify multiple DML handlers on the same table, to handle different operations on the table. All apply processes that apply changes to the specified table in the local database use the specified DML handler.

Set the DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the DML handler for UPDATE operations on the hr.locations table. Therefore, when any apply process that applies changes locally dequeues a row LCR containing an UPDATE operation on the hr.locations table, the apply process sends the row LCR to the history_dml PL/SQL procedure in the strmadmin schema for processing. The apply process does not apply a row LCR containing such a change directly.

In this example, the apply_name parameter is set to NULL. Therefore, the DML handler is a general DML handler that is used by all of the apply processes in the database.

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'hr.locations',
    object_type         => 'TABLE',
    operation_name      => 'UPDATE',
    error_handler       => FALSE,
    user_procedure      => 'strmadmin.history_dml',
    apply_database_link => NULL,
    apply_name          => NULL);
END;
/

Unsetting a DML Handler

You unset a DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. When you run that procedure, set the user_procedure parameter to NULL for a specific operation on a specific table. After the DML handler is unset, any apply process that applies changes locally will apply a row LCR containing such a change directly.

For example, the following procedure unsets the DML handler for UPDATE operations on the hr.locations table:

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name    => 'hr.locations',
    object_type    => 'TABLE',
    operation_name => 'UPDATE',
    error_handler  => FALSE,
    user_procedure => NULL,
    apply_name     => NULL);
END;
/

Creating a DDL Handler for an Apply Process

A DDL handler must have the following signature:

PROCEDURE handler_procedure (
   parameter_name   IN  ANYDATA);

Here, handler_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is an ANYDATA encapsulation of a DDL LCR.

A DDL handler can be used for any customized processing of DDL LCRs. For example, the handler can modify the LCR and then execute it using the EXECUTE member procedure for the LCR. When you execute a DDL LCR in a DDL handler, the apply process applies the LCR without calling the DDL handler again.

You can also use a DDL handler to record the history of DDL changes. For example, a DDL handler can insert information about an LCR it processes into a table and then apply the LCR using the EXECUTE member procedure.

To create such a DDL handler, first create a table to hold the history information:

CREATE TABLE strmadmin.history_ddl_lcrs(
  timestamp             DATE,
  source_database_name  VARCHAR2(128),
  command_type          VARCHAR2(30),
  object_owner          VARCHAR2(32),
  object_name           VARCHAR2(32),
  object_type           VARCHAR2(18),
  ddl_text              CLOB,
  logon_user            VARCHAR2(32),
  current_schema        VARCHAR2(32),
  base_table_owner      VARCHAR2(32),
  base_table_name       VARCHAR2(32),
  tag                   RAW(10),
  transaction_id        VARCHAR2(10),
  scn                   NUMBER);

CREATE OR REPLACE PROCEDURE history_ddl(in_any IN ANYDATA)  
 IS
   lcr       SYS.LCR$_DDL_RECORD;
   rc        PLS_INTEGER;
   ddl_text  CLOB;
 BEGIN
   -- Access the LCR
   rc := in_any.GETOBJECT(lcr);
   DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
   lcr.GET_DDL_TEXT(ddl_text);
   --  Insert DDL LCR information into history_ddl_lcrs table
   INSERT INTO strmadmin.history_ddl_lcrs VALUES( 
     SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(), 
     lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_OBJECT_TYPE(), 
     ddl_text, lcr.GET_LOGON_USER(), lcr.GET_CURRENT_SCHEMA(), 
     lcr.GET_BASE_TABLE_OWNER(), lcr.GET_BASE_TABLE_NAME(), lcr.GET_TAG(), 
     lcr.GET_TRANSACTION_ID(), lcr.GET_SCN());
   --  Apply DDL LCR
   lcr.EXECUTE();
   -- Free temporary LOB space
   DBMS_LOB.FREETEMPORARY(ddl_text);
END;
/

Setting the DDL Handler for an Apply Process

A DDL handler processes all DDL LCRs dequeued by an apply process. Set the DDL handler for an apply process using the ddl_handler parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the DDL handler for an apply process named strep01_apply to the history_ddl procedure in the strmadmin schema.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name  => 'strep01_apply',
    ddl_handler => 'strmadmin.history_ddl');
END;
/

Removing the DDL Handler for an Apply Process

A DDL handler processes all DDL LCRs dequeued by an apply process. You remove the DDL handler for an apply process by setting the remove_ddl_handler parameter to TRUE in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure removes the DDL handler from an apply process named strep01_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name         => 'strep01_apply',
    remove_ddl_handler => TRUE);
END;
/

Setting and Unsetting Value Dependencies

Use the SET_VALUE_DEPENDENCY procedure in the DBMS_APPLY_ADM package to set or unset a value dependency. The following sections describe scenarios for using value dependencies:

• Schema Differences and Value Dependencies

• Undefined Constraints at the Destination Database and Value Dependencies

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'key_53_foreign_key',
    object_name       => 'us_designs.design_53',
    attribute_list    => 'key_53');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'key_53_foreign_key',
    object_name       => 'us_designs.all_designs_summary',
    attribute_list    => 'key_53');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'key_53_foreign_key',
    object_name       => NULL,
    attribute_list    => NULL);
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'order_id_foreign_key',
    object_name       => 'oe.orders',
    attribute_list    => 'order_id');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'order_id_foreign_key',
    object_name       => 'oe.order_items',
    attribute_list    => 'order_id');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'order_id_foreign_key',
    object_name       => NULL,
    attribute_list    => NULL);
END;
/

Creating and Dropping Object Dependencies

Use the CREATE_OBJECT_DEPENDENCY and DROP_OBJECT_DEPENDENCY procedures in the DBMS_APPLY_ADM package to create or drop an object dependency. The following sections provide detailed instructions for creating and dropping object dependencies.

BEGIN
  DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
    object_name         =>  'ord.customers',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
    object_name         =>  'ord.orders',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
    object_name         =>  'ord.order_items',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
    object_name         =>  'ord.customers',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
    object_name         =>  'ord.orders',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
    object_name         =>  'ord.order_items',
    parent_object_name  =>  'ord.ship_orders');
END;
/

Setting an Update Conflict Handler

Set an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. You can use one of the following prebuilt methods when you create an update conflict resolution handler:

• OVERWRITE

• DISCARD

• MAXIMUM

• MINIMUM

For example, suppose an Oracle Streams environment captures changes to the hr.jobs table at dbs1.net and propagates these changes to the dbs2.net destination database, where they are applied. In this environment, applications can perform DML changes on the hr.jobs table at both databases, but, if there is a conflict for a particular DML change, then the change at the dbs1.net database should always overwrite the change at the dbs2.net database. In this environment, you can accomplish this goal by specifying an OVERWRITE handler at the dbs2.net database.

To specify an update conflict handler for the hr.jobs table in the hr schema at the dbs2.net database, run the following procedure at dbs2.net:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.jobs',
      method_name       => 'OVERWRITE',
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

All apply processes running on a database that apply changes to the specified table locally use the specified update conflict handler.

Modifying an Existing Update Conflict Handler

You can modify an existing update conflict handler by running the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. To update an existing conflict handler, specify the same table and resolution column as the existing conflict handler.

To modify the update conflict handler created in "Setting an Update Conflict Handler", you specify the hr.jobs table and the job_title column as the resolution column. You can modify this update conflict handler by specifying a different type of prebuilt method or a different column list, or both. However, if you want to change the resolution column for an update conflict handler, then you must remove and re-create the handler.

For example, suppose the environment changes, and you want changes from dbs1.net to be discarded in the event of a conflict, whereas previously changes from dbs1.net overwrote changes at dbs2.net. You can accomplish this goal by specifying a DISCARD handler at the dbs2.net database.

To modify the existing update conflict handler for the hr.jobs table in the hr schema at the dbs2.net database, run the following procedure:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.jobs',
      method_name       => 'DISCARD',
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

Removing an Existing Update Conflict Handler

You can remove an existing update conflict handler by running the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. To remove a an existing conflict handler, specify NULL for the method, and specify the same table, column list, and resolution column as the existing conflict handler.

For example, suppose you want to remove the update conflict handler created in "Setting an Update Conflict Handler" and then modified in "Modifying an Existing Update Conflict Handler". To remove this update conflict handler, run the following procedure:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.jobs',
      method_name       => NULL,
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

Stopping Conflict Detection for Nonkey Columns

You can stop conflict detection for nonkey columns using the COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package.

For example, suppose you configure a time column for conflict resolution for the hr.employees table, as described in "MAXIMUM". In this case, you can decide to stop conflict detection for the other nonkey columns in the table. After adding the time column and creating the trigger as described in that section, add the columns in the hr.employees table to the column list for an update conflict handler:

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

This example does not include the primary key for the table in the column list because it assumes that the primary key is never updated. However, other key columns are included in the column list.

To stop conflict detection for all nonkey columns in the table for both UPDATE and DELETE operations at a destination database, run the following procedure:

DECLARE
  cols DBMS_UTILITY.LNAME_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';  
  DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
    object_name  => 'hr.employees',
    column_table => cols, 
    operation    => '*',
    compare      => FALSE);
END;
/

The asterisk (*) specified for the operation parameter means that conflict detection is stopped for both UPDATE and DELETE operations. After you run this procedure, all apply processes running on the database that apply changes to the specified table locally do not detect conflicts on the specified columns. Therefore, in this example, the time column is the only column used for conflict detection.

Setting the Tag Values Generated by the Current Session

You can set the tag for all redo entries generated by the current session using the SET_TAG procedure in the DBMS_STREAMS package. For example, to set the tag to the hexadecimal value of '1D' in the current session, run the following procedure:

BEGIN
   DBMS_STREAMS.SET_TAG(
      tag  =>  HEXTORAW('1D'));
END;
/

After running this procedure, each redo entry generated by DML or DDL statements in the current session will have a tag value of 1D. Running this procedure affects only the current session.

The following are considerations for the SET_TAG procedure:

• This procedure is not transactional. That is, the effects of SET_TAG cannot be rolled back.

• If the SET_TAG procedure is run to set a non-NULL session tag before a data dictionary build has been performed on the database, then the redo entries for a transaction that started before the dictionary build might not include the specified tag value for the session. Therefore, perform a data dictionary build before using the SET_TAG procedure in a session. A data dictionary build happens when the DBMS_CAPTURE_ADM.BUILD procedure is run. The BUILD procedure can be run automatically when a capture process is created.

Getting the Tag Value for the Current Session

You can get the tag for all redo entries generated by the current session using the GET_TAG procedure in the DBMS_STREAMS package. For example, to get the hexadecimal value of the tags generated in the redo entries for the current session, run the following procedure:

SET SERVEROUTPUT ON
DECLARE
   raw_tag RAW(2048);
BEGIN
   raw_tag := DBMS_STREAMS.GET_TAG();
   DBMS_OUTPUT.PUT_LINE('Tag Value = ' || RAWTOHEX(raw_tag));
END;
/

You can also display the tag value for the current session by querying the DUAL view:

SELECT DBMS_STREAMS.GET_TAG FROM DUAL;

Setting the Tag Values Generated by an Apply Process

An apply process generates redo entries when it applies changes to a database or invokes handlers. You can set the default tag for all redo entries generated by an apply process when you create the apply process using the CREATE_APPLY procedure in the DBMS_APPLY_ADM package, or when you alter an existing apply process using the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. In both of these procedures, set the apply_tag parameter to the value you want to specify for the tags generated by the apply process.

For example, to set the value of the tags generated in the redo log by an existing apply process named strep01_apply to the hexadecimal value of '7', run the following procedure:

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
     apply_name  =>  'strep01_apply',
     apply_tag   =>  HEXTORAW('7'));
END;
/

After running this procedure, each redo entry generated by the apply process will have a tag value of 7.

Removing the Apply Tag for an Apply Process

You remove the apply tag for an apply process by setting the remove_apply_tag parameter to TRUE in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. Removing the apply tag means that each redo entry generated by the apply process has a NULL tag. For example, the following procedure removes the apply tag from an apply process named strep01_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name       => 'strep01_apply',
    remove_apply_tag => TRUE);
END;
/

Automatic Merge of Oracle Streams

When you split streams with the SPLIT_STREAMS procedure, the auto_merge_threshold 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 destination queue for the cloned propagation is accepting messages, 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.

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 Directly and Automatically

The example in this section splits and merges streams directly. 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. That is, 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. Connect as the Oracle Streams administrator:

   CONNECT strmadmin/user-password
   

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 destination queue for cloned_prop_a can accept propagated messages and an apply process at the destination database can dequeue and process these messages.

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 an apply process at the destination database.

During this time, the Oracle Scheduler job runs the MERGE_STREAMS_JOB procedure according to its schedule. When the difference between CAPTURE_MESSAGE_CREATE_TIME in the GV$STREAMS_CAPTURE view 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 MERGE_STREAMS procedure performs the following actions:

• Stops the cloned capture process cloned_capture.

• Stops the original capture process strms_capture.

• Re-creates the propagation called strms_prop_a. This propagation propagates messages from the streams_queue queue to the existing destination queue used by the cloned_prop_a propagation. The re-created propagation strms_prop_a uses the same rule set as the cloned propagation cloned_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.

• 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.

Splitting and Merging an Oracle Streams Destination Manually With Scripts

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. That is, 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. Connect as the Oracle Streams administrator:

   CONNECT strmadmin/user-password
   

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 new queue called cloned_queue.

   • Runs the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to create a new 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 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.

   • 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 destination queue for cloned_prop_a can accept propagated messages and an apply process at the destination database can dequeue and process these messages.

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. This propagation propagates messages from the streams_queue queue to the existing destination queue used by the cloned_prop_a propagation. The re-created propagation strms_prop_a uses the same rule set as the cloned propagation cloned_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.

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

   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 new 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.