8 Replies Latest reply: Dec 7, 2011 11:46 PM by Shivananda Rao RSS

    ORA-12154: TNS:could not resolve the connect identifier specified

    Shivananda Rao
      Hi Experts,

      Created physical standby database on Oracle 11.2.0.1 plaftorm OEL 5.5
      Iam able to ping the Primary server from the Standby and viceversa. Also Iam able to tnsping the primary database from the standby server and standby database from primary server.

      With this I was able to connect the databases remotely and later created the standby database.
      But, the logs are not getting shipped from the primary database to the standby database. The alert log file shows
      ORA-12154: TNS:could not resolve the connect identifier specified


      Please help me out.
        • 1. Re: ORA-12154: TNS:could not resolve the connect identifier specified
          mseberg
          Hello;

          Funny, I was just upgrading a Data Guard database and I ran into something similar.

          Can you tnsping both servers from the other?

          Can you connect from ( sqlplus ) from each server to the other?

          Is your listener setup correctly?

          Ex.

          http://www.visi.com/~mseberg/Data_Guard_Listener_Example.html

          If you do a :

          lsnrctl status

          The READY status comes from the instance registering with the listener as shown in the link example.

          Best Regards

          mseberg
          • 2. Re: ORA-12154: TNS:could not resolve the connect identifier specified
            Shivananda Rao
            Hi mseberg,
            Can you tnsping both servers from the other?
            Yes, Iam able to do so. Able to ping the primary from the standby and viceversa.

            Can you connect from ( sqlplus ) from each server to the other?
            Iam able to connect the databases as well remotely. Primary database from standby server and standby database from primary server.

            The primary database is registered with its listener and the status is READY when I check the status.
            The same is the case with the standby database.

            I faced the same issue a few months back while setting up the standby database and later was able to resolve it. The problem was with the NAMES.DIRECTORY_PATH in sqlnet.ora. The parameter did not had a value EZCONNECT. :)

            This time everything is fine but still the logs are not getting shipped.
            • 3. Re: ORA-12154: TNS:could not resolve the connect identifier specified
              mseberg
              I would stop and start the listener on both ends.

              The hardest part of this ( for me ) is waiting a minute or two for the status check.

              They should both show "ready" after a minute or two.

              I assume remote_login_passwordfile is set correctly.

              By chance did you change the SYS password on the Primary? On Oracle 11 this would require a new copy of the password file to the standby.

              Keep this note in your back pocket :

              ORA-01031 ORA-12154 ORA-12514 in Standby or Dataguard Environment [ID 1271362.1]

              Best Regards

              mseberg

              This looks good too

              Logs are not shipped to the physical standby database [ID 1130523.1]

              Edited by: mseberg on Dec 7, 2011 10:57 AM
              • 4. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                Vishalp-Oracle
                If you are able to tnsping from both servers to each other. And if your log_archive_dest is set correctly then could be problem with arc process.

                Follow MOS note 1130523.1
                • 5. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                  Shivananda Rao
                  I've tried restarting the listner on both the servers.
                  The status of the services registered with the respective listener show the READY status.

                  Also the remote_login_passwordfile is set to Exclusive on both the servers. So there is no issue with it and also the password of the sys user on primary database was not changed.


                  Followed the steps provided in the MOS ID 1130523.1 but still no luck.

                  Errors from the Primary DB alert log file:

                  Wed Dec 07 23:01:19 2011
                  Thread 1 advanced to log sequence 18 (LGWR switch)
                  Current log# 3 seq# 18 mem# 0: /u01/home/oracle/oradata/prim/redo03.log
                  Wed Dec 07 23:01:22 2011
                  Archived Log entry 16 added for thread 1 sequence 17 ID 0xf19efae dest 1:
                  Wed Dec 07 23:02:24 2011
                  Error 12154 received logging on to the standby
                  Errors in file /u01/app/oracle/diag/rdbms/prim/prim/trace/prim_arc2_26966.trc:
                  ORA-12154: TNS:could not resolve the connect identifier specified
                  PING[ARC2]: Heartbeat failed to connect to standby 'stnd'. Error is 12154.


                  Errors from the standby DB alert log file:

                  ORA-12154: TNS:could not resolve the connect identifier specified
                  Wed Dec 07 23:10:58 2011
                  Error 12154 received logging on to the standby
                  FAL[client, MRP0]: Error 12154 connecting to prim for fetching gap sequence
                  Errors in file /u01/app/oracle/diag/rdbms/stnd/stnd/trace/stnd_mrp0_13215.trc:
                  ORA-12154: TNS:could not resolve the connect identifier specified
                  Errors in file /u01/app/oracle/diag/rdbms/stnd/stnd/trace/stnd_mrp0_13215.trc:
                  ORA-12154: TNS:could not resolve the connect identifier specified
                  • 6. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                    CKPT
                    Very common and weird problem ever with ora12154

                    Can you please post what is the value for local_listener both on primary and standby?
                    Set that parameter once, manually register listener as alter system register

                    If not worked then bounce the database. Make sure you have local_listener parameters in both primary and standby.
                    • 7. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                      Vishalp-Oracle
                      Also can you paste contents of your sqlnet.ora on primary?

                      Make sure below parameter is set to only tnsnames.

                      NAMES.DIRECTORY_PATH=(TNSNAMES)
                      • 8. Re: ORA-12154: TNS:could not resolve the connect identifier specified
                        Shivananda Rao
                        Thanks for all your support.

                        Was able to identify the problem. The problem laid in the log_archive_dest_2 of the standby database with the value of "service=" was spelled wrongly.