ORA-01555 caused by SQL statement below

Ora-01555 Caused By SELECT Statements With Low Query Duration and no Concurrent DML (Doc ID 361992.1)

ERROR:

**********************************************************************

Following errors written to the Alert log file. Please verify

ORA-01555 caused by SQL statement below (SQL ID: gjfmusra5137t, Query Duration=3085 sec, SCN: 0x056f.c7e884eb):

ORA-01555 caused by SQL statement below (SQL ID: gjfmusra5137t, Query Duration=3071 sec, SCN: 0x056f.c7e88605):

**********************************************************************

APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.7 to 11.2.0.1.0 [Release 9.2 to 11.2]
Information in this document applies to any platform.

SYMPTOMS:

Many ORA-1555 occur while performing only SELECT statements within the reporting database:
ORA-1555 with small query duration in the message :

     ORA-01555 caused by SQL statement below (Query Duration=9 sec, SCN: 0x0001.8be5c8d1):

CHANGES:
The PRODUCTION database is copied every night with its UNDO tablespace and renamed to REPORTING for reporting purposes .

CAUSE:
Hence all undo information of DML requests is kept in the UNDO tablespace and prevent
long running SELECT statements from using this space.

For additional information please see the following note:

NOTE:10640.1 - Extent and Block Space Calculation and Usage in V7-V9 Database

SOLUTION
Once the production database is copied and renamed to REPORTING new database :
1. conn / as sysdba
2. startup
3. create undo tablespace UNDOTBS2 datafile '.....' size ... autoextend on maxsize xxxM;
4. alter system set undo_tablespace=UNDOTBS2 scope=spfile;
4. shutdown immediate
5. startup
6. show parameter UNDO to check that the new UNDOTBS2 is taken into account
7. Run the select statements

Automate this procedure for each night.