Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 editor@searchoracle.com

.

This was last published in August 2010

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close