Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Unanswered: When does a Redo Log File get overwritten or switched?

    Howdy all. I see that I can specify the size of a Redo Log Files, I can make one inactive and switch to another, I can even archive an RLF. But lets say I totally neglect my RLF duties. Does Oracle not have a way to create a new RLF once the current one gets full? Or is there not a way to tell it to "auto grow"?

    TIA, CFR

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Unwilling or incapable to Read The Fine Manual?
    http://download-west.oracle.com/docs...cess.htm#i7261
    Please free to follow the links in the page above to answer all your questions.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by anacedent
    Unwilling or incapable to Read The Fine Manual?
    http://download-west.oracle.com/docs...cess.htm#i7261
    Please free to follow the links in the page above to answer all your questions.

    Both willing and able. However, I was reading this one:

    http://download-west.oracle.com/docs...onlineredo.htm

    Which is where I got all the fun facts I listed in my post. The answers may be in that one and perhaps I missed them. Regardless, I'll check out the one you provided, thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The Concepts Manual to which I provided the URL is a treasure trove of Oracle details (& of course concepts).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    redo logs are circular. If you define 3, then it will write to redo1, then switch to redo2 when redo1 is full, then switch to redo3, then overwrite redo1. If an active transaction is still using redo1 when it needs to overwrite it, the database will halt until it can switch logs. Archiving is simply writting a copy of the previous redo log out to disk for retention.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by beilstwh
    redo logs are circular. If you define 3, then it will write to redo1, then switch to redo2 when redo1 is full, then switch to redo3, then overwrite redo1. If an active transaction is still using redo1 when it needs to overwrite it, the database will halt until it can switch logs. Archiving is simply writting a copy of the previous redo log out to disk for retention.

    Which is what I thought, but it leads me to more questions. Please bear in mind Im a SQL Server guy, so I'm comparing it to SQL Servers Tranaction Log and how it behaves (which is perhaps where Im going wrong). Redo1 needs to be overwritten, but it still has an open transaction. Is there no way to tell it to autogrow? Does this whole thing not make needing to monitor Redo Logs a constant activity?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does this whole thing not make needing to monitor Redo Logs a constant activity?
    I've been doing Oracle for more than a decade & have NEVER monitored REDO logs.
    Part of your confusion seems to be EXACTLY what goes into the REDO log file.
    Only at the time of COMMIT are changes written to the REDO log file.
    Even for a long running "transaction", NOTHING is written to the REDO until the COMMIT is issued; because until the COMMIT occurs a completed TRANSACTION does not exist.
    Therefore your feared scenario never occurs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2004
    Posts
    128
    Awesome, thanks!

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The reason you dont have to worry about long running transactions is that Oracle can overwrite a redo log after it has been archived NOT all current transactions for that redo log are complete.

    Also Anacedents comment about redo not being written until the COMMIT is wrong redo is written while the transaction is running otherwise any long running transaction would bring the database to a quick halt.

    For example we are currently running a process which copies data in a single long running transaction which can last for a couple of hours. Now even though there is no commit till the end you can see archived redo logs being created continously during this time (and no other transactions are going on).

    Alan

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Below is from:
    http://download-west.oracle.com/docs...htm#sthref1546

    When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries. The corresponding changes to data blocks are deferred until it is more efficient to write them. This is called a fast commit mechanism. The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle returns a success code to the committing transaction, although the data buffers have not yet been written to disk.
    Last edited by anacedent; 04-06-07 at 16:38.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You didnt get what it means, when a transaction is active redo is being written to as each statements executes. When the user commits or rollbacks a commit or rollback record is written.

    So your statement "Only at the time of COMMIT are changes written to the REDO log file" is not correct.

    Alan

  12. #12
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by AlanP
    For example we are currently running a process which copies data in a single long running transaction which can last for a couple of hours. Now even though there is no commit till the end you can see archived redo logs being created continously during this time (and no other transactions are going on).

    Alan
    I hate to bring this topic back down to newbie level while you guru's debate the finer points, but this statement is a bit confusing. From what I am reading in my book and online, I specify where the archived redo logs will go. They do not automatically get created unless I run in ARCHIVELOG mode and tell Oracle where I want them to exist. But your above statement makes me think they will be created on their own, without my intervention?

  13. #13
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you are in archivelog mode and you have told the database where the archived redo logs should go, then yes it is completely automatic with no user intervention (and you should be on archivelog mode unless you have very good reason not to be).

    This is why you dont have to worry about redo logs or why they dont need autoextending. When a transaction is going on (say a very long one which maybe involves many GBs of data and is bigger than your redo logs) the redo is being written to the redo log buffer (in memory) and then to the active redo log on disk. When the redo log is full Oracle will switch to the next one and start archiving the old redo log by creating an archive redo log file. Now providing the old redo has been sucessfully archived before it is needed again then you are OK. If it hasnt finished archving Oracle will halt ALL active transactions until the old redo log has finished archiving. So your long running transaction can keep writing data to the redo logs providing they get archived before they are need again. When your transaction finally commits or rollbacks a special commit or rollback record is written.

    Now your archived redo logs should be backed up (using RMAN usually)and once they have been they can safely be deleted (again RMAN handles this).
    If you have setup RMAN backups you may find then using Oracle Enterprise Manager is the best way to do this.

    To summarise make sure your database is in archivelog mode and then you just have to make sure your archivelog directory has enough diskspace to hold all the archivelogs before your next backup runs and clears them out.

    Alan

  14. #14
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One minor point I forgot to mention is that log_archive_start=TRUE for the archiver to be automatically started on database startup.

    Alan

  15. #15
    Join Date
    Nov 2004
    Posts
    128
    Excellent!

    Thank you.

Posting Permissions

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