Oracle Flashback (drop, table, query, version, data archive, transaction, database)

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

-- Flashback Drop   --
----------------------

Si basa sulla tecnologia della Recycle Bin. Se abilitata (parametro di inizializzazione "recyclebin", true/false) le tabelle droppate non vengono realmente cancellate ma settate come spazio disponibile. Queste tabelle droppate, fintantochè non vengono sovrascritte, possono essere lette. Quando un oggetto viene droppato si libera subito lo spazio nella DBA_FREE_SPACE, ma viene mantnuto sulla USER_TS_QUOTAS (almenochè non si fa la purge). Anche se un tablespace è autoextensible viene comnque usato (sovrascritto) lo spazio della recyclebin. Le DDL e le DML non sono permesse sugli oggetti nella recyclebin.

 

--> PARAMETRI INIZIALIZZAZIONE:

alter system set recyclebin=off scope=both;
alter session set recyclebin=off;
SELECT * FROM v$parameter where name='recyclebin';

--> RESTORE (BEFORE DROP):
flashback table "BIN$dui7OTJqRVCv3t3KJfHOnQ==$0" to before drop;
flashback table TAB_01 to before drop rename to TAB_00;

 

--> QUERY:
describe "BIN$VCfmqQB0FfPgQKjAYAJKzg==$0";
select last_name, first_name, email from "BIN$VCfmqQB0FfPgQKjAYAJKzg==$0" where rownum < 10;

 

--> PURGE:
drop table TAB_00 purge; --Non viene mantenuta nella recyclebin
purge table "BIN$U/9fvJKfXOzgQKjAYAIWhw==$0";
purge table TAB_01;
purge tablespace users;
purge tablespace web_orders user inet_us; --(system privilege DROP ANY TABLE)
purge DBA_RECYCLEBIN; --(connect as SYSDBA pivilegi)

-->DATA DICTIONARY:

select * from USER_RECYCLEBIN;
 

----------------------
-- Flashback Table  --

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

Il range di tempo sul quale si può operare una Flashback Table dipende dalla retention dei segmenti di Undo (parametro di inizializzazione dell'istanza UNDO_RETENTION specificato in secondi & parametro del tablespace di UNDO retention guarantee). Per poter riportare una tabella allo stato precedente si dovrà abilitare la modifica dei rowid (ENABLE ROW MOVEMENT). Durante la fase di Flashback Table (transazione singola) oracle impone un lock exclusive ed i dati in tabella saranno consistenti alla data del lock stesso. Se la transaction fallisce l'intero comando viene rolbackato. Gli indici vengono aggiornati ma non le statistiche sull'oggetto. I trigger vengono disabilitati e poi riabilitati prima e dopo la transazione.

--> Grant:
grant FLASHBACK on table_name; -- o FLASHBACK ANY TABLE

--> Grant per opzione "Restor Point":
grant FLASHBACK ANY TABLE;     -- o SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE

--> Abilitazione cambio rowid:
ALTER TABLE table_name ENABLE ROW MOVEMENT;

--> Flashback Tabella
FLASHBACK TABLE nome_tabella TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

----------------------
-- Flashback Query  --
----------------------

La Fleshback Query si basa sugli undo segments, quindi sottostano alle regole (retention, garantee) del tablespace di undo. Oltre agli undo data, oracle usa anche, se configurati, la Flashback Data Archive.
Settando UNDO_MANAGEMENT ad "AUTO" oracle automaticamente aggiusta la undo retention in base alla grandezza degli undo tablespace. Il parametro di UNDO_RETENTION viene ignorato se l'undo tablespace ha una grandezza fissa.
Se si vuole una retention maggiore degli undo per un subset di tabelle, si può usare  la Flashback Data Archive:

Settare la Retention Guarantee dei Tablespace Undo:

alter tablespace undotbs1 retention guarantee;
select tablespace_name,

       retention

from dba_tablespaces

where tablespace_name like 'UNDO%';

-->Grant
grant execute on dbms_flashback to public;
grant flashback any table to public;

--> Flashback Query (as of)
SQL> select * from t1;
        C1 C2                                                 C3
---------- -------------------------------------------------- -------------------
        10 skdhcbsk                                           01/04/2017 10:40:13
        10 skdhcbsk                                           01/04/2017 10:55:43
        10 skdhcbsk                                           01/04/2017 11:02:07
        10 punto pre punto ripristino                         01/04/2017 11:03:13

SQL> select sysdate from dual;
SYSDATE
-------------------
03/04/2017 15:52:40

SQL> insert into t1 select * from t1;
4 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from t1;
  COUNT(*)
----------
         8

SQL> select count(*) from t1 as of timestamp (to_date('03/04/2017 15:52:40'));
  COUNT(*)
----------
         4

--> Flashback su delete

SQL> DELETE SPEI_ADMIN.BONIFICO_BANCARIO  where CODICE_SCT='1101172930068931';

3 rows deleted.

SQL> commit;

Commit complete.

SQL> create table bck_delete as select * from SPEI_ADMIN.BONIFICO_BANCARIO as of timestamp (to_date('30/11/2017 09:00:00','dd/mm/yyyy hh24:mi:ss')) where CODICE_SCT='1101172930068931';

Table created.

SQL> select count(*) from bck_delete;

  COUNT(*)

----------

         3

--> Flashback su Truncate  

SQL> select sysdate from dual;
SYSDATE
-------------------
03/04/2017 16:02:46
SQL>  truncate table t11;
Table truncated.

SQL> select count(*) from t1 as of timestamp (to_date('03/04/2017 16:02:46'));
  COUNT(*)
----------
         8

 

Oppure

select * from t1 as of timestamp (systimestamp - interval '2' minute);

--> Flashback on Data Dictionary

 

select count(*) from user_tables as of timestamp (sysdate -200/24/60/60)
;ORA-01031: insufficient privileges

 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE      <-- Default
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE       <-- Per usare la "AS OF"

 

 

--> Flashback on SCN:
I dati modificati e committati sono individuabili sia tramite il timestamp che l'SCN (system change number).

 

select dbms_flashback.get_system_change_number from dual;

 

select dbms_flashback.get_system_change_number from dual;

select * from employees as of scn   (27153780)

 

 

------------------------
-- Flashback Version  --
------------------------

Per visualizzare lo storico delle modifiche dei dati ad una tabella (relativamente al dato ancora contenuto negli undo segs).
Tramite la Flashback Version Query ritornerà la storia dei records di una tabella all'interno di uun range scn o timestamp.

 

VERSIONS_START{SCN|TIME} Inizio SCN/timestamp quando la modifica è stata apportata al record.
VERSION_END{SCN|TIME}    Fine SCN/timestamp. Se è NULL, l'operazione è ancora in corso o il record non esiste più.
VERSIONS_XID             Transaction ID

VERSIONS_OPERATION       (I=Insert, D=Delete, U=Update)

 

select versions_startscn start_scn,

       versions_endscn end_scn,

       versions_starttime start_time,

       versions_endtime end_time,
       versions_xid "TransactionID",

       versions_operation "InsDelUpd",

       c1, c2, c3
from tab04 versions between scn 1255059 and 1255314
where c1=10
;

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

-- Flashback Data Archive   --
------------------------------

Use Flashback Data Archive (FDA) to automatically track and archive historical versions of changes to tables enabled for flashback archive, ensuring SQL-level access to the versions of database objects without getting a snapshot-too-old error.  A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.  Flashback Data Archive is configured with retention time.  After Flashback Data Archive is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA. 

Nella versione 11g era aviabile solo con la licenza di “advanced compression option”, dalla 12c la FDA lavora anche senza compressione. 

Dalla 12c lo User-Context (quindi anche l’utenza che fa la DML sulla tabella source) può essere aggiunto alla storicizzazione del dato modificato; questo permette di usare l’FDA come audit security. 

I dati storici, i "change", vengono conservati su una tabella:

--> Creazione di uno spazio FDA da 10G sul tablespace fda_ts con retention di 1 anno:

   CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts QUOTA 10G RETENTION 1 YEAR;

--> Dare diritti di archive all'utenza test:

   GRANT  FLASHBACK ARCHIVE ADMINISTER TO test;

   GRANT  EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO test;

--> Dare diritti di uso dell'FDA all'utenza test:

   GRANT  FLASHBACK ARCHIVE ON fda_1year TO test;

--> Creazione tabella in FDA:

   CREATE TABLE tab1 (

     id           NUMBER,

     description  VARCHAR2(50),

     CONSTRAINT tab_1_pk PRIMARY KEY (id)

   ) FLASHBACK ARCHIVE fda_1year

   ;

--> Cattura del SYS_CONTEXT:

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('TYPICAL');

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('NONE');

--> NOTE

Ricordarsi di attendere almeno 15 secondi tra la creazione della tabella associata alla FDA e la commit di eventuali DML (o le informazioni potrebbero essere perse).

Ricordarsi che se si vuole droppare la tabella bisogna prima rimuovere l'FDA.

--> QUERY (vedi Flashback Version):

COLUMN versions_startscn FORMAT 99999999999999999

COLUMN versions_starttime FORMAT A24

COLUMN versions_endscn FORMAT 99999999999999999

COLUMN versions_endtime FORMAT A24

COLUMN versions_xid FORMAT A16

COLUMN versions_operation FORMAT A1

COLUMN description FORMAT A11

COLUMN session_user FORMAT A20

COLUMN client_identifier FORMAT A20

COLUMN my_attribute FORMAT A20

SET LINESIZE 200

SELECT versions_startscn,

       --versions_starttime, 

       versions_endscn,

       --versions_endtime,

       versions_xid,

       versions_operation,

       description,

       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,

       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,

       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute

FROM   tab1 

       VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24/60) AND SYSTIMESTAMP

WHERE  id = 1

ORDER BY versions_startscn;

--> Backup

BEGIN

  DBMS_FLASHBACK_ARCHIVE.create_temp_history_table(

    owner_name1 => 'TEST',

    table_name1 => 'TAB1');

END;

/

INSERT /*+ APPEND */ INTO temp_history SELECT * FROM SYS_FBA_HIST_77672;

--> Viste di dizionario:

SET LINESIZE 150

COLUMN owner_name FORMAT A20

COLUMN flashback_archive_name FORMAT A22

COLUMN create_time FORMAT A20

COLUMN last_purge_time FORMAT A20

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;

SET LINESIZE 150

COLUMN flashback_archive_name FORMAT A22

COLUMN tablespace_name FORMAT A20

COLUMN quota_in_mb FORMAT A11

SELECT flashback_archive_name,

       flashback_archive#,

       tablespace_name,

       quota_in_mb

FROM   dba_flashback_archive_ts

ORDER BY flashback_archive_name;

SET LINESIZE 150

COLUMN owner_name FORMAT A20

COLUMN table_name FORMAT A20

COLUMN flashback_archive_name FORMAT A22

COLUMN archive_table_name FORMAT A20

SELECT owner_name,

       table_name,

       flashback_archive_name,

       archive_table_name,

       status

FROM   dba_flashback_archive_tables

ORDER BY owner_name, table_name;

----------------------------
-- Flashback Transaction  --
----------------------------

Per la visualizzazione dello storico di una transazione. Il database deve essere in ARCHIVELOG e devono essere abilitati log supplementari (primary key). Non  usa solo gli undo segments, ma anche i Redolog Online e Archive gli stessi che usa il Log Miner (tool, set of PL/SQL, che estrae DDL/DML dai redolog per generare un reverese dello statement SQL, vista V$LOGMNR_CONTENTS)

 

Abilitare il logging delle colonne e il valore delle PK:
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
(Comandi Da Vedere)
alter database    add  supplemental log data (primary key, unique) columns;
alter database    drop supplemental log data;
alter database    drop supplemental log data (primary key) columns;
alter table tab02 add  supplemental log data (primary key) columns
alter table tab02 add  supplemental log group (first unique index columns) always
alter table tab02 add  supplemental log data (all) columns;
select * from DBA_LOG_GROUPS;
select * from DBA_LOG_GROUP_COLUMNS;

 

-->Dare i privilege d'esecuzione DBMS_FLASHBACK sul package, SELECT ANY TRANSACTION privilege all'utente che userà la Flashback Transaction Query:
grant execute on dbms_flashback to sched;
grant select any transaction to sched;

 

-->Individuare la transazione:
select *
from (select  dbms_transaction.local_transaction_id id_univ from dual) id,
     V$TRANSACTION trans
where trans.XIDUSN||'.'||trans.XIDSLOT||'.'||trans.XIDSQN= id.id_univ
;

 

--> QUERY FLASHBACK TRANSACTION

select start_scn,
       commit_scn,
       logon_user,
       operation,
       table_name,
       undo_sql
from flashback_transaction_query
where xid = hextoraw('03001600E6030000')
;
select * from FLASHBACK_TRANSACTION_QUERY;
XID Transaction ID number
START_SCN SCN for the first DML in the transaction
START_TIMESTAMP Timestamp of the first DML in the transaction
COMMIT_SCN SCN when the transaction was committed
COMMIT_TIMESTAMP Timestamp when the transaction was committed
LOGON_USER User who owned the transaction
UNDO_CHANGE# Undo SCN
OPERATION DML operation performed: DELETE, INSERT, UPDATE, BEGIN, or UNKNOWN
TABLE_NAME Table changed by DML
TABLE_OWNER Owner of the table changed by DML
ROW_ID ROWID of the row modified by DML
UNDO_SQL SQL statement to undo the DML operation

-------------------------
-- Flashback Database  --
-------------------------

Il Flashback Database è simile al tradizionale point-in-time recovery. Ti consente di riportare un database al suo stato in un momento nel passato recente. Però il Flashback Database è molto più veloce del point-in-time recovery poiché non richiede il ripristino dei data filea dal backup e richiede l'applicazione di un numero inferiore di Redo Logs. Dopo aver abilitato il Flashback Database [e riaperto il database], a intervalli regolari, vengono copiati i blocchi modificati dei datafile nei Flashback Logs. Dopo aver attivato il Restore Point i blocchi presenti nei Flashback Logs vengono usati per ricostruire i datafiles, dopodichè vengono applicati una parte dei RedoLogs [e archive Redo Logs].

Configurare i seguenti settaggi prima di abilitare il Flashback Database:
 - Il database deve essere in archivelog mode
 - Abilitare la fast recovery area (per i flashback log) (in RAC la "fast recovery area" deve essere clustered o in ASM)

Abilitare il flashback logging:
 - Assicurarsi che il database è open o mounted (in questo caso deve essere a seguito di uno shudown cleanly).
 - Opzionalmente settare il parametro DB_FLASHBACK_RETENTION_TARGET in minuti [default 1 gg (1440 minuti)]
     ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
 - Abilitare il Flashback Database
     ALTER DATABASE FLASHBACK ON;
 - Opzionalmente disabilitare il flashback logging per specifici tablespaces.  
    ALTER TABLESPACE tbs_3 FLASHBACK OFF;
    In questo caso ricordarsi di mettere i data files offline prima del FLASHBACK DATABASE.

--Settaggio della Recovery Area
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 300M;

System altered.

SQL> alter system set db_recovery_file_dest='/usw/app/oracle/admin/BMCMTEST/FRA' scope =memory;

System altered.
/usw/app/oracle/admin/BMCMTEST/FRA

db_recovery_file_dest                string      +BSSA01_FRA
db_recovery_file_dest_size           big integer 400G

SELECT name, log_mode, flashback_on
  FROM v$database;

instance started
alter database archivelog;
alter database noarchivelog;
alter database flashback on;

--Verificare che tutti i tablespace siano stati messi in flashback
select ts#,name, included_in_database_backup,flashback_on from v$tablespace;
alter tablespace TEMP flashback on;
alter tablespace TOOLS flashback on;
alter tablespace USERS flashback on;

alter database open;

-- Creazione punto di Ripristino
create restore point ripristino_db2 guarantee flashback database;

set line 175
column name format a35
column TIME format a30
SELECT scn,
       time,
       guarantee_flashback_database,
       name
  FROM v$restore_point
;

insert into an.t2 values ('riga 4 post punto ripristino');
ALTER SYSTEM SWITCH LOGFILE;

-- Restor Database. Use the V$RESTORE_POINT control file view  a list of all currently defined restore points (normal and guaranteed). Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined

shutdown immediate;
startup mount
flashback database to restore point ripristino_db2;

-- Apertura database
alter database open resetlogs;

--Cancellazione Restore Point
drop restore point ripristino_db;
drop restore point ripristino_db2;
alter database flashback off;
(ripulisce i file di log)

cancellazione archive con rman
sqlplus crosscheck archive