1 2 Previous Next 17 Replies Latest reply: Mar 24, 2014 5:26 AM by Franck Pachot RSS

    Confusion about nologging

    d_seng

      Hi all,


      We have a data warehouse (running in ARCHIVELOG mode) where approx. 75% of the tables (by volume and number) are transient staging tables. The data in these tables can be lost without any regret, as only the data from the DWH dimension and fact tables are referred to in subsequent refreshes. Of course, we need these staging tables/indexes to exist for a successful run.


      At the moment all tables are LOGGING, therefore our archivelogs are (I think unnecessarily) huge and our restore (tested on a non-production database) takes an awfully long time. We are looking for ways to reduce both.


      If we switch the staging tables to NOLOGGING mode and take a cold backup, these objects will be included in the backup. However, afterwards if we change the structures of any of these tables subsequently or create new tables (in NOLOGGING mode) or create new indexes on the new tables, etc., will these objects be backed up into the archive logs? Therefore, if we later restore from the latest cold backup and apply the archive logs, will we get back these table/index definitions (not the data, of course)?


      The confusion is stemming from the reasoning that all data dictionary operations are logged, regardless of whether the objects and the database are in logging mode. I'm not sure if data dictionary operations include ALTER TABLE ADD COLUMN, CREATE TABLE, CREATE INDEX, etc.


      Unfortunately I don't have the setup or the skills to test this (I'm not exactly a DBA).

       

      Thanks in advance.


        • 1. Re: Confusion about nologging
          Franck Pachot

          Hi,

          Yes, no problem. the only issue with tables in nologging is that all the blocks that have been loaded in direct-path since the last backup are not protected, so they are marked as corrupt if you restore them.

          If you don't care about the data, then just truncate the tables.

          Note that you are talking about cold backup. As you are in archivelog mode you can do hot backups.

          Regards,

          Franck.

          • 2. Re: Confusion about nologging
            d_seng

            Nice point about blocks being marked as corrupt. Will a simple truncate do the trick?

             

            In fact after the restore, is there a way to identify such tables using the data dictionary or something? This will help to script the truncate/rebuild (or whatever is required) for such tables.

            • 3. Re: Confusion about nologging
              mbobak

              Yes, a simple truncate should clean things up, if you have corruption due to restore from a backup taken before NOLOGGING operations occurred.  (If this the source of the corruption, you should see ORA-26040 in the error stack.)

               

              A few things to keep in mind if you set NOLOGGING:

               

              1.)  Setting NOLOGGING at the tablespace level will have *no* effect on existing segments, it will only change the default for segments created in that tablespace to NOLOGGING.

              2.)  Changes to table structure will *always* log.  (alter table add column/modify column, etc)

              3.)  Only direct load DML (i.e.  insert with APPEND or APPEND_VALUES) will be NOLOGGING.  Deletes and updates will *always* log.

              4.)  Any time you do a NOLOGGING load, your exposure to impact on recovery only lasts until the next backup.  If you recover from a backup subsequent to the most recent NOLOGGING load, you'll be fine.

              5.)  If you do have corruption due to recovery from backup taken previous to NOLOGGING load (ORA-26040), then truncating the table and reloading the data will take care of it.  For indexes, just do 'alter index ... rebuild'.

              6.)  If you have force logging set to YES, (only likely if you're doing DataGuard), then everything will always log, as if NOLOGGING was never set.  (You can check with 'select force_logging from v$database'.)

               

              Hope that helps,

               

              -Mark

              • 4. Re: Confusion about nologging
                d_seng

                Thanks for the insight Mark.

                 

                Please could you also comment on my question in the previous post, will the nologging tables/index that have been restored have a status of INVALID (or some other indicator)? This will help me to write a script to truncate/rebuild only those objects.

                 

                Please forgive me, I am not a DBA and have not actually done a backup/restore hands on.

                 

                Thanks!

                • 5. Re: Confusion about nologging
                  rp0428
                  Please could you also comment on my question in the previous post, will the nologging tables/index that have been restored have a status of INVALID (or some other indicator)? This will help me to write a script to truncate/rebuild only those objects.

                  That question doesn't make any sense.

                   

                  Your need to write a script to 'truncate/rebuild only those objects' doesn't make any sense either.

                   

                  As already pointed out by others there are more things in your post that don't make any sense either

                  We have a data warehouse (running in ARCHIVELOG mode)

                  . . .

                  Therefore, if we later restore from the latest cold backup and apply the archive logs,

                  . . .

                   

                  Why would you be doing cold backups and restores if you are running in ARCHIVELOG mode?

                   

                  You should be using RMAN to perform the backups that you need.

                   

                  See the SQL Language doc section for the 'logging clause' for details of what gets logged for both DML and DDL.

                  http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses005.htm

                  Unfortunately I don't have the setup or the skills to test this (I'm not exactly a DBA).

                  Nothing wrong with that. But I assume you understand that someone that DOES have those skills needs to design and test this?

                   

                   

                   

                   

                  • 6. Re: Confusion about nologging
                    d_seng

                    That question doesn't make any sense.

                    Your need to write a script to 'truncate/rebuild only those objects' doesn't make any sense either.

                    Makes perfect sense if you read my posts carefully

                     

                    As already pointed out by others there are more things in your post that don't make any sense either

                    Who pointed out what?


                    Why would you be doing cold backups and restores if you are running in ARCHIVELOG mode?

                    The baseline backup is a cold backup, that's why


                    Nothing wrong with that. But I assume you understand that someone that DOES have those skills needs to design and test this?

                    I can only assume that you are highly skilled, good for you.

                     

                    Now if you have nothing productive to add, stay away.


                    • 7. Re: Confusion about nologging
                      Franck Pachot

                      Hi,

                       

                      You cannot see that in the index status. I'ts only ORA-01578: ORACLE data block corrupted  and ORA-26040: Data block was loaded using the NOLOGGING option.

                      But if you want to check the tablespaces that are subject to that issue you can do it from RMAN:

                      RMAN> report unrecoverable;

                      or from v$datafile which has unrecoverable operation.

                       

                      I suggest that you start your staging process with a truncate, so that you always start with something clean.

                       

                      Regards,

                      Franck;

                      • 8. Re: Confusion about nologging
                        d_seng

                        Yes, our staging processes already start with truncate, so just need to add the rebuild of indexes, that's very pragmatic and easy to achieve. Thanks for the tip!

                         

                        Of course I'll create a test case and come back to you if I can add anything.

                        • 9. Re: Confusion about nologging
                          Franck Pachot

                          Hi,

                           

                          For the indexes that are on the tables that you truncate, you don't need to rebuild.

                           

                          Here is a full test case:

                           

                          RMAN>

                          echo set on

                           

                           

                          RMAN> create tablespace demo datafile '/tmp/demo.dbf' size 10M;

                          Statement processed

                           

                           

                           

                           

                          RMAN> backup tablespace demo;

                          Starting backup at 23-MAR-14

                          allocated channel: ORA_DISK_1

                          channel ORA_DISK_1: SID=27 device type=DISK

                          channel ORA_DISK_1: starting full datafile backup set

                          channel ORA_DISK_1: specifying datafile(s) in backup set

                          input datafile file number=00005 name=/tmp/demo.dbf

                          channel ORA_DISK_1: starting piece 1 at 23-MAR-14

                          channel ORA_DISK_1: finished piece 1 at 23-MAR-14

                          piece handle=/u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010341_9lw96xw5_.bkp tag=TAG20140323T010341 comment=NONE

                          channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

                          Finished backup at 23-MAR-14

                           

                           

                           

                           

                          RMAN> create table demo ( dummy not null ) tablespace demo nologging as select * from dual;

                          Statement processed

                           

                           

                           

                           

                          RMAN> create index demo on demo(dummy) tablespace demo nologging;

                          Statement processed

                           

                           

                           

                           

                          RMAN> report unrecoverable;

                          Report of files that need backup due to unrecoverable operations

                          File Type of Backup Required Name

                          ---- ----------------------- -----------------------------------

                          5    full or incremental     /tmp/demo.dbf

                           

                           

                           

                           

                          RMAN> select count(*) from demo;

                            COUNT(*)

                          ----------

                                   1

                           

                           

                           

                           

                          RMAN> alter tablespace demo offline;

                          Statement processed

                           

                           

                           

                           

                          RMAN> restore tablespace demo;

                          Starting restore at 23-MAR-14

                          using channel ORA_DISK_1

                           

                           

                          channel ORA_DISK_1: starting datafile backup set restore

                          channel ORA_DISK_1: specifying datafile(s) to restore from backup set

                          channel ORA_DISK_1: restoring datafile 00005 to /tmp/demo.dbf

                          channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010341_9lw96xw5_.bkp

                          channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010341_9lw96xw5_.bkp tag=TAG20140323T010341

                          channel ORA_DISK_1: restored backup piece 1

                          channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

                          Finished restore at 23-MAR-14

                           

                           

                           

                           

                          RMAN> recover tablespace demo;

                          Starting recover at 23-MAR-14

                          using channel ORA_DISK_1

                           

                           

                          starting media recovery

                          media recovery complete, elapsed time: 00:00:00

                           

                           

                          Finished recover at 23-MAR-14

                           

                           

                           

                           

                          RMAN> report unrecoverable;

                          Report of files that need backup due to unrecoverable operations

                          File Type of Backup Required Name

                          ---- ----------------------- -----------------------------------

                          5    full or incremental     /tmp/demo.dbf

                           

                           

                           

                           

                          RMAN> alter tablespace demo online;

                          Statement processed

                           

                           

                           

                           

                          RMAN> select /*+ full(demo) */ count(*) from demo;

                          RMAN-00571: ===========================================================

                          RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

                          RMAN-00571: ===========================================================

                          RMAN-03002: failure of sql statement command at 03/23/2014 01:03:49

                          ORA-01578: ORACLE data block corrupted (file # 5, block # 131)

                          ORA-01110: data file 5: '/tmp/demo.dbf'

                          ORA-26040: Data block was loaded using the NOLOGGING option

                           

                           

                           

                           

                          RMAN> select /*+ index(demo) */ count(*) from demo;

                            COUNT(*)

                          ----------

                                   1

                           

                           

                           

                           

                          RMAN> select status from all_indexes where index_name='DEMO';

                          STATUS

                          --------

                          VALID

                           

                           

                           

                           

                          RMAN> truncate table demo;

                          Statement processed

                           

                           

                           

                           

                          RMAN> select /*+ full(demo) */ count(*) from demo;

                            COUNT(*)

                          ----------

                                   0

                           

                           

                           

                           

                          RMAN> select /*+ index(demo) */ count(*) from demo;

                            COUNT(*)

                          ----------

                                   0

                           

                           

                           

                           

                          RMAN> backup tablespace demo;

                          Starting backup at 23-MAR-14

                          using channel ORA_DISK_1

                          channel ORA_DISK_1: starting full datafile backup set

                          channel ORA_DISK_1: specifying datafile(s) in backup set

                          input datafile file number=00005 name=/tmp/demo.dbf

                          channel ORA_DISK_1: starting piece 1 at 23-MAR-14

                          channel ORA_DISK_1: finished piece 1 at 23-MAR-14

                          piece handle=/u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010350_9lw976ph_.bkp tag=TAG20140323T010350 comment=NONE

                          channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

                          Finished backup at 23-MAR-14

                           

                           

                           

                           

                          RMAN> delete noprompt backup of tablespace demo;

                          using channel ORA_DISK_1

                           

                           

                          List of Backup Pieces

                          BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

                          ------- ------- --- --- ----------- ----------- ----------

                          24      24      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010341_9lw96xw5_.bkp

                          25      25      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010350_9lw976ph_.bkp

                          deleted backup piece

                          backup piece handle=/u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010341_9lw96xw5_.bkp RECID=24 STAMP=842922221

                          deleted backup piece

                          backup piece handle=/u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T010350_9lw976ph_.bkp RECID=25 STAMP=842922230

                          Deleted 2 objects


                          Regards,

                          Franck.

                          • 10. Re: Confusion about nologging
                            rp0428

                            What I said was productive. It's not my fault if you can't recognize that. Trying to blame the messenger isn't going to help get your problem solved.

                             

                            You haven't explained CLEARLY why you need a script to 'rebuild only those objects'.

                             

                            After you use TRUNCATE there is NO data. By extension that means that NONE of the indexes have any 'data' either.

                             

                            Which means that ALL indexes WILL be rebuilt. You can either let Oracle do it as part of the load process (and potentially impact the load performance) or YOU can do the rebuild AFTER the load.

                             

                            When YOU do the rebuild you get to control the ORDER in which the indexes are rebuilt as well as more control over the actual process (e.g. parallel) used for the rebuild.

                             

                            The 'best' plan depends on the numbers and types of indexes (which, as I previously said, you have NOT provided us), the number of rows in the table, the types of columns being indexed and the speed with which the table and some 'minimum' set of indexes needs to be back online and available to the users.

                             

                            For many use case a small number of 'key' indexes are rebuilt offline and then the users are allowed back on the system. Then the remaining indexes are rebuilt online.

                            • 11. Re: Confusion about nologging
                              d_seng

                              Thanks a lot for the test case Franck, much appreciated.

                               

                              The only point that I am curious about is that when you ran "select /*+ index(demo) */ count(*) from demo;" right after the restore but before the truncate, it did not generate an error. I would have thought that the index should have been marked as corrupt as well?

                               

                              If you check this out: Benefits and consequences of the NOLOGGING option | Oracle Examples, you'll note that the last table table_ctas_index_nologging was created in logging mode, while the index on it was created in nologging mode. When he tried to use the index (select object_id from table_ctas_index_nologging  where object_id=1;) after recovery, it generated an error ORA-01578.

                               

                              He also says "I tried to rebuild the index but I was still getting the same error message, at the end I was forced to drop it and recreate it.", which might be because of the DB Version (10.2.0.4)?

                               

                              Not that it matters too much, but just trying to understand what the underlying difference is here...

                              • 12. Re: Confusion about nologging
                                Franck Pachot

                                I'll will check my testcase. you're right the select using index should return an error. When I've run the test the first time I checked that I had the error and that the block reported corrupt is the index block. Then I re-run the test to copy-paste and didn't check. I'll try to understand what happened

                                 

                                About having only the index in nologging, the problem is that when you rebuild an index that is not unusable, then Oracle reads the index data in order to rebuild. So it reads corrupt blocks. You need to force it to read the table instead of the index, so you have to manually make it unusable (or drop-create as in the example).

                                • 13. Re: Confusion about nologging
                                  Franck Pachot

                                  I got the problem with the test case I posted above . The table had only one row, so the index had only one block leaf=branch=root. And it seems that the index root branch is always protected by redo even in nologging operation. The same testcase with a larger table will show the error when reading from the index. I^ve posted the full testcase on the dbi services blog: http://www.dbi-services.com/index.php/blog/entry/the-consequences-of-nologging-in-oracle

                                  • 14. Re: Confusion about nologging
                                    Aman....

                                    Excellent post Franck, thanks for sharing it .

                                     

                                    Regards

                                    Aman....

                                    1 2 Previous Next