redo files are a mess

[SalsaFreak]

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

[davey23uk]

why do you think you need bigger ones - those messages are normal

[OracleDoc]

Yeah, I'm confused...why would you want to make your logs bigger if they're only switching once a day?

[Bore]

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

[simply_dba]

The whole exercise looked scary to me

[ixion]

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?