Solved

Oracle Database Crash Investigation

Posted on 2007-12-04
3
5,837 Views
Last Modified: 2013-12-19
Hi Experts,

This could be a very long post as i am trying to understand and come out with a resolution for the database crash i have experienced.  Here are the sequence of events:

################################################################################
16:34:13 (listener log file)
(CONNECT_DATA=(SID=db1)(SERVER=DEDICATED)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=WEB03VM)(USER=IUSR_WEB03VM))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.122)(PORT=2443)) * establish * db1 * 0

17:01:00 ( in the alert log file)

Mon Dec 03 17:01:00 2007
skgpspawn failed:category = 27143, depinfo = 997, op = spcdr:9261:4200, loc = skgpspawn

17:12:58 - Oracle Database Server Rebooted

18:39:04 - listener log file alerts again

03-DEC-2007 18:39:04 *

(CONNECT_DATA=(SID=db1)(SERVER=DEDICATED)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=WEB03VM)(USER=IUSR_WEB03VM))) *

(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.122)(PORT=3744)) * establish * db1 * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   32-bit Windows Error: 233: Unknown error


18:41:00 - alert log file logs the following errors

Mon Dec 03 18:41:00 2007
skgpspawn failed:category = 27143, depinfo = 997, op = spcdr:9261:4200, loc = skgpspawn

Before the database restarted again at 21:10:00, four trace files were generated in bdump directory
#############################
jrac_ora_2168.trc - 18:36:08  #
###############################


Dump file e:\oracle\product\10.1.0\admin\jrac\bdump\jrac_ora_2168.trc
Mon Dec 03 18:36:08 2007
ORACLE V10.1.0.5.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Release 10.1.0.5.0 - Production
With the OLAP and Data Mining options
Windows NT Version V5.2 Service Pack 1
CPU                 : 4 - type 586, 4 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:2160M/3839M, Ph+PgF:5772M/7758M, VA:29M/2047M
Instance name: jrac

Redo thread mounted by this instance: 1

Oracle process number: 217

Windows thread id: 2168, image: ORACLE.EXE


*** SERVICE NAME:() 2007-12-03 18:36:07.979
Died during process startup with error 4030 (seq=147)
OPIRIP: Uncaught error 4030. Error stack:
ORA-04030: out of process memory when trying to allocate 64 bytes (session heap,trigger condition node)
###################################################################################

There were similar trace files generated with the above message.... .


Regarding the Database Server, this is a Windows 2003 Server and Oracle 10g .
The server has 4GB RAM and SWAP 4GB.

After some investigation it looks like memory could have been an issue, but i am not sure regarding the errors occuring in the listener log files before the erros started to occur in alert log file before the database crashed and had to be rebooted.

I know this is a very long post, but i have tried to give as much information i can give in the most compact manner i could have give. Please let me know if i can provide some other information?

thanks
Vamsi
0
Comment
Question by:vamsi_uk
  • 2
3 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 20410260
Go to Task Manager and click the Performance tab.
What about CPU and memory?
Do you experience 100% of CPU?
If so look for the TNSPING.EXE service and
if eats the processor resource stop it.

If this is not the case investigate the SGA size and drastically decrease
it size. Should not be greater the 2Gb given that your
computer has 4GB RAM.
0
 
LVL 2

Expert Comment

by:hamita
ID: 20411172
TNS-12540: TNS:internal limit restriction exceeded
The alert log has an entry that looks like:  skgpspawn failed category 27143
The skgpspawn failed message refers to a failure to spawn a new session.

Performance monitor shows that the instance that that has the TNS-12540 error occurring is using right around 2gb of memory.  The SGA for the instance is about 1.8gb of memory.
Reconfiguring the SGA to take less memory allows for connections  to be made sucessfully.
 
The most significant init.ora settings that are a candidate to to reduce the size of the SGA are:
   db_block_buffers
   shared_pool_size
   java_pool_size
   sort_area_size
   sort_area_retained_size

Because non Enterprise Windows NT Server limits the amount of memory used per process to 2gb, the process for the instance could not allocate more memory for new connections.  

A Microsoft document that explains what is termed 4GT RAM Tuning can be found at:
http://support.microsoft.com/support/kb/articles/Q171/7/93.ASP
Oracle Server Reference A67790-01


error : ORA-04030: out of process memory when trying to allocate 64 bytes (session heap,trigger condition node)
Increase the data segment size of shell for the user performing the migration.
These values can be checked by 'ulimit -a' command


0
 
LVL 2

Accepted Solution

by:
hamita earned 500 total points
ID: 20411245
You are encountering ORA-04030 or ORA/TNS-12500 in the 32 bit windows environment.
It is assumed that your server machine has less than 16GB of physical RAM, and that both
the /3GB and /PAE switches have been set in the boot.ini file
The purpose of this article is to go into further detail with respect to the
spread of memory usage in a 32bit Windows environment, in the hope that errors such
as ORA-4030 and ORA/TNS-12500 may be avoided or reduced
Just to recap the information in the notes above, a 32-bit Windows machine by default will allow
a user process to address only 2GB of memory, leaving the other 2GB for the OS.  By setting the
/3GB switch we can allow this to increase to 3GB for a user process - oracle.exe, in this case.
By using PERFMON (not Task Manager, which only shows the working set, ie recently-used pages)
and looking at the Virtual Bytes field we can see at any one time how much memory is being used.
By using the AWE (PAE) facility and the init.ora parameter USE_INDIRECT_DATA_BUFFERS
we can allow a much larger data buffer cache to be used, as memory from outside the 3GB
space can be mapped to the Oracle process. However this indirect memory can ONLY be
used for data buffers. The other SGA components must still come out of the 3GB.

So how is the 3GB of memory divided up by Oracle?
3GB =
a) AWE_WINDOW_MEMORY (used to address/map the indirect data buffers) +
b) non-buffer_cache part of the SGA (Shared Pool, Large Pool, Java Pool, Streams Pool, Log buffer) +
c) Oracle Process overhead +
d) (Thread stack x number of threads(ie Oracle processes)) +
c) PGA

So which of these can we alter, and under what circumstances?
a)The default value for AWE_WINDOW_MEMORY is 1 GB, although we can reduce this by changing
its value in the Windows registry.  However, it is the area where we must map the data buffers
obtained from the indirect buffer cache, and there are some restrictions so let's assume we leave it at 1 GB.
b)If you are not using Java or Streams then so let's assume we leave it at 1 GB.
If you are not using Java or Streams then you can set these init.ora params to zero.
The Log buffer is small in comparison to the other areas and usually a value of 1 to 5 MB
is adequate, so let's assume we set it to 5 MB. The Large Pool can also be set to zero if not being used
The Shared Pool size is dependent on a number of factors to do with your application, and how much
of your SQL is shared, and if we make it too small we could end up with a number of problems.
Let's assume for the moment it is 250MB.
c)The Oracle process overhead is fixed, dependent on what options you have, so let's assume it is 100MB
d)Thread stack size is by default 1 MB, and the number of processes/threads is dependent on
your application requirements, and whether you are using Shared Server (Multi-Threaded Server).
For the purposes of illustration let's assume for the moment we have 500 concurrent users and we
are not using Shared Server.
So in our example, with the default and assumed values, and 500 users we have
3GB = 1000MB + 5MB + 250MB + 100MB + 500MB + PGA = 1855MB + PGA

To get an idea of the current PGA memory used we can run a query like
col statistic# form 999
col name form a30
col value form 999999999999

SELECT statistic#, name, value
FROM v$sysstat
WHERE name like '%memory%';
This gives a sum of PGA and UGA memory used by all sessions, eg

STATISTIC# NAME VALUE
---------- ------------------------------ -------------
15 session uga memory 847164
16 session uga memory max 1108988
20 session pga memory 9377704
21 session pga memory max 9565800
229 workarea memory allocated 0
245 sorts (memory) 570

6 rows selected.

However, it may be more useful to find out what memory individual sessions are using :-
SELECT *
FROM v$sesstat
WHERE statistic# in (15,16,20,21)
ORDER BY value DESC ;

        SID  STATISTIC#       VALUE
----------     ----------   -------------
             3                20         5412516
             3                21         5412516
             2                20         1870624
             2                21         1870624
             8                21           528860
             5                20           438904
etc....

The SID from the above query will allow you to find the session details from V$SESSION,
and investigate those sessions using large amounts of PGA memory.

(N.B. The statistic number associated with the names may change from release to release
so you should always run the first piece of SQL to get the statistic number.)

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now