This discussion is archived
1 2 3 Previous Next 40 Replies Latest reply: Feb 18, 2013 6:32 PM by yxes2013 RSS

Can archivelog history in the v$table be full?

yxes2013 Newbie
Currently Being Moderated
Hi All,

9i
OEL 5.6

Our database in set to archivelog mode but we keep on deleting the archive logs because we do cold backup everyday at 12 midnight,
since we only have 9am - 6pm office hours and apps processing window.
All the users go home after 6pm. So we shut down the database and perform tar all the /u01/oradata directory where all the data files are located.
Then we delete all the archive logs.

My question is ....since we do manual delete of the archivelogs (and not rman delete obsolete logs) this means in the data dictionary views all the logs are still recorded?
I am afraid the data dictionary for archive logs will get full since we generate lots of logs everyday.

Any comments or suggestions please....


Thanks a lot,

Yxz
  • 1. Re: Can archivelog history in the v$table be full?
    Mihael Pro
    Currently Being Moderated
    To clean the controlfile you can use :

    RMAN> crosscheck archivelog all;
    RMAN> delete expired archivelog all;
  • 2. Re: Can archivelog history in the v$table be full?
    yxes2013 Newbie
    Currently Being Moderated
    Yeah...I know that :P :)
    But I am not using RMAN but cold backup manual OS copy only....so inside the dictionary the database is being tagged at no backup at all? hence the archive logs can not be obsoleted?
  • 3. Re: Can archivelog history in the v$table be full?
    Mihael Pro
    Currently Being Moderated
    yxes2013 wrote:
    Yeah...I know that :P But I am not using RMAN but cold backup manual OS copy only....so inside the dictionary the database is being tagged at no backup at all? hence the archive logs can not be obsoleted?
    Yes, but you can use rman to clean controlfile.
  • 4. Re: Can archivelog history in the v$table be full?
    asahide Expert
    Currently Being Moderated
    Hi,

    You can login to RMAN using controlfile, like target / .
    And then you can clean up by above command.

    Regards,
  • 5. Re: Can archivelog history in the v$table be full?
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    If you delete files manualy RMAN doesnt get you delete those file or not. That's why you need to run crosscheck archivelog after delete some files manualy.

    The suggested way to delete files from RMAN

    Regard
    Helios
  • 6. Re: Can archivelog history in the v$table be full?
    Bawer Journeyer
    Currently Being Moderated
    yxes2013 wrote:
    Yeah...I know that :P But I am not using RMAN but cold backup manual OS copy only....so inside the dictionary the database is being tagged at no backup at all? hence the archive logs can not be obsoleted?
    What do you copy at midnight? flash_recovery_area or control+data files ?
    is there any scheduled rman task to backup database (daily or weekly) ?
  • 7. Re: Can archivelog history in the v$table be full?
    EdStevens Guru
    Currently Being Moderated
    yxes2013 wrote:
    Yeah...I know that :P But I am not using RMAN but cold backup manual OS copy only....so inside the dictionary the database is being tagged at no backup at all? hence the archive logs can not be obsoleted?
    Every time an archivelog is written, a record of that is created in the control file. Doesn't matter how you do your backups. Those records get written to the control file. Only rman knows how to reconcile those records to reality and only rman can delete those records. So if you are regularly deleting the archivelogs outside of rman, the records of them will just continue to build up in the control file.

    rman "crosscheck " checks the control file records of archivelogs against reaility. If an archivelog if found to have 'gone missing' (and every night you are making a bunch of them go missing) it will mark the control file record for that file as 'expired'.

    rman 'delete expired' will delete all control file records of archivelogs that are marked 'expired'.


    I guess the next question is -- even given your up time requirements and large maintenance window, why are you not using rman to take your back ups? It is a very powerful tool, and without it you are just making any recovery scenario much more difficult.
  • 8. Re: Can archivelog history in the v$table be full?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You can query V$CONTROLFILE_RECORD_SECTION to see the number of entries on ArchiveLogs that are present in the controlfile. Oracle will wrap around the entries to avoid hitting any "archivelog history full" issues.

    Retention of entries is determined by CONTROLFILE_RECORD_KEEP_TIME. Assuming that this is set to 7 (days), {i.e. the default value}, the number of archivelog records maintained in the controlfile will be the peak number of archivelogs in any 7 day period. If in the past you had 1000 archivelogs in 7 days, the controlfile would have grown to accomodate 1000 entries --- even if in current days you are only generating 250 archivelogs in 7 days.

    You can also query V$ARCHIVED_LOG to see the archivelog entries that are actually present in the controlfile.


    Hemant K Chitale
  • 9. Re: Can archivelog history in the v$table be full?
    yxes2013 Newbie
    Currently Being Moderated
    I thank you all,

    @Ed
    Every time an archivelog is written, a record of that is created in the control file.
    So if I am not deleting my expired archive logs using rman crosscheck the control file will grow bigger and bigger? Since it is recording all the archivelogs ?
    If I delete the archivelogs using rman does it make the controlfile smaller?

    ***Oh sorry I think this is answered my heman.
    It is a very powerful tool, and without it you are just making any recovery scenario much more difficult.
    I agree with you dear :), but sometimes I found it hard and complicated to recover if you miss a single archive log or when it gets corrupted for some reason(just a single archivelog will make a database get doomed) :(. Sometimes the database will not start even after appyling the last archive log....it will ask for the next one (why is this?, How can I force startup the database after applying a certain archivelog?)

    Compared to a cold backup (which we can afford since we should down it everyday) , After copying the datafiles ...presto! the database is good. What you see is what you get. You do not
    feel afraid or stressed that something is missing. Well this is only my opinion and it can be wrong.

    @bawer
    What do you copy at midnight? flash_recovery_area or control+data files ?
    is there any scheduled rman task to backup database (daily or weekly) ?
    There is a schedule cron that shutdown db and copy datafiles and then startup db. the archive logs just retained where they are and deleted every week using "rm" and not rman delete.

    Edited by: yxes2013 on 14.2.2013 18:03
  • 10. Re: Can archivelog history in the v$table be full?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Sometimes the database will not start even after appyling the last archive log....it will ask for the next one (why is this?, How can I force startup the database after applying a certain archivelog?)
    If you are using RMAN, use the UNTIL SEQUENCE# last_archivelog_seq+1 clause.
    If you are using SQLPlus command line, use RECOVER ... UNTIL CANCEL and then CANCEL after applying the last available ArchiveLog.


    Hemant K Chitale
  • 11. Re: Can archivelog history in the v$table be full?
    yxes2013 Newbie
    Currently Being Moderated
    Thanks hem,
    If you are using SQLPlus command line, use RECOVER ... UNTIL CANCEL and then CANCEL after applying the last available ArchiveLog.
    Yes I have tried this one, but still the database wont startup and still asking for the next archivelog to be applied. There seems "completeness" check undergooing?
  • 12. Re: Can archivelog history in the v$table be full?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    At the minimum, you need to apply ArchiveLogs until the first ArchiveLog generated after the end of the database backup. If you stop before this ArchiveLog, you do not have a consistent database yet.

    For example, if your BACKUP DATABASE ran from 10:00 to 11:30 and ArchiveLog Sequences# 101 to 106 were generated between 10:00 and 11:30, when you RECOVER, you need ArchiveLog Sequence#107 (the first one after 11:30) to make the database consistent.


    Hemant K Chitale
  • 13. Re: Can archivelog history in the v$table be full?
    yxes2013 Newbie
    Currently Being Moderated
    Thanks dear :)

    For the sake of argument, can I force it to startup at any SCN or any CANCEL time even if my database is not consistent? I just want to get a certain table that was dropped at a certain moment.
  • 14. Re: Can archivelog history in the v$table be full?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    can I force it to startup at any SCN or any CANCEL time even if my database is not consistent? I
    No. Oracle won't allow that.

    Hemant K Chitale
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points