Home » Server Options » Streams & AQ » RMAN-08137: WARNING: archived log not deleted as it is still needed (oracle 11.1.0.7 AIX)
RMAN-08137: WARNING: archived log not deleted as it is still needed [message #524780] Mon, 26 September 2011 13:35 Go to next message
reddy1404
Messages: 8
Registered: September 2011
Junior Member
I am trying to delete archive logs older than 7 days in streams environment.

1) we dont have a standby database
2) checkpoint_retention_time = 7

Please help me with this.

Regards,
Reddy
Re: RMAN-08137: WARNING: archived log not deleted as it is still needed [message #527357 is a reply to message #524780] Mon, 17 October 2011 14:02 Go to previous messageGo to next message
abcd1234
Messages: 23
Registered: September 2011
Junior Member
Hi All,

I have the same issue as above.

SQL> set serveroutput on
DECLARE
hScn number := 0;
lScn number := 0;
sScn number;
ascn number;
alog varchar2(1000);
begin
select min(start_scn), min(applied_scn) into sScn, ascn
from dba_capture;
DBMS_OUTPUT.ENABLE(2000);
for cr in (select distinct(a.ckpt_scn)
from system.logmnr_restart_ckpt$ a
where a.ckpt_scn <= ascn and a.valid = 1
and exists (select * from system.logmnr_log$ l
where a.ckpt_scn between l.first_change# and l.next_change#)
order by a.ckpt_scn desc)
loop
if (hScn = 0) then
hScn := cr.ckpt_scn;
else
lScn := cr.ckpt_scn;
exit;
end if;
end loop;

if lScn = 0 then
lScn := sScn;
end if;
dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:');
for cr in (select name, first_time
from DBA_REGISTERED_ARCHIVED_LOG
where lScn between first_scn and next_scn order by thread#)
loop

dbms_output.put_line(cr.name||' ('||cr.first_time||')');
end loop;
end;
/


When I have run the above code that is given in metalink to check the minimum archive logs required to restart streams i got the below ouput.

Capture will restart from SCN 55313283790in the following file:
+FLASH/ORCL/archivelog/2011_02_22/thread_1_seq_842.346.443519740 (11-MAR-11)
+FLASH/ORCL/archivelog/2011_02_22/thread_2_seq_832.189.440690538 (11-MAR-11)


Does this mean that I will not be able to delete the archive logs from 11-mar-11 ?

Regards,
Mike

Re: RMAN-08137: WARNING: archived log not deleted as it is still needed [message #527365 is a reply to message #527357] Mon, 17 October 2011 16:06 Go to previous message
abcd1234
Messages: 23
Registered: September 2011
Junior Member
I have run the script against a different database and it gives me an ouput as below.

+FLASH/TEST/archivelog/2011_02_22/thread_1_seq_842.346.443519740 (10-JUL-11)
+FLASH/TEST/archivelog/2011_02_22/thread_1_seq_842.346.443519740 (10-JUL-11)


Unfortunately few logs before 10-JUL-2011 are also not deleted.

Regards,
Mike
Previous Topic: Streams - Design Question
Next Topic: AQ truncate
Goto Forum:
  


Current Time: Tue Oct 21 21:28:20 CDT 2014

Total time taken to generate the page: 0.09258 seconds