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):
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb)(UR=A)
)
)
I couldn’t find anything about that in the documentation, but MOS has a note about that.