ORA-01075: you are currently logged on

SCENARIO

On fine morning, a client reported unable to connect to "ORXXXG" database. DBA tried to troubleshoot the request and while connecting to database it resulted in ORA-01075 error pasted below.

ERROR

[oracle@RAEYDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 22 11:25:33 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:

ORA-01075: you are currently logged on

Enter user-name: exit

Enter password:

CAUSE

ORA-01075 is primarily caused due to inappropriate settings of kernel parameter, SGA memory, Memory target and available physical memory due to which Shared Memory segments and Semaphores get created.

SOLUTION

ORA-01075 can be resolved by:

1. Clean all the Shared Memory segments and Semaphores occupied by SID. Caution cleaning semaphore will cause instance to terminate.

Check for any shared memory segment for oracle and any semaphore for oracle using utility “ipcs”

[root@localhost ~]# ipcs –a

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status

0x7403035b 4161536    root      600        4          0

0x00000000 4521985    root      644        80         2

0x00000000 5472262    oracle    640        8388608    33

0x00000000 5505031    oracle    640        213909504  33

0xe3834bf4 5537800    oracle    640        2097152    33

------ Semaphore Arrays --------

key        semid      owner      perms      nsems

0x000000a7 0          root      600        1

0x4c770354 917505     oracle    640        154

------ Message Queues --------

key        msqid      owner      perms      used-bytes   messages

We need to clean the shared memory and semaphore using utility “ipcrm”

ipcrm -m ---- To clean shared memory segment for oracle

ipcrm -m 5505031    

ipcrm -m 5537800    

ipcrm -s ---- To clean semaphore for oracle

ipcrm -s 917505     

In alert log file:

Instance terminated by DBW0, pid = 5417

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_5417.trc:

ORA-27300: OS system dependent operation:semctl failed with status: 22

ORA-27301: OS failure message: Invalid argument

ORA-27302: failure occurred at: sskgpwrm1

ORA-27157: OS post/wait facility removed

ORA-27300: OS system dependent operation:semop failed with status: 43

ORA-27301: OS failure message: Identifier removed

ORA-27302: failure occurred at: sskgpwwait1

      2. Reduce the sga_target value or *.memory_target to appropriate value calculating total physical available memory.

If we have 1GB memory allocated on database server then,

[root@localhost ~]# free -g

             total       used       free     shared    buffers     cached

Mem:             1          1          0          0          0          0

-/+ buffers/cache:          0          1

Swap:            7          0          7

      a) In case the environment is using AUTOMATIC MEMORY MANAGEMENT(AMM), feature available only from 11g release we can allocate 650MB memory, 60% of total available ram for *.memory_target

*.memory_target=681574400 --- 650MB. --- 60% of total memory available on database server.

      b) Incase we are using AUTOMATIC SHARED MEMORY MANAGEMENT(ASMM) feature then also 60% of total memory available on database server can be allocate to pga_aggregate_target and sga_target in ratio of 40:20.

40:20 = sga_target: pga_aggregate_target

ora11g.__pga_aggregate_target = 239075328  --- 228MB 

`

ora11g.__sga_target           = 444596224  --- 424MB

Once the above changes are made we can startup the database and error will be resolved.

SQL> startup

ORACLE instance started.

Total System Global Area  680607744 bytes

Fixed Size                  2256352 bytes

Variable Size             427819552 bytes

Database Buffers          247463936 bytes

Redo Buffers                3067904 bytes

Database mounted.

Database opened.

SQL>

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

ORA11G    READ WRITE           PRIMARY

DISCLAIMER

The views expressed here are subjected to www.RAEY.in business environment. Suggestions and comments will be highly appreciated.