Hi bob,
- these 80 SIDs (which is windows terminology for Oracle instances, right?),
are these on a bare metal windows installation, or is the operating system
virtualised?
- how many CPU core’s and threads are permanently available for the operating
system, thus the instances?
- generally, you can use a listener to provide access to multiple instances,
which eases administration and reduces usage of network ports.
I don’t know any of the specifics of your underlying hardware, which I asked
the above questions.
However, in general, and specifically for a database, the total memory usage of
all the database instances combined should be lower than total memory minus
operating system memory usage minus additional running processes. Again, I
don’t know, and have no indication that there is a memory shortage but you
should investigate. Also mind PGA usage is dynamic.
Additionally, provided you keep the databases for actual usage, you should have
approximately between one CPU thread and one CPU core per database instance.
This is if you actually want to use the instance, so the bare minimum. If you
do more with the specific database instance (which probably is the reason for
the existence of the database instance!), these requirements go up.
The ‘suspending mmon action’ messages from the alert log indicate resource
shortage, and the database trying to overcome that by suspending MMON.
Frits Hoogland
http://fritshoogland.wordpress.com ;<http://fritshoogland.wordpress.com/>
frits.hoogland@xxxxxxxxx <mailto:frits.hoogland@xxxxxxxxx>
Mobile: +31 6 14180860
On 29 Mar 2016, at 20:16, Balwanth Bobilli <Balwanth.Bobilli@xxxxxxxxxxxxx>
wrote:
Product Oracle Database - Enterprise Edition
Product Version:12.1.0.2
Operating System :Microsoft Windows x64 (64-bit)
OS Version: 2012 R2
There are 80 SIDS sitting in one server and each as their own listener.. Same
setup in built in multiple servers… Random Databases go down without
knowledge, All we need to do is restart the database and then it behaves
well. It is production box and happening frequently…Moreover, there are only
three windows scheduler jobs and we do not see any failure in it and those
are small scripts.. Tail of the alert log of one DB, Can anyone say me how
to find the root cause? It will be really helpful
Suspending MMON action 'CLI AutoPartition' for 82800 seconds
Mon Mar 28 07:47:30 2016
Suspending MMON action 'tablespace alert monitor' for 82800 seconds
Mon Mar 28 07:53:00 2016
Suspending MMON action 'Cleanup of unpinned KGL handles' for 82800 seconds
Mon Mar 28 07:58:37 2016
Suspending MMON action 'flushing workload information for optimizer' for
82800 seconds
Mon Mar 28 08:04:10 2016
Suspending MMON action 'KSXM Advance DML Frequencies' for 82800 seconds
Mon Mar 28 08:24:20 2016
Suspending MMON action 'undo usage' for 82800 seconds
Mon Mar 28 09:45:40 2016
Suspending MMON action 'autotask status check' for 82800 seconds
Mon Mar 28 11:27:23 2016
Starting ORACLE instance (normal) (OS id: 90240)
Mon Mar 28 11:27:23 2016
CLI notifier numLatches:131 maxDescs:5068
Mon Mar 28 11:27:23 2016
Specified value of sga_max_size is too small, bumping to 3992977408
SGA Local memory support enabled
LICENSE_MAX_SESSION = 0
Thanks,
Balwanth(Bob)
Oracle Database Administrator
<image001.png>
Balwanth.bobilli@xxxxxxxxxxxxx <mailto:Balwanth.bobilli@xxxxxxxxxxxxx>
1-512-874-1764