Solution- Using UR=A to Connect to Databases in Nomount Mode

              

Description:-

Refreshing a database from prod to test using rman duplicate method.

On Test server listener blocked with service name

startup nomount;

the service on the listener would have a blocked status.

Service "orcldb" has 1 instance(s).
  Instance "orcldb", status BLOCKED, has 1 handler(s) for this service...

Any attempt to connect to the instance using service name will fail.

sqlplus sys@orcldb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 13 20:27:16 2024
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

The TNS entry used is shown below.

DORCLDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prod22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldb)
    )
  )

One solution is to create a static listener entries as done when creating data guard configurations.

Other options is to use UR=A in the TNS entry. With the use of UR=A in TNS entry there's no need to create a static listener entry. Dynamic listener would allow connection without any issue.

DEVCDBUR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devcdb)
      (UR = A)
    )
  )

After adding a (UR=A) parameter on tnsnames.ora  and complete the refresh successfully.