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

    nologging

    864103
      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
          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
            JustinCave
            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
              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
                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
                  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
                    any idea???
                    • 7. Re: nologging
                      rp0428
                      >
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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