How to create restore point in Oracle Database 11g

To create restore point you need to do the below steps.

1. Archive log should be enabled.
2. db_flashback_retention_target need to be set
3. db_recovery_file_dest_size need to be set
4. SHUTDOWN IMMEDIATE;
5. STARTUP MOUNT;
6. FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

SQL> show parameter recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 300G

recovery_parallelism                 integer     0

SQL> ALTER SYSTEM SET db_recovery_file_dest='+ORCL';

System altered.

SQL>  ALTER SYSTEM SET db_recovery_file_dest='+ORCL' sid='*';

System altered.

SQL> CREATE RESTORE POINT BEFORE_CHANGES GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>

To restore database need to follow the below steps.

1. SHUTDOWN IMMEDIATE;
2. STARTUP MOUNT;
3. FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;