• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1203
  • Last Modified:

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?
0
Etravels
Asked:
Etravels
  • 4
1 Solution
 
sujit_kumarCommented:
See what's in your scripts. I believe there is some connect user statement that is not working properly. If possible post your SQL file here.
0
 
EtravelsAuthor Commented:
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...

0
 
EtravelsAuthor Commented:
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
0
 
EtravelsAuthor Commented:
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?
0
 
EtravelsAuthor Commented:
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..
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now