mysql 有 nologging_Oracle ORA-26040: 数据块是使用 NOLOGGING 选项加载的 ORA-26040:FLASHBACK DATABASE WITH NOLOGGI...

在本文中

症状

原因

解决方案

适用于：

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.

>>

>>