Re: Data- and control files not updated after insert?!!

What you actually need is to find out what is wrong with your AIX box, and then also work out a reliable backup strategy.

The idea that a commit should cause a write to a datafile is just plain wrong, still less that a commit should do so. That's why we have a buffer cache, and it's why DBWR is far lazier about writing to disk than LGWR is. Therefore it's your AIX box that is doing strange things, because a commit should simply not cause DBWR to flush, necessarily.

My best guess on zero information is that you've got fast_start_io_target set on the AIX box, and not on the Windows box. As you dirty a new block, so you flush an old one. Continual checkpointing, and unless you need to constrain your instance recovery times, not a good idea, performance-wise. But it could also be to do with ridiculously small redo logs on the AIX box which switch practically every insert (and thus causing a checkpoint).

You then compound the problem by taking backups in what sounds to be an extremely dodgy way. Your strategy appears to rely on O/S timestamps, when the one thing we can say about any O/S with any confidence, vis-a-vis Oracle, is that they are dumber than dumb. An O/S wouldn't know an Oracle block if it came up and hit it on the head. So why you'd think an O/S timestamp is *ever* a reliable indicator of what needs backing up or not is most mysterious.

What's worse, you indicate you are a 24x7 shop. That means, no doubt, you are doing hot backups. Yet the standard advice for hot backups is to issue the 'alter tablespace X begin backup' command. And if there's one thing that's guaranteed to do, it's to flush all dirty buffers to the relevant data file(s). Therefore, if you were doing hot backups properly, your timestamps *would* be updated (unless Sybrand's suggestion of O/S cacheing is kicking in). So the fact that they are not being updated means you aren't doing hot backups correctly, and you are therefore at profound risk of fractured blocks... ie, corrupted datafiles and lost data.

I'd be looking closely at why your AIX box is checkpointing so frequently, whether there are cacheing issues on the Windows box, and seriously looking at implementing a proper backup strategy.

Regards
HJR "Martijn Rutte" <opal_at_xs4all.nl> wrote in message news:aa4da990.0206180522.70047a5a_at_posting.google.com...
> I've got 2 oracle 8.1.6 databases, one running on AIX 4.3.3, the other
> on Windows2000.
>
> When I insert a record in a table on the Oracle-AIX database, the
> concerned datafile on the unix filesystem as well as both oracle
> control-files are instantly updated (date-time as well as size are
> adjusted).
>
> When I do exactly the same on my Oracle-Windows2000 database, the
> date-time and size of the datafile and the controlfiles is NOT
> updated, although I commit the transaction immediately and although
> the insert shows when I perform a 'SELECT * FROM' on the table I
> inserted the record in.
>
> As I am running an incremental backup of all files on the windows2000
> system and the backup tool decides whether or not to backup files
> comparing date/time and size with their last succesful backup, those
> oracle data- and controlfiles are NOT put on tape, thereby causing
> loss of database data.
>
> Strange thing: after shutting down and starting up the database, the
> date-time and size of those files is indeed adjusted. But I don't want
> to shutdown and startup every time before/after performing a backup,
> because the system should be up-and-running 24 hours a day.
>
> This causes me serious trouble. Anyone who can help me out? Do I need
> to set some parameters in the init-file, or somewhere in Windows2000?
>
> Any help will be greatly appreciated,
>
> Martijn Rutte
Received on Wed Jun 19 2002 - 00:57:01 CDT