Oracle Openworld Directory Index
What is the fastest time ?
The Fastest Time….
rm –rf *
The Problem…
A user performs DML to a few tables
The user commits the data
The user is connected to the wrong database
Recovery is now required
Possible Recovery Options
Point in time recovery
Incomplete recovery
Export/Import utilities
Export file too old
LogMiner
May not have the database in archivelog mode
Flashback Query
Flashback Query Overview
Uses DBMS_FLASHBACK
Provides user interface to the database
View historical data
Uses System Change Numbers (SCNs)
Generate consistent read images
Flashback Query Benefits
Immediately available
Self service repair
No DBA involvement
Online operations
No changes to physical structure
Transparent
Applications only see historical data
Supporting Flashback Query
Undo retention
Undo Tablespaces
Automatic Undo Management
Configuring Undo Retention
Dynamic system parameter UNDO_RETENTION
Time in seconds to retain committed undo data
Reduces ORA-1555 errors
Calculating Undo Retention
Size = (UNDO_RETENTION * UBS) + overhead
UNDO_RETENTION
the time in seconds
UBS
undo blocks per second
Overhead
space required for bitmap headers, segment blocks, transaction tables
Example Undo Calculation
Database blocksize is 8k
UBS is estimated at 150
UNDO_RETENTION desired is 30 minutes
Size = ( 30 minutes * 60 seconds ) * 150 blocks per second * 8192 blocksize
Size = 1.105GB
V$UNDOSTAT
SQL> select begin_time, end_time, undoblks
2 from v$undostat;
BEGIN_TIME END_TIME UNDOBLKS
-------------------- ---------------- ----------------
15:20:13 15:25:52 121228
15:10:13 15:20:13 385821
15:00:13 15:10:13 103597
14:50:13 15:00:13 102211
UNDOBLKS = total number of undo blocks used during the interval
Undo Tablespaces
Used for storing undo data
Can only contain undo segments
Automatic undo segment creation
Undo segments are system named
Locally managed auto allocate tablespace
Creating Undo Tablespaces
Two methods available to create undo tablespaces
During database creation with CREATE DATABASE
After database creation with CREATE UNDO TABLESPACE
Create Database Option
CREATE DATABASE
LOGFILE
GROUP 1 ‘/u01/oradata/dba/redo01.log’ size 25m,
GROUP 2 ‘/u01/oradata/dba/redo02.log’ size 25m
UNDO TABLESPACE UNDO1 DATAFILE ‘/u02/oradata/dba/undo01.dbf’ size 100m
DATAFILE ‘/u02/oradata/dba/system01.dbf’ size 100m;
Create Tablespace Option
CREATE UNDO TABLESPACE UNDO2 DATAFILE ‘/u02/oradata/dba/undo02.dbf’ size 100m;
Uses default block size
Create Tablespace Option
CREATE UNDO TABLESPACE UNDO2
DATAFILE ‘/u02/oradata/dba/undo02.dbf’ size 100m blocksize 2k;
DB_xK_CACHE_SIZE needs to be configured
x is the blocksize of the tablespace
Can use non-standard blocksizes of 2k, 4k, 8k, 16k, 32k
Undo Tablespace
Automatic Undo Segment Creation
SESSIONS UNDO_SEGMENTS
--------------- ---------------------------
13 2
14 3
19 4
23 5
28 6
32 7
37 8
41 9
46 10
Undo Tablespaces
Locally managed, auto allocate
EXTENT_SIZE TOTAL_BYTES COUNT
-------------------- --------------------- -----------
65536 1048576 16
1048576 66060288 63
8388608 1006632960 120
67108864 7247757312 108
Automatic Undo Management
Uses undo segments
Automatic undo segment configuration
Easier maintenance for concurrent transactions
reduces ORA-1555 errors
Replaces traditional rollback segments
Rollback segments still available
Automatic Undo Management
Undo segments contain an initial and next
One transaction per undo segment
Create more segments as needed
Automatic Undo Management
Alert log information
Tue Oct 16 00:43:24 2001
Created Undo Segment _SYSSMU905$
Undo Segment 905 Onlined
Tue Oct 16 00:43:25 2001
Created Undo Segment _SYSSMU906$
Undo Segment 906 Onlined
Undo Tablespace Fragmentation
EXTENT_SIZE TOTAL_EXTENTS TOTAL_BYTES
-------------------- ------------------------- ---------------------
65536 276 18087936
131072 134 17563648
196608 72 14155776
262144 40 10485760
327680 27 8847360
393216 7 2752512
458752 5 2293760
524288 3 1572864
589824 1 589824
655360 3 1966080
720896 1 720896
851968 1 851968
917504 1 917504
Undo Segment Cleanup
SMON maintenance at 12 hour intervals
1st 12 hours
drop EXPIRED extents
Mark unneeded undo segments to be taken offline
2nd 12 hours
Offline undo segments
3rd 12 hours
Drop undo segments
DBA_UNDO_EXTENTS
SQL> select segment_name, commit_wtime,
2 status from dba_undo_extents;
SEGMENT_NAME COMMIT_WTIME STATUS
------------------------ ------------------------ ----------------
_SYSSMU3$ 10/19/2001 13:26:55 UNEXPIRED
_SYSSMU3$ 10/19/2001 13:26:55 UNEXPIRED
_SYSSMU4$ 10/19/2001 13:28:57 UNEXPIRED
DBMS_FLAHSBACK Package
ENABLE_AT_TIME
ENABLE_AT_SYSTEM_CHANGE_NUMBER
DISABLE
GET_SYSTEM_CHANGE_NUMBER
Viewing Historical Data
Determine time from the past
Enable Flashback Query using time or SCN
Execute query
Disable Flashback Query
Time-based Flashback Example
Table is created at time 13:45:40
10 rows are individually inserted and committed at 75 second intervals
Last row committed at 13:58:25
Table contains 10 rows
Time-based Flashback Example
SQL>select * from TABLE1;
Time-based Flashback Example
Flashback mode will be enabled when value 4 is committed at time 13:50:44
Time-based Flashback Example
SQL> execute dbms_flashback.-
> enable_at_time(‘19-OCT-2001 13:50:44’);
SQL> select * from table1;
SMON_SCN_TIME
Maps TIMESTAMP to SCN
Updated by SMON every five minutes
Stores five days worth of information
Tracks server availability, not wall time
SMON_SCN_TIME
All Flashback Queries need SCNs
SMON_SCN_TIME resolution is five minutes
Exact time match in SMON_SCN_TIME uses associated SCN
Intermediate time is rounded down to five minute interval to determine SCN
Updating SMON_SCN_TIME
SMON_SCN_TIME
SQL> select TIME_DP, SCN_BAS
2 from sys.smon_scn_time;
TIME_DP SCN_BAS
--------------------------------- --------------
19-OCT-2001 13:43:48 10415734
19-OCT-2001 13:48:55 10415791
19-OCT-2001 13:54:02 10416105
19-OCT-2001 13:59:09 10416417
19-OCT-2001 14:04:17 10416719
Time-based Flashback Revisited
Value 4 is committed at 13:50:44
Exact time not found in SMON_SCN_TIME
Time falls between interval of
TIME_DP SCN_BAS
--------------------------------- --------------
19-OCT-2001 13:48:55 10415791
19-OCT-2001 13:54:02 10416105
Time rounded down and SCN 10415791 is selected
Only 2 rows committed at this SCN
Time-based Flashback Revisited
Time is 19-OCT-2001 13:48:55
SCN is10415791
SCN-based Flashback Query
Uses SCN to view exact points in time
Does not round down values
Not restricted to five minute intervals
SCN is required
SCN-based Flashback Example
SQL> execute dbms_flashback.-enable_at_system_change_number(10415904);
SQL> select * from table1;
ID
----------
1
2
3
4
Ending Flashback Query
DBMS_FLASHBACK.DISABLE
Ends current flashback mode
Return to normal read-write state
Flashback Query Restrictions
No DML or DDL in flashback mode
Cannot execute as user SYS
Must exit current flashback mode before flashing back to a different time
Bounded by SMON_SCN_TIME
DBMS_FLASHBACK Recovery
Determine flashback point
Enable Flashback Query
Open cursor for select
Disable Flashback Query
Perform DML to recover modified data
Recovery with Implicit Cursors
dbms_flashback.enable_at_time(’10-19-01 13:50’);
FOR Table1_Rec in ( select * from table1 ) loop
dbms_flashback.disable;
INSERT into TABLE1 values ( Table1_Rec.Id );
END LOOP;
Recovery with Implicit Cursors
Requires extra system overhead
DBMS_FLASHBACK.DISABLE must be called for each loop iteration
Not the most efficient method
Recovery with Explicit Cursors
CURSOR OldImage is SELECT * FROM TABLE1;
BEGIN
dbms_flashback.enable_at_time(’10-19-01 13:50’);
OPEN OldImage;
dbms_flashback.disable;
LOOP
FETCH OldImage……
EXIT WHEN OldImage%NOTFOUND;
INSERT into TABLE1….
END LOOP;
Recovery with Explicit Cursors
Flashback Query is disabled only once
Less system overhead
Every loop iteration retrieves data from explicit cursor
More efficient than implicit cursors
Faster execution for large data sets
Exporting data with Flashback
Export utility has added flashback support
FLASHBACK_TIME
FLASHBACK_SCN
Available only with command line parameters
exp scott/tiger tables=table1 flashback_scn=161606
Storing SCNs for Future Use
How to find current SCN??
DBMS_FLASHBACK
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
141382
Can be used in queries or DML
No restrictions of previous two methods
Storing SCNs For Future Use
create or replace trigger table1_trigger
before insert or update or delete on Table1
begin
insert into table1_scn values ( sysdate,
sys.dbms_flashback.get_system_change_number,
sys_context('USERENV', 'SESSION_USER'));
end;
Summary
Flashback Query to view historical data
Configure UNDO_RETENTION
Create undo tablespaces
Requires Automatic Undo Management
Interface through DBMS_FLASHBACK
Self service recovery of modified data