4 Managing XStream

Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process

In some XStream Out configurations, you can use the DBMS_XSTREAM_ADM package to manage the capture process that captures changes for an outbound server. However, other configurations require that you use the DBMS_CAPTURE_ADM package or the DBMS_STREAMS_ADM package to manage the capture process.

Specifically, the DBMS_XSTREAM_ADM package can manage an outbound server's capture process in the following ways:

• Add rules to and remove rules from the capture process's rule sets

• Change the capture user for the capture process

• Drop the capture process

The DBMS_XSTREAM_ADM package can manage an outbound server's capture process in either of the following cases:

• The capture process was created by the CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

• The queue used by the capture process was created by the CREATE_OUTBOUND procedure.

To check whether an outbound server's capture process can be managed by the DBMS_XSTREAM_ADM package: 

1. Connect to the outbound server database as the XStream administrator.

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

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Outbound Server Name' FORMAT A30
   COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30
   
   SELECT SERVER_NAME, 
          CAPTURE_NAME
     FROM DBA_XSTREAM_OUTBOUND;
   

   Your output looks similar to the following:

   Outbound Server Name           Capture Process Name
   ------------------------------ ------------------------------
   XOUT                           CAP$_XOUT_4
   

   If the Capture Process Name for an outbound server is non-NULL, then the DBMS_XSTREAM_ADM package can manage the capture process. In this case, you can also manage the capture process using the DBMS_CAPTURE_ADM package or the DBMS_STREAMS_ADM package. However, it is usually better to manage the capture process for an outbound server using the DBMS_XSTREAM_ADM package when it is possible.

   If the Capture Process Name for an outbound server is NULL, then the DBMS_XSTREAM_ADM package cannot manage the capture process. In this case, you must manage the capture process using the DBMS_CAPTURE_ADM package or the DBMS_STREAMS_ADM package.

Managing Rules for an XStream Out Configuration

This section describes managing rules for an XStream Out configuration. Rules control which database changes are streamed to the outbound server and which database changes the outbound server streams to the client application.

This section contains these topics:

• Adding Rules to an XStream Out Configuration

• Removing Rules from an XStream Out Configuration

BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name    => 'xout',
    table_names    => 'oe.orders, oe.order_items',
    schema_names   => 'hr',
    add            => TRUE,
    inclusion_rule => TRUE);
END;
/

BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name    => 'xout',
    table_names    => NULL,
    schema_names   => 'hr',
    add            => TRUE,
    inclusion_rule => FALSE);
END;
/

DECLARE
  cols DBMS_UTILITY.LNAME_ARRAY;
  BEGIN
    cols(1) := 'employee_id';
    cols(2) := 'first_name';
    cols(3) := 'last_name';
    cols(4) := 'email';
    cols(5) := 'phone_number';
    cols(6) := 'hire_date';
    cols(7) := 'job_id';
    cols(8) := 'salary';
    cols(9) := 'commission_pct';
    cols(10) := 'manager_id';
    cols(11) := 'department_id';
  DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES(
    server_name => 'xout',
    table_name  => 'hr.employees',
    condition   => 'department_id=40',
    column_list => cols); 
END;
/

BEGIN
  DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES(
    server_name => 'xout',
    table_name  => 'hr.employees',
    condition   => 'department_id=40',
    column_list => 'salary,commission_pct', 
    keep        => FALSE);
END;
/

Changing the Connect User for an Outbound Server

A client application can connect to an outbound server as the connect user. This section describes changing the connect user for an outbound server using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

The connect user is the user who can attach to the outbound server to retrieve the LCR stream. The client application must attach to the outbound server as the connect user.

You can change the connect user when a client application must connect to an outbound server as a different user. Ensure that the connect user is granted the required privileges.

To change the connect user for an outbound server: 

1. Connect to the outbound server database as the XStream administrator.

   The XStream administrator must be granted the DBA role to change the connect user for an outbound server.

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

2. Run the ALTER_OUTBOUND procedure, and specify the following parameters:

   • server_name - Specify the name of the outbound server.

   • connect_user - Specify the new connect user.

   Example 4-8 Changing the Connect User for an Outbound Server

   To change the connect user to hr for an outbound server named xout, run the following procedure:

   BEGIN
     DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
       server_name  => 'xout',
       connect_user => 'hr');
   END;
   /
   

Changing the Capture User of the Capture Process for an Outbound Server

A capture user is the user in whose security domain a capture process captures changes from the redo log. This section describes changing the capture user for a capture process that captures changes for an outbound server using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

You can change the capture user when the capture process must capture changes in a different security domain. Ensure that the capture user is granted the required privileges. When you change the capture user, the ALTER_OUTBOUND procedure grants the new capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user.

To change the capture user of the capture process for an outbound server: 

1. Connect to the outbound server database as the XStream administrator.

   To change the capture user, the user who invokes the ALTER_OUTBOUND procedure must be granted DBA role. Only the SYS user can set the capture user to SYS.

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

2. Determine whether the DBMS_XSTREAM_ADM package can manage the capture process. See "Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process".

   Based on the check, follow the appropriate instructions:

   • If the capture process can be managed using the DBMS_XSTREAM_ADM package, then proceed to Step 3.

   • If the capture process cannot be managed using the DBMS_XSTREAM_ADM package, then follow the instructions in Oracle Streams Concepts and Administration.

3. Run the ALTER_OUTBOUND procedure, and specify the following parameters:

   • server_name - Specify the name of the outbound server.

   • capture_user - Specify the new capture user.

   Example 4-9 Changing the Capture User of the Capture Process for an Outbound Server

   To change the capture user to hq_admin for an outbound server named xout, run the following procedure:

   BEGIN
     DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
       server_name  => 'xout',
       capture_user => 'hq_admin');
   END;
   /
   

Changing the Start SCN or Start Time of the Capture Process for an Outbound Server

This section describes changing the start system change number (SCN) or start time for a capture process that captures changes for an outbound server using the ALTER_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

The start SCN is the SCN from which a capture process begins to capture changes. The start time is the time from which a capture process begins to capture changes. When you reset a start SCN or start time for a capture process, ensure that the required redo log files are available to the capture process.

Typically, you reset the start SCN or start time for a capture process if point-in-time recovery was performed on one of the destination databases that receive changes from the capture process.

This section contains these topics:

• Changing the Start SCN of the Capture Process for an Outbound Server

• Changing the Start Time of the Capture Process for an Outbound Server

Dropping Components in an XStream Out Configuration

This section describes dropping an outbound server using the DROP_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

This procedure always drops the specified outbound server. This procedure also drops the queue used by the outbound server if both of the following conditions are met:

• The queue was created by the ADD_OUTBOUND or CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

• The outbound server is the only subscriber to the queue.

If either one of the preceding conditions is not met, then the DROP_OUTBOUND procedure only drops the outbound server. It does not drop the queue.

This procedure also drops the capture process for the outbound server if both of the following conditions are met:

• The procedure can drop the outbound server's queue.

• The DBMS_XSTREAM_ADM package can manage the outbound server's capture process. See "Checking Whether the DBMS_XSTREAM_ADM Package Can Manage a Capture Process".

If the procedure can drop the queue but cannot manage the capture process, then it drops the queue without dropping the capture process.

To drop an outbound server: 

1. Connect to the outbound server database as the XStream administrator.

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

2. Run the DROP_OUTBOUND procedure.

   Example 4-13 Dropping an Outbound Server

   To drop an outbound server named xout, run the following procedure:

   exec DBMS_XSTREAM_ADM.DROP_OUTBOUND('xout');
   

Changing the Apply User for an Inbound Server

An inbound server applies messages in the security domain of its apply user, and the client application must attach to the inbound server as the apply user. This section describes changing the apply user for an inbound server using the ALTER_INBOUND procedure in the DBMS_XSTREAM_ADM package.

You can change the apply user when a client application must connect to an inbound server as a different user or when you want to apply changes using the privileges associated with a different user. Ensure that the apply user is granted the required privileges.

To change the apply user for an inbound server: 

1. Connect to the inbound server database as the XStream administrator.

   The XStream administrator must be granted the DBA role to change the apply user for an inbound server.

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

2. Run the ALTER_INBOUND procedure, and specify the following parameters:

   • server_name - Specify the name of the inbound server.

   • apply_user - Specify the new apply user.

   Example 4-14 Changing the Apply User for an Inbound Server

   To change the apply user to hr for an inbound server named xin, run the following procedure:

   BEGIN
     DBMS_XSTREAM_ADM.ALTER_INBOUND(
       server_name => 'xin',
       apply_user  => 'hr');
   END;
   /
   

Managing Eager Errors Encountered by an Inbound Server

An inbound server can encounter an eager error when it cannot access all of the LCRs in an error transaction. The EAGER ERROR error type typically means that an LCR raised an error while the inbound server was receiving and applying LCRs in a large transaction. If an error transaction is not an eager error transaction, then it is referred to as a normal error transaction.

Normal error transactions and eager error transactions must be managed differently. An inbound server moves a normal error transaction, including all of its LCRs, to the error queue, but an inbound server does not move an eager error transaction to the error queue.

The following statements apply to both normal error transactions and eager error transactions:

• The ALL_APPLY_ERROR and the DBA_APPLY_ERROR view contain information (metadata) about the error transaction.

• The inbound server does not apply the error transaction.

Table 4-1 explains the options for managing a normal error transaction.

Table 4-2 explains the options for managing an eager error transaction.

ORA-26909: cannot reexecute an eager error

To manage an eager error transaction encountered by an inbound server: 

1. Connect to the inbound server database as the XStream administrator.

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

2. Query the ERROR_TYPE column in the ALL_APPLY_ERROR data dictionary view:

   SELECT APPLY_NAME, ERROR_TYPE FROM ALL_APPLY_ERROR;
   

   Follow the appropriate instructions based on the error type:

   • If the ERROR_TYPE column shows EAGER ERROR, then proceed to Step 3.

   • If the ERROR_TYPE column shows NULL, then the apply error is not an eager error, and you cannot use the instructions in this section to manage it. Instead, use the instructions in Oracle Database 2 Day + Data Replication and Integration Guide or Oracle Streams Concepts and Administration.

3. Examine the error message raised by the LCR, and determine the cause of the error.

   See Oracle Database 2 Day + Data Replication and Integration Guide for information about checking for apply errors using Oracle Enterprise Manager.

   See Oracle Streams Concepts and Administration for information about checking for apply errors using the DBA_APPLY_ERROR data dictionary view.

4. If possible, determine how to avoid the error, and make any changes necessary to avoid the error.

   Oracle Streams Concepts and Administration contains information about common apply errors.

5. Either retain the error transaction or delete the error transaction:

   • Delete the error transaction only if you have corrected the problem. The inbound server reexecutes the transaction when it is enabled.

   • Retain the error transaction if you cannot correct the problem now or if you plan to reexecute it in the future.

   See Table 4-2 for more information about these choices.

   Caution:

   It might not be possible to recover a normal error transaction that is deleted. Before deleting the error transaction, ensure that the error type is EAGER ERROR.

   See Oracle Database 2 Day + Data Replication and Integration Guide for information about deleting an error transaction using Oracle Enterprise Manager.

   See Oracle Streams Concepts and Administration information about deleting an error transaction using the DBMS_APPLY_ADM package.

6. If the inbound server is disabled, then start the inbound server.

   Query the STATUS column in the ALL_APPLY_ERROR view to determine whether the inbound server is enabled or disabled.

   If the disable_on_error apply parameter is set to Y for the inbound server, then the inbound server becomes disabled when it encounters the error and remains disabled.

   If the disable_on_error apply parameter is set to N for the inbound server, then the inbound server stops and restarts automatically when it encounters the error.

   See Table 4-2 for information about how the inbound server handles the error transaction based on your choice in Step 5.

   See Oracle Database 2 Day + Data Replication and Integration Guide for information about starting an apply process (or inbound server) using Oracle Enterprise Manager.

   See Oracle Streams Concepts and Administration for information about starting an apply process (or inbound server) using the DBMS_APPLY_ADM package.

Dropping Components in an XStream In Configuration

This section describes dropping an inbound server using the DROP_INBOUND procedure in the DBMS_XSTREAM_ADM package.

This procedure always drops the specified inbound server. This procedure also drops the queue for the inbound server if both of the following conditions are met:

• One call to the CREATE_INBOUND procedure created the inbound server and the queue.

• The inbound server is the only subscriber to the queue.

If either one of the preceding conditions is not met, then the DROP_INBOUND procedure only drops the inbound server. It does not drop the queue.

To drop an inbound server: 

1. Connect to the inbound server database as the XStream administrator.

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

2. Run the DROP_INBOUND procedure.

   Example 4-15 Dropping an Inbound Server

   To drop an inbound server named xin, run the following procedure:

   exec DBMS_XSTREAM_ADM.DROP_INBOUND('xin');