Ora-27101: shared memory realm does not exist

[Former Member]

I appreciate if someone can help me with the error

ORA-27101 Shared memory realm does not exist.

I am using Oracle 10.0.2 to run IDES ECC 6.

The last thing I did before I reboot the server was running this command:

alter system set shared_pool_size=400000 scope=spfile;

(I did not type shutdown immediate and startup after the alter command)

Your assistance is appreciated. Points will be rewarded.

Thank you.

Edited by: mun choong see tho on Jul 13, 2008 8:43 PM

[stefan_koehler · Accepted solution]

Hello Mun,

at first just for your information: http://ora-27101.ora-code.com/

>> The last thing I did before I reboot the server was running this command

Which OS are you running?

Maybe your listener is not starting automatically and startdb (from sap) can not start the database. Or maybe there is another problem.. without any log files and outputs - it is hard to say.

Have you looked in the alert.log - check if the database tried to start.

> shell> lsnrctl status

> shell> sqlplus "/ as sysdba"

> SQL> startup

Regards

Stefan

[Former Member · Accepted solution]

Stefan,

Thank you for your reply.

The dispatcher started and turn green and then a while later it stopped. I saw the Oracle error when trying to connect to sql+.

My OS is Win 2003 Server.

I checked the alert_<sid>.log located at the directory

.....<sid>/saptrace/background and the last few update are as below:

Incremental checkpoint up to RBA [0xf2f.12ba.0], current log tail at RBA [0xf2f.12d9.0]

Sat Jul 12 22:38:12 2008

Incremental checkpoint up to RBA [0xf2f.137a.0], current log tail at RBA [0xf2f.1991.0]

Sat Jul 12 22:49:38 2008

Errors in file d:\oracle\ecc\saptrace\usertrace\ecc_ora_1792.trc:

ORA-00600: internal error code, arguments: [12333], [7], [5], [84], [], [], [], []

Sat Jul 12 23:08:13 2008

Incremental checkpoint up to RBA [0xf2f.2164.0], current log tail at RBA [0xf2f.2171.0]

Sat Jul 12 23:25:28 2008

ALTER SYSTEM SET shared_pool_size='400000' SCOPE=SPFILE;

Sat Jul 12 23:38:15 2008

Incremental checkpoint up to RBA [0xf2f.3453.0], current log tail at RBA [0xf2f.35b9.0]

Edited by: mun choong see tho on Jul 13, 2008 9:09 PM

Edited by: mun choong see tho on Jul 13, 2008 9:09 PM

[Former Member · Accepted solution]

I tried to start the database using Window cmd window.

c:> sqlplus

c:> connect /as sysdba

Connected to an idle instance

c:>Startup mount;

Total System Global Area

......

Redo buffer..

Ora-00205 - error in identifying control file, check alert log for more info

*********

I checked the alert log and I got this message:

ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CTL1ECC.ORA'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

The path of the control file is wrong! It was showing a different <sid> and I do not have this filename at all. The control files I have is something like this- D:\oracle\product\10.2.0\oradata\ECC\control01.ctl.

(the path is in init.ora.63202015339)

Is this the reason why I got the original error Ora-027101

'shared memory realm does not exist'?

[Former Member · Accepted solution]

I als found this in the alert log. Tha parameter before I increase the shared_pool_size has the control files:

Starting up ORACLE RDBMS Version: 10.2.0.1.0.

System parameters with non-default values:

processes = 80

sessions = 96

event = 10191 trace name context forever, level 1

sga_max_size = 675282944

shared_pool_size = 322961408

shared_pool_reserved_size= 32196526

filesystemio_options = setall

control_files = D:\ORACLE\ECC\ORIGLOGA\CNTRL\CNTLRECC.DBF, D:\ORACLE\ECC\ORIGLOGB\CNTRL\CNTRLECC.DBF, D:\ORACLE\ECC\SAPDATA1\CNTRL\CNTRLECC.DBF

control_file_record_keep_time= 30

db_block_size = 8192

db_cache_size = 322961408

compatible = 10.2.0

log_archive_dest = D:\oracle\ECC\oraarch\ECCarch

log_buffer = 2894848

log_checkpoint_interval = 0

db_files = 254

log_checkpoints_to_alert = TRUE

dml_locks = 4000

undo_management = AUTO

undo_tablespace = PSAPUNDO

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

job_queue_processes = 1

background_dump_dest = D:\ORACLE\ECC\SAPTRACE\BACKGROUND

user_dump_dest = D:\ORACLE\ECC\SAPTRACE\USERTRACE

core_dump_dest = D:\ORACLE\ECC\SAPTRACE\BACKGROUND

optimizer_features_enable= 10.2.0.1

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = ECC

open_cursors = 800

optimpeek_user_binds = FALSE

pga_aggregate_target = 429287014

workarea_size_policy = AUTO

statistics_level = typical

PSP0 started with pid=3, OS id=3824

PMON started with pid=2, OS id=3820

MMAN started with pid=4, OS id=3828

DBW0 started with pid=5, OS id=3832

LGWR started with pid=6, OS id=3836

CKPT started with pid=7, OS id=3840

SMON started with pid=8, OS id=3844

RECO started with pid=9, OS id=3848

CJQ0 started with pid=10, OS id=3852

MMON started with pid=11, OS id=3856

MMNL started with pid=12, OS id=3860

Sat Jul 12 21:37:18 2008

ALTER DATABASE MOUNT

Sat Jul 12 21:37:22 2008

Setting recovery target incarnation to 1

Sat Jul 12 21:37:23 2008

Successful mount of redo thread 1, with mount id 919879758

Sat Jul 12 21:37:23 2008

Database mounted in Exclusive Mode

Completed: ALTER DATABASE MOUNT

Sat Jul 12 21:37:23 2008

ALTER DATABASE OPEN

Sat Jul 12 21:37:32 2008

Beginning crash recovery of 1 threads

parallel recovery started with 2 processes

Sat Jul 12 21:37:47 2008

Started redo scan

Sat Jul 12 21:37:48 2008

Completed redo scan

25 redo blocks read, 5 data blocks need recovery

Sat Jul 12 21:37:49 2008

Started redo application at

Thread 1: logseq 3886, block 18849

Sat Jul 12 21:37:49 2008

Recovery of Online Redo Log: Thread 1 Group 2 Seq 3886 Reading mem 0

Mem# 0 errs 0: D:\ORACLE\ECC\ORIGLOGB\LOG_G12M1.DBF

Mem# 1 errs 0: D:\ORACLE\ECC\MIRRLOGB\LOG_G12M2.DBF

Sat Jul 12 21:37:49 2008

Completed redo application

Sat Jul 12 21:37:49 2008

Completed crash recovery at

Thread 1: logseq 3886, block 18874, scn 20668666

5 data blocks read, 5 data blocks written, 25 redo blocks read

Sat Jul 12 21:37:58 2008

Thread 1 advanced to log sequence 3887

Thread 1 opened at log sequence 3887

Current log# 3 seq# 3887 mem# 0: D:\ORACLE\ECC\ORIGLOGA\LOG_G13M1.DBF

Current log# 3 seq# 3887 mem# 1: D:\ORACLE\ECC\MIRRLOGA\LOG_G13M2.DBF

Successful open of redo thread 1

Sat Jul 12 21:38:07 2008

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sat Jul 12 21:38:07 2008

SMON: enabling cache recovery

Sat Jul 12 21:38:09 2008

Incremental checkpoint up to RBA [0xf2f.3.0], current log tail at RBA [0xf2f.3.0]

Sat Jul 12 21:38:16 2008

Successfully onlined Undo Tablespace 1.

Sat Jul 12 21:38:18 2008

SMON: enabling tx recovery

Sat Jul 12 21:38:19 2008

Database Characterset is UTF8

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

QMNC started with pid=16, OS id=2656

Sat Jul 12 21:38:37 2008

Completed: ALTER DATABASE OPEN

Sat Jul 12 22:08:12 2008

Incremental checkpoint up to RBA [0xf2f.12ba.0], current log tail at RBA [0xf2f.12d9.0]

Sat Jul 12 22:38:12 2008

Incremental checkpoint up to RBA [0xf2f.137a.0], current log tail at RBA [0xf2f.1991.0]

Sat Jul 12 22:49:38 2008

Errors in file d:\oracle\ecc\saptrace\usertrace\ecc_ora_1792.trc:

ORA-00600: internal error code, arguments: [12333], [7], [5], [84], [], [], [], []

Sat Jul 12 23:08:13 2008

Incremental checkpoint up to RBA [0xf2f.2164.0], current log tail at RBA [0xf2f.2171.0]

Sat Jul 12 23:25:28 2008

ALTER SYSTEM SET shared_pool_size='400000' SCOPE=SPFILE;

Sat Jul 12 23:38:15 2008

Incremental checkpoint up to RBA [0xf2f.3453.0], current log tail at RBA [0xf2f.35b9.0]

Dump file d:\oracle\ecc\saptrace\background\alert_ecc.log

***************************

Parameter after altering the shared_pool_size does not have the control_file (as in alert log). I checked the pfile and spfile and the parameters were there. I used the 'show parameters control_file' and it show the wrong path and wrong file: D:\oracle\product\10.2.0\db_1\database\ctl1ecc.ora

Why is the system getting the wrong path and wrong file even though the paths were correct in spfile, pfile and initecc?

Sun Jul 13 15:12:29 2008

Starting ORACLE instance (normal)

Sun Jul 13 15:12:29 2008

Specified value of sga_max_size is too small, bumping to 675282944

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 2

Autotune of undo retention is turned on.

IMODE=BR

ILAT =10

LICENSE_MAX_USERS = 0

SYS auditing is disabled

ksdpec: called for event 13740 prior to event group initialization

Starting up ORACLE RDBMS Version: 10.2.0.1.0.

System parameters with non-default values:

processes = 80

sessions = 96

event = 10191 trace name context forever, level 1

sga_max_size = 675282944

shared_pool_size = 322961408

shared_pool_reserved_size= 32196526

filesystemio_options = setall

control_file_record_keep_time= 30

db_block_size = 8192

db_cache_size = 322961408

compatible = 10.2.0

log_archive_dest = D:\oracle\ECC\oraarch\ECCarch

log_buffer = 2894848

log_checkpoint_interval = 0

db_files = 254

log_checkpoints_to_alert = TRUE

dml_locks = 4000

undo_management = AUTO

undo_tablespace = PSAPUNDO

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

job_queue_processes = 1

background_dump_dest = D:\ORACLE\ECC\SAPTRACE\BACKGROUND

user_dump_dest = D:\ORACLE\ECC\SAPTRACE\USERTRACE

core_dump_dest = D:\ORACLE\ECC\SAPTRACE\BACKGROUND

optimizer_features_enable= 10.2.0.1

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = ECC

open_cursors = 800

optimpeek_user_binds = FALSE

pga_aggregate_target = 429287014

workarea_size_policy = AUTO

statistics_level = typical

MMAN started with pid=4, OS id=2276

DBW0 started with pid=5, OS id=2588

LGWR started with pid=6, OS id=2464

CKPT started with pid=7, OS id=3752

SMON started with pid=8, OS id=3992

RECO started with pid=9, OS id=2400

CJQ0 started with pid=10, OS id=2424

MMON started with pid=11, OS id=1224

MMNL started with pid=12, OS id=1780

PSP0 started with pid=3, OS id=2484

Sun Jul 13 15:12:31 2008

Edited by: mun choong see tho on Jul 14, 2008 1:44 AM

[Former Member · Accepted solution]

I think I may have corrupted the spfile as I have make changes in the file.

I then create back the file.

Now I get error "Ora-2778 Name given for the log directory is invalid" when I used 'startup' in sql+.

I checked all the directory in the init<sid>.ora and spfile annd could not find one with a problem. Here's my spfile:

db_block_size=8192

db_file_multiblock_read_count=16

open_cursors=300

db_domain=""

db_name=ECC

background_dump_dest=D:\oracle\product\10.2.0/admin/ECC/bdump

core_dump_dest=D:\oracle\product\10.2.0/admin/ECC/cdump

user_dump_dest=D:\oracle\product\10.2.0/admin/ECC/udump

control_files=("D:\oracle\product\10.2.0\oradata\ECC\control01.ctl", "D:\oracle\product\10.2.0\oradata\ECC\control02.ctl", "D:\oracle\product\10.2.0\oradata\ECC\control03.ctl")

db_recovery_file_dest=D:\oracle\product\10.2.0/flash_recovery_area

db_recovery_file_dest_size=2147483648

job_queue_processes=10

compatible=10.2.0.1.0

processes=150

sga_target=612368384

shared_pool_size = 321965260

audit_file_dest=D:\oracle\product\10.2.0/admin/ECC/adump

remote_login_passwordfile=EXCLUSIVE

dispatchers="(PROTOCOL=TCP) (SERVICE=ECCXDB)"

pga_aggregate_target=203423744

undo_management=AUTO

undo_tablespace=UNDOTBS1

Your assistance is appreciated. Thank you.

Edited by: mun choong see tho on Jul 14, 2008 6:59 AM

[stefan_koehler · Accepted solution]

Hello Mun,

> Now I get error "Ora-2778 Name given for the log directory is invalid" when I used 'startup' in sql+.

Have you seen the wrong "/" in the directory and filenames?

Please fix it and try to start the database again. Btw. please check sapnote #830576 for the needed parameter settings.

You have also set some values that are not "standard" -> for example db_recovery_file_dest.

Regards

Stefan

[Former Member · Accepted solution]

Stefan,

Thank you for taking the time to help. After working on it for the whole of Sunday and early morning Monday I successfully fixed the problem.

The original error ORA-27101 was that the system cannot find the control_file. The spfile was messed up when I edited it. (yeah, I am not a database person, just a functional guy ). As a result the database cannot start as the system was looking for a control_file with the wrong path and SID.

Then I educated myself about pfile and spfile. Although it is possible to correct the path in the spfile but since my file was corrupted I planned to recover it.

So I tried to recover the spfile from the pfile that Oracle created during installation. I used the CREATE command to recover the spfile from pfile. However the path for one of the dump parameter was showing a folder that was not yet created in my hard drive. So this was I why received the oracle error ORA-2778 - "Name given for the log directory is invalid".

Then I got another error ORA-2194: indicates a "syntax error" within the event statement.

This was related to the control_file (in my case) where the name of a file shown in the parameter was a little different than the one created in my drive (..cntrl01.dbf in parameter but ..cntlr01.dbf in my hard drive). This was fixed after I renamed the file in my drive.

After all the errors were fixed I can do 'startup mount' with no problem. The dispatcher in the MMC is now working fine. The command in sql+ "SHOW parameter control_file" now shows the correct path.

Lesson learnt: 1) NEVER changed the spfile with an editor. Only make changes to the spfile through sql+

or through the pfile.

2) Always backup the pfile (init<sid>.ora) and the spfile (spfile<sid?.ora) especially before

changing the parameter.

One question: Is it neccessary to have two spfiles? I have one spfile in the %Oracle_Home%/dbs folder

and another one in the %Oracle_Home%/database folder. The two spfiles also had

control_file parameters but with different names and location.

The spfile in the 'database' folder was the one giving the problem. The database folder is

also where the initi<sid>.ora file is located.

Based on what I learnt am I right to say that the system first look at the spfile in the dbs

folder and then proceed to the spfile in the database folder and

finally to the pfile init<sid>.ora .

Thanks again and I will reward points for taking the time.

[fidel_vales · Accepted solution]

Hi,

First, congratulations for solving the issue

Second, thanks for sharing the solution

Third, nice "lessons"

That is a typical error (that happens even to "DBA" people, even when it is documented)

> One question: Is it necessary to have two spfiles? I have one spfile in the %Oracle_Home%/dbs folder and another one in the %Oracle_Home%/database folder. The two spfiles also had control_file parameters but with different names and location.

If you have windows (as it seems) Oracle does not uses %Oracle_Home%/dbs but %Oracle_Home%/database.

In UNIX the directory used is the dbs ($ORACLE_HOME/dbs)

This is one of the differences between UNIX and Windows

Unless you specify the pfile on startup.

[Former Member · Accepted solution]

Thank you everyone for your answers. Points have been awarded. I will change the thread to status answered.

[Former Member]

Solution posted in detail in the thread.