Tuesday, August 19, 2008

Trouble to startup a database: ORA-27102: out of memory

Last night, we had a task to bounce a database. I shut it down without any problem. Then I enter 'startup' in the SQL* Plus command line. However, the Oracle seemed stucking there forever. Per instruction by the team leader, in another session, I used 'shutdown abort' and cleanup any background processes at OS level as well. Then I tried to startup the database again , but recieving the following error:


SQL>startup

ORA-27102: out of memory

HP-UX Error: 12: Not enough space


We tried couple of thing before contacting primary DBA, including

1) Commenting out the large_pool and db_keep_cache_size ( ~ 100G) and tried to startup and it did not work.

2) We brought down another database on the same server to clear up some memory but this also could not startup the database either.


At last, we contact the primary DBA and with his help we found that there were so many active memory segments with ‘no-entry’. See the following for example:


$ ipcs -am grep oracle grep no-entry
m 524297 0x00000000 --rw-r----- oracle dba oracle dba 1 33554432 8330 8330 1:06:21 no-entry 1:06:21
m 327718 0x00000000 D-rw-r----- oracle dba oracle dba 1 55834574848 8330 8330 1:06:21 no-entry 1:16:57
m 32702521 0x00000000 D-rw-r----- oracle dba oracle dba 1 3506438144 8330 8330 1:06:21 no-entry 1:16:57
m 70254652 0x00000000 D-rw-r----- oracle dba oracle dba 1 6559891456 8330 8330 1:06:21 no-entry 1:16:57


We removed them manually, ie by issuing the command:
$ ipcrm -m 524297


We then started the database and it came up. Again we brought the database down in order to see if we can startup the database with the original parameter file (pfile). The same issue with same error massage came back up. When checked, the no-entry active memory segments were back there again. This time we were not able to even remove them. But in the mean time we were able to identify one background process associated with the database:


$> ps -ef grep P2BL47A
oracle 8330 1 252 01:06:19 ? 21:27 oracleP2BL47A (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))


We killed that process and after that all the no-entry active memory segments were also gone. We reduced the db_keep_cache_size to 55G from 100G and started the database and it worked this time and database came up fine.


The primary DBA will investigate this issue further.

1 comment:

JK said...

This post was really helpful. Thanks a lot.