Hi,

I installed OracleExpress on a windows 2003 server. Right after the
installation, I was able to create and use a database. Then, after the
first reboot, the database didn't start automatically, but after
SQL> connect /
SQL> startup
things worked fine again. Now, after a third reboot, this trick doesn't
work anymore:
SQL> connect /
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Q: How can I fix this? (there is already data in my database that I
really would like to use) And that Oracle behaves differently after the
second and the third reboot?

Thanks in advance for your help. Below is the result of a few obvious
things that I checked before posting this message.

M.


I've checked the ORACLE_SID in the environment and it is set to XE.
ORACLE_SID=XE
...

The listener is running:
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit W..: Ver.. 10.2.0.1.0 - Production
Start Date 01-NOV-2006 10:29:36
Uptime 0 days 0 hr. 1 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE

and tnsping works:
OK (30 msec)

Mathijs wrote:
Problems that I have seen which cause the problem (check the alert
log):
init.ora or spfile contains LOG_ARCHIVE_START, which is depreciated
init.ora or spfile contains DB_RECOVERY_FILE_DEST, string cannot be
translated

The init.ora or spfile may specify a folder that does not exist, or
other file which does not exist. You may have attempted to edit the
spfile using Notepad - this will corrupt the binary file.

Check the initialization file, check the alert log.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

01 nov 2006, (Charles Hooper):

The init.ora only contains valid paths (the installation was done 'out of
the box', and I tried to stick to the defaults... I did not edit the
config files manually). The alert_xe.log file contains no data after the
third reboot (1 nov). I don't know what the look for in the alert_xe.log.
I've copied the content below, starting from thursday 26th of october.

Thanks in advance,
M.


[alert_xe.log]
Thu Oct 26 11:20:04 2006
db_recovery_file_dest_size of 10240 MB is 0.98% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Dump file f:\oraclexe\app\oracle\admin\xe\bdump\alert_xe.log
Mon Oct 30 11:35:02 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 1
CPU : 1 - type 586, 1 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:957M/1271M, Ph+PgF:1514M/1697M, VA:1933M/2047M
Mon Oct 30 11:35:02 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as
USE_DB_RECOVERY_FILE_DEST
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:
sessions = 49
__shared_pool_size = 100663296
__large_pool_size = 12582912
__java_pool_size = 4194304
__streams_pool_size = 0
spfile = F:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER
\DBS\SPFILEXE.ORA
sga_target = 348127232
control_files = F:\ORACLEXE\ORADATA\XE\CONTROL.DBF
__db_cache_size = 226492416
compatible = 10.2.0.1.0
db_recovery_file_dest = F:\oraclexe\app\oracle\flash_recovery_area
db_recovery_file_dest_size= 10737418240
undo_management = AUTO
undo_tablespace = UNDO
remote_login_passwordfile= EXCLUSIVE
dispatchers = (PROTOCOL=TCP) (SERVICE=XEXDB)
shared_servers = 4
job_queue_processes = 4
audit_file_dest = F:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP
background_dump_dest = F:\ORACLEXE\APP\ORACLE\ADMIN\XE\BDUMP
user_dump_dest = F:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP
core_dump_dest = F:\ORACLEXE\APP\ORACLE\ADMIN\XE\CDUMP
db_name = XE
open_cursors = 300
os_authent_prefix =
pga_aggregate_target = 115343360
PSP0 started with pid=3, OS id=3044
MMAN started with pid=4, OS id=2540
PMON started with pid=2, OS id=3400
DBW0 started with pid=5, OS id=2140
LGWR started with pid=6, OS id=3816
CKPT started with pid=7, OS id=972
SMON started with pid=8, OS id=2532
RECO started with pid=9, OS id=1904
CJQ0 started with pid=10, OS id=2668
MMON started with pid=11, OS id=3640
MMNL started with pid=12, OS id=1948
Mon Oct 30 11:35:07 2006
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)
(PROTOCOL=TCP))'...
starting up 4 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
Mon Oct 30 11:35:13 2006
ALTER DATABASE MOUNT
Mon Oct 30 11:35:17 2006
Setting recovery target incarnation to 2
Mon Oct 30 11:35:17 2006
Successful mount of redo thread 1, with mount id 2486284977
Mon Oct 30 11:35:17 2006
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Mon Oct 30 11:35:18 2006
ALTER DATABASE OPEN
Mon Oct 30 11:35:19 2006
Beginning crash recovery of 1 threads
Mon Oct 30 11:35:19 2006
Started redo scan
Mon Oct 30 11:35:19 2006
Completed redo scan
1132 redo blocks read, 278 data blocks need recovery
Mon Oct 30 11:35:19 2006
Started redo application at
Thread 1: logseq 9, block 73566
Mon Oct 30 11:35:20 2006
Recovery of Online Redo Log: Thread 1 Group 2 Seq 9 Reading mem 0
Mem# 0 errs 0: F:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLI NELOG
\O1_MF_2_2LSS2S5V_.LOG
Mon Oct 30 11:35:22 2006
Completed redo application
Mon Oct 30 11:35:22 2006
Completed crash recovery at
Thread 1: logseq 9, block 74698, scn 555729
278 data blocks read, 278 data blocks written, 1132 redo blocks read
Mon Oct 30 11:35:23 2006
Thread 1 advanced to log sequence 10
Thread 1 opened at log sequence 10
Current log# 1 seq# 10 mem# 0: F:\ORACLEXE\APP\ORACLE
\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_2LSS2QJ3 _.LOG
Successful open of redo thread 1
Mon Oct 30 11:35:23 2006
SMON: enabling cache recovery
Mon Oct 30 11:35:28 2006
Successfully onlined Undo Tablespace 1.
Mon Oct 30 11:35:28 2006
SMON: enabling tx recovery
Mon Oct 30 11:35:28 2006
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster
replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=3652
Mon Oct 30 11:35:43 2006
Completed: ALTER DATABASE OPEN
Mon Oct 30 11:35:49 2006
db_recovery_file_dest_size of 10240 MB is 0.98% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Oct 30 11:35:58 2006
Memory Notification: Library Cache Object loaded into SGA
Heap size 2208K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==

Mathijs wrote:
I don't have access to XE here, so the commands might be a little
different. It could be that the PATH statement, or one of the other
environment variables is not set correctly, the service is not set to
auto-start, or the initialization files can't be found or are corrupt.

As a starting point, open a command prompt and enter the following:
C:\> PATH

The path should include the Oracle home bin folder, which might be:
F:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\db_1\bin

Then, make certain that the spfile is in the expected location:
C:\> DIR F:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DBS\S PFILEXE.ORA

Check the registry to make certain that the correct values are entered.
The values will likely be located near:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\

Verify that the Oracle related services are set to Automatic startup
and that their status is Started.

Try to connect to Oracle as the SYS user, from a command prompt:
C:\> SQLPLUS /NOLOG
SQL> CONNECT SYS/MY_SECRET_PASSWORD@XE AS SYSDBA
SQL> STARTUP

If the XE database instance fails to start, take a look inside the
spfile, again from a command prompt:
C:\> TYPE F:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DBS\S PFILEXE.ORA

The file will contain binary characters - do not be too concerned with
that. Look through the output, are the values specified in the spfile
the same as what you posted as listed in the alert file?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

01 nov 2006, (Charles Hooper):

Thanks for your effort to help me. I did everything you asked me to do,
but no oddities (except that I can't use Oracle), all seems to be okay
(as I said, it's an out-of-the-box installation where I tried to stick
with the defaults as much as possible and didnt edit any files
manually... And I did manage to use the database twice).

Cheers,
M.



Check:
C:\>path
PATH=F:\oraclexe\app\oracle\product\10.2.0\server\ bin;F:\WINDOWS
\system32;
...


Check:
F:\oraclexe\app\oracle\product\10.2.0\server\dbs>d ir
Directory of F:\oraclexe\app\oracle\product\10.2.0\server\dbs

11-10-2006 13:33 <DIR> .
11-10-2006 13:33 <DIR> ..
30-10-2006 11:35 2.560 SPFILEXE.ORA
1 File(s) 2.560 bytes
2 Dir(s) 6.033.031.168 bytes free


Check (I think... How can I know what the right values are?):

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE]
"StmtCacheSize"="0"
"NLS_LANG"="DUTCH_THE NETHERLANDS.WE8MSWIN1252"
"ORACLE_HOME_NAME"="XE"
"ORACLE_BASE"="F:\\oraclexe\\"
"VERSION"="10.2.0.1.0"
"ORACLE_HOME_KEY"="SOFTWARE\\ORACLE\\KEY_XE"
"ORAMTS_CP_TRACE_LEVEL"="0"
"Type"=dword:00000057
"ORACLE_SID"="XE"
"ORACLE_HOME"="F:\\oraclexe\\app\\oracle\\product\ \10.2.0
\\server"
"ORAMTS_CP_TRACE_DIR"="F:\\oraclexe\\app\\oracle\\ product
\\10.2.0\\server\\oramts\\trace"
"StatementCacheSize"="0"
"OLEDB"="F:\\oraclexe\\app\\oracle\\product\\10.2. 0\\server
\\oledb\\mesg"
"ORA_XE_AUTOSTART"=TRUE
"ORA_XE_SHUTDOWN"=TRUE
"ORA_XE_SHUTDOWNTYPE"=immedeate
"ORA_XE_SHUTDOWN_TIMEOUT"=90

Check:
OracleServiceXE - started, automatic startup
OracleXETNSListener - started, automatic startup

OracleXEClrAgent - Stopped, manual startup
OracleXETNSListenerCLRExtProc, manual startup
OracleXETNSListenerEXTPROC_FOR_XE, manual startup




C:\>SQLPLUS /NOLOG

SQL> CONNECT SYS/MY_SECRET_PASSWORD@XE AS SYSDBA
ERROR:
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor

Note that "SQL> CONNECT /" gave the following error:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


Checked. All the values are the same.

F:\oraclexe\app\oracle\product\10.2.0\server\dbs>t ype SPFILEXE.ORA
? ?
?
Z? ·l?$




xe.__db_cache_size=226492416
xe.__java_pool_size=4194304
xe.__large_pool_size=12582912
xe.__shared_pool_size=100663296
xe.__streams_pool_size=0
*.audit_file_dest='F:\oraclexe\app\oracle\admin\XE \adump'
*.background_dump_dest='F:\oraclexe\app\oracle\adm in\XE\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oraclexe\oradata\XE\control.db f'
*.core_dump_dest='F:\oraclexe\app\oracle\admin\XE\ cdump'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='F:\oraclexe\app\oracle\fl ash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=110M
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.sga_target=330M
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDO'
*.user_dump_dest='F:\oraclexe\app\oracle\admin\XE\ udump'



Euh.. now what?

A few common problems with XE installations:

1) If XE was installed by a Domain Administrator, XE may not install
correctly. The actual problems vary depending on the Domain
Administrator's real capabilities and range from complete failure (while
appearing to install, based on the setup response) to 'crippled install
with various problems'.

Solution: install as a local XP administrator.

2) If XE was installed on a machine using DHCP, and loopback adapter was
not used, XE may install correctly but fail after the IP expired and was
not renewed to the same adddress. This may occur after one or more
reboots. The problems are usually isolated to the listener interaction,
as the listener.ora indicates the host at XE install time and that may
change. One interesting effect is that local SQLPlus logons on the local
machine work (bequeath adapter) whereas remote accesses 'can not display
the database home page' - Oracle's new HTTP listener technique uses the
database listener.

Solution: install with a loopback adapter, and/or keep listener.ora up to
date

3) If XE was installed on the a machine that previously hosted an older
version of Oracle, interesting failures (usually during install) will be
observed due to conflicting pieces in the registry and/or stray DLLs.

Solution: clean the machine thoroughly and reinstall

4) If firewalls are used, they may block ports 1521 and/or 8080 (the
default APEX port). On Linux, these may be inadvertently activated using
SELinux. Symptoms are often similar to listener.ora failures

Solution: properly configure firewalls and SELinux, or disable these
utilities

5) On XP, a number of issues have seen seen when installing on drives
other than C: which seem to relate to 'directory permission inheritance'
on other drives. These may be simply local policies at the site of the
people installing, but are troublesome. The symptoms are often apparent
install, but no database filess or alert_xe.log

Solution: ensure installing user and Oracle really have permission to
create directories and files on the drive



These, and a lot more, are discussed in the Oracle Discussion Forum
specific to XE. To access that, access the OTN discussion forums, sign in
and look for the XE forum. If it does not show up, you need to register -
search for 'register XE' in the Database-General forum for steps.

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

01 nov 2006, (HansF):

Crippled install with various problems, you say? Sounds exactly like my
problem.

"Forum registration is possible only by downloading and installing Oracle
Database XE. After installation, click on the "Registration" link from
the Database homepage."
- http://www.oracle.com/technology/pro.../xe/forum.html

I can't access the database homepage... sigh...

Mathijs schreef:

I suspect this has something to do with it:

However, I would not know where to start resolving this...
Someone else, perhaps?

Any more messages after this line?

01 nov 2006, (Frank van Bortel):

No, that is the very alst line of alert_xe.log.

Frank van Bortel wrote:
I believe that "Heap size 2208K exceeds notification threshold (2048K)"
just indicates that a large object is being read into the SGA. Each
object loaded into the SGA requires a continuous range of memory.
Loading and unloading of large objects into the SGA can cause lot of
little available memory areas, that may be too small for a slightly
larger object to use, thus the instance may reach the SGA_MAX size
quicker than expected. This is just a polite warning that a large
(2.2MB) object is being loaded.

I almost tripped over that warning too.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.