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:
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.
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.
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:
Ideally, the current user is the Oracle Streams Administrator.
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:
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:
The following code examples can be found in the XStreams.sql
code file.
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; /
exec DBMS_APPLY_ADM.START_APPLY('HROE_Out'),
DBMS_XSTREAM_ADM.DROP_OUTBOUND
procedureBEGIN DBMS_XSTREAM_ADM.DROP_OUTBOUND('HROE_Out'), END; /
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);
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:
apply_user
parameter is nullThe following code examples can be found in the XStreams.sql
code file.
BEGIN DBMS_XSTREAM_ADM.CREATE_INBOUND( server_name => 'XSTRM_IN', queue_name => 'XSTRM_IN_ERR_Q', apply_user => 'STRM_ADMIN' ); END; /
The client application must connect to the database as the apply_user
for the XSTREAM Inbound Server to interact with that Server process.
DBMS_APPLY_ADM
package.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; /