Thursday, November 06, 2008

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

After I recover my database, I found below error...
What's going on?

select count(*) from TEMP01;

*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 20)
ORA-01110: data file 4: '+DATA1/db/datafile/users.260.670078195'
ORA-26040: Data block was loaded using the NOLOGGING option

what is ORA-26040?
and this error can not solve by rman "BLOCKRECOVER".

If find out on internet you will find;

Error: ORA-26040
Text: Data block was loaded using the NOLOGGING option
---------------------------------------------------------------------------
Cause: Trying to access data in block that was loaded without
redo generation using the NOLOGGING/UNRECOVERABLE option

Action: Drop the object containing the block.

that's a idea to solve it, right!... Absolutely.

I asked myself How can I know before my database error like that.

This cause ... I use NOLOGGING mode, NOLOGGING mode which will prevent the table from being rolled forward after future changes are applied.

I haven't found ORA-26040 error (after test) when I insert/delete/update with NOLOGGING.

But I have found ... exactly, when I create new table with NOLOGGING.

EXAMPLE:

create table TEMP01 nologging as select * from all_objects;

How can I know, before datafiles fail and error ORA-26040.

Using rman to check by "report unrecoverable" command,
Anyway It'll be better when use rman to backup/recovery as well.

when I found this error, I will backup datafiles by rman.

SQL> create table TEMP01 nologging as select * from all_objects;

Table created.

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------------
TEMP01 USERS

$ rman target /

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4 full or incremental +DATA1/db/datafile/users.260.670078195

RMAN> backup datafile 4;
.
.
Finished backup

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

Not Found

I don't sure why i use insert/update/delete with NOLOGGING mode and not found error.

SQL> create table TEMP02 logging as select * from all_objects where rownum <=1 ; Table created.

SQL> insert into TEMP02 nologging select * from all_objects;

414 rows created.

SQL> commit;

Commit complete.

$ rman target /

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
Not Found

That make sure; If I need to leave from UNRECOVERABLE with creating new table, I should use LOGGING mode.

However, check... check and check database [use RMAN with "report unrecoverable" and backup ... backup] ... that make database leave from UNRECOVERABLE as well.

No comments: