This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 3, 2013 1:30 AM by 984556 RSS

Oracle Database Archive Logs

984556 Newbie
Currently Being Moderated

Hello Everyone,

 

I have searched Oracle Documents and over the internet. It is really hard to get information on Archive Log management , so I thought of posting it here:

 

Now, our Database 9i  ( EBS 11.5.x ) was in no archive mode , so I enabled it to archive mode.

edit the pfile $ORACLE_HOME/admin/SID/pfile which will be /oracle/proddb/9.2.0/dbs/initPROD.ora

 

############################

# Archive Log Destinations -benr(10/15/04)

############################

log_archive_dest_1='location=/yourlocation/archive'

log_archive_start=TRUE


then log in to Database





SQL> shutdown normal;

SQL> startup mount exclusive;

SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> alter system set log_archive_dest_1='LOCATION=/oracle/archivelog'
scope=both;
SQL> alter database archivelog;
SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> alter database open;
SQL> alter system archive log all;
SQL> alter system archive log start;
SQL> archive log start;

 


 

Next day, I checked the archive location and it generated archives of 1 GB even though there is no activity on the Database.

 

 

root@test4:/oracle/archivelog>ls
1_844.dbf   1_862.dbf   1_880.dbf   1_898.dbf   1_916.dbf   1_934.dbf
1_845.dbf   1_863.dbf   1_881.dbf   1_899.dbf   1_917.dbf   1_935.dbf
1_846.dbf   1_864.dbf   1_882.dbf   1_900.dbf   1_918.dbf   1_936.dbf
1_847.dbf   1_865.dbf   1_883.dbf   1_901.dbf   1_919.dbf   1_937.dbf
1_848.dbf   1_866.dbf   1_884.dbf   1_902.dbf   1_920.dbf   1_938.dbf
1_849.dbf   1_867.dbf   1_885.dbf   1_903.dbf   1_921.dbf   1_939.dbf
1_850.dbf   1_868.dbf   1_886.dbf   1_904.dbf   1_922.dbf   1_940.dbf
1_851.dbf   1_869.dbf   1_887.dbf   1_905.dbf   1_923.dbf   1_941.dbf
1_852.dbf   1_870.dbf   1_888.dbf   1_906.dbf   1_924.dbf   1_942.dbf
1_853.dbf   1_871.dbf   1_889.dbf   1_907.dbf   1_925.dbf   1_943.dbf
1_854.dbf   1_872.dbf   1_890.dbf   1_908.dbf   1_926.dbf   1_944.dbf
1_855.dbf   1_873.dbf   1_891.dbf   1_909.dbf   1_927.dbf   1_945.dbf
1_856.dbf   1_874.dbf   1_892.dbf   1_910.dbf   1_928.dbf   1_946.dbf
1_857.dbf   1_875.dbf   1_893.dbf   1_911.dbf   1_929.dbf   1_947.dbf
1_858.dbf   1_876.dbf   1_894.dbf   1_912.dbf   1_930.dbf   lost+found
1_859.dbf   1_877.dbf   1_895.dbf   1_913.dbf   1_931.dbf   test.txt
1_860.dbf   1_878.dbf   1_896.dbf   1_914.dbf   1_932.dbf
1_861.dbf   1_879.dbf   1_897.dbf   1_915.dbf   1_933.dbf

 

 

 

Question

 

1. How can I delete old archive logs without using RMAN ?

 

2. How should I know which archive logs to keep and which ones to delete ?

 

3. Is there a way to know why the Database is generating so many archive logs ?

  • 1. Re: Oracle Database Archive Logs
    JohnWatson Guru
    Currently Being Moderated

    You need to be a bit careful doing this sort of thing with an EBS system. For example, your parameter chagnes may be lost the next time you run autoconfig,

  • 2. Re: Oracle Database Archive Logs
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    If this you test database and do not need any recovery and restore in future you can 

    1.delete archive log using os level command i.e rm

    2. you can not identify in that way. if you need recovery/ restore in future you have to keep that archive

    3.check with application team you are using EBS application there should be some batch job running.

     

    HTH

  • 3. Re: Oracle Database Archive Logs
    984556 Newbie
    Currently Being Moderated

    JohnWatson wrote:

     

    You need to be a bit careful doing this sort of thing with an EBS system. For example, your parameter chagnes may be lost the next time you run autoconfig,

     

    Yes, this is the precise reason why I posted here on the forum. What parameter changes could be lost or what impact it could have on the Ebusiness Suite Application Tier ? Appreciate your feedback.

     

     

    DK2010 wrote:

     

    If this you test database and do not need any recovery and restore in future you can

    1.delete archive log using os level command i.e rm

    2. you can not identify in that way. if you need recovery/ restore in future you have to keep that archive

    3.check with application team you are using EBS application there should be some batch job running.

     

    1. But if I delete at the OS command level then how could I recover if there is corruption with the Control Files ?

     

    2. I need to keep archive log for recovery if in case the control files get corrupted or something like that happens, so in order for recovery , what archive logs I should I keep, in the above list of files you can see more than 100 archive log files , which is for 1 day, tomorrow it will be 200 archive logs ( another 100 will be added )

    at this rate in 10 days I will need 10GB space in 10 days.

     

    So,how can I know for the recovery only, which ones to keep and which ones to delete ?

  • 4. Re: Oracle Database Archive Logs
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    Yes,  you have to keep that archive. you can schedule job to compress the archive files (save space usage) and move it to other location as backup.

    You can schedule the COLD backup as well once in a week , in that case you have to only mange archive for one week.

     

    HTH

  • 5. Re: Oracle Database Archive Logs
    984556 Newbie
    Currently Being Moderated

    DK2010 wrote:

     

    Hi,

     

    Yes,  you have to keep that archive. you can schedule job to compress the archive files (save space usage) and move it to other location as backup.

    You can schedule the COLD backup as well once in a week , in that case you have to only mange archive for one week.

     

    HTH

     

    I mean let's say after 1 month from now I have a disaster -- corruption of control files

     

    1 month archive logs = 30 GB ( from 1 October to 31 October )

    I have a disaster corruption of control files on 1 november

     

    Question:


    1. Do I need past 1 month archive logs -- OR -- archive logs from 31 October ?


    This is what I would like to know, Do I need to backup whole month archive logs or 1 week or last few days ?

     

    2. What is the maintenance strategy for managing archive logs ?

  • 6. Re: Oracle Database Archive Logs
    DK2010 Guru
    Currently Being Moderated

    Hi,

    seems my previous post in not understandable

    let me explain again

    >>1. Do I need past 1 month archive logs -- OR -- archive logs from 31 October ?

    You can schedule the COLD backup as well once in a week , in that case you have to only manage archive for one week. not for a month, generally in database oracle suggest daily/weekely database backup

    >>2. What is the maintenance strategy for managing archive logs ?

    You are not going to use RMAN, then you can schedule the crontab which can compress the old archive log and move at backup loaction


    Hope this time its clear

  • 7. Re: Oracle Database Archive Logs
    EdStevens Guru
    Currently Being Moderated

    984556 wrote:

     

    Hello Everyone,

     

    I have searched Oracle Documents and over the internet. It is really hard to get information on Archive Log management , so I thought of posting it here:

     

    Now, our Database 9i  ( EBS 11.5.x ) was in no archive mode , so I enabled it to archive mode.

    edit the pfile $ORACLE_HOME/admin/SID/pfile which will be /oracle/proddb/9.2.0/dbs/initPROD.ora

     

    ############################

    # Archive Log Destinations -benr(10/15/04)

    ############################

    log_archive_dest_1='location=/yourlocation/archive'

    log_archive_start=TRUE


    then log in to Database





    SQL> shutdown normal;

    SQL> startup mount exclusive;

    SQL> alter system set log_archive_start=TRUE scope=spfile;
    SQL> alter system set log_archive_dest_1='LOCATION=/oracle/archivelog'
    scope=both;
    SQL> alter database archivelog;
    SQL> ALTER SYSTEM ARCHIVE LOG START;
    SQL> alter database open;
    SQL> alter system archive log all;
    SQL> alter system archive log start;
    SQL> archive log start;

     


     

    Next day, I checked the archive location and it generated archives of 1 GB even though there is no activity on the Database.

     

     

    1. root@test4:/oracle/archivelog>ls 
    2. 1_844.dbf   1_862.dbf   1_880.dbf   1_898.dbf   1_916.dbf   1_934.dbf 
    3. 1_845.dbf   1_863.dbf   1_881.dbf   1_899.dbf   1_917.dbf   1_935.dbf 
    4. 1_846.dbf   1_864.dbf   1_882.dbf   1_900.dbf   1_918.dbf   1_936.dbf 
    5. 1_847.dbf   1_865.dbf   1_883.dbf   1_901.dbf   1_919.dbf   1_937.dbf 
    6. 1_848.dbf   1_866.dbf   1_884.dbf   1_902.dbf   1_920.dbf   1_938.dbf 
    7. 1_849.dbf   1_867.dbf   1_885.dbf   1_903.dbf   1_921.dbf   1_939.dbf 
    8. 1_850.dbf   1_868.dbf   1_886.dbf   1_904.dbf   1_922.dbf   1_940.dbf 
    9. 1_851.dbf   1_869.dbf   1_887.dbf   1_905.dbf   1_923.dbf   1_941.dbf 
    10. 1_852.dbf   1_870.dbf   1_888.dbf   1_906.dbf   1_924.dbf   1_942.dbf 
    11. 1_853.dbf   1_871.dbf   1_889.dbf   1_907.dbf   1_925.dbf   1_943.dbf 
    12. 1_854.dbf   1_872.dbf   1_890.dbf   1_908.dbf   1_926.dbf   1_944.dbf 
    13. 1_855.dbf   1_873.dbf   1_891.dbf   1_909.dbf   1_927.dbf   1_945.dbf 
    14. 1_856.dbf   1_874.dbf   1_892.dbf   1_910.dbf   1_928.dbf   1_946.dbf 
    15. 1_857.dbf   1_875.dbf   1_893.dbf   1_911.dbf   1_929.dbf   1_947.dbf 
    16. 1_858.dbf   1_876.dbf   1_894.dbf   1_912.dbf   1_930.dbf   lost+found 
    17. 1_859.dbf   1_877.dbf   1_895.dbf   1_913.dbf   1_931.dbf   test.txt 
    18. 1_860.dbf   1_878.dbf   1_896.dbf   1_914.dbf   1_932.dbf 
    19. 1_861.dbf   1_879.dbf   1_897.dbf   1_915.dbf   1_933.dbf 

     

     

     

    Question

     

    1. How can I delete old archive logs without using RMAN ?

     

    2. How should I know which archive logs to keep and which ones to delete ?

     

    3. Is there a way to know why the Database is generating so many archive logs ?

     

    1.  why would you want to avoid the use of rman?  It is the tool that oracle provides at no additional cost for this very purpose.  Don't tell me you don't want to use rman because you don't understand it.  As a DBA your primary responsibility is to be able to recover a lost database.  If you can't do that, nothing else matters. And rman is the tool for it.  If you don't understand how to use it, you'd better start learning.  Today.  Now.

     

    Whenever an archivelog is written, a record of it is written to the control file.  If you delete the archivlogs by any means other than rman commands, then oracle has no way of knowing those files have gone missing and thinks they are still available for recovery.  Not good.

     

    2 .  Again, that is what rman is for.  Specifically, you don't want to delete any archivelogs that you haven't backed up with rman.

     

    3.  Archivelogs are created when an online redo log is filled.  That happens as a direct result of DML being issued.  If you think the database is "idle" and so should not be generating any redo, you can use the logminer utility to analyze the redologs and see exactly what DML is being issued.

  • 8. Re: Oracle Database Archive Logs
    EdStevens Guru
    Currently Being Moderated

    984556 wrote:

     

    JohnWatson wrote:

     

    You need to be a bit careful doing this sort of thing with an EBS system. For example, your parameter chagnes may be lost the next time you run autoconfig,

     

    Yes, this is the precise reason why I posted here on the forum. What parameter changes could be lost or what impact it could have on the Ebusiness Suite Application Tier ? Appreciate your feedback.

     

     

    DK2010 wrote:

     

    If this you test database and do not need any recovery and restore in future you can

    1.delete archive log using os level command i.e rm

    2. you can not identify in that way. if you need recovery/ restore in future you have to keep that archive

    3.check with application team you are using EBS application there should be some batch job running.

     

    1. But if I delete at the OS command level then how could I recover if there is corruption with the Control Files ?

     

    2. I need to keep archive log for recovery if in case the control files get corrupted or something like that happens, so in order for recovery , what archive logs I should I keep, in the above list of files you can see more than 100 archive log files , which is for 1 day, tomorrow it will be 200 archive logs ( another 100 will be added )

    at this rate in 10 days I will need 10GB space in 10 days.

     

    So,how can I know for the recovery only, which ones to keep and which ones to delete ?

     

    Use rman the way it was designed, and all your questions just go away.  All of your difficulties here stem from your assumption of not using rman.

  • 9. Re: Oracle Database Archive Logs
    984556 Newbie
    Currently Being Moderated

    EdStevens wrote:

     


    my quote

    Next day, I checked the archive location and it generated archives of 1 GB even though there is no activity on the Database.

     

     

     

     

     

    Question

     

    1. How can I delete old archive logs without using RMAN ?

     

    2. How should I know which archive logs to keep and which ones to delete ?

     

    3. Is there a way to know why the Database is generating so many archive logs ?

     

    1.  why would you want to avoid the use of rman?  It is the tool that oracle provides at no additional cost for this very purpose.  Don't tell me you don't want to use rman because you don't understand it.  As a DBA your primary responsibility is to be able to recover a lost database.  If you can't do that, nothing else matters. And rman is the tool for it.  If you don't understand how to use it, you'd better start learning.  Today.  Now.

     

    Whenever an archivelog is written, a record of it is written to the control file.  If you delete the archivlogs by any means other than rman commands, then oracle has no way of knowing those files have gone missing and thinks they are still available for recovery.  Not good.

     

    2 .  Again, that is what rman is for.  Specifically, you don't want to delete any archivelogs that you haven't backed up with rman.

     

    3.  Archivelogs are created when an online redo log is filled.  That happens as a direct result of DML being issued.  If you think the database is "idle" and so should not be generating any redo, you can use the logminer utility to analyze the redologs and see exactly what DML is being issued.

     

    Thanks for the reply and advice.

    yes, I agree RMAN should be used. However, we want to test few things on our production system so we made a clone to do the experiments on it.

     

    making RMAN Catalog database for the clone would require additional resources ( machine , space, ip management etc ) to avoid this , I thought of not using RMAN for the cloned system.

     

     

    So,basically all the archive logs should be maintained for recovery purpose. it cannot be deleted because it contains information about control files.

     

    Since the day archive logs are enabled, I have to maintain all the archive logs ? other than RMAN there is no way to delete the old archive logs while taking into consideration recovery

  • 10. Re: Oracle Database Archive Logs
    EdStevens Guru
    Currently Being Moderated

    984556 wrote:

     

    EdStevens wrote:

     


    my quote

    Next day, I checked the archive location and it generated archives of 1 GB even though there is no activity on the Database.

     

     

     

     

     

    Question

     

    1. How can I delete old archive logs without using RMAN ?

     

    2. How should I know which archive logs to keep and which ones to delete ?

     

    3. Is there a way to know why the Database is generating so many archive logs ?

     

    1.  why would you want to avoid the use of rman?  It is the tool that oracle provides at no additional cost for this very purpose.  Don't tell me you don't want to use rman because you don't understand it.  As a DBA your primary responsibility is to be able to recover a lost database.  If you can't do that, nothing else matters. And rman is the tool for it.  If you don't understand how to use it, you'd better start learning.  Today.  Now.

     

    Whenever an archivelog is written, a record of it is written to the control file.  If you delete the archivlogs by any means other than rman commands, then oracle has no way of knowing those files have gone missing and thinks they are still available for recovery.  Not good.

     

    2 .  Again, that is what rman is for.  Specifically, you don't want to delete any archivelogs that you haven't backed up with rman.

     

    3.  Archivelogs are created when an online redo log is filled.  That happens as a direct result of DML being issued.  If you think the database is "idle" and so should not be generating any redo, you can use the logminer utility to analyze the redologs and see exactly what DML is being issued.

     

    Thanks for the reply and advice.

    yes, I agree RMAN should be used. However, we want to test few things on our production system so we made a clone to do the experiments on it.

    making RMAN Catalog database for the clone would require additional resources ( machine , space, ip management etc ) to avoid this , I thought of not using RMAN for the cloned system.

    That is so wrong on so many levels.

    First, you don't have to create another catalog for the clone.  One catalog serves all the databases in your entire organization.  Alternatively, the catalog itself is really optional.  The key parts of the rman repository are kept in the database control file whether you use a catalog or not.

     

    So,basically all the archive logs should be maintained for recovery purpose. it cannot be deleted because it contains information about control files.

    Yes, archivelogs are maintained for recovery.  No, they do not contain information about the control file.  Just the opposite, the control file contains information about the archivelogs.




    Since the day archive logs are enabled, I have to maintain all the archive logs ? other than RMAN there is no way to delete the old archive logs while taking into consideration recovery

     

    No, you don't have to maintain the archivelogs from they day they are enabled. You need to keep the ones that date from whatever backup you want to use for a beginning point for a recovery.

    Rman is your friend, but only if you use it, and you have not yet presented a valid reason for not using it.  And if you want to be able to recover your database you will not be able to present a valid reason for not using, because there are none.  As I said before, if a DBA cannot recover a database, nothing else matters.  And being able to recover involves much more than simply having backups, it means understanding the principles of backup and recovery and how to use the tools that oracle provides for that.  Your questions and the direction you keep wanting to take indicate that you are currently lacking that knowledge.  Trust me, you don't want to wait until your production database is lost before you decide its time to acquire that knowledge.  The time is NOW.

     

    Start here:  Oracle Backup and Recovery User's Guide

    and here:  http://www.amazon.com/Oracle-RMAN-Backup-Recovery-Press/dp/0071628606/ref=sr_1_1?s=books&ie=UTF8&qid=1378296311&sr=1-1&keywords=oracle+rman

  • 11. Re: Oracle Database Archive Logs
    984556 Newbie
    Currently Being Moderated

    Basically Archive logs are go to back in time recovery. however, I realized the easiest way to delete it is through RMAN

     

    connect to the database using rman

    $ rman target / nocatalog


    Execute  the following command to delete specific archive log file

    RMAN> delete archivelog sequence 1234;


    Execute the following command to delete all archivelog.

    RMAN> delete archivelog all;


    Crosscheck to sync the database dictionary, otherwise you will get error, when you take archive log backup of missing archive log

    RMAN> crosscheck archivelog all;



    Archivelog List Commands

    RMAN>list archivelog all;
    RMAN>list copy of archivelog until time ‘SYSDATE-10′;
    RMAN>list copy of archivelog from time ‘SYSDATE-10′
    RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
    RMAN>list copy of archivelog from sequence 1000;
    RMAN>list copy of archivelog until sequence 1500;
    RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

    Archivelog Delete Commands

    RMAN>delete archivelog all;
    RMAN>delete archivelog until time ‘SYSDATE-10′;
    RMAN>delete archivelog from time ‘SYSDATE-10′
    RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
    RMAN>delete archivelog from sequence 1000;
    RMAN>delete archivelog until sequence 1500;
    RMAN>delete archivelog from sequence 1000 until sequence 1500;

    Note : Also, you can use noprompt statement for do not yes-no question.
    RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

  • 12. Re: Oracle Database Archive Logs
    tnoble Newbie
    Currently Being Moderated

    You don't want to be deleting archivelogs like this,


    RMAN> delete archivelog sequence 1234;


    You want to set a reasonable retention policy in rman:

     

    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;

     

    (set the control_file_record_keep_time parameter in the init.ora to reflect your retention policy, especially if you're not using a catalog)

    and then let rman decide which archivelogs it no longer needs based on the retention policy.

     

    delete obsolete;

     

    or, if you can't keep that many days worth of archivelogs around, then you could purge them by backup count:

     

    backup check logical tag='database backup' database plus archivelog not backed up 2 times tag = 'archivelog backup';

    delete noprompt archivelog all backed up 2 times to device type disk;

     

    So, you would get two copies of each archivelog backed up and then they would be deleted from the archive_log_dest, later rman will remove the backups when the retention policy indicates that they are no longer needed.

    But, whatever you do, let rman decide what archivelogs to get rid of, don't try to outguess rman.

  • 13. Re: Oracle Database Archive Logs
    984556 Newbie
    Currently Being Moderated

    tnoble wrote:

     

    You don't want to be deleting archivelogs like this,


    RMAN> delete archivelog sequence 1234;


    You want to set a reasonable retention policy in rman:

     

    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;

     

    (set the control_file_record_keep_time parameter in the init.ora to reflect your retention policy, especially if you're not using a catalog)

    and then let rman decide which archivelogs it no longer needs based on the retention policy.

     

    delete obsolete;

     

    or, if you can't keep that many days worth of archivelogs around, then you could purge them by backup count:

     

    backup check logical tag='database backup' database plus archivelog not backed up 2 times tag = 'archivelog backup';

    delete noprompt archivelog all backed up 2 times to device type disk;

     

    So, you would get two copies of each archivelog backed up and then they would be deleted from the archive_log_dest, later rman will remove the backups when the retention policy indicates that they are no longer needed.

    But, whatever you do, let rman decide what archivelogs to get rid of, don't try to outguess rman.

     

    I believe you have answered it in the most appropriate and precise manner. You have mentioned a scenario with no catalog, but what do you advice for a Catalog setup. Thanks

  • 14. Re: Oracle Database Archive Logs
    tnoble Newbie
    Currently Being Moderated

    there's no changes to be made here with the addition of a catalog.  Having the catalog will give you more recovery options.

1 2 Previous Next

Legend

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