"Transaction recovery: lock conflict caught and ignored" in alert log
This is a normal informational message indicating that SMON is trying to recover a failed transaction,
may be SMON is taking to finish the recovery process depends on the size of data modified and need to be recovered and the degree of parallelism when recovering terminated transactions. i.e., This happens when there is a long transactions interrupted and try to recover.
Verify with below query to check volume of dead transactions.
select sysdate, b.name useg, a.ktuxeusn xid_usn, a.ktuxeslt
xid_slot, a.ktuxesqn xid_seq, a.ktuxesta
from x$ktuxe a, undo$ b
where a.ktuxecfl like '%DEAD%' ;
If you are getting a very high volume no. of records, then you needs to change the following parameter and that to in OLTP environment ( more Write operation).
Check the value of FAST_START_PARALLEL_ROLLBACK parameter.
It needs to be set to high in order to speed the recovery, ensure it's set as follows:
ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH;
Hope this message will not come to your alert log.
But, after setting 'HIGH' value, still you are getting same message, then it indicates a block / segment corruption.
Points to Remember:
Parallel Transaction Recovery :
Recovery occurs in parallel mode. Several parallel slave processes will be spawned and will be involved in recovery. This is also termed as Fast Start Parallel Rollback. The background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes. To enable Parallel recovery mode, set the parameter FAST_START_PARALLEL_ROLLBACK to LOW / HIGH.
Values:
•FALSE - Parallel rollback is disabled
•LOW - Limits the maximum degree of parallelism to 2 * CPU_COUNT
•HIGH -Limits the maximum degree of parallelism to 4 * CPU_COUNT
If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism. Fast-start parallel rollback is mainly useful when a system has transactions that run a long time before committing, especially parallel INSERT, UPDATE, and DELETE operations. When SMON discovers that the amount of recovery work is above a certain threshold, it automatically begins parallel rollback by dispersing the work among several parallel processes: process 1 rolls back one transaction, process 2 rolls back a second transaction, and so on. The threshold is the point at which parallel recovery becomes cost-effective, in other words, when parallel recovery takes less time than serial recovery.
Please go through the below link to troubleshot the issue.
Click here : Troubleshoot block corruption
Caution: Any change in production database, please change with Oracle support recommendation or with your own risk.
Click here to read similar issue from my post .
Please feel free to post comment...
This is a normal informational message indicating that SMON is trying to recover a failed transaction,
may be SMON is taking to finish the recovery process depends on the size of data modified and need to be recovered and the degree of parallelism when recovering terminated transactions. i.e., This happens when there is a long transactions interrupted and try to recover.
Verify with below query to check volume of dead transactions.
select sysdate, b.name useg, a.ktuxeusn xid_usn, a.ktuxeslt
xid_slot, a.ktuxesqn xid_seq, a.ktuxesta
from x$ktuxe a, undo$ b
where a.ktuxecfl like '%DEAD%' ;
If you are getting a very high volume no. of records, then you needs to change the following parameter and that to in OLTP environment ( more Write operation).
Check the value of FAST_START_PARALLEL_ROLLBACK parameter.
It needs to be set to high in order to speed the recovery, ensure it's set as follows:
ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH;
Hope this message will not come to your alert log.
But, after setting 'HIGH' value, still you are getting same message, then it indicates a block / segment corruption.
Points to Remember:
Parallel Transaction Recovery :
Recovery occurs in parallel mode. Several parallel slave processes will be spawned and will be involved in recovery. This is also termed as Fast Start Parallel Rollback. The background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes. To enable Parallel recovery mode, set the parameter FAST_START_PARALLEL_ROLLBACK to LOW / HIGH.
Values:
•FALSE - Parallel rollback is disabled
•LOW - Limits the maximum degree of parallelism to 2 * CPU_COUNT
•HIGH -Limits the maximum degree of parallelism to 4 * CPU_COUNT
If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism. Fast-start parallel rollback is mainly useful when a system has transactions that run a long time before committing, especially parallel INSERT, UPDATE, and DELETE operations. When SMON discovers that the amount of recovery work is above a certain threshold, it automatically begins parallel rollback by dispersing the work among several parallel processes: process 1 rolls back one transaction, process 2 rolls back a second transaction, and so on. The threshold is the point at which parallel recovery becomes cost-effective, in other words, when parallel recovery takes less time than serial recovery.
Please go through the below link to troubleshot the issue.
Click here : Troubleshoot block corruption
Caution: Any change in production database, please change with Oracle support recommendation or with your own risk.
Click here to read similar issue from my post .
Please feel free to post comment...
No comments:
Post a Comment