Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server

Question

I installed 11g on Centos VM on my mac.I have everything up and working on server.I have included following command I ran on cenos.

$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-JUN-2014 14:49:41
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hayer)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 23-JUN-2014 14:03:58
Uptime 0 days 0 hr. 45 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/hayer/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hayer)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

My tnsnames.ora file is as follows:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))
ORCL =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = orcl)
 (SID = orcl)
 )
 )
~ 
~ 
~ 
~ 

On Centos server I can log in using sqlplus too. I changed tnsnames.ora on windows to same as server.I have added oracle host information in windows host file and I can ping oracle host and telnet host on port 1522.

I have disables service iptables so no type of firewall exists on centos.

But I still get following error on Windows client:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 23 14:51:31 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Answer 1

On your Windows client, you need to specify the TNS alias @ORCL, or else it assumes you're trying to connect to a local database running on your Windows client, which there isn't one. Also, system as sysdba generally isn't used, not even sure if that makes sense. SYS as sysdba and just plain system are used.

Enter user-name: system@ORCL
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

If you don't have tnsnames.ora set up on your Windows client, you can still connect via EZCONNECT string @"hayer:1522/orcl" instead of setting up tnsnames (yes, the quotes are needed).

Enter user-name: system@"hayer:1522/orcl"
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

• Joshua Huber: Side comment: I use SYSTEM for 99% of my DB Admin tasks. SYS (as SYSDBA) is more powerful and not needed for general tasks. You can't accidentally shut down the database when logged in as SYSTEM.

Answer 2

Try this:

1. Open Oracle Net Configuration Assistant

2. Select radio button option at "Local Net Service Name Configuration", then click "Next"

3. Select radio button option at "Reconfigure", then click "next"

4. Select the net service name you want to reconfigure, either ORACLR_CONNECTION_DATA or ORCL, then click "Next"

5. input your Service Name, if your service name of installed oracle as ORCL, then write ORCL to the field, then click "Next"

6. select the protocol you want to use, for example TCP, then click "Next"

7. input the hostname where your oracle database engine (service) is installed, for example your desktop or notebook.

   Then fill in your port number (select option "Use the Standard port number" of 1521) or if use other port, fill in the port number.

   Then click "next"

8. select "Yes", perform test, if necessary, change user logon

   Then click "next".

9. if you don't want to configure another net service name, select "No" and then click "next"

10. Click "Finish"