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.
GouthamAnandAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The database isn't running.  The Service isn't the database...

From a CMD prompt:
sqlplus / as sysdba
startup

Post any error messages.
GouthamAnandAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
What is the value for LOCAL_LISTENER?

SQL>  show parameter LOCAL_LISTENER

Also from a CMD prompt:
lsnrctl status
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GouthamAnandAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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?
GouthamAnandAuthor Commented:
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.
GouthamAnandAuthor Commented:
previously I gave 'LISTNER_ORCL'(manually changed) you can consider that also as LISTNER_SEZDB
slightwv (䄆 Netminder) Commented:
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';
slightwv (䄆 Netminder) Commented:
>>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.
GouthamAnandAuthor Commented:
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).
GouthamAnandAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>>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.
GouthamAnandAuthor Commented:
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';
slightwv (䄆 Netminder) Commented:
>> 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.
GouthamAnandAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
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?
GouthamAnandAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Notice there is no SEZDB registered?  That is the problem.

If should try to look for a default listener.

Let's try to go back to what was previously set:

check the file:
D:\app\administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

See if it has an entry for LISTENER_SEZDB.  

That is what was looked for by the value for local_listener that I had you remove because there was a problem with the value.

If it does, are the values correct for the listener?

If not, you'll need to add one:
LISTENER_SEZDB =
   (ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE1234)(PORT=1522))

Add the local_listener value back:
alter system set local_listener=LISTENER_SEZDB;
alter system register;

Then check the listener status.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GouthamAnandAuthor Commented:
Thank you very much. This perfectly worked. Thanks a lot.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.