This discussion is archived
14 Replies Latest reply: Jan 18, 2013 1:55 AM by becks RSS

Enabling archive log

becks Newbie
Currently Being Moderated
Hi all,

1) How to enable archive log in oracle 10g. Found a link in google. http://cuddletech.com/articles/oracle/node58.html
Is it the correct way, need to down the database.

2) Could the archive log affect the performance of the database, if i have average of 1 write transactions to the database? I have a database that have already switch on by my precessder. I notice that it generate a 40,000kb of file every 3 minutes.
  • 1. Re: Enabling archive log
    sb92075 Guru
    Currently Being Moderated
    becks wrote:
    Hi all,

    1) How to enable archive log in oracle 10g. Found a link in google. http://cuddletech.com/articles/oracle/node58.html
    Is it the correct way, need to down the database.
    yes

    >
    2) Could the archive log affect the performance of the database, if i have average of 1 write transactions to the database? I have a database that have already switch on by my precessder. I notice that it generate a 40,000kb of file every 3 minutes.
    you can't get something (data protection) for nothing.
    If you want to protect the data, then you must pay the price in performance.
  • 2. Re: Enabling archive log
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;
    1) How to enable archive log in oracle 10g. Found a link in google. http://cuddletech.com/articles/oracle/node58.html
    Is it the correct way, need to down the database.
    You can also check
    How to enable archivelog mode in 11g on windows 7 os?
    2) Could the archive log affect the performance of the database, if i have average of 1 write transactions to the database? I have a database that have already switch on by my precessder. I notice that it generate a 40,000kb of file every 3 minutes.
    You need to observer your system, you may need to increase your redo files,

    Regard
    Helios
  • 3. Re: Enabling archive log
    Fran Guru
    Currently Being Moderated
    1) How to enable archive log in oracle 10g. Found a link in google. http://cuddletech.com/articles/oracle/node58.html
    Is it the correct way, need to down the database.
    Yes, you must put the database in mount state to active archivelog mode.
    2) Could the archive log affect the performance of the database, if i have average of 1 write transactions to the database? I have a database that have already switch on by my precessder. I notice that it generate a 40,000kb of file every 3 minutes.
    It affects more space than performance of the database. But yes, it affects.
  • 4. Re: Enabling archive log
    becks Newbie
    Currently Being Moderated
    Any ideas how much the performance is affected. Is it normal to generate such amount of files? Cause my window server will experience 100%cpu and large amount is used by the Oracle services process.(For this oracle server that has archive log switch on.)

    Helios, you suggest to increase redo files. Is is the redo buffer size? any guide to read up and configure this?
  • 5. Re: Enabling archive log
    ashishpande Newbie
    Currently Being Moderated
    Hi Becks,

    1) How to enable archive log in oracle 10g. Found a link in google. http://cuddletech.com/articles/oracle/node58.html
    Is it the correct way, need to down the database.
    You cannot enable archive log without bouncing the database.
    Follow these steps for this.

    1)Shutdown immediate;
    2)startup mount
    3)alter database archivelog;
    4)alter database open;

    you can check it by using command

    archive log list;
    2) Could the archive log affect the performance of the database, if i have average of 1 write transactions to the database? I have a database that have already switch on by my precessder. I notice that it generate a 40,000kb of file every 3 minutes.
    Yes, It will surely affect the performance of your server because in background ARCn process is running which uses memory of the server.
  • 6. Re: Enabling archive log
    sb92075 Guru
    Currently Being Moderated
    becks wrote:
    Any ideas how much the performance is affected. Is it normal to generate such amount of files? Cause my window server will experience 100%cpu and large amount is used by the Oracle services process.(For this oracle server that has archive log switch on.)

    Helios, you suggest to increase redo files. Is is the redo buffer size? any guide to read up and configure this?
    DML generates REDO.
    no DML, no REDO gets generated.
    REDO log files should be size such that they switch (fill up) about once every 15 - 20 minutes.
  • 7. Re: Enabling archive log
    EdStevens Guru
    Currently Being Moderated
    becks wrote:
    Any ideas how much the performance is affected. Is it normal to generate such amount of files?
    The archive logs are nothing more than a copy of the online redologs, made when the online redo log is filled. Larger online redo logs = fewer but larger archive logs. The total amount of redo generated (and archived) is purely a function of the amount of update activity occurring in the database.

    Cause my window server will experience 100%cpu and large amount is used by the Oracle services process.(For this oracle server that has archive log switch on.)
    you can't just assume that this is due to the fact that you are in archivelog mode. If you are serious about protecting your data (and that is the PRIME DIRECTIVE for a dba) then archivelogmode on a production database is non-negotiable. If you are experienceing performance issues, the first thing you have to do is throw out any pr-conceived notions of why you have a performance issue.
    >
    Helios, you suggest to increase redo files. Is is the redo buffer size? any guide to read up and configure this?
    redo buffer size is not the same thing as online redo log size.
  • 8. Re: Enabling archive log
    jgarry Guru
    Currently Being Moderated
    The questions are, how often are your redo log switches, are the users noticing a performance issue, what level of service have you agreed to for backup and recovery?

    There can be trade-offs made, depending on the exact circumstances. As Ed noted, not losing data is the prime directive. There are many ways to avoid losing data, someone has to decide which are most appropriate and which are just too expensive. Someone playing what-if scenarios with your projected budgets is going to have a different idea about that than someone who is in charge of millions of dollars of online orders. DW and DSS systems often have other places to get the data and may not require being able to point-in-time recover. It depends.

    What you need to avoid is managers saying things like "we only care about restoring to last nights backup," when they don't understand what they are saying.

    Some bulk-loading situations can benefit from nologging operations, and some (like a massive update in an app upgrade) can benefit from going out of and into archivelog mode, since such things can generate huge amounts of redo without a requirement to recover halfway through them. Just be sure and take an appropriate backup after such things. That also means, take a backup as soon as you go into archivelog mode.
  • 9. Re: Enabling archive log
    becks Newbie
    Currently Being Moderated
    Hi all,

    Thanks for all the advice. But i have problem setting the archive log destination. I followed the instructions by changing the pfile, but it does not take effect. Any ideas how to change the destiantions and the size of each redo files.
  • 10. Re: Enabling archive log
    sb92075 Guru
    Currently Being Moderated
    becks wrote:
    Hi all,

    Thanks for all the advice. But i have problem setting the archive log destination. I followed the instructions by changing the pfile, but it does not take effect. Any ideas how to change the destiantions and the size of each redo files.
    pfile name must be explicitly provided similar to example below

    SQL> STARTUP PFILE='\ORAHOME\PRODUCT\V11.2\DATABASE\INITORCL.ORA'
  • 11. Re: Enabling archive log
    Jeremy D Smith Newbie
    Currently Being Moderated
    There's plenty of stuff on the net about this.

    alter system set log_archive_dest = '/u01/app/oracle/arch/mydatabase';

    This guy gives a good definition of changing the log size http://muneer2908.wordpress.com/2011/01/27/how-to-change-the-redo-log-file-size-in-oracle-database/
    Just make sure you're not changing the one that's currently being used..but he talks about that.
  • 12. Re: Enabling archive log
    becks Newbie
    Currently Being Moderated
    thanks c3po
    this article is good, having the same issue as affecting the performance. Btw i still try to google some oother questions like.

    1) Other than storage issue, any differences for having bigger size redo files. Because if having smaller files size affect the performanace, i would definetly choose a big redo file size like even 500mb???

    2) the command to only housekeep the redo log files. As in dba can specify the amount of log files to keep.
  • 13. Re: Enabling archive log
    Jeremy D Smith Newbie
    Currently Being Moderated
    1. I think 100M is big enough. Maybe there is an underlying problem if you're shooting out too many archivelog files. Also, larger files take longer to write to disk so you could get some latency there. Also if they're smaller its easier to work out a restore point if you're doing a recovery because you know when they were created.

    2. I take it you mean archived log files. You need your own process to manage this. You will need to only delete them when you're sure you've backed them up. You might be able to use RMAN to manage this, not sure. But a DBAs job depends on this so I like to be certain for myself.
  • 14. Re: Enabling archive log
    becks Newbie
    Currently Being Moderated
    After some trying with the database.
    What is the advantage/disadvantage of using bigger or smaller size archive log file?
    1)About the restore points, the restore is up to a redo file? Means if a bigger log file is used, I will lost more data as the last archive log file is not archived before the crash happen. Or Is just easier to pin point to the exact time that we want to restore to?

    2)RMAN is able to manage the archived redo log files.

Legend

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