OraFAQ Forum: Client Tools » ORACLE not available; Shared memory realm does not exist

Home » Other » Client Tools » ORACLE not available; Shared memory realm does not exist (SQL Plus 10.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORACLE not available; Shared memory realm does not exist [message #460578]

Sat, 12 June 2010 14:47

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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460580 is a reply to message #460578]

Sat, 12 June 2010 15:14

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Database 2 Day DBA
Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows

Regards
Michel

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460581 is a reply to message #460578]

Sat, 12 June 2010 15:17

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460582 is a reply to message #460578]

Sat, 12 June 2010 15:30

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460583 is a reply to message #460582]

Sat, 12 June 2010 15:39

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]

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460584 is a reply to message #460583]

Sat, 12 June 2010 15:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

from command window issue the following command

SET

COPY the results & PASTE back here

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460585 is a reply to message #460584]

Sat, 12 June 2010 15:59

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?

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460586 is a reply to message #460585]

Sat, 12 June 2010 16:01

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460587 is a reply to message #460586]

Sat, 12 June 2010 16:03

bhushan5
Messages: 11
Registered: June 2010
Location: Pittsburgh

Junior Member

Totally!

Smile

Here's the response-
"SP2-0545: SET command requires an argument."

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460588 is a reply to message #460587]

Sat, 12 June 2010 16:05

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460589 is a reply to message #460588]

Sat, 12 June 2010 16:07

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

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460590 is a reply to message #460589]

Sat, 12 June 2010 16:16

BlackSwan
Messages: 26766
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

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>

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460593 is a reply to message #460592]

Sat, 12 June 2010 16:32

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460594 is a reply to message #460593]

Sat, 12 June 2010 16:55

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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460656 is a reply to message #460593]

Sun, 13 June 2010 23:32

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?

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460657 is a reply to message #460656]

Sun, 13 June 2010 23:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can we mention the physical path?
yes

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460669 is a reply to message #460657]

Mon, 14 June 2010 01:05

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?

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460777 is a reply to message #460669]

Mon, 14 June 2010 13:53

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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #460780 is a reply to message #460777]

Mon, 14 June 2010 14:50

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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648205 is a reply to message #460578]

Thu, 18 February 2016 08:59

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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648207 is a reply to message #648205]

Thu, 18 February 2016 09:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> CREATE SPFILE FROM PFILE;

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648216 is a reply to message #648205]

Thu, 18 February 2016 10:09

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648231 is a reply to message #648216]

Fri, 19 February 2016 01:27

manjuds93
Messages: 5
Registered: February 2016
Location: Bangalore

Junior Member

where will i find pfile?

Is it in Database folder?

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648232 is a reply to message #648231]

Fri, 19 February 2016 01:31

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

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?

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648234 is a reply to message #648233]

Fri, 19 February 2016 01:40

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: ORACLE not available; Shared memory realm does not exist [message #648239 is a reply to message #648238]

Fri, 19 February 2016 02:46

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 01:14:17 CST 2024