As per my experience, I'm explaining here about Committed to Update Transactions in the Oracle Database Architecture.
Step 1: When a user sends a request to update transaction in the table.
Step 2: A user request will go to the listener process, then the listener process sends the request to the server process, then server process is chosen for hard parsing as well as choosing cost-based optimization. Based on the best execution plan, it will get to the library cache.
Step 3: If the library cache doesn't have the hash (#) value of the query, then it will go to the data dictionary cache, generate metadata for update transactions from the base table, then create a hash(#) value, and place it in the library cache.
Step 4: Based on the hash (#) value, the server process passes the request to the database buffer cache. The entire process will done by server process.
Step 5: In the database buffer cache (DBBC). Here, the DB writer takes a copy of user-requested data (Update statement) from datafiles to the Database buffer cache, and one extra copy is kept in undo tablespace when the commit does not happen to roll back the transaction.
Step 6: When changes are done after, when issuing a "commit", all the change vectors, the server process will pass it redo log buffer cache, then the LG Writer will write change vectors from the redo log buffer cache to online redo log files.
What is change Vector= Block ID + Bit address + Bit value is called change vector.
Note: When performing an update/insert transaction. If DDBC doesn't have sufficient buffer, then it will go to PGA. Still not sufficient buffer, then it will have temporary tablespace for sorting operations.
Change Vectors: In four possible scenarios, LGWR writes change vectors from redo log buffer cache to an online redo logfile.
a) Every 3 seconds
b) When commit happens.
c) Whenever log switch occurs.
d) Whenever a check point occurred.
Step 7: Finally, the Archiver background process will write change vectors from online redo logfile to archive logfile for recovery.
Best Regards,
Devathi Ramakrishna
Sr. Associate - Projects