Redo / Archive Log Best Practices?
441265Mar 31 2005 — edited Apr 1 2005I am a newb when it comes to Oracle administration. The problem is that our "DBA" knows even less about it.
I'd like to get some advice/recommendations on redo and archive logs.
We are currently running:
Windows 2000 Server
Oracle 8.1.7
Oracle DB is ~50gb
~250 users
Database is under fairly heavy load, as it is used to run the company's primary accounting software.
Recently when reviewing back up procedures, I realized that our "DBA" did not have archive logging turned on. This obviously is a problem. Our DBA was relying solely on a data dump every night that was then backed up to tape. I was forced to take care of this, as the "DBA" didn't have any knowledge on this subject. I got archive logging turned on, changed the init file, etc. etc.
Where the problem comes in, and where my questions come from... The database was writing archive logs ~2-3 mins, sometimes less depending on the database load. Oracle was configured to use 3 redo logs @ ~105mb each. The server was getting "Archive process error: Oracle instance xxxx - Cannot allocate log, archival required." I changed the redo logs to run 5 logs at ~200mb each. I also added a scsi drive to the server for the sole purpose of storing the archive logs. Log Buffer was set at 64k, I upped this to 1mb.
My specific questions are:
- How fast should logs be being written?
- Should I up the number of redo logfiles, or up the size of each?
- Should I be writing the redo logs to multiple destinations?
- Should I archive to multiple destinations? If so, would archiving to a network drive lag the archive process, and kill the bandwidth to the server/database since it would be writing 200mb+ files to the network every few minutes?
- What are some recommended file size minimums / maximums under the current environment listed above?
- Other tips/suggestions?
Any help would be appreciated.
Thanks.