Prev: ORA-00600: internal error code, arguments: [17059]
Next: APEX in Oracle XE stopped working
From: Mathijs on 1 Nov 2006 04:57
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.
>set
...
ORACLE_SID=XE
...

The listener is running:
>lsnrctl status XE
STATUS of the LISTENER
------------------------
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:
>tnsping xe
...
OK (30 msec)

From: Charles Hooper on 1 Nov 2006 07:00
Mathijs wrote:
> 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.
> >set
> ...
> ORACLE_SID=XE
> ...
>
> The listener is running:
> >lsnrctl status XE
> STATUS of the LISTENER
> ------------------------
> 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:
> >tnsping xe
> ...
> OK (30 msec)

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.

From: Mathijs on 1 Nov 2006 07:40
01 nov 2006, (Charles Hooper):

> Check the initialization file, check the alert log.

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\ONLINELOG
\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==
From: Charles Hooper on 1 Nov 2006 08:59
Mathijs wrote:
> 01 nov 2006, (Charles Hooper):
>
> > Check the initialization file, check the alert log.
>
> 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
> 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==

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\SPFILEXE.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(a)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\SPFILEXE.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?
> 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
....

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

From: Mathijs on 1 Nov 2006 09:34
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.



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

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


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

Check:
F:\oraclexe\app\oracle\product\10.2.0\server\dbs>dir
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 the registry to make certain that the correct values are
> entered.
> The values will likely be located near:
> HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\

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

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

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

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




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

C:\>SQLPLUS /NOLOG

SQL> CONNECT SYS/MY_SECRET_PASSWORD(a)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


> 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\SPFILEXE.ORA
> Look through the output, are the values specified in the spfile
> the same as what you posted as listed in the alert file?

Checked. All the values are the same.

F:\oraclexe\app\oracle\product\10.2.0\server\dbs>type 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\admin\XE\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oraclexe\oradata\XE\control.dbf'
*.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\flash_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?
 |  Next  |  Last
Pages: 1 2 3 4
Prev: ORA-00600: internal error code, arguments: [17059]
Next: APEX in Oracle XE stopped working