We tried performing SHUTDOWN of an oracle database. This started the instance shutdown process that involves closing of all open files, stopping and closing of oracle database followed by oracle instance termination that involves termination of oracle background processes and releasing of memory structures allocated for the oracle database instance. I did close the terminal window in between and tried logging onto database again.
Tried Setting ORACLE_SID parameter the system identifier that uniquely identifies the database instance and tried connecting as sysdba user the user with superuser privilege onto the oracle database:
sqlplus sys/password as sysdba
This showed connected to instance message an successful instance connection establishment message. I tried issuing commands and I’ve been getting the following error ORA-01012: not logged on
I issued SHUTDOWN IMMEDIATE command and it showed message saying that shutdown is in progress.
I now issued a SHUTDOWN ABORT command and it immediately shutdown the instance.Following this I issued a STARTUP command.This mounted and opened the database.Now I issued commands and they worked fine.
SQL> select * from v$instance;
select * from v$instance
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SQL> shutdown abort;
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1375792 bytes
Variable Size 587203024 bytes
Database Buffers 478150656 bytes
Redo Buffers 4603904 bytes
SQL> select * from v$instance;
Error : ORA-01012: not logged on
Reason : Improper shutdown of oracle database instance has caused this error ORA-01012: not logged on inspite of properly setting the environmental variables
Shutdown abort can be used in unavoidable circumstances. By default when shutdown is issues, internally this translates to shutdown immediate command
It is to be noted that ora-01012 is an error that can also happen on a sporadic basis. Root cause of the ora-01012 error can be owing to many reasons including:
1) Database in shutdown situation popularly called system down situation causing connectivity issues. The result returned is a broken connection with this error message
2) Operating system level file permission and folder level permission changes – This can be result of manual intervention, operating system latest patches causing this unexpected issue. OS level crash dump investigation, checking permission of $ORACLE_HOME (or) %ORACLE_HOME% folders can come handy. Work with Unix/Windows system administrator to make sure this is as expected
3) This issue can happen as a result of $ORACLE_SID (or) %ORACLE_SID% the essentially important variable not set properly. Check the profile files if the environment is automated upon server startup. If not check the values in command prompt
4) Set appropriate number of processes values against this parameter in spfile (or) init.ora file. In case if there is a maximum number of processes exceeded error this error can pop-up
5) If there was a prior instance crash and the memory flush is incomplete this error can happen. Check for dumps in trace folder as well as making using of tools like ipcs,sysresv comes handy
These memory segments are referred to as orphaned shared memory segments. Tools like sysresv, ipcs can be used to locate them. Clear them using ipcrm. First locate orphaned memory segments as follows:
sysresv – This provides list of ID of shared memory segments. Use ipcrm to remove these memory segments: