While trying to connect to one of my development Oracle 10.1.0.4 databases I kept receiving the following error message: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
This is a new message introduced in Oracle 10g. A quick search for the oracle error code provided the following information:
Oracle Error : ORA-12528: TNS:listener: all appropriate instances are blocking new connections Cause: 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. Action: Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.
Since I knew that the database had not been shutdown for a long time I thought the message strange. I did a google on possible causes of the error message and all pointed to the same thing above. It turned out that the SERVICE_NAME initialization parameter was inadvertently changed to something else. This causes the status of the listener service to appear as BLOCKED.
To make a long story short you have to set the service name parameter to include the name of the instance or else you'll get that misleading error message.