Friday , August 18 2017
Home / Oracle DBA / RMAN-04006: error from auxiliary database:ORA-12528: TNS:listener: all appropriate instances are blocking new connections:

RMAN-04006: error from auxiliary database:ORA-12528: TNS:listener: all appropriate instances are blocking new connections:

If you try to connecting duplicate database using rman and you get following error:

[oracle@dbcentre1 ~]$ rman target sys/rac auxiliary sys/rac@to_ckpt_stdby

Recovery Manager: Release 11.2.0.2.0 – Production on Fri Jul 28 15:10:25 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: CKPT (DBID=1925149927)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Checking  Listener Service using lsnrctl status, and  also shows All the service with instance with status is ‘BLOCKED’:
NOW CHECKING  TNSPING :
[oracle@dbcentre2 admin]$ tnsping to_ckpt_stdby

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbcentre2.oracle.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =ckpt_stdby)))
OK (0 msec)

NOW  CHECKING  LISTENER STATUS:
[oracle@dbcentre2 admin]$ lsnrctl status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbcentre2.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “ckpt_stdby” has 1 instance(s).
Instance “ckpt_stdby”, status BLOCKED, has 1 handler(s) for this service…
The command completed successfully.

SOLUTION:

In This Most probably your auxiliary database in  ( NOMOUNT STATE) is the only instance works on current ORACLE_HOME. Each instance is registered in listener by PMON process, which starts only when database is at least mounted. If there is nothing registered in listener before, instance in blocked.

Then you can do In this thing:

  1. Modify  tnsnames.ora file :
[oracle@dbcentre2 admin]$ vi tnsnames.ora[oracle@dbcentre2 admin]$ vi tnsnames.ora
TO_CKPT_STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbcentre2.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =ckpt_stdby)(UR=A)
)

Above tnsnames.ora file in you can apply ( UR=A), After modify to the tnsnames.or file and lets check  tnsping. Then Reload listener and check status, as its status registration , Service  with instance in unknown status added to listener.Once the status is UNKNOWN for the instance you should be able to connect.

[oracle@dbcentre2 admin]$ lsnrctl status

Services Summary…
Service “ckpt_stdby” has 2 instance(s).
Instance “ckpt_stdby”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

now you get  unknown status then you connect to the auxiliary database.

[oracle@dbcentre1 ~]$ rman target sys/rac auxiliary sys/rac@to_ckpt_stdby

Recovery Manager: Release 11.2.0.2.0 – Production on Fri Jul 28 16:07:38 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: CKPT (DBID=1925149927)
connected to auxiliary database (not started)

RMAN>

 

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *