Oracle 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. >> >>