Did You Know #24 – Blocked Instances

Even after years of working with something, you can always learn new stuff. Today I tried to create a standby database using the duplicate command. When you duplicate a database you need to connect to both instances (primary as target and standby as auxiliary) using SQL*Net (and not “/”). Since the standby is in nomount, the listener blocks connections to it, so when trying to connect to it using the listener we get “ORA-12528: TNS:listener: all appropriate instances are blocking new connections”.

Usually I just create a static registration entry in the listener.ora file and bounce the listener, this time I didn’t want to (this is a RAC environment with a lot of instances and I didn’t want to start playing with the listener.ora file and bounce the listener).

Searching for this issue I found a really cool trick. A small addition to the tnsnames.ora allows the client to connect to an instance, even if it’s in BLOCKED mode (when the instance is started or mounted).

This is what the tnsnames entry should look like (note the “(UR=A)” after the service_name):

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

I couldn’t find anything about that in the documentation, but MOS has a note about that.

4 thoughts on “Did You Know #24 – Blocked Instances”

  1. Hi Liron, I ran into the same thing. UR=A means “UnRestricted access = Allowed”

    A couple of other interesting MOS notes on it:

    Connection to Auxiliary using connect string failed with ORA-12528 (Doc ID 419440.1)
    Data guard Switchover Via Broker Fails With ORA-12523/ORA-12518 (Doc ID 2150384.1)

    Nice blog!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s