Re: Informix, Sybase, Oracle or MS SQL server

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/04/29
Message-ID: <4m2hrb$5cp_at_inet-nntp-gw-1.us.oracle.com>


Paul Chen <pchen_at_cougar.mv.com> wrote:

>tkyte_at_us.oracle.com (Thomas J Kyte) wrote:
>>
>>- Database does not need to be taken offline,
>>hot (online) backups have been part of Oracle since version 6
 

>But Tom, can we use hot backup on UNIX with Oracle database on raw
>devices?

Yes, absolutely.

>The impression I got from Michael R. Ault's book "Oracle 7.0
>Administration & Management" seems to imply the negative answer
>to the question above. This is what he said in his book, on
>page 287.
 

> "The hot backup differs from the cold backup in that only
> sections of the database are backed up at one time. Under
> UNIX this will require the use of normal mounted file systems
> and not RAW devices."

There are some cases where Oracle only supports RAW Partitions, the Oracle parallel server for loosely coupled systems for example. Hot backup is fully supported on these platforms (and all others).

I think the wording choosen by Mike in his book is poor on this page. He says for example:

<quote>
8.1.3 UNIX Backups

The type of backup you perform in UNIX is dependent on whether you use RAW devices or not. RAW devices will require a backup of the entire device, while the use of the mounted file systems will allow partial backups. <!-- My addition "OF DEVICES">

....

</quote>

If you have a raw device, you have to back up the entire device. If you are using a cooked file system and have lots of little datafiles on them, you back up little datafiles one at a time, you don't backup the entire filesystem (device).

In short, you either backup an entire raw disk partition or not. You copy the entire partition, not just the part of the partition containing data (so if you have a 2 gig raw partition and only have a 1 gig datafile on it, you will dd the entire partition, not just the 1 gig part of it you are using. This is OK, since you would never put a 1 gig datafile on a 2 gig raw partition since the other 1 gig would always be wasted and couldn't be used by anyone else. You would never have this case).

He is quite wrong in stating

 >   "The hot backup differs from the cold backup in that only
 >    sections of the database are backed up at one time.  Under
 >    UNIX this will require the use of normal mounted file systems
 >    and not RAW devices."

I have done many a hot backup on 100+ gig databases consisting entirely of raw partitions. This is absolutely wrong. On the same page he goes on to say:

<quote>
A hot backup, or one taken while the database is active, can only give a read-consistent copy, but it doesn't handle active transactions. </quote>

Is wrong as well. A hot backup actually takes Inconsistent copies (the datafiles are not consistent with respect to a point in time). The datafiles continue to be written to during a hot backup. A hot backup requires both the datafile(s) and archived redo log files to recover with. The restore process has you putting back the inconsistent datafile and using the redo log files to roll forward/back transactions to recover the datafile to a point in time and make it consistent again.

>So, if he is correct, are we required to use filesystems to build
>Oracle 7.0 database on UNIX if we want hot backup? If so, will this
>causedata integrity problem? Again, from the same book, on page 18,
>Michael R. Ault said the following.
 

> "If you use normal UNIX file devices, there is no guarantee that
> all updates will be written to disk in a timely manner. This is
> due to the UNIX file buffers. If a system crash should occur
> between the time data leaves the Oracle buffers and transits
> through the UNIX buffers, data could be lost."

He again is incorrect here. There has been an ongoing thread in these newsgroups (comp.databases.sybase and comp.databases.oracle "Database Writing Architectures" is the subject of the thread). As pointed out by many in that thread, UNIX allows for either the O_SYNC options when opening a file for update or the unix system call fsync() can be used on a file to sync it up (flush the buffers). As was also pointed out in that thread, this can cause severe performance implications in a Single Process, Multi-Threaded database. Since Oracle is not a single process database, we do not suffer from the performance impact. In fact, since the DBWR process is the only process writing to the datafiles and we can have many DBWR processes going to simulate ASYNC IO on cooked partitions, his statement of "... performance for disk IO by over 50 percent ..." is way overstated. In most implementations I have seen, a modest 10-15% can be gained in certain heavily IO bound applications by switching to raw partitions, 50% is way overstated.

>Both hot backup and data integrity are essential to many 24x7 shops.
>But Michael R. Ault's book seems to imply that hot backup and
>raw devices are mutually exclusive for Oracle 7.0 on UNIX. Is he
>right or wrong? Tom, can you clarify these issues? Thanks.

he is wrong (mostly due to poor wording in the above cases, the crux of what he writes is for the most part correct).

>Paul Chen, Ph.D.
>Database Consultant
>Disclaimer: My opinions only!

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government



opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Mon Apr 29 1996 - 00:00:00 CEST

Original text of this message