FlashBack Restore Point in oracle

1.  Requirements for Guaranteed Restore Points

The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode.

A flash recovery area must be configured Guaranteed restore points use a mechanism similar to flashback logging. Oracle must store the required logs in the flash recovery area.

Oracle 10.2

If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point

SQL> ALTER DATABASE FLASHBACK ON;

Oracle 11.x

There is no need to mount the database. Flashback can be tunred on at open state.

SQL> ALTER DATABASE FLASHBACK ON;

2. Creating Restore points [CREATE RESTORE POINT]

1 2 3 4 5 6 7

# Create Normal restore points SQL> CREATE RESTORE POINT before_upgrade; #Create guaranteed restore points SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

3. Listing restore points [V$RESTORE_POINT]

1 2 3 4 5 6 7

# To see a list of the currently defined restore points SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,     GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; #To view only the guaranteed restore points: SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,     GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT     WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

4. Dropping restore points [DROP RESTORE POINT]

1 2	#Same statement is used to drop both normal and guaranteed restore points. SQL> DROP RESTORE POINT before_app_upgrade;

5. Turn of Flashback

SQL> ALTER DATABASE FLASHBACK OFF;

Hope this helps! Regards Rupam

Flashback Cheatsheet

Enabling/disabling Logging for Flashback Database

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE FLASHBACK OFF;

Estimating Disk Space Requirements for Flashback Database Logs

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

By default, flashback logs are generated for all permanent tablespaces.

SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;

SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;

Backup database

RMAN> backup database plus archivelog;

Backup flash recovery area

RMAN> backup recovery area;

Flashback usage

sql> select * from v$recovery_file_dest;

sql> select * from v$flash_recovery_area_usage;

Create Restore Point

sql> create restore point rp01;

sql> select name from v$restore_point;

sql> drop restore point rp01;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

-- Determining the Current Window for Flashback Database

sql> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME

      FROM V$FLASHBACK_DATABASE_LOG;

Restore to Restore Point using RMAN

RMAN> SHUTDOWN IMMEDIATE;

RMAN> STARTUP MOUNT;

RMAN> LIST RESTORE POINT ALL;

RMAN> LIST RESTORE POINT  RP03;

RMAN> flashback database  to  restore point rp01;

RMAN> alter database open resetlogs;

RMAN> FLASHBACK DATABASE TO SCN 46963;

RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY'; # VERIFY CHANGES

Restore to Restore Point using SQL

sql> shutdown immediate

sql> startup mount

sql> flashback database  to  restore point rp01;

sql> alter database open resetlogs;

Options After Flashback Database to the Wrong Time

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

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

RMAN>  RECOVER DATABASE;

Performing Flashback Database to Undo an OPEN RESETLOGS

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

Flashback Database To The Right of Open Resetlogs

sql> select oldest_flashback_scn from v$flashback_database_log;

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

RMAN> LIST INCARNATION OF DATABASE trgt;

RMAN> RESET DATABASE TO INCARNATION 1;

RMAN> FLASHBACK DATABASE TO SCN 1500;

Flashback Table

   -- Prerequisites for Using Flashback Table

sql> ALTER TABLE table ENABLE ROW MOVEMENT;

sql> SELECT, INSERT, DELETE, and ALTER privileges on the table.

 Performing Flashback Table

sql> FLASHBACK TABLE EMP TO SCN 123456;

sql> FLASHBACK TABLE EMP TO TIMESTAMP

      TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Viewing, Querying and flashback  Objects in the Recycle Bin

SQL> show recyclebin;

SQL> SELECT object_name as recycle_name, original_name, type

     FROM recyclebin;

SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";

SQL> FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

OR

sql> FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP

     RENAME TO hr.int2_admin_emp;

Hope this helps! Regards Rupam

FLASHBACK QUERY

# flashback query as of timestamp

# flashback query as of scn

FLASHBACK TABLE

# flashback to SCN

# flashback table to timestamp

# flashback to restore point

Flashback table

The table could be restored to using flashback table option. Before attempting it, find out how far to flash the table.

Steps :-

1. sql> select count(*) from scott.emp;

2.  export of the table 

3. flashback table

sql> alter table scott.emp enable row movement;

sql> flashback table scott.emp to timestamp

       to_timestamp('2010-12-18 12:00:00','YYYY-MM-DD HH24:MI:SS');

      

Flashback complete.

4. sql> select count(*) from scott.emp;

  COUNT(*)

----------

    784871

5. confirm with apps team

Hope this helps! Regards Rupam

Flashback Table to Before drop

Oracle FlashBack Table

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline.

Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints

Example:

 sql> DROP TABLE flashback_drop_test;

 sql> SHOW RECYCLEBIN

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

You can retrieve it and assign it a new name using a RENAME TO clause.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP

RENAME TO flashback_drop_test_old;

### Several purge options exist:

PURGE TABLE tablename;

PURGE INDEX indexname;

PURGE TABLESPACE ts_name;

PURGE TABLESPACE ts_name USER username;

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;   

### Query Recycle Bin

col original_name format a10

col owner format a10

col type format a10

col droptime format a10

col pace format a12

select owner, original_name, object_name, type, droptime

from dba_recyclebin

where can_undrop='YES';

### Query the dropped table data from Recycle bin

SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

### Bypass the Recycle Bin

drop table flashback_drop_test purge;

Hope this help. Regards Rupam

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;

FLASH RECOVERY AREA (FRA)

Configure

sql > alter system set db_recovery_file_dest_size = 2G scope=both sid='*';
sql > alter system set db_recovery_file_dest='+ORAFLASH001' scope=both sid='*';

sql > show parameter db_reco

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +ORAFLASH001
db_recovery_file_dest_size big integer 2G


Check space utilization

summary

col name format a20
select * from v$recovery_file_dest;

details

select * from v$flash_recovery_area_usage;