4 Replies Latest reply: Jan 11, 2013 8:10 AM by 984350 RSS

    DGMGRL add database error ora-12154

    963362
      Dear all,
      We want to setup the DR site, and use oracle data guard broker.
      Oracle database: 11.2.0.3
      OS: AIX
      Production is HA, HA hostname: ERPDB (run dgmgrl)
      DR server hostname: ERPDBZ
      We have startup mount the standby database on ERPDBZ.
      And modify the listener and tnsnames files.
      We got following error when add the database into dg broker.
      DGMGRL> add database 'srp_dr' as connect identifier is 'srp_erpdbz' maintained as physical;
      Error: ORA-12154: TNS:could not resolve the connect identifier specified
      However, tnsping srp_erpdbz is Ok.
      Could you please help to see it?
      Thanks.
      Best Regards,
      J.W.

      ERPDB - tnsnames.ora_
      srp_erpdbz.WORLD =
      (DESCRIPTION =
      (ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = erpdbz)(PORT = 1527))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
           (GLOBAL_NAME = srp_erpdbz.WORLD)
      (SERVICE_NAME = srp_erpdbz)
      )
      )
      srp_erpdb.WORLD =
      (DESCRIPTION =
      (ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = erpdb)(PORT = 1527))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
           (GLOBAL_NAME = srp_erpdb.WORLD)
      (SERVICE_NAME = srp_erpdb)
      )
      )
      --------
      ERPDB - listener.ora_
      ################
      # Filename......: listener.ora
      # Created.......: created by SAP AG, R/3 Rel. >= 6.10
      # Name..........:
      # Date..........:
      # @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/LISTENER.ORA#1 $
      ################
      ADMIN_RESTRICTIONS_LISTENER = on
      LISTENER =
      (ADDRESS_LIST =
      (ADDRESS =
      (PROTOCOL = IPC)
      (KEY = SRP.WORLD)
      )
      (ADDRESS=
      (PROTOCOL = IPC)
      (KEY = SRP)
      )
      (ADDRESS =
      (COMMUNITY = SAP.WORLD)
      (PROTOCOL = TCP)
      (HOST = ERPDB)
      (PORT = 1527)
      )
      # (ADDRESS =
      # (COMMUNITY = SAP.WORLD)
      # (PROTOCOL = TCP)
      # (HOST = ERPDBA)
      # (PORT = 1527)
      # )
      )
      STARTUP_WAIT_TIME_LISTENER = 0
      CONNECT_TIMEOUT_LISTENER = 10
      TRACE_LEVEL_LISTENER = OFF
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = SRP)
      (ORACLE_HOME = /oracle/SRP/112_64)
      )
      (SID_DESC =
      (SID_NAME = SRP)
           (GLOBAL_DBNAME=srp_erpdb)
      (ORACLE_HOME = /oracle/SRP/112_64)
      )
           (SID_DESC =
      (SID_NAME = SRP)
           (GLOBAL_DBNAME=srp_erpdb_dgmgrl)
      (ORACLE_HOME = /oracle/SRP/112_64)
      )
      )
      #
      #Automatic Diagnostics Repository (ADR) Base directory
      #
      ADR_BASE_LISTENER=/oracle/SRP/saptrace
      ----------
      ERPDBZ - tnsnames.ora*
      srp_erpdbz.WORLD=
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS =
      (COMMUNITY = SAP.WORLD)
      (PROTOCOL = TCP)
      (HOST = ERPDBZ)
      (PORT = 1527)
      )
      )
      (CONNECT_DATA =
      (SID = SRP)
      (GLOBAL_NAME = srp_erpdbz.WORLD)
           (SERVICE_NAME = srp_erpdbz)
      )
      )
      srp_erpdb.WORLD=
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS =
      (COMMUNITY = SAP.WORLD)
      (PROTOCOL = TCP)
      (HOST = ERPDB)
      (PORT = 1527)
      )
      )
      (CONNECT_DATA =
      (SID = SRP)
      (GLOBAL_NAME = srp_erpdb.WORLD)
           (SERVICE_NAME = srp_erpdb)
      )
      )
      -----------
      ERPDBZ - listener.ora_
      ################
      # Filename......: listener.ora
      # Created.......: created by SAP AG, R/3 Rel. >= 6.10
      # Name..........:
      # Date..........:
      # @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/LISTENER.ORA#1 $
      ################
      ADMIN_RESTRICTIONS_LISTENER = on
      LISTENER =
      (ADDRESS_LIST =
      (ADDRESS =
      (PROTOCOL = IPC)
      (KEY = SRP.WORLD)
      )
      (ADDRESS=
      (PROTOCOL = IPC)
      (KEY = SRP)
      )
      (ADDRESS =
      (COMMUNITY = SAP.WORLD)
      (PROTOCOL = TCP)
      (HOST = ERPDBZ)
      (PORT = 1527)
      )
      )
      STARTUP_WAIT_TIME_LISTENER = 0
      CONNECT_TIMEOUT_LISTENER = 10
      TRACE_LEVEL_LISTENER = OFF
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = SRP)
      (ORACLE_HOME = /oracle/SRP/112_64)
      )
           (SID_DESC =
      (SID_NAME = SRP)
           (GLOBAL_DBNAME=srp_erpdbz)
      (ORACLE_HOME = /oracle/SRP/112_64)
      )
           (SID_DESC =
      (SID_NAME = SRP)
           (GLOBAL_DBNAME=srp_erpdbz_dgmgrl)
      (ORACLE_HOME = /oracle/SRP/112_64)
      )
      )
        • 1. Re: DGMGRL add database error ora-12154
          mseberg
          Hello;


          Would expect a full hostname after the _DGMGRL :
          (SID_DESC =
                 (global_dbname = PRIMARY_DGMGRL.thanks.buddy.com)
                 (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
                 (sid_name = PRIMARY)
          Will double check everything.

          Can you post - lsnrctl status results?


          h3. later

          I checked and you can do this without a hostname.


          Make that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain
           SID_LIST_LISTENER =
            (SID_LIST =
           (SID_DESC =
                  (GLOBAL_DBNAME = STANDBY_dgmgrl)
                  (ORACLE_HOME = /u01/oracle/product/11.2.0.0)
                  (SID_NAME = STANDBY)
                  )
          )
          Edit the listener.ora on each server and make sure you restart the listener.


          h3. Something else worth checking

          LOCAL_LISTENER is required if using a port other than 1521


          Best Regards

          mseberg

          Edited by: mseberg on Sep 20, 2012 10:20 AM

          Edited by: mseberg on Sep 20, 2012 10:46 AM
          • 2. Re: DGMGRL add database error ora-12154
            Shivananda Rao
            Please take a look at this http://oracledbabhuvan.blogspot.in/2011/10/ora-12154-tnscould-not-resolve-connect.html
            • 3. Re: DGMGRL add database error ora-12154
              Mahir M. Quluzade
              Hi,


              Welcome to OTN Forums !

              I think this link : http://www.oracle.com/us/solutions/sap/wp-ora4sap-dataguard11g-303811.pdf with help you. Check steps, please

              Regards
              Mahir M. Quluzade
              • 4. Re: DGMGRL add database error ora-12154
                984350
                DGMGRL ignores $TNS_ADMIN. Put tnsnames.ora in your $ORACLE_HOME/network/admin or use symbolic link
                HTH