-- flashback database and nologging operations problem demo
-- Some notes --
-- Flashback feature is not enabled at XE
-- It is better to have a RMAN full backup before this demo :) here is an example how-to
-- 		http://tonguc.yilmaz.googlepages.com/XErmanbackupandrecoverydemo.txt
-- you must run on archivelog mode and flashback enabled for this demo, to check these
conn / as sysdba
archive log list;
select flashback_on from v$database; 
-- if you are not then you can use these steps to turn on archivelog mode and flashback
shutdown immediate;
startup mount; 
alter database archivelog;
alter database flashback on;
alter database open;
archive log list;
select flashback_on from v$database; 
-- we will create two demo tables this time but with nologging options this time
drop table hr.t1 purge ;
create table hr.t1 nologging as select * from user_users ;
select count(*) count_t1 from hr.t1 ;
drop table hr.t2 purge ;
create table hr.t2 nologging as select * from all_users ;
select count(*) count_t2 from hr.t2 ;
-- we create a safe restore point to flashback the database before the truncate and drop purge DDLs
create restore point before_damage ; 
truncate table hr.t1 ; 
drop table hr.t2 purge;
-- startup mount to flashback database 
shutdown immediate;
startup mount; 
flashback database to restore point before_damage ;
alter database open resetlogs;
-- 
select count(*) count_t1 from hr.t1 ;
select count(*) count_t2 from hr.t2 ;

SQL> SQL> SQL> SQL> select count(*) count_t1 from hr.t1 ;
select count(*) count_t1 from hr.t1
                                 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 41229)
ORA-01110: data file 6: '+DGROUP1/tong/datafile/tbs_asm_test1.256.642125051'
ORA-26040: Data block was loaded using the NOLOGGING option

SQL> select count(*) count_t2 from hr.t2
                                 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 41357)
ORA-01110: data file 6: '+DGROUP1/tong/datafile/tbs_asm_test1.256.642125051'
ORA-26040: Data block was loaded using the NOLOGGING option