What process is in charge? 1

[liliya13]

Hi Everybody,

Being the Oracle DBA (9i) on the machine MI I have just logged into the ORACLE account and issue the UNIX command to find the following processes:
ora-ckpt_MI01
ORA-SMON_MI01
ORA-PMON_MI01
ORA-DBW0_MI01
ORA-LGWR_MI01
ORA-RECO_MI01

While I am logged into the database and running updates on the tables to change table contents... I issue a commit to make the changes permanent.

I am trying to understand the following:

1.what process is responsible for making sure that the changes are written to the disk BEFORE the COMMIT process finishes and I get the SQL*PLUS prompt back again?

2. What process is responsible for writing data buffer cache info into the datafiles of the database system?

I am on Sub OS 5.9

Thank you in advance,

Liliya

 

[Turkbear]

Hi,
Not to sound like a smart-a**, but reading the Concepts docs would be a a real good idea for anyone who expects to be a functional DBA -

Try here:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/toc.htm

If asked to register for Technet, do so..it is free and will be a big asset for you..

 

[SantaMufasa]

Liliya,

I agree with Turkbear. Of all the software packages, database-management software is a category that definitely demands clear understanding from some source: concepts manual, training course(s), et cetera.

To your disadvantage, however, Liliya, is the fact that even concepts manuals, courses, and other sources do not, themselves, always understand the process when "COMMIT happens" . Therefore, I would like to present to you some information that, I hope, clears up this topic for you insofar as Oracle is concerned.

The first important concept to understand about Oracle is its architecture. The two main components of a "running" Oracle are:
1) The Instance and
2) The Database.

The main components of the Database are
1) The Control Files,
2) The Database Data Files and
3) The On-line Redo Log Files.

The Control Files: a textual map of the components and characteristics of the database. Amongst the information that the control file contain are:
a) the name of the database,
b) whether the database is in "ARCHIVELOG" mode
c) maximum numbers of log files, log members, data files, instances that can be managing the database simultantiously, and maximum amount of log history
d) log file groups and log file names
e) database data file names
f) database characters set

The Database Data Files: These are the disk files that provide storage space for your database's objects. A Database Data File supports one tablespace; a tablespace has one or more data files. These database data files logically contain extents. An extent simply is a collection of contiguous database blocks. The size of your database blocks is a multiple of your operating system's block size. Oracle labels the extents as either "free" or "used". If a block is "used", it means that it contains data for a database object known as a segment. Typical examples of segments are tables and indexes. If Oracle has not yet allocated an extent for use by a segment, then the extent (the physically contiguous collection of blocks) is called a "free extent". It is from this collection of "free extents" that Oracle allocates more space for tables and indexes, as needed.

The On-line Redo Log Files: On-line redo log files are the key to Oracle's success as a database. They are simply a sequential "diary" of all the events that occur to the database. It is the "stream of consciousness" that, if you were to "replay" it, from start to finish, could logically and physically rebuild your entire database, current up to the most recently committed (and even uncommitted) transaction.

So, Control Files, Database Data Files and On-line Redo Log Files make up an Oracle database.

====================================================
Now, for an explanation of The Instance. The Oracle Instance is made up of:
1) The System Global Area (SGA) and
2) The Background Processes

The main components of The System Global Area (SGA) are:
1) The Shared Pool,
2) The Database Buffer Cache and
3) The Log Buffer.

The Shared Pool: A main purpose of the Shared Pool is that it is the area of memory that stores SQL statements for execution and re-use. It stores a text version and an interpreted, executable version of SQL statements.

The Database Buffer Cache: This major memory component is the location where Oracle
a) reads data blocks into memory from the Database Data Files,
b) performs updates, deletes, and inserts of rows of data that appear in (or logically disappear from) the data blocks, and
c) displays data from here to the screen and processes of database users everywhere.
The Database Writer (DBWR) process writes information from this memory structure out to the Database Data Files on an as-needed basis. (But this is a very important note: a COMMIT statement is not one of the triggering events that causes DBWR to write to disk. We’ll talk more about this, below.)

The Log Buffer: This vital memory component is the “scratch pad” where Oracle jots down all the changes that occur to the database, including whether or not transaction changes are worthy of saving (COMMITted) or not worthy of saving (ROLLedBACK). The Log Writer (LGWR) process writes information from this memory structure out to the On-line Redo Log Files on both a regular and irregular/as-needed basis. One of the events that triggers the LGWR to write to disk is a COMMIT statement.

The Background Processes: These processes are similar to the “kitchen help” at a restaurant. They are the processes that do much of the work to “make your dining experience a pleasure.” [But it is your own Dedicated Server (your personal "waiter" or "waitress"), that actually delivers to you what you request and takes care of your personal needs.] The background processes include these software components:
a) The System Monitor (SMON),
b) The Process Monitor (PMON),
c) The Database Writer (DBWR),
d) The Log Writer (LGWR),
e) The Checkpoint process (CKPT)
f) The Recovery process (RECO)

So, The SGA and Background Processes make up an Oracle Instance.
====================================================

It took all the above background to get us to the point where we can now clearly understand what happens as a result of a COMMIT statement.

When a User performs an INSERT, UPDATE or DELETE statement, s/he is instructing her/his Dedicated Server Process (SP) to make physical changes to Database Blocks that reside in the Database Buffer Cache in the SGA. (For an UPDATE or DELETE, the SP must ensure that the affected data block(s) reside in the Database Buffer Cache. If the block(s) are not there, then the SP must copy the necessary block(s) from the Database Data File(s) on disk into the Database Buffer Cache in the SGA.)

Before an SP actually makes any changes, however, it must write a copy of the “pre-changed” data out to a Rollback Segment, in case the User changes her/his mind and wants to abandon the changes. The Rollback Segment also provides a location for other SPs that are serving other Users to obtain an “unchanged”, consistent copy of the database data until such time that the changing User decides to COMMIT (or ROLLBACK) the changes that s/he has made.

When an SP makes changes for a User, it writes the changes to both the data block that resides in memory in the Database Buffer Cache and also to the Log Buffer. [Remember that the Log Buffer is a “diary” of all changes to the database, whether we “keep” the changes (via COMMIT) or “discard” the changes (via ROLLBACK or via abnormal end (abend) to a User session or something else unexpected such as a power failure that brings the entire instance down.) ]

It is very important to point out that an SP might need to go to different locations to obtain “true” data (“true” meaning currently COMMITted, consistent data). The SP might need to access “true” data from either:
1) Table/Index blocks in a Database Data File (on disk)
2) Table/Index blocks that have already been copied into the Database Buffer Cache in memory in the SGA,
3) Rollback-Segment blocks still residing in the Database Buffer Cache in memory in the SGA,
4) Rollback-Segment blocks in a Database Data File (on disk).

This means that “true” database data (meaning COMMITted data) might reside in one of four different locations, two of which are on disk and two of which are in memory.

At this point, you are probably saying, “I know for a fact that when ‘COMMIT happens’, something must be written to disk if for no other reason than to protect against a sudden power failure that erases everything in memory, including COMMITted images.” You would be correct…something absolutely, positively is written to disk as a result of a COMMIT: it is the information (including the COMMIT verification) that resides in the Log Buffer. And the response, "Commit complete", does not return to your screen until Oracle can confirm that it has successfully written the contents of the Log Buffer to the current On-line Redo Log File.

So, it is very important to understand that as a result of this architecture, that:
1) COMMITted data might not be written to the Database Data Files for an extended period following the COMMIT
2) Un-COMMITted data might be written to the Database Data Files long before it is ever COMMITted.

The above apparent anomaly disappears when one realises that Oracle always writes COMMITted data to the On-line Redo Log File. It is the contents of the On-line Redo Log Files that Oracle uses in case there is an abnormal termination to an Oracle Instance or session.

I hope this all seems reasonable, because this is how Oracle works.

Let us know your reactions to all this,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 00:18 (23Oct04) UTC (aka "GMT" and "Zulu"),
@ 17:18 (22Oct04) Mountain Time

 

[liliya13]

Mufasa,

Thank you very much for your explanation! I am actually not a DBA: I am just trying to start doing it.I really appreciated as it was very helpful

Liliya