REDO files messed up, need help

[aspijker]

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 '<redo log directory>/newredo1.log' size 10m;
ALTER DATABASE ADD LOGFILE '<redo log directory>/newredo2.log' size 10m;
ALTER DATABASE ADD LOGFILE '<redo log directory>/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 '<redo log directory>/redo01.log';
ALTER DATABASE DROP LOGFILE '<redo log directory>/redo02.log';
ALTER DATABASE DROP LOGFILE '<redo log directory>/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, Aspijker

 

[slaing]

First of all , if your logfiles are only switching once a day , why do you think you need larger ones ?

If they were switching every 10 minutes then I could see a case for making them larger.

Next, based on your SELECT from V$LOGFILE what you currently have is : 4 groups with 1 member in each group. From what I can see, the system should work happily with that. It doesn't really matter that one of your groups (group 4 : NEWREDO1.LOG) is a different size from the others. It's only important for all the members of a group to be the same size, and Oracle won't let you break that rule.

NIEUWREDO02, NIEUWREDO03 are in different groups, so they can't both be active. Use ALTER SYSTEM SWITCH LOGFILE repeatedly to make each of them ACTIVE in turn.

The command to drop a logfile is ALTER DATABASE DROP LOGFILE and then either a filename, or GROUP or MEMBER clause.

Is this a live database that you're working with ?
If so, PLEASE set up a test system (even on your PC) and try out adding and dropping MEMBERs and GROUPs until you are confident with the commands.


Steve

 

[aspijker]

Dear steve,

thanks for your reply, it certainly cleared some things for me.

First of all how do you know my files are switching once a day???

And because of the message in the logfile
Thread 1 advanced to log sequence 156
Current log# 2 seq# 156 mem# 0: D:\ORACLE\ORADATA\OCTOVH\REDO02.LOG
I thought I needed to make the redo log files bigger. Should all the logfiles be in one group?

IO will try out the commands in a test environment.

Thanks again for your help: the situation is now like this:

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

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

D:\ORACLE\ORADATA\OCTOVH\NIEUWREDO03
7 INACTIVE 136314880

It looks like there will always be one inactive...

Best regards, Aspijker

 

[slaing]

Aspijker -

The timestamps next to the messages at the start of your original post are (roughly) a day apart. Oracle is moving on to the next log file each time the current one fills up (or when you force it with ALTER SYSTEM SWITCH LOGFILE).

These messages are perfectly normal and show that the system is logging transactions. Check the rest of the alert log and make sure there aren't peaks where the files are switching every few minutes (but remember that data loads and using the ALTER SYSTEM SWITCH LOGFILE will also cause these messages to occur). If the messages appear too frequently then you will get a performance hit


Can I step back for a moment and recap the basics of Redo logs....

Each time you do an update, insert, delete, Oracle records the statement needed to repeat the operation and the SQL needed to undo the operation (in a compiled form) in the redo logs.

Once the redo log is full Oracle closes it and moves onto the next.

Once all the groups have been used Oracle loops back to the first group and overwrites the 1st redo log.

In production environment there are 2 other things you should consider
a) creating extra members in each group on different disks so that in the event of disk failure or corruption you have a good copy.
b) Since Oracle overwrites the redo logs eventually, you may want to turn on ARCHIVELOG mode so that Oracle copies the redo logs to another directory before overwriting them.
ARCHIVELOG mode also allows you to do online backups.

Therefore the answer to your 2nd question is 'No', all the log files can't be in one group. As a minimum you should have 3 groups with 1 member in each


The best way of becoming comfortable with Oracle logs is to set up a Test system and mess around with them. The Oracle Concepts manual should give you a more detailed overview of the process.


Steve

 

[KenCunningham]

Steve explains things succinctly and I would only add that to carry out hot backups and do recoveries (to a point in time for example), it is essential to be running in archivelog mode, certainly for a production environment. Of course it then becomes a housekeeping issue as to when it's safe to delete the old logs to free up space - that's a judgement call depending upon full (verified) backup frequency etc.

Then again there's RMAN, which I'm afraid I have no experience of, but no doubt someone will chip in.

 

[aspijker]

Thanks guys!

I am setting up a test system, so I can try some things out. I also need to know a bit more about archivelog mode, because that seems very important!

Best regards, Aspijker