6 Replies Latest reply: Feb 24, 2014 1:07 AM by Tom321 RSS

    unable to connect to database via service name

    user10341747

      unable to connect to database via service name 

       

       

       

       

      Hello team,

       

       

      I have 2 node RAc configuration.below are my listener.ora,tnsnames.ora conf file,listener service status . While connecting the database with service service it is showing the error: Please let me know how i solve this issue

      =================================================================================================================================================

       

       

      Enter user-name: sys/oracle@rac1

      ERROR:

      ORA-12514: TNS:listener does not currently know of service requested in connect

      descriptor

       

       

       

       

      Enter user-name:

      [oracle@rac3 ~]$ echo $ORACLE_SID

      rac1

      [oracle@rac3 ~]$

       

       

      ==============================================

       

       

      [oracle@rac3 ~]$ lsnrctl service listener_rac1

       

       

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-FEB-2014 00:27:35

       

       

      Copyright (c) 1991, 2009, Oracle.  All rights reserved.

       

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC1)))

      Services Summary...

      Service "rac1" has 1 instance(s).

        Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:0 refused:0

               LOCAL SERVER

      Service "rac2" has 1 instance(s).

        Instance "rac2", status UNKNOWN, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:0 refused:0

               LOCAL SERVER

      The command completed successfully

       

       

      ==============================================

       

       

      [oracle@rac3 admin]$ more listener.ora

      LISTENER_RAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RA

      C1))))          # line added by Agent

       

       

      SID_LIST_LISTENER_RAC1=

      (SID_LIST =

        (SID_DESC =

                (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

                (SID_NAME=rac1)

         )

        (SID_DESC=

                (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

                (SID_NAME=rac2)

        )

      )

       

       

       

       

      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_RAC1=ON         # line added by Agent

      [oracle@rac3 admin]$

       

       

      ==========================================

       

       

      RAC =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = rac.localdomain)

          )

        )

       

       

      RAC2 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = rac2.rac.localdomain)

          )

        )

       

       

      RAC1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = rac1.rac.localdomain)

          )

        )

       

       

       

       

       

       

       

       

      ================================================

       

      Regards,

        • 1. Re: unable to connect to database via service name
          kgaur

          try connecting with rac1.rac.localdomain

           

          as:

           

          sqlplus sys/oracle@rac1.rac.localdomain

          • 2. Re: unable to connect to database via service name
            user10341747

            Hi Kuber,

             

            [oracle@rac3 admin]$  sqlplus sys/oracle@rac1.rac.localdomian

             

             

            SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 20 02:17:45 2014

             

             

            Copyright (c) 1982, 2009, Oracle.  All rights reserved.

             

             

            ERROR:

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

             

            ===========================================================

             

             

             

             

            i am getting this error in my sqlnet.log

             

             

            Fatal NI connect error 12541, connecting to:

            (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac3-vip)(PORT=1521))(CONNECT_DATA=(SID=rac1)(CID=(PROGRAM=emagent)(HOST=rac3.localdomain)(USER=oracle))))

             

             

              VERSION INFORMATION:

                    TNS for Linux: Version 11.2.0.1.0 - Production

                    TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

              Time: 03-NOV-2013 19:11:15

              Tracing not turned on.

              Tns error struct:

                ns main err code: 12541

                TNS-12541: TNS:no listener

                ns secondary err code: 12560

                nt main err code: 511

                TNS-00511: No listener

                nt secondary err code: 111

             

            ================================================================

             

            Regards

            • 3. Re: unable to connect to database via service name
              Tom321

              Hi,

               

              on your last test you had an typo:

              sqlplus sys/oracle@rac1.rac.localdomian

               

              Provide output for listener and scan listener:

              lsnrctl status LISTENER

              lsncrtl status LISTENER_SCAN1-3

               

              Do the listen on the right port? Do your databases register them with the right service name, incl. your db domain "localdomain"?

               

              Regards

              Thomas

              • 4. Re: unable to connect to database via service name
                hmartinezlopez

                Hi,

                 

                The service names registered in the listener are "rac1" and "rac2", without domain.

                 

                What is the value of DB_DOMAIN parameter?

                • 5. Re: unable to connect to database via service name
                  user10341747

                  Hi Tom321,

                   

                  i have checked the status of my listener. below is the o/p. i connected to database using sqlplus " sys/oracle@rac" as sysdba ...and i am able to connect . even with the same service name on second node.

                   

                  But when i ran backup archivelog all from any one of the node through rman. It is should take either thread 1 or thread 2 archives. Means if i connect to rman on rac1 and i take archivelog backup it should take only thread 1 archives but it also consider thread 2 archives (rac2) . How i can distinguish that to rman??. Is there is any command????

                   

                  [oracle@rac3 log]$ ps -ef|grep tns

                  oracle    4033     1  0 04:27 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit

                  oracle    4059     1  0 04:27 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit

                  oracle    4082     1  0 04:27 ?        00:00:00 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER_RAC1 -inherit

                  ===========================================

                   

                   

                   

                  [oracle@rac3 log]$ lsnrctl status LISTENER

                   

                   

                  LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-FEB-2014 04:49:44

                   

                   

                  Copyright (c) 1991, 2009, Oracle.  All rights reserved.

                   

                   

                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                  STATUS of the LISTENER

                  ------------------------

                  Alias                     LISTENER

                  Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

                  Start Date                21-FEB-2014 04:27:40

                  Uptime                    0 days 0 hr. 22 min. 4 sec

                  Trace Level               off

                  Security                  ON: Local OS Authentication

                  SNMP                      OFF

                  Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora

                  Listener Log File         /u01/app/oracle/diag/tnslsnr/rac3/listener/alert/log.xml

                  Listening Endpoints Summary...

                    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.101)(PORT=1521)))

                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=1521)))

                  Services Summary...

                  Service "+ASM" has 1 instance(s).

                    Instance "+ASM1", status READY, has 1 handler(s) for this service...

                  Service "rac.localdomain" has 1 instance(s).

                    Instance "rac1", status READY, has 1 handler(s) for this service...

                  Service "racXDB.localdomain" has 1 instance(s).

                    Instance "rac1", status READY, has 1 handler(s) for this service...

                  The command completed successfully

                   

                  ==========================================

                   

                  Enter user-name: sys/oracle@rac as sysdba

                   

                   

                  Connected to:

                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                  With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                  Data Mining and Real Application Testing options

                   

                   

                  SQL> select instance_name,instance_number from v$instance;

                   

                   

                  INSTANCE_NAME    INSTANCE_NUMBER

                  ---------------- ---------------

                  rac1                           1

                   

                  ============================================

                   

                  Connected to:

                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                  With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

                  Data Mining and Real Application Testing options

                   

                   

                  SQL> select instance_name,instance_number from v$instance;

                   

                   

                  INSTANCE_NAME    INSTANCE_NUMBER

                  ---------------- ---------------

                  rac2                           2

                   

                   

                  SQL> !

                   

                  ==================================================

                   

                   

                  [oracle@rac4 ~]$ rman

                   

                   

                  Recovery Manager: Release 11.2.0.1.0 - Production on Fri Feb 21 04:56:11

                   

                   

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

                   

                   

                  RMAN> connect target sys/oracle@rac

                   

                   

                  connected to target database: RAC (DBID=2418215644)

                   

                   

                  RMAN> backup archivelog all;

                   

                   

                  Starting backup at 21-FEB-14

                  current log archived

                  using target database control file instead of recovery catalog

                  allocated channel: ORA_DISK_1

                  channel ORA_DISK_1: SID=65 instance=rac2 device type=DISK

                  archived log /u01/app/oracle/product/11.2.0/archive/archarch_1_20_830233

                  rc not found or out of sync with catalog

                  trying alternate file for archived log of thread 1 with sequence 20

                  archived log /u01/app/oracle/product/11.2.0/archive/archarch_1_21_830233

                  kp not found or out of sync with catalog

                  trying alternate file for archived log of thread 1 with sequence 21

                  archived log /u01/app/oracle/product/11.2.0/archive/archarch_1_21_830233

                  rc not found or out of sync with catalog

                  trying alternate file for archived log of thread 1 with sequence 21

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

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

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

                  RMAN-03002: failure of backup command at 02/21/2014 17:41:17

                  RMAN-06059: expected archived log not found, loss of archived log compro

                  recoverability

                  ORA-19625: error identifying file /u01/app/oracle/product/11.2.0/db_1/db

                  harch_1_21_830233644.arc

                  ORA-27037: unable to obtain file status

                  Linux Error: 2: No such file or directory

                  Additional information: 3

                   

                  ===================================

                   

                  regards,

                  • 6. Re: unable to connect to database via service name
                    Tom321

                    Hi,

                     

                    so the connection issue is solved now? All connections work as expected?

                    ...

                    oracle    4082     1  0 04:27 ?        00:00:00 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER_RAC1 -inherit <<< Additional listener in db home

                     

                    You should use the listener running in the grid home, an additional listener will make service registration more complex and could cause the issues you had, e.g. if db instance only registers at listener from grid home, but you ask the db home listener for databases at connect time.

                    Why did you create another listener running in the database home?

                     

                     

                    You create the archivelogs in the db home and it looks like you don't have a shared storage for this!

                    RMAN-06059: expected archived log not found, loss of archived log compro

                    recoverability

                    ORA-19625: error identifying file /u01/app/oracle/product/11.2.0/db_1/db

                    harch_1_21_830233644.arc

                    ORA-27037: unable to obtain file status

                     

                    You must have a shared storage for the backups and archivelogs in rac! Technically it is possible to take backups from just one thread if you directly name the sequence number like this:

                    backup archivelog from sequence 352 until sequence 353 thread 1;

                     

                    But it's a terrible idea! For recovery purposes you need the backups and the archivelogs from both rac nodes, you can't just recover one node and ignore the other archivelogs. What are you trying to do with this setup?

                     

                    Regards

                    Thomas