Flashback database – with and without resetlogs.

FLASHBACK DATABASE – IMPACT OF RESETLOGS-

A flashback database can be completed and the database can either be opened for READ ONLY or opened with a RESETLOGS option.

As long as the RESETLOGS option is not used, a flashback can be rolled back .

Example –

Create table test – insert some records –

select count (*) from test ;

COUNT(*)
———-
100000

Select current_scn from v$database;

653026

Switch a couple of logfiles –

alter system switch logfile;

Insert another 50000 records into table test.

select count (*) from test ;

COUNT(*)
———-
150000

select first_change# ,to_char(first_time,'DD-MON-YYYY HH24:MI:SS'),sequence# from V$LOG_HISTORY ORDER BY 1 ;

FIRST_CHANGE# TO_CHAR(FIRST_TIME,’ SEQUENCE#
————- ——————– ———-
538113 11-AUG-2009 10:58:17 1
576251 11-AUG-2009 11:03:35 2
653024 11-AUG-2009 11:18:11 3
653026 11-AUG-2009 11:18:12 4
653029 11-AUG-2009 11:18:17 5

Now shutdown immediate and flashback database to SCN 653026 – Before the latest insert to table test – at which point the count(*) of test is 100,000.

shutdown immediate;

startup mount ;

Now flashback database to SCN 653026.

SQL> flashback database to scn 653026;

Now open the database READ ONLY –

alter database read only;

select count(*) from test;

COUNT(*)
———-
100000

If you are satisfied with the results, the database can be opened with the RESETLOGS option –

Shutdown immediate;

startup mount;

alter database open resetlogs;

If not satisfied and you want to redo the flashback or get the database back to it’s current state-

Shutdown immediate;

startup mount;

recover database;

Media recovery complete.

alter database open;

Database altered.

select count(*) from test;

COUNT(*)
———-
150000

Therefore as long as a RESETLOGS is not mentioned, the flashback of a database can be reversed.

Ex – if you use RESETLOGS –

Shutdown immediate;

Startup mount;

flashback database to scn 653026;

Flashback complete.

alter database open resetlogs;

Database altered.

select count(*) from test;

COUNT(*)
———-
100000

shutdown immediate;

startup mount;

recover database;

SQL> ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

alter database open;

Database altered.

select count(*) from test;

COUNT(*)
———-
100000

Therefore the RESETLOGS option does not allow you to reverse the FLASHBACK of the database.