Flashback Database

Enable Flashback Database

ALTER DATABASE FLASHBACK ON;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=20160; #14 days or 20160 mins

Verify Flashback Database

SELECT FLASHBACK_ON FROM V$DATABASE; 

Before you do anything drastic

CREATE RESTORE POINT RP1 GUARANTEE FLASHBACK DATABASE;

Rollback to Restore Point

SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO RESTORE POINT RP1;
ALTER DATABASE OPEN RESETLOGS;

Rollback to Timestamp

SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP  TO_TIMESTAMP('2023-01-30 11:25:00','YYYY-MM-DD HH:MI:SS');
ALTER DATABASE OPEN RESETLOGS;

Rollback to Restore Point - CDB/PDB

alter pluggable database PDB1 close immediate;
flashback pluggable database PDB1 to restore point RP1;
alter pluggable database PDB1 open resetlogs;

Remove Restore Point

DROP RESTORE POINT RP1;

How far back can I flashback

SELECT v.oldest_db_fb,
         EXTRACT (DAY FROM oldest_db_fb_interval) * 24
       + EXTRACT (HOUR FROM oldest_db_fb_interval) oldest_db_fb_hours
  FROM (SELECT CAST (
                   FROM_TZ (CAST (oldest_flashback_time AS TIMESTAMP),
                            DBTIMEZONE)
                       AT TIME ZONE 'US/Eastern'
                       AS DATE)
                   oldest_db_fb,
               ROUND ((SYSDATE - oldest_flashback_time) * 24, 1)
                   oldest_db_fb_hours,
                 SYSTIMESTAMP
               - FROM_TZ (CAST (oldest_flashback_time AS TIMESTAMP),
                          DBTIMEZONE)
                     AT TIME ZONE 'US/Eastern'
                   oldest_db_fb_interval
          FROM v$flashback_database_log) v;

List Restore Point

SELECT
    database_incarnation# AS incar,
    scn,
    name,
    time,
    storage_size,
    guarantee_flashback_database
FROM
    v$restore_point
ORDER BY
    4;