RE: SAP/Oracle (about log group order)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Les.Hollis@xxxxxx>, <jkstill@xxxxxxxxx>, <jo_holvoet@xxxxxxxx>
- Date: Sat, 11 Dec 2004 13:06:41 -0500
Last time I carefully tested the new logs were added after the current log
in the rotation. That doesn't mean your results are wrong, just that they
don't represent all possible orders. So if the current log was 6, your
results would match my results, but if your current log was 5, the result
would be
1,5,2,3,4,6.
If the current log was #1, then the 1,2,3,4,5,6 result might confuse someone
into thinking the names had something to do with the order.
Anyway, you can be absolutely sure by switching through and seeing the order
in which they become current. Usually you only need to know if you're in a
high throughput situation such that you've provided a portion of your i/o
subsystem that will respond to LGWR and ARCH in the manner of use by a
single user and you've made use of ping ponging. Likewise, if your need to
transmit logs to another site exceeds the rate that dataguard and keep up
with you can turn off automatic archiving and drive manual archiving to a
rotation of drives from which your ftp or other process can transmit (so
that ARCH is not competing with ftp, as LGWR is not competing with ARCH.)
Note that this is not the finest granularity of transmitted protection to
the remote site, but if your transmission volume is very high this
bottlenecks at a higher total capacity. Most sites do not need this level of
complexity, and if you've aggregated all your disks to a statistical "no hot
spots" grid behind the covers you simply won't be able to do this anyway.
Sites that are paced by on line log switching and report no improvement due
to ping ponging usually didn't understand what was meant by "independently
operating disks." As the cost of SSD drops and the size of individual disk
drives rises, if you need this level of throughput you are probably better
served to make a disk group from SSD dedicated to these functions where the
aggregate i/o is enormous and the seek time is on the order of a memory
access so you don't have to ping pong after all, and you stop caring much
about the order.
Now if your write (LGWR) activity is bursty and your DR site granule is
fairly large, you may do well to just use a log of groups (size is a lot
cheaper than extra spindles these days). If you have enough on-line log
space to support your most update voluminous burst to a time when ARCH can
catch up, you won't hang because you'll have another group available to
switch to throughout the peak load.
Regards,
Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hollis, Les
Sent: Friday, December 10, 2004 5:10 PM
To: jkstill@xxxxxxxxx; jo_holvoet@xxxxxxxx
Cc: nsouto@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
oracle-l-bounce@xxxxxxxxxxxxx
Subject: RE: SAP/Oracle
Not totally correct
"This is because the sequence number indicates the actual order in which
the redo log files are used and NOT the group number."
The logs are used in the order in which the groups were CREATED.....
Create log group 1, 2, 3, 4, 5, 6
Drop group 2, 3, 4 and recreate them and your sequence of use is NOW
Group 1, 5, 6, 2, 3, 4 =20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Friday, December 10, 2004 12:20 PM
To: jo_holvoet@xxxxxxxx
Cc: nsouto@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: SAP/Oracle
On Fri, 10 Dec 2004 11:55:31 +0100, jo_holvoet@xxxxxxxx
<jo_holvoet@xxxxxxxx> wrote:
> And get used to the following :
>=20
> * SAP will try its darndest to keep you out of the database except
through
> their tools (such as sapdba) which introduce interesting new bugs
Just say 'NO'. These tools are good for what they were designed for:
Allowing SAP Basis Admins to do DBA tasks when they are not really
comfortable with many DBA tasks.
For an experienced DBA they are a large PITA, at least SAPDBA is.
The backup tools aren't bad, though we do not use the recovery tools.
We will be converting to RMAN on our SAP databases and give brbackup
the ole heave ho.
Something else to watch out for are their 'Early Watch' reports. Those
for the database are designed to help you 'optimize' your database.
Here is a sampling of some 'recommendation' they sent to our SAP team
2 years ago.
------------------------------------------
Order of Redo Log Files
Using the Oracle views v$log and v$logfiles, we found the name and the
order of the redo log files as listed in the following table. Please
note that the files are ordered by the "sequence number" as found in
v$log. This is because the sequence number indicates the actual order
in which the redo log files are used and NOT the group number.
GROUP# SEQUENCE# File Name
7 54177 I:\ORACLE\PRD\ORIGLOGB\LOG\LOG_G07_M02.DBF
7 54177 K:\ORACLE\PRD\ORIGLOGA\LOG\LOG_G07_M01.DBF
5 54178 I:\ORACLE\PRD\ORIGLOGA\LOG\LOG_G05_M01.DBF
5 54178 J:\ORACLE\PRD\ORIGLOGB\LOG\LOG_G05_M02.DBF
6 54179 K:\ORACLE\PRD\ORIGLOGB\LOG\LOG_G06_M02.DBF
6 54179 J:\ORACLE\PRD\ORIGLOGA\LOG\LOG_G06_M01.DBF
To avoid contention, it is recommended to locate the redo log files on
different disks such that two succesive redo log files are NOT located
on the same disk. In this way, there is no competition between the
"write" requests of the logwriter and the "read" requests of the
archive process.
-----------
My response to that one - "They should have waited for another log
switch". =20
Their contention argument? Yeah, I fixed that. =20
----------------
Of course there was a recommendation to reorganize the entire database,
as there
were 'too many extents' in many of the tables.
SAP is *very* big on re-organizing databases. There are extensive
utilities
from SAP just for reorgs.
<sigh>
-----------------
The moral is: you must carefully examine any advices SAP offers on=20
'improving' database performance.
--=20
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: SAP/Oracle (about log group order)
