-
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
-
why do you think you need bigger ones - those messages are normal
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|