By Franck Pachot

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.