Flashback Data Archive

Setup FDA

CREATE TABLESPACE fda_data DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G;
CREATE flashback archive default FDA_1YEAR tablespace fda_data retention 1 year;
exec dbms_flashback_archive.set_context_level(level=> 'ALL');

SELECT
flashback_archive_name,
flashback_archive#,
tablespace_name,
quota_in_mb
FROM
dba_flashback_archive_ts
ORDER BY
flashback_archive_name;

SELECT
owner_name,
flashback_archive_name,
flashback_archive#,
retention_in_days,
TO_CHAR(create_time,'DD-MON-YYYY HH24:MI:SS') AS create_time,
TO_CHAR(last_purge_time,'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
status
FROM
dba_flashback_archive
ORDER BY
owner_name,
flashback_archive_name;

Add Tables to FDA

BEGIN
DBMS_FLASHBACK_ARCHIVE.register_application(
application_name       => 'PARIS_APP',
flashback_archive_name => 'FDA_1YEAR');
END;
/
BEGIN
DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
application_name => 'PARIS_APP',
table_name       => 'APP_DIRECTORIES_AND_PARMS',
schema_name      => 'OPTIT');
END;
/

SELECT
a.appname,
b.faname
FROM
sys_fba_app a
JOIN sys_fba_fa b ON a.fa# = b.fa#;

SELECT
a.appname,
c.owner AS table_owner,
c.object_name AS table_name
FROM
sys_fba_app a
JOIN sys_fba_app_tables b ON a.app# = b.app#
JOIN dba_objects c ON b.obj# = c.object_id
ORDER BY
1,
2,
3;

Enable FDA by Application

BEGIN
DBMS_FLASHBACK_ARCHIVE.enable_application(
application_name => 'PARIS_APP');
END;
/

SELECT
owner_name,
table_name,
flashback_archive_name,
archive_table_name,
status
FROM
dba_flashback_archive_tables
ORDER BY
owner_name,
table_name;

Purge FDA

alter flashback archive FDA_1YEAR purge ALL;

Disable FDA by Application

BEGIN
DBMS_FLASHBACK_ARCHIVE.disable_application(
application_name => 'PARIS_APP');
END;
/

Grant FDA read access to a user

grant EXECUTE ON DBMS_FLASHBACK_ARCHIVE to USER1;
grant FLASHBACK any TABLE to USER1;

Show all Changes

SELECT
    adap.versions_startscn,
    adap.versions_starttime,
    adap.versions_endscn,
    adap.versions_endtime,
    adap.versions_xid,
    adap.versions_operation,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','SESSION_USER')
        END
    session_user,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','HOST')
        END
    host,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','MODULE')
        END
    module,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN DBMS_FLASHBACK_ARCHIVE.get_sys_context(adap.versions_xid,'USERENV','CLIENT_IDENTIFIER')
        END
            client_identifier,
    adap.*
FROM
    optit.app_directories_and_parms VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue adap
WHERE
    adap.app_database = 'PARIS'
    AND   adap.app_alias = 'BIPub'
    AND   adap.app_dir_parm_no = 4
ORDER BY
    adap.versions_startscn NULLS LAST;

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 HH24: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;