The following topics describe identifying and resolving common apply process problems in an Oracle Streams environment:
Is the Apply Process's Queue Receiving the Messages to be Applied?
Is the AQ_TM_PROCESSES Initialization Parameter Set to Zero?
Is an Apply Server Performing Poorly for Certain Transactions?
See Also:
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';
See Also:
"Apply Processes and Oracle Real Application Clusters" for information about restarting an apply process in an Oracle Real Application Clusters (Oracle RAC) environment
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.
See Also:
"Determining the Capture to Apply Latency for a Message for Each Apply Process"
The DBMS_APPLY_ADM.SET_PARAMETER
procedure in the Oracle Database PL/SQL Packages and Types Reference for detailed information about the apply process parameters
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.
See Also:
Oracle Streams Replication Administrator's Guide for information about configuring Oracle Streams replication
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.
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.
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.
See Also:
Oracle Database Advanced Queuing User's Guide for information about the AQ_TM_PROCESSES
initialization parameter
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.
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.
See Also:
Oracle Database Performance Tuning Guide for more information about contention and about resolving different types of contention
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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about apply process parameters
Oracle Database Administrator's Guide and Oracle Database SQL Language Reference for more information about INITRANS
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;
See Also:
Oracle Database Performance Tuning Guide and Oracle Database Reference for more information about theV$SQL_PLAN
dynamic performance viewWhen 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.
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.
See Also:
"Managing a DML Handler"You might encounter the following types of apply process errors for LCRs:
ORA-26786 A row with key exists but has conflicting column(s) in table
ORA-26787 The row with key column_value does not exist in table table_name
The errors marked with an asterisk (*) in the previous list often result from a problem with an apply handler or a rule-based transformation.
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.
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.
See Also:
"Considerations for Applying DML Changes to Tables" for information about possible causes of apply errors
Oracle Streams Replication Administrator's Guide for more information about Oracle Streams tags
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.
See Also:
Chapter 6, "Rule-Based Transformations"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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the DELETE_COLUMN
and RENAME_COLUMN
member procedures for row LCRs
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.
See Also:
Chapter 6, "Rule-Based Transformations"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.
See Also:
Oracle Streams Replication Administrator's Guide for more information about instantiation
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.
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".
See Also:
"Considerations for Applying DML Changes to Tables" for information about possible causes of apply errors
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_TAG(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
.
See Also:
Oracle Streams Replication Administrator's Guide for information about conflict resolution
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 more information.
See Also:
Oracle Streams Replication Administrator's Guide for information about conflict resolution