33 Troubleshooting Apply
The following topics describe identifying and resolving common apply process problems in an Oracle Streams environment:
Is the Apply Process Enabled?
An apply process applies changes only when it is enabled.
You can check whether an apply process is enabled, disabled, or aborted by querying the DBA_APPLY data dictionary view. For example, to check whether an apply process named apply is enabled, run the following query:
SELECT STATUS FROM DBA_APPLY WHERE APPLY_NAME = 'APPLY';
If the apply process is disabled, then your output looks similar to the following:
STATUS
--------
DISABLED
If the apply process is disabled, then try restarting it. If the apply process is aborted, then you might need to correct an error before you can restart it successfully. If the apply process did not shut down cleanly, then it might not restart. In this case, it returns the following error:
ORA-26666 cannot alter STREAMS process
If this happens then, then run the STOP_APPLY procedure in the DBMS_APPLY_ADM package with the force parameter set to TRUE. Next, restart the apply process.
To determine why an apply process aborted, query the DBA_APPLY data dictionary view or check the trace files for the apply process. The following query shows when the apply process aborted and the error that caused it to abort:
COLUMN APPLY_NAME HEADING 'APPLY|Process|Name' FORMAT A10
COLUMN STATUS_CHANGE_TIME HEADING 'Abort Time'
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40
SELECT APPLY_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
FROM DBA_APPLY WHERE STATUS='ABORTED';
Is the Apply Process Current?
If an apply process has not applied recent changes, then the problem might be that the apply process has fallen behind. If apply process latency is high, then you might be able to improve performance by adjusting the setting of the parallelism apply process parameter.
You can check apply process latency by querying the V$STREAMS_APPLY_COORDINATOR dynamic performance view.
Does the Apply Process Apply Captured LCRs?
An apply process can apply either captured LCRs from its buffered queue, or it can apply messages from its persistent queue, but not both types of messages. Messages in a persistent queue can be persistent LCRs and persistent user messages. An apply process might not be applying messages of a one type because it was configured to apply the other type of messages.
You can check the type of messages applied by an apply process by querying the DBA_APPLY data dictionary view. For example, to check whether an apply process named apply applies captured LCRs or not, run the following query:
COLUMN APPLY_CAPTURED HEADING 'Type of Messages Applied' FORMAT A25
SELECT DECODE(APPLY_CAPTURED,
'YES', 'Captured',
'NO', 'Messages from Persistent Queue') APPLY_CAPTURED
FROM DBA_APPLY
WHERE APPLY_NAME = 'APPLY';
If the apply process applies captured LCRs, then your output looks similar to the following:
Type of Messages Applied
-------------------------
Captured
If an apply process is not applying the expected type of messages, then you might need to create an apply process to apply these messages.
Is the Apply Process's Queue Receiving the Messages to be Applied?
An apply process must receive messages in its queue before it can apply these messages. Therefore, if an apply process is applying messages captured by a capture process or a synchronous capture, then the capture process or synchronous capture that captures these messages must be configured properly. If it is a capture process, then it must also be enabled. Similarly, if messages are propagated from one or more databases before reaching the apply process, then each propagation must be enabled and must be configured properly. If a capture process, a synchronous capture, or a propagation on which the apply process depends is not enabled or is not configured properly, then the messages might never reach the apply process's queue.
The rule sets used by all Oracle Streams clients, including capture processes, synchronous captures, and propagations, determine the behavior of these Oracle Streams clients. Therefore, ensure that the rule sets for any capture processes, synchronous capture, or propagations on which an apply process depends contain the correct rules. If the rules for these Oracle Streams clients are not configured properly, then the apply process's queue might never receive the appropriate messages. Also, a message traveling through a stream is the composition of all of the transformations done along the path. For example, if a capture process uses subset rules and performs row migration during capture of a message, and a propagation uses a rule-based transformation on the message to change the table name, then, when the message reaches an apply process, the apply process rules must account for these transformations.
In an environment where a capture process or synchronous capture captures changes that are propagated and applied at multiple databases, you can use the following guidelines to determine whether a problem is caused by a capture process, a synchronous capture, or a propagation on which an apply process depends or by the apply process itself:
-
If no other destination databases of a capture process or synchronous capture are applying the changes, then the problem is most likely caused by the capture process or synchronous capture, or by a propagation near the capture process. In this case, first ensure that the capture process or synchronous capture is configured properly, and then ensure that the propagations nearest the capture process or synchronous capture are enabled and configured properly. For a capture process, also ensure that the capture process is enabled.
-
If other destination databases of a capture process or synchronous capture are applying the changes, then the problem is most likely caused by the apply process itself or a propagation near the apply process. In this case, first ensure that the apply process is enabled and configured properly, and then ensure that the propagations nearest the apply process are enabled and configured properly.
Is a Custom Apply Handler Specified?
You can use apply handlers to handle messages dequeued by an apply process in a customized way. These handlers include statement DML handlers, procedure DML handlers, DDL handlers, precommit handlers, and message handlers. If an apply process is not behaving as expected, then check the handlers used by the apply process, and correct any flaws. You might need to modify a SQL statement in a statement DML handler to correct an apply problem. You also might need to modify a PL/SQL procedure or remove it to correct an apply problem.
You can find the names of these procedures by querying the DBA_APPLY_DML_HANDLERS and DBA_APPLY data dictionary views.
Is the AQ_TM_PROCESSES Initialization Parameter Set to Zero?
The AQ_TM_PROCESSES initialization parameter controls time monitoring on queue messages and controls processing of messages with delay and expiration properties specified. In Oracle Database 10g or later, the database automatically controls these activities when the AQ_TM_PROCESSES initialization parameter is not set.
If an apply process is not applying messages, but there are messages that satisfy the apply process rule sets in the apply process's queue, then ensure that the AQ_TM_PROCESSES initialization parameter is not set to zero at the destination database. If this parameter is set to zero, then unset this parameter or set it to a nonzero value and monitor the apply process to see if it begins to apply messages.
To determine whether there are messages in a buffered queue, you can query the V$BUFFERED_QUEUES and V$BUFFERED_SUBSCRIBERS dynamic performance views. To determine whether there are messages in a persistent queue, you can query the queue table for the queue.
Does the Apply User Have the Required Privileges?
If the apply user does not have explicit EXECUTE privilege on an apply handler procedure or custom rule-based transformation function, then an ORA-26808 error might result when the apply user tries to run the procedure or function. Typically, this error is causes the apply process to abort without adding errors to the DBA_APPLY_ERROR view. However, the trace file for the apply coordinator reports the error. Specifically, an error similar to the following appears in the trace file:
ORA-26808: Apply process AP01 died unexpectedly
Typically, error messages surround this message, and one or more of these messages contain the name of the procedure or function. To correct the problem, grant the required EXECUTE privilege to the apply user.
Is the Apply Process Encountering Contention?
An apply server is a component of an apply process. Apply servers apply DML and DDL changes to database objects at a destination database. An apply process can use one or more apply servers, and the parallelism apply process parameter specifies the number of apply servers that can concurrently apply transactions. For example, if parallelism is set to 5, then an apply process uses a total of five apply servers.
An apply server encounters contention when the apply server must wait for a resource that is being used by another session. Contention can result from logical dependencies. For example, when an apply server tries to apply a change to a row that a user has locked, then the apply server must wait for the user. Contention can also result from physical dependencies. For example, interested transaction list (ITL) contention results when two transactions that are being applied, which might not be logically dependent, are trying to lock the same block on disk. In this case, one apply server locks rows in the block, and the other apply server must wait for access to the block, even though the second apply server is trying to lock different rows. See "Is the Apply Process Waiting for a Dependent Transaction?" for detailed information about ITL contention.
When an apply server encounters contention that does not involve another apply server in the same apply process, it waits until the contention clears. When an apply server encounters contention that involves another apply server in the same apply process, one of the two apply servers is rolled back. An apply process that is using multiple apply servers might be applying multiple transactions at the same time. The apply process tracks the state of the apply server that is applying the transaction with the lowest commit SCN. If there is a dependency between two transactions, then an apply process always applies the transaction with the lowest commit SCN first. The transaction with the higher commit SCN waits for the other transaction to commit. Therefore, if the apply server with the lowest commit SCN transaction is encountering contention, then the contention results from something other than a dependent transaction. In this case, you can monitor the apply server with the lowest commit SCN transaction to determine the cause of the contention.
The following four wait states are possible for an apply server:
-
Not waiting: The apply server is not encountering contention and is not waiting. No action is necessary in this case.
-
Waiting for an event that is not related to another session: An example of an event that is not related to another session is a log file sync event, where redo data must be flushed because of a commit or rollback. In these cases, Oracle Database writes nothing to the log initially because such waits are common and are usually transient. If the apply server is waiting for the same event after a certain interval of time, then the apply server writes a message to the alert log and apply process trace file. For example, an apply server AS01 might write a message similar to the following:
AS01: warning -- apply server 1, sid 26 waiting for event:
AS01: [log file sync] ...
Oracle Database writes this output to the alert log at intervals until the problem is rectified.
-
Waiting for an event that is related to a non apply server session: The apply server writes a message to the alert log and apply process trace file immediately. For example, an apply server AS01 might write a message similar to the following:
AS01: warning -- apply server 1, sid 10 waiting on user sid 36 for event:
AS01: [enq: TM - contention] name|mode=544d0003, object #=a078,
table/partition=0
Oracle Database writes this output to the alert log at intervals until the problem is rectified.
-
Waiting for another apply server session: This state can be caused by interested transaction list (ITL) contention, but it can also be caused by more serious issues, such as an apply handler that obtains conflicting locks. In this case, the apply server that is blocked by another apply server prints only once to the alert log and the trace file for the apply process, and the blocked apply server issues a rollback to the blocking apply server. When the blocking apply server rolls back, another message indicating that the apply server has been rolled back is printed to the log files, and the rolled back transaction is reassigned by the coordinator process for the apply process.
For example, if apply server 1 of apply process AP01 is blocked by apply server 2 of the same apply process (AP01), then the apply process writes the following messages to the log files:
AP01: apply server 1 blocked on server 2
AP01: [enq: TX - row lock contention] name|mode=54580006, usn<<16 |
slot=1000e, sequence=1853
AP01: apply server 2 rolled back
You can determine the total number of times an apply server was rolled back since the apply process last started by querying the TOTAL_ROLLBACKS column in the V$STREAMS_APPLY_COORDINATOR dynamic performance view.
Is the Apply Process Waiting for a Dependent Transaction?
If you set the parallelism parameter for an apply process to a value greater than 1, and you set the commit_serialization parameter of the apply process to FULL, then the apply process can detect interested transaction list (ITL) contention if there is a transaction that is dependent on another transaction with a higher SCN. ITL contention occurs if the session that created the transaction waited for an ITL slot in a block. This happens when the session wants to lock a row in the block, but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block.
ITL contention also is possible if the session is waiting due to a shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK.
When an apply process detects such a dependency, it resolves the ITL contention automatically and records information about it in the alert log and apply process trace file for the database. ITL contention can negatively affect the performance of an apply process because there might not be any progress while it is detecting the deadlock.
To avoid the problem in the future, perform one of the following actions:
-
Increase the number of ITLs available. You can do so by changing the INITRANS setting for the table using the ALTER TABLE statement.
-
Set the commit_serialization parameter to DEPENDENT_TRANSACTIONS for the apply process.
-
Set the parallelism apply process parameter to 1 for the apply process.
Is an Apply Server Performing Poorly for Certain Transactions?
If an apply process is not performing well, then the reason might be that one or more apply servers used by the apply process are taking an inordinate amount of time to apply certain transactions. The following query displays information about the transactions being applied by each apply server used by an apply process named strm01_apply:
COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99999999
COLUMN STATE HEADING 'Apply Server State' FORMAT A20
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied Message|Number' FORMAT 99999999
COLUMN MESSAGE_SEQUENCE HEADING 'Message Sequence|Number' FORMAT 99999999
SELECT SERVER_ID, STATE, APPLIED_MESSAGE_NUMBER, MESSAGE_SEQUENCE
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'STRM01_APPLY'
ORDER BY SERVER_ID;
If you run this query repeatedly, then over time the apply server state, applied message number, and message sequence number should continue to change for each apply server as it applies transactions. If these values do not change for one or more apply servers, then the apply server might not be performing well. In this case, you should ensure that, for each table to which the apply process applies changes, every key column has an index.
If you have many such tables, then you might need to determine the specific table and DML or DDL operation that is causing an apply server to perform poorly. To do so, run the following query when an apply server is taking an inordinately long time to apply a transaction. In this example, assume that the name of the apply process is strm01_apply and that apply server number two is performing poorly:
COLUMN OPERATION HEADING 'Operation' FORMAT A20
COLUMN OPTIONS HEADING 'Options' FORMAT A20
COLUMN OBJECT_OWNER HEADING 'Object|Owner' FORMAT A10
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
COLUMN COST HEADING 'Cost' FORMAT 99999999
SELECT p.OPERATION, p.OPTIONS, p.OBJECT_OWNER, p.OBJECT_NAME, p.COST
FROM V$SQL_PLAN p, V$SESSION s, V$STREAMS_APPLY_SERVER a
WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
AND s.SID = a.SID
AND p.HASH_VALUE = s.SQL_HASH_VALUE;
This query returns the operation being performed currently by the specified apply server. The query also returns the owner and name of the table on which the operation is being performed and the cost of the operation. Ensure that each key column in this table has an index. If the results show FULL for the COST column, then the operation is causing full table scans, and indexing the table's key columns might solve the problem.
In addition, you can run the following query to determine the specific DML or DDL SQL statement that is causing an apply server to perform poorly, assuming that the name of the apply process is strm01_apply and that apply server number two is performing poorly:
SELECT t.SQL_TEXT
FROM V$SESSION s, V$SQLTEXT t, V$STREAMS_APPLY_SERVER a
WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
AND s.SID = a.SID
AND s.SQL_ADDRESS = t.ADDRESS
AND s.SQL_HASH_VALUE = t.HASH_VALUE
ORDER BY PIECE;
This query returns the SQL statement being run currently by the specified apply server. The statement includes the name of the table to which the transaction is being applied. Ensure that each key column in this table has an index.
If the SQL statement returned by the previous query is less than one thousand characters long, then you can run the following simplified query instead:
SELECT t.SQL_TEXT
FROM V$SESSION s, V$SQLAREA t, V$STREAMS_APPLY_SERVER a
WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
AND s.SID = a.SID
AND s.SQL_ADDRESS = t.ADDRESS
AND s.SQL_HASH_VALUE = t.HASH_VALUE;
Are There Any Apply Errors in the Error Queue?
When an apply process cannot apply a message, it moves the message and all of the other messages in the same transaction into the error queue. You should check for apply errors periodically to see if there are any transactions that could not be applied.
Using a DML Handler to Correct Error Transactions
When an apply process moves a transaction to the error queue, you can examine the transaction to analyze the feasibility reexecuting the transaction successfully. If an abnormality is found in the transaction, then you might be able to configure a statement DML handler or a procedure DML handler to correct the problem. In this case, configure the DML handler to run when you reexecute the error transaction.
When a DML handler is used to correct a problem in an error transaction, the apply process that uses the DML handler should be stopped to prevent the DML handler from acting on LCRs that are not involved with the error transaction. After successful reexecution, if the DML handler is no longer needed, then remove it. Also, correct the problem that caused the transaction to moved to the error queue to prevent future error transactions.
Troubleshooting Specific Apply Errors
You might encounter the following types of apply process errors for LCRs:
The errors marked with an asterisk (*) in the previous list often result from a problem with an apply handler or a rule-based transformation.
ORA-01031 Insufficient Privileges
An ORA-01031 error occurs when the user designated as the apply user does not have the necessary privileges to perform SQL operations on the replicated objects. The apply user privileges can be granted directly or through a role.
Specifically, the following privileges are required:
-
For table level DML changes, the INSERT, UPDATE, DELETE, and SELECT privileges must be granted.
-
For table level DDL changes, the ALTER TABLE privilege must be granted.
-
For schema level changes, the CREATE ANY TABLE, CREATE ANY INDEX, CREATE ANY PROCEDURE, ALTER ANY TABLE, and ALTER ANY PROCEDURE privileges must be granted.
-
For global level changes, ALL PRIVILEGES must be granted to the apply user.
To correct this error, complete the following steps:
-
Connect as the apply user on the destination database.
-
Query the SESSION_PRIVS data dictionary view to determine which required privileges are not granted to the apply user.
-
Connect as an administrative user who can grant privileges.
-
Grant the necessary privileges to the apply user.
-
Reexecute the error transactions in the error queue for the apply process.
ORA-01403 No Data Found
Typically, an ORA-01403 error occurs when an apply process tries to update an existing row and the OLD_VALUES in the row LCR do not match the current values at the destination database.
Typically, one of the following conditions causes this error:
-
Supplemental logging is not specified for columns that require supplemental logging at the source database. In this case, LCRs from the source database might not contain values for key columns. You can use a procedure DML handler to modify the LCR so that it contains the necessary supplemental data. See "Using a DML Handler to Correct Error Transactions". Also, specify the necessary supplemental logging at the source database to prevent future errors.
-
There is a problem with the primary key in the table for which an LCR is applying a change. In this case, ensure that the primary key is enabled by querying the DBA_CONSTRAINTS data dictionary view. If no primary key exists for the table, or if the target table has a different primary key than the source table, then specify substitute key columns using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. You also might encounter error ORA-23416 if a table being applied does not have a primary key. After you make these changes, you can reexecute the error transaction.
-
The transaction being applied depends on another transaction which has not yet executed. For example, if a transaction tries to update an employee with an employee_id of 300, but the row for this employee has not yet been inserted into the employees table, then the update fails. In this case, execute the transaction on which the error transaction depends. Then, reexecute the error transaction.
ORA-23605 Invalid Value for Oracle Streams Parameter
When calling row LCR (SYS.LCR$_ROW_RECORD type) member subprograms, an ORA-23605 error might be raised if the values of the parameters passed by the member subprogram do not match the row LCR. For example, an error results if a member subprogram tries to add an old column value to an insert row LCR, or if a member subprogram tries to set the value of a LOB column to a number.
Row LCRs should contain the following old and new values, depending on the operation:
-
A row LCR for an INSERT operation should contain new values but no old values.
-
A row LCR for an UPDATE operation can contain both new values and old values.
-
A row LCR for a DELETE operation should contain old values but no new values.
Verify that the correct parameter type (OLD, or NEW, or both) is specified for the row LCR operation (INSERT, UPDATE, or DELETE). For example, if a procedure DML handler or custom rule-based transformation changes an UPDATE row LCR into an INSERT row LCR, then the handler or transformation should remove the old values in the row LCR.
If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a custom rule-based transformation caused the error, then you might be able to create a DML handler to correct the problem. See "Using a DML Handler to Correct Error Transactions". Also, correct the rule-based transformation to avoid future errors.
ORA-23607 Invalid Column
An ORA-23607 error is raised by a row LCR (SYS.LCR$_ROW_RECORD type) member subprogram, when the value of the column_name parameter in the member subprogram does not match the name of any of the columns in the row LCR. Check the column names in the row LCR.
If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a custom rule-based transformation caused the error, then you might be able to create a DML handler to correct the problem. See "Using a DML Handler to Correct Error Transactions". Also, correct the rule-based transformation to avoid future errors.
An apply handler or custom rule-based transformation can cause this error by using one of the following row LCR member procedures:
-
DELETE_COLUMN, if this procedure tries to delete a column from a row LCR that does not exist in the row LCR
-
RENAME_COLUMN, if this procedure tries to rename a column that does not exist in the row LCR
In this case, to avoid similar errors in the future, perform one of the following actions:
-
Instead of using an apply handler or custom rule-based transformation to delete or rename a column in row LCRs, use a declarative rule-based transformation. If a declarative rule-based transformation tries to delete or rename a column that does not exist, then the declarative rule-based transformation does not raise an error. You can specify a declarative rule-based transformation that deletes a column using the DBMS_STREAMS_ADM.DELETE_COLUMN procedure and a declarative rule-based transformation that renames a column using the DBMS_STREAMS_ADM.RENAME_COLUMN procedure. You can use a declarative rule-based transformation in combination with apply handlers and custom rule-based transformations.
-
If you want to continue to use an apply handler or custom rule-based transformation to delete or rename a column in row LCRs, then modify the handler or transformation to prevent future errors. For example, modify the handler or transformation to verify that a column exists before trying to rename or delete the column.
ORA-24031 Invalid Value, parameter_name Should Be Non-NULL
An ORA-24031 error can occur when an apply handler or a custom rule-based transformation passes a NULL value to an LCR member subprogram instead of an ANYDATA value that contains a NULL.
For example, the following call to the ADD_COLUMN member procedure for row LCRs can result in this error:
new_lcr.ADD_COLUMN('OLD','LANGUAGE',NULL);
The following example shows the correct way to call the ADD_COLUMN member procedure for row LCRs:
new_lcr.ADD_COLUMN('OLD','LANGUAGE',ANYDATA.ConvertVarchar2(NULL));
If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a custom rule-based transformation caused the error, then you might be able to create a DML handler to correct the problem. See "Using a DML Handler to Correct Error Transactions". Also, correct the rule-based transformation to avoid future errors.
ORA-26687 Instantiation SCN Not Set
Typically, an ORA-26687 error occurs because the instantiation SCN is not set on an object for which an apply process is attempting to apply changes. You can query the DBA_APPLY_INSTANTIATED_OBJECTS data dictionary view to list the objects that have an instantiation SCN.
You can set an instantiation SCN for one or more objects by exporting the objects at the source database, and then importing them at the destination database. You can use Data Pump export/import. If you do not want to use export/import, then you can run one or more of the following procedures in the DBMS_APPLY_ADM package:
-
SET_TABLE_INSTANTIATION_SCN
-
SET_SCHEMA_INSTANTIATION_SCN
-
SET_GLOBAL_INSTANTIATION_SCN
Some of the common reasons why an instantiation SCN is not set for an object at a destination database include the following:
-
You used export/import for instantiation, and you exported the objects from the source database before preparing the objects for instantiation. You can prepare objects for instantiation either by creating Oracle Streams rules for the objects with the DBMS_STREAMS_ADM package or by running a procedure or function in the DBMS_CAPTURE_ADM package. If the objects were not prepared for instantiation before the export, then the instantiation SCN information will not be available in the export file, and the instantiation SCNs will not be set.
In this case, prepare the database objects for instantiation at the source database. Next, set the instantiation SCN for the database objects at the destination database.
-
Instead of using export/import for instantiation, you set the instantiation SCN explicitly with the appropriate procedure in the DBMS_APPLY_ADM package. When the instantiation SCN is set explicitly by the database administrator, responsibility for the correctness of the data is assumed by the administrator.
In this case, set the instantiation SCN for the database objects explicitly. Alternatively, you can choose to perform a metadata-only export/import to set the instantiation SCNs.
-
You want to apply DDL changes, but you did not set the instantiation SCN at the schema or global level.
In this case, set the instantiation SCN for the appropriate schemas by running the SET_SCHEMA_INSTANTIATION_SCN procedure, or set the instantiation SCN for the source database by running the SET_GLOBAL_INSTANTIATION_SCN procedure. Both of these procedures are in the DBMS_APPLY_ADM package.
After you correct the condition that caused the error, whether you should reexecute the error transaction or delete it depends on whether the changes included in the transaction were executed at the destination database when you corrected the error condition. Follow these guidelines when you decide whether you should reexecute the transaction in the error queue or delete it:
-
If you performed a new export/import, and the new export includes the transaction in the error queue, then delete the transaction in the error queue.
-
If you set instantiation SCNs explicitly or reimported an existing export dump file, then reexecute the transaction in the error queue.
ORA-26688 Missing Key in LCR
Typically, an ORA-26688 error occurs because of one of the following conditions:
-
At least one LCR in a transaction does not contain enough information for the apply process to apply it. For dependency computation, an apply process always needs values for the defined primary key column(s) at the destination database. Also, if the parallelism of any apply process that will apply the changes is greater than 1, then the apply process needs values for any indexed column at a destination database, which includes unique or non unique index columns, foreign key columns, and bitmap index columns.
If an apply process needs values for a column, and the column exists at the source database, then this error results when supplemental logging is not specified for one or more of these columns at the source database. In this case, specify the necessary supplemental logging at the source database to prevent apply errors.
However, the definition of the source database table might be different than the definition of the corresponding destination database table. If an apply process needs values for a column, and the column exists at the destination database but does not exist at the source database, then you can configure a rule-based transformation to add the required values to the LCRs from the source database to prevent apply errors.
To correct a transaction placed in the error queue because of this error, you can use a procedure DML handler to modify the LCRs so that they contain the necessary supplemental data. See "Using a DML Handler to Correct Error Transactions".
-
There is a problem with the primary key in the table for which an LCR is applying a change. In this case, ensure that the primary key is enabled by querying the DBA_CONSTRAINTS data dictionary view. If no primary key exists for the table, or if the destination table has a different primary key than the source table, then specify substitute key columns using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. You can also encounter error ORA-23416 if a table does not have a primary key. After you make these changes, you can reexecute the error transaction.
ORA-26689 Column Type Mismatch
Typically, an ORA-26689 error occurs because one or more columns at a table in the source database do not match the corresponding columns at the destination database. The LCRs from the source database might contain more columns than the table at the destination database, or there might be a column name or column type mismatch for one or more columns. If the columns differ at the databases, then you can use rule-based transformations to avoid future errors.
If you use an apply handler or a custom rule-based transformation, then ensure that any ANYDATA conversion functions match the data type in the LCR that is being converted. For example, if the column is specified as VARCHAR2, then use ANYDATA.CONVERTVARCHAR2 function to convert the data from type ANY to VARCHAR2.
Also, ensure that you use the correct character case in rule conditions, apply handlers, and rule-based transformations. For example, if a column name has all uppercase characters in the data dictionary, then you should specify the column name with all uppercase characters in rule conditions, apply handlers, and rule-based transformations
This error can also occur because supplemental logging is not specified where it is required for nonkey columns at the source database. In this case, LCRs from the source database might not contain needed values for these nonkey columns.
You might be able to configure a DML handler to apply the error transaction. See "Using a DML Handler to Correct Error Transactions".
ORA-26786 A row with key exists but has conflicting column(s) in table
An ORA-26786 error occurs when the values of some columns in the destination table row do not match the old values of the corresponding columns in the row LCR.
To avoid future apply errors, you can either configure a conflict handler, a DML handler, or an error handler. The handler should resolve the mismatched column in a way that is appropriate for your replication environment.
In addition, you might be able to configure a DML handler to apply existing error transactions that resulted from this error. See "Using a DML Handler to Correct Error Transactions".
Alternatively, you can update the current values in the row so that the row LCR can be applied successfully. If changes to the row are captured by a capture process or synchronous capture at the destination database, then you probably do not want to replicate this manual change to other destination databases. In this case, complete the following steps:
-
Set a tag in the session that corrects the row. Ensure that you set the tag to a value that prevents the manual change from being replicated. For example, the tag can prevent the change from being captured by a capture process or synchronous capture.
EXEC DBMS_STREAMS.SET_TA\G(tag => HEXTORAW('17'));
In some environments, you might need to set the tag to a different value.
-
Update the row in the table so that the data matches the old values in the LCR.
-
Reexecute the error or reexecute all errors. To reexecute an error, run the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package, and specify the transaction identifier for the transaction that caused the error. For example:
EXEC DBMS_APPLY_ADM.EXECUTE_ERROR(local_transaction_id => '5.4.312');
Or, execute all errors for the apply process by running the EXECUTE_ALL_ERRORS procedure:
EXEC DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(apply_name => 'APPLY');
-
If you are going to make other changes in the current session that you want to replicate destination databases, then reset the tag for the session to an appropriate value, as in the following example:
EXEC DBMS_STREAMS.SET_TAG(tag => NULL);
In some environments, you might need to set the tag to a value other than NULL.
ORA-26787 The row with key column_value does not exist in table table_name
An ORA-26787 error occurs when the row that a row LCR is trying to update or delete does not exist in the destination table.
To avoid future apply errors, you can either configure a conflict handler, a DML handler, or an error handler. The handler should resolve row LCRs that do not have corresponding table rows in a way that is appropriate for your replication environment.
In addition, you might be able to configure a DML handler to apply existing error transactions that resulted from this error. See "Using a DML Handler to Correct Error Transactions".
Alternatively, you can update the current values in the row so that the row LCR can be applied successfully. See "ORA-26786 A row with key exists but has conflicting column(s) in table" for instructions.
PK C PK &A OEBPS/strms_ipro.htm
35 Information Provisioning Concepts
Information provisioning makes information available when and where it is needed. Information provisioning is part of Oracle grid computing, which pools large numbers of servers, storage areas, and networks into a flexible, on-demand computing resource for enterprise computing needs. Information provisioning uses many of the features that also are used for information integration.
The following topics contain information about information provisioning:
Overview of Information Provisioning
Oracle grid computing enables resource provisioning with features such as Oracle Real Application Clusters (Oracle RAC), Oracle Scheduler, and Database Resource Manager. Oracle RAC enables you to provision hardware resources by running a single Oracle database server on a cluster of physical servers. Oracle Scheduler enables you to provision database workload over time for more efficient use of resources. Database Resource Manager provisions resources to database users, applications, or services within an Oracle database.
In addition to resource provisioning, Oracle grid computing also enables information provisioning. Information provisioning delivers information when and where it is needed, regardless of where the information currently resides on the grid. In a grid environment with distributed systems, the grid must move or copy information efficiently to make it available where it is needed.
Information provisioning can take the following forms:
-
Bulk Provisioning of Large Amounts of Information: Data Pump export/import, transportable tablespaces, the DBMS_STREAMS_TABLESPACE_ADM package, and the DBMS_FILE_TRANSFER package all are ways to provide large amounts of information. Data Pump export/import enables you to move or copy information at the database, tablespace, schema, or table level. Transportable tablespaces enables you to move or copy tablespaces from one database to another efficiently. The procedures in the DBMS_STREAMS_TABLESPACE_ADM package enable you to clone, detach, and attach tablespaces. In addition, some procedures in this package enable you to store tablespaces in a tablespace repository that provides versioning of tablespaces. When tablespaces are needed, they can be pulled from the tablespace repository and plugged into a database. The procedures in the DBMS_FILE_TRANSFER package enable you to copy a binary file within a database or between databases.
-
Incremental Information Provisioning with Oracle Streams: Some data must be shared as it is created or changed, rather than occasionally shared in bulk. Oracle Streams can stream data between databases, nodes, or blade farms in a grid and can keep two or more copies synchronized as updates are made.
-
On-Demand Information Access: You can make information available without moving or copying it to a new location. Oracle Distributed SQL allows grid users to access and integrate data stored in multiple Oracle databases and, through gateways, non-Oracle databases.
These information provisioning capabilities can be used individually or in combination to provide a full information provisioning solution in your environment. The remaining sections in this chapter discuss the ways to provision information in more detail.
Bulk Provisioning of Large Amounts of Information
Oracle provides several ways to move or copy large amounts of information from database to database efficiently. Data Pump can export and import at the database, tablespace, schema, or table level. There are several ways to move or copy a tablespace set from one Oracle database to another. Transportable tablespaces can move or copy a subset of an Oracle database and "plug" it in to another Oracle database. Transportable tablespace from backup with RMAN enables you to move or copy a tablespace set while the tablespaces remain online. The procedures in the DBMS_STREAMS_TABLESPACE_ADM package combine several steps that are required to move or copy a tablespace set into one procedure call.
Each method for moving or copying a tablespace set requires that the tablespace set is self-contained. A self-contained tablespace has no references from the tablespace pointing outside of the tablespace. For example, if an index in the tablespace is for a table in a different tablespace, then the tablespace is not self-contained. A self-contained tablespace set has no references from inside the set of tablespaces pointing outside of the set of tablespaces. For example, if a partitioned table is partially contained in the set of tablespaces, then the set of tablespaces is not self-contained. To determine whether a set of tablespaces is self-contained, use the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS.
The following sections describe the options for moving or copying large amounts of information and when to use each option:
Data Pump Export/Import
Data Pump export/import can move or copy data efficiently between databases. Data Pump can export/import a full database, tablespaces, schemas, or tables to provision large or small amounts of data for a particular requirement. Data Pump exports and imports can be performed using command line clients (expdp and impdp) or the DBMS_DATAPUMP package.
A transportable tablespaces export/import is specified using the TRANSPORT_TABLESPACES parameter. Transportable tablespaces enables you to unplug a set of tablespaces from a database, move or copy them to another location, and then plug them into another database. The transport is quick because the process transfers metadata and files. It does not unload and load the data. In transportable tablespaces mode, only the metadata for the tables (and their dependent objects) within a specified set of tablespaces are unloaded at the source and loaded at the target. This allows the tablespace data files to be copied to the target Oracle database and incorporated efficiently.
The tablespaces being transported can be either dictionary managed or locally managed. Moving or copying tablespaces using transportable tablespaces is faster than performing either an export/import or unload/load of the same data. To use transportable tablespaces, you must have the EXP_FULL_DATABASE and IMP_FULL_DATABASE role. The tablespaces being transported must be read-only during export, and the export cannot have a degree of parallelism greater than 1.
Transportable Tablespace from Backup with RMAN
The Recovery Manager (RMAN) TRANSPORT TABLESPACE command copies tablespaces without requiring that the tablespaces be in read-only mode during the transport process. Appropriate database backups must be available to perform RMAN transportable tablespace from backup.
DBMS_STREAMS_TABLESPACE_ADM Procedures
The following procedures in the DBMS_STREAMS_TABLESPACE_ADM package can move or copy tablespaces:
-
ATTACH_TABLESPACES: Uses Data Pump to import a self-contained tablespace set previously exported using the DBMS_STREAMS_TABLESPACE_ADM package, Data Pump export, or the RMAN TRANSPORT TABLESPACE command.
-
CLONE_TABLESPACES: Uses Data Pump export to clone a set of self-contained tablespaces. The tablespace set can be attached to a database after it is cloned. The tablespace set remains in the database from which it was cloned.
-
DETACH_TABLESPACES: Uses Data Pump export to detach a set of self-contained tablespaces. The tablespace set can be attached to a database after it is detached. The tablespace set is dropped from the database from which it was detached.
-
PULL_TABLESPACES: Uses Data Pump export/import to copy a set of self-contained tablespaces from a remote database and attach the tablespace set to the current database.
In addition, the DBMS_STREAMS_TABLESPACE_ADM package also contains the following procedures: ATTACH_SIMPLE_TABLESPACE, CLONE_SIMPLE_TABLESPACE, DETACH_SIMPLE_TABLESPACE, and PULL_SIMPLE_TABLESPACE. These procedures operate on a single tablespace that uses only one data file instead of a tablespace set.
File Group Repository
In the context of a file group, a file is a reference to a file stored on hard disk. A file is composed of a file name, a directory object, and a file type. The directory object references the directory in which the file is stored on hard disk. A version is a collection of related files, and a file group is a collection of versions.
A file group repository is a collection of all of the file groups in a database. A file group repository can contain multiple file groups and multiple versions of a particular file group.
For example, a file group named reports can store versions of sales reports. The reports can be generated on a regular schedule, and each version can contain the report files. The file group repository can version the file group under names such as sales_reports_v1, sales_reports_v2, and so on.
File group repositories can contain all types of files. You can create and manage file group repositories using the DBMS_FILE_GROUP package.
Tablespace Repository
A tablespace repository is a collection of tablespace sets in a file group repository. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. A file group repository can store versioned sets of files, including, but not restricted to, tablespace sets.
Different tablespace sets can be stored in a tablespace repository, and different versions of a particular tablespace set can also be stored. A version of a tablespace set in a tablespace repository consists of the following files:
-
The Data Pump export dump file for the tablespace set
-
The Data Pump log file for the export
-
The data files that comprise the tablespace set
All of the files in a version can reside in a single directory, or they can reside in different directories. The following procedures can move or copy tablespaces with or without using a tablespace repository:
-
ATTACH_TABLESPACES
-
CLONE_TABLESPACES
-
DETACH_TABLESPACES
If one of these procedures is run without using a tablespace repository, then a tablespace set is moved or copied, but it is not placed in or copied from a tablespace repository. If the CLONE_TABLESPACES or DETACH_TABLESPACES procedure is run using a tablespace repository, then the procedure places a tablespace set in the repository as a version of the tablespace set. If the ATTACH_TABLESPACES procedure is run using a tablespace repository, then the procedure copies a particular version of a tablespace set from the repository and attaches it to a database.
When to Use a Tablespace Repository
A tablespace repository is useful when you must store different versions of one or more tablespace sets. For example, a tablespace repository can accomplish the following goals:
-
You want to run quarterly reports on a tablespace set. You can clone the tablespace set quarterly for storage in a versioned tablespace repository, and a specific version of the tablespace set can be requested from the repository and attached to another database to run the reports.
-
You want applications to be able to attach required tablespace sets on demand in a grid environment. You can store multiple versions of several different tablespace sets in the tablespace repository. Each tablespace set can be used for a different purpose by the application. When the application needs a particular version of a particular tablespace set, the application can scan the tablespace repository and attach the correct tablespace set to a database.
Differences Between the Tablespace Repository Procedures
The procedures that include the file_group_name parameter in the DBMS_STREAMS_TABLESPACE_ADM package behave differently for the tablespace set, the data files in the tablespace set, and the export dump file. Table 35-1 describes these differences.
Remote Access to a Tablespace Repository
A tablespace repository can reside in the database that uses the tablespaces, or it can reside in a remote database. If it resides in a remote database, then a database link must be specified in the repository_db_link parameter when you run one of the procedures, and the database link must be accessible to the user who runs the procedure.
Only One Tablespace Version Can Be Online in a Database
A version of a tablespace set in a tablespace repository can be either online or offline in a database. A tablespace set version is online in a database when it is attached to the database using the ATTACH_TABLESPACES procedure. Only a single version of a tablespace set can be online in a database at a particular time. However, the same version or different versions of a tablespace set can be online in different databases at the same time. In this case, it might be necessary to ensure that only one database can make changes to the tablespace set.
Tablespace Repository Procedures Use the DBMS_FILE_GROUP Package Automatically
Although tablespace repositories are built on file group repositories, it is not necessary to use the DBMS_FILE_GROUP package to create a file group repository before using one of the procedures in the DBMS_STREAMS_TABLESPACE_ADM package. If you run the CLONE_TABLESPACES or DETACH_TABLESPACES procedure and specify a file group that does not exist, then the procedure creates the file group automatically.
A Tablespace Repository Provides Versioning but Not Source Control
A tablespace repository provides versioning of tablespace sets, but it does not provide source control. If two or more versions of a tablespace set are changed at the same time and placed in a tablespace repository, then these changes are not merged.
Read-Only Tablespaces Requirement During Export
The procedures in the DBMS_STREAMS_TABLESPACE_ADM package that perform a Data Pump export make any read/write tablespace being exported read-only. After the export is complete, if a procedure in the DBMS_STREAMS_TABLESPACE_ADM package made a tablespace read-only, then the procedure makes the tablespace read/write.
Automatic Platform Conversion for Tablespaces
When one of the procedures in the DBMS_STREAMS_TABLESPACE_ADM package moves or copies tablespaces to a database that is running on a different platform, the procedure can convert the data files to the appropriate platform if the conversion is supported. The V$TRANSPORTABLE_PLATFORM dynamic performance view lists all platforms that support cross-platform transportable tablespaces.
When a tablespace repository is used, the platform conversion is automatic if it is supported. When a tablespace repository is not used, you must specify the platform to which or from which the tablespace is being converted.
Options for Bulk Information Provisioning
Table 35-2 describes when to use each option for bulk information provisioning.
Incremental Information Provisioning with Oracle Streams
Oracle Streams can share and maintain database objects in different databases at each of the following levels:
-
Database
-
Schema
-
Table
-
Table subset
Oracle Streams can keep shared database objects synchronized at two or more databases. Specifically, an Oracle Streams capture process or synchronous capture captures changes to a shared database object in a source database, one or more propagations propagate the changes to another database, and an Oracle Streams apply process applies the changes to the shared database object. If database objects are not identical at different databases, then Oracle Streams can transform them at any point in the process. That is, a change can be transformed during capture, propagation, or apply. In addition, Oracle Streams provides custom processing of changes during apply with apply handlers. Database objects can be shared between Oracle databases, or they can be shared between Oracle and non-Oracle databases with an Oracle Database Gateway. In addition to data replication, Oracle Streams provides messaging, event management and notification, and data warehouse loading.
A combination of Oracle Streams and bulk provisioning enables you to copy and maintain a large amount of data by running a single procedure. The following procedures in the DBMS_STREAMS_ADM package use Data Pump to copy data between databases and configure Oracle Streams to maintain the copied data incrementally:
-
MAINTAIN_GLOBAL configures an Oracle Streams environment that replicates changes at the database level between two databases.
-
MAINTAIN_SCHEMAS configures an Oracle Streams environment that replicates changes to specified schemas between two databases.
-
MAINTAIN_SIMPLE_TTS clones a simple tablespace from a source database to a destination database and uses Oracle Streams to maintain this tablespace at both databases.
-
MAINTAIN_TABLES configures an Oracle Streams environment that replicates changes to specified tables between two databases.
-
MAINTAIN_TTS uses transportable tablespaces with Data Pump to clone a set of tablespaces from a source database to a destination database and uses Oracle Streams to maintain these tablespaces at both databases.
In addition, the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures configure an Oracle Streams environment that replicates changes either at the database level or to specified tablespaces between two databases. These procedures must be used together, and instantiation actions must be performed manually, to complete the Oracle Streams replication configuration.
Using these procedures, you can export data from one database, ship it to another database, reformat the data if the second database is on a different platform, import the data into the second database, and start syncing the data with the changes happening in the first database. If the second database is on a grid, then you have just migrated your application to a grid with one command.
These procedures can configure Oracle Streams clients to maintain changes originating at the source database in a single-source replication environment, or they can configure Oracle Streams clients to maintain changes originating at both databases in a bidirectional replication environment. By maintaining changes to the data, it can be kept synchronized at both databases. These procedures can either perform these actions directly, or they can generate one or more scripts that performs these actions.
On-Demand Information Access
Users and applications can access information without moving or copying it to a new location. Distributed SQL allows grid users to access and integrate data stored in multiple Oracle and, through Oracle Database Gateway, non-Oracle databases. Transparent remote data access with distributed SQL allows grid users to run their applications against any other database without making any code change to the applications. While integrating data and managing transactions across multiple data stores, the Oracle database optimizes the execution plans to access data in the most efficient manner.
PK7 - PK &A OEBPS/ap_strup.htm
What's New in Oracle Streams?
This section describes new features of Oracle Streams for Oracle Database 11g and provides pointers to additional information.
This section contains these topics:
Oracle Database 11g Release 2 (11.2) New Features in Oracle Streams
The following Oracle Streams features are new in Oracle Database 11g Release 2 (11.2):
XStream
XStream provides application programming interfaces (APIs) that enable information sharing between Oracle databases and between Oracle databases and other systems. The other systems include Oracle systems, such as Oracle Times Ten, non-Oracle databases, non-RDBMS Oracle products, file systems, third party software applications, and so on.
Statement DML Handlers
A new type of apply handler called a statement DML handler can process row LCRs in a customized way using a collection of SQL statements. Statement DML handlers typically perform better than procedure DML handlers because statement DML handlers require no PL/SQL processing.
Record Table Changes With Oracle Streams
The new MAINTAIN_CHANGE_TABLE procedure in the DBMS_STREAMS_ADM package makes it easy to configure an Oracle Streams environment that records the changes made to a table.
SQL Generation
SQL generation is the ability to generate the SQL statement required to perform the change encapsulated in a row logical change record (row LCR).
Oracle Streams Supports Compressed Tables
In prior releases of Oracle Database, Oracle Streams did not support the capture of changes to compressed tables. In Oracle Database 11g Release 2 (11.2) and later, Oracle Streams capture processes and synchronous captures can capture changes made to tables compressed using either basic table compression or OLTP table compression. In addition, apply processes can apply changes to compressed tables.
|
Note:
Capture processes can capture changes to compressed tables only if the compatibility level is set to 11.2.0 or higher at the source database. In a downstream capture configuration, the compatibility level must be set to 11.2.0 or higher at the database running the capture process also. Synchronous captures can capture changes to compressed tables only if the compatibility level is set to 11.2.0 or higher at the database. |
Capture Processes and Apply Processes Support SecureFile LOBs
In prior releases of Oracle Database, Oracle Streams did not support SecureFile LOBs. In Oracle Database 11g Release 2 (11.2) and later, Oracle Streams capture processes can capture changes made to SecureFile CLOB, NCLOB, and BLOB columns, and Oracle Streams apply processes can apply changes to SecureFile CLOB, NCLOB, and BLOB columns.
New Keep Columns Declarative Rule-Based Transformation
The keep columns declarative rule-based transformation keeps a list of columns in a row logical change record (LCR) that satisfies the specified rule. The transformation deletes columns that are not in the list from the row LCR. You specify a keep columns declarative rule-based transformation using the KEEP_COLUMNS procedure in the DBMS_STREAMS_ADM package.
Automatic Split and Merge
Two new capture process parameters can enable automatic split and merge: split_threshold and merge_theshold. When these parameters are set to specify automatic split and merge, Oracle Scheduler jobs monitor the streams flowing from a capture process. When an Oracle Scheduler job identifies a problem with a stream, the job submits a new Oracle Scheduler job to split the problem stream off from the other streams flowing from the capture process. Other Oracle Scheduler jobs continue to monitor the stream, and, when the problem is corrected, an Oracle Scheduler job merges the stream back with the other streams.
New Apply Process Parameter: txn_age_spill_threshold
The apply process begins to spill messages from memory to hard disk for a particular transaction when the amount of time that any message in the transaction has been in memory exceeds the specified number of seconds in the txn_age_spill_threshold parameter.
Monitoring Jobs
The new START_MONITORING procedure in the UTL_SPADV package can create a monitoring job that monitors Oracle Streams performance continually at specified intervals. Other new procedures in this package enable you to manage monitoring jobs.
New DBA_RECOVERABLE_SCRIPT_HIST View
The new DBA_RECOVERABLE_SCRIPT_HIST view stores the results of recovery operations that were performed by the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package.
Oracle Database 11g Release 1 (11.1) New Features in Oracle Streams
The following Oracle Streams features are new in Oracle Database 11g Release 1 (11.1):
Oracle Streams Topology and Oracle Streams Performance Advisor
The Oracle Streams topology identifies individual streams of messages and the Oracle Streams components configured in each stream. An Oracle Streams environment typically covers multiple databases, and the Oracle Streams topology provides a comprehensive view of the entire Oracle Streams environment.
The Oracle Streams Performance Advisor reports performance measurements for an Oracle Streams topology, including throughput and latency measurements. The Oracle Streams Performance Advisor also identifies bottlenecks in an Oracle Streams topology so that they can be corrected. In addition, the Oracle Streams Performance advisor examines the Oracle Streams components in an Oracle Streams topology and recommends ways to improve their performance.
Automatic Data Type Conversion During Apply
During apply, an apply process automatically converts certain data types when there is a mismatch between the data type of a column in the row logical change record (row LCR) and the data type of the corresponding column in a table.
Simplified Way to Restore Default Values for Parameters
You can set a capture process parameter to its default value by specifying NULL for the value of the parameter in the DBMS_CAPTURE_ADM.SET_PARAMETER procedure. Similarly, you can set an apply process parameter to its default value by specifying NULL for the value of the parameter in the DBMS_APPLY_ADM.SET_PARAMETER procedure.
Oracle Streams Supports Tables in a Flashback Data Archive
In prior releases of Oracle Database, Oracle Streams did not support the replication of changes to tables in a flashback data archive. In Oracle Database 11g Release 1 (11.1) and later, Oracle Streams supports tables in a flashback data archive.
Oracle Streams Supports Virtual Columns
In prior releases of Oracle Database, Oracle Streams did not support the replication of changes to tables with virtual columns. In Oracle Database 11g Release 1 (11.1) and later, Oracle Streams supports tables with virtual columns.
New Capture Process Parameter: skip_autofiltered_table_ddl
A new capture process parameter named skip_autofiltered_table_ddl enables you to capture data definition language (DDL) changes to database objects for which data manipulation language (DML) changes are automatically filtered.
New Apply Process Parameter: rtrim_on_implicit_conversion
A new apply process parameter named rtrim_on_implicit_conversion determines whether the apply process trims character data during automatic data type conversion.
Synchronous Capture
Synchronous capture is a new Oracle Streams client that captures data manipulation language (DML) changes made to tables immediately after the changes are committed.
Oracle Streams Support for XMLType Columns
XMLType is an Oracle-supplied type that you can use to store and query XML data in the database. Oracle Streams can capture, propagate, and apply changes to XMLType data.
Capture processes can capture changes to XMLType columns stored as CLOB columns, but capture processes cannot capture changes to XMLType columns stored object relationally or as binary XML. Apply processes can apply changes to XMLType columns stored as CLOB columns, stored object relationally, or stored as binary XML.
Oracle Streams Support for Transparent Data Encryption
Oracle Streams supports capturing, propagation, and applying changes to columns that have been encrypted using transparent data encryption. Oracle Streams supports columns that were encrypted at the column level or through tablespace encryption. Tablespace encryption enables you to encrypt an entire tablespace. All objects created in the encrypted tablespace are automatically encrypted, including all columns in the database objects in the tablespace. Once a column is encrypted, whether it is due to column encryption or tablespace encryption, Oracle Streams components handle the column data in the same way.
Split and Merge of a Stream Destination
You can easily split off an unavailable replica from a Streams replication configuration. Splitting the stream minimizes the time needed for the replica to "catch up" when it becomes available again. When the replica is caught up, it can be merged back into the original configuration. This feature uses three new procedures in the DBMS_STREAMS_ADM package: SPLIT_STREAMS, MERGE_STREAMS_JOB, and MERGE_STREAMS.
Track LCRs Through a Stream
The new SET_MESSAGE_TRACKING procedure in the DBMS_STREAMS_ADM package lets you specify a tracking label for logical change records (LCRs) generated by a database session. You can query the new V$STREAMS_MESSAGE_TRACKING view to track the LCRs through the stream and see how they were processed by each Oracle Streams client.
LCR tracking is useful if LCRs are not being applied as expected by one or more apply processes. When this happens, you can use LCR tracking to determine where the LCRs are stopping in the stream and address the problem at that location.
Also, the new message_tracking_frequency capture process parameter enables you to track LCRs automatically.
Compare and Converge Shared Database Objects
A new Oracle-supplied package called DBMS_COMPARISON enables you to compare the rows in a shared database object, such as a table, at two different databases. If differences are found in the database object, then this package can converge the database objects so that they are consistent.
Automated Alerts for Oracle Streams Clients and Thresholds
Enterprise Manager automatically alerts you when an Oracle Streams client becomes disabled or when Oracle Streams-related threshold that you have defined is crossed.
Oracle Streams Jobs Use Oracle Scheduler
In past releases, Oracle Streams used jobs created by the DBMS_JOB package to perform jobs such as propagation and event notification, and the JOB_QUEUE_PROCESSES initialization parameter controlled the number of slave processes that were created.
In Oracle Database 11g Release 1 (11.1) and* later, Oracle Streams uses Oracle Scheduler to perform these jobs. Oracle Scheduler automatically tunes the number of slave processes for these jobs based on the load on the computer system, and the JOB_QUEUE_PROCESSES initialization parameter is only used to specify the maximum number of slave processes. Therefore, the JOB_QUEUE_PROCESSES initialization parameter does not need to be set, unless you want to limit the number of slaves that can be created.
Notification Improvements
This release introduces the following notification improvements:
-
Notification grouping by time
-
Better scaling to enable a large number of notifications to be sent simultaneously
-
Improved diagnosability of notifications using registration statistics
New Error Messages for Easier Error Handling
The following apply error messages are new in Oracle Database 11g Release 1 (11.1):
-
An ORA-26787 error is raised if the row to be updated or deleted does not exist in the target table.
-
An ORA-26786 error is raised when the row exists in the target table, but the values of some columns do not match those of the row logical change record (row LCR).
In past releases, an ORA-01403 error was returned in these situations. These new error messages make it easier to handle apply errors in DML handlers and error handlers. If you have existing procedure handlers and error handlers, then they you might need to modify them for the current release.
Combined Capture and Apply
Oracle Streams can improve propagation efficiency under certain conditions.
PKߎ Վ PK &A OEBPS/strms_cpmon.htm
23 Monitoring the Oracle Streams Topology and Performance
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 Oracle Streams environment. The Oracle Streams topology includes information about the components in an Oracle Streams 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.
The following topics contain information about the Oracle Streams Performance Advisor:
About the Oracle Streams Topology
Oracle Streams enables you to send messages between multiple databases. An Oracle Streams environment can send the following types of messages:
The Oracle Streams topology is a representation of the databases in an Oracle Streams environment, the Oracle Streams components configured in these databases, and the flow of messages between these components.
The messages in the environment flow in separate stream paths. A stream path begins where a capture process, a synchronous capture, or an application generates messages and enqueues them. The messages can flow through one or more propagations and queues in its stream path. The stream path ends where the messages are dequeued by an apply process, a messaging client, or an application.
Currently, the Oracle Streams topology only gathers information about a stream path if the stream path ends with an apply process. The Oracle Streams topology does not track stream paths that end when a messaging client or an application dequeues messages.
About the Oracle Streams Performance Advisor
The Oracle Streams Performance Advisor consists of the DBMS_STREAMS_ADVISOR_ADM PL/SQL package and a collection of data dictionary views. You can use the ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package to gather information about the Oracle Streams topology and about the performance of the Oracle Streams components in the topology.
This section contains the following topics:
Oracle Streams Performance Advisor Data Dictionary Views
After information is gathered by the Oracle Streams Performance Advisor, you can view it by querying the following data dictionary views:
The topology information is stored permanently in the following data dictionary views: DBA_STREAMS_TP_DATABASE, DBA_STREAMS_TP_COMPONENT, and DBA_STREAMS_TP_COMPONENT_LINK.
The following views contain temporary information: DBA_STREAMS_TP_COMPONENT_STAT, DBA_STREAMS_TP_PATH_BOTTLENECK, and DBA_STREAMS_TP_PATH_STAT. Some of the data in these views is retained only for the user session that runs the ANALYZE_CURRENT_PERFORMANCE procedure. When this user session ends, this temporary information is purged.
Oracle Streams Components and Statistics
The DBMS_STREAMS_ADVISOR_ADM package gathers information about the following Oracle Streams components:
-
A QUEUE stores messages. The package gathers the following component-level statistics for queues:
-
ENQUEUE RATE
-
SPILL RATE
-
CURRENT QUEUE SIZE
-
A CAPTURE is a capture process. A capture process captures database changes in the redo log and enqueues the changes as logical change records (LCRs). Each capture process has the following subcomponents:
-
LOGMINER BUILDER is a builder server.
-
LOGMINER PREPARER is a preparer server.
-
LOGMINER READER is a reader server.
-
CAPTURE SESSION is the capture process session.
The package gathers the following component-level statistics for each capture process (CAPTURE):
-
CAPTURE RATE
-
ENQUEUE RATE
-
LATENCY
The package also gathers session-level statistics for capture process subcomponents.
-
A PROPAGATION SENDER sends messages from a source queue to a destination queue. The package gathers the following component-level statistics for propagation senders:
-
SEND RATE
-
BANDWIDTH
-
LATENCY
The package also gathers session-level statistics for propagation senders.
-
A PROPAGATION RECEIVER enqueues messages sent by propagation senders into a destination queue. The package gathers session-level statistics for propagation receivers.
-
An APPLY is an apply process. These components either apply messages directly or send messages to apply handlers. This type of component has the following subcomponents:
-
APPLY READER is a reader server.
-
APPLY COORDINATOR is a coordinator process.
-
APPLY SERVER is an apply server.
The package gathers the following component-level statistics for this component (APPLY):
-
MESSAGE APPLY RATE
-
TRANSACTION APPLY RATE
-
LATENCY
The package also gathers session-level statistics for the subcomponents.
When the package gathers session-level statistics for a component or subcomponent, the session-level statistics include the following:
About Stream Paths in an Oracle Streams Topology
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 dequeues the messages. The stream path might flow through multiple queues and propagations before it reaches an apply process. Therefore, a single stream path can consist of multiple source/destination component pairs before it reaches last component.
The Oracle Streams topology assigns a number to each stream path so that you can monitor each one easily. The Oracle Streams topology also assigns a number to each link between two components in a stream path. The number specifies the position of the link in the overall stream path.
Table 23-1 shows the position of each link in a sample stream path.
When the Oracle Streams Performance Advisor gathers information about an Oracle Streams environment, it tracks stream paths by starting with each apply process and working backward to its source. When a capture process is the source, the Oracle Streams Performance Advisor tracks the path from the apply process back to the capture process. When a synchronous capture or an application that enqueues messages is the source, the Oracle Streams Performance Advisor tracks the path from the apply process back to the queue into which the messages are enqueued.
The following sections describe sample replication environments and the stream paths in each one:
Separate Stream Paths in an Oracle Streams Environment
Consider an Oracle Streams environment with two databases. Each database captures changes made to the replicated database objects with a capture process and sends the changes to the other database, where they are applied by an apply process. The stream paths in this environment are completely separate.
Figure 23-1 shows an example of this type of Oracle Streams replication environment.
Notice that the Oracle Streams Performance Advisor assigns a component ID to each Oracle Streams component and a path ID to each path. The Oracle Streams topology in Figure 23-1 shows the following information:
-
There are twelve Oracle Streams components in the Oracle Streams environment.
-
There are two stream paths in the Oracle Streams environment.
-
Stream path 1 starts with component 1 and ends with component 6.
-
Stream path 2 starts with component 7 and ends with component 12.
Shared Stream Paths in an Oracle Streams Replication Environment
When there are multiple apply processes that apply changes generated by a single source, a stream path splits into multiple stream paths. In this case, part of a stream path is shared, but the path splits into two or more distinct stream paths.
Figure 23-2 shows this type of Oracle Streams environment.
The Oracle Streams topology in Figure 23-2 shows the following information:
-
There are ten Oracle Streams components in the Oracle Streams environment.
-
There are two stream paths in the Oracle Streams environment.
-
Stream path 1 starts with component 1 and ends with component 7.
-
Stream path 2 starts with component 1 and ends with component 10.
-
The messages flowing between component 1 and component 2 are in both path 1 and path2.
About the Information Gathered by the Oracle Streams Performance Advisor
The ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package gathers information about the Oracle Streams topology and the performance of Oracle Streams components. The procedure stores the information in a collection of data dictionary views. To use the Oracle Streams Performance Advisor effectively, it is important to understand how the procedure gathers information and calculates statistics.
The procedure takes snapshots of the Oracle Streams environment to gather information and calculate statistics. For some statistics, the information in a single snapshot is sufficient. For example, only one snapshot is needed to determine the current number of messages in a queue. However, to calculate other statistics, the procedure must compare two snapshots. These statistics include the rate, bandwidth, event, and flow control statistics. The first time the procedure is run in a user session, it takes two snapshots to calculate these statistics. In each subsequent run in the same user session, the procedure takes one snapshot and compares it with the snapshot taken during the previous run.
Table 23-2 illustrates how the procedure gathers information in each advisor run in a single user session.
For the best results in an advisor run, meet the following criteria:
-
Ensure that as many Oracle Streams components as possible are enabled during the time period between the two snapshots used in the advisor run. Specifically, capture processes, propagations, apply processes should be enabled, queues should be started, and database links should be active.
-
If data is replicated in the Oracle Streams environment, then ensure that the replicated database objects are experiencing an average, or near average, number of changes during the time period between the two snapshots used in the advisor run. The Oracle Streams Performance Advisor gathers more accurate statistics if it is run when the Oracle Streams replication environment is experiencing typical replication activity.
-
If messages are sent by applications in the Oracle Streams environment, then ensure that the applications are sending an average, or near average, number of messages during the time period between the two snapshots used in the advisor run. The Oracle Streams Performance Advisor gathers more accurate statistics if it is run when the Oracle Streams messaging environment is sending a typical number of messages.
Gathering Information About the Oracle Streams Topology and Performance
To gather information about the Oracle Streams topology and Oracle Streams performance, complete the following steps:
-
Identify the database that you will use to gather the information. An administrative user at this database must meet the following requirements:
-
The user must have access to a database link to each database that contains Oracle Streams components.
-
The user must have been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure, and each database link must connect to a user at the remote database that has been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure.
If you configure an Oracle Streams administrator at each database with Oracle Streams components, then the Oracle Streams administrator has the necessary privileges. See Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administrator.
If no database in your environment meets these requirements, then choose a database, configure the necessary database links, and grant the necessary privileges to the users before proceeding.
The Oracle Streams Performance Advisor running on an Oracle Database 11g Release 2 (11.2) database can monitor Oracle Database 10g Release 2 (10.2) and later databases. It cannot monitor databases before release 10.2.
-
In SQL*Plus, connect to the database you identified in Step 1 as a user that meets the requirements listed in Step 1.
For example, connect to the hub.example.com database as the Oracle Streams administrator.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
-
Run the ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package:
exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
-
Optionally, rerun the ANALYZE_CURRENT_PERFORMANCE procedure one or more times in same session that ran the procedure in Step 3:
exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
-
Run the following query to identify the advisor run ID for the information gathered in Step 4:
SELECT DISTINCT ADVISOR_RUN_ID FROM DBA_STREAMS_TP_COMPONENT_STAT
ORDER BY ADVISOR_RUN_ID;
Your output is similar to the following:
ADVISOR_RUN_ID
--------------
1
2
The Oracle Streams Performance Advisor assigns an advisor run ID to the statistics for each run. Use the last value in the output for the advisor run ID in the queries in "Viewing Performance Statistics for Oracle Streams Components". In this example, use 2 for the advisor run ID in the queries.
Remember that the Oracle Streams Performance Advisor purges some of the performance statistics that it gathered when a user session ends. Therefore, run the performance statistics queries in the same session that ran the ANALYZE_CURRENT_PERFORMANCE procedure.
Complete these steps whenever you want to monitor the current performance of your Oracle Streams environment.
You should also run the ANALYZE_CURRENT_PERFORMANCE procedure when new Oracle Streams components are added to any database in the Oracle Streams environment. Running the procedure updates the Oracle Streams topology with information about any new components.
Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance
This section contains several queries that you can use to view your Oracle Streams topology and monitor the performance of your Oracle Streams components. The queries specify the views described in "About the Oracle Streams Topology".
The queries in this section can be run in any Oracle Stream environment. However, the output shown for these queries is based on the sample Oracle Streams replication environment shown in Figure 23-3.
The Oracle Database 2 Day + Data Replication and Integration Guide contains instructions for configuring the Oracle Streams replication environment shown in Figure 23-3. This environment contains both of the following types of stream paths:
This section contains the following topics:
Viewing the Oracle Streams Topology
To view the Oracle Streams topology, you must first gather information about the Oracle Streams environment using the DBMS_STREAMS_ADVISOR_ADM package. See "Gathering Information About the Oracle Streams Topology and Performance".
The following sections explain how to view different types of information in an Oracle Streams topology:
Viewing the Databases in the Oracle Streams Environment
You can view the following information about the databases in an Oracle Streams environment:
-
The global name of each database
-
The last time the Oracle Streams Performance Advisor was run at each database
-
The version number of each database
-
The compatibility level of each database
-
Whether each database has access to the Oracle Diagnostics Pack and Oracle Tuning Pack
To display this information, run the following query:
COLUMN GLOBAL_NAME HEADING 'Global Name' FORMAT A15
COLUMN LAST_QUERIED HEADING 'Last|Queried'
COLUMN VERSION HEADING 'Version' FORMAT A15
COLUMN COMPATIBILITY HEADING 'Compatibility' FORMAT A15
COLUMN MANAGEMENT_PACK_ACCESS HEADING 'Management Pack' FORMAT A20
SELECT GLOBAL_NAME, LAST_QUERIED, VERSION, COMPATIBILITY, MANAGEMENT_PACK_ACCESS
FROM DBA_STREAMS_TP_DATABASE;
The following output shows the databases in the Oracle Streams replication environment described in "Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance":
Last
Global Name Queried Version Compatibility Management Pack
--------------- --------- --------------- --------------- --------------------
HUB.EXAMPLE.COM 08-APR-08 11.1.0.7.0 11.1.0 DIAGNOSTIC+TUNING
SPOKE1.EXAMPLE. 08-APR-08 11.1.0.7.0 11.1.0 DIAGNOSTIC+TUNING
COM
SPOKE2.EXAMPLE. 08-APR-08 11.1.0.7.0 11.1.0 DIAGNOSTIC+TUNING
COM
This output shows the following information about the databases in the Oracle Streams environment:
-
The Global Name column shows that the global names of the databases are hub.example.com, spoke1.example.com, and spoke2.example.com.
-
The Last Queried column shows that the Oracle Streams Performance Advisor was last run on April 8, 2008 at each database.
-
The Version column shows that version of each database is 11.1.0.7.0.
-
The Compatibility column shows that the compatibility level of each database is 11.1.0.
-
The Management Pack column shows that each database has access to the Oracle Diagnostics Pack and Oracle Tuning Pack.
Viewing the Oracle Streams Components at Each Database
You can view the following information about the components in an Oracle Streams environment:
-
The component ID for each Oracle Streams component. The Oracle Streams topology assigns an ID number to each component and uses the number to track information about the component and about the stream path that flows through the component.
-
The name of the Oracle Streams component. For capture processes and apply processes, the query lists the name of each process. For queues, the query lists the name of each queue. For propagations, two Oracle Streams components are tracked in the Oracle Streams topology:
-
The name of a propagation sender is the source queue of the propagation and the destination queue and database to which the propagation sends messages. For example, a propagation sender with the strmadmin.source_hns source queue that sends messages to the strmadmin.destination_spoke1 destination queue at the spoke1.example.com database is shown in the following way:
"STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DESTINATION_SPOKE1"
@SPOKE1.EXAMPLE.COM
-
The name of a propagation receiver is the source queue and database from which the messages are sent and the destination queue for the propagation. For example, a propagation receiver that gets messages from the strmadmin.source_hns source queue at the hub.example.com database and enqueues them into the strmadmin.destination_spoke1 destination queue is shown in the following way:
"STRMADMIN"."SOURCE_HNS"@HUB.EXAMPLE.COM=>"STRMADMIN".
"DESTINATION_SPOKE1"
-
The type of the Oracle Streams component. The following types are possible:
-
CAPTURE for capture processes
-
QUEUE for queues
-
PROPAGATION SENDER for propagation senders
-
PROPAGATION RECEIVER for propagation receivers
-
APPLY for apply processes
-
The database that contains the component
To display this information, run the following query:
COLUMN COMPONENT_ID HEADING 'ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A43
COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A20
COLUMN COMPONENT_DB HEADING 'Database' FORMAT A10
SELECT COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB
FROM DBA_STREAMS_TP_COMPONENT
ORDER BY COMPONENT_ID;
The following output shows the components in the Oracle Streams replication environment described in "Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance":
ID Name Type Database
---- ------------------------------------------- -------------------- ----------
1 "STRMADMIN"."DESTINATION_SPOKE1" QUEUE HUB.EXAMPL
E.COM
2 "STRMADMIN"."DESTINATION_SPOKE2" QUEUE HUB.EXAMPL
E.COM
3 "STRMADMIN"."SOURCE_HNS" QUEUE HUB.EXAMPL
E.COM
4 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER HUB.EXAMPL
INATION_SPOKE1"@SPOKE1.EXAMPLE.COM E.COM
5 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER HUB.EXAMPL
INATION_SPOKE2"@SPOKE2.EXAMPLE.COM E.COM
6 "STRMADMIN"."SOURCE_HNS"@SPOKE1.EXAMPLE.COM PROPAGATION RECEIVER HUB.EXAMPL
=>"STRMADMIN"."DESTINATION_SPOKE1" E.COM
7 "STRMADMIN"."SOURCE_HNS"@SPOKE2.EXAMPLE.COM PROPAGATION RECEIVER HUB.EXAMPL
=>"STRMADMIN"."DESTINATION_SPOKE2" E.COM
8 APPLY_SPOKE1 APPLY HUB.EXAMPL
E.COM
9 APPLY_SPOKE2 APPLY HUB.EXAMPL
E.COM
10 CAPTURE_HNS CAPTURE HUB.EXAMPL
E.COM
11 "STRMADMIN"."DESTINATION_SPOKE1" QUEUE SPOKE1.EXA
MPLE.COM
12 "STRMADMIN"."SOURCE_HNS" QUEUE SPOKE1.EXA
MPLE.COM
13 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER SPOKE1.EXA
INATION_SPOKE1"@HUB.EXAMPLE.COM MPLE.COM
14 "STRMADMIN"."SOURCE_HNS"@HUB.EXAMPLE.COM=>" PROPAGATION RECEIVER SPOKE1.EXA
STRMADMIN"."DESTINATION_SPOKE1" MPLE.COM
15 APPLY_SPOKE1 APPLY SPOKE1.EXA
MPLE.COM
16 CAPTURE_HNS CAPTURE SPOKE1.EXA
MPLE.COM
17 "STRMADMIN"."DESTINATION_SPOKE2" QUEUE SPOKE2.EXA
MPLE.COM
18 "STRMADMIN"."SOURCE_HNS" QUEUE SPOKE2.EXA
MPLE.COM
19 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER SPOKE2.EXA
INATION_SPOKE2"@HUB.EXAMPLE.COM MPLE.COM
20 "STRMADMIN"."SOURCE_HNS"@HUB.EXAMPLE.COM=>" PROPAGATION RECEIVER SPOKE2.EXA
STRMADMIN"."DESTINATION_SPOKE2" MPLE.COM
21 APPLY_SPOKE2 APPLY SPOKE2.EXA
MPLE.COM
22 CAPTURE_HNS CAPTURE SPOKE2.EXA
MPLE.COM
Viewing Each Stream Path in an Oracle Streams Topology
You can view the following information about the stream paths in an Oracle Streams topology:
-
The path ID. The Oracle Streams topology assigns an ID number to each stream path it identifies. The path ID is associated with each link in the path. For example, a single path ID can be associated with the following component links:
-
Capture process to queue
-
Queue to propagation sender
-
Propagation sender to propagation receiver
-
Propagation receiver to queue
-
Queue to apply process
-
The source component ID. A source component is a component from which messages flow to another component.
-
The name of the source component. See "Viewing the Oracle Streams Components at Each Database" for information about how components are named in the query output.
-
The destination component ID. A destination component receives messages from another component.
-
The name of the destination component.
-
The position in the stream path shows the location of a particular link in a path. For example, a position might be the first link in a path, the second link in a path, and so on.
To display this information, run the following query:
COLUMN PATH_ID HEADING 'Path|ID' FORMAT 9999
COLUMN SOURCE_COMPONENT_ID HEADING 'Source|Component|ID' FORMAT 9999
COLUMN SOURCE_COMPONENT_NAME HEADING 'Source|Component|Name' FORMAT A20
COLUMN DESTINATION_COMPONENT_ID HEADING 'Dest|Component|ID' FORMAT 9999
COLUMN DESTINATION_COMPONENT_NAME HEADING 'Dest|Component|Name' FORMAT A15
COLUMN POSITION HEADING 'Position' FORMAT 9999
SELECT PATH_ID,
SOURCE_COMPONENT_ID,
SOURCE_COMPONENT_NAME,
DESTINATION_COMPONENT_ID,
DESTINATION_COMPONENT_NAME,
POSITION
FROM DBA_STREAMS_TP_COMPONENT_LINK
ORDER BY PATH_ID, POSITION;
The following output shows the paths in the Oracle Streams topology for the components listed in "Viewing the Oracle Streams Components at Each Database":
Source Source Dest Dest
Path Component Component Component Component
ID ID Name ID Name Position
----- --------- -------------------- --------- --------------- --------
1 16 CAPTURE_HNS 12 "STRMADMIN"."SO 1
URCE_HNS"
1 12 "STRMADMIN"."SOURCE_ 13 "STRMADMIN"."SO 2
HNS" URCE_HNS"=>"STR
MADMIN"."DESTIN
ATION_SPOKE1"@H
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ 6 "STRMADMIN"."SO 3
HNS"=>"STRMADMIN"."D URCE_HNS"@SPOKE
ESTINATION_SPOKE1"@H 1.EXAMPLE.COM=>
UB.EXAMPLE.COM "STRMADMIN"."DES
TINATION_SPOKE1"
1 6 "STRMADMIN"."SOURCE_ 1 "STRMADMIN"."DE 4
HNS"@SPOKE1.EXAMPLE. STINATION_SPOKE
COM=>"STRMADMIN"."DE 1"
STINATION_SPOKE1"
1 1 "STRMADMIN"."DESTINA 8 APPLY_SPOKE1 5
TION_SPOKE1"
2 22 CAPTURE_HNS 18 "STRMADMIN"."SO 1
URCE_HNS"
2 18 "STRMADMIN"."SOURCE_ 19 "STRMADMIN"."SO 2
HNS" URCE_HNS"=>"STR
MADMIN"."DESTIN
ATION_SPOKE2"@H
UB.EXAMPLE.COM
2 19 "STRMADMIN"."SOURCE_ 7 "STRMADMIN"."SO 3
HNS"=>"STRMADMIN"."D URCE_HNS"@SPOKE
ESTINATION_SPOKE2"@H 2.EXAMPLE.COM=>
UB.EXAMPLE.COM "STRMADMIN"."DES
TINATION_SPOKE2"
2 7 "STRMADMIN"."SOURCE_ 2 "STRMADMIN"."DE 4
HNS"@SPOKE2.EXAMPLE. STINATION_SPOKE
COM=>"STRMADMIN"."DE 2"
STINATION_SPOKE2"
2 2 "STRMADMIN"."DESTINA 9 APPLY_SPOKE2 5
TION_SPOKE2"
3 10 CAPTURE_HNS 3 "STRMADMIN"."SO 1
URCE_HNS"
3 3 "STRMADMIN"."SOURCE_ 4 "STRMADMIN"."SO 2
HNS" URCE_HNS"=>"STR
MADMIN"."DESTIN
ATION_SPOKE1"@S
POKE1.EXAMPLE.CO
M
3 4 "STRMADMIN"."SOURCE_ 14 "STRMADMIN"."SO 3
HNS"=>"STRMADMIN"."D URCE_HNS"@HUB.N
ESTINATION_SPOKE1"@S ET=>"STRMADMIN"
POKE1.EXAMPLE.COM ."DESTINATION_S
POKE1"
3 14 "STRMADMIN"."SOURCE_ 11 "STRMADMIN"."DE 4
HNS"@HUB.EXAMPLE.COM STINATION_SPOKE
=>"STRMADMIN"."DESTI 1"
NATION_SPOKE1"
3 11 "STRMADMIN"."DESTINA 15 APPLY_SPOKE1 5
TION_SPOKE1"
4 10 CAPTURE_HNS 3 "STRMADMIN"."SO 1
URCE_HNS"
4 3 "STRMADMIN"."SOURCE_ 5 "STRMADMIN"."SO 2
HNS" URCE_HNS"=>"STR
MADMIN"."DESTIN
ATION_SPOKE2"@S
POKE2.EXAMPLE.C
OM
4 5 "STRMADMIN"."SOURCE_ 20 "STRMADMIN"."SO 3
HNS"=>"STRMADMIN"."D URCE_HNS"@HUB.N
ESTINATION_SPOKE2"@S ET=>"STRMADMIN"
POKE2.EXAMPLE.COM ."DESTINATION_S
POKE2"
4 20 "STRMADMIN"."SOURCE_ 17 "STRMADMIN"."DE 4
HNS"@HUB.EXAMPLE.COM STINATION_SPOKE
=>"STRMADMIN"."DESTI 2"
NATION_SPOKE2"
4 17 "STRMADMIN"."DESTINA 21 APPLY_SPOKE2 5
TION_SPOKE2"
Viewing Performance Statistics for Oracle Streams Components
The DBMS_STREAMS_ADVISOR_ADM package and the Oracle Streams topology views comprise the Oracle Streams Performance Advisor. The Oracle Streams topology views enable you to display and analyze performance statistics for the Oracle Streams components in your environment.
To view performance statistics for Oracle Streams components, you must first gather information about the Oracle Streams environment using the DBMS_STREAMS_ADVISOR_ADM package. See "Gathering Information About the Oracle Streams Topology and Performance".
The following sections explain how to view performance statistics for Oracle Streams components:
|
Note:
The performance of Oracle Streams components depends on several factors, including the computer equipment used in the environment and the speed of the network. |
Checking for Bottleneck Components in the Oracle Streams Topology
A bottleneck component is the busiest component or the component with the least amount of idle time. You can view the following information about the bottleneck components in an Oracle Streams environment:
-
The path ID of the path that includes the component.
-
The component ID for each Oracle Streams component. The Oracle Streams topology assigns an ID number to each component and uses the number to track information about the component and about the stream path that flows through the component.
-
The name of the Oracle Streams component. See "Viewing the Oracle Streams Components at Each Database" for information about how components are named in the query output.
-
The type of the Oracle Streams component. The following types are possible:
-
The database that contains the component
Run the following query to check for bottleneck components in your Oracle Streams environment:
COLUMN PATH_ID HEADING 'Path ID' FORMAT 999
COLUMN COMPONENT_ID HEADING 'Component ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A20
COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A20
COLUMN COMPONENT_DB HEADING 'Database' FORMAT A15
SELECT PATH_ID,
COMPONENT_ID,
COMPONENT_NAME,
COMPONENT_TYPE,
COMPONENT_DB
FROM DBA_STREAMS_TP_PATH_BOTTLENECK
WHERE BOTTLENECK_IDENTIFIED='YES' AND
ADVISOR_RUN_ID=2
ORDER BY PATH_ID, COMPONENT_ID;
This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.
The following output shows the bottleneck components for the components listed in "Viewing the Oracle Streams Components at Each Database":
Path ID Component ID Name Type Database
------- ------------ -------------------- -------------------- ---------------
1 6 "STRMADMIN"."SOURCE_ PROPAGATION RECEIVER HUB.EXAMPLE.COM
HNS"@SPOKE1.EXAMPLE.
COM=>"STRMADMIN"."DE
STINATION_SPOKE1"
3 10 CAPTURE_HNS CAPTURE HUB.EXAMPLE.COM
4 10 CAPTURE_HNS CAPTURE HUB.EXAMPLE.COM
If this query returns no results, then the Oracle Streams Performance Advisor did not identify any bottleneck components in your environment. However, if this query returns one or more bottleneck components, then check the status of these components. If they are disabled, then you can enable them. If the components are enabled, then you can examine the components to see if they can be modified to perform better.
In some cases, the Oracle Streams Performance Advisor cannot determine whether a component is a bottleneck component. To view these components, set BOTTLENECK_IDENTIFIED to 'NO' when you query the DBA_STREAMS_TP_PATH_BOTTLENECK view. The output for the ADVISOR_RUN_REASON column shows why the Oracle Streams Performance Advisor could not determine whether the component is a bottleneck component. The following reasons can be specified in the ADVISOR_RUN_REASON column output:
-
PRE-11.1 DATABASE EXISTS means that the component is in a stream path that includes a database before Oracle Database 11g Release 1. Bottleneck analysis is not performed on these components.
-
DIAGNOSTIC PACK REQUIRED means that the component is in a stream path that includes a database that does not have the Oracle Diagnostics Pack. Bottleneck analysis is not performed on these components.
-
NO BOTTLENECK IDENTIFIED means that either no bottleneck was identified in a stream path or that there might be more than one bottleneck component in the stream path.
Viewing Component-Level Statistics
You can view statistics for the Oracle Streams components in the Oracle Streams topology. The query in this section displays the following information for each component:
-
The ID of the path to which the component belongs
-
The name of the Oracle Streams component
-
The type of the Oracle Streams component. The following types are possible:
-
The statistic that was gathered for the component
-
The value and unit of the statistic. For example, a LATENCY statistic shows a number for the value and SECONDS for the unit. A TRANSACTION APPLY RATE statistic shows a number for the value and TRANSACTIONS PER SECOND for the unit.
The ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package gathers the statistics returned by the query in this section. Therefore, the statistics returned by the query were the current statistics when the procedure was run. The statistics are not updated automatically.
Table 23-3 describes each of the statistics that can be returned by the query in this section:
The following are general considerations for these performance statistics:
-
Regarding rate, bandwidth, and event statistics, the time period is calculated as the time difference between the two snapshots used by the ANALYZE_CURRENT_PERFORMANCE procedure in the same user session. See "About the Information Gathered by the Oracle Streams Performance Advisor" for information about the snapshots. When a user session ends, the rate, bandwidth, and event statistics are purged.
-
When a latency statistic is -1 seconds, the ANALYZE_CURRENT_PERFORMANCE procedure could not gather statistics for the component when it was run. In most cases, this result indicates that the component was disabled when the procedure was run. For example, if the LATENCY statistic for an apply process is -1, then the component was probably disabled when the ANALYZE_CURRENT_PERFORMANCE procedure was run.
To display performance statistics for the components in an Oracle Streams topology, run the following query:
COLUMN PATH_ID HEADING 'Path|ID' FORMAT 999
COLUMN COMPONENT_ID HEADING 'Component|ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A20
COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A12
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A15
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 99999999999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A15
SELECT DISTINCT
cp.PATH_ID,
cs.COMPONENT_ID,
cs.COMPONENT_NAME,
cs.COMPONENT_TYPE,
cs.STATISTIC_NAME,
cs.STATISTIC_VALUE,
cs.STATISTIC_UNIT
FROM DBA_STREAMS_TP_COMPONENT_STAT cs,
(SELECT PATH_ID, SOURCE_COMPONENT_ID AS COMPONENT_ID
FROM DBA_STREAMS_TP_COMPONENT_LINK
UNION
SELECT PATH_ID, DESTINATION_COMPONENT_ID AS COMPONENT_ID
FROM DBA_STREAMS_TP_COMPONENT_LINK) cp
WHERE cs.ADVISOR_RUN_ID = 2 AND
cs.SESSION_ID IS NULL AND
cs.SESSION_SERIAL# IS NULL AND
cs.COMPONENT_ID = cp.COMPONENT_ID
ORDER BY PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, STATISTIC_NAME;
This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.
The following output shows a partial list of the performance statistics for the components listed in "Viewing the Oracle Streams Components at Each Database". Specifically, the following output shows performance statistics for the components in stream path 1 and stream path 3:
Path Component
ID ID Name Type Statistic Value Unit
---- ---------- -------------------- ------------ --------------- --------------- ---------------
1 1 "STRMADMIN"."DESTINA QUEUE CURRENT QUEUE S .00 NUMBER OF MESSA
TION_SPOKE1" IZE GES
1 1 "STRMADMIN"."DESTINA QUEUE ENQUEUE RATE 2573.21 MESSAGES PER SE
TION_SPOKE1" COND
1 1 "STRMADMIN"."DESTINA QUEUE SPILL RATE .00 MESSAGES PER SE
TION_SPOKE1" COND
1 6 "STRMADMIN"."SOURCE_ PROPAGATION EVENT: CPU + Wa 32.55 PERCENT
HNS"@SPOKE1.EXAMPLE. RECEIVER it for CPU
COM=>"STRMADMIN"."DE
STINATION_SPOKE1"
1 6 "STRMADMIN"."SOURCE_ PROPAGATION EVENT: SQL*Net 23.62 PERCENT
HNS"@SPOKE1.EXAMPLE. RECEIVER more data from
COM=>"STRMADMIN"."DE client
STINATION_SPOKE1"
1 6 "STRMADMIN"."SOURCE_ PROPAGATION EVENT: latch: r 2.10 PERCENT
HNS"@SPOKE1.EXAMPLE. RECEIVER ow cache object
COM=>"STRMADMIN"."DE s
STINATION_SPOKE1"
1 8 APPLY_SPOKE1 APPLY EVENT: CPU + Wa 23.10 PERCENT
it for CPU
1 8 APPLY_SPOKE1 APPLY EVENT: latch: r 1.31 PERCENT
ow cache object
s
1 8 APPLY_SPOKE1 APPLY EVENT: latch: s 1.57 PERCENT
hared pool
1 8 APPLY_SPOKE1 APPLY LATENCY 2.13 SECONDS
1 8 APPLY_SPOKE1 APPLY MESSAGE APPLY R 10004.00 MESSAGES PER SE
ATE COND
1 8 APPLY_SPOKE1 APPLY TRANSACTION APP 100.00 TRANSACTIONS PE
LY RATE R SECOND
1 12 "STRMADMIN"."SOURCE_ QUEUE CURRENT QUEUE S .00 NUMBER OF MESSA
HNS" IZE GES
1 12 "STRMADMIN"."SOURCE_ QUEUE ENQUEUE RATE 9932.00 MESSAGES PER SE
HNS" COND
1 12 "STRMADMIN"."SOURCE_ QUEUE SPILL RATE .00 MESSAGES PER SE
HNS" COND
1 13 "STRMADMIN"."SOURCE_ PROPAGATION BANDWIDTH 32992.96 BYTES PER SECON
HNS"=>"STRMADMIN"."D SENDER D
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATION EVENT: CPU + Wa 35.96 PERCENT
HNS"=>"STRMADMIN"."D SENDER it for CPU
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATION EVENT: SQL*Net .26 PERCENT
HNS"=>"STRMADMIN"."D SENDER message to dbli
ESTINATION_SPOKE1"@H nk
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATION EVENT: latch: r .26 PERCENT
HNS"=>"STRMADMIN"."D SENDER ow cache object
ESTINATION_SPOKE1"@H s
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATION LATENCY 4.00 SECONDS
HNS"=>"STRMADMIN"."D SENDER
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATION SEND RATE 2568.00 MESSAGES PER SE
HNS"=>"STRMADMIN"."D SENDER COND
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 16 CAPTURE_HNS CAPTURE CAPTURE RATE 10464.00 MESSAGES PER SE
COND
1 16 CAPTURE_HNS CAPTURE ENQUEUE RATE 10002.00 MESSAGES PER SE
COND
1 16 CAPTURE_HNS CAPTURE EVENT: CPU + Wa 11.02 PERCENT
it for CPU
1 16 CAPTURE_HNS CAPTURE EVENT: CPU + Wa 35.96 PERCENT
it for CPU
1 16 CAPTURE_HNS CAPTURE EVENT: SQL*Net 5.51 PERCENT
message from db
link
1 16 CAPTURE_HNS CAPTURE LATENCY 2.65 SECONDS
.
.
.
|
Note:
This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions. |
You can analyze this output along with the output for the queries in "Viewing the Oracle Streams Components at Each Database" and "Viewing Each Stream Path in an Oracle Streams Topology".
Viewing Session-Level Statistics
You can view session-level statistics for the Oracle Streams components. The query in this section displays the following information for each session-level statistic:
-
The name of the Oracle Streams component
-
The type of the Oracle Streams component. The following types are possible:
-
The type of the subcomponent. Only capture processes, apply processes have subcomponents.
The following subcomponent types are possible for capture processes:
-
LOGMINER READER for a builder server of a capture process
-
LOGMINER PREPARER for a preparer server of a capture process
-
LOGMINER BUILDER for a reader server of a capture process
-
CAPTURE SESSION for a capture process session
The following subcomponent types are possible for apply processes:
-
PROPAGATION SENDER+RECEIVER for sending LCRs from a capture process directly to an apply process in a combined capture and apply optimization
-
APPLY READER for a reader server
-
APPLY COORDINATOR for a coordinator process
-
APPLY SERVER for a reader server
-
The statistic that was gathered for the component
-
The value and unit of the statistic. Session-level statistics show PERCENT for the unit. The value is the percentage of time spent either IDLE, paused for FLOW CONTROL, or waiting for an EVENT.
The ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package gathers the statistics returned by the query in this section. Therefore, the statistics returned by the query were the current statistics when the procedure was run. The statistics are not updated automatically.
Table 23-4 describes each of the statistics that can be returned by the query in this section:
Regarding flow control and event statistics, the time period is calculated as the time difference between the two snapshots used by the ANALYZE_CURRENT_PERFORMANCE procedure in the same user session. See "About the Information Gathered by the Oracle Streams Performance Advisor" for information about the snapshots. When a user session ends, the flow control and event statistics are purged.
To display session-level performance statistics for the components in an Oracle Streams topology, run the following query:
COLUMN PATH_ID HEADING 'Path|ID' FORMAT 999
COLUMN COMPONENT_ID HEADING 'Component|ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Component|Name' FORMAT A20
COLUMN COMPONENT_TYPE HEADING 'Component|Type' FORMAT A10
COLUMN SUB_COMPONENT_TYPE HEADING 'Subcomponent|Type' FORMAT A17
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A15
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A7
SELECT DISTINCT
cp.PATH_ID,
cs.COMPONENT_ID,
cs.COMPONENT_NAME,
cs.COMPONENT_TYPE,
cs.SUB_COMPONENT_TYPE,
cs.STATISTIC_NAME,
cs.STATISTIC_VALUE,
cs.STATISTIC_UNIT
FROM DBA_STREAMS_TP_COMPONENT_STAT cs,
(SELECT PATH_ID, SOURCE_COMPONENT_ID AS COMPONENT_ID
FROM DBA_STREAMS_TP_COMPONENT_LINK
UNION
SELECT PATH_ID, DESTINATION_COMPONENT_ID AS COMPONENT_ID
FROM DBA_STREAMS_TP_COMPONENT_LINK) cp
WHERE cs.ADVISOR_RUN_ID=2 AND
cs.SESSION_ID IS NOT NULL AND
cs.SESSION_SERIAL# IS NOT NULL AND
cs.COMPONENT_ID = cp.COMPONENT_ID
ORDER BY PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, STATISTIC_NAME;
This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.
The following output shows a partial list of the session-level performance statistics for the components listed in "Viewing the Oracle Streams Components at Each Database". Specifically, the following output shows session-level performance statistics for the components in stream path 1 and stream path 3:
Path Component Component Component Subcomponent
ID ID Name Type Type Statistic Value Unit
---- --------- -------------------- ---------- ----------------- --------------- ------- -------
1 6 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: CPU + Wa 32.55 PERCENT
HNS"@SPOKE1.EXAMPLE. N RECEIVER it for CPU
COM=>"STRMADMIN"."DE
STINATIO N_SPOKE1"
1 6 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: SQL*Net 23.62 PERCENT
HNS"@SPOKE1.EXAMPLE. N RECEIVER more data from
COM=>"STRMADMIN"."DE client
STINATION_SPOKE1"
1 6 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: latch: r 2.10 PERCENT
HNS"@SPOKE1.EXAMPLE. N RECEIVER ow cache object
COM=>"STRMADMIN"."DE s
STINATION_SPOKE1"
1 6 "STRMADMIN"."SOURCE_ PROPAGATIO FLOW CONTROL .89 PERCENT
HNS"@SPOKE1.EXAMPLE. N RECEIVER
COM=>"STRMADMIN"."DE
STINATION_SPOKE1"
1 6 "STRMADMIN"."SOURCE_ PROPAGATIO IDLE 36.61 PERCENT
HNS"@SPOKE1.EXAMPLE. N RECEIVER
COM=>"STRMADMIN"."DE
STINATION_SPOKE1"
1 8 APPLY_SPOKE1 APPLY APPLY READER EVENT: CPU + Wa .26 PERCENT
it for CPU
1 8 APPLY_SPOKE1 APPLY APPLY SERVER EVENT: CPU + Wa 23.10 PERCENT
it for CPU
1 8 APPLY_SPOKE1 APPLY APPLY SERVER EVENT: latch: r 1.31 PERCENT
ow cache object
s
1 8 APPLY_SPOKE1 APPLY APPLY READER EVENT: latch: s .26 PERCENT
hared pool
1 8 APPLY_SPOKE1 APPLY APPLY SERVER EVENT: latch: s 1.57 PERCENT
hared pool
1 8 APPLY_SPOKE1 APPLY APPLY COORDINATOR FLOW CONTROL .00 PERCENT
1 8 APPLY_SPOKE1 APPLY APPLY READER FLOW CONTROL 10.76 PERCENT
1 8 APPLY_SPOKE1 APPLY APPLY SERVER FLOW CONTROL .00 PERCENT
1 8 APPLY_SPOKE1 APPLY APPLY COORDINATOR IDLE 6.21 PERCENT
1 8 APPLY_SPOKE1 APPLY APPLY READER IDLE 9.24 PERCENT
1 8 APPLY_SPOKE1 APPLY APPLY SERVER IDLE 8.53 PERCENT
1 13 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: CPU + Wa 21.65 PERCENT
HNS"=>"STRMADMIN"."D N SENDER it for CPU
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: SQL*Net .26 PERCENT
HNS"=>"STRMADMIN"."D N SENDER message to dbli
ESTINATION_SPOKE1"@H nk
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: latch: r .26 PERCENT
HNS"=>"STRMADMIN"."D N SENDER ow cache object
ESTINATION_SPOKE1"@H s
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: latch: s .26 PERCENT
HNS"=>"STRMADMIN"."D N SENDER hared pool
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATIO FLOW CONTROL 7.37 PERCENT
HNS"=>"STRMADMIN"."D N SENDER
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 13 "STRMADMIN"."SOURCE_ PROPAGATIO IDLE 67.41 PERCENT
HNS"=>"STRMADMIN"."D N SENDER
ESTINATION_SPOKE1"@H
UB.EXAMPLE.COM
1 16 CAPTURE_HNS CAPTURE LOGMINER READER EVENT: ARCH wai .26 PERCENT
t on c/f tx acq
uire 2
1 16 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: CPU + Wa 35.96 PERCENT
it for CPU
1 16 CAPTURE_HNS CAPTURE LOGMINER BUILDER EVENT: CPU + Wa .26 PERCENT
it for CPU
1 16 CAPTURE_HNS CAPTURE LOGMINER PREPARER EVENT: CPU + Wa 11.02 PERCENT
it for CPU
1 16 CAPTURE_HNS CAPTURE LOGMINER READER EVENT: CPU + Wa .26 PERCENT
it for CPU
1 16 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: SQL*Net 5.51 PERCENT
message from db
link
1 16 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: SQL*Net .26 PERCENT
message to dbli
nk
1 16 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: latch: r .26 PERCENT
ow cache object
s
1 16 CAPTURE_HNS CAPTURE LOGMINER BUILDER EVENT: latch: r 1.84 PERCENT
ow cache object
s
1 16 CAPTURE_HNS CAPTURE LOGMINER PREPARER EVENT: latch: r .79 PERCENT
ow cache object
s
1 16 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: latch: s .26 PERCENT
hared pool
1 16 CAPTURE_HNS CAPTURE LOGMINER READER EVENT: latch: s .79 PERCENT
hared pool
1 16 CAPTURE_HNS CAPTURE CAPTURE SESSION FLOW CONTROL 16.27 PERCENT
1 16 CAPTURE_HNS CAPTURE LOGMINER BUILDER FLOW CONTROL .00 PERCENT
1 16 CAPTURE_HNS CAPTURE LOGMINER PREPARER FLOW CONTROL .00 PERCENT
1 16 CAPTURE_HNS CAPTURE LOGMINER READER FLOW CONTROL .00 PERCENT
1 16 CAPTURE_HNS CAPTURE CAPTURE SESSION IDLE 41.47 PERCENT
1 16 CAPTURE_HNS CAPTURE LOGMINER BUILDER IDLE 97.90 PERCENT
1 16 CAPTURE_HNS CAPTURE LOGMINER PREPARER IDLE 88.19 PERCENT
1 16 CAPTURE_HNS CAPTURE LOGMINER READER IDLE 98.69 PERCENT
.
.
.
3 4 "STRMADMIN"."SOURCE_ PROPAGATIO FLOW CONTROL 6.50 PERCENT
HNS"=>"STRMADMIN"."D N SENDER
ESTINATION_SPOKE1"@S
POKE1.EXAMPLE.COM
3 4 "STRMADMIN"."SOURCE_ PROPAGATIO IDLE 70.50 PERCENT
HNS"=>"STRMADMIN"."D N SENDER
ESTINATION_SPOKE1"@S
POKE1.EXAMPLE.COM
3 10 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: ARCH wai 52.23 PERCENT
t for archivelo
g lock
3 10 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: CPU + Wa 7.35 PERCENT
it for CPU
3 10 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: control .52 PERCENT
file sequential
read
3 10 CAPTURE_HNS CAPTURE CAPTURE SESSION FLOW CONTROL 4.24 PERCENT
3 10 CAPTURE_HNS CAPTURE CAPTURE SESSION IDLE 2.23 PERCENT
3 14 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: CPU + Wa 6.92 PERCENT
HNS"@HUB.EXAMPLE.COM N RECEIVER it for CPU
=>"STRMADMIN"."DESTI
NATION_SPOKE1"
3 14 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: latch: r 2.23 PERCENT
HNS"@HUB.EXAMPLE.COM N RECEIVER ow cache object
=>"STRMADMIN"."DESTI s
NATION_SPOKE1"
3 14 "STRMADMIN"."SOURCE_ PROPAGATIO EVENT: library 3.79 PERCENT
HNS"@HUB.EXAMPLE.COM N RECEIVER cache: mutex X
=>"STRMADMIN"."DESTI
NATION_SPOKE1"
3 14 "STRMADMIN"."SOURCE_ PROPAGATIO FLOW CONTROL .67 PERCENT
HNS"@HUB.EXAMPLE.COM N RECEIVER
=>"STRMADMIN"."DESTI
NATION_SPOKE1"
3 14 "STRMADMIN"."SOURCE_ PROPAGATIO IDLE 85.04 PERCENT
HNS"@HUB.EXAMPLE.COM N RECEIVER
=>"STRMADMIN"."DESTI
NATION_SPOKE1"
3 15 APPLY_SPOKE1 APPLY APPLY COORDINATOR EVENT: latch: r 4.20 PERCENT
ow cache object
s
3 15 APPLY_SPOKE1 APPLY APPLY COORDINATOR EVENT: latch: s .52 PERCENT
hared pool
3 15 APPLY_SPOKE1 APPLY APPLY READER EVENT: latch: s .26 PERCENT
hared pool
3 15 APPLY_SPOKE1 APPLY APPLY COORDINATOR FLOW CONTROL .00 PERCENT
3 15 APPLY_SPOKE1 APPLY APPLY READER FLOW CONTROL 1.56 PERCENT
3 15 APPLY_SPOKE1 APPLY APPLY SERVER FLOW CONTROL .00 PERCENT
3 15 APPLY_SPOKE1 APPLY APPLY COORDINATOR IDLE 87.28 PERCENT
3 15 APPLY_SPOKE1 APPLY APPLY READER IDLE 96.88 PERCENT
3 15 APPLY_SPOKE1 APPLY APPLY SERVER IDLE 91.29 PERCENT
|
Note:
-
This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions.
-
You can view the session ID and serial number for each session by adding the SESSION_ID and SESSION_SERIAL# columns to the query on the DBA_STREAMS_TP_COMPONENT_STAT view.
|
Viewing Statistics for the Stream Paths in an Oracle Streams Environment
The query in this section shows the following information for each stream path in the Oracle Streams topology:
-
Whether optimization mode for Oracle Streams is used for the path. When the OPTIMIZATION_MODE statistic is greater than 0 (zero) for a path, the path uses the combined capture and apply optimization. When the OPTIMIZATION_MODE statistic is 0 (zero) for a path, the path does not use the combined capture and apply optimization.
-
The MESSAGE RATE value is the average number of messages sent each second from the start of the path to the end of the path.
-
The TRANSACTION RATE value is the average number of transactions sent each second from the start of the path to the end of the path.
The time period for these statistics is calculated as the time difference between the two snapshots used by the ANALYZE_CURRENT_PERFORMANCE procedure in the same user session. See "About the Information Gathered by the Oracle Streams Performance Advisor" for information about the snapshots. When a user session ends, these statistics are purged.
To display this information, run the following query:
COLUMN PATH_ID HEADING 'Path ID' FORMAT 999
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A25
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 99999999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A25
SELECT PATH_ID,
STATISTIC_NAME,
STATISTIC_VALUE,
STATISTIC_UNIT
FROM DBA_STREAMS_TP_PATH_STAT
WHERE ADVISOR_RUN_ID=2
ORDER BY PATH_ID, STATISTIC_NAME;
This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.
The following output shows the path statistics for the stream paths listed in "Viewing Each Stream Path in an Oracle Streams Topology":
Path ID Statistic Value Unit
------- ------------------------- ------------ -------------------------
1 OPTIMIZATION_MODE 1.00 NUMBER
1 MESSAGE RATE 10004.00 MESSAGES PER SECOND
1 TRANSACTION RATE 100.00 TRANSACTIONS PER SECOND
2 OPTIMIZATION_MODE 1.00 NUMBER
2 MESSAGE RATE 10028.25 MESSAGES PER SECOND
2 TRANSACTION RATE 100.37 TRANSACTIONS PER SECOND
3 OPTIMIZATION_MODE 1.00 NUMBER
3 MESSAGE RATE 9623.20 MESSAGES PER SECOND
3 TRANSACTION RATE 97.10 TRANSACTIONS PER SECOND
4 OPTIMIZATION_MODE 1.00 NUMBER
4 MESSAGE RATE 10180.05 MESSAGES PER SECOND
4 TRANSACTION RATE 102.68 TRANSACTIONS PER SECOND
|
Note:
This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions. |
Using the UTL_SPADV Package
The UTL_SPADV package provides subprograms to collect and analyze statistics for the Oracle Streams components in a distributed database environment. The package uses the Oracle Streams Performance Advisor to gather statistics.
The COLLECT_STATS and START_MONITORING procedures use the Oracle Streams Performance Advisor to gather statistics about the Oracle Streams components and subcomponents in a distributed database environment. The SHOW_STATS procedure generates output that includes the statistics. The output is formatted so that it can be imported into a spreadsheet easily and analyzed.
You can use the COLLECT_STATS procedure to collect statistics each time the procedure is called. The comp_stat_table and path_stat_table parameters specify the tables that store the performance statistics. By default, these tables are STREAMS$_ADVISOR_COMP_STAT and STREAMS$_ADVISOR_PATH_STAT, respectively.
You can also use the START_MONITORING procedure to create a monitoring job that monitors Oracle Streams performance continually at specified intervals. The monitoring job uses the COLLECT_STATS procedure to collect statistics. The START_MONITORING procedure populates the STREAMS$_PA_MONITORING table, and the SHOW_STATS_TABLE column in this table specifies the table that contains the performance statistics. You can use the ALTER_MONITORING procedure to modify a monitoring job, and you can use the STOP_MONITORING procedure to stop a monitoring job.
These procedures collect the same statistics as the Oracle Streams Performance Advisor. These statistics are described in Table 23-3, "Component-Level Statistics for Oracle Streams Components" and Table 23-4, "Session-Level Statistics for Oracle Streams Components".
This section contains these topics:
Collecting Oracle Streams Statistics Using the UTL_SPADV Package
To collect statistics using the UTL_SPADV package, complete the following steps:
-
Identify the database that you will use to gather the information. An administrative user at this database must meet the following requirements:
-
The user must have access to a database link to each database that contains Oracle Streams components to monitor.
-
The user must have been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure, and each database link must connect to a user at the remote database that has been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure.
If you configure an Oracle Streams administrator at each database with Oracle Streams components, then the Oracle Streams administrator has the necessary privileges. See Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administrator.
If no database in your environment meets these requirements, then choose a database, configure the necessary database links, and grant the necessary privileges to the users before proceeding.
-
In SQL*Plus, connect to the database you identified in Step 1 as a user that meets the requirements listed in Step 1.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
-
Run the utlspadv.sql script in the rdbms/admin directory in ORACLE_HOME to load the UTL_SPADV package. For example:
@utlspadv.sql
-
Either collect the current Oracle Streams performance statistics once, or create a job that continually monitors Oracle Streams performance:
-
To collect the current Oracle Streams performance statistics once, run the COLLECT_STATS procedure:
exec UTL_SPADV.COLLECT_STATS
This example uses the default values for the parameters in the COLLECT_STATS procedure. Therefore, this example runs the Performance Advisor 10 times with 60 seconds between each run. These values correspond with the default values for the num_runs and interval parameters, respectively, in the COLLECT_STATS procedure.
-
To create a job that continually monitors Oracle Streams performance:
exec UTL_SPADV.START_MONITORING
This example creates a monitoring job, and the monitoring job gathers performance statistics continually at set intervals. This example uses the default values for the parameters in the START_MONITORING procedure. Therefore, this example runs the Performance Advisor every 60 seconds. This value corresponds with the default value for the interval parameter in the START_MONITORING procedure. If an interval is specified in the START_MONITORING procedure, then the specified interval is used for the interval parameter in the COLLECT_STATS procedure.
These procedures include several parameters that you can use to adjust the way performance statistics are gathered. See Oracle Database PL/SQL Packages and Types Reference for more information.
You can show the statistics by running the SHOW_STATS procedure. See "Showing Oracle Streams Statistics Using the UTL_SPADV Package".
Checking Whether an Oracle Streams Monitoring Job Is Currently Running
To check whether a monitoring job is running using the UTL_SPADV package, complete the following steps:
-
Connect to the database as the user who submitted the monitoring job.
-
Run the IS_MONITORING function. For example, to determine whether a monitoring job submitted by the current user with the full monitoring job name of STREAM$_MONITORING_JOB is running, enter the following:
SET SERVEROUTPUT ON
DECLARE
is_mon BOOLEAN;
BEGIN
is_mon := UTL_SPADV.IS_MONITORING(
job_name => 'STREAMS$_MONITORING_JOB',
client_name => NULL);
IF is_mon=TRUE THEN
DBMS_OUTPUT.PUT_LINE('The monitoring job is running.');
ELSE
DBMS_OUTPUT.PUT_LINE('No monitoring job was found.');
END IF;
END;
/
The output displays the following text if a monitoring job with the specified full monitoring job name is currently running:
The monitoring job is running.
The output displays the following text if no monitoring job with the specified full monitoring job name is currently running:
No monitoring job was found.
|
Note:
When you submit a monitoring job, the client name and job name are concatenated to form the full monitoring job name. The client name for a monitoring job submitted by Oracle Enterprise Manager is always EM. |
Altering an Oracle Streams Monitoring Job
To alter a monitoring job using the UTL_SPADV package, complete the following steps:
-
Create a monitoring job if you have not done so already by completing the steps described in "Collecting Oracle Streams Statistics Using the UTL_SPADV Package". Ensure that you run the START_MONITORING procedure in Step 4.
-
Connect to the database as the user who submitted the monitoring job. Only the user who submitted a monitoring job can alter the monitoring job, and each user can submit only one monitoring job at a time.
-
Run the ALTER_MONITORING procedure. The following example sets the interval for the monitoring job to 120 seconds:
BEGIN
UTL_SPADV.ALTER_MONITORING(
interval => 120);
END;
/
After running this procedure, the monitoring job gathers statistics every 120 seconds.
Stopping an Oracle Streams Monitoring Job
To stop a monitoring job using the UTL_SPADV package, complete the following steps:
-
Connect to the database as the user who submitted the monitoring job. Only the user who submitted a monitoring job can stop the monitoring job, and each user can submit only one monitoring job at a time.
-
Run the STOP_MONITORING procedure:
exec UTL_SPADV.STOP_MONITORING
The STOP_MONITORING procedure includes a purge parameter that you can use to purge the statistics gathered by the monitoring job from the result tables. By default, the purge parameter is set to FALSE, and the results are retained. Set the purge parameter to TRUE to purge the results.
Showing Oracle Streams Statistics Using the UTL_SPADV Package
The SHOW_STATS procedure displays the statistics that the Performance Advisor gathered and stored. Use the path_stat_table parameter to specify the table that contains the statistics.
When you gather statistics using the COLLECT_STATS procedure, this table is specified in the path_stat_table parameter in the COLLECT_STATS procedure. By default, the table name is STREAMS$_ADVISOR_PATH_STAT.
When you gather statistics using the START_MONITORING procedure, you can determine the name for this table by querying the SHOW_STATS_TABLE column in the STREAMS$_PA_MONITORING view. The default table for a monitoring job is STREAMS$_PA_SHOW_PATH_STAT.
To show statistics collected using the UTL_SPADV package and stored in the STREAMS$_ADVISOR_PATH_STAT table, complete the following steps:
-
Collect statistics by completing the steps described in "Collecting Oracle Streams Statistics Using the UTL_SPADV Package".
-
Connect to the database as the user who collected the statistics.
-
If you are using a monitoring job, then query the SHOW_STATS_TABLE column in the STREAMS$_PA_MONITORING view to determine the name of this table that stores the statistics:
SELECT SHOW_STATS_TABLE FROM STREAMS$_PA_MONITORING;
-
Run the SHOW_STATS procedure.
For example, if you are using a monitoring job and the default storage table, then run the following procedure:
SET SERVEROUTPUT ON SIZE 50000
BEGIN
UTL_SPADV.SHOW_STATS(
path_stat_table => 'STREAMS$_PA_SHOW_PATH_STAT');
END;
/
The output includes the following legend:
LEGEND
<statistics>= <capture> [ <queue> <psender> <preceiver> <queue> ] <apply>
<bottleneck>
<capture> = '|<C>' <name> <msgs captured/sec> <msgs enqueued/sec> <latency>
'LMR' <idl%> <flwctrl%> <topevt%> <topevt>
'LMP' (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>
'LMB' <idl%> <flwctrl%> <topevt%> <topevt>
'CAP' <idl%> <flwctrl%> <topevt%> <topevt>
'CAP+PS' <msgs sent/sec> <bytes sent/sec> <latency> <idl%>
<flwctrl%> <topevt%> <topevt>
<apply> = '|<A>' <name> <msgs applied/sec> <txns applied/sec> <latency>
'PS+PR' <idl%> <flwctrl%> <topevt%> <topevt>
'APR' <idl%> <flwctrl%> <topevt%> <topevt>
'APC' <idl%> <flwctrl%> <topevt%> <topevt>
'APS' (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>
<queue> = '|<Q>' <name> <msgs enqueued/sec> <msgs spilled/sec> <msgs in
queue>
<psender> = '|<PS>' <name> <msgs sent/sec> <bytes sent/sec> <latency> <idl%>
<flwctrl%> <topevt%> <topevt>
<preceiver> = '|<PR>' <name> <idl%> <flwctrl%> <topevt%> <topevt>
<bottleneck>= '|<B>' <name> <sub_name> <sessionid> <serial#> <topevt%> <topevt>
The following table describes the abbreviations used in the legend:
The following is sample output for when an apply process is the last component in a path:
OUTPUT
PATH 1 RUN_ID 3 RUN_TIME 2009-JUL-02 05:59:38 CCA Y
|<C> DB2$CAP 10267 10040 3 LMR 95% 0% 3.3% "" LMP (1) 86.7% 0% 11.7% "" LMB 86.7% 0% 11.7% ""
CAP 71.7% 16.7% 11.7% "" |<Q> "STRMADMIN"."DB2$CAPQ" 2540.45 0 30 |<PS>
=>DB1.EXAMPLE.COM 2152.03 32992.96 4 59.2% 9.8% 0% "" |<PR> DB2.EXAMPLE.COM=> 98.5%
0% 0.6% "" |<Q> "STRMADMIN"."DB2$APPQ" 3657.03 0.01 460 |<A> APPLY$_DB2_2 10042 100 4
APR 93.3% 0% 6.7% "" APC 98.1% 0% 1.8% "" APS (4) 370% 0% 6.1% "" |<B> NO BOTTLENECK
IDENTIFIED
PATH 1 RUN_ID 4 RUN_TIME 2009-JUL-02 06:01:39 CCA Y
|<C> DB2$CAP 10464 10002 3 LMR 95% 0% 1.7% "" LMP (1) 83.3% 0% 16.7% "" LMB 85% 0% 15% ""
CAP 62.9% 0% 35.7% "" |<Q> "STRMADMIN"."DB2$CAPQ" 2677.03 0.01 45 |<PS>
=>DB1.EXAMPLE.COM 2491.08 47883.46 4 65.5% 10.7% 0% "" |<PR> DB2.EXAMPLE.COM=> 0% 83.3%
13.3% "" |<Q> "STRMADMIN"."DB2$APPQ" 2444.03 0.01 0 |<A> APPLY$_DB2_2 10004 100 3
APR 42.9% 57.1% 0% "" APC 90% 0% 10% "" APS (4) 346% 0% 10.3% "" |<B> NO BOTTLENECK
IDENTIFIED
.
.
.
|
Note:
This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions. |
Use the legend and the abbreviations to determine the statistics in the output. For example, the following output is for the db2$cap capture process in path 1, run ID 3:
|<C> DB2$CAP 10267 10040 3 LMR 95% 0% 3.3% "" LMP (1) 86.7% 0% 11.7% "" LMB 86.7% 0% 11.7% ""
CAP 71.7% 16.7% 11.7% ""
This output shows the following statistics:
-
The capture process captured an average of 10267 database changes each second.
-
The capture process enqueued an average of 10040 messages each second.
-
The capture process latency was 3 seconds.
-
The reader server (LMR) used by the capture process spent 95% of its time idle.
-
The reader server used by the capture process spent 0% of its time in flow control mode.
-
The reader server used by the capture process spent 3.3% of its time on the top wait event.
-
The preparer server (LMP) parallelism was 1.
-
The preparer server used by the capture process spent 86.7% of its time idle.
-
The preparer server used by the capture process spent 0% of its time in flow control mode.
-
The preparer server used by the capture process spent 11.7% of its time on the top wait event.
-
The builder server (LMB) used by the capture process spent 86.7% of its time idle.
-
The builder server used by the capture process spent 0% of its time in flow control mode.
-
The builder server used by the capture process spent 11.7% of its time on the top wait event.
-
The capture process session spent 71.7% of its time idle.
-
The capture process session spent 16.7% of its time in flow control mode.
-
The capture process session spent 11.7% of its time on the top wait event.
PKUrH YH PK &A OEBPS/title.htm9
Oracle® Streams
Concepts and Administration
11g Release 2 (11.2)
E17069-07
August 2011
Oracle Streams Concepts and Administration, 11g Release 2 (11.2)
E17069-07
Copyright © 2002, 2011, Oracle and/or its affiliates. All rights reserved.
Primary Author: Randy Urbano
Contributors: Sundeep Abraham, Geeta Arora, Nimar Arora, Lance Ashdown, Ram Avudaiappan, Neerja Bhatt, Ragamayi Bhyravabhotla, Chipper Brown, Jack Chung, Alan Downing, Jacco Draaijer, Curt Elsbernd, Yong Feng, Jairaj Galagali, Lei Gao, Connie Green, Richard Huang, Thuvan Hoang, Lewis Kaplan, Joydip Kundu, Tianshu Li, Jing Liu, Edwina Lu, Raghu Mani, Rui Mao, Pat McElroy, Shailendra Mishra, Valarie Moore, Bhagat Nainani, Srikanth Nalla, Maria Pratt, Arvind Rajaram, Ashish Ray, Abhishek Saxena, Viv Schupmann, Vipul Shah, Neeraj Shodhan, Wayne Smith, Jim Stamos, Janet Stern, Mahesh Subramaniam, Bob Thome, Byron Wang, Wei Wang, James M. Wilson, Lik Wong, Jingwei Wu, Haobo Xu, Jun Yuan, David Zhang, Ying Zhang
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.
PK1; PK &A OEBPS/strms_fgmon.htm S
27 Monitoring Rules
The following topics describe monitoring rules, rule sets, and evaluation contexts:
|
Note:
The Oracle Streams tool in Oracle Enterprise Manager is also an excellent way to monitor an Oracle Streams environment. See the online Help for the Oracle Streams tool for more information. |
Displaying All Rules Used by All Oracle Streams Clients
Oracle Streams rules are created using the DBMS_STREAMS_ADM package or the Oracle Streams tool in Oracle Enterprise Manager. Oracle Streams rules in the rule sets for an Oracle Streams client determine the behavior of the Oracle Streams client. Oracle Streams clients include capture processes, propagations, apply processes, and messaging clients. The rule sets for an Oracle Streams client can also contain rules created using the DBMS_RULE_ADM package, and these rules also determine the behavior of the Oracle Streams client.
For example, if a rule in the positive rule set for a capture process evaluates to TRUE for DML changes to the hr.employees table, then the capture process captures DML changes to this table. However, if a rule in the negative rule set for a capture process evaluates to TRUE for DML changes to the hr.employees table, then the capture process discards DML changes to this table.
You query the following data dictionary views to display all rules in the rule sets for Oracle Streams clients, including Oracle Streams rules and rules created using the DBMS_RULE_ADM package:
-
ALL_STREAMS_RULES
-
DBA_STREAMS_RULES
In addition, these two views display the current rule condition for each rule and whether the rule condition has been modified.
The query in this section displays the following information about all of the rules used by Oracle Streams clients in a database:
-
The name of each Oracle Streams client that uses the rule
-
The type of each Oracle Streams client that uses the rule, either CAPTURE for a capture process, SYNCHRONOUS CAPTURE for a synchronous capture, PROPAGATION for a propagation, APPLY for an apply process, or DEQUEUE for a messaging client
-
The name of the rule
-
The type of rule set that contains the rule for the Oracle Streams client, either POSITIVE or NEGATIVE
-
For Oracle Streams rules, the Oracle Streams rule level, either GLOBAL, SCHEMA, or TABLE
-
For Oracle Streams rules, the name of the schema for schema rules and table rules
-
For Oracle Streams rules, the name of the table for table rules
-
For Oracle Streams rules, the rule type, either DML or DDL
Run the following query to display this information:
COLUMN STREAMS_NAME HEADING 'Oracle|Streams|Name' FORMAT A14
COLUMN STREAMS_TYPE HEADING 'Oracle|Streams|Type' FORMAT A11
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12
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_STREAMS_RULES;
Your output looks similar to the following:
Oracle
Oracle Oracle Streams
Streams Streams Rule Rule Set Rule Schema Object Rule
Name Type Name Type Level Name Name Type
-------------- ----------- ------------ -------- ------- ------ ----------- ----
STRM01_CAPTURE CAPTURE JOBS4 POSITIVE TABLE HR JOBS DML
STRM01_CAPTURE CAPTURE JOBS5 POSITIVE TABLE HR JOBS DDL
DBS1_TO_DBS2 PROPAGATION HR18 POSITIVE SCHEMA HR DDL
DBS1_TO_DBS2 PROPAGATION HR17 POSITIVE SCHEMA HR DML
APPLY APPLY HR20 POSITIVE SCHEMA HR DML
APPLY APPLY JOB_HISTORY2 NEGATIVE TABLE HR JOB_HISTORY DML
OE DEQUEUE RULE$_28 POSITIVE
This output provides the following information about the rules used by Oracle Streams clients in the database:
-
The DML rule jobs4 and the DDL rule jobs5 are both table rules for the hr.jobs table in the positive rule set for the capture process strm01_capture.
-
The DML rule hr17 and the DDL rule hr18 are both schema rules for the hr schema in the positive rule set for the propagation dbs1_to_dbs2.
-
The DML rule hr20 is a schema rule for the hr schema in the positive rule set for the apply process apply.
-
The DML rule job_history2 is a table rule for the hr schema in the negative rule set for the apply process apply.
-
The rule rule$_28 is a messaging rule in the positive rule set for the messaging client oe.
The ALL_STREAMS_RULES and DBA_STREAMS_RULES views also contain information about the rule sets used by an Oracle Streams client, the current and original rule condition for Oracle Streams rules, whether the rule condition has been changed, the subsetting operation and DML condition for each Oracle Streams subset rule, the source database specified for each Oracle Streams rule, and information about the message type and message variable for Oracle Streams messaging rules.
The following data dictionary views also display Oracle Streams rules:
These views display Oracle Streams rules only. They do not display any manual modifications to these rules made by the DBMS_RULE_ADM package, and they do not display rules created using the DBMS_RULE_ADM package. These views can display the original rule condition for each rule only. They do not display the current rule condition for a rule if the rule condition was modified after the rule was created.
Displaying the Oracle Streams Rules Used by a Specific Oracle Streams Client
To determine which rules are in a rule set used by a particular Oracle Streams client, you can query the DBA_STREAMS_RULES data dictionary view. For example, suppose a database is running an apply process named strm01_apply. The following sections describe how to determine the rules in the positive rule set and negative rule set for this apply process.
The following sections describe how to determine which rules are in a rule set used by a particular Oracle Streams client:
Displaying the Rules in the Positive Rule Set for an Oracle Streams Client
The following query displays all of the rules in the positive rule set for an apply processs named strm01_apply:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12
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
COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10
COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A9
SELECT RULE_OWNER,
RULE_NAME,
STREAMS_RULE_TYPE,
SCHEMA_NAME,
OBJECT_NAME,
RULE_TYPE,
SOURCE_DATABASE,
INCLUDE_TAGGED_LCR
FROM DBA_STREAMS_RULES
WHERE STREAMS_NAME = 'STRM01_APPLY' AND
RULE_SET_TYPE = 'POSITIVE';
If this query returns any rows, then the apply process applies LCRs containing changes that evaluate to TRUE for the rules.
Your output looks similar to the following:
Oracle Streams Apply
Rule Rule Schema Object Rule Tagged
Rule Owner Name Level Name Name Type Source LCRs?
---------- --------------- ------- ------ ----------- ---- ---------- ---------
STRMADMIN HR20 SCHEMA HR DML DBS1.EXAM NO
PLE.COM
STRMADMIN HR21 SCHEMA HR DDL DBS1.EXAM NO
PLE.COM
Assuming the rule conditions for the Oracle Streams rules returned by this query have not been modified, these results show that the apply process applies LCRs containing DML changes and DDL changes to the hr schema and that the LCRs originated at the dbs1.example.com database. The rules in the positive rule set that instruct the apply process to apply these LCRs are owned by the strmadmin user and are named hr20 and hr21. Also, the apply process applies an LCR that satisfies one of these rules only if the tag in the LCR is NULL.
If the rule condition for an Oracle Streams rule has been modified, then you must check the current rule condition to determine the effect of the rule on an Oracle Streams client. Oracle Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.
Displaying the Rules in the Negative Rule Set for an Oracle Streams Client
The following query displays all of the rules in the negative rule set for an apply process named strm01_apply:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A15
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
COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10
COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A9
SELECT RULE_OWNER,
RULE_NAME,
STREAMS_RULE_TYPE,
SCHEMA_NAME,
OBJECT_NAME,
RULE_TYPE,
SOURCE_DATABASE,
INCLUDE_TAGGED_LCR
FROM DBA_STREAMS_RULES
WHERE STREAMS_NAME = 'APPLY' AND
RULE_SET_TYPE = 'NEGATIVE';
If this query returns any rows, then the apply process discards LCRs containing changes that evaluate to TRUE for the rules.
Your output looks similar to the following:
Oracle Streams Apply
Rule Rule Schema Object Rule Tagged
Rule Owner Name Level Name Name Type Source LCRs?
---------- --------------- ------- ------ ----------- ---- ---------- ---------
STRMADMIN JOB_HISTORY22 TABLE HR JOB_HISTORY DML DBS1.EXAMP YES
LE.COM
STRMADMIN JOB_HISTORY23 TABLE HR JOB_HISTORY DDL DBS1.EXAMP YES
LE.COM
Assuming the rule conditions for the Oracle Streams rules returned by this query have not been modified, these results show that the apply process discards LCRs containing DML changes and DDL changes to the hr.job_history table and that the LCRs originated at the dbs1.example.com database. The rules in the negative rule set that instruct the apply process to discard these LCRs are owned by the strmadmin user and are named job_history22 and job_history23. Also, the apply process discards an LCR that satisfies one of these rules regardless of the value of the tag in the LCR.
If the rule condition for an Oracle Streams rule has been modified, then you must check the current rule condition to determine the effect of the rule on an Oracle Streams client. Oracle Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.
Displaying the Current Condition for a Rule
If you know the name of a rule, then you can display its rule condition. For example, consider the rule returned by the query in "Displaying the Oracle Streams Rules Used by a Specific Oracle Streams Client". The name of the rule is hr1, and you can display its condition by running the following query:
SET LONG 8000
SET PAGES 8000
SELECT RULE_CONDITION "Current Rule Condition"
FROM DBA_STREAMS_RULES
WHERE RULE_NAME = 'HR1' AND
RULE_OWNER = 'STRMADMIN';
Your output looks similar to the following:
Current Rule Condition
--------------------------------------------------------------------------------
((((:dml.get_object_owner() = 'HR') and :dml.get_source_database_name() = 'DA.EX
AMPLE.COM' )) and (:dml.get_compatible() <= dbms_streams.compatible_11_2))
Displaying Modified Rule Conditions for Oracle Streams Rules
It is possible to modify the rule condition of an Oracle Streams rule. These modifications can change the behavior of the Oracle Streams clients using the Oracle Streams rule. In addition, some modifications can degrade rule evaluation performance.
The following query displays the rule name, the original rule condition, and the current rule condition for each Oracle Streams rule whose condition has been modified:
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A12
COLUMN ORIGINAL_RULE_CONDITION HEADING 'Original Rule Condition' FORMAT A33
COLUMN RULE_CONDITION HEADING 'Current Rule Condition' FORMAT A33
SET LONG 8000
SET PAGES 8000
SELECT RULE_NAME, ORIGINAL_RULE_CONDITION, RULE_CONDITION
FROM DBA_STREAMS_RULES
WHERE SAME_RULE_CONDITION = 'NO';
Your output looks similar to the following:
Rule Name Original Rule Condition Current Rule Condition
------------ --------------------------------- ---------------------------------
HR20 ((:dml.get_object_owner() = 'HR') ((:dml.get_object_owner() = 'HR')
and :dml.is_null_tag() = 'Y' ) and :dml.is_null_tag() = 'Y' and
:dml.get_object_name() != 'JOB_H
ISTORY')
In this example, the output shows that the condition of the hr20 rule has been modified. Originally, this schema rule evaluated to TRUE for all changes to the hr schema. The current modified condition for this rule evaluates to TRUE for all changes to the hr schema, except for DML changes to the hr.job_history table.
|
Note:
The query in this section applies only to Oracle Streams rules. It does not apply to rules created using the DBMS_RULE_ADM package because these rules always show NULL for the ORIGINAL_RULE_CONDITION column and NULL for the SAME_RULE_CONDITION column. |
Displaying the Evaluation Context for Each Rule Set
The following query displays the default evaluation context for each rule set in a database:
COLUMN RULE_SET_OWNER HEADING 'Rule Set|Owner' FORMAT A10
COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
SELECT RULE_SET_OWNER,
RULE_SET_NAME,
RULE_SET_EVAL_CONTEXT_OWNER,
RULE_SET_EVAL_CONTEXT_NAME
FROM DBA_RULE_SETS;
Your output looks similar to the following:
Rule Set Eval Context
Owner Rule Set Name Owner Eval Context Name
---------- -------------------- ------------ ------------------------------
STRMADMIN RULESET$_2 SYS STREAMS$_EVALUATION_CONTEXT
STRMADMIN STRM02_QUEUE_R STRMADMIN AQ$_STRM02_QUEUE_TABLE_V
STRMADMIN APPLY_OE_RS STRMADMIN OE_EVAL_CONTEXT
STRMADMIN OE_QUEUE_R STRMADMIN AQ$_OE_QUEUE_TABLE_V
STRMADMIN AQ$_1_RE STRMADMIN AQ$_OE_QUEUE_TABLE_V
SUPPORT RS SUPPORT EVALCTX
OE NOTIFICATION_QUEUE_R OE AQ$_NOTIFICATION_QUEUE_TABLE_V
Displaying Information About the Tables Used by an Evaluation Context
The following query displays information about the tables used by an evaluation context named evalctx, which is owned by the support user:
COLUMN TABLE_ALIAS HEADING 'Table Alias' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A40
SELECT TABLE_ALIAS,
TABLE_NAME
FROM DBA_EVALUATION_CONTEXT_TABLES
WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND
EVALUATION_CONTEXT_NAME = 'EVALCTX';
Your output looks similar to the following:
Table Alias Table Name
-------------------- ----------------------------------------
PROB problems
Displaying Information About the Variables Used in an Evaluation Context
The following query displays information about the variables used by an evaluation context named evalctx, which is owned by the support user:
COLUMN VARIABLE_NAME HEADING 'Variable Name' FORMAT A15
COLUMN VARIABLE_TYPE HEADING 'Variable Type' FORMAT A15
COLUMN VARIABLE_VALUE_FUNCTION HEADING 'Variable Value|Function' FORMAT A20
COLUMN VARIABLE_METHOD_FUNCTION HEADING 'Variable Method|Function' FORMAT A20
SELECT VARIABLE_NAME,
VARIABLE_TYPE,
VARIABLE_VALUE_FUNCTION,
VARIABLE_METHOD_FUNCTION
FROM DBA_EVALUATION_CONTEXT_VARS
WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND
EVALUATION_CONTEXT_NAME = 'EVALCTX';
Your output looks similar to the following:
Variable Value Variable Method
Variable Name Variable Type Function Function
--------------- --------------- -------------------- --------------------
CURRENT_TIME DATE timefunc
Displaying All of the Rules in a Rule Set
The query in this section displays the following information about all of the rules in a rule set:
-
The owner of the rule.
-
The name of the rule.
-
The evaluation context for the rule, if any. If a rule does not have an evaluation context, and no evaluation context is specified in the ADD_RULE procedure when the rule is added to a rule set, then it inherits the evaluation context of the rule set.
-
The evaluation context owner, if the rule has an evaluation context.
For example, to display this information for each rule in a rule set named oe_queue_r that is owned by the user strmadmin, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
COLUMN RULE_EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A27
COLUMN RULE_EVALUATION_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A11
SELECT R.RULE_OWNER,
R.RULE_NAME,
R.RULE_EVALUATION_CONTEXT_NAME,
R.RULE_EVALUATION_CONTEXT_OWNER
FROM DBA_RULES R, DBA_RULE_SET_RULES RS
WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND
RS.RULE_SET_NAME = 'OE_QUEUE_R' AND
RS.RULE_NAME = R.RULE_NAME AND
RS.RULE_OWNER = R.RULE_OWNER;
Your output looks similar to the following:
Eval Contex
Rule Owner Rule Name Eval Context Name Owner
---------- -------------------- --------------------------- -----------
STRMADMIN HR1 STREAMS$_EVALUATION_CONTEXT SYS
STRMADMIN APPLY_LCRS STREAMS$_EVALUATION_CONTEXT SYS
STRMADMIN OE_QUEUE$3
STRMADMIN APPLY_ACTION
Displaying the Condition for Each Rule in a Rule Set
The following query displays the condition for each rule in a rule set named hr_queue_r that is owned by the user strmadmin:
SET LONGCHUNKSIZE 4000
SET LONG 4000
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A45
SELECT R.RULE_OWNER,
R.RULE_NAME,
R.RULE_CONDITION
FROM DBA_RULES R, DBA_RULE_SET_RULES RS
WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND
RS.RULE_SET_NAME = 'HR_QUEUE_R' AND
RS.RULE_NAME = R.RULE_NAME AND
RS.RULE_OWNER = R.RULE_OWNER;
Your output looks similar to the following:
Rule Owner Rule Name Rule Condition
--------------- --------------- ---------------------------------------------
STRMADMIN APPLY_ACTION hr.get_hr_action(tab.user_data) = 'APPLY'
STRMADMIN APPLY_LCRS :dml.get_object_owner() = 'HR' AND (:dml.get
_object_name() = 'DEPARTMENTS' OR
:dml.get_object_name() = 'EMPLOYEES')
STRMADMIN HR_QUEUE$3 hr.get_hr_action(tab.user_data) != 'APPLY'
Listing Each Rule that Contains a Specified Pattern in Its Condition
To list each rule in a database that contains a specified pattern in its condition, you can query the DBMS_RULES data dictionary view and use the DBMS_LOB.INSTR function to search for the pattern in the rule conditions. For example, the following query lists each rule that contains the pattern 'HR' in its condition:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A30
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A30
SELECT RULE_OWNER, RULE_NAME FROM DBA_RULES
WHERE DBMS_LOB.INSTR(RULE_CONDITION, 'HR', 1, 1) > 0;
Your output looks similar to the following:
Rule Owner Rule Name
------------------------------ ------------------------------
STRMADMIN DEPARTMENTS4
STRMADMIN DEPARTMENTS5
STRMADMIN DEPARTMENTS6
Displaying Aggregate Statistics for All Rule Set Evaluations
You can query the V$RULE_SET_AGGREGATE_STATS dynamic performance view to display statistics for all rule set evaluations since the database instance last started.
The query in this section contains the following information about rule set evaluations:
-
The number of rule set evaluations.
-
The number of rule set evaluations that were instructed to stop on the first hit.
-
The number of rule set evaluations that were instructed to evaluate only simple rules.
-
The number of times a rule set was evaluated without issuing any SQL. Generally, issuing SQL to evaluate rules is more expensive than evaluating rules without issuing SQL.
-
The number of centiseconds of CPU time used for rule set evaluation.
-
The number of centiseconds spent on rule set evaluation.
-
The number of SQL executions issued to evaluate a rule in a rule set.
-
The number of rule conditions processed during rule set evaluation.
-
The number of TRUE rules returned to the rules engine clients.
-
The number of MAYBE rules returned to the rules engine clients.
-
The number of times the following types of functions were called during rule set evaluation: variable value function, variable method function, and evaluation function.
Run the following query to display this information:
COLUMN NAME HEADING 'Name of Statistic' FORMAT A55
COLUMN VALUE HEADING 'Value' FORMAT 999999999
SELECT NAME, VALUE FROM V$RULE_SET_AGGREGATE_STATS;
Your output looks similar to the following:
Name of Statistic Value
------------------------------------------------------- ----------
rule set evaluations (all) 5584
rule set evaluations (first_hit) 5584
rule set evaluations (simple_rules_only) 3675
rule set evaluations (SQL free) 5584
rule set evaluation time (CPU) 179
rule set evaluation time (elapsed) 1053
rule set SQL executions 0
rule set conditions processed 11551
rule set true rules 10
rule set maybe rules 328
rule set user function calls (variable value function) 182
rule set user function calls (variable method function) 12794
rule set user function calls (evaluation function) 3857
|
Note:
A centisecond is one-hundredth of a second. So, for example, this output shows 1.79 seconds of CPU time and 10.53 seconds of elapsed time. |
Displaying Information About Evaluations for Each Rule Set
You can query the V$RULE_SET dynamic performance view to display information about evaluations for each rule set since the database instance last started. The query in this section contains the following information about each rule set in a database:
-
The owner of the rule set.
-
The name of the rule set.
-
The total number of evaluations of the rule set since the database instance last started.
-
The total number of times SQL was executed to evaluate rules since the database instance last started. Generally, issuing SQL to evaluate rules is more expensive than evaluating rules without issuing SQL.
-
The total number of evaluations on the rule set that did not issue SQL to evaluate rules since the database instance last started.
-
The total number of TRUE rules returned to the rules engine clients using the rule set since the database instance last started.
-
The total number of MAYBE rules returned to the rules engine clients using the rule set since the database instance last started.
Run the following query to display this information for each rule set in the database:
COLUMN OWNER HEADING 'Rule Set|Owner' FORMAT A9
COLUMN NAME HEADING 'Rule Set|Name' FORMAT A11
COLUMN EVALUATIONS HEADING 'Total|Evaluations' FORMAT 99999999
COLUMN SQL_EXECUTIONS HEADING 'SQL|Executions' FORMAT 99999999
COLUMN SQL_FREE_EVALUATIONS HEADING 'SQL Free|Evaluations' FORMAT 99999999
COLUMN TRUE_RULES HEADING 'True|Rules' FORMAT 999999999
COLUMN MAYBE_RULES HEADING 'Maybe|Rules' FORMAT 99999999
SELECT OWNER,
NAME,
EVALUATIONS,
SQL_EXECUTIONS,
SQL_FREE_EVALUATIONS,
TRUE_RULES,
MAYBE_RULES
FROM V$RULE_SET;
Your output looks similar to the following:
Rule Set Rule Set Total SQL SQL Free True Maybe
Owner Name Evaluations Executions Evaluations Rules Rules
--------- ----------- ----------- ---------- ----------- ---------- ---------
SYS ALERT_QUE_R 3 0 0 2 0
STRMADMIN RULESET$_4 86 0 0 43 1
STRMADMIN RULESET$_11 458 0 0 11 0
STRMADMIN RULESET$_9 87 0 0 1 42
STRMADMIN RULESET$_7 87 0 0 44 1
|
Note:
Querying the V$RULE_SET view can have a negative impact on performance if a database has a large library cache. |
Determining the Resources Used by Evaluation of Each Rule Set
You can query the V$RULE_SET dynamic performance view to determine the resources used by evaluation of a rule set since the database instance last started. If a rule set was evaluated more than one time since the database instance last started, then some statistics are cumulative, including statistics for the amount of CPU time, evaluation time, and shared memory bytes used.
The query in this section contains the following information about each rule set in a database:
-
The owner of the rule set
-
The name of the rule set
-
The total number of seconds of CPU time used to evaluate the rule set since the database instance last started
-
The total number of seconds used to evaluate the rule set since the database instance last started
-
The total number of shared memory bytes used to evaluate the rule set since the database instance last started
Run the following query to display this information for each rule set in the database:
COLUMN OWNER HEADING 'Rule Set|Owner' FORMAT A15
COLUMN NAME HEADING 'Rule Set Name' FORMAT A15
COLUMN CPU_SECONDS HEADING 'Seconds|of CPU|Time' FORMAT 999999.999
COLUMN ELAPSED_SECONDS HEADING 'Seconds of|Evaluation|Time' FORMAT 999999.999
COLUMN SHARABLE_MEM HEADING 'Bytes|of Shared|Memory' FORMAT 999999999
SELECT OWNER,
NAME,
(CPU_TIME/100) CPU_SECONDS,
(ELAPSED_TIME/100) ELAPSED_SECONDS,
SHARABLE_MEM
FROM V$RULE_SET;
Your output looks similar to the following:
Seconds Seconds of Bytes
Rule Set of CPU Evaluation of Shared
Owner Rule Set Name Time Time Memory
--------------- --------------- ----------- ----------- ----------
SYS ALERT_QUE_R .230 .490 25120
STRMADMIN RULESET$_4 .060 .970 25097
STRMADMIN RULESET$_11 .040 .030 25098
STRMADMIN RULESET$_9 .220 3.040 25505
STRMADMIN RULESET$_7 .040 .380 21313
|
Note:
Querying the V$RULE_SET view can have a negative impact on performance if a database has a large library cache. |
Displaying Evaluation Statistics for a Rule
You can query the V$RULE dynamic performance view to display evaluation statistics for a particular rule since the database instance last started. The query in this section contains the following information about each rule set in a database:
-
The total number of times the rule evaluated to TRUE since the database instance last started.
-
The total number of times the rule evaluated to MAYBE since the database instance last started.
-
The total number of evaluations on the rule that issued SQL since the database instance last started. Generally, issuing SQL to evaluate a rule is more expensive than evaluating the rule without issuing SQL.
For example, run the following query to display this information for the locations25 rule in the strmadmin schema:
COLUMN TRUE_HITS HEADING 'True Evaluations' FORMAT 99999999999
COLUMN MAYBE_HITS HEADING 'Maybe Evaluations' FORMAT 99999999999
COLUMN SQL_EVALUATIONS HEADING 'SQL Evaluations' FORMAT 99999999999
SELECT TRUE_HITS, MAYBE_HITS, SQL_EVALUATIONS
FROM V$RULE
WHERE RULE_OWNER = 'STRMADMIN' AND
RULE_NAME = 'LOCATIONS25';
Your output looks similar to the following:
True Evaluations Maybe Evaluations SQL Evaluations
---------------- ----------------- ---------------
1518 154 0
PK`y PK &A OEBPS/strms_qpmon.htm
11 Advanced Rule Concepts
The following topics contain information about rules.
The Components of a Rule
A rule is a database object that enables a client to perform an action when an event occurs and a condition is satisfied. A rule consists of the following components:
Each rule is specified as a condition that is similar to the condition in the WHERE clause of a SQL query. You can group related rules together into rule sets. A single rule can be in one rule set, multiple rule sets, or no rule sets.
Rule sets are evaluated by a rules engine, which is a built-in part of Oracle. Both user-created applications and Oracle features, such as Oracle Streams, can be clients of the rules engine.
|
Note:
A rule must be in a rule set for it to be evaluated. |
Rule Condition
A rule condition combines one or more expressions and conditions and returns a Boolean value, which is a value of TRUE, FALSE, or NULL (unknown). An expression is a combination of one or more values and operators that evaluate to a value. A value can be data in a table, data in variables, or data returned by a SQL function or a PL/SQL function. For example, the following expression includes only a single value:
salary
The following expression includes two values (salary and .1) and an operator (*):
salary * .1
The following condition consists of two expressions (salary and 3800) and a condition (=):
salary = 3800
This logical condition evaluates to TRUE for a given row when the salary column is 3800. Here, the value is data in the salary column of a table.
A single rule condition can include more than one condition combined with the AND, OR, and NOT logical conditions to a form compound condition. A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. For example, consider the following compound condition:
salary = 3800 OR job_title = 'Programmer'
This rule condition contains two conditions joined by the OR logical condition. If either condition evaluates to TRUE, then the rule condition evaluates to TRUE. If the logical condition were AND instead of OR, then both conditions must evaluate to TRUE for the entire rule condition to evaluate to TRUE.
Variables in Rule Conditions
Rule conditions can contain variables. When you use variables in rule conditions, precede each variable with a colon (:). The following is an example of a variable used in a rule condition:
:x = 55
Variables let you refer to data that is not stored in a table. A variable can also improve performance by replacing a commonly occurring expression. Performance can improve because, instead of evaluating the same expression multiple times, the variable is evaluated once.
A rule condition can also contain an evaluation of a call to a subprogram. Such a condition is evaluated in the same way as other conditions. That is, it evaluates to a value of TRUE, FALSE, or NULL (unknown). The following is an example of a condition that contains a call to a simple function named is_manager that determines whether an employee is a manager:
is_manager(employee_id) = 'Y'
Here, the value of employee_id is determined by data in a table where employee_id is a column.
You can use user-defined types for variables. Therefore, variables can have attributes. When a variable has attributes, each attribute contains partial data for the variable. In rule conditions, you specify attributes using dot notation. For example, the following condition evaluates to TRUE if the value of attribute z in variable y is 9:
:y.z = 9
|
Note:
A rule cannot have a NULL (or empty) rule condition. |
Simple Rule Conditions
A simple rule condition is a condition that has one of the following forms:
-
simple_rule_expression condition constant
-
constant condition simple_rule_expression
-
constant condition constant
Simple Rule Expressions
In a simple rule condition, a simple_rule_expression is one of the following:
-
Table column.
-
Variable.
-
Variable attribute.
-
Method result where the method either takes no arguments or constant arguments and the method result can be returned by the variable method function, so that the expression is one of the data types supported for simple rules. Such methods include LCR member subprograms that meet these requirements, such as GET_TAG, GET_VALUE, GET_COMPATIBLE, GET_EXTRA_ATTRIBUTE, and so on.
For table columns, variables, variable attributes, and method results, the following data types can be used in simple rule conditions:
Use of other data types in expressions results in nonsimple rule conditions.
Conditions
In a simple rule condition, a condition is one of the following:
-
<=
-
<
-
=
-
>
-
>=
-
!=
-
IS NULL
-
IS NOT NULL
Use of other conditions results in nonsimple rule conditions.
Constants
A constant is a fixed value. A constant can be:
-
A number, such as 12 or 5.4
-
A character, such as x or $
-
A character string, such as "this is a string"
Examples of Simple Rule Conditions
The following conditions are simple rule conditions, assuming the data types used in expressions are supported in simple rule conditions:
-
tab1.col = 5
-
tab2.col != 5
-
:v1 > 'aaa'
-
:v2.a1 < 10.01
-
:v3.m() = 10
-
:v4 IS NOT NULL
-
1 = 1
-
'abc' > 'AB'
-
:date_var < to_date('04-01-2004, 14:20:17', 'mm-dd-yyyy, hh24:mi:ss')
-
:adt_var.ts_attribute >= to_timestamp('04-01-2004, 14:20:17 PST', 'mm-dd-yyyy, hh24:mi:ss TZR')
-
:my_var.my_to_upper('abc') = 'ABC'
Rules with simple rule conditions are called simple rules. You can combine two or more simple conditions with the logical conditions AND and OR for a rule, and the rule remains simple. For example, rules with the following conditions are simple rules:
However, using the NOT logical condition in a rule condition causes the rule to be nonsimple.
Benefits of Simple Rules
Simple rules are important for the following reasons:
-
Simple rules are indexed by the rules engine internally.
-
Simple rules can be evaluated without executing SQL.
-
Simple rules can be evaluated with partial data.
When a client uses the DBMS_RULE.EVALUATE procedure to evaluate an event, the client can specify that only simple rules should be evaluated by specifying TRUE for the simple_rules_only parameter.
Rule Evaluation Context
An evaluation context is a database object that defines external data that can be referenced in rule conditions. The external data can exist as variables, table data, or both. The following analogy might be helpful: If the rule condition were the WHERE clause in a SQL query, then the external data in the evaluation context would be the tables and bind variables referenced in the FROM clause of the query. That is, the expressions in the rule condition should reference the tables, table aliases, and variables in the evaluation context to make a valid WHERE clause.
A rule evaluation context provides the necessary information for interpreting and evaluating the rule conditions that reference external data. For example, if a rule refers to a variable, then the information in the rule evaluation context must contain the variable type. Or, if a rule refers to a table alias, then the information in the evaluation context must define the table alias.
The objects referenced by a rule are determined by the rule evaluation context associated with it. The rule owner must have the necessary privileges to access these objects, such as SELECT privilege on tables, EXECUTE privilege on types, and so on. The rule condition is resolved in the schema that owns the evaluation context.
For example, consider a rule evaluation context named hr_evaluation_context that contains the following information:
The hr_evaluation_context rule evaluation context provides the necessary information for evaluating the following rule condition:
dep.location_id IN (:loc_id1, :loc_id2)
In this case, the rule condition evaluates to TRUE for a row in the hr.departments table if that row has a value in the location_id column that corresponds to either of the values passed in by the loc_id1 or loc_id2 variables. The rule cannot be interpreted or evaluated properly without the information in the hr_evaluation_context rule evaluation context. Also, notice that dot notation is used to specify the column location_id in the dep table alias.
|
Note:
Views are not supported as base tables in evaluation contexts. |
Explicit and Implicit Variables
The value of a variable referenced in a rule condition can be explicitly specified when the rule is evaluated, or the value of a variable can be implicitly available given the event.
Explicit variables are supplied by the caller at evaluation time. These values are specified by the variable_values parameter when the DBMS_RULE.EVALUATE procedure is run.
Implicit variables are not given a value supplied by the caller at evaluation time. The value of an implicit variable is obtained by calling the variable value function. You define this function when you specify the variable_types list during the creation of an evaluation context using the CREATE_EVALUATION_CONTEXT procedure in the DBMS_RULE_ADM package. If the value for an implicit variable is specified during evaluation, then the specified value overrides the value returned by the variable value function.
Specifically, the variable_types list is of type SYS.RE$VARIABLE_TYPE_LIST, which is a list of variables of type SYS.RE$VARIABLE_TYPE. Within each instance of SYS.RE$VARIABLE_TYPE in the list, the function used to determine the value of an implicit variable is specified as the variable_value_function attribute.
Whether variables are explicit or implicit is the choice of the designer of the application using the rules engine. The following are reasons for using an implicit variable:
-
The caller of the DBMS_RULE.EVALUATE procedure does not need to know anything about the variable, which can reduce the complexity of the application using the rules engine. For example, a variable can call a function that returns a value based on the data being evaluated.
-
The caller might not have EXECUTE privileges on the variable value function.
-
The caller of the DBMS_RULE.EVALUATE procedure does not know the variable value based on the event, which can improve security if the variable value contains confidential information.
-
The variable will be used infrequently, and the variable's value always can be derived if necessary. Making such variables implicit means that the caller of the DBMS_RULE.EVALUATE procedure does not need to specify many uncommon variables.
For example, in the following rule condition, the values of variable x and variable y could be specified explicitly, but the value of the variable max could be returned by running the max function:
:x = 4 AND :y < :max
Alternatively, variable x and y could be implicit variables, and variable max could be an explicit variable. So, there is no syntactic difference between explicit and implicit variables in the rule condition. You can determine whether a variable is explicit or implicit by querying the DBA_EVALUATION_CONTEXT_VARS data dictionary view. For explicit variables, the VARIABLE_VALUE_FUNCTION field is NULL. For implicit variables, this field contains the name of the function called by the implicit variable.
Evaluation Context Association with Rule Sets and Rules
To be evaluated, each rule must be associated with an evaluation context or must be part of a rule set that is associated with an evaluation context. A single evaluation context can be associated with multiple rules or rule sets. The following list describes which evaluation context is used when a rule is evaluated:
-
If an evaluation context is associated with a rule, then it is used for the rule whenever the rule is evaluated, and any evaluation context associated with the rule set being evaluated is ignored.
-
If a rule does not have an evaluation context, but an evaluation context was specified for the rule when it was added to a rule set using the ADD_RULE procedure in the DBMS_RULE_ADM package, then the evaluation context specified in the ADD_RULE procedure is used for the rule when the rule set is evaluated.
-
If no rule evaluation context is associated with a rule and none was specified by the ADD_RULE procedure, then the evaluation context of the rule set is used for the rule when the rule set is evaluated.
|
Note:
If a rule does not have an evaluation context, and you try to add it to a rule set that does not have an evaluation context, then an error is raised, unless you specify an evaluation context when you run the ADD_RULE procedure. |
Evaluation Function
You have the option of creating an evaluation function to be run with a rule evaluation context. You can use an evaluation function for the following reasons:
-
You want to bypass the rules engine and instead evaluate events using the evaluation function.
-
You want to filter events so that some events are evaluated by the evaluation function and other events are evaluated by the rules engine.
You associate a function with a rule evaluation context by specifying the function name for the evaluation_function parameter when you create the rule evaluation context with the CREATE_EVALUATION_CONTEXT procedure in the DBMS_RULE_ADM package. The rules engine invokes the evaluation function during the evaluation of any rule set that uses the evaluation context.
The DBMS_RULE.EVALUATE procedure is overloaded. The function must have each parameter in one of the DBMS_RULE.EVALUATE procedures, and the type of each parameter must be same as the type of the corresponding parameter in the DBMS_RULE.EVALUATE procedure, but the names of the parameters can be different.
An evaluation function has the following return values:
-
DBMS_RULE_ADM.EVALUATION_SUCCESS: The user specified evaluation function completed the rule set evaluation successfully. The rules engine returns the results of the evaluation obtained by the evaluation function to the rules engine client using the DBMS_RULE.EVALUATE procedure.
-
DBMS_RULE_ADM.EVALUATION_CONTINUE: The rules engine evaluates the rule set as if there were no evaluation function. The evaluation function is not used, and any results returned by the evaluation function are ignored.
-
DBMS_RULE_ADM.EVALUATION_FAILURE: The user-specified evaluation function failed. Rule set evaluation stops, and an error is raised.
If you always want to bypass the rules engine, then the evaluation function should return either EVALUATION_SUCCESS or EVALUATION_FAILURE. However, if you want to filter events so that some events are evaluated by the evaluation function and other events are evaluated by the rules engine, then the evaluation function can return all three return values, and it returns EVALUATION_CONTINUE when the rules engine should be used for evaluation.
If you specify an evaluation function for an evaluation context, then the evaluation function is run during evaluation when the evaluation context is used by a rule set or rule.
Rule Action Context
An action context contains optional information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated for an event. The client of the rules engine can be a user-created application or an internal feature of Oracle, such as Oracle Streams. Each rule has only one action context. The information in an action context is of type SYS.RE$NV_LIST, which is a type that contains an array of name-value pairs.
The rule action context information provides a context for the action taken by a client of the rules engine when a rule evaluates to TRUE or MAYBE. The rules engine does not interpret the action context. Instead, it returns the action context, and a client of the rules engine can interpret the action context information.
For example, suppose an event is defined as the addition of a new employee to a company. If the employee information is stored in the hr.employees table, then the event occurs whenever a row is inserted into this table. The company wants to specify that several actions are taken when a new employee is added, but the actions depend on which department the employee joins. One of these actions is that the employee is registered for a course relating to the department.
In this scenario, the company can create a rule for each department with an appropriate action context. Here, an action context returned when a rule evaluates to TRUE specifies the number of a course that an employee should take. Here are parts of the rule conditions and the action contexts for three departments:
These action contexts return the following instructions to the client application:
-
The action context for the rule_dep_10 rule instructs the client application to enroll the new employee in course number 1057.
-
The action context for the rule_dep_20 rule instructs the client application to enroll the new employee in course number 1215.
-
The NULL action context for the rule_dep_30 rule instructs the client application not to enroll the new employee in any course.
Each action context can contain zero or more name-value pairs. If an action context contains more than one name-value pair, then each name in the list must be unique. In this example, the client application to which the rules engine returns the action context registers the new employee in the course with the returned course number. The client application does not register the employee for a course if a NULL action context is returned or if the action context does not contain a course number.
If multiple clients use the same rule, or if you want an action context to return more than one name-value pair, then you can list more than one name-value pair in an action context. For example, suppose the company also adds a new employee to a department electronic mailing list. In this case, the action context for the rule_dep_10 rule might contain two name-value pairs:
The following are considerations for names in name-value pairs:
-
If different applications use the same action context, then use different names or prefixes of names to avoid naming conflicts.
-
Do not use $ and # in names because they can cause conflicts with Oracle-supplied action context names.
You add a name-value pair to an action context using the ADD_PAIR member procedure of the RE$NV_LIST type. You remove a name-value pair from an action context using the REMOVE_PAIR member procedure of the RE$NV_LIST type. If you want to modify an existing name-value pair in an action context, then you should first remove it using the REMOVE_PAIR member procedure and then add an appropriate name-value pair using the ADD_PAIR member procedure.
|
Note:
Oracle Streams uses action contexts for custom rule-based transformations and, when subset rules are specified, for internal transformations that might be required on LCRs containing UPDATE operations. Oracle Streams also uses action contexts to specify a destination queue into which an apply process enqueues messages that satisfy the rule. In addition, Oracle Streams uses action contexts to specify whether a message that satisfies an apply process rule is executed by the apply process. |
Rule Set Evaluation
The rules engine evaluates rule sets against an event. An event is an occurrence that is defined by the client of the rules engine. The client initiates evaluation of an event by calling the DBMS_RULE.EVALUATE procedure. This procedure enables the client to send some information about the event to the rules engine for evaluation against a rule set. The event itself can have more information than the information that the client sends to the rules engine.
The following information is specified by the client when it calls the DBMS_RULE.EVALUATE procedure:
-
The name of the rule set that contains the rules to use to evaluate the event.
-
The evaluation context to use for evaluation. Only rules that use the specified evaluation context are evaluated.
-
Table values and variable values. The table values contain rowids that refer to the data in table rows, and the variable values contain the data for explicit variables. Values specified for implicit variables override the values that might be obtained using a variable value function. If a specified variable has attributes, then the client can send a value for the entire variable, or the client can send values for any number of the attributes of the variable. However, clients cannot specify attribute values if the value of the entire variable is specified.
-
An optional event context. An event context is a varray of type SYS.RE$NV_LIST that contains name-value pairs that contain information about the event. This optional information is not used directly or interpreted by the rules engine. Instead, it is passed to client callbacks, such as an evaluation function, a variable value function (for implicit variables), and a variable method function.
The client can also send other information about how to evaluate an event against the rule set using the DBMS_RULE.EVALUATE procedure. For example, the caller can specify if evaluation must stop as soon as the first TRUE rule or the first MAYBE rule (if there are no TRUE rules) is found.
If the client wants all of the rules that evaluate to TRUE or MAYBE returned to it, then the client can specify whether evaluation results should be sent back in a complete list of the rules that evaluated to TRUE or MAYBE, or evaluation results should be sent back iteratively. When evaluation results are sent iteratively to the client, the client can retrieve each rule that evaluated to TRUE or MAYBE one by one using the GET_NEXT_HIT function in the DBMS_RULE package.
The rules engine uses the rules in the specified rule set for evaluation and returns the results to the client. The rules engine returns rules using two OUT parameters in the EVALUATE procedure. This procedure is overloaded and the two OUT parameters are different in each version of the procedure:
-
One version of the procedure returns all of the rules that evaluate to TRUE in one list or all of the rules that evaluate to MAYBE in one list, and the two OUT parameters for this version of the procedure are true_rules and maybe_rules. That is, the true_rules parameter returns rules in one list that evaluate to TRUE, and the maybe_rules parameter returns rules in one list that might evaluate to TRUE given more information.
-
The other version of the procedure returns all of the rules that evaluate to TRUE or MAYBE iteratively at the request of the client, and the two OUT parameters for this version of the procedure are true_rules_iterator and maybe_rules_iterator. That is, the true_rules_iterator parameter returns rules that evaluate to TRUE one by one, and the maybe_rules_iterator parameter returns rules one by one that might evaluate to TRUE given more information.
Rule Set Evaluation Process
Figure 11-1 shows the rule set evaluation process:
-
A client-defined event occurs.
-
The client initiates evaluation of a rule set by sending information about an event to the rules engine using the DBMS_RULE.EVALUATE procedure.
-
The rules engine evaluates the rule set for the event using the relevant evaluation context. The client specifies both the rule set and the evaluation context in the call to the DBMS_RULE.EVALUATE procedure. Only rules that are in the specified rule set, and use the specified evaluation context, are used for evaluation.
-
The rules engine obtains the results of the evaluation. Each rule evaluates to either TRUE, FALSE, or NULL (unknown).
-
The rules engine returns rules that evaluated to TRUE to the client, either in a complete list or one by one. Each returned rule is returned with its entire action context, which can contain information or can be NULL.
-
The client performs actions based on the results returned by the rules engine. The rules engine does not perform actions based on rule evaluations.
Partial Evaluation
Partial evaluation occurs when the DBMS_RULE.EVALUATE procedure is run without data for all the tables and variables in the specified evaluation context. During partial evaluation, some rules can reference columns, variables, or attributes that are unavailable, while some other rules can reference only available data.
For example, consider a scenario where only the following data is available during evaluation:
-
Column tab1.col = 7
-
Attribute v1.a1 = 'ABC'
The following rules are used for evaluation:
-
Rule R1 has the following condition:
(tab1.col = 5)
-
Rule R2 has the following condition:
(:v1.a2 > 'aaa')
-
Rule R3 has the following condition:
(:v1.a1 = 'ABC') OR (:v2 = 5)
-
Rule R4 has the following condition:
(:v1.a1 = UPPER('abc'))
Given this scenario, R1 and R4 reference available data, R2 references unavailable data, and R3 references available data and unavailable data.
Partial evaluation always evaluates only simple conditions within a rule. If the rule condition has parts which are not simple, then the rule might or might not be evaluated completely, depending on the extent to which data is available. If a rule is not completely evaluated, then it can be returned as a MAYBE rule.
Given the rules in this scenario, R1 and the first part of R3 are evaluated, but R2 and R4 are not evaluated. The following results are returned to the client:
-
R1 evaluates to FALSE, and so is not returned.
-
R2 is returned as MAYBE because information about attribute v1.a2 is not available.
-
R3 is returned as TRUE because R3 is a simple rule and the value of v1.a1 matches the first part of the rule condition.
-
R4 is returned as MAYBE because the rule condition is not simple. The client must supply the value of variable v1 for this rule to evaluate to TRUE or FALSE.
Database Objects and Privileges Related to Rules
You can create the following types of database objects directly using the DBMS_RULE_ADM package:
-
Evaluation contexts
-
Rules
-
Rule sets
You can create rules and rule sets indirectly using the DBMS_STREAMS_ADM package. You control the privileges for these database objects using the following procedures in the DBMS_RULE_ADM package:
-
GRANT_OBJECT_PRIVILEGE
-
GRANT_SYSTEM_PRIVILEGE
-
REVOKE_OBJECT_PRIVILEGE
-
REVOKE_SYSTEM_PRIVILEGE
To allow a user to create rule sets, rules, and evaluation contexts in the user's own schema, grant the user the following system privileges:
These privileges, and the privileges discussed in the following sections, can be granted to the user directly or through a role.
This section contains these topics:
|
Note:
When you grant a privilege on "ANY" object (for example, ALTER_ANY_RULE), and the initialization parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE, you give the user access to that type of object in all schemas except the SYS schema. By default, the initialization parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE.
If you want to grant access to an object in the SYS schema, then you can grant object privileges explicitly on the object. Alternatively, you can set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to TRUE. Then privileges granted on "ANY" object will allow access to any schema, including SYS.
|
Privileges for Creating Database Objects Related to Rules
To create an evaluation context, rule, or rule set in a schema, a user must meet at least one of the following conditions:
-
The schema must be the user's own schema, and the user must be granted the create system privilege for the type of database object being created. For example, to create a rule set in the user's own schema, a user must be granted the CREATE_RULE_SET_OBJ system privilege.
-
The user must be granted the create any system privilege for the type of database object being created. For example, to create an evaluation context in any schema, a user must be granted the CREATE_ANY_EVALUATION_CONTEXT system privilege.
|
Note:
When creating a rule with an evaluation context, the rule owner must have privileges on all objects accessed by the evaluation context. |
Privileges for Altering Database Objects Related to Rules
To alter an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:
-
The user must own the database object.
-
The user must be granted the alter object privilege for the database object if it is in another user's schema. For example, to alter a rule set in another user's schema, a user must be granted the ALTER_ON_RULE_SET object privilege on the rule set.
-
The user must be granted the alter any system privilege for the database object. For example, to alter a rule in any schema, a user must be granted the ALTER_ANY_RULE system privilege.
Privileges for Dropping Database Objects Related to Rules
To drop an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:
-
The user must own the database object.
-
The user must be granted the drop any system privilege for the database object. For example, to drop a rule set in any schema, a user must be granted the DROP_ANY_RULE_SET system privilege.
Privileges for Placing Rules in a Rule Set
This section describes the privileges required to place a rule in a rule set. The user must meet at least one of the following conditions for the rule:
-
The user must own the rule.
-
The user must be granted the execute object privilege on the rule if the rule is in another user's schema. For example, to place a rule named depts in the hr schema in a rule set, a user must be granted the EXECUTE_ON_RULE privilege for the hr.depts rule.
-
The user must be granted the execute any system privilege for rules. For example, to place any rule in a rule set, a user must be granted the EXECUTE_ANY_RULE system privilege.
The user also must meet at least one of the following conditions for the rule set:
-
The user must own the rule set.
-
The user must be granted the alter object privilege on the rule set if the rule set is in another user's schema. For example, to place a rule in the human_resources rule set in the hr schema, a user must be granted the ALTER_ON_RULE_SET privilege for the hr.human_resources rule set.
-
The user must be granted the alter any system privilege for rule sets. For example, to place a rule in any rule set, a user must be granted the ALTER_ANY_RULE_SET system privilege.
In addition, the rule owner must have privileges on all objects referenced by the rule. These privileges are important when the rule does not have an evaluation context associated with it.
Privileges for Evaluating a Rule Set
To evaluate a rule set, a user must meet at least one of the following conditions:
-
The user must own the rule set.
-
The user must be granted the execute object privilege on the rule set if it is in another user's schema. For example, to evaluate a rule set named human_resources in the hr schema, a user must be granted the EXECUTE_ON_RULE_SET privilege for the hr.human_resources rule set.
-
The user must be granted the execute any system privilege for rule sets. For example, to evaluate any rule set, a user must be granted the EXECUTE_ANY_RULE_SET system privilege.
Granting EXECUTE object privilege on a rule set requires that the grantor have the EXECUTE privilege specified WITH GRANT OPTION on all rules currently in the rule set.
Privileges for Using an Evaluation Context
To use an evaluation context in a rule or a rule set, the user who owns the rule or rule set must meet at least one of the following conditions for the evaluation context:
-
The user must own the evaluation context.
-
The user must be granted the EXECUTE_ON_EVALUATION_CONTEXT privilege on the evaluation context, if it is in another user's schema.
-
The user must be granted the EXECUTE_ANY_EVALUATION_CONTEXT system privilege for evaluation contexts.
Evaluation Contexts Used in Oracle Streams
The following sections describe the system-created evaluation contexts used in Oracle Streams.
Evaluation Context for Global, Schema, Table, and Subset Rules
When you create global, schema, table, and subset rules, the system-created rule sets and rules use a built-in evaluation context in the SYS schema named STREAMS$_EVALUATION_CONTEXT. PUBLIC is granted the EXECUTE privilege on this evaluation context. Global, schema, table, and subset rules can be used by capture processes, synchronous captures, propagations, apply processes, and messaging clients.
During Oracle installation, the following statement creates the Oracle Streams evaluation context:
DECLARE
vt SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
vt := SYS.RE$VARIABLE_TYPE_LIST(
SYS.RE$VARIABLE_TYPE('DML', 'SYS.LCR$_ROW_RECORD',
'SYS.DBMS_STREAMS_INTERNAL.ROW_VARIABLE_VALUE_FUNCTION',
'SYS.DBMS_STREAMS_INTERNAL.ROW_FAST_EVALUATION_FUNCTION'),
SYS.RE$VARIABLE_TYPE('DDL', 'SYS.LCR$_DDL_RECORD',
'SYS.DBMS_STREAMS_INTERNAL.DDL_VARIABLE_VALUE_FUNCTION',
'SYS.DBMS_STREAMS_INTERNAL.DDL_FAST_EVALUATION_FUNCTION'));
SYS.RE$VARIABLE_TYPE(NULL, 'SYS.ANYDATA',
NULL,
'SYS.DBMS_STREAMS_INTERNAL.ANYDATA_FAST_EVAL_FUNCTION'));
DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
evaluation_context_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
variable_types => vt,
evaluation_function =>
'SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION');
END;
/
This statement includes references to the following internal functions in the SYS.DBMS_STREAM_INTERNAL package:
-
ROW_VARIABLE_VALUE_FUNCTION
-
DDL_VARIABLE_VALUE_FUNCTION
-
EVALUATION_CONTEXT_FUNCTION
-
ROW_FAST_EVALUATION_FUNCTION
-
DDL_FAST_EVALUATION_FUNCTION
-
ANYDATA_FAST_EVAL_FUNCTION
|
Caution:
Information about these internal functions is provided for reference purposes only. You should never run any of these functions directly. |
The ROW_VARIABLE_VALUE_FUNCTION converts an ANYDATA payload, which encapsulates a SYS.LCR$_ROW_RECORD instance, into a SYS.LCR$_ROW_RECORD instance before evaluating rules on the data.
The DDL_VARIABLE_VALUE_FUNCTION converts an ANYDATA payload, which encapsulates a SYS.LCR$_DDL_RECORD instance, into a SYS.LCR$_DDL_RECORD instance before evaluating rules on the data.
The EVALUATION_CONTEXT_FUNCTION is specified as an evaluation_function in the call to the CREATE_EVALUATION_CONTEXT procedure. This function supplements normal rule evaluation for captured LCRs. A capture process enqueues row LCRs and DDL LCRs into its queue, and this function enables it to enqueue other internal messages into the queue, such as commits, rollbacks, and data dictionary changes. This information that is enqueued by capture processes is also used during rule evaluation for a propagation or apply process. Synchronous captures do not use the EVALUATION_CONTEXT_FUNCTION.
ROW_FAST_EVALUATION_FUNCTION improves performance by optimizing access to the following LCR$_ROW_RECORD member functions during rule evaluation:
-
GET_OBJECT_OWNER
-
GET_OBJECT_NAME
-
IS_NULL_TAG
-
GET_SOURCE_DATABASE_NAME
-
GET_COMMAND_TYPE
DDL_FAST_EVALUATION_FUNCTION improves performance by optimizing access to the following LCR$_DDL_RECORD member functions during rule evaluation if the condition is <, <=, =, >=, or > and the other operand is a constant:
-
GET_OBJECT_OWNER
-
GET_OBJECT_NAME
-
IS_NULL_TAG
-
GET_SOURCE_DATABASE_NAME
-
GET_COMMAND_TYPE
-
GET_BASE_TABLE_NAME
-
GET_BASE_TABLE_OWNER
ANYDATA_FAST_EVAL_FUNCTION improves performance by optimizing access to values inside an ANYDATA object.
Rules created using the DBMS_STREAMS_ADM package use ROW_FAST_EVALUATION_FUNCTION or DDL_FAST_EVALUATION_FUNCTION, except for subset rules created using the ADD_SUBSET_RULES or ADD_SUBSET_PROPAGATION_RULES procedure.
Evaluation Contexts for Message Rules
When you use either the ADD_MESSAGE_RULE procedure or the ADD_MESSAGE_PROPAGATION_RULE procedure to create a message rule, the message rule uses a user-defined message type that you specify when you create the rule. Such a system-created message rule uses a system-created evaluation context. The name of the system-created evaluation context is different for each message type used to create message rules. Such an evaluation context has a system-generated name and is created in the schema that owns the rule. Only the user who owns this evaluation context is granted the EXECUTE privilege on it.
The evaluation context for this type of message rule contains a variable that is the same type as the message type. The name of this variable is in the form VAR$_number, where number is a system-generated number. For example, if you specify strmadmin.region_pri_msg as the message type when you create a message rule, then the system-created evaluation context has a variable of this type, and the variable is used in the rule condition. Assume that the following statement created the strmadmin.region_pri_msg type:
CREATE TYPE strmadmin.region_pri_msg AS OBJECT(
region VARCHAR2(100),
priority NUMBER,
message VARCHAR2(3000))
/
When you create a message rule using this type, you can specify the following rule condition:
:msg.region = 'EUROPE' AND :msg.priority = '1'
The system-created message rule replaces :msg in the rule condition you specify with the name of the variable. The following is an example of a message rule condition that might result:
:VAR$_52.region = 'EUROPE' AND :VAR$_52.priority = '1'
In this case, VAR$_52 is the variable name, the type of the VAR$_52 variable is strmadmin.region_pri_msg, and the evaluation context for the rule contains this variable.
The message rule itself has an evaluation context. A statement similar to the following creates an evaluation context for a message rule:
DECLARE
vt SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
vt := SYS.RE$VARIABLE_TYPE_LIST(
SYS.RE$VARIABLE_TYPE('VAR$_52', 'STRMADMIN.REGION_PRI_MSG',
'SYS.DBMS_STREAMS_INTERNAL.MSG_VARIABLE_VALUE_FUNCTION', NULL));
DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
evaluation_context_name => 'STRMADMIN.EVAL_CTX$_99',
variable_types => vt,
evaluation_function => NULL);
END;
/
The name of the evaluation context is in the form EVAL_CTX$_number, where number is a system-generated number. In this example, the name of the evaluation context is EVAL_CTX$_99.
This statement also includes a reference to the MSG_VARIABLE_VALUE_FUNCTION internal function in the SYS.DBMS_STREAM_INTERNAL package. This function converts an ANYDATA payload, which encapsulates a message instance, into an instance of the same type as the variable before evaluating rules on the data. For example, if the variable type is strmadmin.region_pri_msg, then the MSG_VARIABLE_VALUE_FUNCTION converts the message payload from an ANYDATA payload to a strmadmin.region_pri_msg payload.
If you create rules for different message types, then Oracle creates a different evaluation context for each message type. If you create a rule with the same message type as an existing rule, then the new rule uses the evaluation context for the existing rule. When you use the ADD_MESSAGE_RULE or ADD_MESSAGE_PROPAGATION_RULE to create a rule set for a messaging client or apply process, the new rule set does not have an evaluation context.
Oracle Streams and Event Contexts
In Oracle Streams, capture processes, synchronous captures, and messaging clients do not use event contexts, but propagations and apply processes do. The following types of messages can be staged in a queue: captured LCRs, buffered LCRs, buffered user messages, persistent LCRs, and persistent user messages. When a message is staged in a queue, a propagation or apply process can send the message, along with an event context, to the rules engine for evaluation. An event context always has the following name-value pair: AQ$_MESSAGE as the name and the message as the value.
If you create a custom evaluation context, then you can create propagation and apply process rules that refer to Oracle Streams events using implicit variables. The variable value function for each implicit variable can check for event contexts with the name AQ$_MESSAGE. If an event context with this name is found, then the variable value function returns a value based on a message. You can also pass the event context to an evaluation function and a variable method function.
Oracle Streams and Action Contexts
The following sections describe the purposes of action contexts in Oracle Streams and the importance of ensuring that only one rule in a rule set can evaluate to TRUE for a particular rule condition.
Purposes of Action Contexts in Oracle Streams
In Oracle Streams, an action context serves the following purposes:
A different name-value pair can exist in the action context of a rule for each of these purposes. If an action context for a rule contains more than one of these name-value pairs, then the actions specified or described by the name-value pairs are performed in the following order:
-
Perform subset transformation.
-
Display information about declarative rule-based transformation.
-
Perform custom rule-based transformation.
-
Follow execution directive and perform execution if directed to do so (apply only).
-
Enqueue into a destination queue (apply only).
Internal LCR Transformations in Subset Rules
When you use subset rules, an update operation can be converted into an insert or delete operation when it is captured, propagated, applied, or dequeued. This automatic conversion is called row migration and is performed by an internal transformation specified in the action context when the subset rule evaluates to TRUE. The name-value pair for a subset transformation has STREAMS$_ROW_SUBSET for the name and either INSERT or DELETE for the value.
Information About Declarative Rule-Based Transformations
A declarative rule-based transformation is an internal modification of a row LCR that results when a rule evaluates to TRUE. The name-value pair for a declarative rule-based transformation has STREAMS$_INTERNAL_TRANFORM for the name and the name of a data dictionary view that provides additional information about the transformation for the value.
The name-value pair added for a declarative rule-based transformation is for information purposes only. These name-value pairs are not used by Oracle Streams clients. However, the declarative rule-based transformations described in an action context are performed internally before any custom rule-based transformations specified in the same action context.
Custom Rule-Based Transformations
A custom rule-based transformation is any modification made by a user-defined function to a message when a rule evaluates to TRUE. The name-value pair for a custom rule-based transformation has STREAMS$_TRANSFORM_FUNCTION for the name and the name of the transformation function for the value.
Execution Directives for Messages During Apply
The SET_EXECUTE procedure in the DBMS_APPLY_ADM package specifies whether a message that satisfies the specified rule is executed by an apply process. The name-value pair for an execution directive has APPLY$_EXECUTE for the name and NO for the value if the apply process should not execute the message. If a message that satisfies a rule should be executed by an apply process, then this name-value pair is not present in the action context of the rule.
Enqueue Destinations for Messages During Apply
The SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package sets the queue where a message that satisfies the specified rule is enqueued automatically by an apply process. The name-value pair for an enqueue destination has APPLY$_ENQUEUE for the name and the name of the destination queue for the value.
Ensure That Only One Rule Can Evaluate to TRUE for a Particular Rule Condition
If you use a non-NULL action context for one or more rules in a positive rule set, then ensure that only one rule can evaluate to TRUE for a particular rule condition. If more than one rule evaluates to TRUE for a particular condition, then only one of the rules is returned, which can lead to unpredictable results.
For example, suppose two rules evaluate to TRUE if an LCR contains a DML change to the hr.employees table. The first rule has a NULL action context. The second rule has an action context that specifies a custom rule-based transformation. If there is a DML change to the hr.employees table, then both rules evaluate to TRUE for the change, but only one rule is returned. In this case, the transformation might or might not occur, depending on which rule is returned.
You might want to ensure that only one rule in a positive rule set can evaluate to TRUE for any condition, regardless of whether any of the rules have a non-NULL action context. By following this guideline, you can avoid unpredictable results if, for example, a non-NULL action context is added to a rule in the future.
Action Context Considerations for Schema and Global Rules
If you use an action context for a custom rule-based transformation, enqueue destination, or execute directive with a schema rule or global rule, then the action specified by the action context is carried out on a message if the message causes the schema or global rule to evaluate to TRUE. For example, if a schema rule has an action context that specifies a custom rule-based transformation, then the transformation is performed on LCRs for the tables in the schema.
You might want to use an action context with a schema or global rule but exclude a subset of LCRs from the action performed by the action context. For example, if you want to perform a custom rule-based transformation on all of the tables in the hr schema except for the job_history table, then ensure that the transformation function returns the original LCR if the table is job_history.
If you want to set an enqueue destination or an execute directive for all of the tables in the hr schema except for the job_history table, then you can use a schema rule and add the following condition to it:
:dml.get_object_name() != 'JOB_HISTORY'
In this case, if you want LCRs for the job_history table to evaluate to TRUE, but you do not want to perform the enqueue or execute directive, then you can add a table rule for the table to a positive rule set. That is, the schema rule would have the enqueue destination or execute directive, but the table rule would not.
User-Created Rules, Rule Sets, and Evaluation Contexts
The DBMS_STREAMS_ADM package generates system-created rules and rule sets, and it can specify an Oracle-supplied evaluation context for rules and rule sets or generate system-created evaluation contexts. If you must create rules, rule sets, or evaluation contexts that cannot be created using the DBMS_STREAMS_ADM package, then you can use the DBMS_RULE_ADM package to create them.
Use the DBMS_RULE_ADM package for the following reasons:
-
You must create rules with rule conditions that cannot be created using the DBMS_STREAMS_ADM package, such as rule conditions for specific types of operations, or rule conditions that use the LIKE condition.
-
You must create custom evaluation contexts for the rules in your Oracle Streams environment.
You can create a rule set using the DBMS_RULE_ADM package, and you can associate it with a capture process, synchronous capture, propagation, apply process, or messaging client. Such a rule set can be a positive rule set or negative rule set for an Oracle Streams client, and a rule set can be a positive rule set for one Oracle Streams client and a negative rule set for another.
This section contains the following topics:
User-Created Rules and Rule Sets
The following sections describe some of the types of rules and rule sets that you can create using the DBMS_RULE_ADM package:
Rule Conditions for Specific Types of Operations
In some cases, you might want to capture, propagate, apply, or dequeue only changes that contain specific types of operations. For example, you might want to apply changes containing only insert operations for a particular table, but not other operations, such as update and delete.
Suppose you want to specify a rule condition that evaluates to TRUE only for INSERT operations on the hr.employees table. You can accomplish this by specifying the INSERT command type in the rule condition:
:dml.get_command_type() = 'INSERT' AND :dml.get_object_owner() = 'HR'
AND :dml.get_object_name() = 'EMPLOYEES' AND :dml.is_null_tag() = 'Y'
Similarly, suppose you want to specify a rule condition that evaluates to TRUE for all DML operations on the hr.departments table, except DELETE operations. You can accomplish this by specifying the following rule condition:
:dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'DEPARTMENTS' AND
:dml.is_null_tag() = 'Y' AND (:dml.get_command_type() = 'INSERT' OR
:dml.get_command_type() = 'UPDATE')
This rule condition evaluates to TRUE for INSERT and UPDATE operations on the hr.departments table, but not for DELETE operations. Because the hr.departments table does not include any LOB columns, you do not need to specify the LOB command types for DML operations (LOB ERASE, LOB WRITE, and LOB TRIM), but these command types should be specified in such a rule condition for a table that contains one or more LOB columns.
The following rule condition accomplishes the same behavior for the hr.departments table. That is, the following rule condition evaluates to TRUE for all DML operations on the hr.departments table, except DELETE operations:
:dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'DEPARTMENTS' AND
:dml.is_null_tag() = 'Y' AND :dml.get_command_type() != 'DELETE'
The example rule conditions described previously in this section are all simple rule conditions. However, when you add custom conditions to system-created rule conditions, the entire condition might not be a simple rule condition, and nonsimple rules might not evaluate efficiently. In general, you should use simple rule conditions whenever possible to improve rule evaluation performance. Rule conditions created using the DBMS_STREAMS_ADM package, without custom conditions added, are always simple.
Rule Conditions that Instruct Oracle Streams Clients to Discard Unsupported LCRs
You can use the following functions in rule conditions to instruct an Oracle Streams client to discard LCRs that encapsulate unsupported changes:
-
The GET_COMPATIBLE member function for LCRs. This function returns the minimal database compatibility required to support an LCR.
-
The COMPATIBLE_9_2 function, COMPATIBLE_10_1 function, COMPATIBLE_10_2 function, COMPATIBLE_11_1 function, COMPATIBLE_11_2 function, and MAX_COMPATIBLE function in the DBMS_STREAMS package. These functions return constant values that correspond to 9.2.0, 10.1.0, 10.2.0, 11.1.0, 11.2.0, and maximum compatibility in a database, respectively. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.
For example, consider the following rule:
BEGIN
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'strmadmin.dml_compat_9_2',
condition => ':dml.GET_COMPATIBLE() > DBMS_STREAMS.COMPATIBLE_9_2()');
END;
/
If this rule is in the negative rule set for an Oracle Streams client, such as a capture process, a propagation, or an apply process, then the Oracle Streams client discards any row LCR that is not compatible with Oracle9i Database Release 2 (9.2).
The following is an example that is more appropriate for a positive rule set:
BEGIN
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'strmadmin.dml_compat_9_2',
condition => ':dml.GET_COMPATIBLE() <= DBMS_STREAMS.COMPATIBLE_10_1()');
END;
/
If this rule is in the positive rule set for an Oracle Streams client, then the Oracle Streams client discards any row LCR that is not compatible with Oracle Database 10g Release 1 or earlier. That is, the Oracle Streams client processes any row LCR that is compatible with Oracle9i Database Release 2 (9.2) or Oracle Database 10g Release 1 (10.1) and satisfies the other rules in its rule sets, but it discards any row LCR that is not compatible with these releases.
You can add the following rule to a positive rule set to discard row LCRs that are not supported by Oracle Streams in your current release of Oracle Database:
BEGIN
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'strmadmin.dml_compat_max',
condition => ':dml.GET_COMPATIBLE() < DBMS_STREAMS.MAX_COMPATIBLE()');
END;
/
The MAX_COMPATIBLE function always returns the maximum compatibility, which is greater than the compatibility constants returned by the DBMS_STREAMS package. Therefore, when you use this function in rule conditions, the rule conditions do not need to be changed when you upgrade to a later release of Oracle Database. Newly supported changes in a later release will automatically be captured and LCRs containing newly supported changes will not be discarded.
The rules in the previous examples evaluate efficiently. If you use schema rules or global rules created by the DBMS_STREAMS_ADM package to capture, propagate, apply, or dequeue LCRs, then you can use rules such as these to discard LCRs that are not supported by a particular database.
|
Note:
-
You can determine which database objects in a database are not supported by Oracle Streams by querying the DBA_STREAMS_UNSUPPORTED and DBA_STREAMS_COLUMNS data dictionary views.
-
Instead of using the DBMS_RULE_ADM package to create rules with GET_COMPATIBLE conditions, you can use one of the procedures in the DBMS_STREAMS_ADM package to create such rules by specifying the GET_COMPATIBLE condition in the AND_CONDITION parameter.
-
DDL LCRs always return DBMS_STREAMS.COMPATIBLE_9_2.
|
Complex Rule Conditions
Complex rule conditions are rule conditions that do not meet the requirements for simple rule conditions described in "Simple Rule Conditions". In an Oracle Streams environment, the DBMS_STREAMS_ADM package creates rules with simple rule conditions only, assuming no custom conditions are added to the system-created rules.
Table 5-3 describes the types of system-created rule conditions that you can create with the DBMS_STREAMS_ADM package. If you must create rules with complex conditions, then you can use the DBMS_RULE_ADM package.
There is a wide range of complex rule conditions. The following sections contain some examples of complex rule conditions.
|
Note:
-
Complex rule conditions can degrade rule evaluation performance.
-
In rule conditions, if you specify the name of a database, then ensure that you include the full database name, including the domain name.
|
Rule Conditions Using the NOT Logical Condition to Exclude Objects
You can use the NOT logical condition to exclude certain changes from being captured, propagated, applied, or dequeued in an Oracle Streams environment.
For example, suppose you want to specify rule conditions that evaluate to TRUE for all DML and DDL changes to all database objects in the hr schema, except for changes to the hr.regions table. You can use the NOT logical condition to accomplish this with two rules: one for DML changes and one for DDL changes. Here are the rule conditions for these rules:
(:dml.get_object_owner() = 'HR' AND NOT :dml.get_object_name() = 'REGIONS')
AND :dml.is_null_tag() = 'Y' ((:ddl.get_object_owner() = 'HR' OR :ddl.get_base_
table_owner() = 'HR') AND NOT :ddl.get_object_name() = 'REGIONS') AND :ddl.is_
null_tag() = 'Y'
Notice that object names, such as HR and REGIONS are specified in all uppercase characters in these examples. For rules to evaluate properly, the case of the characters in object names, such as tables and users, must match the case of the characters in the data dictionary. Therefore, if no case was specified for an object when the object was created, then specify the object name in all uppercase in rule conditions. However, if a particular case was specified with double quotation marks when the objects was created, then specify the object name in the same case in rule conditions. However, the object name cannot be enclosed in double quotes in rule conditions.
For example, if the REGIONS table in the HR schema was actually created as "Regions", then specify Regions in rule conditions that involve this table, as in the following example:
:dml.get_object_name() = 'Regions'
You can use the Oracle Streams evaluation context when you create these rules using the DBMS_RULE_ADM package. The following example creates a rule set to hold the complex rules, creates rules with the previous conditions, and adds the rules to the rule set:
BEGIN
-- Create the rule set
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'strmadmin.complex_rules',
evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');
-- Create the complex rules
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'strmadmin.hr_not_regions_dml',
condition => ' (:dml.get_object_owner() = ''HR'' AND NOT ' ||
' :dml.get_object_name() = ''REGIONS'') AND ' ||
' :dml.is_null_tag() = ''Y'' ');
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'strmadmin.hr_not_regions_ddl',
condition => ' ((:ddl.get_object_owner() = ''HR'' OR ' ||
' :ddl.get_base_table_owner() = ''HR'') AND NOT ' ||
' :ddl.get_object_name() = ''REGIONS'') AND ' ||
' :ddl.is_null_tag() = ''Y'' ');
-- Add the rules to the rule set
DBMS_RULE_ADM.ADD_RULE(
rule_name => 'strmadmin.hr_not_regions_dml',
rule_set_name => 'strmadmin.complex_rules');
DBMS_RULE_ADM.ADD_RULE(
rule_name => 'strmadmin.hr_not_regions_ddl',
rule_set_name => 'strmadmin.complex_rules');
END;
/
In this case, the rules inherit the Oracle Streams evaluation context from the rule set.
|
Note:
In most cases, you can avoid using complex rules with the NOT logical condition by using the DBMS_STREAMS_ADM package to add rules to the negative rule set for an Oracle Streams client |
Rule Conditions Using the LIKE Condition
You can use the LIKE condition to create complex rules that evaluate to TRUE when a condition in the rule matches a specified pattern. For example, suppose you want to specify rule conditions that evaluate to TRUE for all DML and DDL changes to all database objects in the hr schema that begin with the pattern JOB. You can use the LIKE condition to accomplish this with two rules: one for DML changes and one for DDL changes. Here are the rule conditions for these rules:
(:dml.get_object_owner() = 'HR' AND :dml.get_object_name() LIKE 'JOB%')
AND :dml.is_null_tag() = 'Y'
((:ddl.get_object_owner() = 'HR' OR :ddl.get_base_table_owner() = 'HR')
AND :ddl.get_object_name() LIKE 'JOB%') AND :ddl.is_null_tag() = 'Y'
Rule Conditions with Undefined Variables that Evaluate to NULL
During evaluation, an implicit variable in a rule condition is undefined if the variable value function for the variable returns NULL. An explicit variable without any attributes in a rule condition is undefined if the client does not send the value of the variable to the rules engine when it runs the DBMS_RULE.EVALUATE procedure.
Regarding variables with attributes, a variable is undefined if the client does not send the value of the variable, or any of its attributes, to the rules engine when it runs the DBMS_RULE.EVALUATE procedure. For example, if variable x has attributes a and b, then the variable is undefined if the client does not send the value of x and does not send the value of a and b. However, if the client sends the value of at least one attribute, then the variable is defined. In this case, if the client sends the value of a, but not b, then the variable is defined.
An undefined variable in a rule condition evaluates to NULL for Oracle Streams clients of the rules engine, which include capture processes, synchronous captures, propagations, apply processes, and messaging clients. In contrast, for non-Oracle Streams clients of the rules engine, an undefined variable in a rule condition can cause the rules engine to return maybe_rules to the client. When a rule set is evaluated, maybe_rules are rules that might evaluate to TRUE given more information.
The number of maybe_rules returned to Oracle Streams clients is reduced by treating each undefined variable as NULL. Reducing the number of maybe_rules can improve performance if the reduction results in more efficient evaluation of a rule set when a message occurs. Rules that would result in maybe_rules for non-Oracle Streams clients can result in TRUE or FALSE rules for Oracle Streams clients, as the following examples illustrate.
Examples of Undefined Variables that Result in TRUE Rules for Oracle Streams Clients
Consider the following user-defined rule condition:
:m IS NULL
If the value of the variable m is undefined during evaluation, then a maybe rule results for non-Oracle Streams clients of the rules engine. However, for Oracle Streams clients, this condition evaluates to TRUE because the undefined variable m is treated as a NULL. You should avoid adding rules such as this to rule sets for Oracle Streams clients, because such rules will evaluate to TRUE for every message. So, for example, if the positive rule set for a capture process has such a rule, then the capture process might capture messages that you did not intend to capture.
Here is another user-specified rule condition that uses an Oracle Streams :dml variable:
:dml.get_object_owner() = 'HR' AND :m IS NULL
For Oracle Streams clients, if a message consists of a row change to a table in the hr schema, and the value of the variable m is not known during evaluation, then this condition evaluates to TRUE because the undefined variable m is treated as a NULL.
Examples of Undefined Variables that Result in FALSE Rules for Oracle Streams Clients
Consider the following user-defined rule condition:
:m = 5
If the value of the variable m is undefined during evaluation, then a maybe rule results for non-Oracle Streams clients of the rules engine. However, for Oracle Streams clients, this condition evaluates to FALSE because the undefined variable m is treated as a NULL.
Consider another user-specified rule condition that uses an Oracle Streams :dml variable:
:dml.get_object_owner() = 'HR' AND :m = 5
For Oracle Streams clients, if a message consists of a row change to a table in the hr schema, and the value of the variable m is not known during evaluation, then this condition evaluates to FALSE because the undefined variable m is treated as a NULL.
Variables as Function Parameters in Rule Conditions
Oracle recommends that you avoid using :dml and :ddl variables as function parameters for rule conditions. The following example uses the :dml variable as a parameter to a function named my_function:
my_function(:dml) = 'Y'
Rule conditions such as these can degrade rule evaluation performance and can result in the capture or propagation of extraneous Oracle Streams data dictionary information.
User-Created Evaluation Contexts
You can use a custom evaluation context in an Oracle Streams environment. Any user-defined evaluation context involving LCRs must include all the variables in SYS.STREAMS$_EVALUATION_CONTEXT. The type of each variable and its variable value function must be the same for each variable as the ones defined in SYS.STREAMS$_EVALUATION_CONTEXT. In addition, when creating the evaluation context using DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT, the SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION must be specified for the evaluation_function parameter. You can alter an existing evaluation context using the DBMS_RULE_ADM.ALTER_EVALUATION_CONTEXT procedure.
You can find information about an evaluation context in the following data dictionary views:
If necessary, you can use the information in these data dictionary views to build a new evaluation context based on the SYS.STREAMS$_EVALUATION_CONTEXT.
|
Note:
Avoid using variable names with special characters, such as $ and #, to ensure that there are no conflicts with Oracle-supplied evaluation context variables. |
PKBtl X PK &A OEBPS/strms_glossary.htm
Glossary
action context
Optional information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated for a message.
ANYDATA queue
A queue of type ANYDATA. These queues can stage messages of different types wrapped in an ANYDATA wrapper.
See Also: typed queue
applied SCN
A system change number (SCN) relating to a capture process that corresponds to the most recent message dequeued by an apply process that applies changes captured by the capture process.
apply forwarding
A directed network in which messages being forwarded at an intermediate database are first processed by an apply process. These messages are then recaptured by a capture process at the intermediate database and forwarded.
See Also: queue forwarding
apply handler
A collection of SQL statements or a user-defined procedure used by an apply process for customized processing of messages. Apply handlers include statement DML handlers, message handlers, procedure DML handlers, DDL handlers, precommit handlers, and error handlers.
apply process
An optional Oracle background process that dequeues messages from a specific queue and either applies each message directly, discards it, passes it as a parameter to an apply handler, or re-enqueues it. An apply process is an Oracle Streams client.
See Also: logical change record (LCR)
apply servers
A component of an apply process that includes one or more processes that apply LCRs to database objects as DML or DDL statements or pass the LCRs to their appropriate apply handlers. For user messages, the apply servers pass the messages to the message handler. Apply servers can also enqueue logical change record (LCR) and non-LCR messages specified by the DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION procedure. If an apply server encounters an error, then it tries to resolve the error with a user-specified error handler. If an apply server cannot resolve an error, then it places the entire transaction, including all of its LCRs, in the error queue.
See Also: logical change record (LCR)
apply user
The user in whose security domain an apply process dequeues messages that satisfy its rule sets, applies messages directly to database objects, runs custom rule-based transformations configured for apply process rules, and runs apply handlers configured for the apply process.
approximate commit system change number (approximate CSCN)
An SCN value based on the current SCN of the database when a transaction that has enqueued messages into a commit-time queue is committed.
archived-log downstream capture process
A downstream capture process that captures changes in archived redo log files copied from the source database to the downstream database.
barrier transaction
A DDL transaction or a transaction that includes a row logical change record (row LCR) for which an apply process cannot identify the table rows or the database object by using the destination database data dictionary and virtual dependency definitions.
buffered LCR
A logical change record (LCR) that is constructed explicitly by an application and enqueued into the buffered queue portion of an ANYDATA queue.
buffered queue
The portion of a queue that uses the Oracle Streams pool to store messages in memory and a queue table to store messages that have spilled from memory.
buffered user message
A non-LCR message of a user-defined type that is created explicitly by an application and enqueued into a buffered queue. A buffered user message can be enqueued into the buffered queue portion of an ANYDATA queue or a typed queue.
builder server
A component of a capture process that is a process that merges redo records from the preparer server. These redo records either evaluated to TRUE during partial evaluation or partial evaluation was inconclusive for them. The builder server preserves the system change number (SCN) order of these redo records and passes the merged redo records to the capture process.
capture database
The database running the capture process that captures changes made to the source database. The capture database and the source database are the same database when the capture process is a local capture process. The capture database and the source database are different when the capture process is a downstream capture process.
capture process
An optional Oracle background process that scans the database redo log to capture DML and DDL changes made to database objects. A capture process is an Oracle Streams client.
capture user
Either the user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules, or the user in whose security domain a synchronous capture captures changes that satisfy its rule set and runs custom rule-based transformations configured for synchronous capture rules.
captured LCR
A logical change record (LCR) that was captured implicitly by a capture process and enqueued into the buffered queue portion of an ANYDATA queue.
See Also: user message
captured SCN
The system change number (SCN) that corresponds to the most recent change scanned in the redo log by a capture process.
change cycling
Sending a change back to the database where it originated. Typically, change cycling should be avoided in an information sharing environment by using tags and by using the LCR member function GET_SOURCE_DATABASE_NAME in rule conditions.
See Also: logical change record (LCR)
change handler
A special type of statement DML handler that tracks table changes and was created by either the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure or the DBMS_APPLY_ADM.SET_CHANGE_HANDLER procedure.
checkpoint
Information about the current state of a capture process that is stored persistently in the data dictionary of the database running the capture process.
checkpoint interval
A regular interval at which a capture process attempts to record a checkpoint.
checkpoint retention time
The amount of time that a capture process retains checkpoints before purging them automatically.
column list
A list of columns for which an update conflict handler is called when an update conflict occurs for one or more of the columns in the list.
See Also: conflict resolution
conditional log group
A supplemental log group that logs the before images of all specified columns only if at least one of the columns in the supplemental log group is modified.
See Also: unconditional log group
conflict
A mismatch between the old values in an LCR and the expected data in a table. Conflicts are detected by an apply process when it attempts to apply an LCR. Conflicts typically result when two different databases that are sharing data in a table modify the same row in the table at nearly the same time.
See Also: logical change record (LCR)
conflict resolution
Handling a conflict to avoid an apply error. Either prebuilt update conflict handlers or custom conflict handlers can resolve conflicts.
consumption
The process of dequeuing an message from a queue.
coordinator process
A component of an apply process that is an Oracle background process that gets transactions from the reader server and passes them to apply servers.
custom apply
An apply process passes an LCR as a parameter to a user procedure for processing. The user procedure can process the LCR in a customized way.
See Also: logical change record (LCR)
custom rule-based transformation
A rule-based transformation that requires a user-defined PL/SQL function to perform the transformation.
See Also: declarative rule-based transformation
database supplemental logging
The type of supplemental logging that can apply to the primary key, foreign key, and unique key columns in an entire database.
DDL handler
An apply handler that uses a PL/SQL procedure to process DDL LCRs.
See Also: DDL logical change record (DDL LCR)
DDL logical change record (DDL LCR)
A logical change record (LCR) that describes a data definition language (DDL) change.
declarative rule-based transformation
A rule-based transformation that covers one of a common set of transformation scenarios for row LCRs. Declarative rule-based transformations are run internally without using PL/SQL.
See Also: row logical change record (row LCR) and custom rule-based transformation
dequeue
To retrieve a message from a queue.
destination database
A database where messages are consumed. Messages can be consumed when they are dequeued implicitly from a queue by a propagation or apply process, or messages can be consumed when they are dequeued explicitly by an application, a messaging client, or a user.
See Also: consumption
destination queue
The queue that receives the messages propagated by a propagation from a source queue.
direct apply
An apply process applies an LCR without running a user procedure.
See Also: logical change record (LCR)
directed network
A network in which propagated messages pass through one or more intermediate databases before arriving at a destination database.
DML handler
An apply handler that processes row LCRs.
See Also: row logical change record (row LCR)
downstream capture process
A capture process that runs on a database other than its source database.
downstream database
The database on which a downstream capture process runs.
enqueue
To place a message in a queue.
error handler
An apply handler that uses a PL/SQL procedure to try to resolve apply errors. An error handler is invoked only when a row logical change record (row LCR) raises an apply process error. Such an error might result from a conflict if no conflict handler is specified or if the update conflict handler cannot resolve the conflict.
evaluation context
A database object that defines external data that can be referenced in rule conditions. The external data can exist as variables, table data, or both.
exception queue
Messages are transferred to an exception queue if they cannot be retrieved and processed for some reason.
explicit capture
The messages are enqueued into a queue by an application or a user.
explicit consumption
The messages in a queue are dequeued either by a messaging client when it is invoked by a user or application or by an application or user directly.
expression
A combination of one or more values and operators that evaluate to a value.
file
In the context of a file group, a reference to a file stored on hard disk. A file is composed of a file name, a directory object, and a file type. The directory object references the directory in which the file is stored on hard disk.
file group
A collection of versions.
file group repository
A collection of all of the file groups in a database.
first SCN
The lowest system change number (SCN) in the redo log from which a capture process can capture changes.
global rule
A rule that is relevant either to an entire database or an entire queue.
heterogeneous information sharing
Sharing information between Oracle and non-Oracle databases.
high-watermark
The system change number (SCN) beyond which no messages have been applied by an apply process.
See Also: low-watermark
ignore SCN
The system change number (SCN) specified for a table below which changes cannot be applied by an apply process.
implicit capture
The message s are captured automatically by a capture process or by synchronous capture and enqueued into a queue.
implicit consumption
The messages in a queue are dequeued automatically by an apply process.
instantiation
The process of preparing database objects for instantiation at a source database, optionally copying the database objects from a source database to a destination database, and setting the instantiation SCN for each instantiated database object.
instantiation SCN
The system change number (SCN) for a table which specifies that only changes that were committed after the SCN at the source database are applied by an apply process.
LOB assembly
An option for DML handlers and error handlers that assembles multiple row LCRs resulting from a change to a single row with LOB columns into a single row LCR. LOB assembly simplifies processing of row LCRs with LOB columns in DML handlers and error handlers.
local capture process
A capture process that runs on its source database.
logical change record (LCR)
A message with a specific format that describes a database change.
See Also: row logical change record (row LCR) and DDL logical change record (DDL LCR)
LogMiner data dictionary
A separate data dictionary used by a capture process to determine the details of a change that it is capturing. The LogMiner data dictionary is necessary because the primary data dictionary of the source database might not be synchronized with the redo data being scanned by a capture process.
low-watermark
The system change number (SCN) up to which all messages have been applied by an apply process.
See Also: high-watermark
maximum checkpoint SCN
The system change number (SCN) that corresponds to the last checkpoint interval recorded by a capture process.
message
A unit of shared information in an Oracle Streams environment.
message handler
An apply handler that uses a PL/SQL procedure to process persistent user messages.
See Also: logical change record (LCR)
message rule
A rule that is relevant only for a user message of a specific message type.
messaging client
An optional Oracle Streams client that dequeues persistent LCRs or persistent user messages when it is invoked by an application or a user.
negative rule set
A rule set for an Oracle Streams client that results in the Oracle Streams client discarding a message when a rule in the rule set evaluates to TRUE for the message. The negative rule set for an Oracle Streams client always is evaluated before the positive rule set.
nonpersistent queue
Nonpersistent queues store messages in memory. They are generally used to provide an asynchronous mechanism to send notifications to all users that are currently connected. Nonpersistent queues were deprecated in Oracle Database 10g Release 2. Oracle recommends that you use buffered messaging instead.
nontransactional queue
A queue in which each message is its own transaction.
See Also: transactional queue
object dependency
A virtual dependency definition that defines a parent-child relationship between two objects at a destination database.
oldest SCN
For a running apply process, the earliest system change number (SCN) of the transactions currently being dequeued and applied. For a stopped apply process, the oldest SCN is the earliest SCN of the transactions that were being applied when the apply process was stopped.
Oracle Streams client
A mechanism that performs work in an Oracle Streams environment and is a client of the rules engine (when the mechanism is associated with one or more rule sets). The following are Oracle Streams clients: capture process, propagation, apply process, and messaging client.
Oracle Streams data dictionary
A separate data dictionary used by propagations and apply processes to keep track of the database objects from a particular source database.
Oracle Streams pool
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 apply processes.
Oracle Streams topology
A representation of the databases in an Oracle Streams environment, the Oracle Streams components configured in these databases, and the flow of messages between these components.
persistent LCR
A logical change record (LCR) that is enqueued into the persistent queue portion of an ANYDATA queue. A persistent LCR can be enqueued in one of the following ways:
-
Captured implicitly by a synchronous capture and enqueued
-
Constructed explicitly by an application and enqueued
-
Dequeued by an apply process and enqueued by the same apply process using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package
persistent queue
The portion of a queue that only stores messages on hard disk in a queue table, not in memory.
persistent user message
A non-LCR message of a user-defined type that is enqueued into a persistent queue. A persistent user message can be enqueued in one of the following ways:
A persistent user message can be enqueued into the persistent queue portion of an ANYDATA queue or a typed queue.
positive rule set
A rule set for an Oracle Streams client that results in the Oracle Streams client performing its task for a message when a rule in the rule set evaluates to TRUE for the message. The negative rule set for an Oracle Streams client always is evaluated before the positive rule set.
precommit handler
An apply handler that can receive the commit information for a transaction and use a PL/SQL procedure to process the commit information in any customized way.
prepared table
A table that has been prepared for instantiation.
preparer server
A component of a capture process that scans a region defined by the reader server and performs prefiltering of changes found in the redo log. A reader server is a process, and multiple reader servers can run in parallel. Prefiltering entails sending partial information about changes, such as schema and object name for a change, to the rules engine for evaluation, and receiving the results of the evaluation.
procedure DML handler
An apply handler that uses a PL/SQL procedure to process row LCRs.
See Also: row logical change record (row LCR)
propagation
An optional Oracle Streams client that uses an Oracle Scheduler job to send messages from a source queue to a destination queue.
propagation job
An Oracle Scheduler job used by a propagation to propagate messages.
propagation schedule
A schedule that specifies how often a propagation job propagates messages.
queue
The abstract storage unit used by a messaging system to store messages.
queue forwarding
A directed network in which the messages being forwarded at an intermediate database are the messages received by the intermediate database, so that the source database for a message is the database where the message originated.
See Also: apply forwarding
queue table
A database table where queues are stored. Each queue table contains a default exception queue.
reader server
-
A component of a capture process that is a process that reads the redo log and divides the redo log into regions.
-
A component of an apply process that dequeues messages. The reader server is a process that computes dependencies between LCRs and assembles messages into transactions. The reader server then returns the assembled transactions to the coordinator process, which assigns them to idle apply servers.
See Also: logical change record (LCR)
real-time downstream capture process
A downstream capture process that can capture changes made at the source database before the changes are archived in an archived redo log file.
required checkpoint SCN
The system change number (SCN) that corresponds to the lowest checkpoint interval for which a capture process requires redo data.
replication
The process of sharing database objects and data at multiple databases.
resolution column
The column used to identify a prebuilt update conflict handler.
See Also: conflict resolution
row logical change record (row LCR)
A logical change record (LCR) that describes a change to the data in a single row or a change to a single LONG, LONG RAW, or LOB column in a row that results from a data manipulation language (DML) statement or a piecewise operation. One DML statement can result in multiple row LCRs.
row migration
An automatic conversion performed by an internal rule-based transformation when a subset rule evaluates to TRUE in which an UPDATE operation might be converted into an INSERT or DELETE operation.
rule
A database object that enables a client to perform an action when an event occurs and a condition is satisfied.
rule-based transformation
Any modification to a message when a rule in a positive rule set evaluates to TRUE.
rule condition
A component of a rule which combines one or more expressions and conditions and returns a Boolean value, which is a value of TRUE, FALSE, or NULL (unknown).
rule set
A group of rules.
rules engine
A built-in part of Oracle that evaluates rule sets.
schema rule
A rule that is relevant only to a particular schema.
secure queue
A queue for which Oracle Streams Advanced Queuing (AQ) agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue.
source database
The database where changes captured by a capture process are generated in a redo log, or the database where a synchronous capture that generated LCRs is configured.
source queue
The queue from which a propagation propagates messages to a destination queue.
start SCN
The system change number (SCN) from which a capture process begins to capture changes.
statement DML handler
An apply handler that uses one or more SQL statements to process row LCRs.
See Also: row logical change record (row LCR)
subset rule
A rule that is relevant only to a subset of the rows in a particular table.
supplemental log group
A group of columns in a table that is supplementally logged.
See Also: supplemental logging
supplemental logging
Additional column data placed in a redo log whenever an operation is performed. A capture process captures this additional information and places it in LCRs, and the additional information might be needed for an apply process to apply LCRs properly at a destination database.
See Also: logical change record (LCR)
synchronous capture
An optional Oracle Streams client that uses an internal mechanism to capture DML changes made to tables immediately after the changes are made.
system-created rule
A rule with a system-generated name that was created using the DBMS_STREAMS_ADM package.
table rule
A rule that is relevant only to a particular table.
table supplemental logging
The type of supplemental logging that applies to columns in a particular table.
tablespace repository
A collection of the tablespace sets in a file group.
tag
Data of RAW data type that appears in each redo entry and LCR. You can use tags to modify the behavior of Oracle Streams clients and to track LCRs. Tags can also be used to prevent change cycling.
See Also: logical change record (LCR)
transaction control directive
A special type of row LCR captured by a capture process or synchronous capture that contains transaction control statements, such as COMMIT and ROLLBACK.
See Also: row logical change record (row LCR)
transactional queue
A queue in which messages can be grouped into a set that are applied as one transaction.
See Also: nontransactional queue
typed queue
A queue that can stage messages of one specific type only.
See Also: ANYDATA queue
<
!-- class="glossentry" -->
unconditional log group
A supplemental log group that logs the before images of specified columns when the table is changed, regardless of whether the change affected any of the specified columns.
See Also: conditional log group
user message
A non-LCR message of a user-defined type. A user message can be a buffered user message or a persistent user message.
See Also: logical change record (LCR)
value dependency
A virtual dependency definition that defines a table constraint, such as a unique key, or a relationship between the columns of two or more tables.
version
A collection of related files.
virtual dependency definition
A description of a dependency that is used by an apply process to detect dependencies between transactions being applied at a destination database.
PK0
PK &A OEBPS/pt_infprv.htmI
7 Advanced Capture Process Concepts
Capturing information with Oracle Streams means creating a message that contains the information and enqueuing the message into a queue. The captured information can describe a database change, or it can be any other type of information.
The following topics contain conceptual information about capturing information with Oracle Streams:
Multiple Capture Processes in a Single Database
If you run multiple capture processes in a single database, increase the size of the System Global Area (SGA) for each instance. Use the SGA_MAX_SIZE initialization parameter to increase the SGA size. Also, if the size of the Oracle Streams pool is not managed automatically in the database, then increase the size of the Oracle Streams pool by 10 MB for each capture process parallelism. For example, if you have two capture processes running in a database, and the parallelism parameter is set to 4 for one of them and 1 for the other, then increase the Oracle Streams pool by 50 MB (4 + 1 = 5 parallelism).
Also, Oracle recommends that each ANYDATA queue used by a capture process, propagation, or apply process store captured LCRs from at most one capture process from a particular source database. Therefore, use a separate queue for each capture process that captures changes originating at a particular source database, and make sure each queue has its own queue table. Also, do not propagate messages from two or more capture processes with the same source database to the same queue.
|
Note:
The size of the Oracle Streams pool is managed automatically if the MEMORY_TARGET, MEMORY_MAX_TARGET, or SGA_TARGET initialization parameter is set to a nonzero value. |
Capture Process Checkpoints
A checkpoint is information about the current state of a capture process that is stored persistently in the data dictionary of the database running the capture process. A capture process tries to record a checkpoint at regular intervals called checkpoint intervals.
Required Checkpoint SCN
The system change number (SCN) that corresponds to the lowest checkpoint for which a capture process requires redo data is the required checkpoint SCN. The redo log file that contains the required checkpoint SCN, and all subsequent redo log files, must be available to the capture process. If a capture process is stopped and restarted, then it starts scanning the redo log from the SCN that corresponds to its required checkpoint SCN. The required checkpoint SCN is important for recovery if a database stops unexpectedly. Also, if the first SCN is reset for a capture process, then it must be set to a value that is less than or equal to the required checkpoint SCN for the captured process. You can determine the required checkpoint SCN for a capture process by querying the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view.
Maximum Checkpoint SCN
The SCN that corresponds to the last checkpoint recorded by a capture process is the maximum checkpoint SCN. If you create a capture process that captures changes from a source database, and other capture processes already exist which capture changes from the same source database, then the maximum checkpoint SCNs of the existing capture processes can help you to decide whether the new capture process should create a LogMiner data dictionary or share one of the existing LogMiner data dictionaries. You can determine the maximum checkpoint SCN for a capture process by querying the MAX_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view.
Checkpoint Retention Time
The checkpoint retention time is the amount of time, in number of days, that a capture process retains checkpoints before purging them automatically. A capture process periodically computes the age of a checkpoint by subtracting the NEXT_TIME of the archived redo log file that corresponds to the checkpoint from FIRST_TIME of the archived redo log file containing the required checkpoint SCN for the capture process. If the resulting value is greater than the checkpoint retention time, then the capture process automatically purges the checkpoint by advancing its first SCN value. Otherwise, the checkpoint is retained. The DBA_REGISTERED_ARCHIVED_LOG view displays the FIRST_TIME and NEXT_TIME for archived redo log files, and the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE view displays the required checkpoint SCN for a capture process.
Figure 7-1 shows an example of a checkpoint being purged when the checkpoint retention time is set to 20 days.
In Figure 7-1, with the checkpoint retention time set to 20 days, the checkpoint at SCN 435250 is purged because it is 21 days old, while the checkpoint at SCN 479315 is retained because it is 8 days old.
Whenever the first SCN is reset for a capture process, the capture process purges information about archived redo log files before the new first SCN from its LogMiner data dictionary. After this information is purged, the archived redo log files remain on the hard disk, but the files are not needed by the capture process. The PURGEABLE column in the DBA_REGISTERED_ARCHIVED_LOG view displays YES for the archived redo log files that are no longer needed. These files can be removed from disk or moved to another location without affecting the capture process.
If you create a capture process using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package, then you can specify the checkpoint retention time, in days, using the checkpoint_retention_time parameter. The default checkpoint retention time is 60 days if the checkpoint_retention_time parameter is not specified in the CREATE_CAPTURE procedure, or if you use the DBMS_STREAMS_ADM package to create the capture process. The CHECKPOINT_RETENTION_TIME column in the DBA_CAPTURE view displays the current checkpoint retention time for a capture process.
You can change the checkpoint retention time for a capture process by specifying a new time period in the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package. If you do not want checkpoints for a capture process to be purged automatically, then specify DBMS_CAPTURE_ADM.INFINITE for the checkpoint_retention_time parameter in CREATE_CAPTURE or ALTER_CAPTURE.
|
Note:
To specify a checkpoint retention time for a capture process, the compatibility level of the database running the capture process must be 10.2.0 or higher. If the compatibility level is lower than 10.2.0 for a database, then the checkpoint retention time for all capture processes running on the database is infinite. |
A New First SCN Value and Purged LogMiner Data Dictionary Information
If you reset the first SCN value for an existing capture process, or if the first SCN is reset automatically when checkpoints are purged, then Oracle automatically purges LogMiner data dictionary information before the new first SCN setting. If the start SCN for a capture process corresponds to redo information that has been purged, then Oracle Database automatically resets the start SCN to the same value as the first SCN. However, if the start SCN is higher than the new first SCN setting, then the start SCN remains unchanged.
Figure 7-2 shows how Oracle automatically purges LogMiner data dictionary information prior to a new first SCN setting, and how the start SCN is not changed if it is higher than the new first SCN setting.
Given this example, if the first SCN is reset again to a value higher than the start SCN value for a capture process, then the start SCN no longer corresponds to existing information in the LogMiner data dictionary. Figure 7-3 shows how Oracle Database resets the start SCN automatically if it is lower than a new first SCN setting.
As you can see, the first SCN and start SCN for a capture process can continually increase over time, and, as the first SCN moves forward, it might no longer correspond to an SCN established by the DBMS_CAPTURE_ADM.BUILD procedure.
ARCHIVELOG Mode and a Capture Process
The following list describes how different types of capture processes read the redo data:
-
A local capture process reads from the redo log buffer whenever possible. If it cannot read from the log buffer, then it reads from the online redo logs. If it cannot read from the log buffer or the online redo logs, then it reads from the archived redo log files. Therefore, the source database must be running in ARCHIVELOG mode when a local capture process is configured to capture changes.
-
A real-time downstream capture process reads online redo data from its source database whenever possible and archived redo log files that contain redo data from the source database otherwise. In this case, the redo data from the source database is stored in the standby redo log at the downstream database, and the archiver at the downstream database archives the redo data in the standby redo log. Therefore, both the source database and the downstream database must be running in ARCHIVELOG mode when a real-time downstream capture process is configured to capture changes.
-
An archived-log downstream capture process always reads archived redo log files from its source database. Therefore, the source database must be running in ARCHIVELOG mode when an archived-log downstream capture process is configured to capture changes.
You can query the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view to determine the required checkpoint SCN for a capture process. When the capture process is restarted, it scans the redo log from the required checkpoint SCN forward. Therefore, the redo log file that includes the required checkpoint SCN, and all subsequent redo log files, must be available to the capture process.
You must keep an archived redo log file available until you are certain that no capture process will need that file. The first SCN for a capture process can be reset to a higher value, but it cannot be reset to a lower value. Therefore, a capture process will never need the redo log files that contain information before its first SCN. Query the DBA_LOGMNR_PURGED_LOG data dictionary view to determine which archived redo log files will never be needed by any capture process.
When a local capture process falls behind, there is a seamless transition from reading an online redo log to reading an archived redo log, and, when a local capture process catches up, there is a seamless transition from reading an archived redo log to reading an online redo log. Similarly, when a real-time downstream capture process falls behind, there is a seamless transition from reading the standby redo log to reading an archived redo log, and, when a real-time downstream capture process catches up, there is a seamless transition from reading an archived redo log to reading the standby redo log.
|
Note:
At a downstream database in a downstream capture configuration, log files from a remote source database should be kept separate from local database log files. In addition, if the downstream database contains log files from multiple source databases, then the log files from each source database should be kept separate from each other. |
Capture Process Creation
You can create a capture process using a procedure in the DBMS_STREAMS_ADM package or the DBMS_CAPTURE_ADM package. Using a procedure the DBMS_STREAMS_ADM package to create a capture process is simpler because the procedure automatically uses defaults for some configuration options. In addition, when you use a procedure in the DBMS_STREAMS_ADM package, a rule set is created for the capture process, and rules can be added to the rule set automatically. The rule set is a positive rule set if the inclusion_rule parameter is set to TRUE (the default) in the procedure, or it is a negative rule set if the inclusion_rule parameter is set to FALSE in the procedure.
Alternatively, using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package to create a capture process is more flexible, and you create one or more rule sets and rules for the capture process either before or after it is created. You can use the procedures in the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package to add rules to a rule set for the capture process. To create a capture process at a downstream database, you must use the DBMS_CAPTURE_ADM package.
When you create a capture process using a procedure in the DBMS_STREAMS_ADM package and generate one or more rules in the positive rule set for the capture process, the objects for which changes are captured are prepared for instantiation automatically, unless it is a downstream capture process and there is no database link from the downstream database to the source database.
When you create a capture process using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package, you should prepare for instantiation any objects for which you plan to capture changes. Prepare these objects for instantiations as soon as possible after capture process creation. You can prepare objects for instantiation using one of the following procedures in the DBMS_CAPTURE_ADM package:
-
PREPARE_TABLE_INSTANTIATION prepares a single table for instantiation.
-
PREPARE_SCHEMA_INSTANTIATION prepares for instantiation all of the objects in a schema and all objects added to the schema in the future.
-
PREPARE_GLOBAL_INSTANTIATION prepares for instantiation all of the objects in a database and all objects added to the database in the future.
These procedures can also enable supplemental logging for the key columns or for all columns in the table or tables prepared for instantiation.
|
Note:
After creating a capture process, avoid changing the DBID or global name of the source database for the capture process. If you change either the DBID or global name of the source database, then the capture process must be dropped and re-created. |
The LogMiner Data Dictionary for a Capture Process
A capture process requires a data dictionary that is separate from the primary data dictionary for the source database. This separate data dictionary is called a LogMiner data dictionary. There can be more than one LogMiner data dictionary for a particular source database. If there are multiple capture processes capturing changes from the source database, then two or more capture processes can share a LogMiner data dictionary, or each capture process can have its own LogMiner data dictionary. If the LogMiner data dictionary that is needed by a capture process does not exist, then the capture process populates it using information in the redo log when the capture process is started for the first time.
The DBMS_CAPTURE_ADM.BUILD procedure extracts data dictionary information to the redo log, and this procedure must be run at least once on the source database before any capture process configured to capture changes originating at the source database is started. The extracted data dictionary information in the redo log is consistent with the primary data dictionary at the time when the DBMS_CAPTURE_ADM.BUILD procedure is run. This procedure also identifies a valid first SCN value that you can use to create a capture process.
You can perform a build of data dictionary information in the redo log multiple times, and a particular build might or might not be used by a capture process to create a LogMiner data dictionary. The amount of information extracted to a redo log when you run the BUILD procedure depends on the number of database objects in the database. Typically, the BUILD procedure generates a large amount of redo data that a capture process must scan subsequently. Therefore, you should run the BUILD procedure only when necessary.
In most cases, if a build is required when a capture process is created using a procedure in the DBMS_STREAMS_ADM or DBMS_CAPTURE_ADM package, then the procedure runs the BUILD procedure automatically. However, the BUILD procedure is not run automatically during capture process creation in the following cases:
-
You use CREATE_CAPTURE and specify a non-NULL value for the first_scn parameter. In this case, the specified first SCN must correspond to a previous build.
-
You create a downstream capture process that does not use a database link. In this case, the command at the downstream database cannot communicate with the source database to run the BUILD procedure automatically. Therefore, you must run it manually on the source database and specify the first SCN that corresponds to the build during capture process creation.
A capture process requires a LogMiner data dictionary because the information in the primary data dictionary might not apply to the changes being captured from the redo log. These changes might have occurred minutes, hours, or even days before they are captured by a capture process. For example, consider the following scenario:
-
A capture process is configuredh to capture changes to tables.
-
A database administrator stops the capture process. When the capture process is stopped, it records the SCN of the change it was currently capturing.
-
User applications continue to make changes to the tables while the capture process is stopped.
-
The capture process is restarted three hours after it was stopped.
In this case, to ensure data consistency, the capture process must begin capturing changes in the redo log at the time when it was stopped. The capture process starts capturing changes at the SCN that it recorded when it was stopped.
The redo log contains raw data. It does not contain database object names and column names in tables. Instead, it uses object numbers and internal column numbers for database objects and columns, respectively. Therefore, when a change is captured, a capture process must reference a data dictionary to determine the details of the change.
Because a LogMiner data dictionary might be populated when a capture process is started for the first time, it might take some time to start capturing changes. The amount of time required depends on the number of database objects in the database. You can query the STATE column in the V$STREAMS_CAPTURE dynamic performance view to monitor the progress while a capture process is processing a data dictionary build.
Scenario Illustrating Why a Capture Process Needs a LogMiner Data Dictionary
Consider a scenario in which a capture process has been configured to capture changes to table t1, which has columns a and b, and the following changes are made to this table at three different points in time:
Time 1: Insert values a=7 and b=15.
Time 2: Add column c.
Time 3: Drop column b.
If for some reason the capture process is capturing changes from an earlier time, then the primary data dictionary and the relevant version in the LogMiner data dictionary contain different information. Table 7-1 illustrates how the information in the LogMiner data dictionary is used when the current time is different than the change capturing time.
Assume that the capture process captures the change resulting from the insert at time 1 when the actual time is time 3. If the capture process used the primary data dictionary, then it might assume that a value of 7 was inserted into column a and a value of 15 was inserted into column c, because those are the two columns for table t1 at time 3 in the primary data dictionary. However, a value of 15 actually was inserted into column b, not column c.
Because the capture process uses the LogMiner data dictionary, the error is avoided. The LogMiner data dictionary is synchronized with the capture process and continues to record that table t1 has columns a and b at time 1. So, the captured change specifies that a value of 15 was inserted into column b.
Multiple Capture Processes for the Same Source Database
If one or more capture processes are capturing changes made to a source database, and you want to create a capture process that captures changes to the same source database, then the new capture process can either create a LogMiner data dictionary or share one of the existing LogMiner data dictionaries with one or more other capture processes.
Whether a new LogMiner data dictionary is created for a new capture process depends on the setting for the first_scn parameter when you run CREATE_CAPTURE to create a capture process.
If multiple LogMiner data dictionaries exist, and you specify NULL for the first_scn parameter during capture process creation, then the new capture process automatically attempts to share the LogMiner data dictionary of one of the existing capture processes that has taken at least one checkpoint. You can view the maximum checkpoint SCN for all existing capture processes by querying the MAX_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view. During capture process creation, if the first_scn parameter is NULL and the start_scn parameter is non-NULL, then an error is raised if the start_scn parameter setting is lower than all of the first SCN values for all existing capture processes.
If multiple LogMiner data dictionaries exist, and you specify a non-NULL value for the first_scn parameter during capture process creation, then the new capture process creates a new LogMiner data dictionary the first time it is started. In this case, before you create the new capture process, you must run the BUILD procedure in the DBMS_CAPTURE_ADM package on the source database. The BUILD procedure generates a corresponding valid first SCN value that you can specify when you create the new capture process.
You can find a first SCN generated by the BUILD procedure by running the following query:
COLUMN FIRST_CHANGE# HEADING 'First SCN' FORMAT 999999999
COLUMN NAME HEADING 'Log File Name' FORMAT A50
SELECT DISTINCT FIRST_CHANGE#, NAME FROM V$ARCHIVED_LOG
WHERE DICTIONARY_BEGIN = 'YES';
This query can return more than one row if the BUILD procedure was run more than once.
The most important factor to consider when deciding whether a new capture process should share an existing LogMiner data dictionary or create one is the difference between the maximum checkpoint SCN values of the existing capture processes and the start SCN of the new capture process. If the new capture process shares a LogMiner data dictionary, then it must scan the redo log from the point of the maximum checkpoint SCN of the shared LogMiner data dictionary onward, even though the new capture process cannot capture changes before its first SCN. If the start SCN of the new capture process is much higher than the maximum checkpoint SCN of the existing capture process, then the new capture process must scan a large amount of redo data before it reaches its start SCN.
A capture process creates a new LogMiner data dictionary when the first_scn parameter is non-NULL during capture process creation. Follow these guidelines when you decide whether a new capture process should share an existing LogMiner data dictionary or create one:
-
If one or more maximum checkpoint SCN values is greater than the start SCN you want to specify, and if this start SCN is greater than the first SCN of one or more existing capture processes, then it might be better to share the LogMiner data dictionary of an existing capture process. In this case, you can assume there is a checkpoint SCN that is less than the start SCN and that the difference between this checkpoint SCN and the start SCN is small. The new capture process will begin scanning the redo log from this checkpoint SCN and will catch up to the start SCN quickly.
-
If no maximum checkpoint SCN is greater than the start SCN, and if the difference between the maximum checkpoint SCN and the start SCN is small, then it might be better to share the LogMiner data dictionary of an existing capture process. The new capture process will begin scanning the redo log from the maximum checkpoint SCN, but it will catch up to the start SCN quickly.
-
If no maximum checkpoint SCN is greater than the start SCN, and if the difference between the highest maximum checkpoint SCN and the start SCN is large, then it might take a long time for the capture process to catch up to the start SCN. In this case, it might be better for the new capture process to create a LogMiner data dictionary. It will take some time to create the new LogMiner data dictionary when the new capture process is first started, but the capture process can specify the same value for its first SCN and start SCN, and thereby avoid scanning a large amount of redo data unnecessarily.
Figure 7-4 illustrates these guidelines.
|
Note:
-
If you create a capture process using one of the procedures in the DBMS_STREAMS_ADM package, then it is the same as specifying NULL for the first_scn and start_scn parameters in the CREATE_CAPTURE procedure.
-
You must prepare database objects for instantiation if a new capture process will capture changes made to these database objects. This requirement holds even if the new capture process shares a LogMiner data dictionary with one or more other capture processes for which these database objects have been prepared for instantiation.
|
The Oracle Streams Data Dictionary
Propagations and apply processes use an Oracle Streams data dictionary to keep track of the database objects from a particular source database. An Oracle Streams data dictionary is populated whenever one or more database objects are prepared for instantiation at a source database. Specifically, when a database object is prepared for instantiation, it is recorded in the redo log. When a capture process scans the redo log, it uses this information to populate the local Oracle Streams data dictionary for the source database. For local capture, this Oracle Streams data dictionary is at the source database. For downstream capture, this Oracle Streams data dictionary is at the downstream database.
When you prepare a database object for instantiation, you are informing Oracle Streams that information about the database object is needed by propagations that propagate changes to the database object and apply processes that apply changes to the database object. Any database that propagates or applies these changes requires an Oracle Streams data dictionary for the source database where the changes originated.
After an object has been prepared for instantiation, the local Oracle Streams data dictionary is updated when a DDL statement on the object is processed by a capture process. In addition, an internal message containing information about this DDL statement is captured and placed in the queue for the capture process. Propagations can then propagate these internal messages to destination queues at databases.
An Oracle Streams data dictionary is multiversioned. If a database has multiple propagations and apply processes, then all of them use the same Oracle Streams data dictionary for a particular source database. A database can contain only one Oracle Streams data dictionary for a particular source database, but it can contain multiple Oracle Streams data dictionaries if it propagates or applies changes from multiple source databases.
Capture Process Rule Evaluation
A capture process evaluates changes it finds in the redo log against its positive and negative rule sets. The capture process evaluates a change against the negative rule set first. If one or more rules in the negative rule set evaluate to TRUE for the change, then the change is discarded, but if no rule in the negative rule set evaluates to TRUE for the change, then the change satisfies the negative rule set. When a change satisfies the negative rule set for a capture process, the capture process evaluates the change against its positive rule set. If one or more rules in the positive rule set evaluate to TRUE for the change, then the change satisfies the positive rule set, but if no rule in the positive rule set evaluates to TRUE for the change, then the change is discarded. If a capture process only has one rule set, then it evaluates changes against this one rule set only.
A running capture process completes the following series of actions to capture changes:
-
Finds changes in the redo log.
-
Performs prefiltering of the changes in the redo log. During this step, a capture process evaluates rules in its rule sets at a basic level to place changes found in the redo log into two categories: changes that should be converted into LCRs and changes that should not be converted into LCRs. Prefiltering is done in two phases. In the first phase, information that can be evaluated during prefiltering includes schema name, object name, and command type. If more information is needed to determine whether a change should be converted into an LCR, then information that can be evaluated during the second phase of prefiltering includes tag values and column values when appropriate.
Prefiltering is a safe optimization done with incomplete information. This step identifies relevant changes to be processed subsequently, such that:
-
A capture process converts a change into an LCR if the change satisfies the capture process rule sets. In this case, proceed to Step 3.
-
A capture process does not convert a change into an LCR if the change does not satisfy the capture process rule sets.
-
Regarding MAYBE evaluations, the rule evaluation proceeds as follows:
-
If a change evaluates to MAYBE against both the positive and negative rule set for a capture process, then the capture process might not have enough information to determine whether the change will definitely satisfy both of its rule sets. In this case, further evaluation is necessary. Proceed to Step 3.
-
If the change evaluates to FALSE against the negative rule set and MAYBE against the positive rule set for the capture process, then the capture process might not have enough information to determine whether the change will definitely satisfy both of its rule sets. In this case, further evaluation is necessary. Proceed to Step 3.
-
If the change evaluates to MAYBE against the negative rule set and TRUE against the positive rule set for the capture process, then the capture process might not have enough information to determine whether the change will definitely satisfy both of its rule sets. In this case, further evaluation is necessary. Proceed to Step 3.
-
If the change evaluates to TRUE against the negative rule set and MAYBE against the positive rule set for the capture process, then the capture process discards the change.
-
If the change evaluates to MAYBE against the negative rule set and FALSE against the positive rule set for the capture process, then the capture process discards the change.
-
Converts changes that satisfy, or might satisfy, the capture process rule sets into LCRs based on prefiltering.
-
Performs LCR filtering. During this step, a capture process evaluates rules regarding information in each LCR to separate the LCRs into two categories: LCRs that should be enqueued and LCRs that should be discarded.
-
Discards the LCRs that should not be enqueued because they did not satisfy the capture process rule sets.
-
Enqueues the remaining captured LCRs into the queue associated with the capture process.
For example, suppose the following rule is defined in the positive rule set for a capture process: Capture changes to the hr.employees table where the department_id is 50. No other rules are defined for the capture process, and the parallelism parameter for the capture process is set to 1.
Given this rule, suppose an UPDATE statement on the hr.employees table changes 50 rows in the table. The capture process performs the following series of actions for each row change:
-
Finds the next change resulting from the UPDATE statement in the redo log.
-
Determines that the change resulted from an UPDATE statement to the hr.employees table and must be captured. If the change was made to a different table, then the capture process ignores the change.
-
Captures the change and converts it into an LCR.
-
Filters the LCR to determine whether it involves a row where the department_id is 50.
-
Either enqueues the LCR into the queue associated with the capture process if it involves a row where the department_id is 50, or discards the LCR if it involves a row where the department_id is not 50 or is missing.
Figure 7-5 illustrates capture process rule evaluation in a flowchart.
PK< PK &A OEBPS/strms_apmon.htm
26 Monitoring Oracle Streams Apply Processes
The following topics describe monitoring Oracle Streams apply processes:
Determining the Queue, Rule Sets, and Status for Each Apply Process
You can determine the following information for each apply process in a database by running the query in this section:
-
The apply process name
-
The name of the queue used by the apply process
-
The name of the positive rule set used by the apply process
-
The name of the negative rule set used by the apply process
-
The status of the apply process, either ENABLED, DISABLED, or ABORTED
To display this general information about each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A15
SELECT APPLY_NAME,
QUEUE_NAME,
RULE_SET_NAME,
NEGATIVE_RULE_SET_NAME,
STATUS
FROM DBA_APPLY;
Your output looks similar to the following:
Apply Apply Apply
Process Process Positive Negative Process
Name Queue Rule Set Rule Set Status
--------------- --------------- --------------- --------------- ---------------
STRM01_APPLY STREAMS_QUEUE RULESET$_36 ENABLED
APPLY_EMP STREAMS_QUEUE RULESET$_16 DISABLED
APPLY STREAMS_QUEUE RULESET$_21 RULESET$_23 ENABLED
If the status of an apply process is ABORTED, then you can query the ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_APPLY data dictionary view to determine the error. These columns are populated when an apply process aborts or when an apply process is disabled after reaching a limit. These columns are cleared when an apply process is restarted.
|
Note:
The ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_APPLY data dictionary view are not related to the information in the DBA_APPLY_ERROR data dictionary view. |
Displaying General Information About Each Apply Process
You can display the following general information about each apply process in a database by running the query in this section:
To display this general information about each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN APPLY_CAPTURED HEADING 'Applies Captured LCRs?' FORMAT A22
COLUMN APPLY_USER HEADING 'Apply User' FORMAT A20
SELECT APPLY_NAME, APPLY_CAPTURED, APPLY_USER
FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name Applies Captured LCRs? Apply User
-------------------- ---------------------- --------------------
STRM01_APPLY YES STRMADMIN
SYNC_APPLY NO STRMADMIN
Listing the Parameter Settings for Each Apply Process
The following query displays the current setting for each apply process parameter for each apply process in a database:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN PARAMETER HEADING 'Parameter' FORMAT A30
COLUMN VALUE HEADING 'Value' FORMAT A22
COLUMN SET_BY_USER HEADING 'Set by|User?' FORMAT A10
SELECT APPLY_NAME,
PARAMETER,
VALUE,
SET_BY_USER
FROM DBA_APPLY_PARAMETERS;
Your output looks similar to the following:
Apply Process Set by
Name Parameter Value User?
--------------- ------------------------------ ---------------------- ----------
APPLY$_DB_3 ALLOW_DUPLICATE_ROWS N NO
APPLY$_DB_3 APPLY_SEQUENCE_NEXTVAL N NO
APPLY$_DB_3 COMMIT_SERIALIZATION DEPENDENT_TRANSACTIONS NO
APPLY$_DB_3 COMPARE_KEY_ONLY N NO
APPLY$_DB_3 DISABLE_ON_ERROR Y NO
APPLY$_DB_3 DISABLE_ON_LIMIT N NO
APPLY$_DB_3 GROUPTRANSOPS 250 NO
APPLY$_DB_3 IGNORE_TRANSACTION NO
APPLY$_DB_3 MAXIMUM_SCN INFINITE NO
APPLY$_DB_3 MAX_SGA_SIZE INFINITE NO
APPLY$_DB_3 PARALLELISM 4 NO
APPLY$_DB_3 PRESERVE_ENCRYPTION Y NO
APPLY$_DB_3 RTRIM_ON_IMPLICIT_CONVERSION Y NO
APPLY$_DB_3 STARTUP_SECONDS 0 NO
APPLY$_DB_3 TIME_LIMIT INFINITE NO
APPLY$_DB_3 TRACE_LEVEL 0 NO
APPLY$_DB_3 TRANSACTION_LIMIT INFINITE NO
APPLY$_DB_3 TXN_AGE_SPILL_THRESHOLD 900 NO
APPLY$_DB_3 TXN_LCR_SPILL_THRESHOLD 10000 NO
APPLY$_DB_3 WRITE_ALERT_LOG Y NO
|
Note:
If the Set by User? column is NO for a parameter, then the parameter is set to its default value. If the Set by User? column is YES for a parameter, then the parameter was set by a user and might or might not be set to its default value. |
Displaying Information About Apply Handlers
This section contains instructions for displaying information about the apply handlers for apply processes.
This section contains these topics:
Displaying Information About DML Handlers
The following sections contain instructions for displaying information about DML handlers:
Displaying Information About All DML Handlers
You can display the following information about all of the DML handlers in a database, including all statement DML handlers and all procedure DML handlers:
-
The owner and name of the table for which the DML handler is set
-
The operation for which the DML handler is set
-
The name of the DML handler
-
The type of the DML handler, either statement or procedure
-
The name of the apply process that uses the DML handler
To display this information for each DML handler in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A7
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A11
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN HANDLER HEADING 'DML Handler' FORMAT A13
COLUMN HANDLER_TYPE HEADING 'Handler|Type' FORMAT A9
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
SELECT OBJECT_OWNER,
OBJECT_NAME,
OPERATION_NAME,
NVL(USER_PROCEDURE,HANDLER_NAME) Handler,
DECODE(HANDLER_TYPE,'PROCEDURE HANDLER','PROCEDURE','STMT HANDLER',
'STATEMENT','UNKNOWN') HANDLER_TYPE,
APPLY_NAME
FROM DBA_APPLY_DML_HANDLERS
WHERE ERROR_HANDLER = 'N' AND
APPLY_DATABASE_LINK IS NULL
ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Apply
Table Handler Process
Owner Table Name Operation DML Handler Type Name
------- ----------- --------- ------------- --------- ---------------
HR DEPARTMENTS UPDATE "STRMADMIN"." PROCEDURE
SQL_GEN_DEP"
HR JOBS UPDATE TRACK_JOBS STATEMENT APPLY$_PROD_25
OE ORDERS INSERT MODIFY_ORDERS STATEMENT APPLY$_PROD_25
Because Apply Process Name is NULL for the strmadmin.sql_gen_dep procedure DML handler, this handler is a general handler that runs for all of the local apply processes.
Displaying Information About Statement DML Handlers
The following sections contain queries that display information about the statement DML handlers in a database:
Displaying the Statement DML Handlers in a Database
You can display the following information about the statement DML handlers in a database:
-
The name of the statement DML handler
-
The comment for the statement DML handler
-
The time when the statement DML handler was created
-
The time when the statement DML handler was last modified
To display this information for each statement DML handler in a database, run the following query:
COLUMN HANDLER_NAME HEADING 'Handler Name' FORMAT A15
COLUMN HANDLER_COMMENT HEADING 'Comment' FORMAT A35
COLUMN CREATION_TIME HEADING 'Creation|Time' FORMAT A10
COLUMN MODIFICATION_TIME HEADING 'Last|Change|Time' FORMAT A10
SELECT HANDLER_NAME,
HANDLER_COMMENT,
CREATION_TIME,
MODIFICATION_TIME
FROM DBA_STREAMS_STMT_HANDLERS
ORDER BY HANDLER_NAME;
Your output looks similar to the following:
Last
Creation Change
Handler Name Comment Time Time
--------------- ----------------------------------- ---------- ----------
MODIFY_ORDERS Modifies inserts into the orders ta 12-MAR-09
ble 07.59.56.9
46180 AM
TRACK_JOBS Tracks updates to the jobs table 11-MAR-09
10.47.52.7
76489 AM
When the MODIFICATION_TIME is NULL, shown in this output by Last Change Time, it indicates that the handler has not been modified since its creation.
Displaying the Statement DML Handlers Used by Each Apply Process
When you specify a statement DML handler using the ADD_STMT_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you can either specify that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally. If a statement DML handler for an operation on a table is used by a specific apply process, and another statement DML handler is a general handler for the same operation on the same table, then both handlers are invoked when an apply process dequeues a row LCR with the operation on the table. Each statement DML handler receives the original row LCR, and the statement DML handlers can execute in any order.
You can display the following information about the statement DML handlers used by the apply processes in the database:
-
The owner and name of the table for which the statement DML handler is set
-
The operation for which the statement DML handler is set
-
The name of the apply process that uses the statement DML handler
-
The name of the statement DML handler
To display this information for the statement DML handlers used by each apply process, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A10
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN HANDLER_NAME HEADING 'Statement DML|Handler Name' FORMAT A30
SELECT OBJECT_OWNER,
OBJECT_NAME,
OPERATION_NAME,
APPLY_NAME,
HANDLER_NAME
FROM DBA_APPLY_DML_HANDLERS
WHERE HANDLER_TYPE='STMT HANDLER'
ORDER BY OBJECT_OWNER, OBJECT_NAME, OPERATION_NAME;
Your output looks similar to the following:
Table Apply Process Statement DML
Owner Table Name Operation Name Handler Name
---------- ---------- --------- --------------- ------------------------------
HR JOBS UPDATE APPLY$_PROD_25 TRACK_JOBS
OE ORDERS INSERT APPLY$_PROD_25 MODIFY_ORDERS
When Apply Process Name is NULL for a statement DML handler, the handler is a general handler that runs for all of the local apply processes.
Displaying All of the Statements in Statement DML Handlers
The query in this section displays the following information about the statements in statement DML handlers in a database:
-
The name of the statement DML handler that includes each statement
-
The execution order of each statement
-
The text of each statement
To display this information, run the following query:
COLUMN HANDLER_NAME HEADING 'Statement|Handler' FORMAT A15
COLUMN EXECUTION_SEQUENCE HEADING 'Execution|Sequence' FORMAT 999999
COLUMN STATEMENT HEADING 'Statement' FORMAT A50
SET LONG 8000
SET PAGES 8000
SELECT HANDLER_NAME,
EXECUTION_SEQUENCE,
STATEMENT
FROM DBA_STREAMS_STMTS
ORDER BY HANDLER_NAME, EXECUTION_SEQUENCE;
Your output looks similar to the following:
Statement Execution
Handler Sequence Statement
--------------- --------- --------------------------------------------------
MODIFY_ORDERS 1 INSERT INTO oe.orders(
order_id,
order_date,
order_mode,
customer_id,
order_status,
order_total,
sales_rep_id,
promotion_id)
VALUES(
:new.order_id,
:new.order_date,
:new.order_mode,
:new.customer_id,
DECODE(:new.order_status, 1, 2, :new.
order_status),
:new.order_total,
:new.sales_rep_id,
:new.promotion_id)
TRACK_JOBS 10 :lcr.execute TRUE
TRACK_JOBS 20 INSERT INTO hr.track_jobs(
change_id,
job_id,
job_title,
min_salary_old,
min_salary_new,
max_salary_old,
max_salary_new,
timestamp)
VALUES(
hr.track_jobs_seq.NEXTVAL,
:new.job_id,
:new.job_title,
:old.min_salary,
:new.min_salary,
:old.max_salary,
:new.max_salary,
:source_time)
Displaying Information About Procedure DML Handlers
When you specify a local procedure DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, you can either specify that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally, when appropriate. A specific procedure DML handler takes precedence over a generic procedure DML handler. A DML handler is run for a specified operation on a specific table.
To display the procedure DML handler for each apply process in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A11
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A15
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A25
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
SELECT OBJECT_OWNER,
OBJECT_NAME,
OPERATION_NAME,
USER_PROCEDURE,
APPLY_NAME
FROM DBA_APPLY_DML_HANDLERS
WHERE ERROR_HANDLER = 'N' AND
USER_PROCEDURE IS NOT NULL
ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Table Apply Process
Owner Table Name Operation Handler Procedure Name
----------- --------------- --------- ------------------------- ---------------
HR DEPARTMENTS UPDATE "STRMADMIN"."SQL_GEN_DEP"
Because Apply Process Name is NULL for the strmadmin.sql_gen_dep procedure DML handler, this handler is a general handler that runs for all of the local apply processes.
Displaying the DDL Handler for Each Apply Process
To display the DDL handler for each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN DDL_HANDLER HEADING 'DDL Handler' FORMAT A40
SELECT APPLY_NAME, DDL_HANDLER FROM DBA_APPLY;
Your output looks similar to the following:
Apply Process Name DDL Handler
-------------------- ----------------------------------------
STREP01_APPLY "STRMADMIN"."HISTORY_DDL"
Displaying All of the Error Handlers for Local Apply Processes
When you specify a local error handler using the SET_DML_HANDLER p rocedure in the DBMS_APPLY_ADM package at a destination database, you can specify either that the handler runs for a specific apply process or that the handler is a general handler that runs for all apply processes in the database that apply changes locally when an error is raised by an apply process. A specific error handler takes precedence over a generic error handler. An error handler is run for a specified operation on a specific table.
To display the error handler for each apply process that applies changes locally in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A10
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A30
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
SELECT OBJECT_OWNER,
OBJECT_NAME,
OPERATION_NAME,
USER_PROCEDURE,
APPLY_NAME
FROM DBA_APPLY_DML_HANDLERS
WHERE ERROR_HANDLER = 'Y'
ORDER BY OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Table Apply Process
Owner Table Name Operation Handler Procedure Name
----- ---------- ---------- ------------------------------ --------------
HR REGIONS INSERT "STRMADMIN"."ERRORS_PKG"."REGI
ONS_PK_ERROR"
Apply Process Name is NULL for the strmadmin.errors_pkg.regions_pk_error error handler. Therefore, this handler is a general handler that runs for all of the local apply processes.
Displaying the Message Handler for Each Apply Process
To display each message handler in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN MESSAGE_HANDLER HEADING 'Message Handler' FORMAT A20
SELECT APPLY_NAME, MESSAGE_HANDLER FROM DBA_APPLY
WHERE MESSAGE_HANDLER IS NOT NULL;
Your output looks similar to the following:
Apply Process Name Message Handler
-------------------- --------------------
STRM03_APPLY "OE"."MES_HANDLER"
Displaying the Precommit Handler for Each Apply Process
You can display the following information about each precommit handler used by an apply process in a database by running the query in this section:
To display each this information for each precommit handler in the database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A15
COLUMN PRECOMMIT_HANDLER HEADING 'Precommit Handler' FORMAT A30
COLUMN APPLY_CAPTURED HEADING 'Applies Captured|Messages?' FORMAT A20
SELECT APPLY_NAME, PRECOMMIT_HANDLER, APPLY_CAPTURED
FROM DBA_APPLY
WHERE PRECOMMIT_HANDLER IS NOT NULL;
Your output looks similar to the following:
Applies Captured
Apply Process Name Precommit Handler Messages?
-------------------- ------------------------------ --------------------
STRM01_APPLY "STRMADMIN"."HISTORY_COMMIT" YES
Displaying Session Information About Each Apply Process
The query in this section displays the following session information about each session associated with a apply process in a database:
-
The apply process component
-
The session identifier
-
The serial number
-
The operating system process identification number
-
The process names of the coordinator process, the reader process, and the apply servers
To display this information for each capture process in a database, run the following query:
COLUMN ACTION HEADING 'Apply Process 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 'Process|Names' 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 ='Streams' AND
ACTION LIKE '%Apply%';
Your output looks similar to the following:
Session
Serial Operating System Process
Apply Process Component Session ID Number Process Number Names
------------------------------ ---------- --------- ----------------- -------
APPLY$_EMDBB_3 - Apply Coordin 17 3040 9863 AP01
ator
APPLY$_EMDBB_3 - Apply Server 58 52788 9869 AS02
APPLY$_EMDBB_3 - Apply Reader 63 21 9865 AS01
APPLY$_EMDBB_3 - Apply Server 64 37 9872 AS03
APPLY$_EMDBB_3 - Apply Server 67 22 9875 AS04
APPLY$_EMDBB_3 - Apply Server 69 1 9877 AS05
Displaying Information About the Reader Server for Each Apply Process
The reader server for an apply process dequeues messages from the queue. The reader server is a process that computes dependencies between LCRs and assembles messages into transactions. The reader server then returns the assembled transactions to the coordinator, which assigns them to idle apply servers.
The query in this section displays the following information about the reader server for each apply process:
-
The name of the apply process
-
The type of messages dequeued by the reader server. An apply process either can dequeue either captured LCRs, or an apply process can dequeue persistent LCRs and persistent user messages.
-
The name of the process used by the reader server. The process name is in the form ASnn, where nn can be letters and numbers.
-
The current state of the reader server
See "Reader Server States".
-
The total number of messages dequeued by the reader server since the last time the apply process was started
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN APPLY_CAPTURED HEADING 'Dequeues Captured|Messages?' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Process|Name' FORMAT A7
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Messages|Dequeued' FORMAT 99999999
SELECT r.APPLY_NAME,
ap.APPLY_CAPTURED,
SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
r.STATE,
r.TOTAL_MESSAGES_DEQUEUED
FROM V$STREAMS_APPLY_READER r, V$SESSION s, DBA_APPLY ap
WHERE r.SID = s.SID AND
r.SERIAL# = s.SERIAL# AND
r.APPLY_NAME = ap.APPLY_NAME;
Your output looks similar to the following:
Apply Process Dequeues Captured Process Total Messages
Name Messages? Name State Dequeued
--------------- ----------------- ------- ----------------- --------------
APPLY_SPOKE YES AS01 DEQUEUE MESSAGES 54
Monitoring Transactions and Messages Spilled by Each Apply Process
If the txn_lcr_spill_threshold apply process parameter is set to a value other than INFINITE, then an apply process can spill messages from memory to hard disk when the number of messages in a transaction exceeds the specified number.
The first query in this section displays the following information about each transaction currently being applied for which the apply process has spilled messages:
-
The name of the apply process
-
The transaction ID of the transaction with spilled messages
-
The system change number (SCN) of the first message in the transaction
-
The number of messages currently spilled in the transaction
To display this information for each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A20
COLUMN 'Transaction ID' HEADING 'Transaction ID' FORMAT A15
COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999
COLUMN MESSAGE_COUNT HEADING 'Message Count' FORMAT 99999999
SELECT APPLY_NAME,
XIDUSN ||'.'||
XIDSLT ||'.'||
XIDSQN "Transaction ID",
FIRST_SCN,
MESSAGE_COUNT
FROM DBA_APPLY_SPILL_TXN;
Your output looks similar to the following:
Apply Name Transaction ID First SCN Message Count
-------------------- --------------- --------- -------------
APPLY_HR 1.42.2277 2246944 100
The next query in this section displays the following information about the messages spilled by the apply processes in the local database:
-
The name of the apply process
-
The total number of messages spilled by the apply process since it last started
-
The amount of time the apply process spent spilling messages, in seconds
To display this information for each apply process in a database, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A15
COLUMN TOTAL_MESSAGES_SPILLED HEADING 'Total|Spilled Messages' FORMAT 99999999
COLUMN ELAPSED_SPILL_TIME HEADING 'Elapsed Time|Spilling Messages' FORMAT 99999999.99
SELECT APPLY_NAME,
TOTAL_MESSAGES_SPILLED,
(ELAPSED_SPILL_TIME/100) ELAPSED_SPILL_TIME
FROM V$STREAMS_APPLY_READER;
Your output looks similar to the following:
Total Elapsed Time
Apply Name Spilled Messages Spilling Messages
--------------- ---------------- -----------------
APPLY_HR 100 2.67
|
Note:
The elapsed time spilling messages is displayed in seconds. The V$STREAMS_APPLY_READER view displays elapsed time in centiseconds by default. A centisecond is one-hundredth of a second. The query in this section divides each elapsed time by one hundred to display the elapsed time in seconds. |
Determining Capture to Dequeue Latency for a Message
The query in this section displays the following information about the last message dequeued by each apply process:
-
The name of the apply process.
-
The latency. For captured LCRs, the latency is the amount of time between when the message was created at a source database and when the message was dequeued by the apply process. For any other type of message, the latency is the amount of time between when the message enqueued at the local database and when the message was dequeued by the apply process.
-
The message creation time. For captured LCRs, the message creation time is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo data at the source database for the message. For messages enqueued by an application or apply process, the message creation time is the last time the message was enqueued. A message can be enqueued one or more additional times by propagations before it reaches an apply process.
-
The time when the message was dequeued by the apply process.
-
The message number of the message that was last dequeued by the apply process.
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATION HEADING 'Message Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 9999999999
SELECT APPLY_NAME,
(DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,
TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,
DEQUEUED_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_READER;
Your output looks similar to the following:
Latency
Apply Process in Dequeued
Name Seconds Message Creation Last Dequeue Time Message Number
----------------- ------- ----------------- -------------------- --------------
APPLY$_STM1_14 1 15:22:15 06/13/05 15:22:16 06/13/05 502129
Displaying General Information About Each Coordinator Process
A coordinator process gets transactions from the reader server and passes these transactions to apply servers. The coordinator process name is APnn, where nn is a coordinator process number.
The query in this section displays the following information about the coordinator process for each apply process:
-
The apply process name
-
The number of the coordinator in the process name APnn, where nn can include letters and numbers
-
The session identifier of the coordinator's session
-
The serial number of the coordinator's session
-
The current state of the coordinator
See "Coordinator Process States".
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display this information for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Coordinator|Process|Name' FORMAT A11
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A21
SELECT c.APPLY_NAME,
SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
c.SID,
c.SERIAL#,
c.STATE
FROM V$STREAMS_APPLY_COORDINATOR c, V$SESSION s
WHERE c.SID = s.SID AND
c.SERIAL# = s.SERIAL#;
Your output looks similar to the following:
Coordinator Session
Apply Process Process Session Serial
Name Name ID Number State
----------------- ----------- ------- ------- ---------------------
APPLY_SPOKE AP01 944 5 IDLE
Displaying Information About Transactions Received and Applied
The query in this section displays the following information about the transactions received, applied, and being applied by each apply process:
-
The apply process name
-
The total number of transactions received by the coordinator process since the apply process was last started
-
The total number of transactions successfully applied by the apply process since the apply process was last started
-
The number of transactions applied by the apply process that resulted in an apply error since the apply process was last started
-
The total number of transactions currently being applied by the apply process
-
The total number of complete transactions that the coordinator process has received but has not yet assigned to any apply servers
-
The total number of transactions received by the coordinator process but ignored because the apply process had already applied the transactions since the apply process was last started
The information displayed by this query is valid only for an enabled apply process.
For example, to display this information for an apply process named apply, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN TOTAL_RECEIVED HEADING 'Total|Trans|Received' FORMAT 99999999
COLUMN TOTAL_APPLIED HEADING 'Total|Trans|Applied' FORMAT 99999999
COLUMN TOTAL_ERRORS HEADING 'Total|Apply|Errors' FORMAT 9999
COLUMN BEING_APPLIED HEADING 'Total|Trans Being|Applied' FORMAT 99999999
COLUMN UNASSIGNED_COMPLETE_TXNS HEADING 'Total|Unnasigned|Trans' FORMAT 99999999
COLUMN TOTAL_IGNORED HEADING 'Total|Trans|Ignored' FORMAT 99999999
SELECT APPLY_NAME,
TOTAL_RECEIVED,
TOTAL_APPLIED,
TOTAL_ERRORS,
(TOTAL_ASSIGNED - (TOTAL_ROLLBACKS + TOTAL_APPLIED)) BEING_APPLIED,
UNASSIGNED_COMPLETE_TXNS,
TOTAL_IGNORED
FROM V$STREAMS_APPLY_COORDINATOR;
Your output looks similar to the following:
Total Total Total Total Total Total
Trans Trans Apply Trans Being Unnasigned Trans
Apply Process Name Received Applied Errors Applied Trans Ignored
-------------------- --------- --------- ------ ----------- ---------- ---------
APPLY_FROM_MULT1 81 73 2 6 4 0
APPLY_FROM_MULT2 114 96 0 14 7 4
Determining the Capture to Apply Latency for a Message for Each Apply Process
This section contains two different queries that show the capture to apply latency for a particular message. That is, these queries show the amount of time between when the message was created at a source database and when the message was applied by an apply process. One query uses the V$STREAMS_APPLY_COORDINATOR dynamic performance view. The other uses the DBA_APPLY_PROGRESS static data dictionary view.
The two queries differ in the following ways:
-
You can use the query on the V$STREAMS_APPLY_COORDINATOR view to determine capture to apply latency for captured LCRs or persistent LCRs. However, the query on the DBA_APPLY_PROGRESS view only returns results for captured LCRs.
-
The apply process must be enabled when you run the query on the V$STREAMS_APPLY_COORDINATOR view, while the apply process can be enabled or disabled when you run the query on the DBA_APPLY_PROGRESS view. Therefore, if the apply process is currently disabled and change capture is performed by a capture process, then run the query on the DBA_APPLY_PROGRESS view to determine the capture to apply latency.
-
The query on the V$STREAMS_APPLY_COORDINATOR view can show the latency for a more recent transaction than the query on the DBA_APPLY_PROGRESS view.
Both queries display the following information about a message applied by each apply process:
-
The apply process name
-
The capture to apply latency for the message
-
The message creation time
For captured LCRs, the message creation time is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo data at the source database for the message.
For persistent LCRs, the message creation time is the time when the LCR was constructed.
-
The time when the message was applied by the apply process
-
The message number of the message
Example V$STREAMS_APPLY_COORDINATOR Query for Latency
Run the following query to display the capture to apply latency using the V$STREAMS_APPLY_COORDINATOR view for a captured LCR or a persistent LCR for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A13
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN HWM_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 9999999999
SELECT APPLY_NAME,
(HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
HWM_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_COORDINATOR;
Your output looks similar to the following:
Apply Process Message
Name Latency in Seconds Message Creation Apply Time Number
------------- ------------------ ----------------- ----------------- -----------
APPLY$_DA_2 2 13:00:10 07/14/10 13:00:12 07/14/10 672733
Example DBA_APPLY_PROGRESS Query for Latency
Run the following query to display the capture to apply latency using the DBA_APPLY_PROGRESS view for a captured LCR for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 9999999999
SELECT APPLY_NAME,
(APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
APPLIED_MESSAGE_NUMBER
FROM DBA_APPLY_PROGRESS;
Your output looks similar to the following:
Applied
Apply Process Message
Name Latency in Seconds Message Creation Apply Time Number
----------------- ------------------ ----------------- ----------------- -------
APPLY$_STM1_14 33 14:05:13 06/13/05 14:05:46 06/13/05 498215
Displaying Information About the Apply Servers for Each Apply Process
An apply process can use one or more apply servers that apply LCRs to database objects as DML statements or DDL statements or pass the LCRs to their appropriate handlers. For non-LCR messages, the apply servers pass the messages to the message handler. Each apply server is a process.
The query in this section displays the following information about the apply servers for each apply process:
-
The name of the apply process.
-
The names of the reader server processes, in order. Each process name is in the form ASnn, where nn can be letters and numbers.
-
The current state of each apply server
See "Apply Server States".
-
The total number of transactions assigned to each apply server since the last time the apply process was started. A transaction can contain more than one message.
-
The total number of messages applied by each apply server since the last time the apply process was started.
The information displayed by this query is valid only for an enabled apply process.
Run the following query to display information about the apply servers for each apply process:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A22
COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_ASSIGNED HEADING 'Total|Transactions|Assigned' FORMAT 99999999
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total|M essages|Applied' FORMAT 99999999
SELECT r.APPLY_NAME,
SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
r.STATE,
r.TOTAL_ASSIGNED,
r.TOTAL_MESSAGES_APPLIED
FROM V$STREAMS_APPLY_SERVER R, V$SESSION S
WHERE r.SID = s.SID AND
r.SERIAL# = s.SERIAL#
ORDER BY r.APPLY_NAME, r.SERVER_ID;
Your output looks similar to the following:
Total Total
Transactions Messages
Apply Process Name Process Name State Assigned Applied
---------------------- ------------ ----------------- ------------ ---------
APPLY$_DA_2 AS02 IDLE 1012 109190
APPLY$_DA_2 AS03 IDLE 996 107568
APPLY$_DA_2 AS04 IDLE 1006 108648
APPLY$_DA_2 AS05 IDLE 987 10659
6
Displaying Effective Apply Parallelism for an Apply Process
In some environments, an apply process might not use all of the apply servers available to it. For example, apply process parallelism can be set to five, but only three apply servers are ever used by the apply process. In this case, the effective apply parallelism is three.
The following query displays the effective apply parallelism for an apply process named apply:
SELECT COUNT(SERVER_ID) "Effective Parallelism"
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'APPLY' AND
TOTAL_MESSAGES_APPLIED > 0;
Your output looks similar to the following:
Effective Parallelism
---------------------
2
This query returned two for the effective parallelism. If parallelism is set to three for the apply process named apply, then one apply server has not been used since the last time the apply process was started.
You can display the total number of messages applied by each apply server by running the following query:
COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total Messages Applied' FORMAT 999999
SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'APPLY'
ORDER BY SERVER_ID;
Your output looks similar to the following:
Apply Server ID Total Messages Applied
--------------- ----------------------
1 2141
2 276
3 0
4 0
In this case, apply servers 3 and 4 have not been used by the apply process since it was last started. If the parallelism setting for an apply process is much higher than the effective parallelism for the apply process, then consider lowering the parallelism setting. For example, if the parallelism setting is 6, but the effective parallelism for the apply process is 2, then consider lowering the setting.
Viewing Rules that Specify a Destination Queue on Apply
You can specify a destination queue for a rule using the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package. If an apply process has such a rule in its positive rule set, and a message satisfies the rule, then the apply process enqueues the message into the destination queue.
To view destination queue settings for rules, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN DESTINATION_QUEUE_NAME HEADING 'Destination Queue' FORMAT A30
SELECT RULE_OWNER, RULE_NAME, DESTINATION_QUEUE_NAME
FROM DBA_APPLY_ENQUEUE;
Your output looks similar to the following:
Rule Owner Rule Name Destination Queue
--------------- --------------- ------------------------------
STRMADMIN DEPARTMENTS17 "STRMADMIN"."STREAMS_QUEUE"
Viewing Rules that Specify No Execution on Apply
You can specify an execution directive for a rule using the SET_EXECUTE procedure in the DBMS_APPLY_ADM package. An execution directive controls whether a message that satisfies the specified rule is executed by an apply process. If an apply process has a rule in its positive rule set with NO for its execution directive, and a message satisfies the rule, then the apply process does not execute the message and does not send the message to any apply handler.
To view each rule with NO for its execution directive, run the following query:
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
SELECT RULE_OWNER, RULE_NAME
FROM DBA_APPLY_EXECUTE
WHERE EXECUTE_EVENT = 'NO';
Your output looks similar to the following:
Rule Owner Rule Name
-------------------- --------------------
STRMADMIN DEPARTMENTS18
Determining Which Apply Processes Use Combined Capture and Apply
A combined capture and apply environment is efficient because the capture process acts as the propagation sender that sends logical change records (LCRs) directly to the propagation receiver.
When an apply process uses combined capture and apply, the following columns in the V$STREAMS_APPLY_READER data dictionary view are populated:
-
PROXY_SID shows the session ID of the propagation receiver
-
PROXY_SERIAL shows the serial number of the propagation receiver
-
PROXY_SPID shows the process identification number of the propagation receiver
-
CAPTURE_BYTES_RECEIVED shows the number of bytes received by the apply process from the capture process since the apply process last started
When an apply process does not use combined capture and apply, the PROXY_SID and PROXY_SERIAL columns are 0 (zero), and the PROXY_SPID and CAPTURE_BYTES_RECEIVED columns are not populated.
To determine whether an apply process uses combined capture and apply, run the following query:
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN PROXY_SID HEADING 'Propagation|Receiver|Session ID' FORMAT 99999999
COLUMN PROXY_SERIAL HEADING 'Propagation|ReceiverSerial|Number' FORMAT 99999999
COLUMN PROXY_SPID HEADING 'Propagation|Receiver|Process ID' FORMAT 99999999999
COLUMN CAPTURE_BYTES_RECEIVED HEADING 'Number of|Bytes Received' FORMAT 9999999999
SELECT APPLY_NAME,
PROXY_SID,
PROXY_SERIAL,
PROXY_SPID,
CAPTURE_BYTES_RECEIVED
FROM V$STREAMS_APPLY_READER;
Your output looks similar to the following:
Propagation Propagation Propagation
Receiver ReceiverSerial Receiver Number of
Apply Process Name Session ID Number Process ID Bytes Received
-------------------- ----------- -------------- ------------ --------------
APPLY_SPOKE1 940 1 22636 4358614
APPLY_SPOKE2 928 4 29154 4310581
This output indicates that the apply_spoke1 apply process uses combined capture and apply. Since it last started, this apply process has received 4358614 bytes from the capture process. The apply_spoke2 apply process also uses combined capture and apply. Since it last started, this apply process has received 4310581 bytes from the capture process.
Displaying the Substitute Key Columns Specified at a Destination Database
You can designate a substitute key at a destination database, which is a column or set of columns that Oracle can use to identify rows in the table during apply. You can use substitute key columns to specify key columns for a table that has no primary key, or they can be used instead of a table's primary key when the table is processed by any apply process at a destination database.
To display all of the substitute key columns specified at a destination database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Table Owner' FORMAT A20
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Substitute Key Name' FORMAT A20
COLUMN APPLY_DATABASE_LINK HEADING 'Database Link|for Remote|Apply' FORMAT A15
SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, APPLY_DATABASE_LINK
FROM DBA_APPLY_KEY_COLUMNS
ORDER BY APPLY_DATABASE_LINK, OBJECT_OWNER, OBJECT_NAME;
Your output looks similar to the following:
Database Link
for Remote
Table Owner Table Name Substitute Key Name Apply
-------------------- -------------------- -------------------- ---------------
HR DEPARTMENTS DEPARTMENT_NAME
HR DEPARTMENTS LOCATION_ID
HR EMPLOYEES FIRST_NAME
HR EMPLOYEES LAST_NAME
HR EMPLOYEES HIRE_DATE
|
Note:
This query shows the database link in the last column if the substitute key columns are for a remote non-Oracle database. The last column is NULL if a substitute key column is specified for the local destination database. |
Monitoring Virtual Dependency Definitions
The following sections contain queries that display information about virtual dependency definitions in a database:
Displaying Value Dependencies
To display the value dependencies in a database, run the following query:
COLUMN DEPENDENCY_NAME HEADING 'Dependency Name' FORMAT A25
COLUMN OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A15
SELECT DEPENDENCY_NAME,
OBJECT_OWNER,
OBJECT_NAME,
COLUMN_NAME
FROM DBA_APPLY_VALUE_DEPENDENCIES;
Your output should look similar to the following:
Dependency Name Object Owner Object Name Column Name
------------------------- --------------- -------------------- ---------------
ORDER_ID_FOREIGN_KEY OE ORDERS ORDER_ID
ORDER_ID_FOREIGN_KEY OE ORDER_ITEMS ORDER_ID
KEY_53_FOREIGN_KEY US_DESIGNS ALL_DESIGNS_SUMMARY KEY_53
KEY_53_FOREIGN_KEY US_DESIGNS DESIGN_53 KEY_53
This output shows the following value dependencies:
-
The order_id_foreign_key value dependency describes a dependency between the order_id column in the oe.orders table and the order_id column in the oe.order_items table.
-
The key_53_foreign_key value dependency describes a dependency between the key_53 column in the us_designs.all_designs_summary table and the key_53 column in the us_designs.design_53 table.
Displaying Object Dependencies
To display the object dependencies in a database, run the following query:
COLUMN OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A15
COLUMN PARENT_OBJECT_OWNER HEADING 'Parent Object Owner' FORMAT A20
COLUMN PARENT_OBJECT_NAME HEADING 'Parent Object Name' FORMAT A20
SELECT OBJECT_OWNER,
OBJECT_NAME,
PARENT_OBJECT_OWNER,
PARENT_OBJECT_NAME
FROM DBA_APPLY_OBJECT_DEPENDENCIES;
Your output should look similar to the following:
Object Owner Object Name Parent Object Owner Parent Object Name
--------------- --------------- -------------------- --------------------
ORD CUSTOMERS ORD SHIP_ORDERS
ORD ORDERS ORD SHIP_ORDERS
ORD ORDER_ITEMS ORD SHIP_ORDERS
This output shows an object dependency in which the ord.ship_orders table is a parent table to the following child tables:
-
ord.customers
-
ord.orders
-
ord.order_items
Checking for Apply Errors
To check for apply errors, run the following query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A11
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20
COLUMN MESSAGE_COUNT HEADING 'Messages in|Error|Transaction' FORMAT 99999999
SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;
If there are any apply errors, then your output looks similar to the following:
Apply Local Messages in
Process Source Transaction Error
Name Database ID Error Number Error Message Transaction
----------- ---------- ----------- ------------ -------------------- -----------
APPLY$_DB_2 DB.EXAMPLE 13.16.334 26786 ORA-26786: A row wit 1
.COM h key ("EMPLOYEE_ID"
) = (206) exists but
has conflicting col
umn(s) "SALARY" in t
able HR.EMPLOYEES
ORA-01403: no data f
ound
APPLY$_DB_2 DB.EXAMPLE 15.17.540 26786 ORA-26786: A row wit 1
.COM h key ("EMPLOYEE_ID"
) = (206) exists but
has conflicting col
umn(s) "SALARY" in t
able HR.EMPLOYEES
ORA-01403: no data f
ound
If there are apply errors, then you can either try to reexecute the transactions that encountered the errors, or you can delete the transactions. If you want to reexecute a transaction that encountered an error, then first correct the condition that caused the transaction to raise an error.
If you want to delete a transaction that encountered an error, then you might need to resynchronize data manually if you are sharing data between multiple databases. Remember to set an appropriate session tag, if necessary, when you resynchronize data manually.
Displaying Detailed Information About Apply Errors
This section contains SQL scripts that you can use to display detailed information about the error transactions in the error queue in a database. These scripts are designed to display information about LCRs, but you can extend them to display information about any non-LCR messages used in your environment as well.
To use these scripts, complete the following steps:
-
Grant Explicit SELECT Privilege on the DBA_APPLY_ERROR View
-
Create a Procedure that Prints the Value in an ANYDATA Object
-
Create a Procedure that Prints a Specified LCR
-
Create a Procedure that Prints All the LCRs in the Error Queue
-
Create a Procedure that Prints All the Error LCRs for a Transaction
|
Note:
These scripts display only the first 253 characters for VARCHAR2 values in LCRs. |
- Step 1 Grant Explicit SELECT Privilege on the DBA_APPLY_ERROR View
-
The user who creates and runs the print_errors and print_transaction procedures described in the following sections must be granted explicit SELECT privilege on the DBA_APPLY_ERROR data dictionary view. This privilege cannot be granted through a role. Running the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package on a user grants this privilege to the user.
To grant this privilege to a user directly, complete the following steps:
-
In SQL*Plus, connect as an administrative user who can grant privileges.
See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
-
Grant SELECT privilege on the DBA_APPLY_ERROR data dictionary view to the appropriate user. For example, to grant this privilege to the strmadmin user, run the following statement:
GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
-
Grant EXECUTE privilege on the DBMS_APPLY_ADM package. For example, to grant this privilege to the strmadmin user, run the following statement:
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
-
Connect to the database as the user to whom you granted the privilege in Step 2 and 3.
- Step 2 Create a Procedure that Prints the Value in an ANYDATA Object
-
The following procedure prints the value in a specified ANYDATA object for some selected data types. Optionally, you can add more data types to this procedure.
CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS
tn VARCHAR2(61);
str VARCHAR2(4000);
chr VARCHAR2(1000);
num NUMBER;
dat DATE;
rw RAW(4000);
res NUMBER;
BEGIN
IF data IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NULL value');
RETURN;
END IF;
tn := data.GETTYPENAME();
IF tn = 'SYS.VARCHAR2' THEN
res := data.GETVARCHAR2(str);
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253));
ELSIF tn = 'SYS.CHAR' then
res := data.GETCHAR(chr);
DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253));
ELSIF tn = 'SYS.VARCHAR' THEN
res := data.GETVARCHAR(chr);
DBMS_OUTPUT.PUT_LINE(chr);
ELSIF tn = 'SYS.NUMBER' THEN
res := data.GETNUMBER(num);
DBMS_OUTPUT.PUT_LINE(num);
ELSIF tn = 'SYS.DATE' THEN
res := data.GETDATE(dat);
DBMS_OUTPUT.PUT_LINE(dat);
ELSIF tn = 'SYS.RAW' THEN
-- res := data.GETRAW(rw);
-- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));
DBMS_OUTPUT.PUT_LINE('BLOB Value');
ELSIF tn = 'SYS.BLOB' THEN
DBMS_OUTPUT.PUT_LINE('BLOB Found');
ELSE
DBMS_OUTPUT.PUT_LINE('typename is ' || tn);
END IF;
END print_any;
/
- Step 3 Create a Procedure that Prints a Specified LCR
-
The following procedure prints a specified LCR. It calls the print_any procedure created in "Create a Procedure that Prints the Value in an ANYDATA Object".
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN ANYDATA) IS
typenm VARCHAR2(61);
ddllcr SYS.LCR$_DDL_RECORD;
proclcr SYS.LCR$_PROCEDURE_RECORD;
rowlcr SYS.LCR$_ROW_RECORD;
res NUMBER;
newlist SYS.LCR$_ROW_LIST;
oldlist SYS.LCR$_ROW_LIST;
ddl_text CLOB;
ext_attr ANYDATA;
BEGIN
typenm := lcr.GETTYPENAME();
DBMS_OUTPUT.PUT_LINE('type name: ' || typenm);
IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN
res := lcr.GETOBJECT(ddllcr);
DBMS_OUTPUT.PUT_LINE('source database: ' ||
ddllcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE('owner: ' || ddllcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE('object: ' || ddllcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE('is tag null: ' || ddllcr.IS_NULL_TAG);
DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
ddllcr.GET_DDL_TEXT(ddl_text);
DBMS_OUTPUT.PUT_LINE('ddl: ' || ddl_text);
-- Print extra attributes in DDL LCR
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('serial#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('session#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('thread#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('tx_name');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('username');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
END IF;
DBMS_LOB.FREETEMPORARY(ddl_text);
ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN
res := lcr.GETOBJECT(rowlcr);
DBMS_OUTPUT.PUT_LINE('source database: ' ||
rowlcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE('owner: ' || rowlcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE('object: ' || rowlcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE('is tag null: ' || rowlcr.IS_NULL_TAG);
DBMS_OUTPUT.PUT_LINE('command_type: ' || rowlcr.GET_COMMAND_TYPE);
oldlist := rowlcr.GET_VALUES('old');
FOR i IN 1..oldlist.COUNT LOOP
IF oldlist(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('old(' || i || '): ' || oldlist(i).column_name);
print_any(oldlist(i).data);
END IF;
END LOOP;
newlist := rowlcr.GET_VALUES('new', 'n');
FOR i in 1..newlist.count LOOP
IF newlist(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('new(' || i || '): ' || newlist(i).column_name);
print_any(newlist(i).data);
END IF;
END LOOP;
-- Print extra attributes in row LCR
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('row_id');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('row_id: ' || ext_attr.ACCESSUROWID());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('serial#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('session#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('thread#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('tx_name');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('username');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm);
END IF;
END print_lcr;
/
- Step 4 Create a Procedure that Prints All the LCRs in the Error Queue
-
The following procedure prints all of the LCRs in all of the error queues. It calls the print_lcr procedure created in "Create a Procedure that Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_errors IS
CURSOR c IS
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
FROM DBA_APPLY_ERROR
ORDER BY SOURCE_DAT` ABASE, SOURCE_COMMIT_SCN;
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgno NUMBER;
msgcnt NUMBER;
errnum NUMBER := 0;
errno NUMBER;
errmsg VARCHAR2(2000);
lcr ANYDATA;
r NUMBER;
BEGIN
FOR r IN c LOOP
errnum := errnum + 1;
msgcnt := r.MESSAGE_COUNT;
txnid := r.LOCAL_TRANSACTION_ID;
source := r.SOURCE_DATABASE;
msgno := r.MESSAGE_NUMBER;
errno := r.ERROR_NUMBER;
errmsg := r.ERROR_MESSAGE;
DBMS_OUTPUT.PUT_LINE('*************************************************');
DBMS_OUTPUT.PUT_LINE('----- ERROR #' || errnum);
DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid);
DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source);
DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno);
DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno);
DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE('--message: ' || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid);
print_lcr(lcr);
END LOOP;
END LOOP;
END print_errors;
/
To run this procedure after you create it, enter the following:
SET SERVEROUTPUT ON SIZE 1000000
EXEC print_errors
- Step 5 Create a Procedure that Prints All the Error LCRs for a Transaction
-
The following procedure prints all the LCRs in the error queue for a particular transaction. It calls the print_lcr procedure created in "Create a Procedure that Prints a Specified LCR".
CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgno NUMBER;
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(2000);
lcr ANYDATA;
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgno, msgcnt, errno, errmsg
FROM DBA_APPLY_ERROR
WHERE LOCAL_TRANSACTION_ID = ltxnid;
DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid);
DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source);
DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno);
DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno);
DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE('--message: ' || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); -- gets the LCR
print_lcr(lcr);
END LOOP;
END print_transaction;
/
To run this procedure after you create it, pass to it the local transaction identifier of an error transaction. For example, if the local transaction identifier is 1.17.2485, then enter the following:
SET SERVEROUTPUT ON SIZE 1000000
EXEC print_transaction('1.17.2485')
PK0͜2t ` PK &A OEBPS/index.htm
