ORA-16038 & ORA-19809: limit exceeded for recovery files

Today my FRA got filled up and I was getting "archiver error: cannot connect until freed"

So as the quick and dirty way to solve is to delete the archivelogs from the FRA.

Since this is a test environment and I don't care about the backups and want bring the system back as quickly as possible.

so I went ahead and deleted the archivelogs.

ASMCMD> cd ARCHIVELOG/
ASMCMD> ls
2011_07_24/
ASMCMD> rm -r 2011_07_24/
You may delete multiple files and/or directories.
Are you sure? (y/n) y

Now the good thing is space is freed but the bad thing is yet to come:


I still cannot connect to the database and getting the archiver error, hmmm I just freed up the space, deleted todays archive logs also...including the current one ..."hmmmm wait I deleted the current archive log also"....bad ....

so now I cannot switch the logfile on instance-2 on which there was a long running job running since two days:

it just hangs
SQL> alter system switch logfile;




^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL>
SQL>
SQL>
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 3 sequence# 485 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_01/TESTDB/onlinelog/group_3.269.750189149'
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_02/TESTDB/onlinelog/group_3.269.750189157'


SQL> alter system switch logfile;


^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL>
SQL>
SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 2 INACTIVE
4 2 CURRENT

####cannot drop the group also..

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance TESTDB2 (thread 2) needs to be archived
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_01/TESTDB/onlinelog/group_3.269.750189149'
ORA-00312: online log 3 thread 2:
'+ODIQA_REDO_02/TESTDB/onlinelog/group_3.269.750189157'


SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL>
SQL>
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 2 CURRENT
4 2 ACTIVE

SQL> alter system switch logfile;




^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

still the same...

so tried another feat.

rman>connect target /
rman>crosscheck archive log all;
rman>DELETE EXPIRED ARCHIVELOG ALL;
Deleted 198 EXPIRED objects


RMAN> exit


Recovery Manager complete.


Now I gave another shot:


SQL> alter system checkpoint;

System altered.




SQL> alter system archive log current;

System altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 CURRENT
3 2 ACTIVE
4 2 CURRENT

SQL> alter system archive log current;

System altered.

SQL>




It finally worked..cool deal..