Tuesday, April 30, 2019

ORA-01012: not logged on startup failed

Problem

Getting ORA-01012: no logged on startup failed during database startup

Solution

This behavior in our case caused by "disgraceful" database shutdown.. Seems like zombie Oracle processes existed that prevented database instance from starting up.

sysresv utility allows to diagnose orphaned shared memory segments.

> sysresv

IPC Resources for ORACLE_SID "<your SID>" :
Maximum shared memory segment size (shmmax): 8589934592 bytes
Total system shared memory (shmall): 6871949312 bytes
Total system shared memory count (shmmni): 4096
*********************** Dumping ipcs output ********************
------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 0          oracle     600        8634368    891
0x00000000 32769      oracle     600        6157238272 446
0x00000000 65538      oracle     600        8142848    446
0x9ab568e0 98307      oracle     600        28672      446

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x1d625978 98304      oracle     600        227
0x1d625979 131073     oracle     600        227
0x1d62597a 163842     oracle     600        227

*********************** End of ipcs commanddump **************

***************** Dumping Resource Limits(s/h) *****************
core file size                         0 KB/UNLIMITED
data seg size                     UNLIMITED/UNLIMITED
scheduling priority                    0 KB/0 KB
file size                         UNLIMITED/UNLIMITED
pending signals                       63 KB/63 KB
max locked memory                     14 GB/14 GB
max memory size                   UNLIMITED/UNLIMITED
open files                            64 KB/64 KB
POSIX message queues                 800 KB/800 KB
real-time priority                     0 KB/0 KB
stack size                            32 MB/32 MB
cpu time                          UNLIMITED/UNLIMITED
max user processes                    16 KB/16 KB
virtual memory                    UNLIMITED/UNLIMITED
file locks                        UNLIMITED/UNLIMITED

***************** End of Resource Limits Dup ******************

Maximum map count configured per process:  65530
Total /dev/shm size: 8403361792 bytes, used: 0 bytes
Shared Memory:
ID              KEY
32769           0x00000000
65538           0x00000000
0               0x00000000
98307           0x9ab568e0
Semaphores:
ID              KEY
98304           0x1d625978
131073          0x1d625979

Kill memory segments

ipcrm -m <Shared Memory ID>

 Kill semaphores

ipcrm -s <Semaphore>

Try to startup database instance again.

No comments:

Post a Comment