9 Replies Latest reply: Dec 21, 2012 1:02 AM by JMOHIT RSS

    nologging

    user13549752
      DB:11.2.0.3


      can we set nologging at database level or we have to set it at user level and object level.
        • 1. Re: nologging
          Mark D Powell
          First of all the nologging feature is of limited use. Only certain operations such as direct loads (sqlldr, CTAS) can be performed in a nologging mode so it is basically a statement level feature. Second if you use a physical standby database you cannot perform nologging operations since you need a log record of all changes to update the standby with so the DBA should have diabled the feature at the database level.

          What is your intent for use of the nologging feature?
          Do you use Data Guard?
          Do the target tables for you nologging operations have table row triggers?

          HTH -- Mark D Powell --
          • 2. Re: nologging
            Fran
            you can do it a database or table level, but you must think too much if you need it. It isn't commendable.
            SQL> alter table fran.test1 nologging;
            
            Tabla modificada.
            
            SQL> alter database no force logging;
            
            Base de datos modificada.
            • 3. Re: nologging
              Mark D Powell
              Here are some Oracle support documents on the subject. The one on Data Guard has the command to allow and to disallow nologging at the database level examples.

              Nologging Table Still generating Lots Of Archive Logs [ID 976722.1]

              SQL*Loader - Using the NOLOGGING and UNRECOVERABLE options [ID 160092.1]

              The Gains and Pains of Nologging Operations in a Data Guard Environment [ID 290161.1]

              HTH -- Mark D Powell --
              • 4. Re: nologging
                user13549752
                its a dev database . one of the user ISTGD do lot of insert, updated and delete transactions . Since these transactions are temporary it will be much beneficial if i can enable nologging for ISTGD user. Can it be possible.
                • 5. Re: nologging
                  Mark D Powell
                  Even if you create a table with the nologging attribute all following normal DML operations will be logged. Only if the code uses direct load operations like insert /*+ APPEND */ will a nologging operation be performed. See the officail documentation for nologging:

                  http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables002.htm#ADMIN11629

                  HTH -- Mark D Powell --
                  • 6. Re: nologging
                    user13549752
                    can we put entire database in nologging mode
                    • 7. Re: nologging
                      sb92075
                      user13549752 wrote:
                      can we put entire database in nologging mode
                      NO!

                      consider using OS flat file instead of Oracle.
                      • 8. Re: nologging
                        jgarry
                        In some situations, it makes sense to put the db into noarchivelog mode. Logging still happens, but the archiving of the logs doesn't, which can be a significant speed up and space savings. Nothing in the database can be recovered, so be sure that is ok with everyone. This is often acceptable for developers, since they have to be able to re-do everything from scratch anyways.

                        The general rule is to always be recoverable for production, and even development is someone's production. The trick is to understand how and when to use restoration and recovery, and get everyone to agree (and especially don't let this be a top-down management decree).

                        One of the reasons to have multiple database is severely different recovery requirements.
                        • 9. Re: nologging
                          JMOHIT
                          SQL> alter database no force logging;