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

测试如下：

SQL> create table testerr
  2  as
  3  select * from dba_objects where 1=2;
 
Table created
 
SQL> alter table testerr nologging;
 
Table altered

然后进行备份，然后。。。

SQL> insert /*+ append */ into testerr
  2  select * from dba_objects;
 
49867 rows inserted

然后还原，recovery，然后查询即可

 

ORA-01578: ORACLE data block corrupted (file # 5, block # 91202)
ORA-01110: data file 5: '/oradata/xuexi/XUEXI/datafile/o1_mf_tomf_8yqwrdoj_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

DBV检查

DBVERIFY - Verification complete

Total Pages Examined         : 94208
Total Pages Processed (Data) : 90268
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 112
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3438
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 390
Total Pages Marked Corrupt   : 2740
Total Pages Influx           : 0
Highest block SCN            : 1248242 (0.1248242)

所以准备做库迁移的时候，如果不知道数据库是否经历NOGGLING的导入，事先在备份前做一次ALTER DATABASD  FORCE LOGGING；

同时

select * from v$datafile;的字段

UNRECOVERABLE_CHANGE# NUMBER Last unrecoverable change number made to this datafile. If the database is
in ARCHIVELOG mode, then this column is updated when an
unrecoverable operation completes. If the database is not in ARCHIVELOG
mode, this column does not get updated.
UNRECOVERABLE_TIME DATE Timestamp of the last unrecoverable change. This column is updated only
if the database is in  ARCHIVELOG mode.

确认是否有不可恢复的操作

Identify the affected segment as described in Note 819533.1, then:

• If it is an INDEX, drop/create the index
• If it is a TABLE then procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements and decide to re-create the table. Note 556733.1 has a DBMS_REPAIR example.
• If it is a LOB segment associated to a LOB column in a Table, use Note 293515.1
• If the error is produced in a Physical STANDBY database, the option is to restore the affected file from the PRIMARY database (only if the problem is not present in the PRIMARY).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-767676/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-767676/