Alter system archivelog current
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Alter system archivelog current

  1. #1
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Alter system archivelog current

    I'm running 8.1.7.4 in archivelog mode (of course) under NT.

    Every 10 minutes I issue a ALTER SYSTEM SWITCH LOGFILE, wait 10 seconds for archiving to finish, then start zipping the archlog(s) to send them across the country.

    It would seem MUCH better to use ALTER SYSTEM ARCHIVE LOG CURRENT and not wait. Can I be sure that the archlog file has been totally written and any OS sharing locks released, before control is returned (ARCH being an asychronous process)?

    (The answer ought to be "Yes" - but I can't find it anywhere).

    TIA - DaPi
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Alter system archivelog current

    Originally posted by DaPi
    It would seem MUCH better to use ALTER SYSTEM ARCHIVE LOG CURRENT and not wait. Can I be sure that the archlog file has been totally written and any OS sharing locks released, before control is returned (ARCH being an asychronous process)?

    (The answer ought to be "Yes" - but I can't find it anywhere).
    The answer is kinda "YES", but your scenario might still not work!

    I think (but have never tested it, it's just my impression) that after ALTER SYSTEM ARCHIVE LOG CURRENT command is executed you can be sure that archlog file has realy been written.

    BUT: You have no guarantie that the previous redo logs have also been archived by that time! So for example if your current redo log is archived as seq# 100009, your ZIP file might contain archfiles #100009, #100007, #100006, ...., but #100008 might be missing because it has not been archived yet.

    Archlogs might be created out of sequential order, meaning that nothing is stopping oracle to write archlog #N+1 even if the archiving of #N or any previous redologs has not been finished yet.

    And BTW, you could encounter the same scenario with your present setup (ALTER SYSTEM SWITCH LOGFILE + wait).

    If you would like to avoid this, then you'd better use "ALTER SYSTEM ARCHIVE LOG ALL" instead.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Jurij,

    Thanks for your input. I find these parameters in the 8i doc for ALTER SYSTEM ARCHIVE LOG:

    < QUOTE >
    CURRENT
    Specify CURRENT to manually archive the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, Oracle archives all redo log file groups from all enabled threads, including logs previous to current logs.

    Restriction: You can use this clause only when your instance has the database open.

    Note: If you specify a redo log file group for archiving with the CHANGE or CURRENT clause, and earlier redo log file groups are not yet archived, Oracle archives all unarchived groups up to and including the specified group.
    < UNQUOTE >

    < QUOTE >
    ALL
    Specify ALL to manually archive all online redo log file groups from the specified thread that are full but have not been archived. If you omit the THREAD parameter, Oracle archives all full unarchived redo log file groups from all enabled threads.
    < UNQUOTE >


    So I think ALL will NOT cause a log switch. CURRENT seems to do what I want - UNLESS it returns control before it has finished!

    Yes, I realise that my present setup is pretty poor (but well intentioned - and usually works!).

    Thanks again - DaPi
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Of course I could do a SWITCH LOGFILE then a ARCHIVE LOG ALL . . .
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hi Dapi

    Use ARCHIVE LOG CURRENT rather than a variation.

    ARCHIVE LOG CURRENT > This option archives all the inactive redo log groups including the one that is currently written into.

    ARCHIVE LOG ALL> This variation archive all inactive redo log groups that have not been archived yet. (Except the current one)

    Hope this help
    A bon attendeur salut.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Ablakios,

    Merci. That was indeed my conclusion.

    But we still haven't confirmed that the process is complete before I get (e.g.) the next SQL> prompt - ARCH being an asynchronous process. Both Jurij and I THINK that's the case . . . does any one KNOW?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Just to close off on this:

    ALTER SYSTEM SWITCH LOGFILE returns control in less than a second.
    ALTER SYSTEM ARCHIVE LOG CURRENT takes up to two seconds - so it's doing something extra before returning control.

    For about a month, I have been doing ALTER SYSTEM ARCHIVE LOG CURRENT and not waiting before processing the archivelog(s). No problems encountered. (Though I do get a "Falied to archive log# x seq# yyyy" in the alrt.log - it usually completes within the same second.)

    So it looks like I was wrong to doubt.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width