Blog - comments

hi i am also facing the same issue please tell me how u resolved this.

sandeep singh

How you resolved this

sandeep singh
Hi Nag,Thanks for your comment. Do you have a problem when creating the procedure or when executing ...

Yes it is not line with that statement but I didn't write the first quote ;)

Anton Ivanovitch

thank you very much. It was like a walk in the park

Blog Franck Pachot The consequences of NOLOGGING in Oracle

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

The consequences of NOLOGGING in Oracle

While answering to a question on Oracle forum about NOLOGGING consequences, I provided a test case that deserves a bit more explanation. Nologging operations are good to generate minimal redo on bulk operations (direct-path inserts, index creation/rebuild). But in case we have to restore a backup that was made before the nologging operation, we loose data. And even if we can accept that, we have some manual operations to do.

Here is the full testcase.


I create a tablespace and backup it:

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=30 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_TAG20140323T160453_9lxy0pfb_.bkp tag=TAG20140323T160453 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-MAR-14



I create a table and an index, both in NOLOGGING

RMAN> create table demo ( dummy not null ) tablespace demo nologging as select * from dual connect by level Statement processed

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


Note how I like 12c for doing anything from RMAN...

Because I will need it later, I do a treedump of my index:

RMAN> begin
2>  for o in (select object_id from dba_objects where owner=user and object_name='DEMO' and object_type='INDEX')
3>   loop execute immediate 'alter session set tracefile_identifier=''treedump'' events ''immediate trace name treedump level '||o.object_id||'''';
4> end loop;
5> end;
6> /
Statement processed


Here is the content of my treedump trace file:

----- begin tree dump
branch: 0x140008b 20971659 (0: nrow: 2, level: 1)
   leaf: 0x140008c 20971660 (-1: nrow: 552 rrow: 552)
   leaf: 0x140008d 20971661 (0: nrow: 448 rrow: 448)
----- end tree dump


Because of the nologging, the tablespace is 'unrecoverable' and we will see what it means.

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 tells me that I need to do a backup, which is the right thing to do after nologging operations. But here my goal is to show what happens when we have to restore a backup that was done before the nologging operations.

I want to show that the issue does not only concern the data that I've loaded, but any data that may come later in the blocks that have been formatted by the nologging operation. So I'm deleteing the rows and inserting a new one.

2> delete from demo;
Statement processed

RMAN> insert into demo select * from dual;
Statement processed


Time to restore the tablespace from the backup that has been done before the nologging operation:

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_TAG20140323T160453_9lxy0pfb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/U1/backupset/2014_03_23/o1_mf_nnndf_TAG20140323T160453_9lxy0pfb_.bkp tag=TAG20140323T160453
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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> alter tablespace demo online;
Statement processed


We can check the unrecoverable tablespace

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


but we don't know which objects are concerned until we try to read from them:

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 16:05:03
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;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/23/2014 16:05:04
ORA-01578: ORACLE data block corrupted (file # 5, block # 140)
ORA-01110: data file 5: '/tmp/demo.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


So I can't read from the table because of block (file # 5, block # 131) which is corrupted and I can't read from the index because of block (file # 5, block # 140) which is corrupted. The reason is that recovery was not possible on them as there was no redo to protect them from the time they were formatted (by the nologging operation).

Let's see which blocks were reported:

RMAN> select segment_type,header_file,header_block , dbms_utility.make_data_block_address(header_file,header_block) from dba_segments where owner=user and segment_name='DEMO';
------------------ ----------- ------------
INDEX                        5          138
TABLE                        5          130
RMAN> select dbms_utility.make_data_block_address(5, 140) from dual;



The full scan failed as soon as it reads the block 131 which is the first one that contains data. The segment header block itself was protected by redo.

For the index the query failed on block 140 which is the first leaf (this is why I did a treedump above). The root branch (which is always the next after the segment header) seem to be protected by redo even for nologging operation. The reason why I checked that is because in the first testcase I posted in the forum, I had a very small table for which the index was so small that it had only one leaf - which is the root branch as well - so the index was still recovrable.

The important point to know is that the index is still valid:

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


And the only solution is to truncate the table:

RMAN> truncate table demo;
Statement processed

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

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


no corruption anymore, but no data either...

Last point: if you have only the indexes that are unrecoverable, you can rebuild them. But because the index is valid, Oracle will try to read it in order to rebuild it - and fail with ORA-26040. You have to make then unusable before.

The core message is:

  • Use nologging only when you accept to loose data and you accept to have some manual operations to do after recovery (so document it): truncate table, make indexes unusable and rebuild.
  • Backup the unrecoverable tablespaces as soon as you can after your nologging operations
  • If you need redo for other goals (such as standby database) use force logging.
Tagged in: Oracle Recovery
Rate this blog entry:

I'm a Senior Consultant, and Oracle Technology Leader at dbi services (Switzerland).
Certified DBA (OCM 11g, OCP 12c, Performance Tuning Expert, Exadata Implementation) I cover all database areas: architecture, data modeling, database design, tuning, operation, and training.
My preferred area is troubleshooting oracle and performance tuning, especially when I acheive to enable an efficient collaboration between the developers and the
operational team.

As an Oracle Ace, I participate in the Oracle Community in forums, blogs, articles and presentation. You can follow my activity on this blog: RSS and my twitter account: @FranckPachot






  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Friday, 03 July 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter