--- layout: post title: ORA-12514 on Oracle RAC category : Oracle tags : [Oracle, Database, DBA, Exception] --- ## 异常症状 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