Link to home
Start Free TrialLog in
Avatar of GouthamAnand
GouthamAnand

asked on

ORA-01034: ORACLE not available

Hi,
In windows server 2008, I installed Oracle 11g. We are able to connect this database properly till yesterday, but now when I try to login (in the server itself where the oracle got installed),  am getting error like
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist.

Oracle service is also in "started" mode.

Can you please help?

Thanks.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

The database isn't running.  The Service isn't the database...

From a CMD prompt:
sqlplus / as sysdba
startup

Post any error messages.
Avatar of GouthamAnand

ASKER

when I do startup got below messages

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

Thanks.
What is the value for LOCAL_LISTENER?

SQL>  show parameter LOCAL_LISTENER

Also from a CMD prompt:
lsnrctl status
SQL> show parameter LOCAL_LISTENER
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

D:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 09-JUN-2015 17:46:22

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                09-JUN-2015 15:19:42
Uptime                    0 days 2 hr. 26 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         d:\app\administrator\diag\tnslsnr\MACHINE1234\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE1234)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
From the SQL prompt:
startup nomount

If that works, try the show parameter again.

If it doesn't work:
create pfile=d:\q.ora from spfile;

Open that file in notepad and post the local_listener value.

>> (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE1234)(PORT=1522)))

Does that look correct to you?
SQL> startup nomount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_SEZDB'

create pfile='d:\q.ora' from spfile;

local_listener='LISTENER_SEZDB'

Yes. Actually I just changed the original machine name.
previously I gave 'LISTNER_ORCL'(manually changed) you can consider that also as LISTNER_SEZDB
If the listener you want is called the default, LISTENER, delete the local_listener line from the pfile and save it.

The from a SQL prompt:
startup pfile='d:\q.ora';

Then check the listener's status and make sure the database has registered with it.

ONLY WHEN everything checks out and you are good, recreate the spfile:
create spfile from pfile='d:\q.ora';
>>Yes. Actually I just changed the original machine name.

That explains why the database 'stopped working'.  

You should have posted what changed between when it was working and when it stopped working.  

It will help us get you back up and running quicker.
Sorry for confusion.
what I mean is, I manually changed just for posting my question purpose as I may  not supposed to post my company  system names.

I did not change anything in the server. But not sure why database is not responding. I think network cable got disturbed but we connected properly immediately. But database is not connecting.

But I did not why I need to remove the local_listner line from the pfile.
Because it is showing correctly my database SID(SEZDB). It did not calling the default.
Just for posting question purpose I changed (to ORCL).
Anyway when I remove and do as below

 startup pfile='d:\q.ora';

Database started.

SQL> show parameter LOCAL_LISTENER

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       strig

Here no value is displaying...

thanks.
>>But I did not why I need to remove the local_listner line from the pfile.

The startup error states the value for that parameter is incorrect.  The listener status doesn't show a listener named LISTENER_SEZDB.  The one you posted is the default name of LISTENER.

If you have a listener named LISTENER_SEZDB then the database value might have been correct and that listener isn't running.

I asked if LISTENER is the 'correct' listener for the system and if it looked correct.

>>Here no value is displaying...

Without the value, it looks for the 'default' listener which is named LISTENER.
Startup error also showed LISTNER_SEZDB only.(I changed just for posting purpose to ORCL to hide original name).

Anyway, shall I create spfile from pfile now?
as
create spfile from pfile='d:\q.ora';
>> Anyway, shall I create spfile from pfile now?

I cannot say.

If you are happy with everything and all the applications are working as they should, I think you are safe but I don't know your system so cannot say if everything is good to go.

I'm a little concerned that the only thing that "changed" was an unplugged network cable.

Since the database had LISTNER_SEZDB as the local_listener, I'm thinking that at one time you had a listener named LISTNER_SEZDB.  Maybe on a different network card, ip address or port?

If you did, then applications may no longer be able to connect to the database.
ok.
I created spfile as create spfile from pfile='d:\q.ora';

Now am able to connect in the server for all the schemas.

But not able to connect from client.(from other machine)
I am getting the error like

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Can you please suggest.
In http:#a40820084, I stated that you need to make sure the database registered with the listener...

On the database server what is the listener status?
D:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 09-JUN-2015 19:41
:13

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                09-JUN-2015 15:19:42
Uptime                    0 days 4 hr. 21 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\administrator\product\11.2.0\dbhome_1\network
\admin\listener.ora
Listener Log File         d:\app\administrator\diag\tnslsnr\MACHINE1234\list
ener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE1234)(PORT=1
522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much. This perfectly worked. Thanks a lot.