This discussion is archived
1 2 3 4 Previous Next 49 Replies Latest reply: Jan 21, 2013 11:08 AM by 864103 RSS

nologging

864103 Newbie
Currently Being Moderated
Hi all ,

oracle 11g.2 ASM RAC under RHEL 5

i know when you are running in archive mode the redo log file will be not
overwitten and generate archive redo log file once log sequence is happen ,
but when we have table and change his mod in nologging and do insert in this table
there is nothing was written to redo log file so base on that there is no archive
redo log file ???? correct me if i'm wrong , due to i change the mod of my table
to nologging and do huage insert in this table but we see the archive redo log file was generated????


second question i see my database size is increased every day 1 GB but it's generated
archive log files more that 2 GB why ??? it's just log (ex : 100 insert ,40 delete , create tablespace ,add datafile , as a command )
no actual data ????


thanks
  • 1. Re: nologging
    vlethakula Expert
    Currently Being Moderated
    For first question
    Refer
    http://www.dba-oracle.com/t_nologging_append.htm ------------->It discuss in which case REDO is generated


    2)Redolog files/Archive logs contains changes made to the database. They contain changed data not commands.
    changes could be Insert/Update/Delete
  • 2. Re: nologging
    Justin Cave Oracle ACE
    Currently Being Moderated
    If you actually do an unlogged operation (which requires more than marking the table NOLOGGING), less redo will be generated (index maintenance still generates redo as does things like allocating extents) so the amount of archived redo generated will be decreased.

    Your REDO logs have to have enough data to be able to, as the name implies, redo an operation. If you are doing an INSERT, you would need to write the data values that you are inserting to the REDO logs in order to be able to redo the operation. So it is very much expected that if you are adding 1 GB of data every day on net that you're going to generate more than 1 GB of archived redo particularly where you are actually, say, deleting 400 MB of data and inserting 1.4 GB of data to get to that 1 GB net.

    Justin
  • 3. Re: nologging
    864103 Newbie
    Currently Being Moderated
    okay , thanks for this good articals , so now if i apply the nologging must be with appeand hint
    so there is not generate redo ???? just confirm
    but concern the redo log file it's contain data or log of data
    (contain insert statement or the data of insert statement)
    if it's contain data why the datafile size is more than redo log size ???


    thanks
  • 4. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    REDO will always be generated ... there is no way to not generate it. There are ways to minimize it and 99% of the time doing so saves very little time and puts the system at risk.

    I see nothing in what you have posted that justifies minimizing logging.

    As to why the volume of redo is larger than the increase in database size ... there are many possible reasons. Just one example ... with 8K blocks you update one byte in one row of one table.The size increase of your database is 0 but how much redo was generated? Now delete one row ... how much redo was generated? Now build a B*Tree index on the column where the change took place when one byte in one row was updated: How much redo? Now make that index a bitmap index: How much redo?

    The answer to the last question is ~4K. If anyone reading this can't answer these questions I suggest purchasing one of Jonathan Lewis' books ASAP.
  • 5. Re: nologging
    864103 Newbie
    Currently Being Moderated
    thanks sir , but as this link we can bypassed generate redo if your table in nologging and insert statement contain appeand hint
    http://www.dba-oracle.com/t_nologging_append.htm


    so please if you have articals that tell us there is no way to avaoid to generate redo (redo will generated always) i will appreciated.


    also just confirm the redo log and archive redo log files that contain the data block or the command of all change make to database
    in order if we apply recover it's roll forward vie insert the data again to datafile or apply the command ????


    till now i'm confusing concern the size of archive redo log file due to i'm apply insert to my database
    more than one it's take space from tablespace 30GB but it generate more than 80 GB ????


    i will be a high appreciated to all expert dba's here to share with me these 3 points???



    thanks in advanced
  • 6. Re: nologging
    864103 Newbie
    Currently Being Moderated
    any idea???
  • 7. Re: nologging
    rp0428 Guru
    Currently Being Moderated
    >
    so please if you have articals that tell us there is no way to avaoid to generate redo (redo will generated always) i will appreciated.

    also just confirm the redo log and archive redo log files that contain the data block or the command of all change make to database
    in order if we apply recover it's roll forward vie insert the data again to datafile or apply the command ????

    till now i'm confusing concern the size of archive redo log file due to i'm apply insert to my database
    more than one it's take space from tablespace 30GB but it generate more than 80 GB ????
    >
    See this AskTom blog (his first reply) where he shows you a table that explains when you do and when you do not generate redo for various combinations of logging/nologging, append/no append, etc.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869
    >
    It is even more deep then that. For example:

    Table Mode Insert Mode ArchiveLog mode result
    ----------- ------------- ----------------- ----------
    LOGGING APPEND ARCHIVE LOG redo generated
    NOLOGGING APPEND ARCHIVE LOG no redo
    LOGGING no append "" redo generated
    NOLOGGING no append "" redo generated
    LOGGING APPEND noarchive log mode no redo
    NOLOGGING APPEND noarchive log mode no redo
    LOGGING no append noarchive log mode redo generated
    NOLOGGING no append noarchive log mode redo generated
  • 8. Re: nologging
    marksmithusa Journeyer
    Currently Being Moderated
    damorgan wrote:
    REDO will always be generated ... there is no way to not generate it. There are ways to minimize it and 99% of the time doing so saves very little time and puts the system at risk.

    I see nothing in what you have posted that justifies minimizing logging.

    As to why the volume of redo is larger than the increase in database size ... there are many possible reasons. Just one example ... with 8K blocks you update one byte in one row of one table.The size increase of your database is 0 but how much redo was generated? Now delete one row ... how much redo was generated? Now build a B*Tree index on the column where the change took place when one byte in one row was updated: How much redo? Now make that index a bitmap index: How much redo?

    The answer to the last question is ~4K. If anyone reading this can't answer these questions I suggest purchasing one of Jonathan Lewis' books ASAP.
    As Darren says, NOLOGGING should only be used in very particular circumstances: for instance, if you're doing a huge load THAT YOU CAN RE-RUN from a system of record to an EDW or such. NOLOGGING is simply not worth the significant amount of potential recoverability you lose for the sake of....not really all that much.

    Also, if you have to restore the database to a point of time, you might end up having a hard time if you're recovering to a point in time where there was a NOLOGGING operation in progress (you might get lucky and have corrupted indexes, you might get unlucky and have corrupted data).

    Be also aware that your DATABASE force_logging setting might be turned on and that takes precedence over any tablespace or table level setting.
  • 9. Re: nologging
    864103 Newbie
    Currently Being Moderated
    thanks for all , now i get it

    nologging append archive mode no redo
    nologging append noarchive mode no redo
    logging append noarchive mode no redo
    but first must be force_logging set to no


    concern my second question i get it but not all

    when i insert data redo generated and delete data redo generated also but database size decrease .


    thanks
  • 10. Re: nologging
    864103 Newbie
    Currently Being Moderated
    it's seems you are right !!!!!!!!!!!
    SQL> alter table xdx logging;
    
    Table altered.
    
    SQL>
    SQL> insert into xdx
      2  select * from employees;
    
    107 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    redo size was increase ---
    
    
    SQL> alter table xdx nologging;
    
    Table altered.
    
    SQL> insert /*+ append */ into xdx
      2  select * from employees;
    
    107 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    
    
    also redo size was increase and generated.
    
    
    i use the below query to found the redo size
    
    
    SQL> select name,a.value
      2  from v$sesstat a, v$sysstat b
      3  where b.statistic#=a.statistic#
      4  and b.name = 'redo size' and sid = 22;
  • 11. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Anyone that relies on information from that site being accurate needs to learn how to use the internet.

    Start here:
    http://www.jlcomp.demon.co.uk/untested.html
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35336203098853
    Re: When to rebuild indexes

    then try this search on google:
    "Burleson and Kyte"

    Who are you going to believe? Tom Kyte? Jonathan Lewis? Richard Foote? the entire Oak Table membership? Essentially 100% of all Oracle ACE Directors? Oracle Corp.? Or something you found on the internet?
  • 12. Re: nologging
    damorgan Oracle ACE Director
    Currently Being Moderated
    Just to make sure everyone here knows how I work when I go on-site in a consulting relationship one of the first things I do after a database health check is the following:
    ALTER DATABASE FORCE LOGGING;
    I have done this on systems larger than most of you can imagine ever touching in your career. Not once have I found a need to disable it to improve performance.

    I highly recommend anyone thinking of utilizing NOLOGGING read Tom Kyte's numerous statements on the subject.
  • 13. Re: nologging
    marksmithusa Journeyer
    Currently Being Moderated
    damorgan wrote:
    Just to make sure everyone here knows how I work when I go on-site at in a consulting relationship one of the first things I do after a database health check is the following:
    ALTER DATABASE FORCE LOGGING;
    I have done this on systems larger than most of you can imagine ever touching in your career. Not once have I found a need to disable it to improve performance.

    I highly recommend anyone thinking of utilizing NOLOGGING read Tom Kyte's numerous statements on the subject.
    I agree. I've also done this on huge systems (maybe not as big as you, but Exadata DWs, highly OLTP databases, etc) and every single time we mention it before we implement it, the app owner gets panicked about impact on performance. Not once have I seen it cause an issue, but some managers are distrusting of it, especially those systems where performance is on a knife-edge.

    To avoid stress lately for everyone, we don't tend to bother informing anyone that we're switching this on anymore - if anyone ever asks, we just put it down as part of our operational responsibility that enables us to perform the most important part of our job.

    I also like your comments about Don Burleston. Our team basically ignores any Google search results that are from dba-oracle.com. Been burned too many times. I -think- I saw Tom Kyte use an example of a browser add-on which excluded certain domains from your search results - I think he used dba-oracle.come as an example, but I could be wrong :)
  • 14. Re: nologging
    sb92075 Guru
    Currently Being Moderated
    marksmithusa wrote:
    I also like your comments about Don Burleston. Our team basically ignores any Google search results that are from dba-oracle.com.
    127.0.0.1 localhost.localdomain localhost dba-oracle.com

    above can be used to ignore any site you wish to ignore
1 2 3 4 Previous Next

Legend

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