By Franck Pachot
.
When you load data in direct-path and have the NOLOGGING attribute set, you minimize redo generation, but you take the risk, in case of media recovery, to loose the data in the blocks that you’ve loaded. So you probably run a backup as soon as the load is done. But what happens if you have a crash, with media failure, before the backup is finish?
I encountered recently the situation but – probably because of a bug – the result was not exactly what I expected. Of course, before saying that it’s a bug I need to clear any doubt about what I think is the normal situation. So I’ve reproduced the normal situation and I’m sharing it here in case someone wants to see how to handle it.
First, let me emphasize something that is very important. I didn’t say that you can loose the data that you’ve loaded. You loose the data which were in the blocks that have been allocated by your load. It may concern conventional DML happening long time after the nologging load. And anyway, you probably loose the whole table (or partition) because as you will see the proper way to recover from nologging recovery is to truncate the table (or partition).
I’m in 12c so I can run my SQL statements from RMAN. I create a DEMO tablespace and a 1000 rows table in it:
RMAN> echo set on
RMAN> create tablespace DEMO datafile '/tmp/demo.dbf' size 10M;
Statement processed
RMAN> create table DEMO.DEMO pctfree 99 tablespace DEMO nologging as select * from dual connect by level commit;
Statement processed
Imagine that I’ve a media failure and I have to restore my tablespace:
RMAN> alter tablespace DEMO offline;
Statement processed
RMAN> restore tablespace DEMO;
Starting restore at 04-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
creating datafile file number=2 name=/tmp/demo.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 04-SEP-14
and recover up to the point of failure:
RMAN> recover tablespace DEMO;
Starting recover at 04-SEP-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-SEP-14
RMAN> alter tablespace DEMO online;
Statement processed
Then here is what happen when I want to query the table where I’ve loaded data without logging:
RMAN> select count(*) from DEMO.DEMO;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/04/2014 16:21:27
ORA-01578: ORACLE data block corrupted (file # 2, block # 131)
ORA-01110: data file 2: '/tmp/demo.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Let’s see that:
RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 167 974 1280 6324214
File Name: /tmp/demo.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 306
Finished validate at 04-SEP-14
167 blocks have been marked as corrupt.
The solution is to truncate the concerned table.
And if you don’t know what are the tables that are concerned then you need to check v$database_block_corruption and dba_extents. So, my advise is that the tables loaded in NOLOGGING should be documented in the recovery plan, with the way to reload the data. Of course, that’s not an easy task because NOLOGGING is usually done by developers and recovery is done by the DBA. The other alternative is to prevent any NOLOGGING operation and put the database in FORCE LOGGING. In a Data Guard configuration, you should do that anyway.
So I truncate my table:
RMAN> truncate table DEMO.DEMO;
Statement processed
and if I check my tablespace, I still see the blocks as ‘Marked Corrupt’:
RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 167 974 1280 6324383
File Name: /tmp/demo.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 306
Finished validate at 04-SEP-14
This is the normal behaviour. The blocks are still marked as corrupt until they are formatted again.
I put back my data;
RMAN> insert /*+ append */ into DEMO.DEMO select * from dual connect by level commit;
Statement processed
And check my tablespace again:
RMAN> validate tablespace DEMO;
Starting validate at 04-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/tmp/demo.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 974 1280 6324438
File Name: /tmp/demo.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 167
Index 0 0
Other 0 139
Finished validate at 04-SEP-14
The 167 corrupted blocks have been reused, now being safe and containing my newly loaded data.
This is the point I wanted to validate because I’ve seen a production database where the blocks remained marked as corrupted. The load has allocated exents containing those blocks but, fortunately, has avoided to put rows in it. However, monitoring is still reporting corrupt blocks and we have to fix that as soon as we can move the tables to another tablespace.
Last point. If you want to see if some tablespace had NOLOGGING operations since the last backup, run:
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
2 full /tmp/demo.dbf
This is an indication that you should backup that datafile now. Knowing the objects concerned if a lot more complex…
I’ll not open a SR as I can’t reproduce the issue I encountered (corrupt flag remaining after reallocating blocks) but if anyone had that kind of issue, please share.