asked on

sp2_0640 not connected, plus other 'login' issues

I am trying run create statements & stored procedures/tablespaces etc on 2 of my db servers:

I can connect as sysdba and either using OEM or as normal login and its fine but once i start to run '@create.sql' statement from my c: drive (all files are here and normally work) but i keep getting the error:

SP2_0640 NOT CONNECTED,

if i then type show user
i get ''
so basically it keeps dropping off.? I have checked my tnsnames.ora, listener.ora and sqlnet.ora and restarted the services, and rebooted and the same keeps happening. I thought it was just one db but its happening to them all> any ideas?

ASKER CERTIFIED SOLUTION

sujit_kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Etravels

ASKER

no problem, also the I see the OracleOra92HomeTNSListener won't start? i have restarted it, and stop/start. I even rebooted it and it hasn't restarted as it should.

my scripts:

# LISTENER.ORA Network Configuration File: C:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Dougal)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Dougal)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = Dougal1)
)
(SID_DESC =
(GLOBAL_DBNAME = neptune)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = neptune)
)
)
(SID_DESC =
(GLOBAL_DBNAME = neptune)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = neptune_dougal)
)
)

# SQLNET.ORA Network Configuration File: C:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

#SQLNET.AUTHENTICATION_SERVICES= (NTS)
SQLNET.AUTHENTICATION_SERVICES = (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

NEPTUNE.CHARTERUK.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dougal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = neptune)
)
)

NEPTUNE_DOUGAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dougal)(PORT = 1521))
)
(CONNECT_DATA =
(SID = neptune)
(SERVER = DEDICATED)
)
)

DOUGAL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dougal1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = dougal1)
(SERVER = DEDICATED)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Dougal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

hope this helps, 2 files...

Etravels

ASKER

Tby using the code in the cmd prompt on server, using lsnrctl, I see the following, maybe this explains it?

NSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(P
RT=1521)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...

Emer

Etravels

ASKER

ALSO, I have checked the above log file:

Started with pid=840
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-APR-2005 09:23:31

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Trace information written to C:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0

Started with pid=1536
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-APR-2005 09:23:53

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Trace information written to C:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0

Started with pid=1536
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-APR-2005 09:24:06

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Trace information written to C:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0

Started with pid=932
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-APR-2005 09:24:27

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Trace information written to C:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0

Started with pid=1588
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-APR-2005 09:25:55

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Trace information written to C:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0

Started with pid=816
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-APR-2005 09:26:22

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Trace information written to C:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0

Started with pid=216
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-APR-2005 09:34:03

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Trace information written to C:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0

Started with pid=1732
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=1521)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
26-APR-2005 09:34:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Eagle5))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * status * 0
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
26-APR-2005 09:34:30 * service_register * Dougal1 * 0
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Dougal.charteruk.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
26-APR-2005 09:34:30 * service_register * neptune * 0
26-APR-2005 09:34:51 * (CONNECT_DATA=(SERVICE_NAME=dougal)(CID=(PROGRAM=C:\Program Files\Oracle\jre\1.1.8\bin\jrew.exe)(HOST=DOUGAL)(USER=Eagle5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.1.51)(PORT=1175)) * establish * dougal * 0
26-APR-2005 09:34:52 * service_update * neptune * 0
26-APR-2005 09:34:54 * service_update * Dougal1 * 0

i think i'll get there somehow, need to see why?

Etravels

ASKER

i did some lsnrctl : here are the results, I'm still lost as the service still wont start, hence the error message....it

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
LSNRCTL> net start OracleOraHome92TNSListener
NL-00853: undefined command "net". Try "help"
LSNRCTL> start

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
System parameter file is C:\oracle\ora92\network\admin\liste
Log messages written to C:\oracle\ora92\network\log\listener
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Doug
RT=1521)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter
NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...

how can i fix this file? thanks all..