Thursday, December 9, 2010

Dynamic listener registration (instance registration) - Connections via the Listener to the Database [TNS-12526 - TNS-12527 - TNS-12528 ]

If you are using dynamic listener registration (instance registration). The instance does not register with the listener until you mount the controlfile. Without sysdba, Net service name using SID or Net service name using SERVICE_NAME with UR=A .


RMAN requires a static listener entry and the tnsnames.ora should not contain FAIL_OVER=ON or LOAD_BALANCING. Just a plain vanilla dedicated server connection.

RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections



There is no static listener entry for the instance you are attempting too connect to. This uses instance automatic registration. The instance registers with the listener when you mount and PMON is started. Before there is nothing to register the database to the listerner so the instance is blocked.

Connections via the listener to an instance that is in RESTRICTED status or NO MOUNT status may fail with TNS-12526, TNS-12527 or TNS-12528 even when supplying the credentials for a privileged account.

The lsnrctl services output will show that the service handler for this instance is in state: BLOCKED or RESTRICTED.

This can be solved in to ways;
  1. The global dbname in listener configuration will be the static service name
  2. Specify in the TNSNAMES string (UR=A)

The (UR=A) clause for TNS connect strings was created in response to an enhancement request.
This clause can be inserted into the "(CONNECT_DATA=" section of a TNS connect string and allow a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users. This feature is introduced since Oracle 10g.

(UR = A) example:

JOORDS_DB = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = node_db.joords.nl)(PORT = 1525)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = v10_service.joords.nl) 
(UR = A) 
) 
)


Note: The (UR=A) clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred. This method can also be used by ASM, Standby Database databases.