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

Question

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:

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.

Answer 1

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