Re: Log in Storm Caused Database Crash

Thanks a lot everyone for your response. I will put all the info together to work on this.

On Mon, Oct 2, 2017 at 6:12 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> A typical (not to be confused with good) approach from application servers
> is that if a connection is not available, some number of connections for a
> pool is spawned.
>
>
>
> Too often this approach is implemented without any semaphore observable
> from the application server to prevent immediately succeeding connection
> requests from driving another pool spawn and instead to retry first after
> the already requested spawn has completed.
>
>
>
> This pattern (“I didn’t get a connection, I’ll help everyone else by
> getting a connection for myself and also for the next, say, 60, attempters,
> so they don’t have to wait”) is a simple minded recipe for disaster waiting
> to happen when a connection request burst (whether denial of service, load
> testing, or simply clock driven like 9 AM in an office setting) fields
> requests at a rate quicker than the time to make the additional connections
> available.
>
>
>
> The load on the system from a single request in resource and lag time goes
> up by a factor and since it cannot service quick successive requests, these
> each in turn add to the problem.
>
>
>
> This may or may not be your problem, but it is worth checking for.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Ravi Teja Bellamkonda
> *Sent:* Monday, October 02, 2017 1:06 AM
> *To:* Matthew Parker
> *Cc:* Upendra nerilla; oracle-l
>
> *Subject:* Re: Log in Storm Caused Database Crash
>
>
>
> Matthew and Stefan,
>
>
>
>
>
> Thank you for your points. I will definitely go through those logs.
>
>
>
> During that interval, I made sure from v$session (machine) that all the
> sessions are from the application server itself. This made me feel that the
> spike is because of the application server configuration during load times.
>
>
>
> On Sun, Oct 1, 2017 at 9:58 PM, Matthew Parker <
> dimensional.dba_at_comcast.net> wrote:
>
> You can also look at you listener log to see number of connection attempts
> to system.
>
> If it was a true overwhelming connection storm then you will also probably
> have information in the alert.log where errors are being reported spawning
> against the database.
>
>
>
> *Matthew Parker*
>
> *Chief Technologist*
>
> *Dimensional DBA*
>
> *425-891-7934 <(425)%20891-7934> (cell)*
>
> *D&B *047931344
>
> *CAGE *7J5S7
>
> *Dimensional.dba_at_comcast.net* <Dimensional.dba_at_comcast.net>
>
> *View Matthew Parker's profile on LinkedIn*
> <http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
>
> www.dimensionaldba.com
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Ravi Teja Bellamkonda
> *Sent:* Sunday, October 1, 2017 9:45 PM
> *To:* Upendra nerilla <nupendra_at_hotmail.com>
> *Cc:* oracle-l <oracle-l_at_freelists.org>
> *Subject:* Re: Log in Storm Caused Database Crash
>
>
>
> Hi Upendra,
>
>
>
> Thank you for your response. This is an internet facing application and we
> were expecting a burst load to check for the capacity of the system. Is
> there a way to measure what no of sessions in the database is breaking
> point. I was doubting if any Sub-Optimal Connection Pooling might have
> caused this.
>
>
>
> Highly appreciated your help here.
>
>
>
> On Sun, Oct 1, 2017 at 7:39 PM, Upendra nerilla <nupendra_at_hotmail.com>
> wrote:
>
> Is this an internet facing application or internal? If it is external
> facing application, investigate if there was DoS type attack or a spike in
> the user sessions due to any issues with application servers?
>
>
>
> If you need to isolate where the connections originated from, you could
> look into DBA_Hist views.
>
>
>
> You may want to start with this one.. https://docs.oracle.com/cd/
> B19306_01/server.102/b14237/statviews_3125.htm#REFRN23400
>
>
>
> DBA_HIST_ACTIVE_SESS_HISTORY - Oracle Help Center
> <https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3125.htm#REFRN23400>
>
> docs.oracle.com
>
> DBA_HIST_ACTIVE_SESS_HISTORY. DBA_HIST_ACTIVE_SESS_HISTORY displays the
> history of the contents of the in-memory active session history of recent
> system activity.
>
> Also look into any application server logs and see if there were any
> issues with the application server itself..
>
>
>
> -Upendra
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Ravi Teja Bellamkonda <raviteja.bellamkonda7_at_gmail.com>
> *Sent:* Sunday, October 1, 2017 9:25 PM
> *To:* oracle-l
> *Subject:* RE: Log in Storm Caused Database Crash
>
>
>
> Hi List,
>
>
>
> We ran into an issue recently and wanted some help in figuring out this
> issue.
>
>
>
> Database was not responding and one thing from AWR observed before fail
> over was the login storm.
>
>
>
> [image: Inline image 1]
>
>
>
>
>
> Logons cumulative also increased during this interval.
>
>
>
> [image: Inline image 2]
>
>
>
> Logons cumulative were 1237 in total in the before AWR report. Any
> suggestions are highly appreciated.
>
> --
>
> Thanks & Regards,
>
> Ravi Teja
>
>
>
>
>
>
>
> --
>
> Thanks & Regards,
>
> Ravi Teja Bellamkonda
>
>
>
>
>
> --
>
> Thanks & Regards,
>
> Ravi Teja Bellamkonda
>
> Ph: (816)-905-7577 <(816)%20905-7577>.
>

-- 
Thanks & Regards,
Ravi Teja Bellamkonda









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




image/png attachment: image002.png



image/png attachment: image001.png