FLASHBACK DATABASE

FLASHBACK DATABASE

Flashback database allows you to rollback database to a time in the past.

Useful if you have :

1. Dropped user
2. Truncated table
3. Batch job:Partial changes.

PRE-REQUISITE: DATABASE MUST BE IN ARCHIVELOG MODE AND YOU MUST SET FLASHBACK ON( BY DEFAULT IT IS OFF)

FLASHBACK LOGS ARE CREATED IN YOUR FLASH_RECOVERY_AREA SPECIFIED BY THE INIT.ORA PARAMETER – DB_RECOVERY_FILE_DEST

To set FLASHBACK ON in your 10g database.

shutdown immediate;

startup mount exclusive;….Must be mounted exclusive.

alter database flashback on;

alter database open;

To Flashback database…

Flashback database can be issued with 3 different conditions:

1. TO_TIME
2. TO SCN
3. TO SEQUENCE( LOG ARCHIVE SEQ)

FLASHBACK TO_TIME

PRESENT TIME IS 2009-06-29 07:40:00

drop user arjun cascade;

user dropped.

shutdown immediate;

startup mount;

flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:38:00′,’YYYY-MM-DD HH24:MI:SS’);

alter database open resetlogs;

select username from dba_users;

SQL> select username from dba_users;

USERNAME
——————————
SYS
SYSTEM
DBSNMP
ARJUN
OUTLN

After this is done you cannot flashback the database to a time before the original flashback…

Shutdown immediate;

Startup mount;

SQL> flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:35:00′,’YYYY-MM-DD HH24:MI:SS’);
flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:35:00′,’YYYY-MM-DD HH24:MI:SS’)
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

But you can flashback the tablespace to a time after the original flashback time of 07:38:00

SQL> flashback database to timestamp TO_TIMESTAMP( ‘2009-06-29 07:45:00′,’YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

alter database open resetlogs;

FOR FLASHBACK TO SCN….

Alter database mount;

Flashback database to scn=12355;

To get current_scn: select current_scn from v$database;

TO FLASHBACK TO SEQUENCE NUMBER…

Alter database mount;

Flashback database to sequence=223 thread=1;