Forum Stats

  • 3,824,773 Users
  • 2,260,416 Discussions
  • 7,896,309 Comments

Discussions

RMAN-04006: Error from auxiliary database: ORA-12514: TNS: Listener is currently unable to resolve t

User_CQS9E
User_CQS9E Member Posts: 90 Red Ribbon
edited Feb 24, 2020 10:11AM in Recovery Manager (RMAN)

Hi,

I know this is a very common error and there are lots of solutions on Google and elsewhere. But i have tried most and its not working in my case. We have multiple instances on a since hosts.

I am trying to setup Active data guard for POC, but when i am trying to connect to my Auxiliary instance via RMAN its giving me the below mentioned error.

RMAN> connect target sys/******

Mit Zieldatenbank verbunden: CHENNAI (DBID=125112213) -- this is ok

RMAN> connect auxiliary sys/********@DELHI -- This is where it fails.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

"RMAN-04006: Error from auxiliary database: ORA-12514: TNS: Listener is currently unable to resolve the service specified in the Connect descriptor"

The tnsping is working fine both ways, below is the output.

[deuxo2aa:/pkg/vddw1/oradw1/12.2.0.1/dbs][oradw1]# tnsping CHENNAI

TNS Ping Utility for HPUX: Version 12.2.0.1.0 - Production on 19-FEB-2020 18:15:57

Copyright (c) 1997, 2017, Oracle.  All rights reserved.

Parameterdateien benutzt:

/pkg/vddw1/oradw1/12.2.0.1/network/admin/sqlnet.ora

Adapter TNSNAMES zur Aufl▒sung des Alias benutzt

Verbindungsversuch mit (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = source.XX.XX.XX)(PORT = 2121))) (CONNECT_DATA = (SERVICE_NAME = CHENNAI)(UR=A)))

OK (50 ms)

[deuxo2aa:/pkg/vddw1/oradw1/12.2.0.1/dbs][oradw1]# tnsping DELHI

TNS Ping Utility for HPUX: Version 12.2.0.1.0 - Production on 19-FEB-2020 18:16:02

Copyright (c) 1997, 2017, Oracle.  All rights reserved.

Parameterdateien benutzt:

/pkg/vddw1/oradw1/12.2.0.1/network/admin/sqlnet.ora

Adapter TNSNAMES zur Aufl▒sung des Alias benutzt

Verbindungsversuch mit (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = target.XX.XX.XX)(PORT = 2121))) (CONNECT_DATA = (SERVICE_NAME = DELHI)(UR=A)))

OK (10 ms)

CHENNAI is my source and DELHI is my STANDBY.

Listener entries are as follows -

On Source (CHENNAI) -

LISTENER_CHENNAI =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = source.XX.XX.XX)(PORT =2121))

    )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_CHENNAI = OFF

ADMIN_RESTRICTIONS_LISTENER_TOSCA = ON

STARTUP_WAIT_TIME_LISTENER_TOSCA = 0

CONNECT_TIMEOUT_LISTENER_TOSCA   = 10

TRACE_LEVEL_LISTENER_TOSCA      = OFF

LOG_DIRECTORY_LISTENER_TOSCA     = /pkg/vddw1/logs/oradw1/LISTENER_CHENNAI

LOG_FILE_LISTENER_TOSCA          = listener_CHENNAI.log

TRACE_DIRECTORY_LISTENER_TOSCA   = /pkg/vddw1/logs/oradw1/LISTENER_CHENNAI

SID_LIST_LISTENER_TOSCA =

  (SID_LIST =

    (SID_DESC =

(GLOBAL_DBNAME=CHENNAI)

      (SID_NAME = CHENNAI)

      (ORACLE_HOME = /pkg/vddw1/oradw1/12.2.0.1)

      (PRESPAWN_MAX = 10)

    )

(SID_DESC =

      (GLOBAL_DBNAME=DELHI)

      (SID_NAME = DELHI)

      (ORACLE_HOME = /pkg/vddw1/oradw1/12.2.0.1)

      (PRESPAWN_MAX = 10)

    )

On Target DELHI -

LISTENER_DELHI =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = target.XX.XX.XX)(PORT =2121))

    )

(SID_DESC =

(GLOBAL_DBNAME = DELHI)

(ORACLE_HOME = /pkg/vddz8/oradz8/12.2.0.1)

(SID_NAME = DELHI)

)

(SID_DESC =

(GLOBAL_DBNAME = CHENNAI)

(ORACLE_HOME = /pkg/vddw1/oradw1/12.2.0.1)

(SID_NAME = CHENNAI)

)

Can you please let me know what have i missed or any step by step guide to help my cause.

Thanks

Ameya Deshmukh

Sunny kichlooBo A

Best Answer

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Feb 19, 2020 2:42PM Answer ✓

    tnsping proves nothing beyond the fact that there is (or is not) a listener at the specified host and port.  It says nothing about the status or availability of any database.

    Why is your listener named LISTENER_CHENNAI instead of simply the default LISTENER.  A non-default name for the listener only serves to confuse matters further.  And this is a good example.  You named the listener LISTENER_CHENNAI, but then your SID_LIST is SID_LIST_LISTENER_TOSCA.  Listener LISTENER_CHENNAI needs SID_LIST_LISTENER_CHENNAI, not SID_LIST_LISTENER_TOSCA.

    And why is your listener on the non-default port of 2121?  If you think it is for security, guess again.  It would take a moderately competant network scanner just a few seconds to see through that one.

    Then we go to LISTENER_DELHI.  Again, no reason not to simply use the default name.  And your listener file is mal-formed, as you seem to start the sid list without the SID_LIST section header.

    Show us the ouput of 'lsnrctl status' on both servers.

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Feb 19, 2020 2:42PM Answer ✓

    tnsping proves nothing beyond the fact that there is (or is not) a listener at the specified host and port.  It says nothing about the status or availability of any database.

    Why is your listener named LISTENER_CHENNAI instead of simply the default LISTENER.  A non-default name for the listener only serves to confuse matters further.  And this is a good example.  You named the listener LISTENER_CHENNAI, but then your SID_LIST is SID_LIST_LISTENER_TOSCA.  Listener LISTENER_CHENNAI needs SID_LIST_LISTENER_CHENNAI, not SID_LIST_LISTENER_TOSCA.

    And why is your listener on the non-default port of 2121?  If you think it is for security, guess again.  It would take a moderately competant network scanner just a few seconds to see through that one.

    Then we go to LISTENER_DELHI.  Again, no reason not to simply use the default name.  And your listener file is mal-formed, as you seem to start the sid list without the SID_LIST section header.

    Show us the ouput of 'lsnrctl status' on both servers.

  • User_CQS9E
    User_CQS9E Member Posts: 90 Red Ribbon
    edited Feb 20, 2020 4:15AM

    Hi,

    Thanks for the information. I have recreated the listeners as required and now i am able to connect via RMAN.

    The reason the use non default ports is that we have multiple instances hosted on the server and default ports have been take, so as the LISTENER.

    As this is a POC it would be ok for me, but in PROD it would be default.

    Thanks for the help

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Feb 20, 2020 8:52AM
    3143529 wrote:Hi,Thanks for the information. I have recreated the listeners as required and now i am able to connect via RMAN.The reason the use non default ports is that we have multiple instances hosted on the server and default ports have been take, so as the LISTENER.As this is a POC it would be ok for me, but in PROD it would be default.Thanks for the help 

    It sounds like you are trying to run a separate listener for each database on a given server.  This is a huge mistake.  Please stay after school and write the following 100 times on the chalkboard:

    One single listener, using the default name of 'LISTENER' and the default port of 1521 is quite capable of - indeed, WAS DESIGNED TO - service multiple databases of multiple versions running from multiple homes.

    The listener is analogous to a telephone switchboard.  You would not build a separate switchboard for each telephone customer, but by running multiple listeners that is effectively what you are doing.  On each database server, create a single listener named simply LISTENER.  Run it on port 1521.  Unset the LOCAL_LISTENER parameter of all databases and they will use port 1521 to self-register with the listener.  You really don't even need a listener.ora file, as the listener will happily start with all default values if listener.ora is not found.  The only reason I create one is when I have a need to connect to an idle instance and so need the static registration of the SID_LIST.

    Sunny kichlooBo A
  • User_CQS9E
    User_CQS9E Member Posts: 90 Red Ribbon
    edited Feb 24, 2020 10:11AM

    Hi,

    Thanks for the information, i will try to configure it

    Regards,

    Ameya Deshmukh