Monday, 3 July 2017

Argument list too long

EM12c, EM13c: Cloud Control Agent Installation On RHEL 7 Machine
Fails With "/bin/uname: Argument list too long" (Doc ID 2281440.1)

Error find from BI log file.

 Enterprise Manager Cloud Control running the command "/apps/middleware/bin/emctl stop oms -all -force -debug". 

APPLIES TO:

Enterprise Manager Base Platform - Version 12.1.0.4.0 and later
Information in this document applies to any platform.
SYMPTOMS

Agent installation on RHEL 7 machine using push method from OEM cloud control or using silent
 method fails at agent deployment with following error in agentDeploy_<timestamp>.log:

================
Cloning the agent home...
INFO: /opt/app/OracleHomes/agent_13.1.0.0.0/bin/emctl: line 287: /usr/bin/uname: Argument list too long
INFO: /opt/app/OracleHomes/agent_13.1.0.0.0/bin/emctl: line 288: /usr/bin/uname: Argument list too long
INFO: /opt/app/OracleHomes/agent_13.1.0.0.0/bin/emctl: line 475: /opt/app/OracleHomes/agent_13.1.0.0.0/perl/bin/perl:
 Argument list too long
================

CAUSE

Issue is due to stack guard page added to Linux kernel as mentioned by Red Hat in the following link:
https://access.redhat.com/security/vulnerabilities/stackguard?sc_cid=70160000000gzltAAA&


SOLUTION

 Perform any of the following steps to resolve the issue:


Workaround 1:
==========


Kindly follow the below steps. 
[+] Backup <MIDDLEWARE_HOME>/bin/commonenv [+] Increase the stack thread size (EM_THREAD_STACK_SIZE) in the commonenv file as described below: 
from ===================================== if [ "$uname" = "Linux" ] ; then if [ "$EM_THREAD_STACK_SIZE" = "" ] ; then EM_THREAD_STACK_SIZE=3072 fi ulimit -S -s $EM_THREAD_STACK_SIZE 
if [ "$EM_DONT_CHANGE_CORE_SIZE" = "" ] ; then ulimit -S -c hard fi fi ===================================== 
TO 
===================================== if [ "$uname" = "Linux" ] ; then if [ "$EM_THREAD_STACK_SIZE" = "" ] ; then EM_THREAD_STACK_SIZE=8192 fi ulimit -S -s $EM_THREAD_STACK_SIZE 
if [ "$EM_DONT_CHANGE_CORE_SIZE" = "" ] ; then ulimit -S -c hard fi fi 
===================================== 
[+] In bash profile of the operating system user (user performing the OMS installation), set the following parameter: 

$ export EM_THREAD_STACK_SIZE=8192 

[+] If the UI is still open, then please click on "Retry" and continue with the installation.
 
[+] If you accidentally exit the installer before clicking Retry, then do NOT restart the installer to reach the same screen; instead, invoke the runConfig.sh script from the Oracle home to rerun the Configuration Assistant in silent mode. For Microsoft Windows platforms, invoke runConfig.bat script. 

$<ORACLE_HOME>/oui/bin/runConfig.sh ORACLE_HOME=<absolute_path_to_Oracle_home> MODE=perform ACTION=configure COMPONENT_XML={encap_oms.1_0_0_0_0.xml} 



a. In bash profile of the operating system user (user performing the agent installation .bash_profile, .bash_rc), 
set the following parameter:
      $ export EM_THREAD_STACK_SIZE=8192


b. Retry the agent installation



Workaround 2:
==========
As agent home will be created already as part of the agent installation process, so run the following command to
 complete the agent installation:


a. In bash profile of the operating system user (user performing the agent installation .bash_profile, .bash_rc),
set the following parameter:
         $ export EM_THREAD_STACK_SIZE=8192

b. Rename the agent instance (agent_inst) folder if exists

c. Configure the agent by running the following command:
            $ cd <AGENT HOME>/sysman/install
            $ ./agentDeploy.sh AGENT_BASE_DIR=<agent base directory> AGENT_INSTANCE_HOME=<agent instance home> OMS_HOST=<oms hostname> EM_UPLOAD_PORT=<oms upload port> > AGENT_REGISTRATION_PASSWORD=<agent registration password> -configOnly

Wednesday, 7 June 2017

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 on 12c PDB

The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.
In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.
Like the database character set, the national character set is defined when the database is initially
created and can usually no longer be changed, at least not easily or without involving quite a lot of work  (export, recreate database, import).
Except when creating the database, where the national character set is defined explicitly,


You require SYSDBA authorization to change the national character set.
Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.

Connect PDB as sysdba

[oracle@CDB ~]$ sqlplus sys/password@SID as sysdba

SQL> Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SQL> Select owner, table_name, column_name
     from dba_tab_columns
     where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and
     owner != 'SYS' and owner != 'SYSTEM';

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
NLS_CHARACTERSET
AL32UTF8                                                                  0
NLS_NCHAR_CHARACTERSET
AL16UTF16                                                                 0

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                   AL16UTF16



Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> STARTUP RESTRICT;
Pluggable Database opened.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> update props$
set value$='UTF8'
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); 

2 rows updated.

SQL> commit;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Pluggable Database closed.

SQL> ALTER DATABASE OPEN ;
Database altered.

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                    UTF8

Monday, 5 June 2017

ORA-28374


When I am trying to create tablespace on pluggable databases (PDB) facing below issue.

SQL Error: ORA-28374: typed master key not found in wallet
28374. 0000 -  "typed master key not found in wallet"

*Cause:    You attempted to access encrypted tablespace or redo logs with
           a typed master key not existing in the wallet.

*Action:   Copy the correct Oracle Wallet from the instance where the tablespace
           was created.


Solution

Login with CDB and follow below steps.

[oracle@JCDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 5 15:53:48 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options


SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

SQL> select '!rm ' || wrl_parameter || 'cwallet.sso' from v$encryption_wallet;

'!RM'||WRL_PARAMETER||'CWALLET.SSO'
--------------------------------------------------------------------------------
!rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> !rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
CLOSED                         UNKNOWN                       0


SQL> administer key management set keystore open identified by sys_password container=all;

keystore altered.

SQL> administer key management set key identified by sys_password with backup;

keystore altered.

SQL> administer key management set key identified by sys_password with backup container=all;

keystore altered.


Retest issue.


SQL> alter session set container=PDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB

SQL> CREATE TABLESPACE tablespace-name DATAFILE '/u02/app/oracle/oradata/CDB/data01.dbf' size 1024M;

Tablespace created.


issue resolved.

ORA-29874: warning in the execution of ODCIINDEXALTER routine

R12 E-Business Suite Autoconfig Fails On Script jtfictx.sh With Errors ORA-29874: warning in the execution of ODCIINDEXALTER routine And DRG-100595 and DRG-11000 After An Upgrade To 12.1.3 And RDBMS 12.1.0.1 (Doc ID 2223844.1) To BottomTo Bottom


APPLIES TO:

Oracle Applications DBA - Version 12.1.3 and later
Information in this document applies to any platform.
SYMPTOMS

E-Business Suite R12 Applications DBA, Upgrade Related issues

 After an upgrade from R12.0.6 to R12.1.3 including a database migration from 11.2.0.3 to 12.1.0.1,  AutoConfig fails with the following error:

ERROR
-----------------------
jtfictx.sh started at Mon Jan 16 09:00:00 2017


SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 16 09:00:00 2017

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

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-11000: invalid keyword SYNC
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 96


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
jtfictx.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.
...
...
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
 

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
  Directory: /prodapps/PROD/inst/apps/UPDB8_oupapp/admin/install
  jtfictx.sh INSTE8_PRF 1


AutoConfig is exiting with status 1

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade R12.0.6 to R12.1.3
2. Upgrade database from 11.2.0.3 to 12.1.0.1.
3. Run AutoConfig on DB Tier and then run AutoConfig on AP Tier.



CHANGES

 Upgraded EBS from R12.0.6 to R12.1.3 and Database from 11.2.0.3 to 12.1.0.1

CAUSE

One of the mandatory patches for the 12.1.3 upgrade was not applied:

Patch 16289505 - INTEROPERABILITY PATCH FOR EBS 12.1.3 ON RDBMS 12C (12.1.0.1)

The case is discussed in the following bug:

BUG 22235648 AFTER 12C DATABASE UPGRADE ISSUE WITH AUTOCONFIG - JTFICTX.SH EXITING WITH STATUS,

Per the above bug the following SQL statement should have been replaced with call to ad_ctx_ddl.sync_index in jtfirip_ux.sql 120.4.12010000.4:

  ALTER INDEX REBUILD ON LINE PARAMETERS('sync')

This file should have been provided with patch 16289505 when upgrading DB to 12.1.0.1 as stated in the following document:
Note 1524398.1 - Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1


SOLUTION

To resolve the issue test the following steps in a development instance and then migrate accordingly:

1. Download Patch 16289505 along with any required prerequisite patches.

2. Apply Patch 16289505 along with any required prerequisite patches per the readme instructions.

3. Restart the instance to ensure the changes are successful.

4. Retest AutoConfig and confirm the error is resolved.

Saturday, 3 June 2017

ORA-06598: insufficient INHERIT PRIVILEGES privilege

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges.

12c introduced this new piece of functionality that initially looks a little odd but actually does make sense when you follow it through, i would imagine there are limited cases though where this specific functionality is required.

What it essentially allows only is relevant  for invokers rights procedures where you might want a certain piece of the code block to have rights that the invoker might not have and you don't want to give them that right directly - you only want it to be valid when they run that code block. This new feature allows you to do that - with me so far?

Ok lets do a quick demo, we'll create a c##test user which will own the plsql procedure and a c##rich user who we will let invoke it but we don't actually want that user to have any additional rights - just be able to run the few lines of plsql with elevated rights. Now you could argue here - why bother with invokers rights then just run with definers rights - and in the simple case below that is true - but I'm sure there are more complex other cases where this new feature is useful.

Anyway lets set a few things up - starting with the user

Re-create grants and synonyms for Apps Schema using adadmin utility

Facing below error
sqlplus -s APPS/***** @/apps/ebs/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adgrnctx.sql &systempwd &un_fnd CTXSYS &un_jtf JTF

Connected.
Connected.
User altered.
declare
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SYSTEM.AD_DDL", line 165
ORA-06512: at line 160

Solution: -

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
But if SYS trusts x, the following can be done:

SQL> grant inherit privileges on user sys to system;
Grant succeeded.

This will allow x to inherit the privileges of SYS when running code like the above.

SQL> grant INHERIT PRIVILEGES ON USER sys to public;
Grant succeeded.

SQL> grant INHERIT PRIVILEGES ON USER system to apps;
Grant succeeded.

SQL> grant dba to system;
Grant succeeded.

SQL> grant dba to apps;

Grant succeeded.

ORA-01804

Autoconfig Failed On 12c Database With "ORA-01804: failure to initialize timezone information"
(Doc ID 1908836.1)

Oracle Applications DBA - Version 12.1.0.2 to 12.1.3 Information in this document applies to any platform.

SYMPTOMS

On Oracle Applications 11.5.10.CU2, OR 12.0.6 OR 12.1.3 version, when attempting to execute autoconfig on the database tier,

the following error occurs:

ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly


The issue can be reproduced at will with the following steps:
1. Autoconfig run on the database tier.
2. The error occurs.

CHANGES

Newly upgraded 12c Database.

CAUSE

The issue is caused by 12c Timezone Specification file code was not added in following files:

ad8idbux.env
ad8idbnt.env
adlnkoh.sh

---------------------In ad8idbux.env file---------------------

if test "%s_database%" = "db112" -o "%s_database%" = "db121" ; then
     ORA_TZFILE=""
else
     ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"
fi
export ORA_TZFILE
---------------------In ad8idbux.env file---------------------



---------------------In ad8idbnt.env file---------------------

IF "%s_database%" == "db121" (
     set ORA_TZFILE=
)
---------------------In ad8idbnt.env file---------------------

This issue has been fixed in ad8idbux.env 120.8.12010000.3 or Higher, and in ad8idbnt.env 120.4.12010000.5 or
Higher.

SOLUTION

To implement the solution, please execute the following steps:

1. Download and review the read me and pre-requisites for Patch 16289505.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions(database node):
       $ORACLE_HOME/appsutil/template/ad8idbux.env     120.8.12010000.3
       $ORACLE_HOME/appsutil/template/ad8idbnt.env     120.4.12010000.5
       $ORACLE_HOME/appsutil/template/adlnkoh.sh         120.16.12010000.11
You can use the commands like the following:
       strings -a $ORACLE_HOME/appsutil/template/ad8idbux.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/ad8idbnt.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/adlnkoh.sh | grep Header

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

OR try below steps also issue resolved.

Database:- 12.1.0.2 or Apps:- R12.1.3

SQL> conn / as sysdba
Connected.
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18



Symptoms

 Running AutoConfig on a database tier of E-Business Suite 12.1.3 results in the following error:
ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

Cause

 This issue is caused by the current AutoConfig template file that points to the wrong file.

Solution


1. This is resolved by editing the template ad8idbux.env in location .../oracle/db/tech_st/11.2.0/appsutil/template changing:

From:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg.dat

To:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg_18.dat

2. Running AutoConfig after this change means the environment file is created with the ORA_TZFILE value as timezlrg_14.dat and AutoConfig completes successfully.


Issue resolved.

Friday, 2 June 2017

UnsatisfiedLinkError exception loading native library: njni11

When running Autoconfig for Oracle database 12.1.0.2.0 with R12.1.3 i get the 

error:
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /lib/libnjni11.so: ld.so.1: java: fatal: /lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)
/lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)


Solution 

1. Apply patch latest TXK patch. For R12.1.3 Patch 7651166 

2. Migrate the code from the APPS-Tier to the DB-Tier via the appsutil.zip 

3. Check that appsutil has all the required subdirectories (for example scripts, if not copy them from the old appsutil!) 

4. Create the Database Context File again
perl $ORACLE_HOME/appsutil/bin/adbldxml.pl
template=$ORACLE_HOME/appsutil/template/adxdbctx.tmp
out=/u01/clone/db/tech_st/11.2.0/appsutil/CLONE_ebs6.xml

5. Run Database Autoconfig

Oracle Support says that ORACLE_HOME 11.2 does not contain 32-bit libraries, but Autoconfig still uses a 32-bit Java version. You must use 64-bit Java. 

Friday, 19 May 2017

Oracle row count for all tables in schema

Count each number of table rows in specific schema.



Oracle ACE Laurent Schneider has a more elegant solution for counting tables, using dbms_xmlgen to store the row counts for multiple tables in a single SQL query list:

SQL> set pages 999;
SQL> col count format 999,999,999;
SQL> spool /home/oracle/row_count.txt
SQL> select
   table_name,
   to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||table_name))
    ,'/ROWSET/ROW/C')) count
from
   user_tables
order by
   table_name;

SQL> spool off;

Output like this

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5

Thursday, 20 April 2017

UNDO tablespace keeps growing in Oracle

The UNDO tablespace size is governed by the the undo_retention parameter.  f its non-default and is set to a high value in order to enable a large flash recover area.  Undo table-space will tend to keep the old data till that time period. Also check out that whether you have any transactions which are not getting committed/rolled back as if this is not happening than your table-space will keep on growing.

Undo data is managed within the UNDO table-space using UNDO segments that are automatically created and maintained by Oracle. Every Oracle Database must have a method of maintaining "before images" of rows that is used to roll back changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed.  UNDO rows are use for several purposes.  We also see these reasons for UNDO:
  • Transaction rollback:   UNDO is the mechanism by which you can undo your changes before a commit with the ROLLBACK command. Also, if a process is abnormally terminated UNDO will be used to clean up any uncommitted transactions.

  • Flashback. Oracle 10g and beyond has Flashback Database. Flashback Query and Flashback Table (but not dropped table) utilize UNDO.

  • Read consistency:  If you start a query at 9AM and it takes 3 hours, are the results you see from 9AM or noon? The answer is 9AM, the moment you pressed the enter key. Oracle accomplishes this through read consistency, which pulls the data you require from current data blocks or consistent read blocks, which is UNDO. If you do not have enough UNDO to satisfy your long running queries, you might get a ORA-01555 error.

  • Transaction Recovery: If your instance is abnormally terminated (kill -9 pmon, shutdown abort), SMON will handle recovery on instance startup. This involves four phases: mount the database, roll forward all redo since the last checkpoint to the data-files, open database, and rollback all non-committed transactions.
Hence, if the UNDO tablespace has a high undo_retention then it will eat up all of your disk space in the UNDO tablespace. I suggest that you take a look at your UNDO adviser within OEM and see what it recommends for an UNDO table-space size for your database.
There are some views that show information related to UNDO activity:
  • V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
  • V$TRANSACTION: present time view providing information on current transactions.
  • V$SESSTAT: individual session statistics, which includes one for undo usage.
You can query the v$undostat and the v$transaction view to see the actual usage of your UNDO table-space.

You can also run this query to see UNDO usage:

select
   a.sid,
   b.name,
   a.value

from
   v$sesstat  a,
   v$statname b

where
   a.statistic# = b.statistic#

and
   a.statistic# = 176
order by
   a.value DESC;


We also have this query to measure growing UNDO tablespace usage:

colusername FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;


In a nutshell, the undo_retention parameter is used to throttle a growing UNDO tablespace.

Managing the UNDO TABLESPACE

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Flashback Query
Recover from logical corruptions using Flashback features
Earlier releases of Oracle Database used rollback segments to store undo. Oracle 9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends that you use undo tablespace to manage undo rather than rollback segments.

Switching to Automatic Management of Undo Space

To go for automatic management of undo space set the following parameter.

Steps:-

1    If you have not created an undo tablespace at the time of creating a database then, create an undo tablespace by typing the following command

SQL>create undo tablespace myundo datafile
         ‘/u01/oracle/ica/undo_tbs.dbf’ size 500M
                        autoextend ON next 5M ;

When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace. In this case, you can enable automatic extension for datafiles of the undo tablespace so that they automatically increase in size when more space is needed

2.    Shutdown the Database and set the following parameters in parameter file.
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=myundo

3.    Start the Database.
Now Oracle Database will use Automatic Undo Space Management.

Calculating the Space Requirements For Undo Retention

You can calculate space requirements manually using the following formula:

UndoSpace = UR * UPS + overhead

where:

UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is 100 undo blocks for each second, with a 8K block size, the required undo space is computed as follows:

(3 * 3600 * 100 * 8K) = 8.24GBs

To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the following statement

SQL> Select * from V$UNDOSTAT;



Altering UNDO Tablespace

If the Undo tablespace is full, you can resize existing datafiles or add new datafiles to it

The following example extends an existing datafile

SQL> alter database datafile ‘/u01/oracle/ica/undo_tbs.dbf’ resize 700M

The following example adds a new datafile to undo tablespace

SQL> ALTER TABLESPACE myundo ADD DATAFILE '/u01/oracle/ica/undo02.dbf' SIZE 200M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tablespace undotbs_01:

SQL> DROP TABLESPACE myundo;

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails.

Switching Undo Tablespaces

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = myundo2;

Assuming myundo is the current undo tablespace, after this command successfully executes, the instance uses myundo2 in place of myundo as its undo tablespace.

Viewing Information about Undo Tablespace

To view statistics for tuning undo tablespace query the following dictionary

SQL>select * from v$undostat;

To see how many active Transactions are there and to see undo segment information give the following command

SQL>select * from v$transaction;

To see the sizes of extents in the undo tablespace give the following query

SQL>select * from DBA_UNDO_EXTENTS;