Data- and control files not updated after insert?!!
Martijn Rutte
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
Vigi98
server, data are not written on disks immediately. They remain in memory.
And it's normal that data files are updated when you stop your database
because at that time, Oracle flushes the memory to the disks.
If you want that all data would be written on disk before you backup data
files, just do a checkpoint before your backup. I don't know how you launch
your backup, but if it is by a script (launched by the cron for instance),
just put in this script the SQL command to perform a checkpoint. This
command is the following :
alter system checkpoint
Good luck
"Martijn Rutte" <op...@xs4all.nl> a écrit dans le message news:
aa4da990.02061...@posting.google.com...
Yang
change time.
if your database is an 24h/day, you must do on-line backup (have you the
database on archivelog mode ?), this needs you issue 'alter tablespace xxx
begin backup', and after that, you should backup the associated files.
hth.
Yang
"Martijn Rutte" <op...@xs4all.nl> wrote in message
news:aa4da990.02061...@posting.google.com...
Sybrand Bakker
Apparently you have enabled the win2k file system cache.
I do know how to disable it on NT4, but regrettably I don't know how
to disable it on win2k. That is the direction you need to search in,
some other responses are not to be trusted.
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Knut Talman
> I do know how to disable it on NT4, but regrettably I don't know how
> to disable it on win2k. That is the direction you need to search in,
> some other responses are not to be trusted.
Just a curious question: I thought that the controlfile is updated after a log
switch, not everytime something is written to the online redo logs. Am I wrong?
Regards,
Knut
Stan
You were correct, controlfiles are updated with latest SCN's on a log
switch as its triggers checkpoint process, but there are many other ways
checkpoints get triggered too.
-Stan
Stan
You were correct, controlfiles are updated with latest SCN's on a log
switch as its triggers checkpoint process, but there are many other ways
checkpoints get triggered too.
-Stan
Howard J. Rogers
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" <op...@xs4all.nl> wrote in message
news:aa4da990.02061...@posting.google.com...
Knut Talman
> I do know how to disable it on NT4, but regrettably I don't know how
> to disable it on win2k. That is the direction you need to search in,
> some other responses are not to be trusted.
Sybrand,
when reading files which are cached (or parts of them), shouldn't the OS point
the reading process to the cache instead of reading from disk? So it shouldn't
matter if the files are in cache or not (of course it matters if the machine
shows a blue screen of death before flushing the cache).
Regards,
Knut
Howard J. Rogers
"Howard J. Rogers" <d...@hjrdba.com> wrote in message
news:aep6gb$els$1...@lust.ihug.co.nz...
> 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.
Sorry. That should have read "...still less that an INSERT should do so".
HJR
Martijn Rutte
Could you please tell me how you disable it on NT4?
Thanks,
Martijn
Sybrand Bakker <pos...@sybrandb.demon.nl> wrote in message news:<crquguojv0oj56v52...@4ax.com>...
kyoungrok, Lee
"Howard J. Rogers" <d...@hjrdba.com> wrote in message news:<aep6gb$els$1...@lust.ihug.co.nz>...
Niall Litchfield
news:717c65.020619...@posting.google.com...
> How about use ocopy made by Oracle.
You still need to issue the begin backup and end backup commands.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Howard J. Rogers
would otherwise not be copyable because they are locked (ie, in use) by
Oracle. NT has a habit of refusing to do things to files which other people
are using, and OCOPY was designed to get around that.
Although I'll go one stage further and say that I've never used OCOPY in my
life, even doing hot backups on NT, because explorer has always permitted me
to copy files regardless.
None of which gets around the fundamental issue: OCOPY still means the
operating system is doing the copying, and the O/S is totally oblivious to
the internal structure of what it is copying, and fractured blocks are
therefore just as likely to happen using it as not. Therefore, there is
still a requirement to issue 'begin backup' and 'end backup' commands.
It is really very simple: You cannot cheat the system. There are no
circumstances when you can avoid using 'begin backup' and 'end backup',
unless you are using RMAN, or unless you are using some form of 'split
mirror-copy-resilver' technique... and even there, I'd be wary.
Regards
HJR
"kyoungrok, Lee" <tun...@korea.com> wrote in message
news:717c65.020619...@posting.google.com...
Sean M
>
> It is really very simple: You cannot cheat the system. There are no
> circumstances when you can avoid using 'begin backup' and 'end backup',
> unless you are using RMAN, or unless you are using some form of 'split
> mirror-copy-resilver' technique... and even there, I'd be wary.
Agreed 100% - I'd go even farther and say, to the best of my knowledge,
no sort of disk subsystem/split-mirror technology can prevent fractured
blocks during an Oracle hotbackup. All of the ones I've had experience
with require the begin/end backup command (unless of course the TS is
read-only or offline).
Regards,
Sean M