Oracle 11g R2 – Troubleshooting Idle Instance When Creating Database

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 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)
    )
  )