Resolving ORA-12528 Errors

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.

Comments

  1. How do you set the service name parameter to include the name of the instance?

    ReplyDelete

Post a Comment

Popular posts from this blog

Viewing ASM trace files and alert logs in Unix/Linux

ORA-00020: maximum number of processes (%s) exceeded

Troubleshooting RAC Public Network Failure