- Oracle P2 Text Usn16 Slot Machine
- Oracle P2 Text Usn16 Slot Machines
- Oracle P2 Text Usn16 Sloth
- Oracle P2 Text Usn16 Slots
Oracle - Using the Wait Interface - null event MOSC: Search BC Oracle Sites Home E-mail Us Oracle Articles New Oracle Articles Oracle Training. Column P2 format 9999 column P3 format 99 select SID, EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3, WAITTIME, SECONDSINWAIT. Repository Description Open Source Terms and Restrictions; coherence-operator: Docker images for Oracle Coherence Kubernetes Operator: The container image you have selected and all of the software that it contains is licensed under one or more open source license that are.
This chapter contains information about identifying and resolving common problems in a Streams replication environment.
This chapter contains these topics:
- Are There Any Apply Errors in the Error Queue?
See Also: Oracle Streams Concepts and Administration for more information about troubleshooting Streams environments
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 may use one or more apply servers, and the
parallelism
apply process parameter specifies the number of apply servers that may 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 may 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 also may result from physical dependencies. For example, interested transaction list (ITL) contention results when two transactions that are being applied, which may 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 may 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 information must be flushed because of a commit or rollback. In these cases, nothing is written 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 servera001
may write a message similar to the following:This output is written 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
a001
may write a message similar to the following:This output is written to the alert log at intervals until the problem is rectified. - Waiting for another apply server session: This state may be caused by interested transaction list (ITL) contention, but it also may 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
a001
is blocked by apply server 2 of the same apply process (a001
), then the apply process writes the following messages to the log files:You can determine the total number of times an apply server was rolled back since the apply process last started by querying theTOTAL_ROLLBACKS
column in theV$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
- Oracle Streams Concepts and Administration for more information about trace files and the alert log
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 may 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 may negatively affect the performance of an apply process because there may 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 theALTER
TABLE
statement. - Set the
commit_serialization
parameter tonone
for the apply process. - Set the
parallelism
apply process parameter to1
for the apply process.See Also: - Oracle Streams Concepts and Administration for more information about apply process parameters and about checking the trace files and alert log for problems
- Oracle Database Administrator's Guide and Oracle Database SQL Reference for more information about
INITRANS
Is an Apply Server Performing Poorly for Certain Transactions?
If an apply process is not performing well, then the reason may 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
: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 may not be performing well. In this case, you should make sure 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 may 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: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. Make sure 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 may 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: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. Make sure 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 may run the following simplified query instead:
See Also: Oracle Database Performance Tuning Guide and Oracle Database Reference for more information about the V$SQL_PLAN dynamic performance view |
![Text Text](/uploads/1/2/5/1/125158182/675240718.jpg)
Are There Any Apply Errors in the Error Queue?
When an apply process cannot apply an event, it moves the event and all of the other events in the same transaction into the error queue. You should check the for apply errors periodically to see if there are any transactions that could not be applied. You can check for apply errors by querying the
DBA_APPLY_ERROR
data dictionary view.See Also: Oracle Streams Concepts and Administration for more information about checking for apply errors and about managing apply errors |
Oracle P2 Text Usn16 Slot Machine
You may encounter the following types of apply process errors for LCR events:
- ORA-23605 Invalid Value for Streams Parameter*
- ORA-23607 Invalid Column*
- ORA-24031 Invalid Value, parameter_name Should Be Non-NULL*
- ORA-26688 Missing Key in LCR*
- ORA-26689 Column Type Mismatch*
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
This 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 must be granted by an explicit grant of each privilege. Play real cash money slots online. Granting these privileges through a role is not sufficient for the Streams apply user.
Specifically, the following privileges are required:
- For table level DML changes, the
INSERT
,UPDATE
,DELETE
, andSELECT
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
, andALTER
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.
See Also: - 'Apply and Streams Replication' for more information about apply users
- Oracle Streams Concepts and Administration for information about reexecuting error transactions
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 this destination database.Typically, one of the following conditions causes these errors:
- Supplemental logging is not specified for columns that require supplemental logging at the source database. In this case, LCRs from the source database may not contain values for key columns. You can use a DML handler to modify the LCR so that it contains the necessary supplemental data. After you set the DML handler, you can reexecute the error transaction. After successful reexecution, if the DML handler is no longer needed, then remove it.
- There is a problem with the primary key in the table for which an LCR is applying a change. In this case, make sure 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 theSET_KEY_COLUMNS
procedure in theDBMS_APPLY_ADM
package. You also may encounter errorORA-23416
if a table being applied does not have a primary key. After you make these changes, you can reexecute the error transaction. - There is a data mismatch between a row LCR and the table for which the LCR is applying a change. Make sure row data in the table at the destination database matches the row data in the LCR. When you are checking for differences in the data, if there are any
DATE
columns in the shared table, then make sure your query shows the hours, minutes, and seconds. If there is a mismatch, then you can use a DML handler to modify an LCR so that it matches the table. After you set the DML handler, you can reexecute the error transaction. After successful reexecution, if the DML handler is no longer needed, then remove it.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 at the destination database, then you probably do not want to replicate this manual change to destination databases. In this case, complete the following steps:- Set a tag in the session that corrects the row. Make sure you set the tag to a value that prevents the manual change from being replicated. For example, the tag may prevent the change from being captured by a capture process. In some environments, you may 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 theDBMS_APPLY_ADM
package, and specify the transaction identifier for the transaction that caused the error. For example:Or, execute all errors for the apply process by running theEXECUTE_ALL_ERRORS
procedure: - 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: In some environments, you may need to set the tag to a value other than
NULL
.See Also: - 'Supplemental Logging for Streams Replication' and 'Monitoring Supplemental Logging'
- 'Considerations for Applying DML Changes to Tables' for information about possible causes of apply errors
- Oracle Streams Concepts and Administration for more information about managing apply errors and for instructions that enable you to display detailed information about apply errors
- Set a tag in the session that corrects the row. Make sure you set the tag to a value that prevents the manual change from being replicated. For example, the tag may prevent the change from being captured by a capture process.
ORA-23605 Invalid Value for Streams Parameter
![Oracle p2 text usn16 slots Oracle p2 text usn16 slots](/uploads/1/2/5/1/125158182/641080518.jpg)
This error occurs if an incorrect value is used for a Streams parameter or if a row LCR does not contain the correct old and new values. 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 may contain both new values and old values. - A row LCR for a
DELETE
operation should contain old values but no new values.
Oracle P2 Text Usn16 Slot Machines
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 DML handler or 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 rule-based transformation caused the error, then you can create a DML handler that will run when you reexecute the error transaction. Configure this DML handler to correct the problem. After successful reexecution, if the DML handler is no longer needed, then remove it. You also should correct the rule-based transformation that caused the error to prevent future errors.
See Also:
|
ORA-23607 Invalid Column
Oracle P2 Text Usn16 Sloth
This error is caused by an invalid column specified in the column list of a row LCR. Check the column names in the row LCR. This error results if an apply handler or rule-based transformation attempts one of the following actions:
- Delete a column from a row LCR that does not exist in the row LCR
- Rename a column that does not exist in the row LCR
If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a rule-based transformation caused the error, then you can create a DML handler that will run when you reexecute the error transaction. Configure this DML handler to correct the problem. After successful reexecution, if the DML handler is no longer needed, then remove it. You also should correct the rule-based transformation that caused the error to prevent future errors.
See Also:
|
ORA-24031 Invalid Value, parameter_name Should Be Non-NULL
This error may occur when an apply handler or a rule-based transformation passes a
NULL
value to an LCR member subprogram instead of a SYS.AnyData
value that contains a NULL
.For example, the following call to the
ADD_COLUMN
member procedure for row LCRs may result in this error:The following example shows the correct way to call the
ADD_COLUMN
member procedure for row LCRs:If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a rule-based transformation caused the error, then you can create a DML handler that will run when you reexecute the error transaction. Configure this DML handler to correct the problem. After successful reexecution, if the DML handler is no longer needed, then remove it. You also should correct the rule-based transformation that caused the error to prevent future errors.
See Also:
|
ORA-26687 Instantiation SCN Not Set
Typically, this 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 may use either Data Pump export/import or original 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 Streams rules for the objects with the
DBMS_STREAMS_ADM
package or by running a procedure in theDBMS_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 by following the instructions in 'Preparing Database Objects for Instantiation at a Source Database'. Next, set the instantiation SCN for the database objects at the destination database. - You used original export/import for instantiation, and you performed the import without specifying
y
for theSTREAMS_INSTANTIATION
import parameter. If this parameter is not set toy
for the import, then the instantiation SCN will not be set.In this case, repeat the original export/import operation, and set theSTREAMS_INSTANTIATION
parameter toy
during import. Follow the instructions in 'Instantiating Objects in a Streams Environment Using Transportable Tablespaces'.Alternatively, use Data Pump export/import. An instantiation SCN is set for each imported prepared object automatically when you use Data Pump import. - 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 by following the instructions in 'Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package'. Alternatively, you may choose to perform a metadata-only export/import to set the instantiation SCNs by following the instructions in 'Setting Instantiation SCNs at a Destination Database'. - 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 theSET_GLOBAL_INSTANTIATION_SCN
procedure. Both of these procedures are in theDBMS_APPLY_ADM
package. Follow the instructions in 'Setting Instantiation SCNs Using 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 Concepts and Administration for information about reexecuting and deleting error transactions
ORA-26688 Missing Key in LCR
Typically, this error occurs because of one of the following conditions:
- The object for which an LCR is applying a change does not exist in the destination database. In this case, check to see if the object exists. Also, make sure 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 and in apply handlers.
- Supplemental logging is not specified for columns that require supplemental logging at the source database. In this case, LCRs from the source database may not contain values for key columns. You can use a DML handler to modify the LCR so that it contains the necessary supplemental data. After you set the DML handler, you can reexecute the error transaction. After successful reexecution, if the DML handler is no longer needed, then remove it.
- There is a problem with the primary key in the table for which an LCR is applying a change. In this case, make sure the primary key is enabled by querying the
DBA_CONSTRAINTS
data dictionary view. If no primary key exists for the table, or if the table has a multiple column primary key, then specify substitute key columns using theSET_KEY_COLUMNS
procedure in theDBMS_APPLY_ADM
package. You also may encounter errorORA-23416
if a table does not have a primary key. After you make these changes, you can reexecute the error transaction.See Also: - 'Supplemental Logging for Streams Replication' and 'Monitoring Supplemental Logging'
Oracle P2 Text Usn16 Slots
ORA-26689 Column Type Mismatch
Typically, this 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 may contain more columns than the table at the destination database, or there may be a type mismatch for one or more columns. If the columns differ at the databases, then you can use rule-based transformations to avoid errors.
If you use an apply handler or a rule-based transformation, then make sure any
SYS.AnyData
conversion functions match the datatype in the LCR that is being converted. For example, if the column is specified as VARCHAR2
, then use SYS.AnyData.CONVERTVARCHAR2
function to convert the data from type ANY
to VARCHAR2
.Also, make sure you use the correct character case in rule conditions and apply handlers. 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 and in apply handlers.
This error may 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 may not contain needed values for these nonkey columns.
See Also:
|