Basic XSTREAMS Configuration

As discussed in Chapter 1, GoldenGate XSTREAMS is new in 11gR2 and provides an alternative to the above traditional Heterogeneous methods. XSTREAMS technology can be used to build LCRs from Non-Oracle database log files, that can then be enqueued into an Oracle Streams queue. While much of the XSTREAM OCI and Java API usage is beyond the scope of this book, we briefly discuss the Oracle database configuration and Specialized Server processes that support XSTREAMS OCI and Java API.

The following views provide XSTREAMS configuration and performance information:

  • DBA/ALL_XSTREAM_OUTBOUND
  • DBA/ALL_XSTREAM_OUTBOUND_PROGRESS
  • DBA/ALL_XSTREAM_INBOUND
  • DBA/ALL_XSTREAM_INBOUND_PROGRESS
  • DBA/ALL_XSTREAM_RULES
  • V$STREAMS_APPLY_READER
  • V$STREAMS_APPLY_SERVER
  • V$STREAMS_CAPTURE
  • V$STREAMS_MESSAGE_TRACKING
  • V$STREAMS_TRANSACTION

XSTREAMS Servers

First, let's look at configuring the Specialized Server processes that support XSTREAMS. There are two: XSTREAM Out and XSTREAM In.

The XSTREAM Out process is an Outbound Server that supports XSTREAM access to dequeue LCRs from a Streams Capture queue. The XSTREAMS In process is an Inbound server that supports XSTREAM access to enqueue LCR's and messages to a Streams apply queue.

You can configure multiple Outbound and Inbound Servers; just remember to increase your PROCESSES initialization parameter as necessary to accommodate the added processes. As with regular Streams client processes, an XSTREAMS Server process can only be assigned to a single queue. However, multiple Servers can be assigned to the same queue.

Configuring the Database

Before creating the XSTREAM Server processes, make sure that your Oracle Streams and database is configured to support the normal Streams clients and queues (capture, apply): this includes, database parameter, Streams Pool and Streams Administrator configuration, database link configuration, archive logging, etc (see Chapter 3 for configuring your database to support Streams). Once you have Oracle Streams configured, you can tap into the Stream using XSTREAMS.

The XSTREAMS Server processes are configured using the DBMS_XSTREAM_ADM package.

Configuring XSTREAMS Out

An XSTREAMS Out server can either be created with its own capture queue and process, or can be created to an existing capture queue and process. To create an Out server that uses its own capture queue and process, you use the DBMS_XSTREAMS_ADM.CREATE_OUTBOUND procedure. To create an Out Server that uses an existing capture queue and process, you use the DBMS_XSTREAMS_ADM.ADD_OUTBOUND procedure.

The DBMS_XSTREAMS_ADM.CREATE_OUTBOUND procedure accomplishes the following:

  • Configures supplemental logging for the specified table objects if not already done
  • Creates a Capture queue with a system-generated name used by the Outbound Server
  • Creates and starts a Capture process with a system-generated name with appropriate capture rule sets
  • Creates and starts an Outbound server with appropriate Apply rule sets
  • Sets the current user as the connect user for the outbound server

Ideally, the current user is the Oracle Streams Administrator.

Note

Note: If you wish to specify names for the Capture queue and Process, you will need to create the Capture queue and process manually, and then use the DBMS_XSTREAMS_ADM.ADD_OUTBOUND procedure to add the Outbound Server rules to the Capture process.

The DBMS_XSTREAMS_ADM.ADD_OUTBOUND procedure accomplishes the following:

  • Creates and starts an Outbound server with appropriate Apply rule sets
  • Sets the current user as the connect user for the outbound server

Ideally, the current user is the Oracle Streams Administrator.

The CREATE_OUTBOUND and ADD_OUTBOUND procedures can be used to add Table, Schema, and Global level rules. The procedures have a table_names parameter and a schema_names parameter that accept either a DBMS_UTILITY.UNCL_ARRAY datatype (for multiple names), or a VARCHAR2 string (for single name). You can specify both parameters in the same call, just avoid specifying tables in the table_names parameter that will be included in the schema objects of the schemas specified in the schema_names parameter. If you want to generate Global Level Rules, Set both table_name and schema_name parameters to NULL.

An Outbound Server is essentially a specialized Apply process, and can be managed with the DBMS_APPLY_ADM procedures. However, not all DBMS_APPLY_ADM procedures can be used. The following lists which DBMS_APPLY_ADM procedures can be used to manage an XSTREAMS Outbound Server process:

  • ALTER_APPLY Procedure
  • DROP_APPLY Procedure
  • SET_GLOBAL_INSTANTIATION_SCN Procedure
  • SET_PARAMETER Procedure
  • SET_SCHEMA_INSTANTIATION_SCN Procedure
  • SET_TABLE_INSTANTIATION_SCN Procedure
  • START_APPLY Procedure
  • STOP_APPLY Procedure

Steps for configuring an XSTREAMS Outbound Server

The following code examples can be found in the XStreams.sql code file.

  1. Create the Outbound Server:

    If the Capture queue and Process exist, use DBMS_XSTREAMS_ADM.ADD_OUTBOUND

    In this example we will assume that we have a Capture queue, HROE_CAPT_Q, and a Capture process, HROE_CAPT, that captures Schema level changes for the HR and OE schemas already configured. Here we want the Outbound Server to access all HR schema changes and table level changes for the OE.ORDERS and OE_ORDER_ITEMS tables. 

    DECLARE
ob_tables DBMS_UTILITY.UNCL_ARRAY;
ob_schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tables(1) := 'OE.ORDERS';
tables(2) := 'OE.ORDER_ITEMS';
schemas(1) := 'HR';
DBMS_XSTREAM_ADM.ADD_OUTBOUND(
server_name => 'HROE_Out',
queue_name => 'STRM_ADMIN.HROE_CAPT_Q',
source_database => 'STRM1',
table_names => ob_tables,
schema_names => ob_schemas);
END;
/

    OR

    To create a capture queue and process with the Outbound Server, use DBMS_XSTREAMS_ADM.CREATE_OUTBOUND. 

    DECLARE
ob_tables DBMS_UTILITY.UNCL_ARRAY;
ob_schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tables(1) := 'OE.ORDERS';
tables(2) := 'OE.ORDER_ITEMS';
schemas(1) := 'HR';
DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
server_name => 'HROE_Out',
table_names => ob_tables,
schema_names => ob_schemas);
END;
/
  2. Create the Client Application that will use the Outbound Server process.
  3. The client application must connect to the database as the user schema that created the XSTREAM Outbound Server to interact with that Server process.

    Note

    For an example client application, please reference the "Sample XSTREAM Client Application" Section of Chapter 3 — Configuring XStream in the Oracle Database XStream Guide.

  4. Add Additional Outbound Servers as needed.
  5. Start the Outbound Server
    exec DBMS_APPLY_ADM.START_APPLY('HROE_Out'),
  6. To remove an Outbound Server configuration, use the DBMS_XSTREAM_ADM.DROP_OUTBOUND procedure
    BEGIN
DBMS_XSTREAM_ADM.DROP_OUTBOUND('HROE_Out'),
END;
/
Subsetting Rules for an Outbound Server

You can also add Subsetting rules to an existing Outbound server

The DBMS_XSTREAMS_ADM.ADD_SUBSET_OUTBOUND_RULES adds subsetting rules, much like the DBMS_STREAMS_ADM.ADD_SUBSET_RULES procedure.

The ADD_SUBSET_OUTBOUND_RULES condition parameter equates to the ADD_SUBSET_RULES dml_condition parameter. The ADD_SUBSET_OUTBOUND_RULES keep parameter equates the ADD_SUBSET include_rule parameter (If TRUE, include the LCRs that meet the condition. If FALSE, ignore the LCRs that meet the condition).

DECLARE
col_list DBMS_UTILITY.LNAME_ARRAY;
x number := 0;
BEGIN
select column_name from dba_tab_cols where owner = 'HR'
and table_name = 'EMPLOYEES';
for arec in (select column_name from dba_tab_cols
where owner = 'HR' and table_name = 'EMPLOYEES')
loop
x := x+1;
col_list(x) := arec.column_name;
end loop;
--last position in the array must be set to NULL
If x >0 then
x := x+1;
col_list(x) := NULL;
end if;
DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES(
server_name => 'HROE_Out',
table_name => 'HR.EMPLOYEES',
condition => 'department_id = 50',
column_list => col_list,
keep => TRUE);
END;
/

To remove a Subsetting rule from an Outbound Server, first determine the Subset Rule names:

SELECT STREAMS_NAME, STREAMS_TYPE, STREAMS_RULE_TYPE,
RULE_OWNER, SUBSETTING_OPERATION, RULE_NAME
FROM DBA_XSTREAM_RULES
WHERE SUBSETTING_OPERATION IS NOT NULL;

Then use the DBMS_XSTREAM_ADM.REMOVE_SUBSET_OUTBOUND_RULES procedure to remove the rules

DBMS_XSTREAM_ADM.REMOVE_SUBSET_OUTBOUND_RULES(
server_name IN VARCHAR2,
insert_rule_name IN VARCHAR2,
update_rule_name IN VARCHAR2,
delete_rule_name IN VARCHAR2);

Configuring XSTREAMS In

XSTREAM Inbound Servers can receive DML and DDL changes, configured as an LCR, from a client application. These changes can be applied to Oracle database objects, or can be handled by customized processing via apply handlers. The client application can use the XSTREAMS OCI or Java API interface to generate and pass the LCR to the Inbound Server process.

As with the Outbound Server process, the Inbound Server process is a specialized Apply process. One big difference with an Inbound Server Apply process is that it only uses its assigned queue to store erred LCRs. Another difference is that, by default, the Inbound server does not use rules or rule sets. However, rules and rule sets can be added to an Inbound Server process using the DBMS_STREAMS_ADM or DBMS_RULE_ADM packages once the Inbound Server process is created.

An Inbound Server process is created using the DBMS_XSTREAMS_ADM.CREATE_INBOUND procedure.

The DBMS_XSTREAMS_ADM.CREATE_OUTBOUND procedure accomplishes the following:

  • Creates an Inbound server process
  • Assigns the specified queue to the Inbound Server process as its error queue
  • If the queue does not exist, the procedure creates it
  • Sets the current user as the Apply user for the Inbound Server process if the apply_user parameter is null
Steps for configuring an XSTREAMS inbound server

The following code examples can be found in the XStreams.sql code file.

  1. Create the Inbound Server:
    BEGIN
DBMS_XSTREAM_ADM.CREATE_INBOUND(
server_name => 'XSTRM_IN',
queue_name => 'XSTRM_IN_ERR_Q',
apply_user => 'STRM_ADMIN'
);
END;
/
  2. Create the Client Application that will use the Inbound Server process.

    The client application must connect to the database as the apply_user for the XSTREAM Inbound Server to interact with that Server process.

    Note

    For an example client application, please reference the "Sample XSTREAM Client Application" section of Chapter 3—Configuring XStream in the Oracle Database XStream Guide.

  3. Add Apply Handler to the Inbound Server as needed.
  4. This can be done using the DBMS_APPLY_ADM package.
  5. Start the Inbound Server
    exec DBMS_APPLY_ADM.START_APPLY('XSTRM_IN'),

Subsetting is not supported with XSTREAMS Inbound Server processes.

To remove an Inbound Server configuration, use the DBMS_XSTREAM_ADM.DROP_INBOUND procedure

BEGIN
DBMS_XSTREAM_ADM.DROP_OUTBOUND('XSTRM_IN'),
END;
/

Note

For more information on configuring XSTREAMS Server processes and using the DBMS_XSTREAMS_ADM package, please reference the Oracle Database XSTREAMS Guide.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.129.13.201