Oracle 12c Unable to connect to Oracle database : ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist

2
votes

I was running a delete script on a table with around 80 million records, the script ran for two days and eventually failed and the hard disk space (in which Oracle was installed) was reduced to 300MB (available) from 50GB (available). Then I truncated the table. The database was up and running until this point. Then I did a system restart, after that Oracle services are up, but I am not able to connect to the database from SQL*Plus or SQL Developer. When I try to connect it gives following error:

ERROR: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

After that I increased my D drive memory to 100GB (available space) and restarted the Oracle services, but still was not able to connect to yjr database.

Then I tried all the approaches in this link like

1) Restarting Oracle 2) Setting ORACLE_SID environment variable 3) Verified the ORACLE_HOME from listener.ora and ORACLE_HOME from system properties. 4) Cleared windows event logs

When I run below sequence of commands:

set oracle_sid=DATABASE NAME
sqlplus /nolog
conn sys/sys as sysdba
shutdown abort
startup

the startup step produces the following error:

ORACLE instance started.

Total System Global Area 3892314112 bytes
Fixed Size                  3084576 bytes
Variable Size            1388318432 bytes
Database Buffers         2361393152 bytes
Redo Buffers              139517952 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6996
Session ID: 7 Serial number: 21027

The SID of the database I am trying to connect is PSPRODDB. Below is a screenshot of my environment variables:

Screenshot of env variables

Here is my listerner.ora file. 

# listener.ora Network Configuration File: D:\oracle\product\12.1.0.2\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oracle\product\12.1.0.2\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\product\12.1.0.2\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.244.45.240)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Let me know if you need any further info. Please help.

1
databaseoracleoracle12c
What does the alert log show? Is everything in your DB on your D: drive or is space also needed on another drive that is full, perhaps? - Alex Poole
Connections to Oracle 12 in multi-tenant installation require using SERVICE NAME rather than SID. - mathguy
@AlexPoole: I am a beginner in Oracle, can you please let me know how to check alert log? Everything related to DB is on drive D. Also Drive C has 100GB free space and E drive has 30 GB free space. - Shiva
@mathguy: I am a beginner in Oracle, can you please let me know about multi-tenant installation and how/where to use SERVICE NAME? - Shiva
@AlexPoole : Thanks for suggesting alert logs, based on them I saw that some reset log was causing the issue. Following the steps in this dba-oracle.com/t_ora_01589_must_use_resetlogs.htm link resolved the issue for me. - Shiva

1 Answers

1
votes

Following the steps in this dba-oracle.com/t_ora_01589_must_use_resetlogs.htm link resolved the issue for me