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

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...

(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 Sat Mar 31 2001 - 14:14:46 CST