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.
Thursday, November 06, 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment