Understanding rollback in Oracle following SHUTDOWN IMMEDIATE

Oracle expert Brian Peasland explains the concept of rollback in Oracle, and explains how Oracle rollback works when a SHUTDOWN IMMEDIATE is issued.

I am hoping you can shed some light on the topic of rollback in Oracle. I will use SHUTDOWN IMMEDIATE as an example. A DML update command is issued updating, say, 10,000 records. No COMMIT is issued. The DBA issues a SHUTDOWN IMMEDIATE command, which prevents additional sessions from connecting and rolls back the uncommitted transactions.

My question is, what is Oracle rolling back? If the changes were uncommitted, never written to disk, never written to the datafiles and only affected blocks in memory, what is it actually rolling back?

Your question assumes the changes were uncommitted and thus:

1.       Changes were never written to disk

2.       Changes were never written to the datafiles

Both of these are incorrect. It is possible for an uncommitted transaction to write changes to the online redo logs and the tablespace’s datafiles. Think of it this way…your LOG BUFFER is 1MB in size but you are inserting 10MB of data into a table. Once the Log Buffer gets filled, where do the other 9MB of changes get stored before the COMMIT? The Log Buffer writes the change vectors to the online redo logs even though a COMMIT was never issued. Also, assume your DB_BUFFER_CACHE is 1MB in size (not realistic I know but the example is valid). Once the buffer cache is filled with changed blocks, those changes need to be written to the database datafiles before new blocks can be modified. Datafiles and online redo logs can contain uncommitted changes. Therefore, when a SHUTDOWN IMMEDIATE is issued, Oracle rolls back any uncommitted changes before completing the shutdown.

Have a question for Brian Peasland? Send an e-mail to [email protected]


Dig Deeper on Oracle database backup and recovery