Oracle ORA-26040: 数据块是使用 NOLOGGING 选项加载的 ORA-26040:FLASHBACK DATABASE WITH NOLOGGING OBJECTS/ACTIVITIES RESULTS IN CORRUPTION 以nologging对象/活动闪回数据库导致损坏

在本文中
症状
原因
解决方案

适用于：

Oracle Server – Enterprise Edition – 版本: 10.2.0.1到 10.2.0.4 – Release: 10.2 to 10.2
本文信息适用于任何平台。

症状
保证还原点可用于将整个数据库逆转revert到几天或几周以前的已知的良好状态，只要有在闪回恢复区flash recovery area有足够的磁盘空间来存储所需的日志。通过Flashback Database，即使是类似直接加载插入（direct load insert）的NOLOGGING操作的影响，也可以用*保证的*还原点逆转revert。

使用Flashback Database ，当尝试被逆转，类似直接加载插入（direct load insert）的NOLOGGING操作的影响会导致损坏，但当使用*非保证的non-guaranteed *还原点时，这只会出现CTAS (create table as select) 。例如：

CTAS/NOLOGGING/非保证的还原点non-guaranteed restore points：

示例
SYS

SQL> create restore point s1;
Restore point created.

SCOTT
SQL> CREATE TABLE S NOLOGGING ENABLE ROW MOVEMENT AS SELECT * FROM EMP;
Table created.

SQL> create restore point s2;
Restore point created.

SQL> INSERT /*+ APPEND */ INTO S SELECT * FROM EMP;
14 rows created.

SQL> COMMIT;
Commit complete.

SYS

SQL> create restore point s3;
Restore point created.

=-=-=- FB to restore point s1 and select from emp =-=-=-
SYS
SQL> SHUT IMMEDIATE
SQL> startup mount exclusive
SQL> flashback database to restore point s1;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.

SCOTT
SQL> select * from emp;   >>>> This would be OK

=-=-=- FB to restore point s3 and select from emp. =-=-=-
SYS

SQL> flashback database to restore point s3;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

SCOTT

SQL> select * from s;
select * from s
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 412)
ORA-01110: data file 4: ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST999\USERS01.DBF’
ORA-26040: Data block was loaded using the NOLOGGING option  <<<

这适用于 –
1. Create table … nologging;
2. direct insert
3. restore point 1
4. direct insert
5. flashback restore point 1

原因

这是预料中的。闪回回到目标时间之前的一个“模糊”快照，且可能要恢复高达30分钟的重做。在这个情况下，恢复遇到由nologging操作生成的 “invalidataion redo” 且发出ORA-26040。

解决方案

作为最佳实践，你应该在nologging操作遇到失效重做后至少1小时来创建你的还原点。

查看示例

>>At 7:51 PM

SCOTT
SQL> create table t (x,y,z) enable row movement nologging
2 as select object_id,object_name,object_type from all_objects where rownum<=5;
Table created.

SQL> insert /*+ append */ into t values(9,’my’,’people’);
1 row created.

SQL> commit;
Commit complete.

>>At 9 PM

SYS

SQL> create restore point s3;
Restore point created.

SQL> SHUT IMMEDIATE
SQL> startup mount exclusive
SQL> flashback database to restore point s3;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

SCOTT

SQL> select * from t;

X Y Z
———- —————————— ——————-
258 DUAL TABLE
259 DUAL SYNONYM
311 SYSTEM_PRIVILEGE_MAP TABLE
313 SYSTEM_PRIVILEGE_MAP SYNONYM
314 TABLE_PRIVILEGE_MAP TABLE
9 my people

6 rows selected.

>>
>>