Re: Commit -> write to data file immediately???

"David" <david_petit_at_yahoo.com> wrote in message news:3AC63AB6.23BFB54C_at_yahoo.com...
>
>
> I've conducted 2 experiments (win2000, Oracle 8.1.7):
>
> (1)
> i) create a table and insert a record to this table. Make transaction
> commit.
> (timestamp of all files seem no change)
> ii) turn off the computer
> iii) the newly created table and the new record exist after Oracle
> restart
>
> (2)
> i) create a table and insert a record to this table. Make transaction
> commit.
> (timestamp of all files seem no change)
> ii) pull the plug
> iii) the newly created table and the new record exist after Oracle
> restart
>
> don't know why in case 2, the new record can be found in table...
>

Because on subsequent startup, SMON spots that there is a transaction in the redo logs after the point of the last checkpoint, and thus replays it. That's just basic Instance Recovery.

Oracle guarantees that committed transactions will never be lost (unless the DBA doesn't know what he's doing with backup and recovery issues etc).

The fact is that you, the user, are not informed that the commit has been successful until AFTER the transaction has been recorded in the redo logs, so if you ever see a response from a commit statement, that transaction is safe (provided you look after your redo, as you found below). The interesting test would be to pull the plug as the commit request is sent (you'd have to be pretty nifty with the timing!)... because the Instance would then fail before LGWR flushed to the redo logs... and hence something which you issued a commit for would indeed be lost (but Oracle would argue that although you *told* it to commit, until it tells *you* that the commit was successful, it hasn't really been committed yet).

Regards
HJR
> (3)
> i) create a table and insert a record to this table. Make transaction
> commit.
> (timestamp of all files seem no change)
> ii) shutdown abort Oracle and remove all log files
> iii) cannot restart Oracle (don't know is it possible to start Oracle in
> this situation if without data file backup)
>
>
> David
>
> Frank Hubeny wrote:
> >
> > Although I don't know the answer to your question, I would suggest an
> > experiment to add to the confusion.
> >
> > Get a test database that no one else is using. (Mine happens to be on
> > Win2000 Oracle 8.1.7.) Pick a tablespace and note the datafiles in the
> > tablespace. Place command prompts on the directories with your
 datafiles
> > and redo logs. Do a "dir" or "ls -l" to get the current time. Connect
 as
> > sysdba. Now you are set up to perform the test.
> >
> > (1) Wait a minute or so to make sure you are at a later time. Create a
> > table in a tablespace where you have identified all the datafiles.
 (create
> > table abc (a number) tablespace <name>;) Do a "dir" or "ls -l" again.
 Does
> > the timestamp change on the redo logs or the datafiles holding the new
> > table? (Mine doesn't.)
> >
> > (2) Wait a minute or so make sure you are at a later time. Add a record
 or
> > two to the new table and commit. (insert into abc values (100);commit;)
> > Does the timestamp change on the redo logs or the datafiles holding the
 new
> > data? (Mine doesn't.)
> >
> > (3) Switch logfiles (alter system switch logfile;). Does the timestamp
> > change on the redo logs or the datafile? (If your experience is like
 mine,
> > this is the first time I notice a change in the timestamp of the two
 redo
> > logs affected by the switch even though commits have occurred in the
> > previous two steps. There is no change to the data or control files.)
> >
> > (4) Switch logfiles again to see if you can generate some change in the
> > datafiles. Does the timestamp change on the redo logs or the
 datafiles?
> > (If your experience is like mine, now all the datafiles and control
 files
> > have a new timestamp.)
> >
> > I've read a lot of the theoreies, like those that others have posted,
 trying
> > to explain what should happen. I would have expected, unless I
> > misunderstood, that the redo logs should show a change after the table
> > creation and commit in steps 1 and 2. They did not. I would have
 expected
> > that switching logfiles the first time would have forced at least the
 data
> > to be written to the tablespace in which I created the new table. It
 did
> > not.
> >
> > It makes me wonder if it is not possible to create an experiment where I
> > could crash Oracle and lose committed data. I tried "shutdown abort"
 after
> > committing another record to the table, but data got written to disk
 right
> > after the shutdown abort command. (Although I am tempted, it is too
 late in
> > the day to pull the plug on the box after a commit.)
> >
> > Frank Hubeny
> >
> > David wrote:
> >
> > > Hi all,
> > >
> > > When user issues "commit", does Oracle writes all changed data
 (of
> > > this user) to data file immediately? Or Oracle writes to data file
 only
> > > when database buffer is full or during checkpoint? IF Oracle not
 writing
> > > to data file immediately, and at that time, Oracle is crashed and log
> > > files (all) are corrupted, how does Oracle to recover the "committed"
> > > data?
> > >
> > > Thanks,
> > > David
Received on Fri Mar 30 2001 - 23:44:46 CST