ASM spfile

ASM spfile in a disk group

Starting with ASM version 11.2, the ASM spfile can be stored in an ASM disk group. Indeed, during a new ASM installation, the Oracle Universal Installer (OUI) will place the ASM spfile in the disk group that gets created during the installation. This is true for both Oracle Restart (single instance environments) and Cluster installations. It should be noted that the first disk group created during the installation is the default spfile location, but not a requirement. The spfile can still be on a file system, in say $ORACLE_HOME/dbs directory.

New ASMCMD commands

To support this feature, new ASMCMD commands were introduced to back up, copy and move the ASM spfile. The commands are:

• spbackup - backs up an ASM spfile to a backup file. The backup file is not a special file type and is not identified as an spfile.
• spcopy - copies an ASM spfile from the source location to an spfile in the destination location.
• spmove - moves an ASM spfile from source to destination and automatically updates the GPnP profile.

The SQL commands CREATE PFILE FROM SPFILE and CREATE SPFILE FROM PFILE are still valid for the ASM spfile stored in the disk group.

ASM spfile in disk group DATA

In my environment, the ASM spfile is (somewhere) in the disk group DATA. Let's find it:

$ asmcmd find --type ASMPARAMETERFILE +DATA "*"

+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.822856169

As we can see, the ASM spfile is in a special location and it has ASM file number 253. The ASM spfile stored in the disk group is a registry file, and will always be the ASM metadata file number 253.

Of course, we see the same thing from the sqlplus:

$ sqlplus / as sysasm

SQL> show parameter spfile

NAME   TYPE   VALUE

------ ------ -------------------------------------------------

spfile string +DATA/ASM/ASMPARAMETERFILE/registry.253.822856169

SQL>

Let's make a backup of that ASM spfile.

$ asmcmd spbackup +DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.822856169 /tmp/ASMspfile.backup

And check out the contents of the file:

$ strings /tmp/ASMspfile.backup

+ASM.__oracle_base='/u01/app/grid'#ORACLE_BASE set from in memory value

+ASM.asm_diskgroups='RECO','ACFS'#Manual Mount

*.asm_power_limit=1

*.large_pool_size=12M

*.remote_login_passwordfile='EXCLUSIVE'

As we can see, this is a copy of the ASM spfile, that includes the parameters and associated comments.

ASM spfile discovery

So, how can the ASM instance read the spfile on startup, if the spfile is in a disk group that is not mounted yet? Not only that - the ASM doesn't really know which disk group has the spfile, or even if the spfile is in a disk group. And what is the value of the ASM discovery string?

The ASM Admin guide says this on the topic:

When an Oracle ASM instance searches for an initialization parameter file, the search order is:

1. The location of the initialization parameter file specified in the Grid Plug and Play (GPnP) profile.
2. If the location has not been set in the GPnP profile, then the search order changes to:
1. SPFILE in the Oracle ASM instance home (e.g. $ORACLE_HOME/dbs/spfile+ASM.ora)
2. PFILE in the Oracle ASM instance home

This does not tell us anything about the ASM discovery string, but at least it tells us about the spfile and the GPnP profile. It turns out the ASM discovery string is also in the GPnP profile. Here are the values from an Exadata environment:

$ gpnptool getpval -p=profile.xml -asm_dis -o-

o/*/*

$ gpnptool getpval -p=profile.xml -asm_spf -o-

+DBFS_DG/spfileASM.ora

There is no GPnP profile in a single instance set up, so this information is in the ASM resource (ora.asm), stored in the Oracle Local Repository (OLR). Here are the values from a single instance environment:

$ crsctl stat res ora.asm -p | egrep "ASM_DISKSTRING|SPFILE"

ASM_DISKSTRING=

SPFILE=+DATA/ASM/ASMPARAMETERFILE/registry.253.822856169

So far so good. Now the ASM knows where to look for ASM disks and where the spfile is. But the disk group is not mounted yet, as the ASM instance still hasn't started up, so how can ASM read the spfile?

The trick is in the ASM disk headers. To support the ASM spfile in a disk group, two new fields were added to the ASM disk header:

• kfdhdb.spfile - Allocation unit number of the ASM spfile.
• kfdhdb.spfflg - ASM spfile flag. If this value is 1, the ASM spfile is on this disk in allocation unit kfdhdb.spfile.

As part of the disk discovery process, the ASM instance reads the disk headers and looks for the spfile information. Once it finds the disks that have the spfile, it can read the actual initialization parameters.

Let's have a look at my disk group DATA. First check the disk group state and redundancy

$ asmcmd lsdg -g DATA | cut -c1-26

Inst_ID  State    Type

     1  MOUNTED  NORMAL

The disk group is mounted and the redundancy is normal. This means the ASM spfile will be mirrored, so we should see two disks with kfdhdb.spfileand kfdhdb.spfflg values set. Let's have a look:

$ for disk in `asmcmd lsdsk -G DATA --suppressheader`

> do

> echo $disk

> kfed read $disk | grep spf

> done

/dev/sdc1

kfdhdb.spfile:                       46 ; 0x0f4: 0x0000002e

kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001

/dev/sdd1

kfdhdb.spfile:                     2212 ; 0x0f4: 0x000008a4

kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001

/dev/sde1

kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000

kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

As we can see, two disks have the ASM spfile.

Let's check the contents of the Allocation Unit 46 on disk /dev/sdc1:

$ dd if=/dev/sdc1 bs=1048576 skip=46 count=1 | strings

+ASM.__oracle_base='/u01/app/grid'#ORACLE_BASE set from in memory value

+ASM.asm_diskgroups='RECO','ACFS'#Manual Mount

*.asm_power_limit=1

*.large_pool_size=12M

*.remote_login_passwordfile='EXCLUSIVE'

1+0 records in

1+0 records out

1048576 bytes (1.0 MB) copied, 0.0352732 s, 29.7 MB/s

The AU 46 on disk /dev/sdc1 indeed contains the ASM spfile.

ASM spfile alias block

In addition to the new ASM disk header fields, there is a new metadata block type - KFBTYP_ASMSPFALS - that describes the ASM spfile alias. The ASM spfile alias block will be the last block in the ASM spfile.

Let's have a look at the last block of the Allocation Unit 46:

$ kfed read /dev/sdc1 aun=46 blkn=255

kfbh.endian:                          1 ; 0x000: 0x01

kfbh.hard:                          130 ; 0x001: 0x82

kfbh.type:                           27 ; 0x002: KFBTYP_ASMSPFALS

kfbh.datfmt:                          1 ; 0x003: 0x01

kfbh.block.blk:                     255 ; 0x004: blk=255

kfbh.block.obj:                     253 ; 0x008: file=253

kfbh.check:                   806373865 ; 0x00c: 0x301049e9

kfbh.fcn.base:                        0 ; 0x010: 0x00000000

kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000

kfbh.spare1:                          0 ; 0x018: 0x00000000

kfbh.spare2:                          0 ; 0x01c: 0x00000000

kfspbals.incarn:              822856169 ; 0x000: 0x310bc9e9

kfspbals.blksz:                     512 ; 0x004: 0x00000200

kfspbals.size:                        3 ; 0x008: 0x0003

kfspbals.path.len:                    0 ; 0x00a: 0x0000

kfspbals.path.buf:                      ; 0x00c: length=0

There is not much in this metadata block. Most of the entries have the block header info (fields kfbh.*). The actual ASM spfile alias data (fields kfspbals.*) has only few entries. The spfile file incarnation (822856169) is part of the file name (REGISTRY.253.822856169), the block size is 512 (bytes) and the file size is 3 blocks. The path info is empty, meaning I don't actually have the ASM spfile alias.

Let's create one. I will first create a pfile from the existing spfile and then create the spfile alias from that pfile.

$ sqlplus / as sysasm

SQL> create pfile='/tmp/pfile+ASM.ora' from spfile;

File created.

SQL> shutdown abort;

ASM instance shutdown

SQL> startup pfile='/tmp/pfile+ASM.ora';

ASM instance started

Total System Global Area 1135747072 bytes

Fixed Size                  2297344 bytes

Variable Size            1108283904 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

SQL> create spfile='+DATA/spfileASM.ora' from pfile='/tmp/pfile+ASM.ora';

File created.

SQL> exit

Looking for the ASM spfile again shows two entries:

$ asmcmd find --type ASMPARAMETERFILE +DATA "*"

+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.843597139

+DATA/spfileASM.ora

We now see the ASM spfile itself (REGISTRY.253.843597139) and its alias (spfileASM.ora). Having a closer look at spfileASM.ora confirms this is indeed the alias for the registry file:

$ asmcmd ls -l +DATA/spfileASM.ora

Type              Redund  Striped  Time             Sys  Name

ASMPARAMETERFILE  MIRROR  COARSE   MAR 30 20:00:00  N    spfileASM.ora => +DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.843597139

Check the ASM spfile alias block now:

$ kfed read /dev/sdc1 aun=46 blkn=255

kfbh.endian:                          1 ; 0x000: 0x01

kfbh.hard:                          130 ; 0x001: 0x82

kfbh.type:                           27 ; 0x002: KFBTYP_ASMSPFALS

kfbh.datfmt:                          1 ; 0x003: 0x01

kfbh.block.blk:                     255 ; 0x004: blk=255

kfbh.block.obj:                     253 ; 0x008: file=253

kfbh.check:                  2065104480 ; 0x00c: 0x7b16fe60

kfbh.fcn.base:                        0 ; 0x010: 0x00000000

kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000

kfbh.spare1:                          0 ; 0x018: 0x00000000

kfbh.spare2:                          0 ; 0x01c: 0x00000000

kfspbals.incarn:              843597139 ; 0x000: 0x32484553

kfspbals.blksz:                     512 ; 0x004: 0x00000200

kfspbals.size:                        3 ; 0x008: 0x0003

kfspbals.path.len:                   13 ; 0x00a: 0x000d

kfspbals.path.buf:        spfileASM.ora ; 0x00c: length=13

Now we see that the alias file name appears in the ASM spfile alias block. Note the new incarnation number, as this is a new ASM spfile, created from the pfile.

Conclusion

Starting with ASM version 11.2, the ASM spfile can be stored in an ASM disk group. To support this feature, we now have new ASMCMD commands and, under the covers, we have new ASM metadata structures.

Upgrade Oracle 11.2.0.4 standalone Grid and ASM to Oracle 12.1.0.2

This Document describe the procedure to upgrade the Oracle 11.2.0.4 standalone Grid and ASM to Oracle 12.1.0.2 standalone Grid and ASM using OUI Oracle Universal Installer.
Starting from Oracle 11g , Oracle recommends out of place upgrades. means upgrade will happen in new ORACLE_HOME.

Prerequisites :-
1. Take a full Backup of Database and existing GRID_HOME.
2. Create new directory structure for the new GRID_HOME.
3. Copy the Oracle Grid 12c binaries at your choice location and unzip it.
4. unset the following before starting the upgrade.

  unset ORACLE_BASE
  unset ORACLE_HOME
  unset ORACLE_SID

In my testing environment i'm using Oracle user as owner of my GRID_HOME.

As Oracle user run installer.



Installation Option.
choose Upgrade Oracle Grid infrastructure or Oracle ASM

Select Language.

Management option provide option to register with Enterprise Manager to manage the Grid and ASM.

i'm using only DBA group. we can use different groups to separate the duties.

It will show the warning cause i'm using DBA group only. 

Specify the new location of the ORACLE_BASE and ORACLE_HOME.

Click on Automatically run configuration scripts. it will prompt to provide the root password to execute the scripts by itself.

Due to limited resources on my laptop, i have allocated 2G memory in my virtual box. ignore this error. In real environment all prerequisites should met.
For OS Kernel Parameter panic_on_oops.

It will show warning cause i have ignored prerequisites. click on yes.

Review the summary. and click on install.

Review the progress.

New window will pop-up to confirm the execution of scripts as root. Click on yes.

Once the scripts executed. we can see the upgrade of GRID was successful window. click on close.

Verify the  release version and software version.

[oracle@oracle123 bin]$ ./crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [12.1.0.2.0]
[oracle@oracle123 bin]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [12.1.0.2.0]


Connect to the ASM instance and verify the version.

[oracle@oracle123 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 22 18:03:32 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option
SQL> select * from v$version;

BANNER      CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production   0
PL/SQL Release 12.1.0.2.0 - Production   0
CORE 12.1.0.2.0 Production   0
TNS for Linux: Version 12.1.0.2.0 - Production   0
NLSRTL Version 12.1.0.2.0 - Production

Connect to ASM instance and change the Compatible parameter to enable new features.

alter diskgroup data set attribute 'compatible.asm'='12.1.0.2';
alter diskgroup fra set attribute 'compatible.asm'='12.1.0.2';

Verify the COMPATIBILITY.

SQL> select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME     COMPATIBILITY            DATABASE_COMPATIBILITY
------------ ------------------------------ ------------------------------------------------------------ ------------------
   2 FRA     12.1.0.2.0       10.1.0.0.0
   1 DATA     12.1.0.2.0       10.1.0.0.0


Change the diagnostic_dest to new GRID_HOME

alter system set diagnostic_dest='/u02/app/grid' scope=spfile;

Once we find everything is working fine from new GRID_HOME. we can delete the old GRID_HOME.

UNDO and temp tablespace

Change or switch undo and Temp tablespace in Oracle database

Change or switch undo tablespace in Oracle database :
---------------------------------------------------------

      The complexity comes in if there were pending transactions in your old undo tablespace and you have already switched to the new one and when you try to drop the old one it says "Tablespace is in use".

How to switch the database to a new UNDO tablespace and drop the old one
$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
undo_management       string      AUTO
undo_retention               integer    900
undo_tablespace           string      UNDOTBS1

The current undo tablespace as suggested by the initialization parameter undo_tablespace is UNDOTBS1. Leave this sysdba as is, open another console, log in as user SCOTT and initiate a transaction.

$ sqlplus scott/tiger

SQL>update emp set sal = sal + 1000 where empno=7839;
1 row updated.

With an update on emp table we have initiated a transaction. The undo data is written to a segment in the UNDOTBS1 tablespace. Now leave this SCOTT's session intact and go back to the sysdba console without issuing any COMMIT or ROLLBACK.

SQL>CREATE UNDO TABLESPACE undotbs2
DATAFILE '/d01/apps/oradata/oraxpo/undotbs201.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

-- We created a new UNDO tablespace named UNDOTBS2

SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

System altered.

-- Switch the database to the new UNDO tablespace.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

-- Try to drop the tablespace but failed.

With the alter system set undo_tablespace=UNDOTBS2, the database UNDO tablespace is changed and any new transaction's undo data will go to the new tablespace i.e. UNDOTBS2. 

But the undo data for already pending transaction (e.g. the one initiated by SCOTT before the database UNDO tablespace switch) is still in the old tablespace with a status of PENDING OFFLINE. As far as it is there you cannot drop the old tablespace.

set lines 10000
column name format a10

SELECT a.name,b.status
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN (
  SELECT segment_name
  FROM dba_segments
  WHERE tablespace_name = 'UNDOTBS1'
 );

NAME            STATUS
----------            ---------------
_SYSSMU8$  PENDING OFFLINE

The above query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status. Now lets see which users/sessions are running this pending transaction.

column username format a6

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
  SELECT segment_name
  FROM dba_segments
  WHERE tablespace_name = 'UNDOTBS1'
 );

NAME            STATUS               USERNAME         SID    SERIAL#
----------    --------------     ------ ---------- ----------
_SYSSMU8$  PENDING OFFLINE SCOTT         147          4

So this is SCOTT with SID=147 and SERIAL#=4. Since we know now the user, we can go to him/her and request to end the transaction gracefully i.e. issue a ROLLBACK or COMMIT. However, if this is not possible (say the user initiated the transaction and left for annual leave :) and trust me this happens) you may go ahead and kill the session to release the undo segments in the UNDOTBS1 tablespace.

SQL> alter system kill session '147,4';

System altered.

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
  SELECT segment_name
  FROM dba_segments
  WHERE tablespace_name = 'UNDOTBS1'
 );
no rows selected

As we can see once the session is kills we don't see anymore segments occupied in the UNDOTBS1 tablespace. Lets drop UNDOTBS1.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
If you are retaining undo data then you still won't be able to drop the tablespace because it is still in use by undo_retention. Let the UNDO_RETENTION time pass and then try to drop the tablespace. In my case it is 900 seconds i.e. 15 minutes.

-- After 15 minutes.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.



How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g:
----------------------------------------------------------------

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

lashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.

The properties of the UNDO_RETENTION parameter are mentioned below:

• Parameter type – Integer
• Default value – 900
• Range of values – 0 to 2³² – 1
• Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;

However it is worth to tune the following important parameters

1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter

Calculate UNDO_RETENTION for given UNDO Tabespace

You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

Optimal Undo Retention =
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time.

Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

Optimal Undo Retention Calculation

Formula:
Optimal Undo Retention = 
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25)    "UNDO RETENTION [Sec]",
    ROUND((d.undo_size / (to_number(f.value) *
    g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
       FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Calculate Needed UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Automatic Undo Retention Tuning

Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old”errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:

Reset the undo low threshold.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Guarantee the minimum threshold is maintained.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
 
SELECT tablespace_name, retention FROM dba_tablespaces;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

Switch back to the default mode.

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

The Undo Advisor PL/SQL Interface

Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is ‘Undo Advisor‘. The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT. In the following example, the START_SNAPSHOT is “1” and END_SNAPSHOT is “2”.

DECLARE
   tid    NUMBER;
   tname  VARCHAR2(30);
   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', 1);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
   DBMS_ADVISOR.execute_task(tname);
end;