2

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)
    )
  )
2
  1. Uninstall Oracle Database Express Edition 11g Release 2 for Windows x32
  2. Download and install Oracle Database Express Edition 11g Release 2 for Windows x64 - the 64-bit installer.
  3. Take some time to de-stress. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.