Restore points give the DBA a convenient method of defining an arbitrary string that is equated to an SCN.

There are two types of restore points:

1) Normal Restore point
A normal restore point is associated with a specific time / SCN as a kind of bookmark.
Flashback target limited by db_flashback_retention_target.
SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege is required to create a normal restore point.


To create a normal restore point before dropping a table.

SQL>  Create restore point before_drop;

2) Guaranteed restore point
Guaranteed restore points prohibit the database from purging flashback logs needed to flashback the database to that point. CAUTION: If you run out of space in the flash recovery area, the database will hang.
SYSDBA privilege is required to create a guaranteed restore point.



To create a guaranteed restore point before dropping a table.

SQL>  Create restore point G_before_drop guarantee flashback database;

Requirements:
--------------
- Database compatibility should be 10.2 Or greater.
- Database must be in Archivelog mode.
- Flash recovery area must be configured.


Example of restore point:

SQL>  connect / as sysdba

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter system set db_recovery_file_dest = '/ora_flash_area';

SQL> alter system set db_recovery_file_dest_size = 2g;

SQL> alter system set db_flashback_retention_target = 1440;

SQL> alter database flashback on; 

SQL> alter database open;

#DB_FLASHBACK_RETENTION_TARGET is set to 2880 =2 Days


SQL> create table test(

OWNER	VARCHAR2(30),
NAME	VARCHAR2(30),
TYPE	VARCHAR2(12),
LINE	NUMBER(10),
TEXT	VARCHAR2(4000)
);

SQL> Create restore point G_before_drop guarantee flashback database;

SQL> select * from v$restore_point;
 
SQL> drop table test;

SQL>  shutdown immediate;

SQL>  startup mount;

SQL>  flashback database to restore point G_before_drop;

SQL>  alter database open resetlogs;

SQL>  Desc test;

Thanks,
k_ocp

تم تعديل 30 مارس 200817 سنة بواسطة k_ocp

معلومات قيمة وسرد جميل ،،

ولكن تستطيع عمل Flashback لجدول معين من غير Restorepoint عن طريق

flashback table test to before drop;

وعالعموم شكراً جزيلاً لك .

مشاركة جميلة والله يا اخوانى