We help IT Professionals succeed at work.

Redo Log Group Invalid

ddandekar
ddandekar asked
on
Medium Priority
3,394 Views
Last Modified: 2012-05-05
I was creating redo log groups to expand the size from 32 MB to 64 MB.

I created two groups and while creating next group group no 10 there was a problem in disk space availability and the group was not created.

Hence I tried to create redolog group again the operation fails giving message
ora-01185: logfile group no 10 is invalid.

Now I can not create any new redolog group since Oracle assigns next group number automatically.

How to come out of this situation ?

Comment
Watch Question

KongAWS Certified Solutions Architect - Professional
CERTIFIED EXPERT

Commented:
You may have hit your MAXLOGFILES limit.

Query v$controlfile_record_section where type = 'REDO LOG' examine records_total and records_used column.

Archive off the remaining 32MB log files, drop them and add 64MB log files.

If you have a limit of 9, and would like to have more log files you will need to recreate your controlfile, readjusting MAXLOGFILES parameter.

Author

Commented:
The max logfile parameter is 16 and there 10 logfiles in 6 groups 4 groups having 2 members each and 2 groups have 1 member each.
AWS Certified Solutions Architect - Professional
CERTIFIED EXPERT
Commented:
Got to run now, but can you display output from:

select * from v$logfile;
select * from v$log;
select * from v$controlfile_record_section where type = 'REDO LOG';

Thanks,
K

Commented:
Hi,

have you check fist if there is no physical file created? if so deleted and re-create new group.

have you try with command

ALTER DATABASE <db_name> ADD LOGFILE GROUP <group_no> '/usr1/oracle/oradata/redolog/logfile7A.ora','/usr2/oracle/oradata/redolog/logfile7B.ora';

so you can control the group no ?!?!?!
it's just an example.

best regards,
Marius Nicoras
All your redo logs should be the same size.  This helps during recovery purposes.  Besides, I have never seen a database with 10 groups before.  Do you really need that many? If your database is switching logs that fast, maybe you just need bigger redo logs.

Author

Commented:
For Marius Nicoras

i have tried creating new log group by giving this command but still the ora-01185 comes

For Kong
Select * from v$logfile;
Group  Status   Member
04              /data4/orc8/log4.ora
04     Invalid  /data17/orc8/log4a.ora
05              /data4/orc8/log5.ora
05     Invalid  /data17/orc8/log5a.ora
06              /data4/orc8/log6.ora
06     Invalid  /data17/orc8/log6a.ora
07              /data4/orc8/log7.ora
07     Invalid  /data17/orc8/log7a.ora
08              /data4/orc8/log8.ora
09              /data4/orc8/log9.ora
10 rows selected.

Shows 6 groups with current group and other inactive groups
Select * from v$controlfile
TYPE                   RECORD_SIZE    RECORDS_TOTAL     RECORDS_USED
----------------- ---------------- ---------------- ----------------
     FIRST_INDEX       LAST_INDEX       LAST_RECID
---------------- ---------------- ----------------
REDO LOG                        72               16               16
               0                0               29

As it seems control file has already used 16 records.

Now I will try to drop a group and see if I can add new group.


Author

Commented:
After dropping one log group the control file record still shows that 16 records have been used.

That means that there is no other way except to recreate controlfile and add log members as required or receeation itself will reset the used entry to 9 / 10 as is the case.

I have been avoiding this option because people say it is risky and I have never created and reused a control file.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Error:  ORA 1185
Text:   log file group number <num> is invalid
Cause:  An ALTER DATABASE ADD log file command specified a redo log number for   the new redo log that is too large.
Action: Specify a valid redo log file number.

Modify the initialization parameter LOG_FILES or recreate database with a
higher value for MAXLOGFILES.
 
You are creating a new database and when adding the redo logfiles you receive the following error:
 
    ORA-01185: "logfile group number %s is invalid"
        Cause: An ALTER DATABASE ADD LOGFILE command specified a log  number for the new log which is too large.
Action: Specify a valid redo log file number.
 
LOG_FILES:
----------
 
Perform the following steps to change the value of LOG_FILES:  
 
1. Edit the database init.ora file, which is usually located in the
   "$ORACLE_HOME/dbs" directory.  Change the value of LOG_FILES to a  higher number.  
 
   If the parameter LOG_FILES is not in the init.ora, this means that it is using the default value of 2.  Add a line to the file similar to:
 
       LOG_FILES = <number_between_2_and_255>
 
   where you replace the <...> with the maximum number of redo logfiles
   that can be opened at runtime.
 
2. Shutdown the database cleanly, using either the normal or immediate
   option.
 
       SVRMGRL> shutdown immediate
 
3. Restart the database to have the changed init.ora file read.
 
       SVRMGRL> startup
 
 
 
MAXLOGFILES:
------------
 
MAXLOGFILES is set during database creation, via the create database
statement, and can only be changed by recreating the database.  
 
 
Explanation:
============
 
The values specified for MAXLOGFILES and/or LOG_FILES are too small for your
needs.

The init.ora parameter LOG_FILES sets the current maximum number of Redo Logs.
 LOG_FILES can temporarily reduce the limit set by MAXLOGFILES, but it cannot
raise MAXLOGFILES.
 
MAXLOGFILES in the CREATE DATABASE statement sets the absolute maximum number
of Redo Log files.
 
If neither MAXLOGFILES or LOG_FILES is used, the default number of Redo Logs
is 255.
 

KongAWS Certified Solutions Architect - Professional
CERTIFIED EXPERT

Commented:
From the query results it looks like you have 6 redo log groups, the last two with no log members. But still well under the 16 MAXLOGFILES.

Instead of adding a new group, try adding logfiles to group 1 (which you have already dropped) ie:

alter database add logfile group 1 '/data4/orc8/log1.ora' size 64M;

Author

Commented:
select * from v$controlfile_record_section where type = 'REDO LOG';

After i checked this I realized that entries in control file are full. However control file still retains old entries and does not overwrite in case a redo log file has been dropped. This creates a problem requiring recreation of control file which is risky.