Oracle XE 11g the XE database was not found

Question

I am a new one who started to use Oracle Database and faced the problem during installation the last verstion of Oracle XE 11g x64 to the Microsoft Windows 7 x64 Enterprise.

During installation were not any errors and Windows Services (OracleXETNSListener and OracleServiceXE) were successfully created and started.

However, when I try to get the list of available databases I receive an error

ORA-01034: ORACLE not available

For some reason the XE database was not created into %ORACLE_BASE%\oradata\XE.

The content of my listener.ora with localhost:

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 = 127.0.0.1)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

I fixed tnsnames.ora by setting localhost IP address:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(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) 
    ) 
  ) 

sqlnet.ora contains both services:

SQLNET.AUTHENTICATION_SERVICES = (NONE;NTS)

The log file from '%ORACLE_HOME%\server\log\myhost\client' contains this error:

Oracle Database 11g Clusterware Release 11.2.0.2.0 - Production Copyright 1996, 2010 Oracle. All rights reserved. 2016-01-20 19:51:43.920: [ default][5096]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2 [
CLSE][5096]clse_get_crs_home: Error retrieving OLR configuration [0] [Error opening olr registry key. Не удается найти указанный файл. ]

How can I find what goes wrong and fix the problem?

Pissarro

First of all I should warn you that this will probably take a bit to trouble shoot! Are you sure that the Database creation itself has been successful? The error you show indicates a problem with the client connecting to the Database. Can you share the content of OracleDatabaseXEServerInstall.log, it is located in the system root which usually is C:\WINDOWS – Prefrontal

Unfortunately, did not find this log file into Windows and Oracle directories – Pissarro

Ok, but do you have a problem with installing the Database or with connecting to it? The location of your log file message would indicate that this is an issue with connecting to the Database. – Prefrontal

An erorr is ORA-01034: ORACLE not available. I provided additional details about the problem – Pissarro

Answer 1

After struggling with this problem for almost a week I found a solution, how to make XE database available from this source. It is in Russian, I provide the solution right here in English.

1. Install the last version of Oracle XE 11gR2 from the Oracle site to the default location c:\oraclexe with default password admin. If not default, then you should edit scripts below (paths and pwd) before use them.

2. Run cmd command line under administrator privileges and use it until the end of reconfiguration. First of all, set environment variables for your DB:

   set ORACLE_SID=XE
   set ORACLE_BASE=c:\oraclexe\app\oracle 
   set ORACLE_HOME=c:\oraclexe\app\oracle\product\11.2.0\server
   

3. Delete the XE database by command:

   oradim -delete -sid XE
   

4. Delete PWDXE.ORA file:

   del %ORACLE_HOME%\database\PWDXE.ORA
   

5. Copy init.ora from %ORACLE_HOME%\config\scripts to %ORACLE_HOME%\database, instead of existing, rename it to initXE.ora and add the configuration below:

   open_cursors=300
   db_name=XE
   control_files=("C:\oraclexe\app\oracle\oradata\XE\control.dbf")
   job_queue_processes=4
   compatible=11.2.0.0.0
   diagnostic_dest=C:\oraclexe\app\oracle\.
   memory_target=1G
   sessions=100
   audit_file_dest=C:\oraclexe\app\oracle\admin\XE\adump
   remote_login_passwordfile=EXCLUSIVE
   dispatchers="(PROTOCOL=TCP) (SERVICE=XEXDB)"
   shared_servers=4
   undo_management=AUTO
   undo_tablespace=UNDOTBS1
   DB_RECOVERY_FILE_DEST_SIZE = 10G
   DB_RECOVERY_FILE_DEST=C:\oraclexe\app\oracle\fast_recovery_area
   #optional
   nls_language="RUSSIAN"
   nls_territory="RUSSIA"
   

6. From the previous cmd line with admin previleges run the command to create Oracle services and file %ORACLE_HOME%\database\PWDXE.ORA:

   oradim -new -sid %ORACLE_SID% -intpwd admin -startmode auto -srvcstart system
   

7. Run commands below to stop Windows Oracle XE services, delete them and additional XE files:

   sqlplus /nolog
   connect sys/admin as sysdba;
   shutdown immediate;
   exit;
   del %ORACLE_BASE%\oradata\XE\CONTROL.DBF
   del %ORACLE_BASE%\fast_recovery_area\XE\ONLINELOG\*.LOG
   

8. Create two files into one directory:

createXE.cmd

set ORACLE_SID=XE
set NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251
set ORACLE_BASE=C:\oraclexe\app\oracle
sqlplus /nolog @createXE.sql

createXE.sql

spool %ORACLE_BASE%\admin\XE\CreateXE.log;
connect sys/admin as sysdba;
startup nomount;
CREATE DATABASE XE
LOGFILE GROUP 1 SIZE 51200K,
GROUP 2 SIZE 51200K
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
CHARACTER SET CL8MSWIN1251
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'c:\oraclexe\app\oracle\oradata\XE\system.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'c:\oraclexe\app\oracle\oradata\XE\sysaux.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'c:\oraclexe\app\oracle\oradata\XE\temp.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE 'c:\oraclexe\app\oracle\oradata\XE\undotbs1.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
CREATE SMALLFILE TABLESPACE USERS DATAFILE 'c:\oraclexe\app\oracle\oradata\XE\users.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;
-- catalog
@%ORACLE_HOME%\rdbms\admin\catalog.sql;
@%ORACLE_HOME%\rdbms\admin\catblock.sql;
@%ORACLE_HOME%\rdbms\admin\catproc.sql;
@%ORACLE_HOME%\rdbms\admin\catoctk.sql;
connect system/manager
-- sqlplus profile
@%ORACLE_HOME%\sqlplus\admin\pupbld.sql;
@%ORACLE_HOME%\sqlplus\admin\help\hlpbld.sql helpus.sql;
connect sys/admin as sysdba;
-- xdb protocol
@%ORACLE_HOME%\rdbms\admin\catqm.sql change_on_install SYSAUX TEMP NO;
@%ORACLE_HOME%\rdbms\admin\catxdbj.sql;
@%ORACLE_HOME%\rdbms\admin\catrul.sql;
-- compiling invalid objects
@%ORACLE_HOME%\rdbms\admin\utlrp.sql;
spool off;
exit;

9. Run createdXE.cmd from command line with admin privileges.

10. Done.

Pissarro