Using UR=A to Connect to Databases in Nomount Mode

Time to time there are situation where connecting to a database in nomount mode is needed. Most noteably is the data guard standby creation. When a database is started in nomount mode

startup nomount;

the service on the listener would have a blocke status.

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

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

sqlplus sys@devcdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 14 20:27:16 2021
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.

DEVCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devcdb)
    )
  )

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 = test-4-254.codegen.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devcdb)
      (UR = A)
    )
  )
  
sqlplus sys@devcdbur as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 14 20:47:13 2021
Version 19.12.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

Even during normal DG operation use of UR=A in TNS entries used for DG traffic could be useful. During switchover primary could attmept to connect to standby which is in the nomount state (progressing to mount or read only). Without UR=A the primary connection would fail (and succeed later after reattempt, once standby has transitioned out of nomount). To reduce the false alerts occuring in these situation UR=A could be used.
For example without UR=A entry it took nearly 15s for DG broker to connect to the new standby during a switchover (output on a 19.13 system).

DGMGRL> switchover to dgtest
Performing switchover NOW, please wait...
Operation requires a connection to database "dgtest"
Connecting ...
Connected to "dgtest"
Connected as SYSDBA.
New primary database "dgtest" is opening...
Oracle Clusterware is restarting database "dgtest3" ...
[W000 2021-10-25T13:49:46.227+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:47.231+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:48.234+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:49.237+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:50.240+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:51.242+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:52.245+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:53.248+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:54.250+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:55.252+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:56.255+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:57.257+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:58.260+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:59.262+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:50:00.265+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:50:01.274+00:00] Failed to attach to dgtest3tns.
Connected to "dgtest3"
Connected to "dgtest3"
Switchover succeeded, new primary is "dgtest"

However, with UR=A in the TNS entry it was able to connect sooner.

DGMGRL> switchover to dgtest
Performing switchover NOW, please wait...
Operation requires a connection to database "dgtest"
Connecting ...
Connected to "dgtest"
Connected as SYSDBA.
New primary database "dgtest" is opening...
Oracle Clusterware is restarting database "dgtest3" ...
[W000 2021-10-25T13:55:24.705+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:25.708+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:26.710+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:27.714+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:28.716+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:29.718+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:30.722+00:00] Failed to attach to dgtest3tns.
Connected to "dgtest3"
Connected to "dgtest3"
Switchover succeeded, new primary is "dgtest"

UR=A is used in the TNS entries created in DBCS VM DBs that also has data guard enabled.

Related Metalink note
Connections to NOMOUNT/MOUNTED or RESTRICTED Databases Fail [ID 362656.1]

Update 28 January 2022
Entries similar to "[W000 2021-10-25T13:55:24.705+00:00] Failed to attach to dgtest3tns" are due to bug 30870248. Apply the patch if available for the RU.