"David" <david_petit_at_yahoo.com> wrote in message
news:3AC67C77.78C0EF0_at_yahoo.com...
> "Howard J. Rogers" wrote:
> >
> > "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
>
> but the interesting point is, as Oracle Concepts manual:
>
> When a transaction is committed, the following occurs:
> 1. The internal transaction table for the associated rollback segment
> records that
> the transaction has committed, and the corresponding unique system
> change
> number (SCN) of the transaction is assigned and recorded in the table.
> 2. The log writer process (LGWR) writes redo log entries in the SGA's
> redo log
> buffers to the online redo log file; it also writes the transaction's
> SCN to the
> online redo log file. This atomic event constitutes the commit of the
> transaction.
> ....
>
> however, as Frank (also me) mentioned, the timestamp of all redo log
> files remain the same, so LGRW writes nothing to the redo log files. How
> (and where) Oracle restore the committed record? May be after pulling
> the plug, and before the computer turn off, Oracle writes the entry to
> the redo log....
Well, I haven't checked timestamps, and you're talking at that point how the
O/S handles updating of timestamps on a file, which is beyond my area of
expertise.... what I'm getting at is that I wouldn't trust the timestamps to
be telling you anything.
The transaction gets written to the log files when a commit is issued, and
you are told the commit has happened only after that write takes place. And
yes, you can see the checkpoint number from a variety of places -I rather
thought it was in v$log, but I may be mistaken (and don't have a database to
hand to check, sorry.)
Regards
HJR
>
> Or is it possible to see the SCN of the redo log files such that I know
> whether there is different (of SCN) before and after commit.
>
> David
>
>
> >
> > > (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 - 04:30:39 CST
