ORA-19809 & ORA-12560

oracle

Following some maintenance on our server (change raid battery + reboot) my database (Oracle 11g) is not available anymore. I m a newbie in Oracle and I have been through the forum and I can t find the solution of my pb, I can t fix the ORA-19809 error since I can t connect to the DB as sysdba, all the solution that I have found need to be connected as sysdba. Please help I have been struggling on that for days and I seems to go in circle at the moment. Here is what I ve got :

C:\>set oracle_sid=MYTIKKA

C:\>sqlplus /nolog

SQL>connect / as sysdba
ERROR ORA-12560: TNS:protocol adapter error

Here are the errors I have in my c:/oracle/diag/rdbms/mytikka/mytikka/trace/alert_mytikka.log :

Errors in file c:\oracle\diag\rdbms\mytikka\mytikka\trace\mytikka_arc1_6420.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 30839808 bytes disk space from 10485760000 limit
ARC1: Error 19809 Creating archive log file to 'C:\ORACLE\FLASH_RECOVERY_AREA\MYTIKKA\ARCHIVELOG\2015_03_31\O1_MF_1_88141_%U_.ARC'
Errors in file c:\oracle\diag\rdbms\mytikka\mytikka\trace\mytikka_ora_7524.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10485760000 bytes is 99.86% used, and has 14655488 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.
************************************************************************
Errors in file c:\oracle\diag\rdbms\mytikka\mytikka\trace\mytikka_ora_7524.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 30839808 bytes disk space from 10485760000 limit
ARCH: Error 19809 Creating archive log file to 'C:\ORACLE\FLASH_RECOVERY_AREA\MYTIKKA\ARCHIVELOG\2015_03_31\O1_MF_1_88141_%U_.ARC

'
Errors in file c:\oracle\diag\rdbms\mytikka\mytikka\trace\mytikka_ora_7524.trc:
ORA-16038: log 1 sequence# 88141 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\MYTIKKA\REDO01.LOG'
USER (ospid: 7524): terminating the instance due to error 16038
Tue Mar 31 16:08:06 2015
ARC3 started with pid=23, OS id=8104 
Instance terminated by USER, pid = 7524

TNSPING seems to work

C:\>tnsping mytikka
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2
015 02:32:14
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 420025-w
eb1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mytikka)
))
OK (0 msec)

lsnrctl
C:>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 01-APR-2015 02:35:21


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


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=420025-web1)(PORT=1521))
)
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                31-MAR-2015 16:08:17
Uptime                    0 days 10 hr. 27 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\11.2.0\dbhome_1\network\admin\listen
er.ora
Listener Log File         c:\oracle\diag\tnslsnr\420025-web1\listener\alert\log.
xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=420025-web1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
The listener supports no services
The command completed successfully

Here is my tnsnames.ora :

# tnsnames.ora Network Configuration File: C:\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.


MYTIKKA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 420025-web1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mytikka)
    )
  )


TSAPIDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 420025-web1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mytikka)
    )
  )

It s a Windows Server, I have checked and all the services are running : OracleMTSRecoveryService & OracleServiceMYTIKKA

sqlnet.ora

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

#SQLNET.AUTHENTICATION_SERVICES = (NTS)
trace_level_client=16
trace_file_client=sqlnet.trc
trace_directory_client = c:\temp

Best Answer

Try connecting to database with -prelim option, I have used it to change database parameters when normal logon is not possible

> sqlplus -prelim / as sysdba
SQL> startup mount;
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 20G scope=both;
SQL> shutdown immediate;
SQL> startup;