sqlplus gets ORA-12504 when using EZCONNECT syntax

Question

Having the opposite problem as this person. TNSPING OK but sqlplus gives ORA-12154?

I'm trying to install Oracle on a VM in VMWare Workstation. I've tried to install Oracle 11g and 12c on:

• Windows Server 2008 R2
• Windows Server 2012 R2
• Windows Server 2016 Tech Preview 3 (out of desperation)

In every case, I get the dialog that says "minimum requirements not met". When I check the install log in AppData I get this.

Checking whether the IP address of the localhost could be determined... SEVERE: CVU do not support target environment

I've tried setting my VMs to use NAT as well as using a Bridged connection. According to this thread, the 'CVU error' can be ignored. https://community.oracle.com/thread/2478769

Oracle installs successfully every time and it auto inserts an entry in my tnsnames.ora file. I can connect as the sys user by doing sys@TNS_ENTRY as sysdba

But the second I try using EZCONNECT syntax, it doesn't work. For example:

>sqlplus /nolog
>connect sys@localhost:1521/service

I then get an ORA-12504: TNS:Listener was not given the SERVICE_NAME in CONNECT_DATA. This is weird because I'm not even trying to use an entry from the tnsnames.ora file to connect. I've tried promoting EZCONNECT in the sqlnet.ora config manager in Net Manager.

I have verified that the Oracle installer did put the listener on my machine called "LISTENER"

I have another server running Windows Server 2012 R2 running on bare metal. I cross checked all of the tns, listener, and sqlnet configuration and their all identical (default configs that were created by the 11g installer)

I know VMWare supports Oracle databases on their VMs. I tried to stick to these best practices listed here. http://www.vmware.com/files/pdf/partners/oracle/Oracle_Databases_on_VMware_-_Best_Practices_Guide.pdf

I checked lsnrctl to make sure that it's loading the configuration correctly. I see my endpoint there when I restart the listener

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mymachinename)(PORT=1521)))

The firewall is off.

Here's the result of the tnsping:

C:\Users\Administrator>tnsping localhost

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-SEP-2015 11:36:02

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

Used parameter files: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))) OK (0 msec)

^looks exactly the same as the tnsping from my 2012 R2 bare metal server with 11g on it that works.

Am I missing something really easy here? I'm about at my wits' end with this.

Kimberlite

Answer 1

This is a problem with the EZCONNECT syntax, not the databse configuration, as described here by Mark Williams. The forward slash in the connect string is confusing EZCONNECT, it must be escaped like this:

C:\>sqlplus sys@\"localhost:1521/orcl12\" as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 18 13:10:19 2015

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Sou

Thank you! I feel like a complete idiot... I couldn't even connect from sqldeveloper which is strange. But things are working well now. :) – Kimberlite

Permanent link for Mark Williams' blog in case it ever goes down. web.archive.org/web/20120717020405/http://oradim.blogspot.com/… – Kimberlite

Note that you must also use \"username\" in case your Oracle user name is not all uppercase too. Especially if you have both a foo and FOO user. W/o the \" around the user name, Oracle does its implicit uppercase transformation, and that's not the same user as intended perhaps! Yes, such non-uppercase user names are not recommended, and even more overloaded by case only, but Oracle does allow and support it, so better be prepared. – Bingo