Solutions for Oracle ORA-28002 and ORA-27101

Running an Oracle database is great, because it is stable and just runs, but sometimes you are hit with unexpected errors when you restart your machine. Thought, I know Oracle quite well, I’m always surprised at some things. Here are two errors (and solution) I was just confronted with:

ORA-27101: shared memory realm does not exist

To be honest, I don’t know why I was confronted with this error, since we haven’t changed anything to the machine or to the environment variables. Metalink suggest to check that the ORACLE_HOME and ORACLE_SID are correct. Funny thing is that this system runs for over a year without a change to the path, nevertheless I checked the ORACLE_HOME path and sure enough I had trailing slash at the end.

So, the solution was to see that ORACLE_HOME does NOT have a trailing slash.

echo $ORACLE_HOME
/opt/oracle/product/11/ <--- WRONG!

echo $ORACLE_HOME
/opt/oracle/product/11 <--- CORRECT!

Remember to log out of your current shell session in order to reapply the new settings.

ORA-28002: the password has expired

Now, this error caught be even worse, because it happened right within a production environment. Also, here I was unaware of this setting. In any case, Oracle seams to want you to reset your password after one year or so. In case, you want to disable this on a user without changing the users password you need to issue the following commands in sqlplus:

alter profile {user} limit password_verify_function null;

This will set the verification for this user to null. If you want to do this for every user in your system you would use this (this is applied to the DEFAULT profile):

alter profile DEFAULT limit password_verify_function null;

Once done you can then reset the password for the user with the same password or with another one with:

alter user {user} identified by {password};

Exit sqlplus and your changes should have been applied.