UNDO tablespace growing non-stop

Recently I found that the UNDO tablespace keep on growing, even after a database restart, it is still holding up the space. Creating a new UNDOTBS2 tablespace doesn't help either. 120GB "active" undo space never released.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
alter system set undo_tablespace=UNDOTBS2 scope=both;

Check the UNDO tablespace usage

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
291                              23.8125 UNEXPIRED UNDOTBS1                      
93                               378.375 UNEXPIRED UNDOTBS2                      
545                             750.9375 EXPIRED   UNDOTBS1                      
1349                           10969.625 EXPIRED   UNDOTBS2                      
1                                     64 ACTIVE    UNDOTBS2                      
18130                         121075.375 ACTIVE    UNDOTBS1                      

6 rows selected.

Checking which active transaction is holding up those spaces tells me there is none.

SELECT s.username,s.sid, t.xidusn, t.ubafil,
    t.ubablk, t.used_ublk
    FROM v$session s, v$transaction t
   WHERE s.saddr = t.ses_addr;

USERNAME           SID     XIDUSN     UBAFIL     UBABLK  USED_UBLK
----------- ---------- ---------- ---------- ---------- ----------
                    14        308          0          0          1

Rollback Segment indicated there are problem with some segments

SELECT segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS               TABLESPACE_NAME
------------------------------ -------------------- ------------------
SYSTEM                         ONLINE               SYSTEM
_SYSSMU1_3780397527$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU2_2232571081$           OFFLINE              UNDOTBS1
_SYSSMU3_2097677531$           OFFLINE              UNDOTBS1
_SYSSMU4_1152005954$           OFFLINE              UNDOTBS1
_SYSSMU5_1527469038$           OFFLINE              UNDOTBS1
_SYSSMU6_2443381498$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU7_3286610060$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU8_2012382730$           OFFLINE              UNDOTBS1
_SYSSMU9_1424341975$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU10_3550978943$          OFFLINE              UNDOTBS1
_SYSSMU11_3715213117$          ONLINE               UNDOTBS2
_SYSSMU12_3563522984$          ONLINE               UNDOTBS2
_SYSSMU13_1284218525$          ONLINE               UNDOTBS2
_SYSSMU14_1624181735$          ONLINE               UNDOTBS2
_SYSSMU15_1102482685$          ONLINE               UNDOTBS2
_SYSSMU16_1909532494$          ONLINE               UNDOTBS2
_SYSSMU17_3453924897$          ONLINE               UNDOTBS2
_SYSSMU18_4116382225$          ONLINE               UNDOTBS2
_SYSSMU19_606215510$           ONLINE               UNDOTBS2
_SYSSMU20_1963701883$          ONLINE               UNDOTBS2

21 rows selected.

SELECT
    LISTAGG(''''||segment_name||'''', ',') WITHIN GROUP(
        ORDER BY
            segment_name
    )
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

SELECT '"drop rollback segment "'||segment_name||'";'
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

Let's kill the problem segments

create pfile from spfile

Add this to the end of the pfile
paris._offline_rollback_segments=('_SYSSMU1_3780397527$','_SYSSMU6_2443381498$','_SYSSMU7_3286610060$','_SYSSMU9_1424341975$')

shutdown immediate;
startup pfile='E:\oracle\db_home\product\12.2.0\dbhome_1\database\INITPARIS.ORA'

drop rollback segment "_SYSSMU1_3780397527$";
drop rollback segment "_SYSSMU6_2443381498$";
drop rollback segment "_SYSSMU7_3286610060$";
drop rollback segment "_SYSSMU9_1424341975$";
drop tablespace UNDOTBS1 including contents and datafiles;

shutdown immediate;
startup;

Check the UNDO tablespace usage again

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
                162                  678 UNEXPIRED UNDOTBS2                      
                319               2840.5 EXPIRED   UNDOTBS2

Oracle Database 18c XE and Transparent Data Encryption

TDE Setup

The process is slightly different than 12c.

Create pfile

create pfile from spfile;
shutdown immediate;

Add this to /opt/oracle/product/18c/dbhomeXE/dbs/initXE.ora

*.wallet_root=/opt/encrypted_wallet

Create spfile from pfile with WALLET_ROOT

create spfile from pfile;
startup;

Configure the Software Keystore Type

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;
shutdown immediate;
startup;

Setup auto-login Wallet

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/encrypted_wallet/tde/' IDENTIFIED BY "wve6Wq54IUhg39XY";
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/encrypted_wallet/tde/' IDENTIFIED BY "wve6Wq54IUhg39XY";

Create the master key in all containers

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wve6Wq54IUhg39XY" CONTAINER=ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "wve6Wq54IUhg39XY" WITH BACKUP CONTAINER=ALL;

Checkout Setting

select * FROM V_$ENCRYPTION_WALLET;
select * FROM V_$ENCRYPTION_KEYS;
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name;

Migrate existing tablespaces to encrypted tablespace

ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users ENCRYPTION OFFLINE ENCRYPT;
ALTER TABLESPACE users ONLINE;

Checkout tablespace encryption setting

select * from V$ENCRYPTED_TABLESPACES;

Setting Future Tablespaces to be Encrypted

ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS;

RMAN Encrypted Backup

RMAN Configuration

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';

RCV file

show all;
set encryption on;
crosscheck backupset;
crosscheck archivelog all;
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
run {  
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
list backupset;
exit

Datapump Encrypted Backup

Add these options to expdp

ENCRYPTION=ALL
ENCRYPTION_MODE=TRANSPARENT 
ENCRYPTION_ALGORITHM=AES256

Oracle Database 18c Express Edition (XE) RPM Installation On Amazon Oracle Linux 7 (OL7)

Create 3GB Linux swap

# dd if=/dev/zero of=/swapfile bs=1024 count=1048576
# mkswap /swapfile
# swapon /swapfile
# chmod 600 /swapfile
# swapon -s

Add Linux swap to /etc/fstab

/swapfile  none  swap  sw 0  0

Oracle database will be installed under /opt. So, make sure you have enough room under /opt

# yum -y localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm
# /etc/init.d/oracle-xe-18c configure

Test Database under non-root account

$ . oraenv
$ sqlplus sys/xxxxxx@//localhost:1521/XE as sysdba
$ sqlplus sys/xxxxxx@//localhost:1521/XEPDB1 as sysdba

Fix iptables
Allow port 80, 443 and 1521

# iptables -I INPUT -p tcp --dport 1521 -j ACCEPT
# iptables -t eth0 -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080
# iptables -t eth0 -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080
# iptables -t eth0 -I PREROUTING -p tcp --dport 443 -j REDIRECT --to-ports 8443
# iptables -t eth0 -I OUTPUT -p tcp --dport 443 -j REDIRECT --to-ports 8443
# service iptables save

Configure Oracle to startup on boot

# systemctl daemon-reload
# systemctl enable oracle-xe-18c
# reboot

Oracle user environment setup .bash_profile

export ORAENV_ASK=NO
export ORACLE_SID=XE
. oraenv

sqlplus / as sysdba

Apply Oracle Database Patch

Take screenshot, shutdown all oracle services and change them to MANUAL startup
reboot
open window command, run as administrator

List all the patches applied to database

E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch lspatches

Apply the patch

cd C:\Users\CyberArkDBA\Downloads\p28574555_122010_MSWIN-x86-64\28574555
E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch apply

Startup Oracle
cd %ORACLE_HOME%/OPatch
datapatch -verbose

Base on the screenshot, put back the original startup setting
reboot

Recompile all objects

exec utl_recomp.recomp_parallel;

select * from dba_objects where status = 'INVALID' order by 1,2;

Check patch history

select * from dba_registry_sqlpatch;

select dbms_java.get_jdk_version() from dual;

select dbms_qopatch.get_opatch_lsinventory() from dual;

Remove patch (if there is conflict)

E:\oracle\db_home\product\12.2.0\dbhome_1\OPatch\opatch rollback -id 28135129
cd %ORACLE_HOME%/OPatch
datapatch -verbose -rollback 28135129

Oracle APEX Upgrade

Upgrade
I am using Oracle APEX Static Resources on Content Delivery Network. There is no need to copy the images directory to tomcat as /i/

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@apexins.sql APEX_DATA APEX_DATA TEMP https://static.oracle.com/cdn/apex/20.2.0.00.20/
@apex_rest_config.sql

Install French Language
goto this directory apex/builder/fr

set NLS_LANG=American_America.AL32UTF8
sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
ALTER SESSION SET CURRENT_SCHEMA = APEX_200200;
@load_fr.sql

Apply Patch
unzip p32006852_2020_GENERIC.zip, goto patch directory

sqlplus / as sysdba
ALTER SESSION SET CONTAINER = PDB01;
@catpatch.sql

Re-compile all objects

EXEC utl_recomp.recomp_parallel;
SELECT COUNT (*)
  FROM dba_objects
 WHERE status = 'INVALID'; 

O/S authenticated local login without password

Create User

CREATE USER "OPS$OPT_DOMAIN\CYBERARKDBA" IDENTIFIED EXTERNALLY AS 'null'    
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT CREATE SESSION TO "OPS$OPT_DOMAIN\CYBERARKDBA";

Now, you can login to oracle without password

sqlplus /

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 10 22:55:51 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
OPS$OPT_DOMAIN\CYBERARKDBA

Move audit information away from SYSAUX

Check current tablespace location

SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; 

Create tablespace for audit

create tablespace AUDIT_DATA datafile size 1G autoextend on; 

Move to the new AUDIT_DATA tablespace

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
  audit_trail_location_value => 'AUDIT_DATA');
  DBMS_AUDIT_MGMT.set_audit_trail_location(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
  audit_trail_location_value => 'AUDIT_DATA');
END;
/

Java Path Location

Windows
C:\ProgramData\Oracle\Java\javapath

Linux

Extract entire database DDL script for SVN

The best program I found is scheme2dll. It can generate ddl script per schema/object type/object name in a tree structure. This is perfect for SVN.

You can download the binary here.

This is the command I use to extract the ddl, schema_export.cmd

@echo off
REM SVN Revision Information: DO NOT REMOVE
REM $Revision: 37 $
REM $Author: chiup $
REM $Date: 2018-06-08 11:56:08 -0400 (Fri, 08 Jun 2018) $
REM
setlocal
java -jar scheme2ddl.jar -url dba_account/xxxxxxxxxx@localhost:1521:paris -p 8 -s ^
ACTUARY_BBS,^
APS,^
B2B,^
BAM,^
BBS,^
BFS,^
CHATS,^
DATAMANAGER,^
DCS,^
DEATH_RECALC,^
DISCOVERER5,^
DSGATEWAY,^
ENROL,^
GENERIC_TRD,^
IP,^
LTIP,^
MAINT,^
MBK,^
OCA,^
ODS,^
OPTFIN,^
OPTIT,^
ORABPEL,^
ORAOCA_PUBLIC,^
PAY,^
PBC,^
PCONV,^
PENWEB,^
PIN,^
PPI,^
PPS_OPSEU,^
PRISM_DBA,^
RMS_OPSEU,^
RULES_TRD,^
SES,^
TRD,^
UDDISYS,^
UNCL,^
WCRSYS,^
XFS,^
XPSPA ^
-o F:\oracle_ddl
endlocal

Installing Oracle XE 11g r2 on Amazon EC2

Add a disk via EC2 console and setup it up in linux

mkfs -t ext4 /dev/xvdb
mkdir /u01

Find out the uuid of the disk

ls -al /dev/disk/by-uuid/

Add the following line to /etc/fstab to mount the disk at boot time

UUID=82497d4a-9507-47db-b19c-c4473827ead6 /u01   ext4    defaults,nofail        0       2

Mount the disk and verify fstab syntax

mount -a

Add Swap space

dd if=/dev/zero of=/swapfile bs=1024 count=3072000
mkswap /swapfile
swapon /swapfile

Add the following line to /etc/fstab to enable swap at boot time

/swapfile swap swap defaults 0 0

Mount the swap and verify fstab syntax

mount -a

Add oracle group and user

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba,oinstall oracle
chown -R oracle:oinstall /u01
passwd oracle

Add the following lines to /etc/security/limits.conf

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
oracle              soft    stack   10240

Install Oracle

yum install glibc make binutils gcc libaio
rpm -i oracle-xe-11.2.0-0.5.x86_64.rpm
/etc/init.d/oracle-xe configure

Add the following lines to ~oracle/.bash_profile

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE

PATH=$PATH:$ORACLE_HOME/bin:$HOME/.local/bin:$HOME/bin
export PATH

RAC database commands

Database level

srvctl stop database -d orcl
srvctl start database -d orcl

PDB level

alter pluggable database PDB1 open  instances=all;
alter pluggable database PDB1 close  instances=all;

SELECT * FROM gv$pdbs;
SELECT * FROM dba_pdb_saved_states;

Oracle 12.2 and Transparent Data Encryption

TDE Setup

Add these lines to sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=E:\encrypted_wallet\)))

Bounce Database

shutdown immediate;
startup;

Setup auto-login Wallet

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'E:\encrypted_wallet\' IDENTIFIED BY "wve6Wq54IUhg39XY";
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'E:\encrypted_wallet\' IDENTIFIED BY "wve6Wq54IUhg39XY";

Checkout Setting

select * FROM V_$ENCRYPTION_WALLET;
select * FROM V_$ENCRYPTION_KEYS;
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name;

Create the master key in all containers

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wve6Wq54IUhg39XY" CONTAINER=ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "wve6Wq54IUhg39XY" WITH BACKUP CONTAINER=ALL;

Migrate existing tablespaces to encrypted tablespace

Ensure compatibility is 12.2.0

alter system set compatible = '12.2.0' scope=spfile;
show parameter compatible;

Do this for each tablespace. After all tablespaces are migrated to the encrypted one, we need to manual delete the unencrypted datafiles. Oracle supposed to remove them, but they are not doing a good job.

SELECT
    'ALTER TABLESPACE '
    || tablespace_name
    || ' ENCRYPTION ONLINE USING ''AES256'' ENCRYPT;' v_sql
FROM
    dba_tablespaces
WHERE
    encrypted = 'NO'
    AND contents IN (
        'PERMANENT'
    )
    AND tablespace_name NOT IN (
        'SYSTEM',
        'SYSAUX'
    )
ORDER BY
    1;

RMAN Encrypted Backup

RMAN Configuration

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';

RCV file

show all;
set encryption on;
crosscheck backupset;
crosscheck archivelog all;
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
run {  
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
list backupset;
exit

Datapump Encrypted Backup

Add these options to expdp

ENCRYPTION=ALL
ENCRYPTION_MODE=TRANSPARENT 
ENCRYPTION_ALGORITHM=AES256

Oracle Database Security Assessment Tool (DBSAT)

The Oracle Database Security Assessment Tool is a stand-alone command line tool that accelerates the assessment and regulatory compliance process by collecting relevant types of configuration information from the database and evaluating the current security state to provide recommendations on how to mitigate the identified risks.

To Run

SET ZIP_CMD=%ORACLE_HOME%\bin\zip.exe 
SET UNZIP_CMD=%ORACLE_HOME%\bin\unzip.exe

dbsat collect chiup@paris12c paris12c

dbsat report paris12c

Oracle RMAN Cold Backup

rman target /

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure controlfile autobackup format for device type disk to 'F:\ora_backup\%F';
configure channel device type disk format 'F:\ora_backup\%U' maxpiecesize 8 G;
run 
{
  shutdown immediate
  startup mount
  backup database;
  alter database open;
}

Check Flashback Recovery Area Size

show parameter db_recovery

NAME                       TYPE        VALUE                  
-------------------------- ----------- ---------------------- 
db_recovery_file_dest      string      F:\flash_recovery_area 
db_recovery_file_dest_size big integer 900G     

show parameter db_flashback

NAME                          TYPE    VALUE 
----------------------------- ------- ----- 
db_flashback_retention_target integer 720 

select space_used/(1024*1024*1024) gb_used, space_limit/(1024*1024*1024) gb_limit from v$recovery_file_dest;

   GB_USED   GB_LIMIT
---------- ----------
427.937144        900

select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                           .03                       .01              13          0
BACKUP PIECE                         46.85                         0              13          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                          .68                       .65             122          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected. 

Set FRA limit to be 90% of the drive size

alter system set db_recovery_file_dest_size=900G SCOPE=BOTH;

Proxy User and Connect Through

Assuming we have a user called CHIUP and we want to connect to LIB1 without knowing the password, we could do the following.

P.S. This method works for Forms 12c but not Forms 10g.

As DBA

ALTER USER LIB1 GRANT CONNECT THROUGH CHIUP;

Connect via SQL*Plus

CONN CHIUP[LIB1]/xxxxxxx

Connect via SQL Developer
Hit "Advanced..." button in the connection setup window


Revoke Access
As DBA

ALTER USER LIB1 REVOKE CONNECT THROUGH CHIUP;

Reset Password By Values in 12c

select u.username,
'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd
from dba_users u
join sys.user$ s
on u.user_id = s.user#
where u.username = upper('user1');

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