How to create restore point in Oracle database 19c

In Oracle 19c, you can create a restore point to mark a specific point in time in the database to which you can later restore the database if needed. A restore point is typically used in conjunction with Flashback technology, which allows you to perform operations such as flashback database, flashback table, or flashback restore.

There are two types of restore points in Oracle 19c:

1. Normal Restore Point: A regular restore point that allows you to restore to that point in time.
2. Guaranteed Restore Point (GRP): This type of restore point guarantees that the database can be flashed back to this point, even if the archived logs required for recovery are no longer available.

Steps to Create a Restore Point in Oracle 19c

1. Creating a Normal Restore Point

To create a normal restore point, you can use the CREATE RESTORE POINT command.

CREATE RESTORE POINT restore_point_name;

For example:

CREATE RESTORE POINT my_restore_point;

This creates a restore point with the name my_restore_point. You can use this restore point later to perform flashback operations.

2. Creating a Guaranteed Restore Point (GRP)

A guaranteed restore point ensures that the database can be flashed back to this point even if the archive logs are lost. To create a GRP, use the GUARANTEE FLASHBACK DATABASE option.

CREATE RESTORE POINT restore_point_name GUARANTEE FLASHBACK DATABASE;

For example:

CREATE RESTORE POINT my_guaranteed_restore_point GUARANTEE FLASHBACK DATABASE;

This ensures that all necessary archived logs and undo data are retained to allow a flashback operation to this point.

3. Checking the List of Restore Points

To view a list of existing restore points, you can query the V$RESTORE_POINT view:

SELECT restore_point_name, guarantee_flashback, scn, time 
FROM v$restore_point;

4. Dropping a Restore Point

If you no longer need a restore point, you can drop it using the DROP RESTORE POINT command:

DROP RESTORE POINT restore_point_name;

For example:

DROP RESTORE POINT my_restore_point;

If the restore point is a Guaranteed Restore Point (GRP), Oracle will automatically remove the guarantee when you drop it.

5. Using a Restore Point for Flashback Operations

Once a restore point is created, you can use it to perform flashback operations such as FLASHBACK DATABASE or FLASHBACK TABLE.

For example, to flashback the entire database to a specific restore point:

FLASHBACK DATABASE TO RESTORE POINT my_restore_point;

Or, to flashback a specific table:

FLASHBACK TABLE my_table TO RESTORE POINT my_restore_point;

Note:

• Flashback Retention: For guaranteed restore points, make sure that Flashback Retention is appropriately set. If the required undo data is flushed before it is retained, the flashback operation might fail.
• Archivelog Mode: Ensure the database is in archivelog mode to take full advantage of flashback and restore point functionality.