LOG_ARCHIVE_DEST problems

[Gus]

Have set 3 log archive destinations:

log_archive_start = true
log_archive_dest_1 = 'location=F:\Oracle\oradata\Athlon\archive1 mandatory'
log_archive_dest_2 = 'location=F:\Oracle\oradata\Athlon\archive2 mandatory reopen=180'
log_archive_dest_3 = 'location=F:\Oracle\oradata\Athlon\archive3 optional'
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC


Have played around with the destinations so that 2+3 will fail just to see what happens. I am expecting the DB to stall. No such luck archive logs are continuing to be written, but where apart from dest 1? Then I find that the archive logs are being written to my OFA directory 'oradata' where all my datafiles are kept. How is this happening as I understand it if the set mandatory archive dests fail then the DB just hangs, not happening here. If a destinatiion fails is there an error against it and if so how can i clear it so the dest can be renabled.

I restart the DB, check the v$archive_dest and all looks okay as the all dests are valid yet logs are still being archived as above that is to dest 1 and the oradata folder??

Any suggestion pls
Gus

[Mr.Hanky]

Did you check log_archive_duplex_dest, the name is self explanatory.

Check it out...

MH

[Gus]

Hi Hanky,
Not being used! Maybe its a case of the phantom archive dest!!

I'm going for a jog before this really pisses me off. Whats annoying (not that theres any black magic in using this command) is that I've looked in the online docs on oracles tahiti server where this is detailed but nowhere can i find a working example of the log archive dest parameter being used with the options, not sure whay I'm mentioning this but a bit of a definite shortsight which i often come across in their docs.

[Mr.Hanky]

Weird!
what is your log_archive_min_succeed_dest set to?
Maybe this is some new failsafe?
What version of Oracle is this?

MH

[Gus]

definitely weird, have not set the 'log_archive_min_succeed_dest' param. Did a listing on the v$parameter table and the dests are set as to the original post. this beats me!! Am using 8.1.6


Gus

[Gus]

Right let me tell you whats really weird, found out whats happening. As you all know those of you that have been following this hot story I originaly set my LA params as......

log_archive_dest_1 = 'location=F:\Oracle\oradata\Athlon\archive1 mandatory'
log_archive_dest_2 = 'location=F:\Oracle\oradata\Athlon\archive2 mandatory reopen=180'
log_archive_dest_3 = 'location=F:\Oracle\oradata\Athlon\archive3 optional'
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

Then just to try out and test the options i had set i changed the names of the directories in the OS for dests 2+3 where the LAs were supposed to go so as to cause the DB to complain and hopefully hang since it would no longer be able to find the LA directory as specified above in the init file. Well no such luck

EXAMPLE....

F:\Oracle\oradata\Athlon\archive3

I changed to....

F:\Oracle\oradata\Athlon\archive30

DB then decides to archive the logs to

F:\Oracle\oradata\Athlon

huh? and not only that but it appended the last part of the directory path of where it was supposed to go and was now ignoring, to the beginning of the new archive log file......

ARCHIVE3ATHLONT001S00011.ARC

meanwhile dest 1 is archiving happily as planned...

ATHLONT001S00011.ARC


How the hell is this happening

Fergus

[rmgirma]

If your objective is to see the db hang,
set your log_archive_min_succeed_dest=3 (since you have 3 arc destinations) and do lots of updates to generate redo logs. This will hang your db. Unless you specify the log_archive_min_succeed_dest, archiving is not required and Oracle will archive when it can and continue operating normaly when it can't.

[jovery]

Are you using 9i?

If so you will need to add a \ to the end of your log destinations

eg
log_archive_dest_1 = 'location=F:\Oracle\oradata\Athlon\archive1\ mandatory'
log_archive_dest_2 = 'location=F:\Oracle\oradata\Athlon\archive2\ mandatory reopen=180'
log_archive_dest_3 = 'location=F:\Oracle\oradata\Athlon\archive3\ optional'
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

This is why the database is not stalling, in you example the last part of the path is being used as the first part of the archive log name, Therefore it don't matter what you do to the directory.

[Gus]

Thanks Jovery have added the backslash on my 8.1.6 on Xp and it works now phew! Have also noticed after archiving has stopped due to problems with destinations etc the only way to restart archiving is to use the ALTER SYSTEM ARCHIVE STOP/START otherwise it dosen't automatically restart when the destinations problems have been cleared.

Gus

[jmodic]

Originally posted by Gus
How the hell is this happening

It's a known isue. Funny enough, Oracle can't decide if this is a bug or not. In fact they sometimes refer to this behaviour as being a feature, not a bug (well, at best they clasiffy it as "documentation bug")! I'm not kidding, check bug notes #974890 and #831114 on metalink!