Current configuration of database:
Database version -- 126.96.36.199
OS: Linux x86-64
DB Server: EXADATA
Recently upgraded from 10.2.0.4 to 188.8.131.52
To Explain the issue:
Recently it has come to our notice that 1 of our concurrent program gets locking issues immediately after a Database and application bounce.
This issue had been coming up after we upgraded our Database from 10g to 11g.
We generally have our maintenance during the weekend(sunday) once or twice a month depending on business need.
Immediately after the Database Upgrade when the Program was submitted it failed with the below error.
Error ORA-20001: FLEX-DSQL EXCEPTION
Team then applied the fix as in note id: 974396.1
execute immediate('ALTER session SET '||'"_fix_control"'||'='||'''5909305:OFF''');
It then has been brought up to us now that the same program is now facing problems with Locks.
In general what's happening is whenever we have a DB and application services bounce, the very first time after bounce when the program is submitted it gets locking issues as below:
Issue with Application program is not completely ruled out but same code worked fine in 10g and it also works fine after a few days of maintenance.
Cause: You are trying to lock a row in the pay_org_paym
Error creating Paycard Payment Method for Employee Number :1031699 :ORA-20001: The current row is locked
After a week or two of bounce when the Program is submitted it never faces any locks issue.
Above is not a DEADLOCK; which throws ORA-00060
[oracle@localhost dbs]$ oerr ora 60
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.
You have application malfunction so the solution is to fix the application
It doesn't look like a database issue but a usage issue. A rush of users / application processes (concurrent programs in EBiz Suite ?) attempting to run a backlog of jobs (e.g. jobs that would normally have been sequentially executed when the database is running but were queued and not running while the database was down now being attempted to run concurrently).
Hemant K Chitale
Thanks for your response.
If users being in queue when database goes down and then rushing in queue when it comes back again is the reason.
Please suggest how do we take necessary proof for it i.e., i would like to confirm that this is the issue. Also what can be done to avoid this scenario.
Thanks in advance.