DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Redo log group deadlock - unsufficient space

  1. #1
    Join Date
    Jul 2003
    Posts
    136

    Redo log group deadlock - unsufficient space

    URGENT URGENT

    Environment: Windows 2003 server
    Oracle 10g

    We have a server on which oracle/db was set up where
    redo log group 1 is 10G (was set up by mistake)
    redo log group 2 is 40MB
    redo log group 3 is 40MB

    None of group's status is 'archived'. Gp 3 is the current one.
    I need to remove this group and create a new one with 40 MB size

    I cannot delete group 1 as its not archived yet. Its waiting to write its contents but doesn't have space available.
    ---
    ORA-00350: log 1 of instance db01 (thread 1) needs to be archived ORA-00312: online log 1 thread 1: 'D:\ORACLE\PRODUCT\10G\ORADATA\db01\REDO01.LOG'
    ----
    The server is only left with 2 GB free space. There are still new archive files being written to.

    Please Please suggest if there is a way of replacing 10G redo gp with 40MB without loosing the data... or do I have to reinstall db/oracle?

    -D

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Just add a few new redo log groups, do a log switch or two, then drop and recreate the first redo group.

  3. #3
    Join Date
    Jul 2003
    Posts
    136
    Quote Originally Posted by gandolf989
    Just add a few new redo log groups, do a log switch or two, then drop and recreate the first redo group.
    It doesn't let me drop gp 1.

    Select Group Status # of Members Archived Size (KB) Sequence First Change#
    1 Inactive 1 No 10485760 5 418393
    2 Inactive 1 No 40960 384 9924154
    3 Inactive 1 No 40960 383 9910447
    4 Current 1 No 40960 385 9928380

    see error below
    ------------
    Tue Jul 11 10:05:39 2006
    ARC1: Evaluating archive log 1 thread 1 sequence 5
    ARC1: Archiving not possible: No primary destinations
    ARC1: Failed to archive log 1 thread 1 sequence 5 (4)
    Tue Jul 11 10:05:39 2006
    Errors in file d:\oracle\product\10g\admin\spe01\bdump\spe01_arc1_4944.trc:
    ORA-16014: log 1 sequence# 5 not archived, no available destinations
    ORA-00312: online log 1 thread 1: 'D:\ORACLE\PRODUCT\10G\ORADATA\SPE01\REDO01.LOG'
    ----
    -Daljit

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    I think you need to take a risk here if there is no space available. As a last option, clear the unarchived log file, switch the group. Drop the 10GB log group and create a new one if you want.

    Warning:
    1. Do this as the last option
    2. take a backup immediately.

    Better do this with the help of DBA. And this should be the last option.. Complete recovery is not possible, because you didn't archive the log. before that you can try to archive the log file to a different location.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Hard Disk is cheap today.
    Just throw $$.

    Tamil

  6. #6
    Join Date
    Jul 2002
    Posts
    335
    Have you tried taking the database out of archive mode and then dropping the redo log group?
    Bear in mind you'll need to do a backup straight away, and any standby will be have to recreated/restored. Needless to say you'll want to re-enable archiving asap.

    Bazza

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