FlashBack Database

### IMPORTANT CONSIDERATIONS
1. If flashback database cannot be used, an incomplete
recovery operation should be used.
2. To undo a flashback operation, perform a complete recovery
3. Flashback logs may deleted automatically, if space is limited.


### CONFIGURE FLASHBACK PARAMETERS

ALTER SYSTEM SET db_recovery_file_dest_size = 30G SCOPE=BOTH SID='*'

# Ensure flashback dest, size and retention is defined

SHOW PARAMETER RECOVERY

SHOW PARAMETER FLASH


# archive is set to null, means, it would default to FRA

SHOW PARAMETER ARCHIVE

### ENABLE FLASHBACK

select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

### MUST HAVE : DATABASE IN ARCHIVELOG MODE

ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 416
Next log sequence to archive 418
Current log sequence 418

### STOP AND START DATABASE IN MOUNT MODE

srvctl stop database -d demo
srvctl start instance –d demo –i demo1 –o mount

select flashback_on from v$database;

FLASHBACK_ON
------------------
NO


### DATABASE W/ FLASHBACK ENABLED

ALTER DATABASE FLASHBACK ON;

Database altered.

select flashback_on from v$database;

FLASHBACK_ON
------------------
YES


### STOP AND START DATABASE W/ FLASHBACK ENABLED

srvctl stop database -d demo
srvctl start database -d demo

select flashback_on from v$database;

### SPACE USAGE OF FLASH RECOVERY AREA(FRA)

col name format a20
col space_limit format 999999999999
select * from v$recovery_file_dest;




select * from v$flash_recovery_area_usage;



### DETERMINING THE CURRENT WINDOW FOR FLASHBACK DATABASE

select * from V$flashback_database_log;



### ESITMATE STORAGE REQUIREMENT FOR FLASHBACK DATABASE

select estimated_flashback_size, flashback_size
from v$flashback_database_log;


### Creating normal and guaranteed restore points

CREATE RESTORE POINT before_upgrade;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

### Dropping restore points

DROP RESTORE POINT before_app_upgrade;

### Listing restore points

select name, scn, time, database_incarnation#,
guarantee_flashback_database,storage_size
from v$restore_point;

select name, scn, time, database_incarnation#,
guarantee_flashback_database, storage_size
from v$restore_point
where guarantee_flashback_database='yes';

#### Performing flashback database: scenario

### Using Flashback Database with RMAN

# Step 1:

Determine the desired SCN, restore point or point in time for the
FLASHBACK DATABASE command

select current_scn from v$database;
select oldest_flashback_scn,oldest_flashback_time
from v$flashback_database_log;

# Step 2

rman TARGET /
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT

select oldest_flashback_scn,oldest_flashback_time
from v$flashback_database_log;


### options:

FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;
FLASHBACK DATABASE TO SEQUENCE my_seq;
FLASHBACK DATABASE TO TIME('2006-01-05 16:10:00');
FLASHBACK DATABASE TO TIME = TO_DATE('2006-12-10 16:10:00','YYYY-MM-DD HH24:MI:SS')
FLASHBACK DATABASE TO RESTORE POINT before_changes;
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;


# Step 3

Once flashback is complete;Test the database to determine if it was successful

ALTER DATABASE OPEN READ ONLY;

# Options After a Successful Flashback Database Operation

you are satisfied with the state of the database after the Flashback Database
operation, you have two choices:

Option 1:

Make the database available for updates by performing an OPEN RESETLOGS operation:

RMAN> ALTER DATABASE OPEN RESETLOGS;



Option 2:
RMAN> RECOVER DATABASE;

This step undoes the effect of the Flashback Database, by re-applying all
changes in the redo logs to the database, returning it to the most recent SCN.

# OPTIONS AFTER FLASHBACK DATABASE TO THE WRONG TIME

Option 3:

If your chosen target time was not far enough in the past, then you can use
another FLASHBACK DATABASE command to rewind the database further in time.

RMAN> FLASHBACK DATABASE TO SCN 42963; #earlier than current SCN

Option 4:

If you chose a target SCN that is too far in the past, then you can mount the
database and use RECOVER DATABASE UNTIL to wind the database forward in time
to the desired SCN:

RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN

Option 5:

If you want to completely undo the effect of the FLASBACK DATABASE command,
you can perform complete recovery of the database by using the RECOVER DATABASE
command without an UNTIL clause or SET UNTIL command:

RMAN> RECOVER DATABASE;

This re-applies all changes to the database, returning it to the most
recent SCN.

### PERFORMING FLASHBACK DATABASE TO A GUARANTEED RESTORE POINT

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';


Having identified the restore point to use, mount the database and run the
FLASHBACK DATABASE command, using the restore point. For example:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_CHANGES';

When the command completes, you may open the database read-only and inspect
the effects of the operation, and if satisfied, open the database with the
RESETLOGS option.


### PERFORMING FLASHBACK DATABASE TO UNDO AN OPEN RESETLOGS

Before performing the flashback, verify that the beginning of the flashback
window is earlier than the time of the most recent OPEN RESETLOGS.

select resetlogs_change# from v$database;
select oldest_flashback_scn from v$flashback_database_log;

If V$DATABASE.RESETLOGS_CHANGE# > V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN
shutdown database, mount it; re-check the flashback window, if true, then

RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;

verify, using

alter database open read only;

Looks good, then

ALTER DATABASE OPEN RESETLOGS;

### FLASHBACK DATABASE TO THE RIGHT OF OPEN RESETLOGS:

EXAMPLE

In some cases, you may need to return the database to a point in time in the
parent incarnation, later than the SCN of the OPEN RESETLOGS at which the
current incarnation path branched from the old incarnation

# Verify that the flashback logs contain enough information to flash back to
that SCN:

select oldest_flashback_scn from v$flashback_database_log;

# Determine the target incarnation number for the flashback, that is, the
incarnation key for the parent incarnation:

select prior_incarnation# from v$database_incarnation
where status = 'CURRENT';

# In RMAN, shut down the database, then mount it:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;

# Set the database incarnation to the parent incarnation:
RMAN> RESET DATABASE TO INCARNATION 1;

# Run the FLASHBACK DATABASE command:
RMAN> FLASHBACK DATABASE TO SCN 1500;

Once the flashback is complete, you can verify the results, and if successful,
open the database with RESETLOGS

### Test performed on demo

create table t1 ( x number) tablespace users;
Table created.

insert into t1 values (1);
1 row created.
commit;
Commit complete.
exit


RMAN> flashback database to scn 9208793;

Starting flashback at 15-MAY-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 instance=demo1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=140 instance=demo1 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=139 instance=mcwd1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 5.1 (2005081402)


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 15-MAY-06

RMAN> EXIT

Tablespaces in Flashback mode

select name, flashback_on from v$tablespace;