This part contains descriptions of the data dictionary views related to XStream. This part contains the following chapters:
This chapter describes the dynamic performance (V$) views related to XStream. In an XStream configuration, an apply process can function as an XStream outbound server or inbound server.
This chapter contains these topics:
V$STREAMS_APPLY_COORDINATOR displays information about each apply process coordinator. The coordinator for an apply process gets transactions from the apply process reader and passes them to apply servers. An apply process coordinator is a subcomponent of an apply process, outbound server, or inbound server.
| Column | Data Type | Description |
|---|---|---|
SID | NUMBER | Session ID of the coordinator's session |
SERIAL# | NUMBER | Serial number of the coordinator's session |
STATE | VARCHAR2(21) | State of the coordinator:
|
APPLY# | NUMBER | Apply process number
An apply process coordinator is an Oracle background process, prefixed by |
APPLY_NAME | VARCHAR2(30) | Name of the apply process |
TOTAL_APPLIED | NUMBER | Total number of transactions applied by the apply process since the apply process was last started |
TOTAL_WAIT_DEPS | NUMBER | Number of times since the apply process was last started that an apply server waited to apply a logical change record (LCR) in a transaction until another apply server applied a transaction because of a dependency between the transactions |
TOTAL_WAIT_COMMITS | NUMBER | Number of times since the apply process was last started that an apply server waited to commit a transaction until another apply server committed a transaction to serialize commits |
TOTAL_ADMIN | NUMBER | Number of administrative jobs issued since the apply process was last started |
TOTAL_ASSIGNED | NUMBER | Number of transactions assigned to apply servers since the apply process was last started |
TOTAL_RECEIVED | NUMBER | Total number of transactions received by the coordinator process since the apply process was last started |
TOTAL_IGNORED | NUMBER | Number of transactions which were received by the coordinator but were ignored because they had been previously applied |
TOTAL_ROLLBACKS | NUMBER | Number of transactions which were rolled back due to unexpected contention |
TOTAL_ERRORS | NUMBER | Number of transactions applied by the apply process that resulted in an apply error since the apply process was last started |
UNASSIGNED_COMPLETE_TXNS | NUMBER | Total number of complete transactions that the coordinator has not assigned to any apply servers |
AUTO_TXN_BUFFER_SIZE | NUMBER | Current value of transaction buffer size
Transaction buffer size refers to the number of transactions that the apply reader can assemble ahead of apply servers. The apply process periodically adjusts the transaction buffer size. |
LWM_TIME | DATE | Time when the message with the lowest message number was recorded
The creation time of the message with the lowest message number was also recorded at this time. |
LWM_MESSAGE_NUMBER | NUMBER | Number of the message corresponding to the low-watermark
That is, messages with a commit message number less than or equal to this message number have definitely been applied, but some messages with a higher commit message number also may have been applied. |
LWM_MESSAGE_CREATE_TIME | DATE | For captured messages, creation time at the source database of the message corresponding to the low-watermark. For user-enqueued messages, time when the message corresponding to the low-watermark was enqueued into the queue at the local database. |
HWM_TIME | DATE | Time when the message with the highest message number was recorded
The creation time of the message with the highest message number was also recorded at this time. |
HWM_MESSAGE_NUMBER | NUMBER | Number of the message corresponding to the high-watermark
That is, no messages with a commit message number greater than this message number have been applied. |
HWM_MESSAGE_CREATE_TIME | DATE | For captured messages, creation time at the source database of the message corresponding to the high-watermark. For user-enqueued messages, time when the message corresponding to the high-watermark was enqueued into the queue at the local database. |
STARTUP_TIME | DATE | Time when the apply process was last started |
ELAPSED_SCHEDULE_TIME | NUMBER | Time elapsed (in hundredths of a second) scheduling messages since the apply process was last started |
ELAPSED_IDLE_TIME | NUMBER | Elapsed idle time |
LWM_POSITION | RAW(64) | Position of the low-watermark LCR |
HWM_POSITION | RAW(64) | Position of the high-watermark LCR |
PROCESSED_MESSAGE_NUMBER | NUMBER | Message number currently processed by the apply coordinator |
|
Note: TheELAPSED_SCHEDULE_TIME column is only populated if the TIMED_STATISTICS initialization parameter is set to true, or if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. |
V$STREAMS_APPLY_READER displays information about each apply reader. The apply reader is a process which reads (dequeues) messages from the queue, computes message dependencies, and builds transactions. It passes the transactions on to the coordinator in commit order for assignment to the apply servers. An apply reader is a subcomponent of an apply process, outbound server, or inbound server.
| Column | Data Type | Description |
|---|---|---|
SID | NUMBER | Session ID of the reader's session |
SERIAL# | NUMBER | Serial number of the reader's session |
APPLY# | NUMBER | Apply process number
An apply process is an Oracle background process prefixed by |
APPLY_NAME | VARCHAR2(30) | Name of the apply process |
STATE | VARCHAR2(36) | State of the reader:
|
TOTAL_MESSAGES_DEQUEUED | NUMBER | Total number of messages dequeued since the apply process was last started |
TOTAL_MESSAGES_SPILLED | NUMBER | Number of messages spilled by the reader since the apply process was last started |
DEQUEUE_TIME | DATE | Time when the last message was received |
DEQUEUED_MESSAGE_NUMBER | NUMBER | Number of the last message received |
DEQUEUED_MESSAGE_CREATE_TIME | DATE | For captured messages, creation time at the source database of the last message received. For user-enqueued messages, time when the message was enqueued into the queue at the local database. |
SGA_USED | NUMBER | Amount (in bytes) of SGA memory used by the apply process since it was last started |
ELAPSED_DEQUEUE_TIME | NUMBER | Time elapsed (in hundredths of a second) dequeuing messages since the apply process was last started |
ELAPSED_SCHEDULE_TIME | NUMBER | Time elapsed (in hundredths of a second) scheduling messages since the apply process was last started. Scheduling includes computing dependencies between messages and assembling messages into transactions. |
ELAPSED_SPILL_TIME | NUMBER | Elapsed time (in hundredths of a second) spent spilling messages since the apply process was last started |
LAST_BROWSE_NUM | NUMBER | Reserved for internal use |
OLDEST_SCN_NUM | NUMBER | Oldest SCN |
LAST_BROWSE_SEQ | NUMBER | Reserved for internal use |
LAST_DEQ_SEQ | NUMBER | Last dequeue sequence number |
OLDEST_XIDUSN | NUMBER | Transaction ID undo segment number of the oldest transaction that either has been applied or is being applied |
OLDEST_XIDSLT | NUMBER | Transaction ID slot number of the oldest transaction that either has been applied or is being applied |
OLDEST_XIDSQN | NUMBER | Transaction ID sequence number of the oldest transaction that either has been applied or is being applied |
SPILL_LWM_SCN | NUMBER | Spill low-watermark SCN |
PROXY_SID | NUMBER | When the apply process uses combined capture and apply, the session ID of the propagation receiver that is responsible for direct communication between capture and apply. If the apply process does not use combined capture and apply, then this column is 0. |
PROXY_SERIAL | NUMBER | When the apply process uses combined capture and apply, the serial number of the propagation receiver that is responsible for direct communication between capture and apply. If the apply process does not use combined capture and apply, then this column is 0. |
PROXY_SPID | VARCHAR2(12) | When the apply process uses combined capture and apply, the process identification number of the propagation receiver that is responsible for direct communication between capture and apply. If the apply process does not use combined capture and apply, then this column is 0. |
CAPTURE_BYTES_RECEIVED | NUMBER | When the apply process uses combined capture and apply, the number of bytes received by the apply process from the capture process since the apply process last started. If the apply process does not use combined capture and apply, then this column is not populated. |
DEQUEUED_POSITION | RAW(64) | Dequeued position
This column is populated only for an apply process that is functioning as an XStream inbound server. |
LAST_BROWSE_POSITION | RAW(64) | Reserved for internal use |
OLDEST_POSITION | RAW(64) | The earliest position of the transactions currently being dequeued and applied
This column is populated only for an apply process that is functioning as an XStream inbound server. |
SPILL_LWM_POSITION | RAW(64) | Spill low-watermark position
This column is populated only for an apply process that is functioning as an XStream inbound server. |
OLDEST_TRANSACTION_ID | VARCHAR2(128) | Oldest transaction ID |
TOTAL_LCRS_WITH_DEP | NUMBER | Total number of LCRs with row-level dependencies since the apply process last started |
TOTAL_LCRS_WITH_WMDEP | NUMBER | Total number of LCRs with watermark dependencies since the apply process last started
A watermark dependency occurs when an apply process must wait until the apply process's low-watermark reaches a particular threshold. |
TOTAL_IN_MEMORY_LCRS | NUMBER | Total number of LCRs currently in memory |
SGA_ALLOCATED | NUMBER | The total amount of shared memory (in bytes) allocated from the Streams pool for the apply process since the apply process last started |
|
Note: TheELAPSED_DEQUEUE_TIME and ELAPSED_SCHEDULE_TIME columns are only populated if the TIMED_STATISTICS initialization parameter is set to true, or if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. |
V$STREAMS_APPLY_SERVER displays information about each apply server and its activities. An apply server receives messages from the apply coordinator for an apply process. For each message received, an apply server either applies the message or sends the message to the appropriate apply handler. An apply server is a subcomponent of an apply process, outbound server, or inbound server.
| Column | Data Type | Description |
|---|---|---|
SID | NUMBER | Session ID of the apply server's session |
SERIAL# | NUMBER | Serial number of the apply server's session |
APPLY# | NUMBER | Apply process number
An apply process is an Oracle background process prefixed by |
APPLY_NAME | VARCHAR2(30) | Name of the apply process |
SERVER_ID | NUMBER | Parallel execution server number of the apply server |
STATE | VARCHAR2(20) | State of the apply server:
|
XIDUSN | NUMBER | Transaction ID undo segment number of the transaction currently being applied |
XIDSLT | NUMBER | Transaction ID slot number of the transaction currently being applied |
XIDSQN | NUMBER | Transaction ID sequence number of the transaction currently being applied |
COMMITSCN | NUMBER | Commit SCN of the transaction currently being applied |
DEP_XIDUSN | NUMBER | Transaction ID undo segment number of a transaction on which the transaction being applied by this apply server depends |
DEP_XIDSLT | NUMBER | Transaction ID slot number of a transaction on which the transaction being applied by this apply server depends |
DEP_XIDSQN | NUMBER | Transaction ID sequence number of a transaction on which the transaction being applied by this apply server depends |
DEP_COMMITSCN | NUMBER | Commit SCN of the transaction on which this apply server depends |
MESSAGE_SEQUENCE | NUMBER | Number of the current message being applied by the apply server. This value is reset to 1 at the beginning of each transaction. |
TOTAL_ASSIGNED | NUMBER | Total number of transactions assigned to the apply server since the apply process was last started |
TOTAL_ADMIN | NUMBER | Total number of administrative jobs done by the apply server since the apply process was last started. See the STATE information in this view for the types of administrative jobs. |
TOTAL_ROLLBACKS | NUMBER | Number of transactions assigned to this server that were rolled back |
TOTAL_MESSAGES_APPLIED | NUMBER | Total number of messages applied by this apply server since the apply process was last started |
APPLY_TIME | DATE | Time the last message was applied |
APPLIED_MESSAGE_NUMBER | NUMBER | Number of the last message applied |
APPLIED_MESSAGE_CREATE_TIME | DATE | Creation time at the source database of the last captured message applied. No information about user-enqueued messages is recorded in this column. |
ELAPSED_DEQUEUE_TIME | NUMBER | Time elapsed (in hundredths of a second) dequeuing messages since the apply process was last started |
ELAPSED_APPLY_TIME | NUMBER | Time elapsed (in hundredths of a second) applying messages since the apply process was last started |
COMMIT_POSITION | RAW(64) | Commit position of the transaction.
This column is populated only for an apply process that is functioning as an XStream outbound server or inbound server. |
DEP_COMMIT_POSITION | RAW(64) | Commit position of the transaction the slave depends on
This column is populated only for an apply process that is functioning as an XStream inbound server. |
LAST_APPLY_POSITION | RAW(64) | For inbound servers, the position of the last message applied; for outbound servers, the position of the last message sent to the XStream client application
This column is populated only for an apply process that is functioning as an XStream outbound server or inbound server. |
TRANSACTION_ID | VARCHAR2(128) | Transaction ID that the slave is applying
This column is populated only for an apply process that is functioning as an XStream inbound server. |
DEP_TRANSACTION_ID | VARCHAR2(128) | Transaction ID of the transaction the slave depends on
This column is populated only for an apply process that is functioning as an XStream inbound server. |
|
Note:
|
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
V$XSTREAM_CAPTURE displays information about each capture process that sends LCRs to an XStream outbound server.
|
Note: This view does not display information about capture processes that send LCRs to Oracle Streams apply processes. To view information about such capture processes, query theV$STREAMS_CAPTURE view. |
| Column | Data Type | Description |
|---|---|---|
SID | NUMBER | Session identifier of the capture process |
SERIAL# | NUMBER | Session serial number of the capture process session |
CAPTURE# | NUMBER | Capture process number
A capture process is an Oracle background process prefixed by |
CAPTURE_NAME | VARCHAR2(30) | Name of the capture process |
LOGMINER_ID | NUMBER | Session ID of the Oracle LogMiner session associated with the capture process |
STARTUP_TIME | DATE | Time when the capture process was last started |
STATE | VARCHAR2(551) | State of the capture process:
|
TOTAL_PREFILTER_DISCARDED | NUMBER | Total number of prefiltered messages discarded |
TOTAL_PREFILTER_KEPT | NUMBER | Total number of prefiltered messages kept |
TOTAL_PREFILTER_EVALUATIONS | NUMBER | Total number of prefilter evaluations |
TOTAL_MESSAGES_CAPTURED | NUMBER | Total number of redo entries passed by LogMiner to the capture process for detailed rule evaluation since the capture process last started. A capture process converts a redo entry into a message and performs detailed rule evaluation on the message when capture process prefiltering cannot discard the change. |
CAPTURE_TIME | DATE | Time when the most recent message was captured |
CAPTURE_MESSAGE_NUMBER | NUMBER | Number of the most recently captured message |
CAPTURE_MESSAGE_CREATE_TIME | DATE | Creation time of the most recently captured message |
TOTAL_MESSAGES_CREATED | NUMBER | Count associated with ELAPSED_LCR_TIME to calculate rate |
TOTAL_FULL_EVALUATIONS | NUMBER | Count associated with ELAPSED_RULE_TIME to calculate rate |
TOTAL_MESSAGES_ENQUEUED | NUMBER | Total number of messages enqueued since the capture process was last started |
ENQUEUE_TIME | DATE | Time when the last message was enqueued |
ENQUEUE_MESSAGE_NUMBER | NUMBER | Number of the last enqueued message |
ENQUEUE_MESSAGE_CREATE_TIME | DATE | Creation time of the last enqueued message |
AVAILABLE_MESSAGE_NUMBER | NUMBER | For local capture, the last redo SCN flushed to the log files. For downstream capture, the last SCN added to LogMiner through the archived redo log files. |
AVAILABLE_MESSAGE_CREATE_TIME | DATE | For local capture, the time the SCN was written to the log file. For downstream capture, the time the most recent archived redo log file (containing the most recent SCN) was added to LogMiner. |
ELAPSED_CAPTURE_TIME | NUMBER | Elapsed time (in hundredths of a second) scanning for changes in the redo log since the capture process was last started |
ELAPSED_RULE_TIME | NUMBER | Elapsed time (in hundredths of a second) evaluating rules since the capture process was last started |
ELAPSED_ENQUEUE_TIME | NUMBER | Elapsed time (in hundredths of a second) enqueuing messages since the capture process was last started |
ELAPSED_LCR_TIME | NUMBER | Elapsed time (in hundredths of a second) creating LCRs since the capture process was last started |
ELAPSED_REDO_WAIT_TIME | NUMBER | Elapsed time (in hundredths of a second) spent by the capture process in the WAITING FOR REDO state |
ELAPSED_PAUSE_TIME | NUMBER | Elapsed flow control pause time (in hundredths of a second) |
STATE_CHANGED_TIME | DATE | Time at which the state of the capture process changed |
SGA_USED | NUMBER | The total amount of shared memory (in bytes) currently used by the capture process out of the amount allocated (SGA_ALLOCATED) |
SGA_ALLOCATED | NUMBER | The total amount of shared memory (in bytes) allocated from the Streams pool for the capture process |
BYTES_OF_REDO_MINED | VARCHAR2(64) | The total amount of redo data mined (in bytes) since the capture process last started |
SESSION_RESTART_SCN | VARCHAR2(64) | The SCN from which the capture process started mining redo data when it was last started |
|
Note: TheELAPSED_CAPTURE_TIME, ELAPSED_RULE_TIME, ELAPSED_ENQUEUE_TIME, ELAPSED_LCR_TIME, and ELAPSED_REDO_WAIT_TIME columns are only populated if the TIMED_STATISTICS initialization parameter is set to true, or if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. |
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
V$XSTREAM_MESSAGE_TRACKING displays information about LCRs tracked through the stream that are processed by XStream components.
You can track an LCR through a stream using one of the following methods:
Set the message_tracking_frequency capture process parameter to 1 or another relatively low value.
Run the SET_MESSAGE_TRACKING procedure in the DBMS_STREAMS_ADM package.
When the actions parameter in the DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING procedure is set to DBMS_STREAMS_ADM.ACTION_MEMORY, information about the LCRs is tracked in memory, and this view is populated with information about the LCRs. Currently, DBMS_STREAMS_ADM.ACTION_MEMORY is the only valid setting for the actions parameter in the procedure.
|
Note: This view does not display information about messages flowing in an Oracle Streams configuration. To view information about such message streams, query theV$STREAMS_MESSAGE_TRACKING view. |
| Column | Data Type | Description |
|---|---|---|
TRACKING_LABEL | VARCHAR2(30) | User-specified tracking label |
TAG | RAW(30) | First 30 bytes of the tag of the LCR |
COMPONENT_NAME | VARCHAR2(30) | Name of the component that processed the LCR |
COMPONENT_TYPE | VARCHAR2(30) | Type of the component that processed the LCR |
ACTION | VARCHAR2(50) | Action performed on the LCR |
ACTION_DETAILS | VARCHAR2(100) | Details of the action |
TIMESTAMP | TIMESTAMP(9) WITH TIME ZONE | Time when the action was performed |
MESSAGE_CREATION_TIME | DATE | Time when the message was created |
MESSAGE_NUMBER | NUMBER | SCN of the message |
TRACKING_ID | RAW(16) | Globally unique OID of the LCR |
SOURCE_DATABASE_NAME | VARCHAR2(128) | Name of the source database |
OBJECT_OWNER | VARCHAR2(30) | Owner of the object |
OBJECT_NAME | VARCHAR2(30) | Name of the object |
XID | VARCHAR2(128) | Transaction ID |
COMMAND_TYPE | VARCHAR2(30) | Command type of the LCR |
MESSAGE_POSITION | RAW(64) | Position of the message |
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
V$XSTREAM_OUTBOUND_SERVER displays statistics about an outbound server. An outbound server sends LCRs to an XStream client application.
|
Note: When theCOMMITTED_DATA_ONLY column is YES in the V$XSTREAM_OUTBOUND_SERVER view, the V$STREAMS_APPLY_SERVER view provides additional information about the outbound server process, and information about the apply server background processes used by the outbound server. |
| Column | Data Type | Description |
|---|---|---|
SID | NUMBER | Session ID of the outbound server's session |
SERIAL# | NUMBER | Serial number of the outbound server's session |
SPID | VARCHAR2(12) | Process identification number of the operating-system process that sends LCRs to the client application |
SERVER_NAME | VARCHAR2(30) | Name of the outbound server |
STARTUP_TIME | DATE | Time when the client application attached to the outbound server |
STATE | VARCHAR2(37) | State of the outbound server
When the
When the
When a state refers to a capture process, it is the capture process that captures changes for the outbound server. When a state refers to a propagation, it is the outbound server that sends LCRs to the XStream client application. |
XIDUSN | NUMBER | Transaction ID undo segment number of the transaction currently being processed
This column is populated only if the |
XIDSLT | NUMBER | Transaction ID slot number of the transaction currently being processed
This column is populated only if the |
XIDSQN | NUMBER | Transaction ID sequence number of the transaction currently being processed
This column is populated only if the |
COMMITSCN | NUMBER | Commit SCN of the transaction currently being processed
This column is populated only if the |
TOTAL_TRANSACTIONS_SENT | NUMBER | Total number of transactions sent by the outbound server to the XStream client application since the last time the client application attached to the outbound server
This column is populated only if the |
MESSAGE_SEQUENCE | NUMBER | Number of the current LCR being processed by the outbound server. This value is reset to 1 at the beginning of each transaction.
This column is populated only if the |
TOTAL_MESSAGES_SENT | NUMBER | Total number of LCRs sent by the outbound server to the XStream client application since the last time the client application attached to the outbound server |
SEND_TIME | DATE | Time the last LCR was sent by the outbound server to the XStream client application |
LAST_SENT_MESSAGE_NUMBER | NUMBER | Message number of the last LCR sent by the outbound server to the XStream client application |
LAST_SENT_MESSAGE_CREATE_TIME | DATE | Creation time at the source database of the last LCR sent by the outbound server to the client application |
ELAPSED_SEND_TIME | NUMBER | Time elapsed (in hundredths of a second) sending LCRs to the XStream client application since the last time the client application attached to the outbound server |
COMMIT_POSITION | RAW(64) | Commit position of the transaction currently being processed
This column is populated only if the |
LAST_SENT_POSITION | RAW(64) | Position of the last LCR sent to the XStream client application
This column is populated only if the |
BYTES_SENT | NUMBER | Total number of bytes sent by the outbound server to the XStream client application since the last time the client application attached to the outbound server |
COMMITTED_DATA_ONLY | VARCHAR2(3) | YES if the outbound server can send only LCRs in committed transactions to the XStream client application. A committed transaction is an assembled, noninterleaving transaction with no rollbacks.
|
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
V$XSTREAM_TRANSACTION displays information about transactions that are being processed by capture processes, outbound servers, and inbound servers. This view can identify long running transactions and display how many LCRs are being processed in each transaction. This view only contains information about captured LCRs. It does not contain information about user-enqueued LCRs or user messages.
This view only shows information about LCRs that are being processed because they satisfied the rule sets for the component at the time of the query. For capture processes, this view only shows information about changes in transactions that the capture process has converted into LCRs. It does not show information about all the active transactions present in the redo log.
For outbound servers, this view only shows information about LCRs that the outbound server has dequeued. It does not show information about LCRs in the outbound server's queue. For outbound servers, information about a transaction remains in the view until the transaction is sent to the XStream client application.
For inbound servers, information about a transaction remains in the view until the transaction commits or until the entire transaction is rolled back.
|
Note: This view does not display information about Oracle Streams transactions. To view information about Oracle Streams transactions, query theV$STREAMS_TRANSACTION view. |
| Column | Data Type | Description |
|---|---|---|
COMPONENT_NAME | VARCHAR2(30) | Name of the component |
COMPONENT_TYPE | VARCHAR2(10) | Type of component:
|
XIDUSN | NUMBER | Transaction ID undo segment number of the transaction |
XIDSLT | NUMBER | Transaction ID slot number of the transaction |
XIDSQN | NUMBER | Transaction ID sequence number of the transaction |
CUMULATIVE_MESSAGE_COUNT | NUMBER | Number of LCRs processed in the transaction. If a component is restarted while the transaction is being processed, then this column shows the number of LCRs processed in the transaction since the component was started. |
TOTAL_MESSAGE_COUNT | NUMBER | Total number of LCRs processed in the transaction by an outbound server or inbound server. This column does not pertain to capture processes. |
FIRST_MESSAGE_TIME | DATE | Time stamp of the first LCR processed in the transaction. If a capture process is restarted while the transaction is being processed, then this column shows the time stamp of the first LCR processed after the capture process was started. |
FIRST_MESSAGE_NUMBER | NUMBER | SCN of the first message in the transaction. If a capture process is restarted while the transaction is being processed, then this column shows the SCN of the first message processed after the capture process was started. |
LAST_MESSAGE_TIME | DATE | Time stamp of the last LCR processed in the transaction |
LAST_MESSAGE_NUMBER | NUMBER | SCN of the most recent message encountered in the transaction |
FIRST_MESSAGE_POSITION | RAW(64) | Position of the first message seen by an XStream inbound server
This column is populated only for an apply process that is functioning as an XStream inbound server. |
LAST_MESSAGE_POSITION | RAW(64) | Position of the last message seen by an XStream inbound server
This column is populated only for an apply process that is functioning as an XStream inbound server. |
TRANSACTION_ID | VARCHAR2(128) | Transaction ID for an XStream inbound server
This column is populated only for an apply process that is functioning as an XStream inbound server. |
This chapter provides instructions for monitoring XStream.
This chapter contains these topics:
This chapter describes monitoring an XStream Out configuration and an XStream In configuration. This chapter provides instructions for querying data dictionary views related to XStream. The queries provide information about XStream components and statistics related to XStream.
The main interface for monitoring XStream database components is SQL*Plus, although you can monitor some aspects of an XStream configuring using Oracle Enterprise Manager. For example, you can view information about capture processes, outbound servers, inbound servers, and rules in Enterprise Manager. Outbound servers and inbound servers appear as apply processes in Enterprise Manager.
This chapter also describes using the Oracle Streams Performance Advisor to monitor an XStream configuration. The Oracle Streams Performance Advisor consists of the DBMS_STREAMS_ADVISOR_ADM package and a collection of data dictionary views. The Oracle Streams Performance Advisor enables you to monitor the topology and performance of an XStream environment.
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
The query in this section displays the following session information about each XStream component in a database:
The XStream component name
The session identifier
The serial number
The operating system process identification number
The XStream process number
This query is especially useful for determining the session information for specific XStream components when there are multiple XStream Out or XStream In components configured in a database.
To display this information for each XStream component in a database:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN ACTION HEADING 'XStream Component' FORMAT A30
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 99999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A17
COLUMN PROCESS_NAME HEADING 'XStream|Process|Number' FORMAT A7
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
SID,
SERIAL#,
PROCESS,
SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
FROM V$SESSION
WHERE MODULE ='XStream';
Your output for an XStream Out configuration looks similar to the following:
Session XStream
Serial Operating System Process
XStream Component Session ID Number Process Number Number
------------------------------ ---------- --------- ----------------- -------
XOUT - Apply Server 35 33 16386 TNS
XOUT - Apply Coordinator 41 1 14093 AP01
XOUT - Apply Reader 43 1 14095 AS01
XOUT - Apply Server 45 1 14097 AS02
XOUT - Propagation Send/Rcv 47 55 16401 CS01
CAP$_XOUT_1 - Capture 48 7 16399 CP01
The row that shows TNS for the XStream process number contains information about the session for the XStream client application that is attached to the outbound server.
Your output for an XStream In configuration looks similar to the following:
Session XStream
Serial Operating System Process
XStream Component Session ID Number Process Number Number
------------------------------ ---------- --------- ----------------- -------
XIN - Propagation Receiver 32 21 16386 TNS
XIN - Apply Coordinator 38 23 16414 AP01
XIN - Apply Reader 40 3 16418 AS01
XIN - Apply Server 42 1 16420 AS02
XIN - Apply Server 44 1 16422 AS03
XIN - Apply Server 46 1 16424 AS04
XIN - Apply Server 48 1 16426 AS05
The row that shows TNS for the XStream process number contains information about the session for the XStream client application that is attached to the inbound server.
This section provides sample queries that you can use to monitor XStream Out.
This section contains these topics:
Displaying Status and Error Information for an Outbound Server
Displaying Information About an Outbound Server's Current Transaction
Displaying the Processed Low Position for an Outbound Server
With XStream Out, an Oracle Streams apply process functions as an outbound server. Therefore, you can also use the data dictionary views for apply processes to monitor outbound servers. In addition, an XStream Out environment includes capture processes and queues, and might include other components, such as propagations, rules, and rule-based transformations.
You can display the following information for an outbound server by running the query in this section:
The outbound server name
The name of the connect user for the outbound server
The connect user is the user who can attach to the outbound server to retrieve the logical change record (LCR) stream. The client application must attach to the outbound server as the specified connect user.
The name of the capture user for the capture process that captures changes for the outbound server to process
The name of the capture process that captures changes for the outbound server to process
The name of the source database for the captured changes
The owner of the queue used by the outbound server
The name of the queue used by the outbound server
The DBA_XSTREAM_OUTBOUND view contains information about the capture user, the capture process, and the source database in either of the following cases:
The outbound server was created using the CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.
The outbound server was created using the ADD_OUTBOUND procedure in the DBMS_XSTREAM_ADM package, and the capture process for the outbound server runs on the same database as the outbound server.
If the outbound server was created using the ADD_OUTBOUND procedure, and the capture process for the outbound server is on a different database, then the DBA_XSTREAM_OUTBOUND view does not contain information about the capture user, the capture process, or the source database.
To display this general information about an outbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A10
COLUMN CONNECT_USER HEADING 'Connect|User' FORMAT A10
COLUMN CAPTURE_USER HEADING 'Capture|User' FORMAT A10
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A11
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A11
COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A10
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A10
SELECT SERVER_NAME,
CONNECT_USER,
CAPTURE_USER,
CAPTURE_NAME,
SOURCE_DATABASE,
QUEUE_OWNER,
QUEUE_NAME
FROM DBA_XSTREAM_OUTBOUND;
Your output looks similar to the following:
Outbound Capture
Server Connect Capture Process Source Queue Queue
Name User User Name Database Owner Name
---------- ---------- ---------- ----------- ----------- ---------- ----------
XOUT XSTRMADMIN XSTRMADMIN CAP$_XOUT_1 DB.EXAMPLE. XSTRMADMIN Q$_XOUT_2
COM
You can monitor an outbound server using the same queries as you use to monitor an Oracle Streams apply process. See Oracle Streams Concepts and Administration for instructions.
The ALL_APPLY and DBA_APPLY views show XStream Out in the PURPOSE column for an apply process that is functioning as an outbound server.
To display detailed information about an outbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A10
COLUMN STATUS HEADING 'Status' FORMAT A8
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 9999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40
SELECT APPLY_NAME,
STATUS,
ERROR_NUMBER,
ERROR_MESSAGE
FROM DBA_APPLY
WHERE PURPOSE = 'XStream Out';
Your output looks similar to the following:
Apply Name Status Error Number Error Message ---------- -------- ------------ ---------------------------------------- XOUT ENABLED
This output shows that XOUT is an apply process that is functioning as an outbound server. Use the instructions in Oracle Streams Concepts and Administration to display detailed information about the outbound server.
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
The V$XSTREAM_OUTBOUND_SERVER view contains the following information about the transaction currently being processed by an XStream outbound server:
The name of the outbound server
The transaction ID of the transaction currently being processed
Commit system change number (SCN) of the transaction currently being processed
Commit position of the transaction currently being processed
The position of the last LCR sent to the XStream client application
The message number of the current LCR being processed by the outbound server
Run this query to determine how many LCRs an outbound server has processed in a specific transaction. You can query the TOTAL_MESSAGE_COUNT column in the V$XSTREAM_TRANSACTION view to determine the total number of LCRs in a transaction.
To display information about an outbound server's current transaction:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A10
COLUMN 'Transaction ID' HEADING 'Transaction|ID' FORMAT A11
COLUMN COMMITSCN HEADING 'Commit SCN' FORMAT 9999999999999
COLUMN COMMIT_POSITION HEADING 'Commit Position' FORMAT A15
COLUMN LAST_SENT_POSITION HEADING 'Last Sent|Position' FORMAT A15
COLUMN MESSAGE_SEQUENCE HEADING 'Message|Number' FORMAT 999999999
SELECT SERVER_NAME,
XIDUSN ||'.'||
XIDSLT ||'.'||
XIDSQN "Transaction ID",
COMMITSCN,
COMMIT_POSITION,
LAST_SENT_POSITION,
MESSAGE_SEQUENCE
FROM V$XSTREAM_OUTBOUND_SERVER;
Your output looks similar to the following:
Outbound
Server Transaction Last Sent Message
Name ID Commit SCN Commit Position Position Number
---------- ----------- -------------- --------------- --------------- ----------
XOUT 17.23.59 645856 00000009DAE0000 00000009DAE0000 4
000010000000100 000010000000100
000009DAE000000 000009DAE000000
0010000000101 0010000000101
|
Note: TheCOMMITSCN and COMMIT_POSITION values are populated only if the COMMITTED_DATA_ONLY value is YES in V$XSTREAM_OUTBOUND_SERVER. |
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
The V$XSTREAM_OUTBOUND_SERVER view contains the following statistics about the database changes processed by an XStream outbound server:
The name of the outbound server
The number of transactions sent from the outbound server to the XStream client application since the last time the client application attached to the outbound server
The number of LCRs sent from the outbound server to the XStream client application since the last time the client application attached to the outbound server
The number of megabytes sent from the outbound server to the XStream client application since the last time the client application attached to the outbound server
The amount of time the outbound server spent sending LCRs to the XStream client application since the last time the client application attached to the outbound server
The message number of the last LCR sent by the outbound server to the XStream client application
Creation time at the source database of the last LCR sent by the outbound server to the client application
To display statistics for an outbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A8
COLUMN TOTAL_TRANSACTIONS_SENT HEADING 'Total|Trans|Sent' FORMAT 9999999
COLUMN TOTAL_MESSAGES_SENT HEADING 'Total|LCRs|Sent' FORMAT 9999999999
COLUMN BYTES_SENT HEADING 'Total|MB|Sent' FORMAT 99999999999999
COLUMN ELAPSED_SEND_TIME HEADING 'Time|Sending|LCRs|(in seconds)' FORMAT 99999999
COLUMN LAST_SENT_MESSAGE_NUMBER HEADING 'Last|Sent|Message|Number' FORMAT 99999999
COLUMN LAST_SENT_MESSAGE_CREATE_TIME HEADING 'Last|Sent|Message|Creation|Time' FORMAT A10
SELECT SERVER_NAME,
TOTAL_TRANSACTIONS_SENT,
TOTAL_MESSAGES_SENT,
(BYTES_SENT/1024)/1024 BYTES_SENT,
(ELAPSED_SEND_TIME/100) ELAPSED_SEND_TIME,
LAST_SENT_MESSAGE_NUMBER,
LAST_SENT_MESSAGE_CREATE_TIME
FROM V$XSTREAM_OUTBOUND_SERVER;
Your output looks similar to the following:
Last
Time Last Sent
Outbound Total Total Total Sending Sent Message
Server Trans LCRs MB LCRs Message Creation
Name Sent Sent Sent (in seconds) Number Time
-------- -------- ----------- --------------- ------------ --------- ----------
XOUT 2000 216000 56 291 9381070 4-AUG-10
11:03 A.M.
|
Note: TheTOTAL_TRANSACTIONS_SENT value is populated only if the COMMITTED_DATA_ONLY value is YES in V$XSTREAM_OUTBOUND_SERVER. |
For an outbound server, the processed low position is the position below which all transactions have been committed and logged by the client application. The processed low position is important when the outbound server or the client application is restarted.
You can display the following information about the processed low position for an outbound server by running the query in this section:
The outbound server name
The name of the source database for the captured changes
The processed low position, which indicates the low watermark position processed by the client application
The time when the processed low position was last updated by the outbound server
To display the processed low position for an outbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A10
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A20
COLUMN PROCESSED_LOW_POSITION HEADING 'Processed|Low LCR|Position' FORMAT A30
COLUMN PROCESSED_LOW_TIME HEADING 'Processed|Low|Time' FORMAT A9
SELECT SERVER_NAME,
SOURCE_DATABASE,
PROCESSED_LOW_POSITION,
TO_CHAR(PROCESSED_LOW_TIME,'HH24:MI:SS MM/DD/YY') PROCESSED_LOW_TIME
FROM DBA_XSTREAM_OUTBOUND_PROGRESS;
Your output looks similar to the following:
Outbound Processed Processed
Server Source Low LCR Low
Name Database Position Time
---------- -------------------- ------------------------------ ---------
XOUT DB.EXAMPLE.COM 00000008F17A000000000000000000 13:39:01
000008F17A000000000000000001 07/15/09
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
An outbound server is an Oracle background process. This background process runs only when an XStream client application attaches to the outbound server. The V$XSTREAM_OUTBOUND_SERVER view contains information about this background process.
You can display the following information for an outbound server by running the query in this section:
The outbound server name
The session ID of the outbound server's session
The serial number of the outbound server's session
The process identification number of the operating-system process that sends LCRs to the client application
To display the process information for an outbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN SERVER_NAME HEADING 'Outbound Server Name' FORMAT A20
COLUMN SID HEADING 'Session ID' FORMAT 9999999999
COLUMN SERIAL# HEADING 'Serial Number' FORMAT 9999999999
COLUMN SPID HEADING 'Operating-System Process' FORMAT A25
SELECT SERVER_NAME,
SID,
SERIAL#,
SPID
FROM V$XSTREAM_OUTBOUND_SERVER;
Your output looks similar to the following:
Outbound Server Name Session ID Serial Number Operating-System Process -------------------- ----------- ------------- ------------------------- XOUT 53 406 25783
|
Note: TheV$STREAMS_APPLY_SERVER view provides additional information about the outbound server process, and information about the apply server background processes used by the outbound server. |
This section provides sample queries that you can use to monitor XStream In.
This section contains these topics:
With XStream In, an Oracle Streams apply process functions as an inbound server. Therefore, you can also use the data dictionary views for apply processes to monitor inbound servers.
You can display the following information for an inbound server by running the query in this section:
The inbound server name
The owner of the queue used by the inbound server
The name of the queue used by the inbound server
The apply user for the inbound server
To display general information about an inbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN SERVER_NAME HEADING 'Inbound Server Name' FORMAT A20
COLUMN QUEUE_OWNER HEADING 'Queue Owner' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN APPLY_USER HEADING 'Apply User' FORMAT A15
SELECT SERVER_NAME,
QUEUE_OWNER,
QUEUE_NAME,
APPLY_USER
FROM DBA_XSTREAM_INBOUND;
Your output looks similar to the following:
Inbound Server Name Queue Owner Queue Name Apply User -------------------- --------------- --------------- --------------- XIN XSTRMADMIN XQUEUE XSTRMADMIN
You can monitor an inbound server using the same queries that you use to monitor an Oracle Streams apply process. See Oracle Streams Concepts and Administration for instructions.
The ALL_APPLY and DBA_APPLY views show XStream In in the PURPOSE column for an apply process that is functioning as an inbound server.
To display the status of an inbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A10
COLUMN STATUS HEADING 'Status' FORMAT A8
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 9999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40
SELECT APPLY_NAME,
STATUS,
ERROR_NUMBER,
ERROR_MESSAGE
FROM DBA_APPLY
WHERE PURPOSE = 'XStream In';
Your output looks similar to the following:
Apply Name Status Error Number Error Message ---------- -------- ------------ ---------------------------------------- XIN ENABLED
This output shows that XIN is an apply process that is functioning as an inbound server. Use the instructions in Oracle Streams Concepts and Administration to display detailed information about the inbound server.
For an inbound server, you can view position information by querying the DBA_XSTREAM_INBOUND_PROGRESS view. Specifically, you can display the following position information by running the query in this section:
The inbound server name
The applied low position for the inbound server
The spill position for the inbound server
The applied high position for the inbound server
The processed low position for the inbound server
To display the position information for an inbound server:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN SERVER_NAME HEADING 'Inbound|Server|Name' FORMAT A10
COLUMN APPLIED_LOW_POSITION HEADING 'Applied Low|Position' FORMAT A15
COLUMN SPILL_POSITION HEADING 'Spill Position' FORMAT A15
COLUMN APPLIED_HIGH_POSITION HEADING 'Applied High|Position' FORMAT A15
COLUMN PROCESSED_LOW_POSITION HEADING 'Processed Low|Position' FORMAT A15
SELECT SERVER_NAME,
APPLIED_LOW_POSITION,
SPILL_POSITION,
APPLIED_HIGH_POSITION,
PROCESSED_LOW_POSITION
FROM DBA_XSTREAM_INBOUND_PROGRESS;
Your output looks similar to the following:
Inbound Server Applied Low Applied High Processed Low Name Position Spill Position Position Position ---------- --------------- --------------- --------------- --------------- XIN C10A C11D C10A C11D
The values of the positions shown in the output were set by the client application that attaches to the inbound server. However, the inbound server determines which values are the current applied low position, spill position, applied high position, and processed low position.
The ALL_XSTREAM_RULES and DBA_XSTREAM_RULES views contain information about the rules used by outbound servers and inbound servers. If an outbound server was created using the CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package, then these views also contain information about the rules used by the capture process that sends changes to the outbound server. However, if an outbound server was created using the ADD_OUTBOUND procedure, then these views do not contain information about the capture process rules. Also, these views do not contain information about the rules used by any propagation in the stream from a capture process to an outbound server.
To display information about the rules used by XStream components:
Connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN STREAMS_NAME HEADING 'Oracle|Streams|Name' FORMAT A12
COLUMN STREAMS_TYPE HEADING 'Oracle|Streams|Type' FORMAT A11
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A10
COLUMN RULE_SET_TYPE HEADING 'Rule Set|Type' FORMAT A8
COLUMN STREAMS_RULE_TYPE HEADING 'Oracle|Streams|Rule|Level' FORMAT A7
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11
COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4
SELECT STREAMS_NAME,
STREAMS_TYPE,
RULE_NAME,
RULE_SET_TYPE,
STREAMS_RULE_TYPE,
SCHEMA_NAME,
OBJECT_NAME,
RULE_TYPE
FROM DBA_XSTREAM_RULES;
Your output looks similar to the following:
Oracle Oracle Streams Streams Streams Rule Rule Set Rule Schema Object Rule Name Type Name Type Level Name Name Type ------------ ----------- ---------- -------- ------- ------ ----------- ---- CAP$_XOUT_49 CAPTURE DB52 POSITIVE GLOBAL DML CAP$_XOUT_49 CAPTURE DB53 POSITIVE GLOBAL DDL XOUT APPLY DB55 POSITIVE GLOBAL DML XOUT APPLY DB56 POSITIVE GLOBAL DDL
Notice that the STREAMS_TYPE is APPLY even though the rules are in the positive rule set for the outbound server xout. You can determine the purpose of an apply process by querying the PURPOSE column in the DBA_APPLY view.
To view information about the rules used by all components, including capture processes, propagations, apply processes, outbound servers, and inbound servers, you can query the ALL_STREAMS_RULES and DBA_STREAMS_RULES views. See Oracle Streams Concepts and Administration for sample queries that enable you to monitor rules.
The Oracle Streams Performance Advisor consists of the DBMS_STREAMS_ADVISOR_ADM PL/SQL package and a collection of data dictionary views. The Oracle Streams Performance Advisor enables you to monitor the topology and performance of an XStream environment. The XStream topology includes information about the components in an XStream environment, the links between the components, and the way information flows from capture to consumption. The Oracle Streams Performance Advisor also provides information about how Oracle Streams components are performing.
Apply processes function as XStream outbound servers and inbound servers. In general, the Oracle Streams Performance Advisor works the same way for an Oracle Streams environment with apply processes and an XStream environment with outbound servers or inbound servers. This section describes important considerations about using the Oracle Streams Performance Advisor in an XStream environment.
This section contains these topics:
|
See Also: Oracle Streams Concepts and Administration for detailed information about using the Oracle Streams Performance Advisor |
The Oracle Streams Performance Advisor tracks the following types of components in an XStream environment:
QUEUE
CAPTURE
PROPAGATION SENDER
PROPAGATION RECEIVER
APPLY
The preceding types are the same in an Oracle Streams environment and an XStream environment, except for APPLY. The APPLY component type can be an XStream outbound server or inbound server.
The following subcomponent types are possible for apply processes, outbound servers, and inbound servers:
PROPAGATION SENDER+RECEIVER for sending LCRs from a capture process directly to an apply process or outbound server in a combined capture and apply optimization
APPLY READER for a reader server
APPLY COORDINATOR for a coordinator process
APPLY SERVER for an apply server
In addition, the Oracle Streams Performance Advisor identifies a bottleneck component as the busiest component or the component with the least amount of idle time. In an XStream configuration, the XStream client application might be the bottleneck when EXTERNAL appears in the ACTION_NAME column of the DBA_STREAMS_TP_PATH_BOTTLENECK view.
In the Oracle Streams topology, a stream path is a flow of messages from a source to a destination. A stream path begins where a capture process, synchronous capture, or application enqueues messages into a queue. A stream path ends where an apply process, outbound server, or inbound server dequeues the messages. The stream path might flow through multiple queues and propagations before it reaches an apply process, outbound server, or inbound server. Therefore, a single stream path can consist of multiple source/destination component pairs before it reaches last component.
The Oracle Streams topology only gathers information about a stream path if the stream path ends with an apply process, an outbound server, or an inbound server. The Oracle Streams topology does not track stream paths that end when a messaging client or an application that dequeues messages.
The Oracle Streams Performance Advisor tracks the following component-level statistics:
The MESSAGE APPLY RATE is the average number of messages applied each second by the apply process, outbound server, or inbound server.
The TRANSACTION APPLY RATE is the average number of transactions applied by the apply process, outbound server, or inbound server each second. Transactions typically include multiple messages.
An LCR can be applied in one of the following ways:
An apply process or inbound server makes the change encapsulated in the LCR to a database object.
An apply process or inbound server passes the LCR to an apply handler.
If the LCR raises an error, then an apply process or inbound server sends the LCR to the error queue.
An outbound server passes the LCR to an XStream client application. If the LCR raises an error, then the outbound server also reports the error to the client application.
Also, the Oracle Streams Performance Advisor tracks the LATENCY component-level statistics. LATENCY is defined in the following ways:
For apply processes, the LATENCY is the amount of time between when the message was created at a source database and when the message was applied by the apply process at the destination database.
For outbound servers, the LATENCY is amount of time between when the message was created at a source database and when the message was sent to the XStream client application.
For inbound servers, the LATENCY is amount of time between when the message was created by the XStream client application and when the message was applied by the apply process.
When a capture process creates an LCR, the message creation time is the time when the redo entry for the database change was recorded. When an XStream client application creates an LCR, the message creation time is the time when the LCR was constructed.
|
See Also: Oracle Streams Concepts and Administration for more information about component-level statistics |
The UTL_SPADV package provides subprograms to collect and analyze statistics for the XStream components in a distributed database environment. The package uses the Oracle Streams Performance Advisor to gather statistics, and the output is formatted so that it can be imported into a spreadsheet easily and analyzed.
The UTL_SPADV package works the same way for an Oracle Streams environment with apply processes and an XStream environment with outbound servers or inbound servers. However, there are some differences in the output for the SHOW_STATS procedure. This section describes the differences between the output for apply processes and the output for XStream outbound servers and inbound servers.
|
Note: The rest of this section assumes that you are familiar with theUTL_SPADV package and the SHOW_STATS output for apply processes. See Oracle Streams Concepts and Administration and Oracle Database PL/SQL Packages and Types Reference for detailed information about using the UTL_SPADV package. |
The following sections describe the output for the SHOW_STATS procedure for outbound servers and inbound servers:
Sample Output When an Outbound Server Is the Last Component in a Path
Sample Output When an Inbound Server Is the Last Component in a Path
The following is sample output for when an outbound server is the last component in a path:
OUTPUT PATH 1 RUN_ID 2 RUN_TIME 2009-MAY-15 12:20:55 CCA Y |<C> CAP$_XOUT_1 2733 2730 3392 LMR 8.3% 91.7% 0% "" LMP (1) 8.3% 91.7% 0% "" LMB 8.3% 91.7% 0% "" CAP 8.3% 91.7% 0% "" |<Q> "XSTRMADMIN"."Q$_XOUT_2" 2730 0.01 4109 |<A> XOUT 2329 2.73 0 -1 PS+PR 8.3% 91.7% 0% "" APR 8.3% 91.7% 0% "" APC 100% 0% 0% "" APS (1) 8.3% 83.3% 8.3% "" |<B> "EXTERNAL" . . .
|
Note: This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions. |
In this output, the A component is the outbound server XOUT. The output for when an outbound server is the last component in a path is similar to the output for when an apply process is the last component in a path. However, the apply server (APS) is not the last component because the outbound server connects to a client application. Statistics are not collected for the client application.
In an XStream Out configuration, the output can indicate flow control for the network because "SQL*Net more data to client" for an apply server is considered as a flow control event. If the output indicates flow control for an apply server, then either the network or the client application is considered the bottleneck component. In the previous output, EXTERNAL indicates that either the network or the client application is the bottleneck.
Other than these differences, you can interpret the statistics in the same way that you would for a path that ends with an apply process. Use the legend and the abbreviations to determine the statistics in the output.
The following is sample output for when an inbound server is the last component in a path:
OUTPUT PATH 1 RUN_ID 2 RUN_TIME 2009-MAY-16 10:11:38 CCA N |<PR> "clientcap"=> 75% 0% 8.3% "CPU + Wait for CPU" |<Q> "XSTRMADMIN"."QUEUE2" 467 0.01 1 |<A> XIN 476 4.71 0 APR 100% 0% 0% "" APC 100% 0% 0% "" APS (4) 366.7% 0% 33.3% "CPU + Wait for CPU" |<B> "EXTERNAL" . . .
|
Note: This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions. |
In this output, the A component is the inbound server XIN. When an inbound server is the last component in a path, the XStream client application connects to the inbound server, and the inbound server applies the changes in the LCRs. The client application is not shown in the output.
The propagation receiver receives the LCRs from the client application. So, the propagation receiver is the first component shown in the output. In the previous sample output, the propagation receiver is named clientcap. In this case, clientcap is the source name given by the client application when it attaches to the inbound server.
If the propagation receiver is idle for a significant percentage of time, then either the network or the client application is considered a bottleneck component. In the previous output, EXTERNAL indicates that either the network or the client application is the bottleneck.
Other than these differences, you can interpret the statistics in the same way that you would for a path that ends with an apply process. Use the legend and the abbreviations to determine the statistics in the output.
This chapter describes configuring the Oracle Database components that are used by XStream. This chapter also includes sample client applications that communicate with an XStream outbound server and inbound server.
This chapter contains these topics:
This section describes preparing for an XStream configuration.
This section contains the following topics:
An XStream administrator configures and manages XStream components in an XStream Out or XStream In environment. This section describes configuring an XStream administrator by granting a user the appropriate privileges. You must configure an XStream administrator in each Oracle database included in the XStream configuration.
Prerequisites
Before configuring an XStream administrator, ensure that the following prerequisites are met:
Ensure that you can log in to each database in the XStream configuration as an administrative user who can create users, grant privileges, and create tablespaces.
Identify a user who will be the XStream administrator. Either create a new user with the appropriate privileges or grant these privileges to an existing user.
Do not use the SYS or SYSTEM user as an XStream administrator, and ensure that the XStream administrator does not use the SYSTEM tablespace as its default tablespace.
If a new tablespace is required for the XStream administrator, then ensure that there is enough disk space on each computer system in the XStream configuration for the tablespace. The recommended size of the tablespace is 25 MB.
Assumptions
This section makes the following assumptions:
The username of the XStream administrator is xstrmadmin.
The tablespace used by the XStream administrator is xstream_tbs.
To configure an XStream administrator:
In SQL*Plus, connect as an administrative user who can create users, grant privileges, and create tablespaces. Remain connected as this administrative user for all subsequent steps.
|
See Also: Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus |
Either create a tablespace for the XStream administrator or use an existing tablespace.
This tablespace stores any objects created in the XStream administrator's schema, including any spillover of messages from the buffered queues owned by the schema.
For example, the following statement creates a new tablespace for the XStream administrator:
CREATE TABLESPACE xstream_tbs DATAFILE '/usr/oracle/dbs/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Create a new user to act as the XStream administrator or identify an existing user.
For example, to create a user named xstrmadmin and specify that this user uses the xstream_tbs tablespace, run the following statement:
CREATE USER xstrmadmin IDENTIFIED BY password
DEFAULT TABLESPACE xstream_tbs
QUOTA UNLIMITED ON xstream_tbs;
|
Note: Enter an appropriate password for the administrative user. |
Grant the Oracle Streams administrator the DBA role:
GRANT DBA TO xstrmadmin;
|
Note: TheDBA role is required for a user to create or alter outbound servers, inbound servers, capture processes, synchronous captures, and apply processes. When the user does not need to perform these tasks, the DBA role can be revoked from the user. |
Run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_XSTREAM_AUTH package.
A user must have explicit EXECUTE privilege on a package to execute a subprogram in the package inside of a user-created subprogram, and a user must have explicit SELECT privilege on a data dictionary view to query the view inside of a user-created subprogram. These privileges cannot be granted through a role. You can run the GRANT_ADMIN_PRIVILEGE procedure to grant such privileges to the XStream administrator, or you can grant them directly.
Depending on the parameter settings for the GRANT_ADMIN_PRIVILEGE procedure, it either grants the privileges for an XStream administrator directly, or it generates a script that you can edit and then run to grant these privileges.
|
Note: Starting with Oracle Database 11g Release 2 (11.2.0.2), theDBMS_XSTREAM_AUTH package is available. |
Use the GRANT_ADMIN_PRIVILEGE procedure to grant privileges directly:
Run the following procedure:
BEGIN
DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'xstrmadmin',
grant_privileges => TRUE);
END;
/
Use the GRANT_ADMIN_PRIVILEGE procedure to generate a script:
Complete the following steps:
Use the SQL statement CREATE DIRECTORY to create a directory object for the directory into which you want to generate the script. A directory object is similar to an alias for the directory. For example, to create a directory object called xstrm_dir for the /usr/admin directory on your computer system, run the following procedure:
CREATE DIRECTORY xstrm_dir AS '/usr/admin';
Run the GRANT_ADMIN_PRIVILEGE procedure to generate a script named grant_xstrm_privs.sql and place this script in the /usr/admin directory on your computer system:
BEGIN
DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'xstrmadmin',
grant_privileges => FALSE,
file_name => 'grant_xstrm_privs.sql',
directory_name => 'xstrm_dir');
END;
/
Notice that the grant_privileges parameter is set to FALSE so that the procedure does not grant the privileges directly. Also, notice that the directory object created in Step a is specified for the directory_name parameter.
Edit the generated script if necessary and save your changes.
Execute the script in SQL*Plus:
SET ECHO ON SPOOL grant_xstrm_privs.out @/usr/admin/grant_xstrm_privs.sql SPOOL OFF
Check the spool file to ensure that all of the grants executed successfully. If there are errors, then edit the script to correct the errors and rerun it.
If necessary, grant the following additional privileges:
If you plan to use Oracle Enterprise Manager to manage databases with XStream components, then configure the XStream administrator to be a Database Control administrator. Doing so grants additional privileges required by Oracle Enterprise Manager, such as the privileges required to run Oracle Enterprise Manager jobs. See Oracle Database 2 Day DBA for instructions.
Grant the privileges for a remote XStream administrator to perform actions in the local database. Grant these privileges using the GRANT_REMOTE_ADMIN_ACCESS procedure in the DBMS_XSTREAM_AUTH package. Grant this privilege if a remote XStream administrator will use a database link that connects to the local XStream administrator to perform administrative actions. Specifically, grant these privileges if either of the following conditions are true:
You plan to configure a downstream capture process at a remote downstream database that captures changes originating at the local source database, and the downstream capture process will use a database link to perform administrative actions at the source database.
You plan to use a remote XStream administrator to set the instantiation system change number (SCN) values for replicated database objects at the local database.
If no apply user is specified for an inbound server, then grant the XStream administrator the necessary privileges to perform DML and DDL changes on the apply objects owned by other users. If an apply user is specified, then the apply user must have these privileges. These privileges can be granted directly or through a role.
If no apply user is specified for an inbound server, then grant the XStream administrator EXECUTE privilege on any PL/SQL subprogram owned by another user that is executed by an inbound server. These subprograms can be used in apply handlers or error handlers. If an apply user is specified, then the apply user must have these privileges. These privileges must be granted directly. They cannot be granted through a role.
Grant the XStream administrator EXECUTE privilege on any PL/SQL function owned by another user that is specified in a custom rule-based transformation for a rule used by a capture process, synchronous capture, propagation, outbound server, or inbound server. For a capture process or synchronous capture, if a capture user is specified, then the capture user must have these privileges. For an inbound server, if an apply user is specified, then the apply user must have these privileges. These privileges must be granted directly. They cannot be granted through a role.
Grant the XStream administrator privileges to alter database objects where appropriate. For example, if the XStream administrator must create a supplemental log group for a table in another schema, then the XStream administrator must have the necessary privileges to alter the table. These privileges can be granted directly or through a role.
If the XStream administrator does not own the queue used by a capture process, synchronous capture, propagation, outbound server, or inbound server, and is not specified as the queue user for the queue when the queue is created, then the XStream administrator must be configured as a secure queue user of the queue if you want the XStream administrator to be able to enqueue messages into or dequeue messages from the queue. The XStream administrator might also need ENQUEUE or DEQUEUE privileges on the queue, or both. See Oracle Streams Concepts and Administration for information about managing queues.
Grant the XStream administrator EXECUTE privilege on any object types that the XStream administrator might need to access. These privileges can be granted directly or through a role.
If the XStream administrator will use Data Pump to perform export and import operations on database objects in other schemas during an instantiation, then grant the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles to the XStream administrator.
If you are using Oracle Database Vault, then the following additional privileges are required:
The XStream administrator must be granted the DV_STREAMS_ADMIN role to perform the following tasks: create a capture process, create an outbound server, and modify the capture user for a capture process. When the XStream administrator is not performing these tasks, you can revoke DV_STREAMS_ADMIN role from the XStream administrator.
The apply user for an inbound server must be authorized to apply changes to realms that include replicated database objects. The replicated database objects are the objects to which the inbound server applies changes.
To authorize an apply user for a realm, run the DBMS_MACADM.ADD_AUTH_TO_REALM procedure and specify the realm and the apply user. For example, to authorize apply user xstrmadmin for the sales realm, run the following procedure:
BEGIN DBMS_MACADM.ADD_AUTH_TO_REALM( realm_name => 'sales', grantee => 'xstrmadmin'); END; /
In addition, the user who performs the following actions must be granted the BECOME USER system privilege:
Creates or alters a capture process
Creates or alters an outbound server
Creates or alters an inbound server
Granting the BECOME USER system privilege to the user who performs these actions is not required if Oracle Database Vault is not installed. You can revoke the BECOME USER system privilege from the user after the completing one of these actions, if necessary.
Repeat all of the previous steps at each Oracle database in the environment that will use XStream.
This section describes the decisions to make and the tasks to complete to prepare for an XStream Out configuration.
When you configure XStream Out, you must configure XStream components to capture database changes and send these changes to the outbound server in the form of logical change records (LCRs). These components include a capture process and at least one queue. The capture process can be a local capture process or a downstream capture process. For some configurations, you must also configure a propagation.
Local capture means that a capture process runs on the source database. Downstream capture means that a capture process runs on a database other than the source database. The source database is the database where the changes were generated. The primary reason to use downstream capture is to reduce the load on the source database, thereby improving its performance. The primary reason to use a local capture is because it is easier to configure and maintain.
The database that captures changes made to the source database is called the capture database. One of the following databases can be the capture database:
Source database (local capture)
Destination database (downstream capture)
A third database (downstream capture)
If the database running the outbound server is not the capture database, then a propagation sends changes from the capture database to the database running the outbound server. If the database running the outbound server is the capture database, then this propagation between databases is not needed because the capture process and outbound server use the same queue.
You can configure the components in the following ways:
Local capture and outbound server in the same database: The database objects, capture process, and outbound server are all in the same database. This configuration is the easiest to configure and maintain because all of the components are contained in one database. See Figure 4-1 for an overview of this configuration.
Local capture and outbound server in different databases: The database objects and capture process are in one database, and the outbound server is in another database. A propagation sends LCRs from the source database to the outbound server database. This configuration is best when you want easy configuration and maintenance and when you want to optimize the performance of the outbound server database. See Figure 4-2 for an overview of this configuration.
Downstream capture and outbound server in the same database: The database objects are in one database, and the capture process and outbound server are in another database. This configuration is best when you want to optimize the performance of the database with the database objects and want to offload change capture to another database. With this configuration, most of the components run on the database with the outbound server. See Figure 4-3 for an overview of this configuration.
Downstream capture and outbound server in different databases: The database objects are in one database, the outbound server is in another database, and the capture process is in a third database. This configuration is best when you want to optimize the performance of both the database with the database objects and the database with the outbound server. With this configuration, the capture process runs on a third database, and a propagation sends LCRs from the capture database to the outbound server database. See Figure 4-4 for an overview of this configuration.
The following figures illustrate these different configurations.
Figure 4-1 Local Capture and Outbound Server in the Same Database
Figure 4-2 Local Capture and Outbound Server in Different Databases
Figure 4-3 Downstream Capture and Outbound Server in the Same Database
Figure 4-4 Downstream Capture and Outbound Server in Different Databases
If you decide to configure a downstream capture process, then you must decide which type of downstream capture process you want to configure. The following types are available:
A real-time downstream capture process configuration means that redo transport services use the log writer process (LGWR) at the source database to send redo data to the downstream database, and a remote file server process (RFS) at the downstream database receives the redo data over the network and stores the redo data in the standby redo log.
An archived-log downstream capture process configuration means that archived redo log files from the source database are copied to the downstream database, and the capture process captures changes in these archived redo log files. These log files can be transferred automatically using redo transport services, or they can be transferred manually using a method such as FTP.
The advantage of real-time downstream capture over archived-log downstream capture is that real-time downstream capture reduces the amount of time required to capture changes made to the source database. The time is reduced because the real-time downstream capture process does not need to wait for the redo log file to be archived before it can capture changes from it. You can configure multiple real-time downstream capture processes that capture changes from the same source database, but you cannot configure real-time downstream capture for multiple source databases at one downstream database.
The advantage of archived-log downstream capture over real-time downstream capture is that archived-log downstream capture allows downstream capture processes for multiple source databases at a downstream database. You can copy redo log files from multiple source databases to a single downstream database and configure multiple archived-log downstream capture processes to capture changes in these redo log files.
Preparing for an XStream Out outbound server is similar to preparing for an Oracle Streams replication environment. The components used in an Oracle Streams replication environment to capture changes and send them to an apply process are the same components used to capture changes and send them to an outbound server. These components include a capture process and one or more queues. If the capture process runs on a different database than the outbound server, then a propagation is also required.
Several of the tasks described in this section are described in more detail in Oracle Streams Replication Administrator's Guide. This section provides an overview of each task and specific information about completing the task for an XStream Out configuration.
Ensure that the following prerequisites are met before configuring XStream Out:
If Required, Configure Network Connectivity and Database Links
If Required, Configure Log File Transfer to a Downstream Database
If Required, Add Standby Redo Logs for Real-Time Downstream Capture
To configure and manage an XStream Out configuration, create an XStream administrator on each Oracle database that is involved in the XStream Out configuration.
Network connectivity and database links are not required when all of the components run on the same database. These components include the capture process, queue, and outbound server.
You must configure network connectivity and database links if you decided to configure XStream in either of the following ways:
The capture process and the outbound server will run on different databases.
Downstream capture will be used.
See "Decide How to Configure XStream" for more information about these decisions.
If network connectivity is required, then configure your network and Oracle Net so that the databases can communicate with each other.
The following database links are required:
When the capture process runs on a different database from the outbound server, create a database link from the capture database to the outbound server database. A propagation uses this database link to send changes from the capture database to the outbound server database.
When you use downstream capture, create a database link from the capture database to the source database. The source database is the database that generates the redo data that the capture process uses to capture changes. The capture process uses this database link to perform administrative tasks at the source database.
The name of each database link must match the global name of the destination database, and each database link should be created in the XStream administrator's schema.
For example, assume that you want to create a database link in a configuration with the following characteristics:
The global name of the source database is dbs1.example.com.
The global name of the destination database is dbs2.example.com.
The XStream administrator is xstrmadmin at each database.
Given these assumptions, the following statement creates a database link from dbs1.example.com to dbs2.example.com:
CONNECT xstrmadmin@dbs1.example.com Enter password: password CREATE DATABASE LINK dbs2.example.com CONNECT TO xstrmadmin IDENTIFIED BY password USING 'dbs2.example.com';
|
See Also:
|
Each source database that generates changes that will be captured by a capture process must be in ARCHIVELOG mode. For downstream capture processes, the downstream database also must be in ARCHIVELOG mode if you plan to configure a real-time downstream capture process. The downstream database does not need to be in ARCHIVELOG mode if you plan to run only archived-log downstream capture processes on it.
If you are configuring XStream in an Oracle Real Application Clusters (Oracle RAC) environment, then the archived redo log files of all threads from all instances must be available to any instance running a capture process. This requirement pertains to both local and downstream capture processes.
|
See Also: Oracle Database Administrator's Guide for instructions about running a database inARCHIVELOG mode |
Some initialization parameters are important for the configuration, operation, reliability, and performance of the components in an XStream configuration. Set these parameters appropriately.
Oracle Streams Replication Administrator's Guide contains detailed information about all of the initialization parameters that are important for an Oracle Streams environment. The guidelines for setting these parameters also apply to an XStream configuration. In addition to the requirements described in Oracle Streams Replication Administrator's Guide for all Oracle Streams components, the following requirements apply to XStream outbound servers:
Ensure that the PROCESSES initialization parameter is set to a value large enough to accommodate the outbound server background processes and all of the other Oracle Database background processes.
Ensure that the SESSIONS initialization parameter is set to a value large enough to accommodate the sessions used by the outbound server background processes and all of the other Oracle Database sessions.
The Oracle Streams pool is a portion of memory in the System Global Area (SGA) that is used by Oracle Streams. The Oracle Streams pool stores buffered queue messages in memory, and it provides memory for capture processes and outbound servers. The Oracle Streams pool always stores LCRs captured by a capture process, and it stores LCRs and messages that are enqueued into a buffered queue by applications. Ensure that there is enough space in the Oracle Streams pool at each database to store LCRs and run the components properly.
Each outbound server requires 1 MB of memory. The Oracle Streams pool is initialized the first time an outbound server is started.
|
See Also: Oracle Streams Replication Administrator's Guide for information about Oracle Streams pool requirements |
If you decided to use a local capture process, then log file transfer is not required. However, if you decided to use downstream capture that uses redo transport services to transfer archived redo log files to the downstream database automatically, then configure log file transfer from the source database to the capture database. See "Decide How to Configure XStream" for information about this decision.
If you decided to configure real-time downstream capture, then add standby redo logs to the capture database. See "Decide How to Configure XStream" for information about this decision.
Ensure that the following prerequisites are met before configuring XStream In:
To configure and manage an XStream In configuration, create an XStream administrator on the Oracle database that will run the XStream inbound server.
Some initialization parameters are important for the configuration, operation, reliability, and performance of XStream inbound servers. Set these parameters appropriately.
Oracle Streams Replication Administrator's Guide contains detailed information about all of the initialization parameters that are important for an Oracle Streams environment. The guidelines for setting these parameters also apply to an XStream configuration. In addition to the requirements described in Oracle Streams Replication Administrator's Guide for all Oracle Streams components, the following requirements apply to XStream inbound servers:
Ensure that the PROCESSES initialization parameter is set to a value large enough to accommodate the inbound server background processes and all of the other Oracle Database background processes.
Ensure that the SESSIONS initialization parameter is set to a value large enough to accommodate the sessions used by the inbound server background processes and all of the other Oracle Database sessions.
The Oracle Streams pool is a portion of memory in the System Global Area (SGA) that provides memory for inbound servers. Ensure that there is enough space in the Oracle Streams pool for the inbound server to run properly. An inbound server requires 1 MB for each inbound server parallelism. For example, if parallelism is set to 10 for an inbound server, then at least 10 MB of memory is required for the inbound server. The Oracle Streams pool also must have enough space to store LCRs before they are applied. The Oracle Streams pool is initialized the first time an inbound server is started.
An outbound server in an XStream Out configuration streams Oracle database changes to a client application. The client application attaches to the outbound server using the Oracle Call Interface (OCI) or Java interface to receive these changes.
Configuring an outbound server involves creating the components that send captured database changes to the outbound server. It also involves configuring the outbound server itself, which includes specifying the connect user that the client application will use to attach to the outbound server.
This section contains these topics:
You can create an outbound server using the following procedures in the DBMS_XSTREAM_ADM package:
The CREATE_OUTBOUND procedure creates an outbound server, a queue, and a capture process in a single database with one procedure call.
The ADD_OUTBOUND procedure only creates an outbound server. You must create the capture process and queue separately, and they must exist before you run the ADD_OUTBOUND procedure. You can configure the capture process on the same database as the outbound server or on a different database.
In both cases, you must create the client application that communicates with the outbound server and receives LCRs from the outbound server.
If you require multiple outbound servers, then you can use the CREATE_OUTBOUND procedure to create the capture process that captures database changes for the first outbound server. Next, you can run the ADD_OUTBOUND procedure to add additional outbound servers that receive the same captured changes. The capture process can reside on the same database as the outbound servers or on a different database.
This section contains these topics:
The CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package creates a capture process, queue, and outbound server in a single database. Both the capture process and the outbound server use the queue created by the procedure. When you run the procedure, you provide the name of the new outbound server, while the procedure generates a name for the capture process and queue. If you want all of the components to run on the same database, then the CREATE_OUTBOUND procedure is the fastest and easiest way to create an outbound server.
Prerequisites
Before configuring XStream Out, ensure that the following prerequisites are met:
Complete the tasks described in "Prerequisites for Configuring XStream Out".
Assumptions
This section makes the following assumptions:
The capture process will be a local capture process, and it will run on the same database as the outbound server.
The instructions in this section can only set up the local capture and outbound server on the same database configuration described in "Decide How to Configure XStream".
The name of the outbound server is xout.
Data manipulation language (DML) and data definition language (DDL) changes made to the oe.orders and oe.order_items tables are sent to the outbound server.
DML and DDL changes made to the hr schema are sent to the outbound server.
Figure 4-5 provides an overview of this XStream Out configuration.
Figure 4-5 Sample XStream Out Configuration Created Using CREATE_OUTBOUND
To create an outbound server using the CREATE_OUTBOUND procedure:
In SQL*Plus, connect to the database as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the CREATE_OUTBOUND procedure.
Given the assumptions for this section, run the following CREATE_OUTBOUND procedure:
DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
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 => 'xout',
table_names => tables,
schema_names => schemas);
END;
/
Running this procedure performs the following actions:
Configures supplemental logging for the oe.orders and oe.order_items tables and for all of the tables in the hr schema.
Creates a queue with a system-generated name that is used by the capture process and the outbound server.
Creates and starts a capture process with a system-generated name with rule sets that instruct it to capture DML and DDL changes to the oe.orders table, the oe.order_items table, and the hr schema.
Creates and starts an outbound server named xout with rule sets that instruct it to send DML and DDL changes to the oe.orders table, the oe.order_items table, and the hr schema to the client application.
Sets the current user as the connect user for the outbound server. In this example, the current user is the XStream administrator. The client application must connect to the database as the connect user to interact with the outbound server.
|
Tip: To capture and send all database changes to the outbound server, specifyNULL (the default) for the table_names and schema_names parameters. |
Create and run the client application that will connect to the outbound server and receive the LCRs. See "Sample XStream Client Application" for a sample application.
To add one or more additional outbound servers that receive LCRs from the capture process created in Step 2, follow the instructions in "Adding an Additional Outbound Server to a Capture Process Stream".
The ADD_OUTBOUND procedure in the DBMS_XSTREAM_ADM package creates an outbound server. This procedure does not create the capture process or the queue. You must configure these components manually.
The instructions in this section can set up any of the configurations described in "Decide How to Configure XStream". However, if you chose the local capture and outbound server on the same database configuration, then it is usually easier to use the CREATE_OUTBOUND procedure to configure all of the components simultaneously. See "Configuring Multiple XStream Out Components Using CREATE_OUTBOUND".
Prerequisites
Before configuring XStream Out, ensure that the following prerequisites are met:
Complete the tasks described in "Prerequisites for Configuring XStream Out".
Assumptions
This section makes the following assumptions:
The name of the outbound server is xout.
The queue used by the outbound server is xstrmadmin.xstream_queue.
The source database is db1.example.com.
DML and DDL changes made to the oe.orders and oe.order_items tables are sent to the outbound server.
DML and DDL changes made to the hr schema are sent to the outbound server.
The capture process for the outbound server does not exist. (If the capture process exists, then skip Steps 1 to 3, and go to Step 4.)
Figure 4-6 provides an overview of this XStream Out configuration.
Figure 4-6 Sample XStream Out Configuration Created Using ADD_OUTBOUND
|
Note: If the capture database and the outbound server database are different databases, then a propagation propagates the LCRs to a queue at the outbound server database. If the capture database and outbound server database are the same, then the propagation is not needed. |
To create an outbound server using the ADD_OUTBOUND procedure:
In SQL*Plus, connect to the database that will run the capture process (the capture database) as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Create the queue that will be used by the capture process.
See Oracle Streams Replication Administrator's Guide for instructions.
Create the capture process.
Add rules to the capture process's rule sets to capture changes to the hr schema, the oe.orders table, and the oe.order_items table. Do not start the capture process.
See Oracle Streams Replication Administrator's Guide for instructions.
If the capture process will run on a different database than the outbound server, then set the xout_client_exists capture process parameter to Y.
Setting this parameter to Y enables the capture process to send LCRs to an outbound server.
Skip this step if the capture process will run on the same database as the outbound server. In this case, the xout_client_exists capture process parameter will be set to Y automatically.
See Oracle Streams Concepts and Administration for information about setting a capture process parameter. See Oracle Database PL/SQL Packages and Types Reference for information about the xout_client_exists capture process parameter.
Connect to the source database.
The source database is the database that contains the database objects for which the capture process will capture changes. The source database and the capture database might be the same database.
Ensure that required supplemental logging is specified for the database objects at the source database.
Supplemental logging is required for the database objects for which the capture process will capture changes. If the capture database and the source database are the same database, then supplemental logging might have been specified during capture process creation.
Ensure that the following supplemental logging is specified at the source database:
Any columns at the source database that are used in a primary key in tables for which changes are processed by the outbound server must be unconditionally logged in a log group or by database supplemental logging of primary key columns.
Any columns at the source database that are used by a rule or a rule-based transformation must be unconditionally logged.
For the example in this section, ensure that supplemental logging is configured for the hr schema, the oe.orders table, and the oe.order_items table.
See Oracle Streams Replication Administrator's Guide for instructions about specifying supplemental logging.
Connect to the database that will run the outbound server as the XStream administrator.
Create the queue that will be used by the outbound server.
This step is not required if the capture process and the outbound server run on the same database and use the same queue.
See Oracle Streams Replication Administrator's Guide for instructions.
Run the ADD_OUTBOUND procedure.
Given the assumption for this section, run the following ADD_OUTBOUND procedure:
DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
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 => 'xout',
queue_name => 'xstrmadmin.xstream_queue',
source_database => 'db1.example.com',
table_names => tables,
schema_names => schemas);
END;
/
If the capture process runs on the same database as the outbound server, then specify the capture process's queue for the queue_name parameter.
Running this procedure performs the following actions:
Creates an outbound server named xout. The outbound server has rule sets that instruct it to send DML and DDL changes to the oe.orders table, the oe.order_items table, and the hr schema to the client application. The rules specify that these changes must have originated at the db1.example.com database. The outbound server dequeues LCRs from the queue xstrmadmin.xstream_queue.
Sets the current user as the connect user for the outbound server. In this example, the current user is the XStream administrator. The client application must connect to the database as the connect user to interact with the outbound server.
|
Tip: For the outbound server to receive all of the LCRs sent by the capture process, specifyNULL (the default) for the table_names and schema_names parameters. |
Connect to the capture database as the XStream administrator.
Create the propagation that sends LCRs from the capture process's queue on the local database to the queue used by the outbound server on the outbound server database.
Add rules to the propagation's rule sets to send changes to the hr schema, the oe.orders table, and the oe.order_items table from the source queue to the destination queue.
This step is not required if the capture process and the outbound server run on the same database and use the same queue.
See Oracle Streams Replication Administrator's Guide for instructions.
Create and run the client application that will connect to the outbound server and receive the LCRs. See "Sample XStream Client Application" for a sample application.
Connect to the database that is running outbound server as the XStream administrator.
Start the outbound server if it is disabled. For example:
exec DBMS_APPLY_ADM.START_APPLY('xout');
Connect to the capture database as the XStream administrator.
Start the capture process created in Step 3.
See Oracle Streams Concepts and Administration for instructions.
To add one or more additional outbound servers that receive LCRs from the capture process created in Step 3, follow the instructions in "Adding an Additional Outbound Server to a Capture Process Stream".
XStream Out configurations often require multiple outbound servers that process a stream of LCRs from a single capture process. This section describes adding an additional outbound server to a database that already includes at least one outbound server. The additional outbound server uses the same queue as another outbound server to receive the LCRs from the capture process. When an XStream Out environment exists, use the ADD_OUTBOUND procedure in the DBMS_XSTREAM_ADM package to add another outbound server to a capture process stream.
Prerequisites
Before completing the steps in this section, configure an XStream Out environment that includes at least one outbound server. The following sections describe configuring and XStream Out environment:
Assumptions
This section makes the following assumptions:
The name of the outbound server is xout2.
The queue used by the outbound server is xstrmadmin.xstream_queue.
DML and DDL changes made to the oe.orders and oe.order_items tables are sent to the outbound server.
DML and DDL changes made to the hr schema are sent to the outbound server.
The source database for the database changes is db1.example.com.
Figure 4-7 provides an overview of this XStream Out configuration.
Figure 4-7 Sample XStream Out Configuration With an Additional Outbound Server
To add another outbound server to a capture process stream using the ADD_OUTBOUND procedure:
In SQL*Plus, connect to the database that will run the additional outbound server as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Determine the name of the queue used by an existing outbound server that receives LCRs from the capture process.
Run the query in "Displaying General Information About an Outbound Server" to determine the owner and name of the queue. This query also shows the name of the capture process and the source database name.
Run the ADD_OUTBOUND procedure.
Given the assumptions for this section, run the following ADD_OUTBOUND procedure:
DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
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 => 'xout2',
queue_name => 'xstrmadmin.xstream_queue',
source_database => 'db1.example.com',
table_names => tables,
schema_names => schemas);
END;
/
Running this procedure performs the following actions:
Creates an outbound server named xout2. The outbound server has rule sets that instruct it to send DML and DDL changes to the oe.orders table, the oe.order_items table, and the hr schema to the client application. The rules specify that these changes must have originated at the db1.example.com database. The outbound server dequeues LCRs from the queue xstrmadmin.xstream_queue.
Sets the current user as the connect user for the outbound server. In this example, the current user is the XStream administrator. The client application must connect to the database as the connect user to interact with the outbound server.
|
Tip: For the outbound server to receive all of the LCRs sent by the capture process, specifyNULL (the default) for the table_names and schema_names parameters. |
If a client application does not exist, then create and run the client application that will connect to the outbound server and receive the LCRs. See "Sample XStream Client Application" for a sample application.
An inbound server in an XStream In configuration receives a stream of changes from a client application. The inbound server can apply these changes to database objects in an Oracle database, or it can process the changes in a customized way. A client application can attach to an inbound server and send row changes and DDL changes encapsulated in LCRs using the OCI or Java interface.
The CREATE_INBOUND procedure in the DBMS_XSTREAM_ADM package creates an inbound server. You must create the client application that communicates with the inbound server and sends LCRs to the inbound server.
Prerequisites
Before configuring XStream In, ensure that the following prerequisites are met:
Complete the tasks described in "Preparing for XStream In".
Assumptions
This section makes the following assumptions:
The name of the inbound server is xin.
The inbound server applies all of the changes it receives from the XStream client application.
The queue used by the inbound server is xstrmadmin.xin_queue.
Figure 4-8 provides an overview of this XStream In configuration.
Figure 4-8 Sample XStream In Configuration
To create an inbound server:
In SQL*Plus, connect to the database that will run the inbound server as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the CREATE_INBOUND procedure.
For example, the following CREATE_INBOUND procedure configures an inbound server named xin:
BEGIN
DBMS_XSTREAM_ADM.CREATE_INBOUND(
server_name => 'xin',
queue_name => 'xin_queue');
END;
/
Running this procedure performs the following actions:
Creates an inbound server named xin.
Sets the queue with the name xin_queue as the inbound server's queue, and creates this queue if it does not exist. This queue does not store LCRs sent by the client application. Instead, it stores error transactions if an LCR raises an error. The current user is the queue owner. In this example, the current user is the XStream administrator.
Sets the current user as the apply user for the inbound server. In this example, the current user is the XStream administrator. The client application must connect to the database as the apply user to interact with the inbound server.
|
Tip: By default, an inbound server does not use rules or rule sets. Therefore, it processes all LCRs sent to it by the client application. To add rules and rule sets, use theDBMS_STREAMS_ADM package or the DBMS_RULE_ADM package. See Oracle Streams Concepts and Administration. |
If necessary, create apply handlers for the inbound server.
Apply handlers are optional. Apply handlers process LCRs sent to an inbound server in a customized way.
Create and run the client application that will connect to the inbound server and send LCRs to it.
See "Sample XStream Client Application" for a sample application.
If the inbound server is disabled, then start the inbound server.
For example, enter the following:
exec DBMS_APPLY_ADM.START_APPLY('xin');
This section contains a sample XStream client application. This application illustrates the basic tasks that are required of an XStream Out and XStream In application.
The application performs the following tasks:
It attaches to an XStream outbound server and inbound server and waits for LCRs from the outbound server. The outbound server and inbound server are in two different databases.
When it receives an LCR from the outbound server, it immediately sends the LCR to the inbound server.
It periodically gets the processed low position from the inbound server and sends this value to the outbound server.
It periodically sends a "ping" LCR from the outbound server to the inbound server to move the inbound server's processed low position forward in times of low activity.
In an XStream Out configuration that does not send LCRs to an inbound server, the client application must obtain the processed low position in another way.
This application waits indefinitely for transactions from the outbound server. To interrupt the application, enter the interrupt command for your operating system. For example, the interrupt command on some operating systems is control-C. If the program is restarted, then the outbound server starts sending LCRs from the processed low position that was set during the previous run.
Figure 4-9 provides an overview of the XStream environment configured in this section.
Before running the sample application, ensure that the following components exist:
Two Oracle databases with network connectivity between them
An XStream administrator on both databases
An outbound server configuration on one database, including a capture process, queue, and outbound server
An inbound server configuration on another database
The sample applications in the following sections perform the same tasks. One sample application uses the OCI API, and the other uses the Java API.
To run the sample XStream client application for the OCI API, compile and link the application file, and enter the following on a command line:
xio -ob_svr xout_name -ob_db sn_xout_db -ob_usr xout_cu -ob_pwd xout_cu_pass -ib_svr xin_name -ib_db sn_xin_db -ib_usr xin_au -ib_pwd xin_au_pass
Substitute the appropriate values for the following placeholders:
xout_name is the name of the outbound server.
sn_xout_db is the service name for the outbound server's database.
xout_cu is the outbound server's connect user.
xout_cu_pass is the password for the outbound server's connect user.
xin_name is the name of the inbound server.
sn_xin_db is the service name for the inbound server's database.
xin_au is the inbound server's apply user.
xin_au_pass is the password for the inbound server's apply user.
When the sample client application is running, it prints information about the row LCRs it is processing. The output looks similar to the following:
----------- ROW LCR Header ----------------- src_db_name=DB.EXAMPLE.COM cmd_type=UPDATE txid=17.0.74 owner=HR oname=COUNTRIES ----------- ROW LCR Header ----------------- src_db_name=DB.EXAMPLE.COM cmd_type=COMMIT txid=17.0.74 ----------- ROW LCR Header ----------------- src_db_name=DB.EXAMPLE.COM cmd_type=UPDATE txid=12.25.77 owner=OE oname=ORDERS ----------- ROW LCR Header ----------------- src_db_name=DB.EXAMPLE.COM cmd_type=UPDATE txid=12.25.77 owner=OE oname=ORDERS
This output contains the following information for each row LCR:
src_db_name shows the source database for the change encapsulated in the row LCR.
cmd_type shows the type of SQL statement that made the change.
txid shows the transaction ID of the transaction that includes the row LCR.
owner shows the owner of the database object that was changed.
oname shows the name of the database object that was changed.
This demo is available in the following location:
$ORACLE_HOME/rdbms/demo/xstream/oci
The file name for the demo is xio.c. See the README.txt file in the demo directory for more information about compiling and running the application.
The code for the sample application that uses the OCI API follows:
#ifndef OCI_ORACLE
#include <oci.h>
#endif
#ifndef _STDIO_H
#include <stdio.h>
#endif
#ifndef _STDLIB_H
#include <stdlib.h>
#endif
#ifndef _STRING_H
#include <string.h>
#endif
#ifndef _MALLOC_H
#include <malloc.h>
#endif
/*----------------------------------------------------------------------
* Internal structures
*----------------------------------------------------------------------*/
#define M_DBNAME_LEN (128)
typedef struct conn_info /* connect info */
{
oratext * user;
ub4 userlen;
oratext * passw;
ub4 passwlen;
oratext * dbname;
ub4 dbnamelen;
oratext * svrnm;
ub4 svrnmlen;
} conn_info_t;
typedef struct params
{
conn_info_t xout; /* outbound info */
conn_info_t xin; /* inbound info */
} params_t;
typedef struct oci /* OCI handles */
{
OCIEnv *envp; /* Environment handle */
OCIError *errp; /* Error handle */
OCIServer *srvp; /* Server handle */
OCISvcCtx *svcp; /* Service handle */
OCISession *authp;
OCIStmt *stmtp;
boolean attached;
boolean outbound;
} oci_t;
static void connect_db(conn_info_t *opt_params_p, oci_t ** ocip, ub2 char_csid,
ub2 nchar_csid);
static void disconnect_db(oci_t * ocip);
static void ocierror(oci_t * ocip, char * msg);
static void attach(oci_t * ocip, conn_info_t *conn, boolean outbound);
static void detach(oci_t *ocip);
static void get_lcrs(oci_t *xin_ocip, oci_t *xout_ocip);
static void get_chunks(oci_t *xin_ocip, oci_t *xout_ocip);
static void print_lcr(oci_t *ocip, void *lcrp, ub1 lcrtype,
oratext **src_db_name, ub2 *src_db_namel);
static void print_chunk (ub1 *chunk_ptr, ub4 chunk_len, ub2 dty);
static void get_inputs(conn_info_t *xout_params, conn_info_t *xin_params,
int argc, char ** argv);
static void get_db_charsets(conn_info_t *params_p, ub2 *char_csid,
ub2 *nchar_csid);
static void set_client_charset(oci_t *outbound_ocip);
#define OCICALL(ocip, function) do {\
sword status=function;\
if (OCI_SUCCESS==status) break;\
else if (OCI_ERROR==status) \
{ocierror(ocip, (char *)"OCI_ERROR");\
exit(1);}\
else {printf("Error encountered %d\n", status);\
exit(1);}\
} while(0)
/*---------------------------------------------------------------------
* M A I N P R O G R A M
*---------------------------------------------------------------------*/
main(int argc, char **argv)
{
/* Outbound and inbound connection info */
conn_info_t xout_params;
conn_info_t xin_params;
oci_t *xout_ocip = (oci_t *)NULL;
oci_t *xin_ocip = (oci_t *)NULL;
ub2 obdb_char_csid = 0; /* outbound db char csid */
ub2 obdb_nchar_csid = 0; /* outbound db nchar csid */
/* parse command line arguments */
get_inputs(&xout_params, &xin_params, argc, argv);
/* Get the outbound database CHAR and NCHAR character set info */
get_db_charsets(&xout_params, &obdb_char_csid, &obdb_nchar_csid);
/* Connect to the outbound db and set the client env to the outbound charsets
* to minimize character conversion when transferring LCRs from outbound
* directly to inbound server.
*/
connect_db(&xout_params, &xout_ocip, obdb_char_csid, obdb_nchar_csid);
/* Attach to outbound server */
attach(xout_ocip, &xout_params, TRUE);
/* connect to inbound db and set the client charsets the same as the
* outbound db charsets.
*/
connect_db(&xin_params, &xin_ocip, obdb_char_csid, obdb_nchar_csid);
/* Attach to inbound server */
attach(xin_ocip, &xin_params, FALSE);
/* Get lcrs from outbound server and send to inbound server */
get_lcrs(xin_ocip, xout_ocip);
/* Detach from XStream servers */
detach(xout_ocip);
detach(xin_ocip);
/* Disconnect from both databases */
disconnect_db(xout_ocip);
disconnect_db(xin_ocip);
free(xout_ocip);
free(xin_ocip);
exit (0);
}
/*---------------------------------------------------------------------
* connect_db - Connect to the database and set the env to the given
* char and nchar character set ids.
*---------------------------------------------------------------------*/
static void connect_db(conn_info_t *params_p, oci_t **ociptr, ub2 char_csid,
ub2 nchar_csid)
{
oci_t *ocip;
printf ("Connect to Oracle as %.*s@%.*s ",
params_p->userlen, params_p->user,
params_p->dbnamelen, params_p->dbname);
if (char_csid && nchar_csid)
printf ("using char csid=%d and nchar csid=%d", char_csid, nchar_csid);
printf("\n");
ocip = (oci_t *)malloc(sizeof(oci_t));
if (OCIEnvNlsCreate(&ocip->envp, OCI_OBJECT, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0,
(size_t) 0, (dvoid **) 0, char_csid, nchar_csid))
{
ocierror(ocip, (char *)"OCIEnvCreate() failed");
}
if (OCIHandleAlloc((dvoid *) ocip->envp, (dvoid **) &ocip->errp,
(ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
{
ocierror(ocip, (char *)"OCIHandleAlloc(OCI_HTYPE_ERROR) failed");
}
/* Logon to database */
OCICALL(ocip,
OCILogon(ocip->envp, ocip->errp, &ocip->svcp,
params_p->user, params_p->userlen,
params_p->passw, params_p->passwlen,
params_p->dbname, params_p->dbnamelen));
/* allocate the server handle */
OCICALL(ocip,
OCIHandleAlloc((dvoid *) ocip->envp, (dvoid **) &ocip->srvp,
OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0));
OCICALL(ocip,
OCIHandleAlloc((dvoid *) ocip->envp, (dvoid **) &ocip->stmtp,
(ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
if (*ociptr == (oci_t *)NULL)
{
*ociptr = ocip;
}
}
/*---------------------------------------------------------------------
* get_db_charsets - Get the database CHAR and NCHAR character set ids.
*---------------------------------------------------------------------*/
static const oratext GET_DB_CHARSETS[] = \
"select parameter, value from nls_database_parameters where parameter = \
'NLS_CHARACTERSET' or parameter = 'NLS_NCHAR_CHARACTERSET'";
#define PARM_BUFLEN (30)
static void get_db_charsets(conn_info_t *params_p, ub2 *char_csid,
ub2 *nchar_csid)
{
OCIDefine *defnp1 = (OCIDefine *) NULL;
OCIDefine *defnp2 = (OCIDefine *) NULL;
oratext parm[PARM_BUFLEN];
oratext value[OCI_NLS_MAXBUFSZ];
ub2 parm_len = 0;
ub2 value_len = 0;
oci_t ocistruct;
oci_t *ocip = &ocistruct;
*char_csid = 0;
*nchar_csid = 0;
memset (ocip, 0, sizeof(ocistruct));
if (OCIEnvCreate(&ocip->envp, OCI_OBJECT, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0,
(size_t) 0, (dvoid **) 0))
{
ocierror(ocip, (char *)"OCIEnvCreate() failed");
}
if (OCIHandleAlloc((dvoid *) ocip->envp, (dvoid **) &ocip->errp,
(ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
{
ocierror(ocip, (char *)"OCIHandleAlloc(OCI_HTYPE_ERROR) failed");
}
OCICALL(ocip,
OCILogon(ocip->envp, ocip->errp, &ocip->svcp,
params_p->user, params_p->userlen,
params_p->passw, params_p->passwlen,
params_p->dbname, params_p->dbnamelen));
OCICALL(ocip,
OCIHandleAlloc((dvoid *) ocip->envp, (dvoid **) &ocip->stmtp,
(ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
/* Execute stmt to select the db nls char and nchar character set */
OCICALL(ocip,
OCIStmtPrepare(ocip->stmtp, ocip->errp,
(CONST text *)GET_DB_CHARSETS,
(ub4)strlen((char *)GET_DB_CHARSETS),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
OCICALL(ocip,
OCIDefineByPos(ocip->stmtp, &defnp1,
ocip->errp, (ub4) 1, parm,
PARM_BUFLEN, SQLT_CHR, (void*) 0,
&parm_len, (ub2 *)0, OCI_DEFAULT));
OCICALL(ocip,
OCIDefineByPos(ocip->stmtp, &defnp2,
ocip->errp, (ub4) 2, value,
OCI_NLS_MAXBUFSZ, SQLT_CHR, (void*) 0,
&value_len, (ub2 *)0, OCI_DEFAULT));
OCICALL(ocip,
OCIStmtExecute(ocip->svcp, ocip->stmtp,
ocip->errp, (ub4)0, (ub4)0,
(const OCISnapshot *)0,
(OCISnapshot *)0, (ub4)OCI_DEFAULT));
while (OCIStmtFetch(ocip->stmtp, ocip->errp, 1,
OCI_FETCH_NEXT, OCI_DEFAULT) == OCI_SUCCESS)
{
value[value_len] = '\0';
if (parm_len == strlen("NLS_CHARACTERSET") &&
!memcmp(parm, "NLS_CHARACTERSET", parm_len))
{
*char_csid = OCINlsCharSetNameToId(ocip->envp, value);
printf("Outbound database NLS_CHARACTERSET = %.*s (csid = %d) \n",
value_len, value, *char_csid);
}
else if (parm_len == strlen("NLS_NCHAR_CHARACTERSET") &&
!memcmp(parm, "NLS_NCHAR_CHARACTERSET", parm_len))
{
*nchar_csid = OCINlsCharSetNameToId(ocip->envp, value);
printf("Outbound database NLS_NCHAR_CHARACTERSET = %.*s (csid = %d) \n",
value_len, value, *nchar_csid);
}
}
disconnect_db(ocip);
}
/*---------------------------------------------------------------------
* attach - Attach to XStream server specified in connection info
*---------------------------------------------------------------------*/
static void attach(oci_t * ocip, conn_info_t *conn, boolean outbound)
{
sword err;
printf ("Attach to XStream %s server '%.*s'\n",
outbound ? "outbound" : "inbound",
conn->svrnmlen, conn->svrnm);
if (outbound)
{
OCICALL(ocip,
OCIXStreamOutAttach(ocip->svcp, ocip->errp, conn->svrnm,
(ub2)conn->svrnmlen, (ub1 *)0, 0, OCI_DEFAULT));
}
else
{
OCICALL(ocip,
OCIXStreamInAttach(ocip->svcp, ocip->errp, conn->svrnm,
(ub2)conn->svrnmlen,
(oratext *)"From_XOUT", 9,
(ub1 *)0, 0, OCI_DEFAULT));
}
ocip->attached = TRUE;
ocip->outbound = outbound;
}
/*---------------------------------------------------------------------
* ping_svr - Ping inbound server by sending a commit LCR.
*---------------------------------------------------------------------*/
static void ping_svr(oci_t *xin_ocip, void *commit_lcr,
ub1 *cmtpos, ub2 cmtpos_len,
oratext *source_db, ub2 source_db_len)
{
OCIDate src_time;
oratext txid[128];
OCICALL(xin_ocip, OCIDateSysDate(xin_ocip->errp, &src_time));
sprintf(txid, "Ping %2d:%2d:%2d",
src_time.OCIDateTime.OCITimeHH,
src_time.OCIDateTime.OCITimeMI,
src_time.OCIDateTime.OCITimeSS);
/* Initialize LCR with new txid and commit position */
OCICALL(xin_ocip,
OCILCRHeaderSet(xin_ocip->svcp, xin_ocip->errp,
source_db, source_db_len,
(oratext *)OCI_LCR_ROW_CMD_COMMIT,
(ub2)strlen(OCI_LCR_ROW_CMD_COMMIT),
(oratext *)0, 0, /* null owner */
(oratext *)0, 0, /* null object */
(ub1 *)0, 0, /* null tag */
txid, (ub2)strlen((char *)txid),
&src_time, cmtpos, cmtpos_len,
0, commit_lcr, OCI_DEFAULT));
/* Send commit lcr to inbound server. */
if (OCIXStreamInLCRSend(xin_ocip->svcp, xin_ocip->errp, commit_lcr,
OCI_LCR_XROW, 0, OCI_DEFAULT) == OCI_ERROR)
{
ocierror(xin_ocip, (char *)"OCIXStreamInLCRSend failed in ping_svr()");
}
}
/*---------------------------------------------------------------------
* get_lcrs - Get LCRs from outbound server and send to inbound server.
*---------------------------------------------------------------------*/
static void get_lcrs(oci_t *xin_ocip, oci_t *xout_ocip)
{
sword status = OCI_SUCCESS;
void *lcr;
ub1 lcrtype;
oraub8 flag;
ub1 proclwm[OCI_LCR_MAX_POSITION_LEN];
ub2 proclwm_len = 0;
ub1 sv_pingpos[OCI_LCR_MAX_POSITION_LEN];
ub2 sv_pingpos_len = 0;
u mb1 fetchlwm[OCI_LCR_MAX_POSITION_LEN];
ub2 fetchlwm_len = 0;
void *commit_lcr = (void *)0;
oratext *lcr_srcdb = (oratext *)0;
ub2 lcr_srcdb_len = 0;
oratext source_db[M_DBNAME_LEN];
ub2 source_db_len = 0;
ub4 lcrcnt = 0;
/* create an lcr to ping the inbound server periodically by sending a
* commit lcr.
*/
commit_lcr = (void*)0;
OCICALL(xin_ocip,
OCILCRNew(xin_ocip->svcp, xin_ocip->errp, OCI_DURATION_SESSION,
OCI_LCR_XROW, &commit_lcr, OCI_DEFAULT));
while (status == OCI_SUCCESS)
{
lcrcnt = 0; /* reset lcr count before each batch */
while ((status =
OCIXStreamOutLCRReceive(xout_ocip->svcp, xout_ocip->errp,
&lcr, &lcrtype, &flag,
fetchlwm, &fetchlwm_len, OCI_DEFAULT))
== OCI_STILL_EXECUTING)
{
lcrcnt++;
/* print header of LCR just received */
print_lcr(xout_ocip, lcr, lcrtype, &lcr_srcdb, &lcr_srcdb_len);
/* save the source db to construct ping lcr later */
if (!source_db_len && lcr_srcdb_len)
{
memcpy(source_db, lcr_srcdb, lcr_srcdb_len);
source_db_len = lcr_srcdb_len;
}
/* send the LCR just received */
if (OCIXStreamInLCRSend(xin_ocip->svcp, xin_ocip->errp,
lcr, lcrtype, flag, OCI_DEFAULT) == OCI_ERROR)
{
ocierror(xin_ocip, (char *)"OCIXStreamInLCRSend failed");
}
/* If LCR has chunked columns (i.e, has LOB/Long/XMLType columns) */
if (flag & OCI_XSTREAM_MORE_ROW_DATA)
{
/* receive and send chunked columns */
get_chunks(xin_ocip, xout_ocip);
}
}
if (status == OCI_ERROR)
ocierror(xout_ocip, (char *)"OCIXStreamOutLCRReceive failed");
/* clear the saved ping position if we just received some new lcrs */
if (lcrcnt)
{
sv_pingpos_len = 0;
}
/* If no lcrs received during previous WHILE loop and got a new fetch
* LWM then send a commit lcr to ping the inbound server with the new
* fetch LWM position.
*/
else if (fetchlwm_len > 0 && source_db_len > 0 &&
(fetchlwm_len != sv_pingpos_len ||
memcmp(sv_pingpos, fetchlwm, fetchlwm_len)))
{
/* To ensure we don't send multiple lcrs with duplicate position, send
* a new ping only if we have saved the last ping position.
*/
if (sv_pingpos_len > 0)
{
ping_svr(xin_ocip, commit_lcr, fetchlwm, fetchlwm_len,
source_db, source_db_len);
}
/* save the position just sent to inbound server */
memcpy(sv_pingpos, fetchlwm, fetchlwm_len);
sv_pingpos_len = fetchlwm_len;
}
/* flush inbound network to flush all lcrs to inbound server */
OCICALL(xin_ocip,
OCIXStreamInFlush(xin_ocip->svcp, xin_ocip->errp, OCI_DEFAULT));
/* get processed LWM of inbound server */
OCICALL(xin_ocip,
OCIXStreamInProcessedLWMGet(xin_ocip->svcp, xin_ocip->errp,
proclwm, &proclwm_len, OCI_DEFAULT));
if (proclwm_len > 0)
{
/* Set processed LWM for outbound server */
OCICALL(xout_ocip,
OCIXStreamOutProcessedLWMSet(xout_ocip->svcp, xout_ocip->errp,
proclwm, proclwm_len, OCI_DEFAULT));
}
}
if (status != OCI_SUCCESS)
ocierror(xout_ocip, (char *)"get_lcrs() encounters error");
}
/*---------------------------------------------------------------------
* get_chunks - Get each chunk for the current LCR and send it to
* the inbound server.
*---------------------------------------------------------------------*/
static void get_chunks(oci_t *xin_ocip, oci_t *xout_ocip)
{
oratext *colname;
ub2 colname_len;
ub2 coldty;
oraub8 col_flags;
ub2 col_csid;
ub4 chunk_len;
ub1 *chunk_ptr;
oraub8 row_flag;
sword err;
sb4 rtncode;
do
{
/* Get a chunk from outbound server */
OCICALL(xout_ocip,
OCIXStreamOutChunkReceive(xout_ocip->svcp, xout_ocip->errp,
&colname, &colname_len, &coldty,
&col_flags, &col_csid, &chunk_len,
&chunk_ptr, &row_flag, OCI_DEFAULT));
/* print chunked column info */
printf(
" Chunked column name=%.*s DTY=%d chunk len=%d csid=%d col_flag=0x%x\n",
colname_len, colname, coldty, chunk_len, col_csid, col_flags);
/* print chunk data */
print_chunk(chunk_ptr, chunk_len, coldty);
/* Send the chunk just received to inbound server */
OCICALL(xin_ocip,
OCIXStreamInChunkSend(xin_ocip->svcp, xin_ocip->errp, colname,
colname_len, coldty, col_flags,
col_csid, chunk_len, chunk_ptr,
row_flag, OCI_DEFAULT));
} while (row_flag & OCI_XSTREAM_MORE_ROW_DATA);
}
/*---------------------------------------------------------------------
* print_chunk - Print chunked column information. Only print the first
* 50 bytes for each chunk.
*---------------------------------------------------------------------*/
static void print_chunk (ub1 *chunk_ptr, ub4 chunk_len, ub2 dty)
{
#define MAX_PRINT_BYTES (50) /* print max of 50 bytes per chunk */
ub4 print_bytes;
if (chunk_len == 0)
return;
print_bytes = chunk_len > MAX_PRINT_BYTES ? MAX_PRINT_BYTES : chunk_len;
printf(" Data = ", chunk_len);
if (dty == SQLT_CHR)
printf("%.*s", print_bytes, chunk_ptr);
else
{
ub2 idx;
for (idx = 0; idx < print_bytes; idx++)
printf("%02x", chunk_ptr[idx]);
}
printf("\n");
}
/*---------------------------------------------------------------------
* print_lcr - Print header information of given lcr.
*---------------------------------------------------------------------*/
static void print_lcr(oci_t *ocip, void *lcrp, ub1 lcrtype,
oratext **src_db_name, ub2 *src_db_namel)
{
oratext *cmd_type;
ub2 cmd_type_len;
oratext *owner;
ub2 ownerl;
oratext *oname;
ub2 onamel;
oratext *txid;
ub2 txidl;
sword ret;
printf("\n ----------- %s LCR Header -----------------\n",
lcrtype == OCI_LCR_XDDL ? "DDL" : "ROW");
/* Get LCR Header information */
ret = OCILCRHeaderGet(ocip->svcp, ocip->errp,
src_db_name, src_db_namel, /* source db */
&cmd_type, &cmd_type_len, /* command type */
&owner, &ownerl, /* owner name */
&oname, &onamel, /* object name */
(ub1 **)0, (ub2 *)0, /* lcr tag */
&txid, &txidl, (OCIDate *)0, /* txn id & src time */
(ub2 *)0, (ub2 *)0, /* OLD/NEW col cnts */
(ub1 **)0, (ub2 *)0, /* LCR position */
(oraub8*)0, lcrp, OCI_DEFAULT);
if (ret != OCI_SUCCESS)
ocierror(ocip, (char *)"OCILCRHeaderGet failed");
else
{
printf(" src_db_name=%.*s\n cmd_type=%.*s txid=%.*s\n",
*src_db_namel, *src_db_name, cmd_type_len, cmd_type, txidl, txid );
if (ownerl > 0)
printf(" owner=%.*s oname=%.*s \n", ownerl, owner, onamel, oname);
}
}
/*---------------------------------------------------------------------
* detach - Detach from XStream server
*---------------------------------------------------------------------*/
static void detach(oci_t * ocip)
{
sword err = OCI_SUCCESS;
printf ("Detach from XStream %s server\n",
ocip->outbound ? "outbound" : "inbound" );
if (ocip->outbound)
{
OCICALL(ocip, OCIXStreamOutDetach(ocip->svcp, ocip->errp, OCI_DEFAULT));
}
else
{
OCICALL(ocip, OCIXStreamInDetach(ocip->svcp, ocip->errp,
(ub1 *)0, (ub2 *)0, /* processed LWM */
OCI_DEFAULT));
}
}
/*---------------------------------------------------------------------
* disconnect_db - Logoff from the database
*---------------------------------------------------------------------*/
static void disconnect_db(oci_t * ocip)
{
if (OCILogoff(ocip->svcp, ocip->errp))
{
ocierror(ocip, (char *)"OCILogoff() failed");
}
if (ocip->errp)
OCIHandleFree((dvoid *) ocip->errp, (ub4) OCI_HTYPE_ERROR);
if (ocip->envp)
OCIHandleFree((dvoid *) ocip->envp, (ub4) OCI_HTYPE_ENV);
}
/*---------------------------------------------------------------------
* ocierror - Print error status and exit program
*---------------------------------------------------------------------*/
static void ocierror(oci_t * ocip, char * msg)
{
sb4 errcode=0;
text bufp[4096];
if (ocip->errp)
{
OCIErrorGet((dvoid *) ocip->errp, (ub4) 1, (text *) NULL, &errcode,
bufp, (ub4) 4096, (ub4) OCI_HTYPE_ERROR);
printf("%s\n%s", msg, bufp);
}
else
puts(msg);
printf ("\n");
exit(1);
}
/*--------------------------------------------------------------------
* print_usage - Print command usage
*---------------------------------------------------------------------*/
static void print_usage(int exitcode)
{
puts("\nUsage: xio -ob_svr <outbound_svr> -ob_db <outbound_db>\n"
" -ob_usr <conn_user> -ob_pwd <conn_user_pwd>\n"
" -ib_svr <inbound_svr> -ib_db <inbound_db>\n"
" -ib_usr <apply_user> -ib_pwd <apply_user_pwd>\n");
puts(" ob_svr : outbound server name\n"
" ob_db : database name of outbound server\n"
" ob_usr : connect user to outbound server\n"
" ob_pwd : password of outbound's connect user\n"
" ib_svr : inbound server name\n"
" ib_db : database name of inbound server\n"
" ib_usr : apply user for inbound server\n"
" ib_pwd : password of inbound's apply user\n");
exit(exitcode);
}
/*--------------------------------------------------------------------
* get_inputs - Get user inputs from command line
*---------------------------------------------------------------------*/
static void get_inputs(conn_info_t *xout_params, conn_info_t *xin_params,
int argc, char ** argv)
{
char * option;
char * value;
memset (xout_params, 0, sizeof(*xout_params));
memset (xin_params, 0, sizeof(*xin_params));
while(--argc)
{
/* get the option name */
argv++;
option = *argv;
/* check that the option begins with a "-" */
if (!strncmp(option, (char *)"-", 1))
{
option ++;
}
else
{
printf("Error: bad argument '%s'\n", option);
print_usage(1);
}
/* get the value of the option */
--argc;
argv++;
value = *argv;
if (!strncmp(option, (char *)"ob_db", 5))
{
xout_params->dbname = (oratext *)value;
xout_params->dbnamelen = strlen(value);
}
else if (!strncmp(option, (char *)"ob_usr", 6))
{
xout_params->user = (oratext *)value;
xout_params->userlen = strlen(value);
}
else if (!strncmp(option, (char *)"ob_pwd", 6))
{
xout_params->passw = (oratext *)value;
xout_params->passwlen = strlen(value);
}
else if (!strncmp(option, (char *)"ob_svr", 6))
{
xout_params->svrnm = (oratext *)value;
xout_params->svrnmlen = strlen(value);
}
else if (!strncmp(option, (char *)"ib_db", 5))
{
xin_params->dbname = (oratext *)value;
xin_params->dbnamelen = strlen(value);
}
else if (!strncmp(option, (char *)"ib_usr", 6))
{
xin_params->user = (oratext *)value;
xin_params->userlen = strlen(value);
}
else if (!strncmp(option, (char *)"ib_pwd", 6))
{
xin_params->passw = (oratext *)value;
xin_params->passwlen = strlen(value);
}
else if (!strncmp(option, (char *)"ib_svr", 6))
{
xin_params->svrnm = (oratext *)value;
xin_params->svrnmlen = strlen(value);
}
else
{
printf("Error: unknown option '%s'.\n", option);
print_usage(1);
}
}
/* print usage and exit if any argument is not specified */
if (!xout_params->svrnmlen || !xout_params->passwlen ||
!xout_params->userlen || !xout_params->dbnamelen ||
!xin_params->svrnmlen || !xin_params->passwlen ||
!xin_params->userlen || !xin_params->dbnamelen)
{
printf("Error: missing command arguments. \n");
print_usage(1);
}
}
To run the sample XStream client application for the Java API, compile and link the application file, and enter the following on a command line:
java xio xsin_oraclesid xsin_host xsin_port xsin_username xsin_passwd xin_servername xsout_oraclesid xsout_host xsout_port xsout_username xsout_passwd xsout_servername
Substitute the appropriate values for the following placeholders:
xsin_oraclesid is the Oracle SID of the inbound server's database.
xsin_host is the host name of the computer system running the inbound server.
xsin_port is the port number of the listener for the inbound server's database.
xsin_username is the inbound server's apply user.
xsin_passwd is the password for the inbound server's apply user.
xin_servername is the name of the inbound server.
xsout_oraclesid is the Oracle SID of the outbound server's database.
xsout_host is the host name of the computer system running the outbound server.
xsout_port is the port number of the listener for the outbound server's database.
xsout_username is the outbound server's connect user.
xsout_passwd is the password for the outbound server's connect user.
xsout_servername is the name of the outbound server.
When the sample client application is running, it prints information about attaching to the inbound server and outbound server, along with the last position for each server. The output looks similar to the following:
xsin_host = server2.example.com xsin_port = 1482 xsin_ora_sid = db2 xsin connection url: jdbc:oracle:oci:@server2.example.com:1482:db2 xsout_host = server1.example.com xsout_port = 1481 xsout_ora_sid = db1 xsout connection url: jdbc:oracle:oci:@server1.example.com:1481:db1 Attached to inbound server:xin Inbound Server Last Position is: 0000000920250000000100000001000000092025000000010000000101 Attached to outbound server:xout Last Position is: 0000000920250000000100000001000000092025000000010000000101
This demo is available in the following location:
$ORACLE_HOME/rdbms/demo/xstream/java
The file name for the demo is xio.java. See the README.txt file in the demo directory for more information about compiling and running the application.
The code for the sample application that uses the Java API follows:
import oracle.streams.*;
import oracle.jdbc.internal.OracleConnection;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;
import java.util.*;
public class xio
{
public static String xsinusername = null;
public static String xsinpasswd = null;
public static String xsinName = null;
public static String xsoutusername = null;
public static String xsoutpasswd = null;
public static String xsoutName = null;
public static String in_url = null;
public static String out_url = null;
public static Connection in_conn = null;
public static Connection out_conn = null;
public static XStreamIn xsIn = null;
public static XStreamOut xsOut = null;
public static byte[] lastPosition = null;
public static byte[] processedLowPosition = null;
public static void main(String args[])
{
// get connection url to inbound and outbound server
in_url = parseXSInArguments(args);
out_url = parseXSOutArguments(args);
// create connection to inbound and outbound server
in_conn = createConnection(in_url, xsinusername, xsinpasswd);
out_conn = createConnection(out_url, xsoutusername, xsoutpasswd);
// attach to inbound and outbound server
xsIn = attachInbound(in_conn);
xsOut = attachOutbound(out_conn);
// main loop to get lcrs
get_lcrs(xsIn, xsOut);
// detach from inbound and outbound server
detachInbound(xsIn);
detachOutbound(xsOut);
}
// parse the arguments to get the conncetion url to inbound db
public static String parseXSInArguments(String args[])
{
String trace, pref;
String orasid, host, port;
if (args.length != 12)
{
printUsage();
System.exit(0);
}
orasid = args[0];
host = args[1];
port = args[2];
xsinusername = args[3];
xsinpasswd = args[4];
xsinName = args[5];
System.out.println("xsin_host = "+host);
System.out.println("xsin_port = "+port);
System.out.println("xsin_ora_sid = "+orasid);
String in_url = "jdbc:oracle:oci:@"+host+":"+port+":"+orasid;
System.out.println("xsin connection url: "+ in_url);
return in_url;
}
// parse the arguments to get the conncetion url to outbound db
public static String parseXSOutArguments(String args[])
{
String trace, pref;
String orasid, host, port;
if (args.length != 12)
{
printUsage();
System.exit(0);
}
orasid = args[6];
host = args[7];
port = args[8];
xsoutusername = args[9];
xsoutpasswd = args[10];
xsoutName = args[11];
System.out.println("xsout_host = "+host);
System.out.println("xsout_port = "+port);
System.out.println("xsout_ora_sid = "+orasid);
String out_url = "jdbc:oracle:oci:@"+host+":"+port+":"+orasid;
System.out.println("xsout connection url: "+ out_url);
return out_url;
}
// print out sample program usage message
public static void printUsage()
{
System.out.println("");
System.out.println("Usage: java xio "+"<xsin_oraclesid> " + "<xsin_host> "
+ "<xsin_port> ");
System.out.println(" "+"<xsin_username> " + "<xsin_passwd> "
+ "<xsin_servername> ");
System.out.println(" "+"<xsout_oraclesid> " + "<xsout_host> "
+ "<xsout_port> ");
System.out.println(" "+"<xsout_username> " + "<xsout_passwd> "
+ "<xsout_servername> ");
}
// create a connection to an Oracle Database
public static Connection createConnection(String url,
String username,
String passwd)
{
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
return DriverManager.getConnection(url, username, passwd);
}
catch(Exception e)
{
System.out.println("fail to establish DB connection to: " +url);
e.printStackTrace();
return null;
}
}
// attach to the XStream Inbound Server
public static XStreamIn attachInbound(Connection in_conn)
{
XStreamIn xsIn = null;
try
{
xsIn = XStreamIn.attach((OracleConnection)in_conn, xsinName,
"XSDEMOINCLIENT" , XStreamIn.DEFAULT_MODE);
// use last position to decide where should we start sending LCRs
lastPosition = xsIn.getLastPosition();
System.out.println("Attached to inbound server:"+xsinName);
System.out.print("Inbound Server Last Position is: ");
if (null == lastPosition)
{
System.out.println("null");
}
else
{
printHex(lastPosition);
}
return xsIn;
}
catch(Exception e)
{
System.out.println("cannot attach to inbound server: "+xsinName);
System.out.println(e.getMessage());
e.printStackTrace();
return null;
}
}
// attach to the XStream Outbound Server
public static XStreamOut attachOutbound(Connection out_conn)
{
XStreamOut xsOut = null;
try
{
// when attach to an outbound server, client needs to tell outbound
// server the last position.
xsOut = XStreamOut.attach((OracleConnection)out_conn, xsoutName,
lastPosition, XStreamOut.DEFAULT_MODE);
System.out.println("Attached to outbound server:"+xsoutName);
System.out.print("Last Position is: ");
if (lastPosition != null)
{
printHex(lastPosition);
}
else
{
System.out.println("NULL");
}
return xsOut;
}
catch(Exception e)
{
System.out.println("cannot attach to outbound server: "+xsoutName);
System.out.println(e.getMessage());
e.printStackTrace();
return null;
}
}
// detach from the XStream Inbound Server
public static void detachInbound(XStreamIn xsIn)
{
byte[] processedLowPosition = null;
try
{
processedLowPosition = xsIn.detach(XStreamIn.DEFAULT_MODE);
System.out.print("Inbound server processed low Position is: ");
if (processedLowPosition != null)
{
printHex(processedLowPosition);
}
else
{
System.out.println("NULL");
}
}
catch(Exception e)
{
System.out.println("cannot detach from the inbound server: "+xsinName);
System.out.println(e.getMessage());
e.printStackTrace();
}
}
// detach from the XStream Outbound Server
public static void detachOutbound(XStreamOut xsOut)
{
try
{
xsOut.detach(XStreamOut.DEFAULT_MODE);
}
catch(Exception e)
{
System.out.println("cannot detach from the outbound server: "+xsoutName);
System.out.println(e.getMessage());
e.printStackTrace();
}
}
public static void get_lcrs(XStreamIn xsIn, XStreamOut xsOut)
{
byte[] ping_pos = null;
byte[] fetchlwm = null;
String src_db = null;
if (null == xsIn)
{
System.out.println("xstreamIn is null");
System.exit(0);
}
if (null == xsOut)
{
System.out.println("xstreamOut is null");
System.exit(0);
}
try
{
while(true)
{
// receive an LCR from outbound server
LCR alcr = xsOut.receiveLCR(XStreamOut.DEFAULT_MODE);
fetchlwm = xsOut.getFetchLowWatermark();
// save source db for ping lcr
if (null != alcr)
src_db = alcr.getSourceDatabaseName();
if (xsOut.getBatchStatus() == XStreamOut.EXECUTING) // batch is active
{
assert alcr != null;
// send the LCR to the inbound server
xsIn.sendLCR(alcr, XStreamIn.DEFAULT_MODE);
// also get chunk data for this LCR if any
if (alcr instanceof RowLCR)
{
// receive chunk from outbound then send to inbound
if (((RowLCR)alcr).hasChunkData())
{
ChunkColumnValue chunk = null;
do
{
chunk = xsOut.receiveChunk(XStreamOut.DEFAULT_MODE);
xsIn.sendChunk(chunk, XStreamIn.DEFAULT_MODE);
} while (!chunk.isEndOfRow());
}
}
processedLowPosition = alcr.getPosition();
ping_pos = processedLowPosition;
}
else // batch is end
{
assert alcr == null;
// send ping lcr if we haven't received any lcr in the batch
// but we got a new fetch lwm, then send a commit lcr to
// ping the inbound server with the new fetch LWM position
if (null != src_db && null != fetchlwm &&
!samePos(fetchlwm,ping_pos))
{
xsIn.sendLCR(createPing(src_db, fetchlwm),
XStreamIn.DEFAULT_MODE);
ping_pos = fetchlwm;
}
// flush the network
xsIn.flush(XStreamIn.DEFAULT_MODE);
// get the processed_low_position from inbound server
processedLowPosition =
xsIn.getProcessedLowWatermark();
// update the processed_low_position at oubound server
if (null != processedLowPosition)
xsOut.setProcessedLowWatermark(processedLowPosition,
XStreamOut.DEFAULT_MODE);
}
}
}
catch(Exception e)
{
System.out.println("exception when processing LCRs");
System.out.println(e.getMessage());
e.printStackTrace();
}
}
public static void printHex(byte[] b)
{
for (int i = 0; i < b.length; ++i)
{
System.out.print(
Integer.toHexString((b[i]&0xFF) | 0x100).substring(1,3));
}
System.out.println("");
}
// ping lcr is used to bump up the inbound server watermark
private static RowLCR createPing(String src_db, byte[] pos)
{
java.util.Date today = new java.util.Date();
java.sql.Timestamp now = new java.sql.Timestamp(today.getTime());
oracle.sql.DATE src_time = new oracle.sql.DATE(now);
RowLCR alcr = new DefaultRowLCR();
((RowLCR)alcr).setSourceDatabaseName(src_db);
((RowLCR)alcr).setSourceTime(src_time);
((RowLCR)alcr).setPosition(pos);
((RowLCR)alcr).setCommandType(RowLCR.COMMIT);
((RowLCR)alcr).setTransactionId("Ping: " + src_time.toString());
return alcr;
}
private static boolean samePos(byte[] pos1, byte[] pos2)
{
int cmp_len;
boolean result;
if (pos1.length != pos2.length)
return false;
for (int i = 0; i<pos1.length; i++)
{
if (pos1[i] != pos2[i])
return false;
}
return true;
}
}
XStream Guide
11g Release 2 (11.2)
E16545-07
August 2011
Oracle Database XStream Guide, 11g Release 2 (11.2)
E16545-07
Copyright © 2009, 2011, Oracle and/or its affiliates. All rights reserved.
Primary Author: Randy Urbano
Contributors: Lance Ashdown, Vinoth Chandar, Alan Downing, Thuvan Hoang, Richard Huang, Joydip Kundu, Tianshu Li, Edwina Lu, Rui Mao, Pat McElroy, Valarie Moore, Srikanth Nalla, Partha Raghunathan, Ashish Ray, Jim Stamos, Byron Wang, Lik Wong, Jun Yuan, Rod Ward, Haobo Xu, Kevin Xu
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
This chapter contains concepts related to XStream.
This chapter contains these topics:
XStream Out can capture transactions from the redo log of an Oracle database and send them efficiently to a client application. XStream Out provides a transaction-based interface for streaming these changes to client applications. The client application can interact with other systems, including non-Oracle systems, such as non-Oracle databases or file systems.
XStream Out has both OCI and Java interfaces and supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType.
This section contains these topics:
With XStream Out, an Oracle Streams apply process functions as an outbound server. An outbound server is an optional Oracle background process that sends database changes to a client application. Specifically, a client application can attach to an outbound server and extract database changes from LCRs. A client application attaches to the outbound server using the OCI or Java interface.
A client application can create multiple sessions. Each session can attach to only one outbound server, and each outbound server can serve only one session at a time. However, different client application sessions can connect to different outbound servers or inbound servers.
In an XStream Out configuration, a capture process captures database changes and sends these changes to an outbound server. A capture process is an optional Oracle background process that scans the database redo log to capture DML and DDL changes made to database objects. When a capture process is configured to capture changes from the redo log, the database where the changes were generated is called the source database for the capture process.
Figure 2-1 shows a capture process.
Change capture can be performed on the same database as the outbound server or on a different database. When change capture is performed on a different database from the one that contains the outbound server, a propagation sends the changes from the change capture database to the outbound server database. Downstream capture is also a supported mode to reduce the load on the source database.
When both the capture process and the outbound server are enabled, data changes, encapsulated in row LCRs and DDL LCRs, are sent to the outbound server. The outbound server can publish LCRs in various formats, such as OCI and Java. The client application can process LCRs that are passed to it from the outbound server or wait for LCRs from the outbound server by using a loop.
An outbound server sends LOB, LONG, LONG RAW, and XMLType data to the client application in chunks. Several chunks comprise a single column value of LOB, LONG, LONG RAW, or XMLType data type.
Figure 2-2 shows an outbound server configuration.
The client application can detach from the outbound server whenever necessary. When the client application re-attaches, the outbound server automatically determines where in the stream of LCRs the client application was when it detached. The outbound server starts sending LCRs from this point forward.
|
See Also: Oracle Streams Concepts and Administration for detailed information about capture processes |
An Oracle Streams apply process functions as an outbound server, but some apply process features are not applicable to an outbound server. The following sections describe which apply process features are applicable to outbound servers and which are not:
Apply Process Features That Are Applicable to Outbound Servers
Apply Process Features That Are Not Applicable to Outbound Servers
The following apply process features can be used with outbound servers:
When a custom rule-based transformation is specified on a rule used by an outbound server, the user who calls the transformation function is the connect user for the outbound server.
The following apply process parameters:
apply_sequence_nextval
disable_on_limit
grouptransops
ignore_transaction
max_sga_size
maximum_scn
startup_seconds
time_limit
trace_level
transaction_limit
txn_age_spill_threshold
txn_lcr_spill_threshold
write_alert_log
These apply process parameters control the behavior of outbound servers.
|
Note: Starting with Oracle Database 11g Release 2 (11.2.0.2), the following parameters are available:apply_sequence_nextval, ignore_transaction, grouptransops, and max_sga_size. |
Transaction assembly by reader servers
Instantiation system change number (SCN) settings
Instantiation SCNs are not required for database objects processed by an outbound server. If an instantiation SCN is set for a database object, then the outbound server only sends the LCRs for the database object with SCN values that are greater than the instantiation SCN value. If a database object does not have an instantiation SCN set, then the outbound server skips the instantiation SCN check and sends all LCRs for that database object. In both cases, the outbound server only sends LCRs that satisfy its rule sets.
The following apply process features cannot be used with outbound servers:
You cannot specify an apply handler for an outbound server. The client application can perform custom processing of the LCRs instead if necessary. However, if apply processes are configured in the same database as the outbound server, then you can specify apply handlers for these apply processes. In addition, you can configure general apply handlers for the database. An outbound server ignores general apply handlers.
The following apply process parameters:
allow_duplicate_rows
commit_serialization
compare_key_only
disable_on_error
parallelism
preserve_encryption
rtrim_on_implicit_conversion
Outbound servers ignore the settings for these apply process parameters.
The commit_serialization parameter is always set to FULL for an outbound server, and the parallelism parameter is always set to 1 for an outbound server.
|
Note: Starting with Oracle Database 11g Release 2 (11.2.0.2), thecompare_key_only parameter is available. |
An outbound server cannot set an apply tag for the changes it processes.
Apply database links
Outbound servers cannot use database links.
Conflict detection and resolution
An outbound server does not detect conflicts, and conflict resolution cannot be set for an outbound server.
An outbound server does not evaluate dependencies because its parallelism must be 1.
Substitute key column settings
An outbound server ignores substitute key column settings.
Enqueue directives specified by the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package
An outbound server cannot enqueue changes into an Oracle database queue automatically using the SET_ENQUEUE_DESTINATION procedure.
Execute directives specified by the SET_EXECUTE procedure in the DBMS_APPLY_ADM package
An outbound server ignores execute directives.
Error creation and execution
An outbound server does not create an error transaction when it encounters an error. It records information about errors in the ALL_APPLY and DBA_APPLY views, but it does not enqueue the transaction into the error queue.
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
XStream Out does not support the following data types in row LCRs:
BFILE
ROWID
User-defined types (including object types, REFs, varrays, and nested tables)
XMLType stored object relationally or as binary XML
The following Oracle supplied types: Any types, URI types, spatial types, and media types
These data type restrictions pertain to both ordinary (heap-organized) tables and index-organized tables.
ID key LCRs enable an XStream client application to process changes to rows that include unsupported data types. ID key LCRs do not contain all of the columns for a row change. Instead, they contain the rowid of the changed row, a group of key columns to identify the row in the table, and the data for the scalar columns of the table that are supported by XStream Out. ID key LCRs do not contain columns for unsupported data types.
An XStream client application can use ID key LCRs in the following ways:
If the application does not require the data in the unsupported columns, then the application can process the values of the supported columns in the ID key LCRs normally.
If the application requires the data in the unsupported columns, then the application can use the information in an ID key LCR to query the correct row in the database and consume the unsupported data for the row.
A demo is available that creates a sample client application that process ID key LCRs. Specifically, the client application attaches to an XStream outbound server and waits for LCRs from the outbound server. When the client application receives an ID key LCR, it can query the appropriate source database table using the rowid in the ID key LCR.
The demo is available in the following location in both OCI and Java code:
$ORACLE_HOME/rdbms/demo/xstream/idkey
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
A sequence LCR is a row LCR that includes information about sequence values. Sequence database objects generate sequence values.
You can stream sequence LCRs in the following ways:
To capture sequence LCRs using a capture process, set the capture process parameter capture_sequence_nextval to Y.
To construct sequence LCRs using the OCI interface, use the OCILCRNew function and the OCILCRHeaderSet function with the OCI_ROWLCR_SEQ_LCR flag.
To construct sequence LCRs using the Java interface, use the DefaultRowLCR constructor and setSequenceLCRFlag method.
An apply process or XStream inbound server can use sequence LCRs to ensure that the sequence values at a destination database use the appropriate values. For increasing sequences, the sequence values at the destination are equal to or greater than the sequence values at the source database. For decreasing sequences, the sequence values at the destination are less than or equal to the sequence values at the source database. To instruct an apply process or XStream inbound server to use sequence LCRs, set the apply_sequence_nextval apply process parameter to Y.
|
Note: Sequence LCRs are intended for one-way replication configurations. Sequence LCRs cannot be used in bi-directional replication configurations. |
|
See Also:
|
The following are considerations for XStream outbound servers:
LCRs processed by an outbound server must be LCRs that were captured by a capture process. An outbound server does not support LCRs that were captured by synchronous captures or LCRs that were constructed by applications.
A single outbound server can process captured LCRs from only one source database. The source database is the database where the changes encapsulated in the LCRs were generated in the redo log.
The source database for the changes captured by a capture process must be at 10.2.0 or higher compatibility level for these changes to be processed by an outbound server.
The capture process for an outbound server must be running on an Oracle Database 11g Release 2 (11.2) or later database.
A single capture process cannot capture changes for both an outbound server and an apply process. However, a single capture process can capture changes for multiple outbound servers.
An outbound server appears as an Oracle Streams apply process in Oracle Enterprise Manager.
Automatic split and merge of a stream is possible when the capture process and the outbound server for the stream run on different database instances. However, when the capture process and outbound server for a stream run on the same database instance, automatic split and merge of the stream is not possible. See Oracle Streams Replication Administrator's Guide for information about automatic split and merge.
You can perform distributed transactions using either of the following methods:
Modify tables in multiple databases in a coordinated manner using database links.
Use the XA interface, as exposed by the DBMS_XA supplied PL/SQL package or by the OCI or JDBC libraries. The XA interface implements X/Open Distributed Transaction Processing (DTP) architecture.
In an XStream Out configuration, changes made to the source database during a distributed transaction using either of the preceding methods are streamed to an XStream outbound server. The outbound server sends the changes in a transaction to the XStream client application after the transaction has committed.
However, the distributed transaction state is not replicated or sent. The client application does not inherit the in-doubt or prepared state of such a transaction. Also, XStream does not replicate or send the changes using the same global transaction identifier used at the source database for XA transactions.
XA transactions can be performed in two ways:
Tightly coupled, where different XA branches share locks
Loosely coupled, where different XA branches do not share locks
XStream supports replication of changes made by loosely coupled XA branches regardless of the COMPATIBLE initialization parameter value. XStream supports replication of changes made by tightly coupled branches on an Oracle RAC source database only if the COMPATIBLE initialization parameter is set to 11.2.0 or higher.
|
See Also:
|
XStream In enables a remote client application to send information into an Oracle database from another system, such as a non-Oracle database or a file system. XStream In provides an efficient, transaction-based interface for sending information to an Oracle database from client applications. XStream In can consume the information coming into the Oracle database in several ways, including data replication, auditing, and change data capture. XStream In supports both OCI and Java interfaces.
When compared with OCI client applications that make DML changes to an Oracle database directly, XStream In is more efficient for near real-time, transaction-based, heterogeneous DML changes to Oracle databases.
XStream In uses the following features of Oracle Streams:
High performance processing of DML changes using an apply process and, optionally, apply process parallelism
Apply process features such as SQL generation, conflict detection and resolution, error handling, and customized processing with apply handlers
Streaming network transmission of information with minimal network round-trips
Rules, rule sets, and rule-based transformations
When a custom rule-based transformation is specified on a rule used by an inbound server, the user who calls the transformation function is the apply user for the inbound server.
XStream In supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType. A client application sends LOB and XMLType data to the inbound server in chunks. Several chunks comprise a single column value of LOB, LONG, LONG RAW, or XMLType data type.
This section contains these topics:
With XStream In, an Oracle Streams apply process functions as an inbound server. An inbound server is an optional Oracle background process that receives LCRs from a client application. Specifically, a client application can attach to an inbound server and send row changes and DDL changes encapsulated in LCRs.
An external client application connects to the inbound server using the OCI or the Java interface. After the connection is established, the client application acts as the capture agent for the inbound server by streaming LCRs to it.
A client application can create multiple sessions. Each session can attach to only one inbound server, and each inbound server can serve only one session at a time. However, different client application sessions can connect to different inbound servers or outbound servers. A client application can detach from the inbound server whenever necessary.
Figure 2-3 shows an inbound server configuration.
|
Note: An inbound server uses a queue that is not shown in Figure 2-3. An inbound server's queue is only used to store error transactions. |
The following are considerations for XStream inbound servers:
You can control a DML or DDL trigger's firing property using the SET_TRIGGER_FIRING_PROPERTY procedure in the DBMS_DDL package. This procedure lets you specify whether a trigger always fires, fires once, or fires for apply process changes only. When a trigger is set to fire once, it fires for changes made by a user process, but it does not fire for changes made by an apply process or inbound server. A trigger's firing property works the same for apply processes and inbound servers. See Oracle Streams Concepts and Administration.
An inbound server ignores the setting for the ignore_transaction apply process parameter because LCRs sent to the inbound server by the client application might not have transaction ID values.
|
Note: Starting with Oracle Database 11g Release 2 (11.2.0.2), theignore_transaction parameter is available for outbound servers and apply processes. |
An inbound server ignores the setting for the maximum_scn apply process parameter because LCRs sent to the inbound server by the client application might not have SCN values.
Currently, an inbound server appears as an Oracle Streams apply process in Oracle Enterprise Manager.
|
See Also: Oracle Database PL/SQL Packages and Types Reference for more information about apply process parameters |
The following sections describe the position order in an LCR stream for both XStream Out and XStream In:
Both XStream Out and XStream In use LCR streams to share transactions. XStream Out sends LCR streams to a client application. XStream In receives LCR streams from a client application.
Each LCR has a position attribute. The position of an LCR identifies its placement in the stream of LCRs in a transaction. Each LCR position has the following properties:
The position is unique for each LCR.
The position is of RAW data type.
The position is strictly increasing within the LCR stream, within a transaction, and across transactions.
The position is byte-comparable, and the comparison results for multiple positions determines the ordering of the LCRs in the stream.
The position of an LCR remains identical when the database, the client application, or an XStream component restarts.
The position is not affected by any rule changes that might reduce or increase the number of LCRs in the stream.
XStream Out only sends committed data, and XStream In only receives committed data.
The following are the properties related to an LCR stream:
An LCR stream must be repeatable.
An LCR stream must contain a list of assembled, committed transactions. LCRs from one transaction are contiguous. There is no interleaving of transactions in an LCR stream.
Each transaction within an LCR stream must have an ordered list of LCRs and a transaction ID.
The last LCR in each transaction must be a commit LCR.
Each LCR must have a unique position.
The position of all LCRs within a single transaction and across transactions must be strictly increasing.
An LCR stream can batch LCRs from multiple transactions and arrange them in increasing position order. LCRs from one transaction are contiguous, and the position must be increasing in the transaction. Also, the position must be nonzero for all LCRs.
An XStream Out outbound server streams LCRs that were captured by a capture process to a client application. This section describes concepts related to the LCR positions for an outbound server.
LCRs that were captured by a capture process contain the following additional attributes related to LCR position:
The scn_from_position attribute contains the SCN of the LCR.
The commit_scn_from_position attribute contains the commit SCN of the transaction to which the LCR belongs.
|
Note: Thescn_from_position and commit_scn_from_position attributes are not present in row LCRs captured by a synchronous capture nor in explicitly captured row LCRs. |
If the outbound server or the client application stops abnormally, then the connection between the two is broken automatically. In this case, the client application must roll back all incomplete transactions.
The processed low position is a position below which all transactions have been processed by the client application. The client application must maintain its processed low position to recover properly after either it or the outbound server (or both) are restarted. The processed low position indicates that the client application has processed all LCRs that are less than or equal to this value. The client application can update the processed low position for each transaction that it consumes.
When the client application attaches to the outbound server, the following conditions related to the processed low position are possible:
The client application can pass a processed low position to the outbound server that is equal to or greater than the outbound server's processed low position. In this case, the outbound server resumes streaming LCRs from the first LCR that has a position greater than the client application's processed low position.
The client application can pass a processed low position to the outbound server that is less than the outbound server's processed low position. In this case, the outbound server raises an error.
The client application can pass NULL to the outbound server. In this case, the outbound server determines the processed low position automatically and starts streaming LCRs from the LCR that has a position greater than this processed low position. When this happens, the client application must suppress or discard each LCR with a position less than or equal to the client application's processed low position.
To minimize network latency, the outbound server streams LCRs to the client application with time-based acknowledgments. For example, the outbound server might send an acknowledgment every 30 seconds. This streaming protocol fully utilizes the available network bandwidth, and the performance is unaffected by the presence of a wide area network (WAN) separating the sender and the receiver. The outbound server extends the underlying Oracle Streams infrastructure, and the outbound server maintains the streaming performance rate.
Using OCI, you can control the time period of the interval by setting the OCI_ATTR_XSTREAM_ACK_INTERVAL attribute through the OCI client application. The default is 30 seconds.
Using Java, you can control the time period of the interval by setting the batchInterval parameter in the attach method in the XStreamOut class. The client application can specify this interval when it invokes the attach method.
If the interval is large, then the outbound server can stream out more LCRs for each acknowledgment interval. However, a longer interval delays how often the client application can send the processed low position to the outbound server. Therefore, a longer interval might mean that the processed low position maintained by the outbound server is not current. In this case, when the outbound server restarts, it must start processing LCRs at an earlier position than the one that corresponds to the processed low position maintained by the client application. Therefore, more LCRs might be retransmitted, and the client application must discard the ones that have been applied.
A client application streams LCRs to an XStream In inbound server. This section describes concepts related to the LCR positions for an inbound server.
Each position must be encoded in a format (such as base-16 encoding) that supports byte comparison. The position is essential to the total order of the transaction stream sent by client applications using the XStream In interface.
The following positions are important for inbound servers:
The applied low position indicates that the LCRs less than or equal to this value have been applied.
An LCR is applied by an inbound server when the LCR has either been executed, sent to an apply handler, or moved to the error queue.
The spill position indicates that the LCRs with positions less than or equal to this value have either been applied or spilled from memory to hard disk.
The applied high position indicates the highest position of an LCR that has been applied.
When the commit_serialization apply process parameter is set to DEPENDENT_TRANSACTIONS for an inbound server, an LCR with a higher commit position might be applied before an LCR with a lower commit position. When this happens, the applied high position is different from the applied low position.
The processed low position is the higher value of either the applied low position or the spill position.
The processed low position is the position below which the inbound server no longer requires any LCRs. This position corresponds with the oldest SCN for an Oracle Streams apply process that applies changes captured by a capture process.
The processed low position indicates that the LCRs with positions less than or equal to this position have been processed by the inbound server. If the client re-attaches to the inbound server, then it must send only LCRs with positions greater than the processed low position because the inbound server discards any LCRs with positions less than or equal to the processed low position.
If the client application stops abnormally, then the connection between the client application and the inbound server is automatically broken. Upon restart, the client application retrieves the processed low position from the inbound server and instructs its capture agent to retrieve changes starting from this processed low position.
To limit the recovery time of a client application using the XStream In interface, the client application can send activity, such as empty transactions, periodically to the inbound server. Row LCRs can include commit transaction control directives. When there are no LCRs to send to the server, the client application can send a row LCR with a commit directive to advance the inbound server's processed low position. This activity acts as an acknowledgment so that the inbound server's processed low position is advanced.
Example 2-1 Advancing the Processed Low Position of an Inbound Server
Consider a client application and an external data source. The client application sends changes made to the hr.employees table to the inbound server for processing, but the external data source includes many other tables, including the oe.orders table.
Assume that the following changes are made to the external data source:
| Position | Change | Client Application Activity |
|---|---|---|
| 1 | Insert into the hr.employees table | Send row LCR including the change to the inbound server |
| 2 | Insert into the oe.orders table | None |
| 3 | Commit | Send a row LCR with a commit directive to inbound server |
| 4 | Insert into the oe.orders table | None |
| 5 | Update the oe.orders table | None |
| 6 | Commit | None |
| 7 | Commit | None |
| ... | ... (Activity on the external data source, but no changes to the hr.employees table) | None |
| 100 | Insert into the oe.orders table | None |
| 101 | Commit | None |
The client application gets the changes from the external data source, generates appropriate LCRs, and sends the LCRs to the inbound server. Therefore, the inbound server receives the following LCRs:
Row LCR for position 1
Row LCR for position 3
After position 3, there are no relevant changes to send to the inbound server. If the inbound server restarts when the client application has processed all the changes up to position 101, then, after restarting, the client application must recheck all of the external database changes from position 4 forward. The rechecks are required because the inbound server's processed low position is 3.
Instead, assume that the client application sends commits to the inbound server periodically, even when there are no relevant changes to the hr.employees table:
| Position | Change | Client Application Activity |
|---|---|---|
| 1 | Insert into the hr.employees table | Send row LCR including the change to the inbound server |
| 2 | Insert into the oe.orders table | None |
| 3 | Commit | Send a row LCR with a commit directive to inbound server |
| 4 | Insert into the oe.orders table | None |
| 5 | Update the oe.orders table | None |
| 6 | Commit | None |
| 7 | Commit | None |
| ... | ... (Activity on the external data source, but no changes to the hr.employees table) | Send several row LCRs, each one with a commit directive, to the inbound server |
| 100 | Insert into the oe.orders table | None |
| 101 | Commit | Send a row LCR with a commit directive to the inbound server |
In this case, the inbound server moves its processed low position to 101 when it has processed all of the row LCRs sent by the client application. If the inbound server restarts, its processed low position is 101, and the client application does not need to check all of the changes back to position 3.
The sample applications in "Sample XStream Client Application" include code that sends a row LCR with a commit directive to an inbound server. These commit directives are sometimes called "ping LCRs." Search for the word "ping" in the sample XStream client applications to find the parts of the applications that include this code.
Table 2-1 compares how an XStream Out outbound server and an XStream In inbound server use positions.
Table 2-1 Position Use in the Outbound Server and the Inbound Server
| XStream Out Outbound Server | XStream In Inbound Server |
|---|---|
|
The outbound server exposes the position. |
The client application sets the position. |
|
If the outbound server or client application stops abnormally, then all LCRs above the processed low position are resent. The processed low position is equivalent to an apply process low watermark (LWM), and the apply process obtains the oldest SCN value by using this value. |
If the inbound server or client application stops abnormally, then the client application must retransmit all LCRs with a position greater than or equal to the processed low position. The processed low position is equivalent to the apply process low water mark (LWM). |
SQL generation is the ability to generate the SQL statement required to perform the change encapsulated in a row LCR. Apply processes, XStream outbound servers, and XStream inbound servers can use SQL generation to generate the SQL statement necessary to perform the insert, update, or delete operation in a row LCR.
This section contains these topics:
You can use the following interfaces to perform SQL generation:
The PL/SQL interface, which uses the GET_ROW_TEXT and GET_WHERE_CLAUSE member procedures for row LCRs
The OCI for XStream
The Java interface for XStream
The PL/SQL interface generates SQL in a CLOB data type, while the OCI and Java interfaces generate SQL in plain text. In the Java interface, the size of the text is limited by the size of String data type.
|
See Also:
|
SQL statements can be generated in one of two formats: inline values or bind variables. Use inline values when the returned SQL statement is relatively small. For larger SQL statements, use bind variables. In this case, the bind variables are passed to the client application in a separate list that includes pointers to both old and new column values.
For information about using bind variables with each interface, refer to the following documentation:
The documentation for the GET_ROW_TEXT and GET_WHERE_CLAUSE row LCR member procedures in Oracle Database PL/SQL Packages and Types Reference
The documentation for DefaultRowLCR.getBinds() in Oracle Database XStream Java API Reference
|
Note: For generated SQL statements with the values inline, SQL injection is possible. SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database to view or manipulate restricted data. Oracle strongly recommends using bind variables if you plan to execute the generated SQL statement. See Oracle Database PL/SQL Language Reference for more information about SQL injection. |
Regarding data types and character sets, SQL generation works the same way for XStream Out outbound servers, XStream In inbound servers, and apply processes. For detailed information, see Oracle Streams Concepts and Administration.
A demo that performs SQL generation is available. The demo uses the DBMS_XSTREAM_ADM PL/SQL package to configure an XStream Out environment, and it uses an OCI client application to perform SQL generation.
The demo uses SQL generation to replicate DML changes from a source database to a destination database. Specifically, the demo creates the xsdemosg schema in both the source database and the destination database. It creates various types of tables in the xsdemosg schema at each database, including tables with LOB columns. It executes a set of DML statements on the tables in xsdemosg schema in the source database. Oracle Streams components, such as a capture process and a queue, send the changes in the form of LCRs to an XStream outbound server that is also running on the source database. The outbound server makes the LCRs available to the demo client application.
The demo client application, when run, uses the OCI API to connect to the outbound server and receive the LCRs. The demo client application uses SQL generation to execute the changes that are encapsulated in the LCRs. Therefore, the client application replicates the changes made to xsdemosg schema in the source database to the xsdemosg in the destination database.
You can modify the demo to replicate changes to any schema. Both the schema and the replicated tables must exist on both the source database and the destination database. SQL generation is only possible for DML changes. Therefore, this demo cannot be used to replicate DDL changes.
This demo is available in the following location:
$ORACLE_HOME/rdbms/demo/xstream/sqlgen
|
Note: The SQL generation demo is not available for the XStream Java API. |
XStream Out allows a user to receive LCRs. After an XStream Out user receives LCRs, the user might save the contents of LCRs to a file or generate the SQL statements to execute the LCRs on a non-Oracle database. XStream In allows a user to update tables in its own schema. XStream does not assume that the connected user to the outbound server or inbound server is trusted.
Java and OCI client applications must connect to an Oracle database before attaching to an XStream outbound server created on that database. The connected user must be the same as the connect user configured for the outbound server. Otherwise, an error is raised.
Java and OCI client applications must connect to an Oracle database before attaching to an XStream inbound server created on that database. The connected user must be the same as the apply user configured for the inbound server. Otherwise, an error is raised.
The XStream Java layer API relies on Oracle JDBC security because XStream accepts the Oracle JDBC connection instance created by client applications in the XStream attach API. The connected user is validated as an XStream user.
|
See Also:
|
Oracle Streams provides other ways to implement heterogeneous information sharing besides XStream, both in past releases and in the current release. These ways include:
Replicating data changes to a non-Oracle database using an Oracle Database Gateway
Dequeuing messages from an Oracle database using a Java Message Service (JMS) client
Enqueuing messages directly into an Oracle database queue with a client application
This chapter describes common problems you might encounter while using XStream and explains how to solve them.
This chapter contains the following topics:
With XStream, an Oracle Streams apply process can function as an outbound server or an inbound server. An XStream configuration can also include other components, such as capture processes, queues, propagations, rules, and rule-based transformations.
To diagnose problems with XStream, you can use many of the same techniques that are used to diagnose problems with Oracle Streams components. These techniques include the following:
Viewing Oracle Streams alerts
Using the Streams configuration report and health check script
Handling performance problems because of an unavailable destination
Checking the trace files and alert log for problems
See Oracle Streams Concepts and Administration for detailed information about these topics.
In general, you can troubleshoot XStream outbound servers and inbound servers in the same way that you troubleshoot Oracle Streams apply processes. In addition, an XStream Out environment includes capture processes and queues, and might include other components, such as propagations, rules, and rule-based transformations. To troubleshoot these components, see the troubleshooting documentation in Oracle Streams Concepts and Administration.
This section describes common problems and solutions for XStream.
This section contains the following topics:
An OCI Client Application Cannot Attach to the Outbound Server
Changes Are Failing to Reach the Client Application in XStream Out
LCRs Streaming from an Outbound Server Are Missing Extra Attributes
An XStream client application cannot attach to an outbound server using the Oracle Call Interface (OCI) OCIXStreamOutAttach() function.
The following sections describe possible problems and their solutions.
Problem 1: Client Application Not Connected as Connect User
The client application is not connected as the outbound server's connect user to the outbound server's database. The client application connected to the database as a different user.
Solution 1
To correct problem 1:
Modify the client application to connect to the database as the connect user before attaching to the outbound server.
Problem 2: Client Application Not Passing Service Handle
The client application is not passing a service handle to the outbound server.
Solution 2
To correct problem 2:
Modify the client application so that it passes a service handle using OCISvcCtx and not OCIServer.
In an XStream Out configuration, database changes that should be captured and streamed to the XStream client application are not reaching the client application.
The following sections describe possible problems and their solutions.
Problem 1: Capture Process Has Fallen Behind
The capture process has fallen behind.
To determine whether the capture process has fallen behind:
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.
Run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN CREATE_MESSAGE HEADING 'Last LCR|Create Time'
COLUMN ENQUEUE_MESSAGE HEADING 'Last|Enqueue Time'
SELECT CAPTURE_NAME,
TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_MESSAGE,
TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_MESSAGE
FROM V$STREAMS_CAPTURE;
This query displays the time when the capture process last created a logical change record (LCR) and the time when the capture process last enqueued an LCR. If the times returned are before the time when the database changes were made, then the capture process must catch up and capture the changes.
Solution 1
No action is required. Normally, the capture process will catch up on its own without the need for intervention.
Problem 2: Rules or Rule-Based Transformation Excluding Changes
Rules or rule-based transformations are excluding the changes that should be captured.
Rules determine which LCRs are captured by a capture process, sent from a source queue to a destination queue by a propagation, and sent to an XStream client application by an outbound server. If the rules are not configured properly, then the client application might not receive the LCRs it should receive. The client application might also receive LCRs that it should not receive.
Rule-based transformations modify the contents of LCRs. Therefore, if the expected change data is not reaching the client application, it might be because a rule-based transformation modified the data or deleted the data. For example, a DELETE_COLUMN declarative rule-based transformation removes a column from an LCR.
Solution 2
To correct problem 2:
Check the rules and rule-based transformations that are configured for each component in the stream from the capture process to the client application, and correct any problems.
Problem 3: LCRs Blocked in the Stream
If the capture process has not fallen behind, and there are no problems with rules or rule-based transformations, then LCRs might be blocked in the stream for some other reason. For example, a propagation or outbound server might be disabled, a database link might be broken, or there might be another problem.
You can track an LCR through a stream using one of the following methods:
Setting the message_tracking_frequency capture process parameter to 1 or another relatively low value
Running the SET_MESSAGE_TRACKING procedure in the DBMS_STREAMS_ADM package
After using one of these methods, use the V$STREAMS_MESSAGE_TRACKING view to monitor the progress of LCRs through the stream. By tracking an LCR through the stream, you can determine where the LCR is blocked.
Solution 3
To correct problem 3:
Take the appropriate action based on the reason that the LCR is blocked. For example, if a propagation is disabled, then enable it.
|
See Also:
|
LCRs streaming from an outbound server are expected to include extra attributes, but these attributes are not included in the LCRs.
LCRs can contain the following extra attributes related to database changes:
row_id
serial#
session#
thread#
tx_name
username
By default, a capture process does not capture these extra attributes. If you want extra attributes to be included in LCRs streamed from an outbound server to an XStream client application, but the LCRs do not contain values for extra attributes, then make sure the capture process that captures changes for the outbound server is configured to capture values for the extra attributes.
The following sections describe the possible problem and its solution.
Problem: Capture Process Not Configured to Capture Extra Attributes
The capture process is not configured to capture the required extra attributes.
To display the extra attributes currently being captured by the capture processes in a database:
Connect to the database running the capture process as the XStream administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Run the following query:
COLUMN CAPTURE_NAME HEADING 'Capture Process' FORMAT A30 COLUMN ATTRIBUTE_NAME HEADING 'Attribute Name' FORMAT A30 SELECT CAPTURE_NAME, ATTRIBUTE_NAME FROM DBA_CAPTURE_EXTRA_ATTRIBUTES WHERE INCLUDE = 'YES' ORDER BY CAPTURE_NAME;
If an extra attribute is not displayed by this query, then it is not being captured.
Solution
To solve the problem, configure the capture process to capture the required extra attributes:
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.
Run the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package.
The XStream client application in an XStream Out configuration is unresponsive.
The following sections describe the possible problem and its solution.
Problem 1: Streams Pool Size Is Too Small
The Streams pool size might be too small.
To determine whether the Streams pool size is too small:
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.
Run the following queries at the database that contains the outbound server:
Query the V$PROPAGATION_RECEIVER view.:
SELECT STATE FROM V$PROPAGATION_RECEIVER;
If the state is WAITING FOR MEMORY, then consider increasing the Streams pool size.
Query the V$STREAMS_POOL_STATISTICS view.:
SELECT TOTAL_MEMORY_ALLOCATED/CURRENT_SIZE FROM V$STREAMS_POOL_STATISTICS;
If the value returned is .90 or greater, then consider increasing the Streams pool size.
If the outbound server receives changes from a capture process that is running on the same database, then query the V$STREAMS_CAPTURE view.:
SELECT STATE FROM V$STREAMS_CAPTURE;
If the state is WAITING FOR BUFFER QUEUE TO SHRINK, then increase the Streams pool size.
Solution 1
To correct problem 1:
Increase the Streams pool size by modifying the STREAMS_POOL_SIZE initialization parameter or by modifying other initialization parameters related to memory.
|
See Also:
|
Problem 2: Programming Errors
If there is enough memory in the Streams pool, then check your client application for programming errors.
Solution 2
To correct problem 2:
Correct the programming errors.
If an XStream In configuration cannot identify an inbound server, then the following error is returned:
ORA-26840: STREAMS unable to identify an apply for the source database "%s"
The following sections describe the possible problem and its solution.
Problem: Multiple Subscribers to the Inbound Server's Queue
The ORA-26840 error indicates that there are multiple subscribers to the queue used by the inbound server. Subscribers can include inbound servers, outbound servers, apply processes, and propagations.
To determine whether there are multiple subscribers to the inbound server's queue:
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.
Run the following query:
SELECT APPLY_NAME SUBSCRIBER, QUEUE_NAME FROM DBA_APPLY UNION SELECT PROPAGATION_NAME, SOURCE_QUEUE_NAME FROM DBA_PROPAGATION ORDER BY QUEUE_NAME;
You can add a WHERE clause to the query to limit the output to the inbound server's queue.
Solution
To correct the problem:
If the query returns multiple subscribers to the inbound server's queue, then reconfigure the subscribers so that the inbound server is the only subscriber.
In an XStream In configuration, database changes are sent in the form of LCRs from the XStream client application to an inbound server.
The following sections describe the possible problem and its solution.
Problem: LCRs Blocked During Apply
If the inbound server is not applying the changes, then the LCRs are blocked during apply. For example, the inbound server might be disabled, an apply handler might be processing LCRs incorrectly, or there might be another problem.
You can track an LCR during apply by an inbound server using the following methods:
If the client application uses the XStream OCI API, then use the OCI_LCR_ATTR_MESSAGE_TRACKING_LABEL attribute in the OCILCRAttributesSet function to set tracking label, and use the OCIXStreamInLCRSend function to send LCRs.
The following is sample code for setting message tracking for LCRs using the OCI API:
static void set_msg_tracking(myctx_t *ctx, void *lcr, oratext *label)
{
oci_t *ocip = ctx->ocip;
oratext *attr_names[] = {OCI_LCR_ATTR_MESSAGE_TRACKING_LABEL};
ub2 attr_names_lens[] =
{sizeof(OCI_LCR_ATTR_MESSAGE_TRACKING_LABEL)-1};
ub2 dty[] = {SQLT_CHR};
OCIInd ind[] = {OCI_IND_NOTNULL};
void *data[1];
ub2 data_lens[1];
data[0] = label;
data_lens[0] = strlen(label);
OCICALL(ocip,
OCILCRAttributesSet(ocip->svcp, ocip->errp, 1, attr_names,
attr_names_lens, dty, data, ind, data_lens,
lcr, OCI_DEFAULT));
}
/*---------------------------------------------------------------------
* send_lcr - Send the given lcr and bump up lcr position.
*---------------------------------------------------------------------*/
static void send_lcr(myctx_t *ctx, void *lcr, ub1 lcrtype)
{
oci_t *ocip = ctx->ocip;
set_msg_tracking(ctx, lcr, "tracking_label");
OCICALL(ocip,
OCIXStreamInLCRSend(ocip->svcp, ocip->errp, lcr, lcrtype,
0, OCI_DEFAULT));
ctx->lcr_pos++;
}
Using this sample code, to enable message tracking, add the set_msg_tracking procedure to the client application, call set_msg_tracking from the send_lcr procedure when LCRs should be tracked. Replace tracking_label with the string you want to use for the tracking label.
If the client application uses the XStream Java API, then use the setMessageTrackingLabel method.
The following is sample code for setting message tracking for LCRs using the Java API:
{
((AbstractLCR)lcr).setMessageTrackingLabel("tracking_label");
}
Replace tracking_label with the string you want to use for the tracking label.
After using one of these methods, use the V$STREAMS_MESSAGE_TRACKING view to monitor the progress of LCRs through a stream. By tracking an LCR, you can determine where the LCR is blocked.
|
Note: If the LCRs originated from an XStream Out configuration, then the easiest way to track the LCRs is by using the methods described in "Changes Are Failing to Reach the Client Application in XStream Out". |
Solution
To correct the problem:
Take the appropriate action based on the reason that the LCR is blocked. For example, if the inbound server is disabled, then enable it.
You can check My Oracle Support at http://support.oracle.com for more solutions to your problem.
You can visit http://www.oracle.com/support/contact.html for more information about Oracle Support.
This part contains the XStream OCI API reference. This part contains the following chapters:
Oracle Database XStream Guide describes the features and functionality of XStream. This document contains conceptual information about XStream, along with information about configuring and managing an XStream environment. In addition, this document contains reference information related to XStream.
This guide is intended for database administrators who configure and manage XStream environments. To use this document, database administrators must be familiar with relational database concepts, SQL, distributed database administration, Oracle Streams concepts, PL/SQL, and the operating systems under which they run an XStream environment.
This guide is also intended for programmers who develop applications that use XStream. To use this document, programmers need knowledge of an application development language and relational database concepts.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
For more information, see the following documents:
Many of the examples in this book use the sample schemas of the sample database, which is installed by default when you install Oracle Database. Refer to Oracle Database Sample Schemas for information about how these schemas were created and how you can use them yourself.
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
XStream provides a flexible infrastructure for sharing information between Oracle data sources and non-Oracle data sources. Therefore, you can use XStream in many different ways to meet the needs of various organizations. This chapter describes the most common use cases for XStream.
This chapter contains these topics:
Each XStream use case in this chapter contains three main elements:
A general description of the use case as it applies to both XStream Out and XStream In
A specific scenario for XStream Out
A specific scenario for XStream In
In some cases, a section includes a reference to sample code in the Oracle Database installation that illustrates a scenario.
In each XStream Out use case, the following components and actions send Oracle Database changes to a client application:
Oracle Streams captures data changes made to an Oracle database.
Oracle Streams sends these changes, in the form of logical change records (LCRs), to an outbound server.
The outbound server sends the LCRs to a client application.
How the client application processes the LCRs is different for each use case.
In each XStream In use case, the following components and actions send Oracle Database changes to an inbound server:
A client application gathers data changes from an external data source and sends them to an inbound server in the form of LCRs.
The inbound server receives the LCRs from a client application.
The inbound server can apply the data changes to database objects in an Oracle database. The inbound server can also process the LCRs in a customized way.
How the client application gathers the data changes is different for each use case.
You can configure a heterogeneous replication environment with XStream. Replication is generally used to improve availability and to improve performance by spreading the network load over multiple regions and servers. In a heterogeneous replication environment, data is replicated between databases from different vendors. See Oracle Streams Replication Administrator's Guide for common reasons to use replication.
XStream Out can send data changes made to an Oracle database to a non-Oracle database. Specifically, the client application connects to the outbound server and receives changes made to tables within the Oracle database. The client application then applies the data changes in the LCRs to the non-Oracle database. The client application can process the LCRs in any customized way before applying them.
XStream In can receive data changes made to a non-Oracle database. Specifically, the client application gathers the data changes made to the non-Oracle database, formats these changes into LCRs, and sends these LCRs to an inbound server. The inbound server applies the changes in the LCRs to the Oracle database.
Some environments use files to store data changes. Typically, files store data changes for the following reasons:
To process data changes in an environment that has no physical network or a limited physical network. For example, some locations do not have a physical network for security reasons.
To process data changes in an environment that uses disconnected computing. For example, a salesperson might fill orders on a laptop at various locations without a network connection, and then update a primary database over the network once a day.
To process data changes in an environment that uses satellite communications. In this case, a bulk transfer of files is more efficient than incremental changes over the network.
XStream Out can send Oracle Database changes to a file in a file system. Specifically, the client application writes the data changes in LCRs to the file. The client application can process the LCRs in any customized way before writing them to the file, and the file can reside on the computer system running the client application or on a different computer system. Using SQL generation, the client application can also write the SQL statement required to perform the change encapsulated in a row LCR to a file.
XStream In can send data changes from a file to an Oracle database. Specifically, the client application reads the data changes from the file and sends the changes, in the form of LCRs, to an inbound server.
A demo is available that creates sample client applications that perform file-based replication using the XStream APIs. Specifically, at one database, the demo creates an XStream Out configuration that captures database changes and sends the LCRs to an outbound server. A client application attaches to the outbound server and writes the database changes to a file.
At a different database, the demo creates an XStream In client application that attaches to an inbound server, reads the changes in the file, and sends them in the form of LCRs to the inbound server. The inbound server applies the changes to the database objects at the destination database.
This demo is available in the following location:
$ORACLE_HOME/rdbms/demo/xstream/fbr
Some environments cache data in memory to improve performance. Cached data can provide low response times and high throughput for systems that require the best possible performance. XStream can share data changes incrementally with a client side memory cache.
XStream Out can incrementally refresh a client-side memory cache by sending Oracle database changes to a memory cache. Specifically, the client application applies the data changes in the LCRs to the memory cache. The client application can process the LCRs in any customized way before applying them, and the memory cache can reside on the computer system running the client application or on a different computer system.
XStream In can incrementally retrieve data changes from a memory cache. Specifically, the client application retrieves the data changes and sends the changes, in the form of LCRs, to an inbound server. The memory cache can reside on the computer system running the client application or on a different computer system.
