Re: Size of files. Can you estimate?

From: ExecMan <johndoe_at_mtekusa.com>
Date: 8 Dec 2006 07:34:29 -0800
Message-ID: <1165592069.101801.76840@f1g2000cwa.googlegroups.com>

Charles Hooper wrote:
> amerar_at_iwc.net wrote:
> > Charles Hooper wrote:
> > > ExecMan wrote:
> > > > Hi,
> > > >
> > > > I've just taken over a database. The database has 28 rollback
> > > > segments. And, to my astonishment, these guys are not running their
> > > > production database in archive log mode.
> > > >
> > > > Short term fix is to put it in archive log mode. but, I'm wondering if
> > > > there is any way to estimate how large the archive log files will be,
> > > > or, how often they will be created based on the number of wraps the
> > > > rollback segments have........
> > > >
> > > > John
> > >
> > > Try the following, assuming that the existing redo log files are
> > > sufficiently large enough to provide a couple hours (or even days) of
> > > history, and that you are not forcing log file switches on a regular
> > > interval (not likely in your case):
> > > SELECT
> > > L.GROUP# GROUP_NUMBER,
> > > LF.MEMBER FILENAME,
> > > L.STATUS,
> > > L.ARCHIVED,
> > > L.BYTES,
> > > L.FIRST_CHANGE#,
> > > L.FIRST_TIME
> > > FROM
> > > V$LOG L,
> > > V$LOGFILE LF
> > > WHERE
> > > LF.GROUP#=L.GROUP#
> > > ORDER BY
> > > L.GROUP#;
> > >
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.
> >
> > Charles,
> >
> > Thanks for the query. What does this output show me?
> >
> > GROUP_NUMBER FILENAME STATUS ARC
> > BYTES FIRST_CHANGE# FIRST_TIM
> > ------------ ------------------------------ ---------------- ---
> > ---------- ------------- ---------
> > 1 /n2/oradata/NI00/redo01.log ACTIVE NO
> > 3072000 2815620270 08-DEC-06
> > 2 /n3/oradata/NI00/redo02.log INACTIVE NO
> > 3072000 2815615949 08-DEC-06
> > 3 /n4/oradata/NI00/redo03.log CURRENT NO
> > 3072000 2815623682 08-DEC-06

>

> The tool that you are using to run the SQL statement is not showing the
> hours and minutes associated with each log switch. Use this SQL
> statement to force the display of the hours and minutes:
> SELECT
> L.GROUP# GROUP_NUMBER,
> LF.MEMBER FILENAME,
> L.STATUS,
> L.ARCHIVED,
> L.BYTES,
> L.FIRST_CHANGE#,
> TO_CHAR(L.FIRST_TIME,'DD-MON-YYYY HH24:MI') FIRST_TIME
> FROM
> V$LOG L,
> V$LOGFILE LF
> WHERE
> LF.GROUP#=L.GROUP#
> ORDER BY
> L.GROUP#;

>

> >From your posting, you have only three log files, each roughly 3MB in
> size. Based on your response to Mark, you are switching log files
> roughly every 9 to 30 seconds. Log file switches ideally should take
> place every 20 to 30 minutes. As the system is configured right now, I
> predict that your database will grind to a halt every 9 to 30 seconds
> if the system is set to archive redo mode. You need to significantly
> increase the size and number of online redo logs.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Thanks for your suggestion Charles. Based on that, I would think that not only do the log files need to be increased in size, but we probably need more than 3 log files.

I've also looked into the alert log and seen this error:

Thread 1 cannot allocate new log, sequence 584375 Checkpoint not complete

My guess is that this also has something to do with the size and frequency of the switches, right?

John Received on Fri Dec 08 2006 - 09:34:29 CST