Thanks for the question, Gururaj.
Asked: January 24, 2020 - 6:30 pm UTC
Answered by: Connor McDonald - Last updated: February 11, 2020 - 4:24 am UTC
Category: PL/SQL - Version: Oracle Database 12c Enterprise Edition Release 126.96.36.199.0 - 64bit Production
Viewed 100+ times
We faced one issue, I have explained the scenario
> Our table has multiple rows with primary key as number for an entity For ex: assuming employeeid for an employee
> We have a status column which suggests calculation status of the current employee id : For ex: OPEN ,IN-PROGRESS and CLOSED.
> Actual calculation will be done and will be inserted to an intermediate table and status will be updated from OPEN to IN-PROGRESS in the main table
> Once the data moves from staging to target it will be updated to CLOSED. Commit to the block is at the end.
Now the issue,
Node 1: Calculation was done and data was inserted to staging and status was updated to IN-PROGRESS
Our operation fetches 8000 rows which is IN progress state from Staging and commits it to target table and then updates the status to CLOSED and then commits entire session.
node 1 goes down due to patching activity.
Node 2: As our job is rerunnable, we trigger the same job on node 2.
When we restarted again so many rows which was committed on node 1 missing but the status in the main table was updated to CLOSED.
My question is
Is it possible that once committed rows on one node, which eventually goes down after committing , and when we re run the same job on other node will be missed? As far as I know data once committed will never be lost
Checked in the dba table of active session history but could not find any clue on this.
Please let me know if there is any other way we can trace that.
and we said...
As far as I know data once committed will never be lost
We're not taking reviews currently, so please try again later if you want to add a review.
That is correct. A commit is a commit and is permanent.
If the commit issued before the node goes down, that data is there forever. If not, the data is rolled back as if the transaction never commenced.
You could check ora_rowscn for the table concerned (its a block level scn) and then use scn_to_timestamp to get an approximate of when the last committed transaction on that block occurred.