ORA-19815, ORA-01031 and ORA-12514 error

Question

I have Linux RedHat Enteprise with Oracle 11g database and I've stumbled upon a circle of errors and I can not find a way out.

First error in alert_XE.log:

ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/fast_recovery_area/XE/archivelog/2018_12_01/o1_mf_1_580_%u_.arc'
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2940.trc:
ORA-16038: log 2 sequence# 580 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dh40n8hk_.log'
System state dump requested by (instance=1, osid=2940), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_diag_2859.trc
USER (ospid: 2940): terminating the instance due to error 16038

Which would be easily solveable with rman, but here comes error number 2 when "connect target /" on rman:

ORA-01031: insufficient privileges

Which is of course again solveable with orapwd file. I've created a new file with the right SYS password. Trying again to login via rman, but of course, no luck.

I've tried also with sqlplus with various commands like "sqlplus sys/@XE as sysdba" and I get error number 3:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I've tried "tnsping XE" and it looks ok:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

Any ideas how to proceed?

Answer 1

The last line in alert.log says that your instance should have been killed (Shutdown abort). This is why you can't connect: listener is up and running and responds to tnsping, BUT the instance is not available (listener does not currently know of service requested in connect descriptor").

You can verify with:

ps -ef | grep pmon  

and check if the process ora_pmon_INSTANCE is present or not.

First you have to free space in archive destination, moving elsewhere archived logs, change parameter db_recovery_file_dest_size to a bigger size and/or add disk space to filesystem where path /u01/app/oracle/fast_recovery_area/XE/archivelog/2018_12_01 resides.

Then restart the instance. Sometimes I saw redolog corruption after this scenario, but this is a different issue, you can face of after resolving the first one (startup the instance).