ORA-12528 TNS Listener All Appropriate instances are blocking new connections

This is a brand new error message with Oracle 10g.

 

Here is what Oracle has to say:

All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.

 

And here are what Oracle advices as action:

Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.

 

And what we have to say:

This error usually crops up when you are starting up your database, or just started up your database. This is actually a known problem with Oracle 10g where a SHUTDOWN IMMEDIATE is followed by a STARTUP MOUNT. If this where the case it would be solved by issuing a SHUTDOWN to the database and then again issuing a STARTUP. This should solve the problem.

 

However there might be some scenarios where this error might occur even if you are starting your database after a restart of the operating system (In fact I got this error after rebooting my Linux machine). I also did not issue any STARTUP MOUNT command but just issued STARTUP. But I got another error which I did not notice while startup of the database. The error was ORA-00205: error in identifying control file, check alert log for more info. I checked for my control files and somebody had deleted them.

 

Now I was in a big problem. But later on when I noticed it was my drives which where missing. Somebody have removed my auto mount scripts of file system, and my files where not mounted.

 

I mounted my file system and then SHUTDOWN my database and then STARTUP once again. Then once again I played with my LSNRCTL for a STOP and START. Then I connected. It was a success.