Oracle Database connection error -- ORA-12560: TNS:protocol adapter error

[Former Member]

Hi,

I am unable to connect to Oracle 8i database  getting the following error

Cause:

SAP system to be migrated from Windows 2000 NT to Windows 2003 Operating system

SAP Installatoin Version 4.6 C with Oracle Database 8.1.7 installed on New server

Offline database backup restored successfully using BR commands

Error while connecting to the database on new server

sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Aug 10 11:44:43 2012

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> conn /as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error

Steps followed :

• Listener services checked and tested
• TNS files checked
• Environment variables checked
• TNS ping to SID possible
• Oraacle SID env set

Attaching herewith the network files

Thanks in advance

Regards,

Kunal Kulkarni

tnsnames.ora.zip

sqlnet.ora.zip

listener.ora.zip

[Former Member]

Hi Kunal,

Could you try to replace (don't delete the originals, just rename) the configuration files, I attached into the message and try to restart the listener. Additionally, execute the command below, at the OS level and paste the result;

lsnrctl status

One more thing to check that be sure the you are executing sqlplus by the "sidadm" user.

Best regards,

Orkun Gedik

listener.ora.txt.zip

tnsnames.ora.txt.zip

sqlnet.ora.txt.zip

[Former Member]

Hi Orkun,

I am executing the same with <sidadm> user & have replaced the network files with the new ones.

Please check below status of lsnrctl status

C:\Documents and Settings\d11adm.MUMSERVER>lsnrctl status

LSNRCTL for 32-bit Windows: Version 8.1.7.4.0 - Production on 10-AUG-2012 14:00:
50

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=D11.WORLD))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 8.1.7.4.0 - Produc
tion
Start Date                10-AUG-2012 14:00:24
Uptime                    0 days 0 hr. 0 min. 25 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   D:\oracle\D11\817\network\ADMIN\listener.ora
Listener Log File         D:\oracle\D11\817\network\log\listener.log
Services Summary...
  D11           has 1 service handler(s)
The command completed successfully

C:\Documents and Settings\d11adm.MUMSERVER>

Regards,

Kunal

[Former Member]

If it is not work try to restart the server. Then, execute the sqlplus

[Former Member]

Hey Orkun,

It is still not connecting after a reboot of server.

Please suggest.

Regards,

Kunal

[former_member189725]

Can you set the ORACLE_SID=D11 , (all uppercase)

and then try to connect.

Regards

Ratnajit

[Former Member]

Hi Ratnajit,

This is not working out . Output posted below

C:\Documents and Settings\d11adm.MUMSERVER>SET ORACLE_SID=D11

C:\Documents and Settings\d11adm.MUMSERVER>SQLPLUS /NOLOG

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Aug 10 15:12:45 2012

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> CONN /AS SYSDBA
ERROR:
ORA-12560: TNS:protocol adapter error

SQL>

Regards,

Kunal

[former_member189725]

Can you try

SQL> conn /@D11 as sysdba

Regards

Ratnajit

[Former Member]

Hi,

To be sure that you are not executing the sqlplus on terminal services, isn't it? If it is correct, try to execute the sqlplus at the local machine desktop.

And sure that Oracle service (OracleService<DBSID>) is running.

Best regards,

Orkun Gedik

[Former Member]

Additionally, check the operating system users and groups;

ORA_DBA -> <sid>adm, SAPService<SID>

ORA_<SID>_DBA -> <sid>adm, SAPService<SID>

ORA_<SID>_OPER -> <sid>adm, SAPService<SID>

SAP_LocalAdmin -> <sid>adm, SAPService<SID>

SAP_<SID>_GlobalAdmin -> <sid>adm, SAPService<SID>

SAP_<SID>_LocalAdmin -> <sid>adm, SAPService<SID>

Best regards,

Orkun Gedik

[Former Member]

Hi Ratnajit,

Thanks . This worked connected to the target database by specifying its SID as " conn /@D11 as sysdba".  Later i faced an controlfile issue which got resloved by recreating the same and opening the database with RESETLOGS option.

How can i fix this , if i want to get connected just by stating "conn / as sysdba" rather than mentioning the desired SID always.

Apprreciate your help.

Best Regards,

Kunal

[Former Member]

Thanks for help Orkun. The solution was to connect the database mentioning the target database SID to be connected  i.e " conn /@ D11 as sysdba " which was not been taken when i was trying "conn /as sysdba"

Thanks

Kunal

[Former Member]

Kunal,

What I mentioned is fixing the problem by connecting to the system with the "conn / as sysdba". So, Did you check last two steps?

Best regards,

Orkun Gedik

[former_member189725]

Can you please paste the output of the command set executed in the same login where you are trying to connect.

Regards

Ratnajit

[Former Member]

Hi Ratanjit,

Please find below the commands executed

C:\Documents and Settings\d11adm.MUMSERVER>set oracle_sid=D11

C:\Documents and Settings\d11adm.MUMSERVER>sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Aug 10 13:50:54 2012

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> conn /as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error

SQL>

Regards,

Kunal