"ORA-12528: TNS:listener: all appropriate instances are blocking new connections" (4248459)

Launching Spotlight on Oracle 6.0 with ASM enabled on the connection profile, the following error message appears:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

There is no Archive log appearing on the home page of Spotlight on Oracle.

RESOLUTION1

Ensure the instance has ASM enabled after running the following query in SQL Plus:
SQL> show parameters dest

RESOLUTION 2

The line (UR=A) needs to be added to the TNS entry in the tnsname.ora, here is an example:

ASMINSTANCE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM1)
(UR=A)
)
)

See https://twiki.cern.ch/twiki/bin/view/PSSGroup/ASM_Internals (and search for UR=A) for some reference materialon this.

RESOLUTION 3

The ASM connection string is the TNS entry for the ASM instance just like the example ASMINSTANCE. The password is the SYS password, SYS being the only user of the ASM instance.

RESOLUTION 4

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

Resolution 5:

Run lsnrctl status on the DOS prompt in order to determine ifthe instance is blocked:

Here is an example of a RAC:
Service +ASM has 4 instance(s).
Instance +ASMone, status BLOCKED, has 1 handler(s) for this service...
Instance +ASMtwo, status BLOCKED, has 1 handler(s) for this service...
Instance +ASMthree, status BLOCKED, has 2 handler(s) for this service...
Instance +ASMfour, status BLOCKED, has 1 handler(s) for this service...

The solution is to change TNS Entry for the ASM to:

ASM_Three_Connection =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testconnection)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(INSTANCE_NAME = +ASMthree)
(UR=A)
)
)

Also, run a trace on the connection with ASM for Spotlight on Oracle 6.0. This might yield somehelpful information.