5 Replies Latest reply: Feb 8, 2013 3:14 AM by Hemant K Chitale RSS

    Trace "unintentional" nologging

    710463
      Hi,

      every time we do an rman clone from production we get


      ORA-01110: data file 13: '***.dbs'
      ORA-26040: Data block was loaded using the NOLOGGING option

      But there are no tables or indexes that have nologging switched on. We've checked the according v$ tables.

      We also checked that there are no Informatica ETL processes at the clone timestamp.

      Are there any scenarios that can unintentionally switch on nologging?

      Regards

      Edited by: ABauer on Feb 8, 2013 9:14 AM
        • 1. Re: Trace "unintentional" nologging
          sb92075
          ABauer wrote:
          Hi,

          every time we do an rman clone from production we get


          ORA-01110: data file 13: '***.dbs'
          ORA-26040: Data block was loaded using the NOLOGGING option

          But there are no tables or indexes that have nologging switched on. We've checked the according v$ tables.

          We also checked that there are no Informatica ETLat the clone timestamp.

          Are there any scenarios that can unintentionally switch on nologging?
          INSERT /*+ APPEND */
          • 2. Re: Trace "unintentional" nologging
            Iordan Iotzov
            You can also consider putting your production database in FORCED LOGGING mode.
            alter database force logging ;
            You will never have to worry about this problem again.


            Iordan Iotzov
            http://iiotzov.wordpress.com/
            • 3. Re: Trace "unintentional" nologging
              Karan
              It may happen for sure like when you insert /*+ append */ into table values ('abc'); and create index idx . . . nologging;


              To enable force logging:

              ALTER TABLESPACE users FORCE LOGGING;

              ALTER DATABASE force logging;

              The following operations can make use of the NOLOGGING option:

              direct load with SQL*Loader
              direct load INSERT (using APPEND)
              alter table...split partition
              alter table...move partition
              create table...as select
              create index
              alter index...split partition
              alter index...rebuild
              alter index...rebuild partition
              • 4. Re: Trace "unintentional" nologging
                Osama_Mustafa
                Refer to
                ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution [ID 794505.1]
                • 5. Re: Trace "unintentional" nologging
                  Hemant K Chitale
                  Are there any scenarios that can unintentionally switch on nologging?
                  No. NOLOGGING is a result of deliberate decisions.
                  For example,
                  Create a Table with NOLOGGING
                  Use Direct Path Insert into a NOLOGGING Table
                  Rebuild an Index with NOLOGGING

                  Oracle, by iteslf, doesn't execute NOLOGGING operations unless it is directed to.



                  Hemant K Chitale