ORA-12514

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
-------------------------------------------------------------------------------------------
I would like to share complete solutions for this errors. Recently I come across these errors while configuring data guard from active database. Here is the error

$ sqlplus sys/oracle@sbmtest
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 5 22:30:17 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Here is the possible ways to resolve this problem.

1. Check tnsnames.ora ($ORACLE_HOME/network/admin). It should have proper SERVICE_NAME as per database name. A simple tnsnames.ora here

SBMTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <your Host Name>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SBMTEST)
)
)

2. Check Listener parameters in the database. Here is example

SQL> show parameter listener;
NAME                                                         TYPE                                     VALUE
———————————— ———– ——————————
listener_networks                                      string
local_listener                                               string                                    SBMTEST
remote_listener                                          string
If local_listener parameter don’t have any value then execute this command.
SQL> alter system set local_listener=’SBMTEST’ scope=spfile;
System altered.
3.  Still issue persists check database status.
SQL> select instance_name, status, database_status from v$instance;
INSTANCE_NAME            STATUS               DATABASE_STATUS
—————- ———— —————————————
SBMTEST                            STARTED              ACTIVE
Database should be active.  Other wise check alert log and troubleshoot.

4. Check the services you will find clue here. Here is the sample output.

$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 06-JUL-2012 10:53:51
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary…
Service “MTEST” has 1 instance(s).
Instance “MTEST”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:73 refused:0 state:ready
LOCAL SERVER
Service “MTESTXDB” has 1 instance(s).
Instance “MTEST”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000? established:0 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: mone.mserver.com, pid: 8386>
(ADDRESS=(PROTOCOL=tcp)(HOST=mone.mserver.com)(PORT=33456))
The command completed successfully


5.Finally this one resolved my issue. Check services name in database it is pointing to other  instance. I have changed the service_names parameter and bounced database. It worked out.

SQL> show parameter service
NAME                                     TYPE                                           VALUE
———————————— ———– ——————————
service_names                       string                                            MTEST
SQL> alter system set service_names=’SBMTEST’ scope=spfile;
System altered.
bounce database then check

SQL> show parameter service;
NAME                                                       TYPE             VALUE
———————————— ———– ——————————
service_names                                         string             SBMTEST

$ sqlplus “sys/oracle@sbmtest as sysdba”

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 5 23:32:15 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;
INSTANCE_NAME               STATUS
——————————————————–
SBMTEST                               STARTED