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.
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.
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.
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
Also from a CMD prompt:
lsnrctl status
ASKER
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=(PRO TOCOL=IPC) (KEY=EXTPR OC1522)))
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\produ ct\11.2.0\ dbhome_1\n etwork\adm in\listene r.ora
Listener Log File d:\app\administrator\diag\ tnslsnr\MA CHINE1234\ listener\a lert\log.x ml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1522 ipc)))
(DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=MACH INE1234)(P ORT=1522)) )
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
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=(PRO
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\produ
Listener Log File d:\app\administrator\diag\
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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=(PRO TOCOL=tcp) (HOST=MACH INE1234)(P ORT=1522)) )
Does that look correct to you?
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=(PRO
Does that look correct to you?
ASKER
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_S EZDB'
Yes. Actually I just changed the original machine name.
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_S
Yes. Actually I just changed the original machine name.
ASKER
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';
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.
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.
ASKER
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).
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).
ASKER
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.
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.
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.
ASKER
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?
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.
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.
ASKER
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.
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?
On the database server what is the listener status?
ASKER
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=(PRO TOCOL=IPC) (KEY=EXTPR OC1522)))
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\produ ct\11.2.0\ dbhome_1\n etwork
\admin\listener.ora
Listener Log File d:\app\administrator\diag\ tnslsnr\MA CHINE1234\ list
ener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1522 ipc)))
(DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=MACH INE1234)(P ORT=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
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=(PRO
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\produ
\admin\listener.ora
Listener Log File d:\app\administrator\diag\
ener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. This perfectly worked. Thanks a lot.
From a CMD prompt:
sqlplus / as sysdba
startup
Post any error messages.