Managing Undo Tablespaces

Managing Undo Tablespaces

Obtaining Information on Undo
SHOW PARAMETER UNDO
NAME TYPE VALUE
----------------- ------- -------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS01
-- undo usage in 10-min-intervals for last 7 days
select TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
UNDOBLKS UNDO_BLOCKS,
MAXQUERYLEN MAX_QUERY_LENGTH_INSEC,
MAXQUERYID LONGEST_QUERY_ID,
TXNCOUNT TOTAL_TRANSACTIONS,
MAXCONCURRENCY
from V$UNDOSTAT
order by BEGIN_TIME DESC
-- stats history of V$UNDOSTAT
select
TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
INSTANCE_NUMBER,SNAP_ID,
UNDOBLKS,
TXNCOUNT TOTAL_TRANSACTIONS,
MAXQUERYLEN MAX_QUERY_LENGTH_INSEC,
MAXQUERYSQLID,
MAXCONCURRENCY,
SSOLDERRCNT ORA01555_CNT,
NOSPACEERRCNT NOSPACE_CNT,
ACTIVEBLKS,UNEXPIREDBLKS
from DBA_HIST_UNDOSTAT
-- undo segments
select SEGMENT_NAME,OWNER,STATUS from DBA_ROLLBACK_SEGS;

-- undo extents
select
e.SEGMENT_NAME,
e.TABLESPACE_NAME,
e.EXTENT_ID,
e.FILE_ID,
e.BLOCK_ID,
e.BYTES/1024 SIZE_KB,
e.RELATIVE_FNO,
e.STATUS -- ACTIVE EXPIRED UNEXPIRED
from DBA_UNDO_EXTENTS e
order by e.STATUS ASC
-- undo sizes by STATUS
select
e.TABLESPACE_NAME,
e.STATUS,
to_char(sum(e.BYTES/1024),'999,999,999,999') SIZE_KB
from DBA_UNDO_EXTENTS e
group by e.TABLESPACE_NAME, e.STATUS
order by e.STATUS
-- undo sizes consumed by active transactions by username
SELECT s.username, sum(t.used_ublk) used_undo_blocks
from v$session s, v$transaction t
where s.saddr = t.ses_addr
and t.status='ACTIVE'
group by s.username
order by s.username
Enabling Automatic Undo Management
select value from v$parameter where upper(name)='UNDO_MANAGEMENT';
alter system set UNDO_MANAGEMENT=AUTO scope=spfile ;
Creating an Undo Tablespace
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 200M REUSE AUTOEXTEND ON;
Setting Startup Undo Tablespace
select value from v$parameter where upper(name)='UNDO_TABLESPACE';
-- if there are multiple undo tablespaces
-- after all active transactions have committed, the undo tablespace automatically
-- goes from the PENDING OFFLINE mode to the OFFLINE mode.
alter system set UNDO_TABLESPACE='UNDOTBS1';
-- the following switches out current undo tablespace to the next available one
alter system set UNDO_TABLESPACE = '';
Tuning Undo Retention
 If the undo tablespace is configured with the AUTOEXTEND option, undo retention tuning
is slightly different. In this case, the database tunes the undo retention period to be
slightly longer than the longest-running query on the system at that time.
 For a fixed size undo tablespace, the database tunes the undo retention period based on
85% of the tablespace size, or on the warning alert threshold percentage for space used,
whichever is lower.
 If you want a fixed undo size, use the Undo Advisor to specify the proper undo size for
your requirement. You should estimate:

o The length of your expected longest running query
o The longest interval that you will require for flashback operations
-- determine the current retention period
select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention
from v$undostat order by end_time;
-- maximum query time
SELECT round(MAX(maxquerylen)/60) Minutes FROM v$undostat;
Using Undo Advisor
 Use OEM or PL/SQL
-- retreive available AWR snaps
select SNAP_ID, STARTUP_TIME, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT
order by SNAP_ID;
set serveroutput on
DECLARE
tid NUMBER; -- task ID
tname VARCHAR2(30); -- task name
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, null, oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 52);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 56);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
DBMS_ADVISOR.execute_task(tname);
DBMS_OUTPUT.PUT_LINE(tname);
end;
/
SELECT DBA_ADVISOR_RECOMMENDATIONS.GET_TASK_REPORT('TASKNAME')
FROM DUAL;
Setting the Undo Retention Period
 You must set UNDO_RETENTION parameter when:
o The undo tablespace has the AUTOEXTEND option enabled
o You want to set undo retention for LOBs
o You want retention guarantee
select value from v$parameter where upper(name)='UNDO_RETENTION';
alter system set UNDO_RETENTION = 2400;
Enabling Retention Guarantee
 Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.
select RETENTION from DBA_TABLESPACES where TABLESPACE_NAME='UNDOTBS1';
create undo tablespace undotbs01 .. RETENTION GUARANTEE;
alter tablespace undotbs1 RETENTION GUARANTEE;
alter tablespace undotbs1 RETENTION NOGUARANTEE;
Dropping an Undo Tablespace
DROP TABLESPACE undotbs_01;

To Drop a Corrupt UNDO Tablespace
An Undo tablespace containing a corrupted undo rollback segment may lead to one or more of
the following:
 ORA-00376: file xx cannot be read at this time
 SIMON process being hang waiting for the event "wait for a undo record" forever!
Identify the bad rollback segment(s)(Oracle would report it anyway):
-- looking for NEEDS RECOVERY or PARTIALLY AVAILABLE
select
segment_name, status
from dba_rollback_segs
where tablespace_name='undotbs_corrupt'
and status not in ('OFFLINE', 'ONLINE');
-- Let's say it's _SYSSMU9$
-- (optionally) create a new undo tablespace
-- and replace it with the current one
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 200M REUSE AUTOEXTEND ON;
alter system set UNDO_TABLESPACE='UNDOTBS2';
-- Next, create pfile and in it:
undo_management = MANUAL
_offline_rollback_segments=_SYSSMU9$
-- shutdown the db
-- open it using the pfile
-- drop the offending rollback segment
-- (and the undo tablespace altogether if the other one was created)
-- Note: datafiles might not actually be deleted, check them
drop tablespace .. including contents and datafiles
-- bounce the db (make it start using the spfile)
-- this means it will go back to using the automatic undo
Using Oracle Managed Files (OMF)
 OMF datafiles have to be created in one directory
set DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n, and DB_RECOVERY_FILE_DEST
-- when OMF enabled
CREATE TABLESPACE finance01;
ALTER TABLESPACE finance01 ADD DATAFILE 500M;
DROP TABLESPACE finance01; -- dbf auto deleted
-- create db with OMF
db_name=mydb
DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata'
DB_RECOVERY_FILE_DEST_SIZE = 100M
DB_RECOVERY_FILE_DEST = '/u04/app/oracle/oradata'
LOG_ARCHIVE_DEST_1 = 'LOCATION = USE_DB_RECOVERY_FILE_DEST'
SQL> connect sys/sys_passwd as sysdba
Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='initmydb.ora';
SQL> CREATE DATABASE mydb;