Flashback Restore Points in Oracle

Flashback Database…

Flashback Database quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data

corruptions or user errors.
Flashback database uses flashback logs stored in the flash recovery area to allow users to flashback the database to a

prior point in time.

New Features in Oracle Database 10g Release 2

Restore Points

When an Oracle database point-in-time recovery operation is required, a DBA must determine a time or SCN to which the

data must be rolled back. Oracle Database 10g Release 2 simplifies point in time recovery with restore points. A restore

point is a user-defined name that can be substituted for an SCN or clock time when used in conjunction with Flashback

Database, Flashback Table, and Recovery Manager (RMAN), and can be created at the command-line with SQL*Plus or

RMAN, or through Enterprise Manager. Restore points eliminate the need to investigate the SCN or time of a transaction

and provides users with the ability to bookmark a database transaction event. Guaranteed restore points ensure that

sufficient flashback logs are always maintained to get back to that restore point. This means that flashback logs will not be

deleted by the Flash Recovery Area, unless they are not needed for the current guaranteed restore points. These special

restore points can be created before major database changes, such as a database batch job or schema upgrade, and used

for flashback if the changes need to be undone.

Example of a Restore point. …

1. My database is in Archivelog mode. The database should be in Archivelog mode.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

2. I have a table called emp_test which is present now. ( I have crested that with create table emp_test as select * from

scott.emp;

SQL> select count(*) from emp_test;

COUNT(*)
———-
14

3. The following parameters has to be enabled db_recovery_file_dest= destination directory for the flashback,

db_recovery_file_dest_size = size of the flashback or maximum amount of space allocable for the flash recovery area

SQL> show parameter db_recovery

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string C:\oracle\product\10.2.0\flash
_recovery_area
db_recovery_file_dest_size big integer 5G

4. check that your database is in flashback mode

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

5. The parameter db_flashback_retention_target has to be enabled, it means oracle will hold the logs for the period of 2

days. specified in minutes

SQL> show parameter db_flashback_retention_target;

NAME TYPE VALUE
———————————— ———– ——————————
db_flashback_retention_target integer 2880
SQL>

SQL>
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

6. Shutting down and started in mount

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 310380796 bytes
Database Buffers 293601280 bytes
Redo Buffers 7094272 bytes
Database mounted.

7. Create a restore point.

SQL> create restore point bef_drop_emp guarantee flashback database;

Restore point created.

8. Opened the database.

SQL> alter database open;

Database altered.

9. Checking the SCN number before drop.

SQL> select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size
2 from v$restore_point
3 where guarantee_flashback_database = ‘YES’;

NAME
—————————————————————————-
SCN
———-
TIME
—————————————————————————
DATABASE_INCARNATION# GUA STORAGE_SIZE
——————— — ————
BEF_DROP_EMP
891533
19-JUN-09 08.31.39.000000000 PM
4 YES 8192000

SQL> set lines 500
SQL> /

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

10. The table has been dropped.

SQL> drop table emp_test;

Table dropped.

SQL>
SQL>
SQL> alter system switch logfile;

System altered.

System altered.

SQL> alter system switch logfile;

System altered.

11. The database has been sturdown and open in mount state.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 318769404 bytes
Database Buffers 285212672 bytes
Redo Buffers 7094272 bytes
Database mounted.

12. Restoring it.

SQL> flashback database to restore point bef_drop_emp;

Flashback complete.

13. Checking the SCN.

SQL> select HXFIL File_num,FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence
2 from X$KCVFH;

FILE_NUM SCN STATUS SEQUENCE
———- —————- ———- ———-
1 891536 8192 4
2 891536 0 4
3 891536 0 4
4 891536 0 4

14. Alter the database in read only.. Or open the database by resetlogs option.

SQL> alter database open read only;

Database altered.

15. Checking the table. Its there.

SQL> select count(*) from emp_test;

COUNT(*)
———-
14

You need to open the database with resetlogs.

Flashback Database Through RESETLOGS

Flashback Database through RESETLOGS allows flashback logs created prior to a RESETLOGS operation to be utilized for Flashback Database operations. In Oracle Database 10g Release 2, flashback logs are preserved after opening the database with RESETLOGS. This new feature is useful when a long-standing logical error is not discovered until after RESETLOGS is performed, and a flashback prior to RESETLOGS is needed. In an Oracle Data Guard environment, this capability allows a physical standby database that has been opened read-write to later flashback the changes and be converted back to a physical standby database. If a logical error is discovered after a switchover operation, the primary and standby databases can be flashed back to an SCN or a point in time prior to the switchover operation.