Tuesday, June 26, 2018

ORA-12528: TNS:listener: all appropriate instances are blocking new connections via RMAN DUPLICATE

Symptom:

   We try to use rman duplicate to create a new standby DB.
   When we run rman> connect auxiliary sys/password@newstandby
   It error out ORA-12528: TNS:listener: all appropriate instances are blocking new connections
  When we check the new standby listener status, it shows BLOCKED

New standby DB must be in  nomount mode for rman dupplicate to work
In nomount mode, PMON can't register it to listener with ready status, so the listener status is BLOCKED,

Solution:

We need to add it manually to listener.ora
   LISTENER_standby_1529 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY=LISTENER_standby_1529))
      (ADDRESS = (PROTOCOL = TCP)(HOST = newstandby-vip)(PORT = 1529))
    )
  )

SID_LIST_LISTENER_standby_1529 =
  (SID_LIST =
    (SID_DESC =
        (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2)
        (SID_NAME =standby1)
    )
  )

The listener status should shows UNKNOWN after listener bounce, but it will allow primary to connect into nomount DB.
We can't use service_name to connect nomount db, we need to use SID, modify primary DB tnsnames.ora like this :
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.us.test.com)(PORT = 1529))(CONNECT_DATA = (SERVER = DEDICATED)(SID = primarydb1)))


1 comment:

Desong Liu said...

This is excellent! Thanks for your posting. This is very helpful.

Desong Liu