Tuesday, May 29, 2012

ORA-12514 on +ASM , ASM & ORA-12514 , ASM tnsnames.ora

Environment was 3 Node RAC running on Grid Infrastructure. 11.2.0.1

I tried to connect to +ASM instances using sqlplus from same node using TNSnames but i got ORA-12514.

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 29 19:44:36 2012

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

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


Enter user-name: 

Both Listener is running.
RACG1@:/u00/app/11.2.0/grid/network/admin :+ASM $ps -ef | grep tns
oracle    9661     1  0 18:54 ?        00:00:00 /u00/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    9696     1  0 18:55 ?        00:00:00 /u00/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit

Below is my configuration.
RACG1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $vi listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))             # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                                         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                                         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                                         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON                                               # line added by Agent

RACG1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $vi endpoints_listener.ora
LISTENER_RACG1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG1-VIP)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.121)(PORT=1521)(IP=FIRST))))  # line added by Agent

Tnsentry.
+ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = RACG-SCAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )

Here are services supported by from both listener.
RACG1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $lsnrctl services listener

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-MAY-2012 19:45:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "RACT.localdomain.com" has 1 instance(s).
  Instance "RACT1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "RACTXDB.localdomain.com" has 1 instance(s).
  Instance "RACT1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=RACG1.localdomain.com)(PORT=54564))
The command completed successfully


RACG1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $lsnrctl services listener_scan2

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-MAY-2012 19:45:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary...
Service "RACT.localdomain.com" has 3 instance(s).
  Instance "RACT1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG1-VIP)(PORT=1521)))
  Instance "RACT2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG2-VIP)(PORT=1521)))
  Instance "RACT3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG3-VIP)(PORT=1521)))
Service "RACTXDB.localdomain.com" has 3 instance(s).
  Instance "RACT1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=RACG1.localdomain.com)(PORT=54564))
  Instance "RACT2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=RACG2.localdomain.com)(PORT=65286))
  Instance "RACT3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=RACG3.localdomain.com)(PORT=39505))
The command completed successfully

Here What we needed to do ....

RACG1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 29 19:46:21 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE

Just add REMOTE_LISTENER pointing to SCAN ip address.

SQL> ALTER SYSTEM SET remote_listener='RACG-SCAN:1521' scope=both;

System altered.

SQL> ALTER SYSTEM REGISTER;

System altered.


As you can see how it reflected in scan listener

RACG1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $lsnrctl services listener_scan2

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-MAY-2012 19:48:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary...
Service "+ASM" has 3 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG1-VIP)(PORT=1521)))
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG2-VIP)(PORT=1521)))
  Instance "+ASM3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG3-VIP)(PORT=1521)))
Service "RACT.localdomain.com" has 3 instance(s).
  Instance "RACT1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG1-VIP)(PORT=1521)))
  Instance "RACT2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG2-VIP)(PORT=1521)))
  Instance "RACT3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=RACG3-VIP)(PORT=1521)))
Service "RACTXDB.localdomain.com" has 3 instance(s).
  Instance "RACT1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=RACG1.localdomain.com)(PORT=54564))
  Instance "RACT2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=RACG2.localdomain.com)(PORT=65286))
  Instance "RACT3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=RACG3.localdomain.com)(PORT=39505))
The command completed successfully