Home » RDBMS Server » Enterprise Manager » Oracle 11g Ora error ORACLE not available (Oracle 11g, 11.2.0.1.0 , Windows Server 2008 R2)
Oracle 11g Ora error ORACLE not available [message #607389] Wed, 05 February 2014 07:43 Go to next message
Dylz
Messages: 5
Registered: February 2014
Location: Cape Town
Junior Member
Hi

I am having a problem connecting to our database, we use an Oracle tool called Oracle Enterprise Data Quality(OEDQ), which we just did an upgrade on, and after the upgrade we ran some data through for testing and it froze halfway through the process when i checked the OEDQ logs i see there was an error connecting to the database, and when i try to connect to the database via sqlplus i get the following error:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

sorry I am not a DBA, and don't know where to start looking at how to fix this issue, i have searched on google but non of the solutions i find are working. some help would be much appreciated.

many thanks
Re: Oracle 11g Ora error ORACLE not available [message #607390 is a reply to message #607389] Wed, 05 February 2014 07:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

What solutions did you find on Google that did not work? If you show what the problem was (use copy/paste, and do not forget to enclose within [code] tags perhaps someone can help.
Re: Oracle 11g Ora error ORACLE not available [message #607391 is a reply to message #607390] Wed, 05 February 2014 07:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
are both OS Services for the database & listener up & running now?

post results from following OS commands

lsnrctl start
lsnrctl status
lsnrctl service
Re: Oracle 11g Ora error ORACLE not available [message #607398 is a reply to message #607391] Wed, 05 February 2014 08:49 Go to previous messageGo to next message
Dylz
Messages: 5
Registered: February 2014
Location: Cape Town
Junior Member
lsnrctl start:

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-FEB-2014 16:18:36
Copyright (c) 1991, 2010, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started


lsnrctl status:

LSNRCTL for 64-bit windows: Version 11.2.0.1.0 - Production on 05-FEB-2014 16:23:08
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Server2)(PORT=1521)))
STATUS of the LISTENER
-----------------------
Alias                    LISTENER
Version                  TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date               05-Feb-2014 14:49:07
Uptime                   0 days 1 hr. 34min. 1sec
Trace Level              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  C:\Oracle\product\11.2.0\network\admin\listener.ora
Listener Log File        c:\oracle\product\11.2.0\log\diag\tnslsnr\Server2\listener\alert\log.xml
Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Server2)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status READY, has 2 handler(s) for this service...
Service "cwhdqdev" has 1 instance(s).
  Instance "cwhdqdev", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


lsnrctl service:

LSNRCTL for 64-bit windows: Version 11.2.0.1.0 - Production on 05-FEB-2014 16:35:48
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Server2)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s)
  Instance "CLRExtProc", status READY, has 2 handlers(s) for this service...
    Handler(s):
      "ORACLE SERVER" established:0 refused:0 current:0 max:25 state ready
         CLRExtProc
         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_2BEC_6693E230.ORA))
      "ORACLE SERVER" established:0 refused:0 current:0 max:25 state ready
         CLRExtProc
         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_2BEC_6693E230.ORA))
Service "cwhdqdev" has 1 instance(s)
  Instance "cwhdqdev", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


listener.ora File:
# listener.ora Network Configuration File: C:\Oracle\product\11.2.0\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cwhdqdev)
      (ORACLE_HOME = C:\Oracle\product\11.2.0)
      (SID_NAME = cwhdqdev)
    )
  )

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

ADR_BASE_LISTENER = C:\Oracle\product\11.2.0\log


tnsnames.ora file:
# tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

CWHDQDEV_LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
	  (SERVER = DEDICATED)
      (SERVICE_NAME = cwhdqdev)
    )
  )

INFOBLUE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = infoblue)
    )
  )


Re: Oracle 11g Ora error ORACLE not available [message #607399 is a reply to message #607398] Wed, 05 February 2014 08:54 Go to previous messageGo to next message
Dylz
Messages: 5
Registered: February 2014
Location: Cape Town
Junior Member
all the services are started on the server.

if i use the "ORACLE Enterprise manager 11g" console i get the following:

Database Instance:
Status Shutdown
Host Server2
Port 1521
SID infoblue
Oracle Home C:\Oracle\product\11.2.0

Listener:
Status Up
Host Server2
Port 1521
Name LISTENER
Oracle Home C:\Oracle\product\11.2.0
Location C:\Oracle\product\11.2.0\network\admin


Agent Connection to Instance:
Status Failed
Details ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)
Re: Oracle 11g Ora error ORACLE not available [message #607400 is a reply to message #607399] Wed, 05 February 2014 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
in tnsnames.ora file

>(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
not as above but as bellow
(ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521))
Re: Oracle 11g Ora error ORACLE not available [message #607401 is a reply to message #607400] Wed, 05 February 2014 09:09 Go to previous messageGo to next message
Dylz
Messages: 5
Registered: February 2014
Location: Cape Town
Junior Member
I have change both in the tnsnames.ora file now and i still get the same ora error

# tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

CWHDQDEV_LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521))
    )
    (CONNECT_DATA =
	  (SERVER = DEDICATED)
      (SERVICE_NAME = cwhdqdev)
    )
  )

INFOBLUE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = infoblue)
    )
  )

[Updated on: Wed, 05 February 2014 09:10]

Report message to a moderator

Re: Oracle 11g Ora error ORACLE not available [message #607402 is a reply to message #607401] Wed, 05 February 2014 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Throughout this whole thread you have NEVER actually shown exactly what you are doing which make it challenging to guess what you are doing wrong.
I can see that a listener exists & is running.
The listener knows about Instance "cwhdqdev".
So necessary items do exist but still insufficient to successfully connect by what ever you do.

sqlplus scott/tiger@CWHDQDEV_LOCAL

what results when you do like above?

Please COPY & PASTE whole terminal session; not just error message.
Re: Oracle 11g Ora error ORACLE not available [message #607413 is a reply to message #607402] Thu, 06 February 2014 00:24 Go to previous message
Dylz
Messages: 5
Registered: February 2014
Location: Cape Town
Junior Member
Well all i am trying to do is connect to the database, using: sqlplus sysdba/ sysdba

What i have noticed is that this error normally comes up when trying to install Oracle, but that is not the case here, we where running data through the Oracle Data Quality tool (OEDQ) and the process froze and the log files for OEDQ said cannot connect to database and when i tried to connect to the database with the sysdba user or another user DQ_STAGING we get this error.

if i type sqlplus scott/tiger@CWHDQDEV_LOCAL I get:
Microsoft Windows [Version 6.1.7600]
C:\Users\Administrator>sqlplus scott/tiger@CWHDQDEV_LOCAL
SQL*PLUS: Release 11.2.0.1.0 Production on Thu Feb 6 08:18:50 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12518: TNS:listener count not hand off client connection

Enter user-name:


I hope that helps?

[Updated on: Thu, 06 February 2014 00:41]

Report message to a moderator

Previous Topic: OEM Silent Install software_only
Next Topic: Enterprise manager database control blank
Goto Forum:
  


Current Time: Thu Mar 28 15:50:50 CDT 2024