Restore Point in Oracle 19c

 RESTORE POINT 

Note:- Before creating GRP make sure below 

1) db should be in archive log mode and flashback must be on

>> beacuse of fra get full if restore point not drop

A restore point is a name assigned to a system change number (SCN) in Oracle.

An SCN is a number that uniquely identifies each new change to an Oracle database; 

this number is incremented whenever users commit a new transaction to the database.

**************************************************************************

 To Enable Flashback Database 

shutdown immediate;

startup nomount;

alter database mount;

archive log list;

alter database archivelog;

change FRA size and then dest :-

alter system set db_flashback_retention_target=2880; ----> 2880 minutes 

alter system set db_recovery_file_dest_size=10G;

alter system set db_recovery_file_dest='/path/to/FRA';

alter system set undo_retention=86400; -----------> 86400 seconds

alter database flashback on;

alter database open;

if not open then do "alter database open resetlogs" or "alter database open noresetlogs" 

SQL >

select name, value from gv$parameter where name in ('db_flashback_retention_target','db_recovery_file_dest_size','db_recovery_file_dest','undo_retention');

To Check Flashback on/off :-

select flashback_on from gv$database;

***********************************************************

SQL> CREATE RESTORE POINT before_upgrade;

Restore point created.

====================================================================================

Guarantee Restore Point:

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

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

==========================================================================================

List out restore points:

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

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

========================================================================================

Only Guarantee Restore Points:

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

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

=========================================================================================

Droping Restore Points:

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

SQL> DROP RESTORE POINT before_app_upgrade;

Restore point dropped.

==========================================================================================

5.2.5 Monitoring Space Usage For Guaranteed Restore Points

When guaranteed restore points are defined on your database,

 you should monitor the amount of space used in your 

flash recovery area for files required to meet the guarantee. 

Use the query for viewing guaranteed restore points in

 "Listing Restore Points" and refer to the STORAGE_SIZE 

column to determine the space required for files related to 

each guaranteed restore point. To see the total usage of your

 flash recovery area, use the query provided in 

"Using

 V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE".

======================================================================================

 SQL>  select name,FLASHBACK_ON,open_mode from v$database;

NAME      FLASHBACK_ON     OPEN_MODE

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

TECHNO_DB  NO              READ WRITE

============================================================================

 SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

YES

Check for Fast Recovery Area size and location

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +FLASH

db_recovery_file_dest_size           big integer 100G

SQL>

Check for any restore point using v$restore_point view

set lines 200

col TIME for a40

col NAME for a40

select inst_id,NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

no rows selected

Create actual restore point using CREATE RESTORE POINT command

SQL> create restore point RESTORE_POINT_20FEB2019 guarantee flashback database;

Restore point created.

Verify if restore point is created

set lines 200

col TIME for a40

col NAME for a40

select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

    NAME                 TIME           GUA STORAGE_SIZE/1024/1024/1024

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

RESTORE_POINT_20FEB2019 20-FEB-19 06.20.40.000000000 AM    YES        2

DROP Restore Point

Drop restore point can be performed using DROP RESTORE POINT command and we need turn off flashback_on option

SQL> DROP RESTORE POINT RESTORE_POINT_20FEB2019;

Restore point dropped.

SQL> alter database flashback on;

Database altered.