Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Oracle Database Archive Logs
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' <strong style="color: #000000;">scope=both;<br/></strong>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.
[email protected]:/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 ?
Best Answer
-
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
Answers
-
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,
-
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
-
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 ?
-
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
-
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 ?
-
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
-
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>altersystem setlog_archive_dest_1='LOCATION=/oracle/archivelog'<strong style="color: #000000;">scope=both;<br/></strong>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.- [email protected]:/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
[email protected]:/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. 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.
-
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.
-
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
-
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