Introduction.

This morning the first challenge of the day became the fact that we could not get proper access to one of the databases. We would set the Environment, set the proper ORACLE_SID  and yet after logging on would get

ORA-01012: not logged on

with any query ( select * from v$instance ; show parameter blablah ).

Reading the posts on the web give a quick solution…  First  proof:  the alert log was flooded with messages like these:

ORA-04031: unable to allocate 2072 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","parameter table block
")
Wed Feb 27 09:31:49 2013
Process J000 died, see its trace file
Wed Feb 27 09:31:49 2013
kkjcre1p: unable to spawn jobq slave process

Sqlplus /  as sysdba worked, but any query failed with ORA-01012.

Solution:

Created a pfile from spfile , edited both  SGA_MAX_SIZE and SGA_TARGET  and increased  the  processes as well in the init.ora .

Created spfile file from the altered pfile.

Restarted the Database, and after that happy database ( and dba ).

Happy reading,

Mathijs