Oracle 由于NOLOGGING产生的 ORA-1578 / ORA-26040损坏块 – 错误说明和解决方案 ORA-01578: ORACLE 数据块损坏 (文件号 , 块号 ) ORA-26040: 数据块是使用 NOLOGGING 选项加载的

Posted by PDSERVICE on May 28, 2016 In

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

ORA-01578: ORACLE 数据块损坏 (文件号 , 块号 )

ORA-26040: 数据块是使用 NOLOGGING 选项加载的

适用于：

Oracle Database – Enterprise Edition – 版本 7.1.6.0 到12.1.0.2 [Release 7.1.6 to 12.1]
本文信息适用于任何平台。

目的

本文旨在描述Oracle如何报告由NOLOGGING 操作导致的损坏且如何修复错误。

范围

本文用于Customers and Oracle Support.

详情

当用NOLOGGING 属性定义一个段，且如果NOLOGGING/UNRECOVERABLE 操作更新了段，在之后执行恢复时，联机重做日志文件以最少的信息被更新来验证受影响的块。

如果相关的重做/归档日志文件用于恢复数据文件，Oracle 使这种块无效（invalidate）且在下一个块读中SQL语句报告错误ORA-26040 以及错误ORA-1578。

错误示例：

SQL> select * from test_nologging;

ORA-01578: ORACLE data block corrupted (file # 11, block # 84)
ORA-01110: data file 4: ‘/oradata/users.dbf’
ORA-26040: Data block was loaded using the NOLOGGING option

NOLOGGING 属性被储存在如下的数据字典中的列LOGGING中：

DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS, etc.

LOGGING=’NO’ indicates NOLOGGING.

The block is then marked as Soft Corrupt meaning that the next block read will report the ORA-1578/ORA-26040 errors.

RMAN/DBV 和由于NOLOGGING的损坏块

DBV 在10.2.0.4以下的rdbms 版本打印出generic信息 DBV-200 ，在10.2.0.4及以上版本打印出错误DBV-201 。 Doc ID 5031712.8:

DBV-00200: Block, dba 46137428, already marked corrupted
DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application

“VALIDATE” RMAN 命令用于识别NOLOGGING块并填充视图v$database_block_corruption （12c以下版本）和v$nonlogged_block （12c及以上）。在下一个示例中，验证输出报告数据文件4中标记为损坏的933 块，然后v$database_block_corruption 或v$nonlogged_block 会通过以下信息被更新：

RMAN> VALIDATE DATABASE;
…
…..
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 OK 933 1 6401 2275124
File Name: /oracle/dbs/users.dbf

在10.2.0.5 及以下和11.1.0.7的版本中，RMAN VALIDATE报告它，generic信息如下：

10.2.0.4 and lower, 11.1.0.6, 11.1.0.7:

The VALIDATE RMAN command reports the NOLOGGING blocks v$database_block_corruption with CORRUPTION_TYPE=LOGICAL

在版本10.2.0.5 或11.2.0.1及以上，RMAN 被强化，报告 CORRUPTION_TYPE=NOLOGGING。参考 Doc ID 7396077.8 :

10.2.0.5 and 11.2.0.1+:

The VALIDATE RMAN command reports the NOLOGGING blocks in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING

在版本12c及以上，RMAN VALIDATE不再填充视图v$database_block_corruption；而新视图v$nonlogged_block 被更新：

12c:

RMAN validate reports it in v$nonlogged_block

由于NOLOGGING损坏块，RMAN备份不会失败。一般来说，RMAN不由于软损坏块而失败，所以在这种情况下MAXCORRUPT子句是不必要的。在这种情况下，备份将包含软损坏块且当一个还原将保持损坏为备份创建时的情况。

当有除了错误ORA-26040以外的generic信息，可以进行块dump并查看字节0xff是否与块相关或块是否与一个段有关，尝试用SQL语句来读取会生成错误ORA-1578/ORA-26040，因为以NOLOGGING操作进行恢复导致了块损坏。

监控NOLOGGING操作

当一个数据文件被NOLOGGING 操作更改，RMAN 命令“REPORT UNRECOVERABLE” 报告且从此以后数据文件不再被备份。例如：

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 /oracle/dbs/users.dbf

当执行NOLOGGING 操作时，V$DATAFILE有多列被更新且参数db_unrecoverable_scn_tracking 被设为true （默认值）：db_unrecoverable_scn_tracking在10g中不可用。在我们的Oracle Database Reference Documentation 中参考下一个V$DATAFILE列：

UNRECOVERABLE_CHANGE#
UNRECOVERABLE_TIME
FIRST_NONLOGGED_SCN
FIRST_NONLOGGED_TIME

在11.2.0.4或12.1.0.2+中，当事件16490 被设为级别1时，在Standby Database中的MRP能在警告日志中报告NOLOGGING更改。警告日志被更新，显示这些信息：

ORA-16490 “logging invalidated blocks on standby due to invalidation redo”

“INVD_BLKS: Invalidating (file <file number>, bno <block number>)”
“fname: ‘Datafile name’. rdba: …”

识别块何时被标记为NOLOGGING

要识别块何时被标记为NOLOGGING，使用跟踪文件中的块scn 或使用v$database_block_coruption中的列 CORRUPTION_CHANGE# 将其翻译为一个时间戳：

使用跟踪文件的块scn

跟踪文件示例：

Start dump data blocks tsn: 60 file#: 4 minblk 84 maxblk 84
buffer tsn: 3 rdba: 0x02c00054 (11/84)
scn: 0x0771.4fa24eb5 seq: 0xff flg: 0x04 tail: 0x4eb500ff

取0x0771.4fa24eb5，删除‘.’ 并将0x07714fa24eb 转换为十进制，即511453045995

使用v$database_block_coruption 中列CORRUPTION_CHANGE# 中的值

如果RMAN VALIDATE在运行，视图v$database_block_coruption 以corruption_type=’NOLOGGING’ (10.2.0.5 和 11.2.0.1+)填充且列CORRUPTION_CHANGE# 有十进制 scn 值。

获取SCN 时间戳

要获取时间戳，使用以下任一方法：

select scn_to_timestamp(&&decimal_scn)
from dual;

如果RMAN VALIDATE在运行：

select file#, block#, scn_to_timestamp(CORRUPTION_CHANGE#)
from v$database_block_corruption
where CORRUPTION_TYPE=’NOLOGGING’;

在12c中：

select file#, block#, scn_to_timestamp(NONLOGGED_START_CHANGE#)
from v$nonlogged_block;

如果生成错误ORA-08181，查询gv$archived_log 或gv$log_history来获取时间间隔近似值：

alter session set nls_date_format = ‘DD-MON-YY HH24:MI:SS’;

select first_time, next_time
from gv$archived_log
where &decimal_scn between first_change# and next_change#;

或

select first_time
from gv$log_history
where &decimal_scn between first_change# and next_change#;

如果RMAN validate在运行：

alter session set nls_date_format = ‘DD-MON-YY HH24:MI:SS’;

select file#, block#, first_time, next_time
from v$archived_log, v$database_block_corruption
where CORRUPTION_CHANGE# between first_change# and next_change#
and CORRUPTION_TYPE=’NOLOGGING’;

或

select file#, block#, first_time
from v$log_history, v$database_block_corruption
where CORRUPTION_CHANGE# between first_change# and next_change#
and CORRUPTION_TYPE=’NOLOGGING’;

在12c中：

alter session set nls_date_format = ‘DD-MON-YY HH24:MI:SS’;

select file#, block#, first_time, next_time
from v$nonlogged_block, v$archived_log
where NONLOGGED_START_CHANGE# between first_change# and next_change#;

或

select file#, block#, first_time
from v$nonlogged_block, v$log_history
where NONLOGGED_START_CHANGE# between first_change# and next_change#;

SYSAUX 表空间中的NOARCHIVELOG 和NOLOGGING / AWR, EM，等

如果数据库在 rdbms 版本11.1.0.6或 11.1.0.7或11.2.0.1上运行，在NOARCHIVELOG 模式数据库手动恢复数据库之后，即使数据库中启用了FORCE LOGGING 或即使对段定义了LOGGING，由于DIRECT PATH的NOLOGGING，可以会生成ORA-1578 和ORA-26040

这在储存在SYSAUX表空间中的AWR 或EM 对象(Enterprise Manager) 中最常见。

参考 Doc Id 1071869.1 获取更多详情和解决方案。注意数据库当前可能在11.1以上或 11.2.0.1版本，但在从这些受影响版本升级时可能会引发问题。

该限制已在11.2.0.2+中被取消，且在10g中并没有出现这个问题。

根据RDBMS 版本更改

RDBMS 版本	更改
10.2.0.4+	DBverify报告NOLOGGING 块以及错误“DBV-00201: Block, DBA <rdba> , marked corrupt for invalid redo application”
10.2.0.5, 11.2.0.1+	RMAN validate在 v$database_block_corruption 中报告NOLOGGING块，以及 corruption_type=’NOLOGGING’
11g+	参数 db_unrecoverable_scn_tracking 被引入。
11.1.0.6, 11.1.0.7, 11.2.0.1	在NOARCHIVELOG 模式数据库手动恢复数据库之后，即使启用了FORCE LOGGING 由于DIRECT PATH的NOLOGGING，可以会生成ORA-1578 和ORA-26040。该限制已在11.2.0.2+中被取消，且在10g中并没有出现这个问题。
11.2.0.4, 12.1.0.2+	当事件16490被设为级别1，Managed Physical Standby Recovery (MRP) 能在警告日志中报告NOLOGGING 更改。警告日志被以下信息更新： ORA-16490 “logging invalidated blocks on standby due to invalidation redo” 这种改善由 Bug 16319519的修复引入
12c	RMAN validate 不再填充视图v$database_block_corruption；而新视图v$nonlogged_block 被更新

解决方案

注意受影响块中的数据是不可拯救的。类似“Media Recovery”或 “RMAN blockrecover”的方法无法修复问题，除非数据文件在NOLOGGING操作记录在重做日志后被备份。

Is error after RMAN DUPLICATE?

If the error is after a RMAN DUPLICATE or RESTORE, enable FORCE LOGGING at SOURCE database and perform the DUPLICATE or RESTORE (after new BACKUP) steps again:

alter database force logging;

Is error produced in a PHYSICAL STANDBY Database?

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) and to avoid the problem from being introduced there is the option to force logging in the PRIMARY database with:

alter database force logging;

In order to resolve the errors and if it is not an INDEX the segment can be recovered from a backup like an export dump or from another source. If backups are not available the segment might be recreated following the next steps:

Identify the affected segment

Identify the affected segment as described in Doc ID 819533.1 or identify all the corrupt objects as described in Doc ID 472231.1, then:

Is it a FREE Block?

If the NOLOGGING Block is a FREE Block (the associated extent is in dba_free_space), which could be discovered by running DBVerify with error DBV-00201 or shown in view v$database_block_corruption, there is the option to wait until the block is reused which will automatically re-format the block or force re-formatting the block using Doc ID 336133.1

Is it an INDEX?

If it is an INDEX, drop and create the index

Is it a TABLE?

If it is a TABLE, procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements; Doc ID 556733.1 has a DBMS_REPAIR example. Then decide to re-create the segment:

by moving the table: alter table &table_name move;

by saving the data (export, Create Table as Select, etc) and then truncate or drop/create.

Is it a LOB?

If it is a LOB use Doc ID 293515.1

Once the issue is fixed and if the corruption is still reported in v$database_block_corruption, run a rman validate to clear that view.

REFERENCES

NOTE:293515.1 – ORA-1578 ORA-26040 in a LOB segment – Script to solve the errors
NOTE:556733.1 – DBMS_REPAIR SCRIPT
NOTE:472231.1 – How to identify all the Corrupted Objects in the Database with RMAN
NOTE:7396077.8 – Bug 7396077 – RMAN does not differentiate NOLOGGING corrupt blocks that produce ORA-1578/ORA-26040
NOTE:819533.1 – How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
NOTE:1071869.1 – ORA-1578 ORA-26040 in 11g for DIRECT PATH with NOARCHIVELOG even if LOGGING is enabled
NOTE:290161.1 – The Gains and Pains of Nologging Operations

You are here

Oracle 由于NOLOGGING产生的 ORA-1578 / ORA-26040损坏块 – 错误说明和解决方案 ORA-01578: ORACLE 数据块损坏 (文件号 , 块号 ) ORA-26040: 数据块是使用 NOLOGGING 选项加载的