Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Saturday, July 23, 2011

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..

1 comment:

Anonymous said...

Hi there, I believe your website may be getting browser compatibility troubles. When I seem at your site in Safari, it seems good but when opening in Net Explorer, it's got some overlapping. I just wished to provide you with a speedy heads up! Other then that, amazing website!