Re: Root cause for ORA-00471

For the redo log question (if 3 groups of 200MB each are enough) you can check the log switch frequency. Oracle recommends trying to hit 4-5 redo log switches per hour. (*ref: **Can Not Allocate Log Doc ID 1265962.1*)

So if your redo log switches are around 15 every hour (for example), they're not large enough.

You can check your log switches with this script:

Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In RAC (Doc ID 2373477.1)

Depending on your hardware age and storage, don't be afraid to have large redo logs. On a very busy medical system I used to work on, we had 2G redo logs to get the redo log switches down to 4-5 per hour.

HTH,
Chris

On Mon, Jul 8, 2024 at 7:03 AM Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

> Hi,
>
> An instance version 12c terminated by PMON with the message :
> PMON(ospid:3996): terminating the instance due to error 471.
>
> Around the time that it happened I saw for all redo groups the event :
> LGWR all worker groups, but this event occurred in other periods. There are
> redo groups.
> Also I got that at this moment (a little before) there were a lot of
> sessions ON CPU.
> At the moment
>
> My doubts are
> - Where can I get the root cause for this error ?
> - Is there a way (queries) to check if 3 groups with 200mb each one is
> being enough for this environment? I don't see the interval of the
> checkpoint.
> The standard version can use the resource of threads for the redo log
> groups ?
> - Is there more than one possible cause for the error ORA-00471 ?
> - Is there any query that I can use to determine the % total amount of CPU
> used at this moment or interval ? ( I don't have access to the Enterprise
> Manager, It shows this data easily )
>
> I suspect that at this moment there were a lot of sessions in the cpu,
> dbwriter was with all groups busy and PMON based on some configuration
> terminated the instance.
> I am looking for evidence.
>
> Best Regards
> Eriovaldo
>
>
>

--
http://www.freelists.org/webpage/oracle-l