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.
Received on Fri Dec 08 2006 - 09:05:43 CST
