ORA-12514 on Oracle RAC

异常症状

erpapp connect issue

[applmgr@app1 network]$ tnsping prod

TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 05-JUL-2013 10:29:20

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dev1-vip.egolife.com)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=PROD1) (INSTANCE_NAME=PROD1)))
OK (10 msec)

异常确认

[applmgr@app1 network]$ sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Jul 5 10:29:23 2013

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

SQL> conn apps
Enter password: 
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

service on dev1

[grid@dev1 ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-JUL-2013 10:48:14

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> services
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 "PROD" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

service on dev2

[grid@dev2 ~]$ lsnrctl 

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-JUL-2013 10:48:37

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "PROD" has 1 instance(s).
  Instance "PROD2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:29 refused:0 state:ready
         LOCAL SERVER
Service "prod1" has 1 instance(s).
  Instance "PROD2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:29 refused:0 state:ready
         LOCAL SERVER
Service "prod2" has 1 instance(s).
  Instance "PROD2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:29 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

异常解决

the service prod1 is running on instance PROD2(host node is dev2)

relocate service prod1 from instance PROD2 to PROD1

[grid@dev2 ~]$ srvctl relocate service -d PROD -s prod1 -i PROD2 -t PROD1

service on dev1

[grid@dev1 ~]$ lsnrctl 

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-JUL-2013 10:57:10

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> services
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 "PROD" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:73 refused:0 state:ready
         LOCAL SERVER
Service "prod1" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:73 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

service on dev2

[grid@dev2 ~]$ lsnrctl 

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-JUL-2013 10:57:24

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "PROD" has 1 instance(s).
  Instance "PROD2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:52 refused:0 state:ready
         LOCAL SERVER
Service "prod2" has 1 instance(s).
  Instance "PROD2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:52 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Error Message

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

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action:

Wait a moment and try to connect a second time.
Check which services are currently known by the listener by executing: lsnrctl services listener_name
Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
Check for an event in the listener.log file.

Reference

Oracle 11g Error Message

blog comments powered by Disqus

Published

05 July 2013

Github

Rendered

Raw