Home » SQL & PL/SQL » Client Tools » ORACLE not available; Shared memory realm does not exist (SQL Plus 10.2.0.1.0)
ORACLE not available; Shared memory realm does not exist [message #460578] Sat, 12 June 2010 14:47 Go to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Here's the background-
ORACLE was working perfectly on Windows 7 since Jan 2010. A couple of days ago I ran a procedure which was to update a table with 1000 columns. The procedure worked fine till it created a few thousand records and then it slowed down terribly. I closed the SQL Plus command line tool (CTRL C) and tried to restart and then tried to logon to get the following errors-
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Then I used the Start Database tool (net start OracleTNSListener), but it showed "System 5 error". So after researching online I created Admin profile for WINDOWS 7 and logged in as an administrator. In the INIT.ORA file I increasd the SGA_TARGET to 5G and PGA to 3G.
After doing that the above service is running but I'm still getting the ORA-01034 and ORA-27101.

Using cmd prompt in Windows and entering commands sqlplus -> Username: " / as sysdba" gives an insufficient privileges error.

Please let me know if you can help any way.

Thanks.
Re: ORACLE not available; Shared memory realm does not exist [message #460580 is a reply to message #460578] Sat, 12 June 2010 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database 2 Day DBA
Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows

Regards
Michel

Re: ORACLE not available; Shared memory realm does not exist [message #460581 is a reply to message #460578] Sat, 12 June 2010 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>Using cmd prompt in Windows and entering commands sqlplus -> Username: " / as sysdba" gives an insufficient privileges error.

Frequently occurs when logged on OS as user other than the owner of the Oracle software
Re: ORACLE not available; Shared memory realm does not exist [message #460582 is a reply to message #460578] Sat, 12 June 2010 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>and logged in as an administrator. In the INIT.ORA file I increasd the SGA_TARGET to 5G and PGA to 3G.

This will be a problem on a 32-bit system; which can address only 4GB by default.
Re: ORACLE not available; Shared memory realm does not exist [message #460583 is a reply to message #460582] Sat, 12 June 2010 15:39 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Thanks BlackSwan-
I used your suggestion related to "/ as sysdba". I can log on under my user name. Now I'm trying to handle the following error-
ORA-44412: XE edition memory parameter invalid or not specified
Currently referring the following post for this error, but first would try reducing the SGA size as you said.[threadID=427479 on Oracle Forums]

Re: ORACLE not available; Shared memory realm does not exist [message #460584 is a reply to message #460583] Sat, 12 June 2010 15:53 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
from command window issue the following command

SET

COPY the results & PASTE back here
Re: ORACLE not available; Shared memory realm does not exist [message #460585 is a reply to message #460584] Sat, 12 June 2010 15:59 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
OK after reducing e SGA and PGA to 2 and 1 gig resp., didn't help.

So far I'm able to log in "/ as sysdba" under my user ID with the response "Connected to an idle instance." but "startup" gives foll. response "ORA-44412: XE edition memory parameter invalid or not specified"

What should be the argument for SET command?
Re: ORACLE not available; Shared memory realm does not exist [message #460586 is a reply to message #460585] Sat, 12 June 2010 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>What should be the argument for SET command?

There is NO argument.
Please just do as directed & trust me that I know what I am doing.
Re: ORACLE not available; Shared memory realm does not exist [message #460587 is a reply to message #460586] Sat, 12 June 2010 16:03 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Totally! Smile
Here's the response-
"SP2-0545: SET command requires an argument."
Re: ORACLE not available; Shared memory realm does not exist [message #460588 is a reply to message #460587] Sat, 12 June 2010 16:05 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>"SP2-0545: SET command requires an argument."
I did NOT say anything about sqlplus.

At Windows command line issue the OS command
SET
Re: ORACLE not available; Shared memory realm does not exist [message #460589 is a reply to message #460588] Sat, 12 June 2010 16:07 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Gotcha-

ACPath=C:\Program Files\Lenovo\Access Connections\
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\Bhushan\AppData\Roaming
asl.log=Destination=file;OnFirstLog=command,environment
CLASSPATH=.;C:\Program Files\Java\jre6\lib\ext\QTJava.zip
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=BHUSHAN-PC
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\Bhushan
LOCALAPPDATA=C:\Users\Bhushan\AppData\Local
LOGONSERVER=\\BHUSHAN-PC
MINITAB_LICENSE_FILE=C:\Program Files\Minitab 15\
NUMBER_OF_PROCESSORS=2
OS=Windows_NT
Path=C:\oraclexe\app\oracle\product\10.2.0\server\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\Win dowsPow
erShell\v1.0\;C:\Program Files\Lenovo\Access Connections\;C:\Program Files\Common Files\DivX Shared\;C:\Program Files\QuickTime\QTSystem\;C:
\Program Files\Common Files\Lenovo;C:\Program Files\SSH Communications Security\SSH Secure Shell
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 23 Stepping 6, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=1706
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
QTJAVA=C:\Program Files\Java\jre6\lib\ext\QTJava.zip
RR=C:\Program Files\Lenovo\Rescue and Recovery
SESSIONNAME=Console
SWSHARE=C:\SWSHARE
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\Bhushan\AppData\Local\Temp
TMP=C:\Users\Bhushan\AppData\Local\Temp
TVT=C:\Program Files\Lenovo
TVTCOMMON=C:\Program Files\Common Files\Lenovo
TVTPYDIR=C:\Program Files\Common Files\Lenovo\Python24
USERDOMAIN=Bhushan-PC
USERNAME=Bhushan
USERPROFILE=C:\Users\Bhushan
windir=C:\Windows
Re: ORACLE not available; Shared memory realm does not exist [message #460590 is a reply to message #460589] Sat, 12 June 2010 16:16 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>Path=C:\oraclexe\app\oracle\product\10.2.0\server\bin;C:\Windows\system32;

32-bit Windows!

open a Windows command window

COPY the lines below

cd C:\oraclexe\app\oracle\product\10.2.0\server\dbs
sqlplus
/ as sysdba
create pfile from spfile;
exit
dir /Q

PASTE lines above into command window
COPY commands & results then PASTE all back here

there should be a file names "initXE.ora" in this folder now.
manually edit it to correct bad parameter
find file "alert_XE.log" file & inspect it contents.
It will report the error.
After you change "initXE.ora, then do as follows:

sqlplus
/ as sysdba
startup pfile='initXE.ora'
exit

This should start the DB

[Updated on: Sat, 12 June 2010 16:22]

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460592 is a reply to message #460590] Sat, 12 June 2010 16:26 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
The pfile was not created, foll. is the transcript.

COMMANDS-
sqlplus
/ as sysdba

RESPONSE-
Connected to an idle instance.

COMMAND-
create pfile from spfile;

RESPONSE-
ERROR at line 1:
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

COMMAND-
exit
RESPONSE-
Disconnected

COMMAND-
C:\oraclexe\app\oracle\product\10.2.0\server\dbs>dir /Q

RESPONSE-
Volume in drive C is SW_Preload
Volume Serial Number is C859-7283

Directory of C:\oraclexe\app\oracle\product\10.2.0\server\dbs

12/15/2009 02:42 PM <DIR> BUILTIN\Administrators .
12/15/2009 02:42 PM <DIR> NT AUTHORITY\SYSTEM ..
06/11/2010 12:59 AM 2,560 BUILTIN\Administrators SPFILEXE.ORA
1 File(s) 2,560 bytes
2 Dir(s) 27,559,718,912 bytes free

C:\oraclexe\app\oracle\product\10.2.0\server\dbs>
Re: ORACLE not available; Shared memory realm does not exist [message #460593 is a reply to message #460592] Sat, 12 June 2010 16:32 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
pen a Windows command window

COPY the lines below

cd C:\oraclexe\app\oracle\product\10.2.0\server\dbs
SET ORACLE_SID=XE
SET ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server
sqlplus
/ as sysdba
create pfile from spfile;
exit
dir /Q

PASTE lines above into command window
COPY commands & results then PASTE all back here

the follow steps in previous post
Re: ORACLE not available; Shared memory realm does not exist [message #460594 is a reply to message #460593] Sat, 12 June 2010 16:55 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Still the same Error message for CREATE statement. Have 'PM'ed you the alert_xe.log file too that I'm currently looking at.
Re: ORACLE not available; Shared memory realm does not exist [message #460656 is a reply to message #460593] Sun, 13 June 2010 23:32 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Hi Blackswan-
Still unable to execute the "create pfile from spfile;"
Following is the error-
*
ERROR at line 1:
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Can we mention the physical path?
Re: ORACLE not available; Shared memory realm does not exist [message #460657 is a reply to message #460656] Sun, 13 June 2010 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>Can we mention the physical path?
yes
Re: ORACLE not available; Shared memory realm does not exist [message #460669 is a reply to message #460657] Mon, 14 June 2010 01:05 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
OK, I was wrapping my head around what we are doing
(http://www.dba-oracle.com/concepts/pfile_spfile.htm)

Since, the previous error was to unable to open the file I tried putting a dummy file SPFILEXE.ORA in the following path
"cd C:\oraclexe\app\oracle\product\10.2.0\server\DATABASE"
Then ran the foll. statement
"create pfile from spfile"

And it creates the file! It seems, for the spfile, its looking under \database instead of \dbs to execute this statement. Am I missing something?
Re: ORACLE not available; Shared memory realm does not exist [message #460777 is a reply to message #460669] Mon, 14 June 2010 13:53 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Some respite-
Following is the output after giving the physical location of the files:

Volume in drive C is SW_Preload
Volume Serial Number is C859-7283

Directory of C:\oraclexe\app\oracle\product\10.2.0\server\dbs

06/14/2010 02:38 PM <DIR> BUILTIN\Administrators .
06/14/2010 02:38 PM <DIR> NT AUTHORITY\SYSTEM ..
06/14/2010 02:38 PM 862 BUILTIN\Administrators mypfile.ora
06/14/2010 12:25 AM 2,560 BUILTIN\Administrators SPFILEXE.ORA
2 File(s) 3,422 bytes
2 Dir(s) 26,902,044,672 bytes free


Should we manually change the myfile.ora for sga_target as I think that is a bad param.? Or is there anything particular when you said the following:

> find file "alert_XE.log" file & inspect it contents.
> It will report the error.

Re: ORACLE not available; Shared memory realm does not exist [message #460780 is a reply to message #460777] Mon, 14 June 2010 14:50 Go to previous messageGo to next message
bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh
Junior Member
Got it! Just so anyone else facing the same problem finds this-
The sum of PGA and SGA shouldn't be more than 1G (and mention the params in bytes)

ORACLE instance started.

Total System Global Area 620756992 bytes
Fixed Size 1288940 bytes
Variable Size 167773460 bytes
Database Buffers 448790528 bytes
Redo Buffers 2904064 bytes
Database mounted.
Database opened.

Thank you BlackSwan.
Re: ORACLE not available; Shared memory realm does not exist [message #648205 is a reply to message #460578] Thu, 18 February 2016 08:59 Go to previous messageGo to next message
manjuds93
Messages: 5
Registered: February 2016
Location: Bangalore
Junior Member
Even I'm facing same issue kindly help how to fix it.

SPfile got modified someone and database is not getting up.

unable to start spfile from sql*plus also.

One more doubt: if spfile corrupt how can we restore? if we recreate spfile whether existing database will connect.
Re: ORACLE not available; Shared memory realm does not exist [message #648207 is a reply to message #648205] Thu, 18 February 2016 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
SQL> CREATE SPFILE FROM PFILE;
Re: ORACLE not available; Shared memory realm does not exist [message #648216 is a reply to message #648205] Thu, 18 February 2016 10:09 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
How did your spfile become "corrupt"? Did "someone" not realize it is a binary file, and tried to modify it directly with a text editor?

Every time your db starts, it logs all of the init parms with non-default values in the alert log. you can use these messages to construct a new pfile, then create the spfile from that.
Re: ORACLE not available; Shared memory realm does not exist [message #648231 is a reply to message #648216] Fri, 19 February 2016 01:27 Go to previous messageGo to next message
manjuds93
Messages: 5
Registered: February 2016
Location: Bangalore
Junior Member
where will i find pfile?

Is it in Database folder?
Re: ORACLE not available; Shared memory realm does not exist [message #648232 is a reply to message #648231] Fri, 19 February 2016 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

pfile can be anywhere.
Default location is $ORACLE_HOME/dbs or %ORACLE_HOME%\database.

[Updated on: Fri, 19 February 2016 01:31]

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648233 is a reply to message #648232] Fri, 19 February 2016 01:37 Go to previous messageGo to next message
manjuds93
Messages: 5
Registered: February 2016
Location: Bangalore
Junior Member
pfile is in "C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora"
spfile is in "C:\oraclexe\app\oracle\product\10.2.0\server\dbs\SPFILEXE.ORA"

initXE.ora directing to spfile "SPFILE='C:\oraclexe\app\oracle\product\10.2.0\server\dbs/spfileXE.ora'
"

Can we access existing database if we create spfile and pfile?
Re: ORACLE not available; Shared memory realm does not exist [message #648234 is a reply to message #648233] Fri, 19 February 2016 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the problem is only in the parameter file, yes.

[Updated on: Fri, 19 February 2016 01:41]

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648238 is a reply to message #648234] Fri, 19 February 2016 02:34 Go to previous messageGo to next message
manjuds93
Messages: 5
Registered: February 2016
Location: Bangalore
Junior Member
Client side Error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
Server side Error:
SQL> startup
ORA-44412: XE edition memory parameter invalid or not specified.

how do I start my database? I tried to start spfile using below command,
SQL> start spfile=C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora
SP2-0310: unable to open file "spfile=C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora"

I got this error.
Re: ORACLE not available; Shared memory realm does not exist [message #648239 is a reply to message #648238] Fri, 19 February 2016 02:46 Go to previous message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how do I start my database?


You have to first fix the error in your parameter file:

Quote:
ORA-44412: XE edition memory parameter invalid or not specified.


Quote:
SQL> start spfile=C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora
SP2-0310: unable to open file "spfile=C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora"


1/ If this is a pfile (init.ora) then it is not a spfile
2/ Does the file exist?
dir C:\oraclexe\app\oracle\product\10.2.0\server\database
type C:\oraclexe\app\oracle\product\10.2.0\server\database\initXE.ora
dir C:\oraclexe\app\oracle\product\10.2.0\server\dbs


Before, Please read How to use [code] tags and make your code easier to read.

[Updated on: Fri, 19 February 2016 02:47]

Report message to a moderator

Previous Topic: ORA-44412: XE edition memory parameter invalid or not specified
Next Topic: set markup html on
Goto Forum:
  


Current Time: Mon Oct 22 01:47:19 CDT 2018