redo files are a mess
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: redo files are a mess

  1. #1
    Join Date
    Feb 2007
    Posts
    1

    redo files are a mess

    Dear DBA-people,

    We're working with oracle 9.2 and because of the below message I wanted to get bigger redo files.

    Thu Feb 01 10:48:08 2007
    Thread 1 advanced to log sequence 155
    Current log# 1 seq# 155 mem# 0: D:\ORACLE\ORADATA\OCTOVH\REDO01.LOG
    Fri Feb 02 17:06:45 2007
    Thread 1 advanced to log sequence 156
    Current log# 2 seq# 156 mem# 0: D:\ORACLE\ORADATA\OCTOVH\REDO02.LOG
    Sat Feb 03 23:56:34 2007
    Thread 1 advanced to log sequence 157
    Current log# 3 seq# 157 mem# 0: D:\ORACLE\ORADATA\OCTOVH\REDO03.LOG

    And I did the following:
    Locate redo log files and determine their sizes:
    SELECT v$logfile.member, v$logfile.group#, v$log.status, v$log.bytes
    FROM v$log, v$logfile
    WHERE v$log.group# = v$logfile.group#


    Add larger redo log files:
    ALTER DATABASE ADD LOGFILE '/newredo1.log' size 10m;
    ALTER DATABASE ADD LOGFILE '/newredo2.log' size 10m;
    ALTER DATABASE ADD LOGFILE '/newredo3.log' size 10m;


    A production database should have more log members for each log group, and different storage devices should be used to increase performance and reliability.

    Drop the old log files. For each old redo log file, enter the ALTER SYSTEM SWITCH LOGFILE statement until that log file's status is INACTIVE. This is necessary to ensure that Oracle is not using that log file when you try to drop it.

    Then, drop the old redo log file with the following statement:

    ALTER DATABASE DROP LOGFILE '/redo01.log';
    ALTER DATABASE DROP LOGFILE '/redo02.log';
    ALTER DATABASE DROP LOGFILE '/redo03.log';


    Manually delete the old log files from the file system For each old redo log file, use the appropriate operating system statement to delete the unwanted log file from the file system.

    But now when I check with:
    SELECT v$logfile.member, v$logfile.group#, v$log.status, v$log.bytes FROM v$log, v$logfile WHERE v$log.group# = v$logfile.group#;

    I get this:
    SQL> SELECT v$logfile.member, v$logfile.group#, v$log.status, v$log.bytes FROM v$log, v$logfile WHER
    E v$log.group# = v$logfile.group#;

    MEMBER
    --------------------------------------------------------------------------------
    GROUP# STATUS BYTES
    ---------- ---------------- ----------
    D:\ORACLE\ORADATA\OCTOVH\NIEUWREDO01
    1 CURRENT 136314880

    D:\ORACLE\ORADATA\OCTOVH\NIEUWREDO02
    3 UNUSED 136314880

    D:\ORACLE\ORADATA\OCTOVH\NEWREDO1.LOG
    4 ACTIVE 125829120


    MEMBER
    --------------------------------------------------------------------------------
    GROUP# STATUS BYTES
    ---------- ---------------- ----------
    D:\ORACLE\ORADATA\OCTOVH\NIEUWREDO03
    7 UNUSED 136314880

    NIEUWREDO02, NIEUWREDO03 should be active and NEWREDO1 should be dropped, but i don't know how to fix this.

    Please help!

    Regards, SalsaFreak

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    why do you think you need bigger ones - those messages are normal

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Yeah, I'm confused...why would you want to make your logs bigger if they're only switching once a day?
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Get back to documentation, familiarize yourself with the concepts and then think again about that.
    You risk to kill your database by playing with redo logs while being unfamiliar with that

  5. #5
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    The whole exercise looked scary to me
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    The select tells it all. You have 4 logs:

    Status = Current means that LGWR is currently writing to this log.

    Inactive means the log is no longer required for instance recovery.
    Active means the log is needed for instance recovery.

    Unused means the log has not yet been written to.

    There is little risk to the DB by adding logfiles.... And dropping files. As long as the DB is in archive log mode. IT is in archive log mode right?

    But I agree, why did you resize the logs?

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