Corrupt data error when using Windows backup on Oracle

[Former Member]

Our SAP servers include a SDLT internal tape drive that we use for doing a complete system backup. When using Windows Backup we get the following message in the backup log:

WARNING: Portions of "\oracle\T00\sapdata1\protd_2\PROTD.DATA2" cannot be read. The backed up data is corrupt or incomplete.

This file will not restore correctly.

This is occuring on a couple of different systems but the wierd thing is when this happens in occurs on one Oracle drive in one system and the other Oracle drive on another system i.e. the G: drive on our TST sysytem and the E: drive on another. The E: and G: drives contain the main Oracle datafiles.

Has anybody ever encounter something like this and what can I do about it?

Thanks;

Gale S.

[Former Member]

No longer using tape backup

[markus_doehr2]

Another thing, that comes to my mind, is an issue with "dynamic partitions" on W2K3. We have had some similar errors during backups if huge file are stored on Windows dynamic partitions. We got all sort of I/O related errors (Access Denied, file inaccessible, out of memory etc.) when we tried to backup or copy the files off the SAN to somewhere else.

After we switched the filesystem to "Basic" everything ran as expected.

Just another quick shot...

--

Markus

[Former Member]

Thanks for the reply and forgive me for missing that piece of info. We have tried this with Oracle completely shutdown (including services), after re-boot, after a full re-start etc. I have even gone as far as setting all the Oracle services to Manual in the Services applet and then re-booting/starting the server. We have four (not counting our Solution Manager) servers. This currently occuring on just two of those servers although this has happened on all four systems at one point or another.

I do want to point out that the SAP scheduled backups (DB13) seem to behave normally. The only time we have a problem it is either a file lock conflict (with Veritas) or a media error (two in a row for a tape and we toss the tape).

I have all the latest firmware, drivers, service packs and any thing else that I could think of. We have been in contact with Dell (they made the servers) extensively with this and the best they could come up with was to a:) switch to a backup untility such as Verias; b:) Contact Oracle (since this is occurring in the Oracle folders) and c:) re-install the OS.

Right now if we have to do a non-SAP backup we are using our Veritas backup server. This works well and does not have the corruption problem the internal tape gives us.

Gale S.

[markus_doehr2]

Do you shut down Oracle during the backup? If not, then the file is "open" and you won´t be able to back it up consitently.

You have two options:

- shutdown Oracle during the backup

- use a real backup software and backup logically (not physically the files)

--

Markus

[lbreddemann]

Do you shut down Oracle during the backup? If not,

then the file is "open" and you won´t be able to back

it up consitently.

Sorry, but that's not quite true. When we do ONLINE BACKUPS of Oracle Databases we're just copying the files aways while the database is working in the files.

Of course the copies are inconsistent then in terms of database blocks (that's why we need to recover logs after a restore of such a backup) but not in terms of filesystem blocks.

Especially no backup tool would know about the fact that a Oracle block had been changed after it was copied.

Therefore just doing a offline backup would only save the time for the archive log recovery when the database gets restored. But it would mean DOWNTIME (very bad!).

A "logical backup" (yes, we all like how MaxDB is doing it ) is anyhow a good idea. Oracle RMAN is far to few used in SAP environments (well, that's what I think).

But - since the "open files" are not the issue here - this won't fix your backup solution.

KR Lars

[markus_doehr2]

Especially no backup tool would know about the fact

that a Oracle block had been changed after it was

copied.

Now I have to contracdict

There are backup tools (e. g. OpenFile Manager from Legato/EMC), that track the filesystem changes and makes sure, the backups are consistent in sense of filesystem blocks. We´re backing up some ORA databases (non-SAP, 7.3.4 and 8.1.7) for ages now - and I never saw this kind of corruptions.

I just wonder, how the database would deal with such "inconsistent" files. Given the fact, you start a backup at filesystem block 0, the database is being backed up and a transaction is committed and something is written to block 100, 200 and 500. The backup is on block 300. If you now restore the database, it has the already commited block 500 in the file but 100 and 200 are not yet written in the database file. The redo will then find an already commited transaction on 500. What magic will tell the engine, that this is due to an online database backup? I mean, if you set the tablespace to backup, the engine "knows" the state - but if you just backup the file, the engine is unaware of that... I´m just curious how this is handled...

A "logical backup" (yes, we all like how MaxDB is

doing it ) is anyhow a good idea. Oracle RMAN is

far to few used in SAP environments (well, that's

what I think).

I was not thinking about MaxDB - not in this case

--

Markus

[lbreddemann]

Especially no backup tool would know about the

fact

that a Oracle block had been changed after it was

copied.

Now I have to contracdict

There are backup tools (e. g. OpenFile Manager from

Legato/EMC), that track the filesystem changes and

makes sure, the backups are consistent in sense of

filesystem blocks. We´re backing up some ORA

databases (non-SAP, 7.3.4 and 8.1.7) for ages now -

and I never saw this kind of corruptions.

Well, these Backup-Tools do perform copies I/O-Consistent. That's different than DB-Block-consistent in the first place. Anyhow due to the fact that the DBMS do have a syncronizing between I/Os and writing out the blocks this leads both to consistent blocks.

I just wonder, how the database would deal with such

"inconsistent" files. Given the fact, you start a

backup at filesystem block 0, the database is being

backed up and a transaction is committed and

something is written to block 100, 200 and 500. The

backup is on block 300. If you now restore the

database, it has the already commited block 500 in

the file but 100 and 200 are not yet written in the

database file. The redo will then find an already

commited transaction on 500. What magic will tell the

engine, that this is due to an online database

backup? I mean, if you set the tablespace to backup,

the engine "knows" the state - but if you just backup

the file, the engine is unaware of that... I´m just

curious how this is handled...

Well the trick here is: we tell the database before we plan to copy the data. When a ONLINE backup is done, the tablespaces are set into BACKUP mode. This changes two things for us:

1. The datafile headers of these tablespaces are not updated anymore although dirty blocks are still written out to these files. Since the control files that keep the SCN (system change number) are updated nevertheless, the database will know at recovery time that these files had been in online backup mode and do need recovery to become consistent again.

2. The UNDO-information is not only written out to the UNDO/ROLLBACK-Tablespace but additionally to the REDOLOGS. So, for the time where the tablespaces are in BACKUP mode, we have all information needed to make a block consistent again - regardless if a transaction had been commited or rolled back - in the redolog. So a full recovery of all changes is possible with

that.

That's basically the "magic" behind this.

If the copy of the datafile is done without this - well, then there's no magician in the world that would been able to get it consistent again. Online Backups with Oracle are only possible with BACKUP mode or RMAN (ok, and possible some 3rd party hacks...).

KR Lars

[markus_doehr2]

If the copy of the datafile is done without this -

well, then there's no magician in the world that

would been able to get it consistent again. Online

Backups with Oracle are only possible with BACKUP

mode or RMAN (ok, and possible some 3rd party

hacks...).

Ok... that means, that you set the tablespace to backup mode if the backup is started via DB13, the original (first) post didn´t say that and I was implying, because he wrote that "Windows backup is used", that he´s just backing up the files from an operating system view without any tablespace altering and thus my assumption, that he can´t backup "open files".

--

Markus