ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
Default database initialization parameters:
- UNDO_MANAGEMENT='AUTO
- UNDO_TABLESPACE='UNDOTBS1'
- UNDO_RETENTION=900
Collect undo block info with the V$UNDO_STAT view to calculate space requirements, and use them to create an appropriately sized fixed undo tablespace. You can query V$UNDOSTAT.TUNED_UNDORETENTION to determine the amount of time for which undo is retained for the current undo tablespace.
** Use to query all data at a specific point in time:
select employee_id, salary from employees as of TIMESTAMP where EMPLOYEE_ID = 200;
Flashback query example:
Salary = 4400 at 11:00 PM
Salary = 4840 at 11:30 PM
Salary = 4400 at 11.45 PM.
Update employees
Set Salary =
( select salary from employees
as of TIMESTAMP TO_TIMESTAMP
('2017-06-02 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
WHERE employee_id = 200)
WHERE employee_id=200;
Flashback version query:
Shows the committed versions of occurrences:
SELECT versions_xid, SALARY from EMPLOYEES
VERSIONS BETWEEN TIMESTAMP and
WHERE employee_id = 200;
VERSION_XID is a pseudocolumn that returns the transaction identifier of the corresponding version of a row.
-- You can use the FLASHBACK_TRANSACTION_QUERY view to determine all the necessary SQL statements that can be used to undo the changes made either by a specific transaction or during a specific period of time.
-- Enable flashback transactions:
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
grant execute on dbms_flashback to hr;
grant select any transaction to hr;
INSERT INTO hr.regions VALUES (5,'Pole');
commit;
UPDATE hr.regions SET region_name='Poles' WHERE region_id = 5;
UPDATE hr.regions SET region_name='North and South Poles' WHERE region_id = 5;
commit;
INSERT INTO hr.countries VALUES ('TT','Test Country',5);
commit;
ALTER SYSTEM ARCHIVE LOG CURRENT;
CREATE TYPE XID_ARRAY AS VARRAY(100) OF RAW(8);
CREATE OR REPLACE PROCEDURE TRANSACTION_BACKOUT
(numberOfXIDs NUMBER, -- number of transactions passed as input xids XID_ARRAY, -- the list of transaction ids options NUMBER default NOCASCADE, -- back out dependent txn timeHint TIMESTAMP default MINTIME -- time hint on the txn start
);
DESC DBA_FLASHBACK_TXN_STATE;
---- Flashback -----------------------
Flashback setup process:
----- (With FLASHBACK ARCHIVE ADMINISTER previlege) ----
CREATE FLASHBACK ARCHIVE fda1 TABLESPACE fda_tns1 QUOTA 10M RETENTION 1 YEAR;
With FLASHBACK ARCHIVE object privileges' :
ALTER TABLE HR.EMPLOYEES FLASHBACK ARCHIVE fda1;
--- Create the Flashback Data Archive -1
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1 QUOTA 10G RETENTION 5 YEAR;
-- Specify the default Flashback Data Archive-2
ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
-- Enable Flashback Data Archive-3
ALTER TABLE inventory FLASHBACK ARCHIVE;
ALTER TABLE stock_data FLASHBACK ARCHIVE;
SELECT product_number, product_name, count FROM inventory AS OF TIMESTAMP TO_TIMESTAMP ('2017-06-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
-- Optionally, Adding space: -4
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;
-- Optionally, changing retention time: -5
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
-- Optionally, purging data: -6
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' day);
-- Optionally, dropping a Flashback Data Archive: -7
DROP FLASHBACK ARCHIVE fla1;
BYPASSING THE Recycle bin:
DROP TABLE [PURGE] ;
DROP TABLESPACE
[INCLUDING CONTENTS];
DROP USER [CASCADE];
----- MAKE THE RECYCLEBIN OFF
SHOW PARAMETER RECYCLEBIN
ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;
With your database open for at least a day, you can flash back the database one day with the following statements:
SHUTDOWN DATABASE
STARTUP MOUNT
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
FLASHBACK DATABASE TO SCN 42963;
SELECT CURRENT_SCN FROM V$DATABASE;
CREATE TABLE temp_employees (X NUMBER) TABLESPACE USERS;
INSERT INTO TEMP_EMPLOYEES VALUES (1);
INSERT INTO TEMP_EMPLOYEES VALUES (2);
COMMIT;
drop table tabsys;
FLASHBACK TABLE TEMP_EMPLOYEES TO BEFORE DROP;
Querying the Recycly Bin :
SELECT owner,original_name,object_name,type,ts_name,droptime,related,space FROM dba_recyclebin WHERE can_undrop = 'YES';
SELECT original_name,object_name,ts_name,droptime FROM user_recyclebin WHERE can_drop = 'YES';
The Flashback Database operation:
• Works like a rewind button for the database
• Can be used in cases of logical data corruptions made by users
--- Configuring the Flashback Database:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
You can configure Flashback Database as follows:
1. Configure the Fast Recovery Area.
2. Set the retention target with the DB_FLASHBACK_RETENTION_TARGET initialization parameter. You can specify an upper limit, in minutes, on how far back you want to be able to flash back the database. The example uses 2,880 minutes, which is equivalent to two days. This parameter is only a target and does not provide any guarantee. Your flashback time interval depends on how much flashback data has been kept in the Fast Recovery Area.
3. Enable Flashback Database with the following command:
ALTER DATABASE FLASHBACK ON;
Before you can issue the command to enable Flashback Database, the database must be configured for archiving. You can determine whether Flashback Database is enabled with the following query:
SELECT flashback_on FROM v$database;
You can disable Flashback Database with the
ALTER DATABASE FLASHBACK OFF
command. As a result, all existing Flashback Database logs are deleted automatically.
Note: You can enable Flashback Database only when the database is mounted in exclusive mode, not open.
Flashback Database: Examples:
• To flash back: Mounted (in exclusive mode) database
RMAN> FLASHBACK DATABASE TO TIME =
"TO_DATE('2009-05-27 16:00:00',
'YYYY-MM-DD HH24:MI:SS')";
RMAN> FLASHBACK DATABASE TO SCN=23565;
RMAN> FLASHBACK DATABASE
TO SEQUENCE=223 THREAD=1;
SQL> FLASHBACK DATABASE
TO TIMESTAMP(SYSDATE-1/24);
SQL> FLASHBACK DATABASE TO SCN 53943;
SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;
• To review changes: Read-only opened database
• To finalize: Read/write opened database with RESETLOGS
Monitor progress of Flashback Database with the V$SESSION_LONGOPS view.
Flashback Database: Examples You can use the RMAN FLASHBACK DATABASE command to execute the Flashback Database operation. You can use SEQUENCE and THREAD to specify a redo log sequence number and thread as a lower limit. RMAN selects only files that can be used to flash back to, but not including, the specified sequence number.
Alternatively, you can use the SQL FLASHBACK DATABASE command to return the database to a past time or SCN. If you use the TO SCN clause, you must provide a number. If you specify TO TIMESTAMP, you must provide a time stamp value. You can also specify a restore point name. You can monitor the Flashback Database progress with the V$SESSION_LONGOPS view.
Note: The database must be mounted in exclusive mode to issue the FLASHBACK DATABASE command and opened read-only to review changes. The database must be opened read/write with the RESETLOGS option when finished.
Monitoring Flashback Database :
To monitor the ability to meet your retention target:
• View the Fast Recovery Area disk quota:
• Determine the current flashback window:
• Monitor logging in the Flashback Database logs:
SQL> SELECT estimated_flashback_size,
flashback_size
FROM V$FLASHBACK_DATABASE_LOG;
SQL> SELECT * FROM V$FLASHBACK_DATABASE_STAT;
SQL> SELECT oldest_flashback_scn, oldest_flashback_time
FROM V$FLASHBACK_DATABASE_LOG;
Monitoring Flashback Database :
It is important for you to monitor space usage of the Fast Recovery Area so that you know how well you are meeting your retention target. Use the V$FLASHBACK_DATABASE_LOG view to monitor the Flashback Database retention target:
• ESTIMATED_FLASHBACK_SIZE uses previously logged flashback data to provide an estimate of how much disk space is needed in the Fast Recovery Area for flashback logs to meet the current flashback retention target. The estimate is based on the workload since the instance was started, or during the most recent time interval equal to the flashback retention target, whichever is shorter.
• FLASHBACK_SIZE gives you the current size, in bytes, of the flashback data.
• OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME display the approximate lowest SCN and time to which you can flash back your database. CURRENT_SCN in
V$DATABASE gives you the current database SCN. Use the V$FLASHBACK_DATABASE_STAT view to monitor the overhead of logging flashback data in the Flashback Database logs. This view contains 24 hours of information, with each row representing a one-hour time interval. You can use this view to determine rate changes in the flashback data generation.
SQL> SELECT begin_time, end_time, flashback_data, db_data, redo_data, estimated_flashback_size AS EST_FB_SZE FROM V$FLASHBACK_DATABASE_STAT;
Monitoring the flashback database:
SQL> SELECT name, space_limit AS quota, 2 space_used AS used, 3 space_reclaimable AS reclaimable,4 number_of_files AS files 5 FROM v$recovery_file_dest
Guaranteed Restore Points
A guaranteed restore point ensures that you can perform a FLASHBACK DATABASE command to that SCN at any time.
SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
