I tried to connect to my database and got Connected to an idle instance:
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 22 19:07:34 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select status, database_status from v$instance;
select status, database_status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
Further, the Getting Started page cannot be found with Error code: ERR_CONNECTION_REFUSED: http://127.0.0.1:8080/apex/f?p=4950
What I did to try and resolve this issue
While digging in various forums, I found that maybe I needed to specify the initXE.ora file on startup:
SQL> startup pfile='C:\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora'
ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.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.
ORA-01078: failure in processing system parameters
No such spfileXE.ora file. OK, so create it?
SQL> create spfile='C:\oraclexe\app\oracle\product\11.2.0\server\dbs\spfileXE.ora' from pfile='C:\oraclexe\app\oracle\product\11.2.0\server\dbs\init.ora';
File created.
SQL> startup pfile='C:\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora'
ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist [C:\oraclexe\app\oracle\product\11.2.0\server\DATABASE\<ORACLE_BASE>]
ORA-48187: specified directory does not exist
OSD-00002: additional error information
O/S-Error: (OS 123) The filename, directory name, or volume label syntax is incorrect.
SQL>
Well, I guess I have no ADR Base directory..
C:\oraclexe\app\oracle\product\11.2.0\server\database>dir
Volume in drive C is Windows
Volume Serial Number is 8822-A545
Directory of C:\oraclexe\app\oracle\product\11.2.0\server\database
22/09/2014 06:06 PM <DIR> .
22/09/2014 06:06 PM <DIR> ..
22/09/2014 06:06 PM 2,048 hc_xe.dat
22/09/2014 02:44 PM 73 initXE.ora
27/08/2011 09:58 AM 31,744 oradba.exe
22/09/2014 05:07 PM 2,265 oradim.log
4 File(s) 36,130 bytes
2 Dir(s) 298,041,913,344 bytes free
I list as much supporting information as I know how to below.
Environment
- Windows 7 Enterprise, SP1, 64-bit.
- Oracle Database Express Edition 11g Release 2 for Windows x32
I have these environment variables set:
echo %ORACLE_SID% - %ORACLE_HOME%
XE - C:\oraclexe\app\oracle\product\11.2.0\server
Installation settings
These are the installation settings used by the installer:
Destination Folder: C:\oraclexe\
Oracle Home: C:\oraclexe\app\oracle\product\11.2.0\server\
Oracle Base:C:\oraclexe\
Port for 'Oracle Database Listener': 1521
Port for 'Oracle Services for Microsoft Transaction Server': 2030
Port for 'Oracle HTTP Listener': 8080
Output from lsnrctl stat
Some debugging information that may be useful:
C:\oraclexe\app\oracle\product\11.2.0\server\bin>lsnrctl stat
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 22-SEP-2014 19:05:52
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
Start Date 22-SEP-2014 17:07:02
Uptime 0 days 1 hr. 58 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\ITS57151\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mycompany.local)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
C:\oraclexe\app\oracle\product\11.2.0\server\bin>
Various config files
Here are the contents of other important files.
The initXE.ora file - C:\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora
SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'
The listener.ora file - C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mycompany.local)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
The tnsnames.ora file - C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mycompany.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
Edited to restructure question to put the meat of the issue up front and supporting information below.