ORA-26040:FLASHBACK DATABASE WITH NOLOGGING OBJECTS/ACTIVITIES RESULTS IN CORRUPTION (Doc ID 554445.1)

Last updated on OCTOBER 31, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

A guaranteed restore point can be used to revert a whole database to a known good state days or weeks ago, as long as there is enough disk space in flash recovery area to store the needed logs. As with Flashback Database, even the effects of NOLOGGING operations like direct load inserts can be reversed using *guaranteed* restore points.

Using Flashback Database the effects of NOLOGGING operations like direct load inserts could results in corruption when trying to be reversed when using *non-guaranteed* restored points however this is only occurs with CTAS (create table as select) . See the examples:


CTAS/NOLOGGING/non-guaranteed restore points:

Example
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  <<<

This works -
1. Create table ... nologging;
2. direct insert
3. restore point 1
4. direct insert
5. flashback restore point 1


This works -
1. Create table ...
2. alter table x nologging;
3. direct insert
4. restore point 1
5. direct insert
6. flashback restore point 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms