Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Exclamation Unanswered: Deleting Archived Redo Logs

    What's the right way to purge archived redo log files? I have taken a backup (hot) of the database. I have copied the Archived redo log files (and then copied them again to a remote share on another server).

    Is there a proper command to purge these files so that they don't fill up the directory? Or am I left to do it via the OS?

    Regards,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Use OS command:

    Code:
    find <arclogdir> -mtime +10 -exec rm {} \;
    Removes files older than 10 days.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    I presume that's UNIX; I can find the equivalent in Windows. But there's no way to do this through Oracle? I see in the Oracle tables (v$log or v$logfile, I can't remember which) a column for deleted. What's that for?

    Regards,

    hmscott

    Quote Originally Posted by LKBrwn_DBA
    Use OS command:

    Code:
    find <arclogdir> -mtime +10 -exec rm {} \;
    Removes files older than 10 days.
    Have you hugged your backup today?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Baloney

    Im sure you can look into the manual and find out what the 'deleted' column means. but the v$log or v$logfile views have nothing to do with archive logs.

    The standard procedure to remove archive logs normally is built into the cold backup script:

    1) Shutdown db.
    2) Copy and compress database files and archivelogs.
    3) Copy and compress controlfile and other supporting files.
    4) Remove archive logs.
    5) Startup the db.

    All within the same cold backup OS script.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >All within the same cold backup OS script
    I envy you for having the luxury of being able to shutdown Oracle at all,
    let alone long enough to do cold backups.

    My SLA is for 99.999% uptime per year; which is about 5 minutes of downtime per year;
    and this has been the case for the last 8 years.
    Many Oracle DBs support 365*24 operations where downtime has been eliminated or at least reduce to being a close to ZERO as possible.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    That's all OK.
    If no downtime is possible..then how to establish our backup procedure in the company. Can anybody tell me the standard way of backup if i hv implemented HOT Backup and archive log files are being generated...

    - The Hot backup is taken daily at 9:00PM.

    So, with every hot backup, it backs up the archive log files also. Shoule i delete these archive log files immediately after hot backup (that is, archive log generated till such time of hot backup is copied on backup media and then deleted).

    Will it work if i need sometimes my backup for recovery etc.

    Please suggest,.

    Regards,
    Kamesh Rastogi
    - KR

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If your using RMAN to do the hot backup then use 'BACKUP ARCHIVELOG ALL DELETE ALL INPUT;' command to backup the archived logs and then delete them.

    Otherwise with the old style hotbackup,
    1) Backup the tablespaces,
    2) Then do an archive log switch
    3) Create a list of archived redo logs and back them up to tape
    4) Delete all files which are in the list if the backup was successful.

    Alan

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    are you using RMAN????
    RMAN can do this for you.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Forgive my ignorance on Oracle system views. The correct view was V$ARCHIVED_LOG. There are two columns: ARCHIVED and DELETED. Despite the fact that the files are actually deleted from the OS, they are still listed as NO in the V$ARCHIVED_LOG table. This led me to the conclusion that there was an "Oracle" system stored procedure which would properly delete the file from the OS and then update the table with the fact that this had been done. It appears that RMAN will do this, but I am not currently running RMAN.

    As for making a cold backup of the database, I am not allowed to do that. The site SLA calls for 24/7 operations.

    I am considering installing RMAN and OEM; however I have been in charge of the Oracle databases for all of six weeks now and I don't have the comfort level with RMAN that I'm others do.

    Regards,

    hmscott

    Quote Originally Posted by LKBrwn_DBA
    Im sure you can look into the manual and find out what the 'deleted' column means. but the v$log or v$logfile views have nothing to do with archive logs.

    The standard procedure to remove archive logs normally is built into the cold backup script:

    1) Shutdown db.
    2) Copy and compress database files and archivelogs.
    3) Copy and compress controlfile and other supporting files.
    4) Remove archive logs.
    5) Startup the db.

    All within the same cold backup OS script.
    Have you hugged your backup today?

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    I am currently considering installation of RMAN, but I'm still new to the Oracle world.

    I'm running Oracle 8.1.7 on a W2K box.

    Sigh. Study, study, study.

    Regards,

    hmscott

    Quote Originally Posted by The_Duck
    are you using RMAN????
    RMAN can do this for you.
    Have you hugged your backup today?

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by RastogiKamesh
    That's all OK.
    - The Hot backup is taken daily at 9:00PM.

    So, with every hot backup, it backs up the archive log files also. Shoule i delete these archive log files immediately after hot backup (that is, archive log generated till such time of hot backup is copied on backup media and then deleted).
    Regards,
    Kamesh Rastogi
    If the achive logs are backed up then you can safely remove
    the originals from the db server.

    If you eventually need to recover, you will have to copy the
    backups back onto the db server.

    In the past, I have zipped up archive logs. After a hotbackup, they can be deleted.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Sorry, I guess I should have been more clear; I created a backup script that makes a local copy of the archive redo logs and then deletes them from their normal repository location. During a backup process yesterday afternoon, the delete statement attempted to delete a log file just as Oracle was trying to write it. The OS delete statement locked the file and crashed Oracle (this is Oracle 8.1.7).

    The things that I am trying to do now:
    1. I resized the log files so that there are fewer switches. The log files were switching every 30 - 45 seconds before; now they are switching every five minutes (during peak periods).
    2. I have moved the scheduled backup to an off-peak period -- actually it was always scheduled for off-peak, but I was testing a change to the process yesterday).

    I am considering installation of RMAN and/or OEM, but I don't fully understand the licensing requirements or the impact on performance of one or possibly two new instances of Oracle running on the server (W2K, 2 Xeon 2.8 GHz processors, 2 GB memory).

    Finally, I am considering using the V$ARCHIVELOG table to specifically identify the files to be deleted and then recording the fact that the files were deleted.

    Thanks again for your help. Navigating the Oracle world as a newbie can be real...um...pain.

    Regards,

    hmscott
    Quote Originally Posted by The_Duck
    If the achive logs are backed up then you can safely remove
    the originals from the db server.

    If you eventually need to recover, you will have to copy the
    backups back onto the db server.

    In the past, I have zipped up archive logs. After a hotbackup, they can be deleted.
    Have you hugged your backup today?

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    I think the find command earlier in the thread removes
    files older than 10 days, that should eliminate the error of
    removing a current arc log like you did.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Just in case you didnt read it the first time try

    1) Backup the tablespaces (In backup mode)
    2) Then do an archive log switch
    3) Create a list of archived redo logs (ls -1 <dir> > logs.txt) and back them up to tape
    4) Delete all files which are in the list if the backup was successful.

    Alan
    Last edited by AlanP; 08-13-04 at 11:52.

  15. #15
    Join Date
    Jan 2004
    Posts
    99
    this should do the job for windows!

    spool del_logs.sql
    select 'del'||name from v$archived_log where completion_time < (sysdate -5);
    spool off
    host del_logs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •