Re: will there be data loss?

On Feb 27, 7:46 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Feb 27, 2:33 pm, Ben <benal..._at_yahoo.com> wrote:
>
>
>
>
>
> > 10.2.0.2 EE, AIX 5.2 64bit. RMAN 10.2.0.2
>
> > I have run the scenario and know the outcome, but was surprised kind
> > of.
>
> > database is in archivelog mode. I start up rman and connect to target
> > and catalog then take a hot tablespace backup of the users tablespace
> > which is made up of one datafile.
> > I then proceed to log in via sqlplus and create a new table
>
> > balvey.testing( txt varchar2(200), numb1 number) tablespace users;
>
> > and insert 4 records and commit
>
> > alter system switch logfile
>
> > select count(*) from balvey.testing and see the 4 records.
>
> > exit sqlplus and using os commands I delete the one and only datafile
> > that makes up the tablespace
>
> > If I log into the database again and insert records into the newly
> > created balvey.testing and commit. Will the data that was inserted
> > after deleting the datafile be lost?
>
> Does a commit require only that the online redo log buffer space that
> belongs to the transaction gets flushed to disk or does a commit also
> force data buffers to be written to datafiles?
>
> If you know the answer to that one ... it might give you a hint about
> possible outcomes.- Hide quoted text -
>
> - Show quoted text -

From what I gathered in the concepts guide it looks like commits indeed only must succeed in writing redo buffers to the logfiles. But isn't there more to it than that?

This all came up because of an upgrade to our veritas netbackup software and wanting to do a test recovery. I went into it expecting the commit to fail and the data never being saved. So only data that was committed before the file deletion would be recoverable.

What I found was a mixed bag of treats. I believe it is what you guys are alluding to, partially.

When I logged back in and performed the insert and commit it was all successful. I could select from the table and see the new records. I inserted another row and then updated all the rows and committed. Still successful.

Then I selected from balvey.big_table (a 1 million row table) FAILURE. I got the ORA-01116 error opening file message. Then I tried another insert & commit into balvey.testing SUCCESS. I went on inserting a few more rows and committing. Then I tried to insert selecting from dba_objects and insert 400 rows. Finally I got a failure.

This leads me to believe that since the table was small and the database was fairly dormant as far as activity goes, the entire table was in the buffer cache and the commit was being written to the redo log files. Only when it was going to insert enough data to grab another data block and bring it into the buffer cache did it fail.

After all this I went back to read some one the processes DBWR, LGWR, and CKPT and that left me a little confused still yet. From the Oracle University manual for Fundamentals I course:

"DBWn defers writing to the data files until one of the following events occurs:
- Incremental or normal checkpoint
......."

"LGWR performs sequential writes from the Redo Log Buffer to the online redo log file under the following situations: - when a transaction commits
...."

"Every three seconds the CKPT process stores data in the control file to identify that place in the online redo log file where recovery is to begin, which is called a checkpoint. The purpose of a checkpoint is to ensure that all of the buffers in the Database Buffer Cache that were modified prior to a point in time have been written to the data files.

Note: CKPT does not write data blocks to disk or redo blocks to the online redo log files."

So if a checkpoint happens every 3 seconds and the DBWn writes blocks to the datafiles on a check point, why did it not fail until I tried to insert enough data to grab another data block? Received on Thu Feb 28 2008 - 07:58:45 CST