2 entries in V$SGASTAT for log_buffer -- Excessive rode log

From: <Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com>
Date: Wed, 13 Sep 2000 15:03:38 -0400
Message-Id: <10618.116885@fatcity.com>

Hello,

I am still working on this issuse with redo logs. It's actually worse then I originally stated in my e-mail. During the night backup the redo logs are generated at the rate of two 10Mb files per minute with practically no activity against the database.

I understand that there will be more redo generated while I am in the backup mode, but not THAT much with no activity.

While trying to work on this problem with Oracle support I decided just for the heck of it to double check the value of the LOG_BUFFER in v$sgastat just to make sure that it's not different from what I have in init.ora.

To my great surprise I got 2 records returned from this view for the following query.

SQL>select * from v$sgastat

        2 where name = 'log_buffer'
        3 /
NAME                            BYTES
-------------------------- ---------
log_buffer                     1048576
log_buffer                     16384

SQL> The 1M entry is what we have it set to in the init.ora. I have no idea where the 16K is coming from and what it means.

If anybody could shed some light on this I would be very appreciative.

We are at the point were we have not had backup for two consequtive nights -- the backup runs very slowly and estimated time to complete is about 24 hours (I assume it happens because the box is bogged down with all this redo archiving).

Thanks,
Val Gamerman.

To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:        (bcc: Val Gamerman/Victoria Financial)
From:     "Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL"@lnn.com @
          NOTES NET
Date:     09/11/2000 09:45:46 PM GMT

Subject: Excessive rode log generation after upgrade

Hello,
I upgraded Oracle over the weekend from 7.2 to 7.3.4 (I know 7.3.4 is to be desupported soon, please no flames :-).
Our redo log switches used to occur every 1 hour - 2 hours. Now they occur every 25-30 minutes.
I know that this is still acceptable, however I can see that the database/application is sort of behaving slower then usual. I tried to talk to Oracle Support and did not get any help from them. The guy I talked to philosophy was -- if it's not down, it's working fine :-) It looks almost as if I was in the BACKUP mode all the time -- too much redo is generated. I did verify v$backup and all the files are in the 'NOT ACTIVE' status. When there is no activity against the database, naturally there is no log switches -- no log switches during the night. What should I check for? What can it be? There were no changes made to the application. We have the same number of users as we had before the upgrade. The only two things that changed are the Oracle version and location of some data files. Platform: Netware 4
Oracle: 7.3.4
LOG file size = 10M, there are 5 of them Database is in the ARCHIVE_LOG mode.
Any help is greatly appreciated.
Thanks,
Val Gamerman

From: "Koivu, Lisa" <lkoivu_at_qode.com>
Date: Wed, 13 Sep 2000 15:25:47 -0400
Subject: RE: How many databases do you support?

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C01DB8.6C14AD3A
Content-Type: text/plain;

charset="iso-8859-1"

Hi Cherie,

Here we have 3 dedicated db hosts, 4 databases. 1 DBA, 1 SA, plus one person who can back both of us up.

I hope this helps you. I know your numbers are a complete nightmare.

Lisa

-----Original Message-----
From: Ron Rogers [mailto:RROGERS_at_galottery.org] Sent: Wednesday, September 13, 2000 2:56 PM To: Multiple recipients of list ORACLE-L Subject: Re: How many databases do you support?

Current job 2 machines, 3 databases. 2 live , 1 development. Previous job 4 machines, 4 databases. 2 live, 1 hot backup, 1 development. 1 DBA/SYSADMIN
ROR
>>> Cherie_Machler_at_gelco.com 09/13/00 02:36PM >>>
Is there any sort of rule-of-thumb for how many database that a DBA should be expected to support? I realize that the answer would vary based on size of databases, stability, production vs. development, mission critical, 24/7, etc.

But in general, how many databases would you think a DBA should be supporting if you divide the total number of databases in an organization by the number of DBAs supporting them?

How many databases do you support on average? What is the size of your team? Received on Wed Sep 13 2000 - 14:03:38 CDT