Oracle 11g XE - sudden ORA-01034: ORACLE not available and ORA-27101: shared memory realm does not exist

oracle oracle11g oracle-xe ora-01034 ora-27101
user3548783 picture user3548783 · Jan 20, 2016 · Viewed 9.7k times · Source
  • Windows 7 32 bit laptop
  • Local instance of Oracle 11g XE

Firstly I have been researching this myself before asking and while there are many references/suggestions around I've not been able to fix the issue for myself.

All of a sudden when i try to login into my local hobbyist instance of Oracle 11g XE i am getting the following:

SQL> connect
Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

It has always worked fine and last tried probably 4 weeks ago without issue. To my knowledge nothing has been changed.

Following advice on the web, things I have tried:

1 - Checked listener status

C:\>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 19-JAN-2016 23:53:50

    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                19-JAN-2016 18:18:13
    Uptime                    0 days 5 hr. 35 min. 44 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\SUPPORT-WIN7-32\listener\alert\log.xml

    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SUPPORT-WIN7-32)(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

2 - Checked relevant services are started which they are - OracleServiceXE & OracleXETNSListener. Have stopped/restarted as well.

3 - Checked my path variable is correct which seems to be - C:\oraclexe\app\oracle\product\11.2.0\server\bin;

4 - Checked relevant files tnsnames and listener files - C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN. I think they are OK but maybe I have missed something obvious.

tsnnames.ora

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

listener.ora

ID_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 = SUPPORT-WIN7-32)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

5 - Checked ORACLE_HOME and ORACLE_SID in registry

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE
ORACLE_SID=XE
ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server

6 - Tried starting up the instance - which throws up another error: ORA-03113: end-of-file on communication channel

C:\>SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.2.0 Production on Wed

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1385488 bytes
Variable Size             398461936 bytes
Database Buffers          239075328 bytes
Redo Buffers                5545984 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3988
Session ID: 130 Serial number: 5

7 - Checked listener logs in C:\oraclexe\app\oracle\diag\tnslsnr\SUPPORT-WIN7-32\listener\alert but it's not obvious to me.

8 - Database logs in C:\oraclexe\app\oracle\diag\rdbms\xe\xe\alert

Database log not listener log:

<msg time='2016-01-20T00:12:06.368+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15120:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  sessions                 = 172
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.370+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  spfile                   = &quot;C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DBS\SPFILEXE.ORA&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.372+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15120:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  memory_target            = 1G
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.374+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  control_files            = &quot;C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.376+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  compatible               = &quot;11.2.0.0.0&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.379+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  db_recovery_file_dest    = &quot;C:\oraclexe\app\oracle\fast_recovery_area&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.381+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15120:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  db_recovery_file_dest_size= 20G
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.383+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  undo_management          = &quot;AUTO&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.385+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  undo_tablespace          = &quot;UNDOTBS1&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.387+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  remote_login_passwordfile= &quot;EXCLUSIVE&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.389+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  dispatchers              = &quot;(PROTOCOL=TCP) (SERVICE=XEXDB)&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.391+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15120:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  shared_servers           = 5
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.394+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15120:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  job_queue_processes      = 20
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.396+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  audit_file_dest          = &quot;C:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.398+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  db_name                  = &quot;XE&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.400+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15120:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  open_cursors             = 300
 </txt>
</msg>
<msg time='2016-01-20T00:12:06.402+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:15111:3633090201' type='NOTIFICATION' group='startup'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6680'>
 <txt>  diagnostic_dest          = &quot;C:\ORACLEXE\APP\ORACLE&quot;
 </txt>
</msg>
<msg time='2016-01-20T00:12:08.320+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='3020'>
 <txt>PMON started with pid=2, OS id=3020 
 </txt>
</msg>
<msg time='2016-01-20T00:12:08.345+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7356'>
 <txt>PSP0 started with pid=3, OS id=7356 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.390+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7980'>
 <txt>VKTM started with pid=4, OS id=7980 at elevated priority
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.414+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7980'>
 <txt>VKTM running at (10)millisec precision with DBRM quantum (100)ms
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.423+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='1820'>
 <txt>GEN0 started with pid=5, OS id=1820 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.445+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7936'>
 <txt>DIAG started with pid=6, OS id=7936 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.471+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7672'>
 <txt>DBRM started with pid=7, OS id=7672 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.498+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7636'>
 <txt>DIA0 started with pid=8, OS id=7636 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.520+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7296'>
 <txt>MMAN started with pid=9, OS id=7296 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.555+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='8120'>
 <txt>DBW0 started with pid=10, OS id=8120 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.584+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7988'>
 <txt>LGWR started with pid=11, OS id=7988 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.609+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='4716'>
 <txt>CKPT started with pid=12, OS id=4716 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.631+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6944'>
 <txt>SMON started with pid=13, OS id=6944 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.653+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7560'>
 <txt>RECO started with pid=14, OS id=7560 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.675+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='7996'>
 <txt>MMON started with pid=15, OS id=7996 
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.702+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='6680'>
 <txt>starting up 1 dispatcher(s) for network address &apos;(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))&apos;...
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.761+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='6680'>
 <txt>starting up 5 shared server(s) ...
 </txt>
</msg>
<msg time='2016-01-20T00:12:11.437+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksu_setup_oracle_base:25413:2787919602' client_id='' type='NOTIFICATION'
 group='startup' level='16' host_id='SUPPORT-WIN7-32'
 host_addr='fe80::6cc6:21a3:2980:ac01%14' module='' pid='6680'>
 <txt>ORACLE_BASE from environment = C:\oraclexe\app\oracle
 </txt>
</msg>
<msg time='2016-01-20T00:12:11.566+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:2974:4222364190' client_id='' type='NOTIFICATION'
 group='admin_ddl' level='16' host_id='SUPPORT-WIN7-32'
 host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe' pid='8112'>
 <txt>alter database mount exclusive
 </txt>
</msg>
<msg time='2016-01-20T00:12:10.702+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='4464'>
 <txt>MMNL started with pid=16, OS id=4464 
 </txt>
</msg>
<msg time='2016-01-20T00:12:15.737+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7988'>
 <txt>Successful mount of redo thread 1, with mount id 2782727451
 </txt>
</msg>
<msg time='2016-01-20T00:12:15.755+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>Database mounted in Exclusive Mode
 </txt>
</msg>
<msg time='2016-01-20T00:12:15.771+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>Lost write protection disabled
 </txt>
</msg>
<msg time='2016-01-20T00:12:16.409+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3045:2802784106' client_id='' type='NOTIFICATION'
 group='admin_ddl' level='16' host_id='SUPPORT-WIN7-32'
 host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe' pid='8112'>
 <txt>Completed: alter database mount exclusive
 </txt>
</msg>
<msg time='2016-01-20T00:12:16.414+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:2974:4222364190' client_id='' type='NOTIFICATION'
 group='admin_ddl' level='16' host_id='SUPPORT-WIN7-32'
 host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe' pid='8112'>
 <txt>alter database open
 </txt>
</msg>
<msg time='2016-01-20T00:12:16.688+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7988'>
 <txt>LGWR: STARTING ARCH PROCESSES
 </txt>
</msg>
<msg time='2016-01-20T00:12:16.721+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksbrdp:4282:3697353022' type='NOTIFICATION' group='process start'
 level='16' host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14'
 pid='6572'>
 <txt>ARC0 started with pid=24, OS id=6572 
 </txt>
</msg>
<msg time='2016-01-20T00:12:17.722+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7988'>
 <txt>ARC0: Archival started
 </txt>
</msg>
<msg time='2016-01-20T00:12:17.725+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7988'>
 <txt>LGWR: STARTING ARCH PROCESSES COMPLETE
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.088+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_8112.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.091+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>************************************************************************
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.094+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>You have following choices to free up space from recovery area:
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.097+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.099+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>   then consider changing RMAN ARCHIVELOG DELETION POLICY.
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.101+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>2. Back up files to tertiary device such as tape using RMAN
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.104+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>   BACKUP RECOVERY AREA command.
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.106+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>3. Add disk space and increase db_recovery_file_dest_size parameter to
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.109+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>   reflect the new space.
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.111+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>4. Delete unnecessary files using RMAN DELETE command. If an operating
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.114+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>   system command was used to delete files, then use RMAN CROSSCHECK and
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.116+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>   DELETE EXPIRED commands.
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.118+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>************************************************************************
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.122+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>ARCH: Error 19809 Creating archive log file to &apos;C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2016_01_20\O1_MF_1_932_%U_.ARC&apos;
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.133+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_8112.trc:
ORA-16038: log 2 sequence# 932 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: &apos;C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_8X88V21X_.LOG&apos;
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.193+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>USER (ospid: 8112): terminating the instance due to error 16038
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.507+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7936'>
 <txt>System state dump requested by (instance=1, osid=8112), summary=[abnormal instance termination].
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.511+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7936'>
 <txt>System State dumped to trace file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_diag_7936.trc
 </txt>
</msg>
<msg time='2016-01-20T00:12:17.725+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='6572'>
 <txt>ARC0: STARTING ARCH PROCESSES
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.728+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='6572'>
 <txt>Logins disabled; aborting ARCH process startup (1092)
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.747+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='6572'>
 <txt>ARC0: Archival disabled due to shutdown: 1092
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.751+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='krso_proc_stop:2101:825284245' client_id='' type='NOTIFICATION'
 group='shutdown' level='16' host_id='SUPPORT-WIN7-32'
 host_addr='fe80::6cc6:21a3:2980:ac01%14' module='' pid='6572'>
 <txt>Shutting down archive processes
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.754+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='krso_proc_stop:2123:1369042098' client_id='' type='NOTIFICATION'
 group='shutdown' level='16' host_id='SUPPORT-WIN7-32'
 host_addr='fe80::6cc6:21a3:2980:ac01%14' module='' pid='6572'>
 <txt>Archiving is disabled
 </txt>
</msg>
<msg time='2016-01-20T00:12:18.870+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module=''
 pid='7936'>
 <txt>Dumping diagnostic data in directory=[cdmp_20160120001218], requested by (instance=1, osid=8112), summary=[abnormal instance termination].
 </txt>
</msg>
<msg time='2016-01-20T00:12:20.556+00:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='SUPPORT-WIN7-32' host_addr='fe80::6cc6:21a3:2980:ac01%14' module='oradim.exe'
 pid='8112'>
 <txt>Instance terminated by USER, pid = 8112
 </txt>
</msg>

Would appreciate any help at all. Can provide any further information if you can direct me.

Many thanks

Answer

stee1rat picture stee1rat · Jan 20, 2016

According to your alert log your DB_RECOVERY_FILE_DEST_SIZE was exceeded:

19809, 00000, "limit exceeded for recovery files"
//*Cause: The limit for recovery files specified by the
//        DB_RECOVERY_FILE_DEST_SIZE was exceeded.
// *Action: There are five possible solutions:
//          1) Take frequent backup of recovery area using RMAN.
//          2) Consider changing RMAN retention policy.
//          3) Consider changing RMAN archived log deletion policy.
//          4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
//          5) Delete files from recovery area using RMAN.

So you need to increase the database parameter DB_RECOVERY_FILE_DEST_SIZE in your initfile or spfile, then start the database and delete all archive logs:

rman target /
delete archivelog all;

You also can delete all archive logs (in case you don't need them), since your database can be mounted successfully:

sqlplus / as sysdba
shutdown abort
startup mount
exit

rman target /
delete archivelog all
exit

sqlplus /as sysdba
alter database open;

Afterwards, I advise you to disable the archivelog mode in your database to avoid the same situation in the future. I believe you don't take hot-backups:

sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

Related questions

How to create a new database after initally installing oracle database 11g Express Edition?

I have installed Oracle Database 11g Express Edition on my pc (windows 7) and I have installed Oracle SQL Developer as well. I want to create a simple database to start with, maybe with one table or two and then use …

Read More
Change NLS Character set parameters on Oracle 11g XE

I am using Oracle 11g Express Edition. Currently when I check NLS character set parameter using SELECT * FROM nls_database_parameters; it gives the default values: NLS_CHARACTERSET: AL32UTF8 NLS_NCHAR_CHARACTERSET: AL16UTF16 I want to set both …

Read More
ora-1658: unable to creat initial extent for segment in tablespace

i'm getting the error "ora-1658: unable to create initial extent for segment in tablespace MYTBS" when trying to import a dump using imp command.i'm using oracle XE 11.2 in a standalone windows machine. database backup is around 200MB. following is …

Read More