Configure Database for Automatic Undo Management
1. Setting minimum undo_retention: ALTER SYSTEM SET UNDO_RETENTION = 86400; ( setting is in seconds)
2. Query V$UNDOSTAT.TUNED_UNDORETENTION to determine length of time undo information is retained for a fixed undo tablespace
Note: Flashback query, Flashback version Query and Flashback Transaction Query will fail if query is outside the limit of undo_retention (ORA-30052: invalid lower limit snapshot expression). Increase undo_retention to match or exceed query period. ( ex. For 1 day ago, set undo_retention = 86400 )
A. Restoring deleted data using flashback query
Flashback Query
1. Database settings needed for ‘AS OF TIMESTAMP’
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
2. First locate deleted information
SELECT * FROM region
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE region_name = 'EUROPE';
3. Use insert to restore data
INSERT INTO regions (
SELECT * FROM regions
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE region_name = 'EUROPE');
4. Createa view of past data
CREATE VIEW past_info AS
SELECT * FROM regions
AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL ’120’ MINUTE );
B. Viewing the previous versions of a data row
Flashback Version Query
1. Database and user settings needed for flashback version query
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
GRANT EXECUTE ON DBMS_FLASHBACK TO user;
GRANT SELECT ANY TRANSACTION TO user;
2. Retrieve different versions of specified rows that existed during the requested time period.
select versions_startscn, versions_starttime,versions_endscn,
versions_endtime,versions_xid, versions_operation,
region_id, region_name from regions
versions between timestamp
to_timestamp('2018-3-17 10:00','yyyy-mm-dd hh24:mi')
and to_timestamp('2018-3-21 20:00','yyyy-mm-dd hh24:mi')
where region_id = 10;
3. Use with Temporal validity
select versions_startscn, versions_starttime,versions_endscn,
versions_endtime,versions_xid, versions_operation,
region_id, region_name from regions
versions period for user_valid_time to_timestamp('2018-3-17');
C. Find the undo sql of previous versions of a row of data
Flashback Transaction Query
1. Database and user settings needed for flashback version query
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
Note: If you have many foreign key constraints, enabling foreign key supplemental logging might not be worth the performance penalty.
GRANT SELECT ANY TRANSACTION TO user;
1. To get xid, user information and undo_sql
SELECT xid, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid in (SELECT versions_xid FROM regions VERSIONS
BETWEEN TIMESTAMP TO_TIMESTAMP('2017-3-17 10:00','YYYY-MM-DD HH24:MI')
AND TO_TIMESTAMP('2017-3-21 20:00','YYYY-MM-DD HH24:MI')
WHERE REGION_ID = 10);
2. Another query to get the undo sql.
SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW( '000200030000002D');
Note: Flashback Transaction Query not supported for 12c multi-tenant database
D. Finding the undo sql of previous versions of data using Log Miner
Log Miner
1. Start Log Miner:
begin
DBMS_LOGMNR.start_logmnr
(
starttime => TO_DATE('16-MAR-2017 05:43:10', 'DD-MON-YYYY HH24:MI:SS'),
endtime => TO_DATE('21-MAR-2017 19:43:35', 'DD-MON-YYYY HH24:MI:SS'),
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/
Note: In Oracle 12.1 multi-tenant database Log miner is run in the Root container and with SYSDBA privileges.
2. Do query on the v$logmnr_contents created when log miner is started.
select username,xid ,operation,sql_redo,sql_undo
from v$logmnr_contents
where username = 'HR'
and table_name = 'REGIONS';
Note: Contents of view are based on total undo data and not limited by undo_retention period.
3. Stop Log Miner
SQL> execute dbms_logmnr.end_logmnr;
E. Setup table for flashback
Flashback Table
1. ALTER TABLE atable ENABLE ROW MOVEMENT;
2. FLASHBACK TABLE atable
TO TIMESTAMP TO_TIMESTAMP('2017-3-17 10:00','YYYY-MM-DD HH24:MI');
3. FLASHBACK TABLE atable TO TIMESTAMP
TO_TIMESTAMP('2017-3-17 10:00','YYYY-MM-DD HH24:MI') ENABLE TRIGGERS;
F. Recovering Drop Tables
Flashback Drop
1. Settings: ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE; requires database restart since parameter is not dynamic
2. Recover dropped table commands
FLASHBACK TABLE hr.regions TO BEFORE DROP;
FLASHBACK TABLE “BIN$recycle bin name” TO BEFORE DROP RENAME TO hr.
G. Viewing database as it appeared at a past time period
DBMS_FLASHBACK
1. DBMS_FLASHBACK provides an interface for the user to view the database at a particular time in the past, with the additional capacity provided by transaction back out features that allow for selective removal of the effects of individual transactions.
2. Start
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (restore_scn);
DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('12-02-2001 14:35:00', 'DD-MM-YYYY HH24:MI:SS'))
3. Backout data
A. Begin
dbms_flashback.TRANSACTION_BACKOUT
(
numtxns => 1 ,
xids => xid_array('04001800191A0000'),
options => DBMS_FLASHBACK.CASCADE
);
end;
/
SQL> commit;
B. To view data before committing, use views dba_flashback_txn_state and dba_flashback_txn_report
select a.xid , b.xid_report
from dba_flashback_txn_state a , dba_flashback_txn_report b
where a.compensating_xid = b.compensating_xid
and a.xid = '04001800191A0000';
4. Stop
DBMS_FLASHBACK.DISABLE;
Note: DBMS_FLASHBACK.TRANSACTION_BACKOUT not supported for 12c multi-tenant database
H. Track and store transactional changes to a table or group of tables over time
Flashback Data Archive
1. Privileges:
GRANT FLASHBACK ARCHIVE ON fda1 TO user1;
GRANT FLASHBACK ARCHIVE ADMINISTER TO user1;
GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO user1;
GRANT CREATE ANY CONTEXT TO user1;
2. Limitations: Table cannot be nested, temporary, external, remote. No nested or LONG columns. No columns with reserve words
STARTSCN, ENDSCN, RID, XID, OP, OPERATION
Note: User context tracking is accessible, if the feature is enabled, to FlashBack Data Archive. This makes it easier to track which user made which changes to a table. DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL, DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT
3. Create/modify flashback data archive
CREATE FLASHBACK ARCHIVE fla1 TABLESPACE tbs1 RETENTION 2 YEAR; (add OPTIMIZE DATA for compression)
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3;
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 3 YEAR;
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE
TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
4. Enable/Disable Flashback Data Archive
CREATE TABLE atable …. FLASHBACK ARCHIVE fla1;
ALTER TABLE atable FLASHBACK ARCHIVE;
ALTER TABLE atable NO FLASHBACK ARCHIVE;
5. Application registration for database hardening
A. Register application name
BEGIN
DBMS_FLASHBACK_ARCHIVE.register_application(
application_name => 'MY_APP',
flashback_archive_name => 'FLA1');
END;
/
B. Add tables to the application
BEGIN
DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
application_name => 'MY_APP',
table_name => 'APP_TAB1',
schema_name => 'TEST');
END;
/
C. Enable the Flashback Data Archive for the application
BEGIN
DBMS_FLASHBACK_ARCHIVE.enable_application(
application_name => 'MY_APP');
END;
/
D. Disable Flashback Data Archive for application
BEGIN
DBMS_FLASHBACK_ARCHIVE.disable_application(
application_name => 'MY_APP');
END;
/
E Remove Tables from application
BEGIN
DBMS_FLASHBACK_ARCHIVE.remove_table_from_application(
application_name => 'MY_APP',
table_name => 'APP_TAB1',
schema_name => 'TEST');
END;
/
F. Views
dba_flashback_archive_tables
sys_fba_app
sys_fba_app_tables
