Ora 12514 ошибка

I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle:

select value from v$parameter where name='service_names'

Once I updated tnsnames.ora to:

TEST =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = *<servicenamefromDB>*)
    )
)

then I ran:

sqlplus user@TEST

Success!
The listener is basically telling you that whatever service_name you are using isn’t a valid service according to the DB.

(*I was running sqlplus from Win7 client workstation to remote DB and blame the DBAs *)

answered Apr 16, 2013 at 23:36

I know this is an old question, but still unanswered. It took me a day of research, but I found the simplest solution, at least in my case (Oracle 11.2 on Windows 2008 R2) and wanted to share.

The error, if looked at directly, indicates that the listener does not recognize the service name. But where does it keep service names? In %ORACLE_HOME%NETWORKADMINlistener.ora

The «SID_LIST» is just that, a list of SIDs and service names paired up in a format you can copy or lookup.

I added the problem Service Name, then in Windows «Services» control panel, I did a «Restart» on the Oracle listener service. Now all is well.

For example, your listener.ora file might initially look like:

# listener.ora Network Configuration File: C:apporacle_userproduct12.1.0dbhome_1networkadminlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:apporacle_userproduct12.1.0dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:apporacle_userproduct12.1.0dbhome_1binoraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

… And to make it recognize a service name of orcl, you might change it to:

# listener.ora Network Configuration File: C:apporacle_userproduct12.1.0dbhome_1networkadminlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:apporacle_userproduct12.1.0dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:apporacle_userproduct12.1.0dbhome_1binoraclr12.dll")
    )
    (SID_DESC = 
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = C:apporacle_userproduct12.1.0dbhome_1)
        (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

In my circumstances the error was due to the fact the listener did not have the db’s service registered. I solved this by registering the services. Example:

My descriptor in tnsnames.ora:

LOCALDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LOCALDB)
    )
  )

So, I proceed to register the service in the listener.ora manually:

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = LOCALDB)
      (ORACLE_HOME = C:Oracleproduct11.2.0dbhome_1)
      (SID_NAME = LOCALDB)
    )

Finally, restart the listener by command:

> lsnrctl stop
> lsnrctl start

Done!

answered May 31, 2017 at 23:39

I had this issue at Windows server 2008 R2 and Oracle 11g

go to Net Manager > Listener > select database services form the combox > «Global Database Name» must be same as «SID» and «Oracle Home Directory» must be correct.

If you don’t have any entry for database services, create one and set correct global database , sid and oracle home.

This really should be a comment to Brad Rippe’s answer, but alas, not enough rep. That answer got me 90% of the way there. In my case, the installation and configuration of the databases put entries in the tnsnames.ora file for the databases I was running. First, I was able to connect to the database by setting the environment variables (Windows):

set ORACLE_SID=mydatabase
set ORACLE_HOME=C:Oracleproduct11.2.0dbhome_1

and then connecting using

sqlplus / as sysdba

Next, running the command from Brad Rippe’s answer:

select value from v$parameter where name='service_names';

showed that the names didn’t match exactly. The entries as created using Oracle’s Database Configuration Assistant were originally:

MYDATABASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydatabase.mydomain.com)
    )
  ) 

The service name from the query was just mydatabase rather than mydatabase.mydomain.com. I edited the tnsnames.ora file to just the base name without the domain portion so they looked like this:

MYDATABASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydatabase)
    )
  ) 

I restarted the TNS Listener service (I often use lsnrctl stop and lsnrctl start from an administrator command window [or Windows Powershell] instead of the Services control panel, but both work.) After that, I was able to connect.

answered Jun 16, 2015 at 7:48

surajmallsurajmall

1581 silver badge7 bronze badges

answered Aug 30, 2021 at 6:49

I had the same problem. For me, just writing

sqlplus myusername/mypassword@localhost

did the trick, doing so makes it connect to the default service name, I guess.

grepitgrepit

20.8k6 gold badges101 silver badges81 bronze badges

answered May 29, 2012 at 0:50

I resolved this issue in my linux enviroment updating the IP of my machine in /etc/hosts file.

You can verify your network IP (inet end.) with:

$ifconfig

See if your IP matches with /etc/hosts file:

$cat /etc/hosts

Edit your /etc/hosts file, if nedded:

$sudo gedit /etc/hosts

Bye.

answered Sep 3, 2014 at 15:27

answered May 12, 2016 at 13:57

answered Jun 26, 2014 at 6:41

th3uiguyth3uiguy

1,8791 gold badge12 silver badges8 bronze badges

Lots of answers here, but here comes a working example with code that you can copy and paste and test immediately:

For me the error 12514 was solved after specifying the correct SERVICE_NAME.
You find that on the server in the file tnsnames.ora which comes with 3 predefined service names (one of them is «XE»).

1. I installed the Oracle Express database OracleXE112 which already comes with some preinstalled demo tables.
2. When you start the installer you are asked for a password. I entered «xxx» as password. (not used in production)
3. My server runs on the machine 192.168.1.158
4. On the server you must explicitely allow access for the process TNSLSNR.exe in the Windows Firewall. This process listens on port 1521.
5. OPTION A: For C# (.NET2 or .NET4) you can download ODAC11, from which you have to add Oracle.DataAccess.dll to your project. Additionally this DLL depends on: OraOps11w.dll, oci.dll, oraociei11.dll (130MB!), msvcr80.dll.
   These DLLs must be in the same directory as the EXE or you must specify the DLL path in: HKEY_LOCAL_MACHINESOFTWAREOracleODP.NET4.112.4.0DllPath. On 64 bit machines write additionally to HKLMSOFTWAREWow6432NodeOracle...
6. OPTION B: If you have downloaded ODAC12 you need Oracle.DataAccess.dll, OraOps12w.dll, oci.dll, oraociei12.dll (160MB!), oraons.dll, msvcr100.dll. The Registry path is HKEY_LOCAL_MACHINESOFTWAREOracleODP.NET4.121.2.0DllPath
7. OPTION C: If you don’t want huge DLL’s of more than 100 MB you should download ODP.NET_Managed12.x.x.x.xxxxx.zip in which you find Oracle.ManagedDataAccess.dll which is only 4 MB and is a pure managed DLL which works in 32 bit and 64 bit processes as well and depends on no other DLL and does not require any registry entries.
8. The following C# code works for me without any configuration on the server side (just the default installation):

using Oracle.DataAccess.Client;
or
using Oracle.ManagedDataAccess.Client;

....

string oradb = "Data Source=(DESCRIPTION="
    + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.158)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
    + "User Id=SYSTEM;Password=xxx;";

using (OracleConnection conn = new OracleConnection(oradb)) 
{
    conn.Open();
    using (OracleCommand cmd = new OracleCommand())
    {
        cmd.Connection  = conn;
        cmd.CommandText = "select TABLESPACE_NAME from DBA_DATA_FILES";

        using (OracleDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                listBox.Items.Add(dr["TABLESPACE_NAME"]);
            }
        }
    }
}

If the SERVICE_NAME=XE is wrong you get error 12514. The SERVICE_NAME is optional. You can also leave it away.

answered Apr 20, 2017 at 21:19

answered Apr 20, 2014 at 2:12

Mosab SasiMosab Sasi

1,1308 silver badges11 bronze badges

answered Oct 27, 2022 at 2:49

SteefSteef

5615 silver badges20 bronze badges

wishmanwishman

7744 gold badges14 silver badges31 bronze badges

Sir. HedgehogSir. Hedgehog

1,2503 gold badges17 silver badges39 bronze badges

user3132194user3132194

2,32123 silver badges17 bronze badges

answered Nov 29, 2017 at 12:34

BenasBenas

2,0362 gold badges39 silver badges65 bronze badges

BronekBronek

10.6k2 gold badges45 silver badges46 bronze badges

answered Apr 17, 2021 at 21:59

Rohit GaikwadRohit Gaikwad

3,6473 gold badges17 silver badges40 bronze badges

em_boem_bo

6246 silver badges13 bronze badges