Showing posts with label Setup Oracle Transactional Flashback Capability (Automatic Undo). Show all posts
Showing posts with label Setup Oracle Transactional Flashback Capability (Automatic Undo). Show all posts

Wednesday, January 28, 2009

Setup Oracle Transactional Flashback Capability (Automatic Undo)

1. Check if Automatic Undo Management is begin used, undo tablespace is set and undo_retention is set?

SQL> select name, value from v$parameter where name like 'undo%';

NAME VALUE
---------------------- -----------------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900

2. If Not Using Automatic Undo Setup Automatic Undo

Create undo tablespace
create undo tablespace undotbs1 datafile ‘c:\oracle\data\ORCL\undotbl1.dbf’ size 500M ;

Set undo_tablespace to undo tablespace created
Alter system set undo_tablespace=undotbs1 scope=spfile ;

Set undo_retention to time period for transactional flashbacks alter system set undo_retention=900 scope=spfile ;


3. If had to setup auto undo bounce database
SQL> shutdown immediate
SQL> startup

4. Update transactional flashback sizing and retention over time
See Sizing Transactional Flashback Capabilities - Sizing the undo tablespace and undo_retention