Hi,
We have an 12.2.0.1 Oracle EE 64bit on Linux and we've on a very regular basis ora-01555 errors. The errors are generated by a Cognos application.
The number of occurrences is (this month):
# Date
3 2019-02-25
11 2019-02-08
12 2019-02-22
14 2019-02-27
15 2019-02-26
16 2019-02-13
17 2019-02-12
20 2019-02-05
20 2019-02-15
22 2019-02-18
23 2019-02-01
23 2019-02-19
26 2019-02-11
28 2019-02-21
30 2019-02-20
43 2019-02-04
50 2019-02-14
54 2019-02-06
89 2019-02-07
I did create a tar with Oracle but it so far it has no provide a possible solution. All undo checks looks fine:
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 21/02/2019 15:00:35
End Time : 28/02/2019 15:00:35
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS2
Current undo tablespace size (datafile size now) : 8192M
Current undo tablespace size (consider autoextend) : 24576M
AUTOEXTEND for undo tablespace is : ON
Current undo retention : 3600
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:The undo tablespace is OK.
Undo Space Recommendation
-------------------------
Allocated undo space is sufficient for the current workload.
Retention Recommendation
------------------------
The best possible retention with current configuration is : 617332 Seconds
The longest running query ran for : 3106 Seconds
The undo retention required to avoid errors is : 3106 Seconds
LONGEST_QUERY
-------------------------------------------------------------------------------------
The Length of the Longest Query in Memory is 3106
LONGEST_QUERY
--------------------------------------------------------------------------------------------------
The Length of the Longest Query During This Time Range is 2140
LONGEST_QUERY
-------------------------------------------------------------------------------------------------------
The Length of the Longest Query During This AWR snaps Range is 2415
REQUIRED_RETENTION
--------------------------------------------------------------------------------------------------
The Required undo_retention using Statistics In Memory is 3106
REQUIRED_RETENTION
----------------------------------------------------------------------------------------------
The Required undo_retention During This Time Range is 2140
LONGEST_QUERY
---------------------------------------------------------------------------------------------------
The Required undo_retention During This AWR snaps Range is 0
BEST_RETENTION
------------------------------------------------------------------------------------------------------------------------------
The best possible value for undo_retention the current undo tablespace can satisfy is 617333
I did enable ora-01555 tracing and one of the things that seems odd to me is that this query is causing ora-01555:
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CASE WHEN DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2' ) THEN CHAR_LENGTH ELSE DATA_PRECISION END,
DATA_LENGTH, DATA_SCALE, NULLABLE
from ALL_TAB_COLUMNS
where OWNER = :owner1
and TABLE_NAME IN ( :name1 ) order by TABLE_NAME, COLUMN_ID
The other queries are based on the schema tables (of a financial system).
As mentioned above all the queries are comming from a Cognos application server and that application server has some times a very high load. Could this high load on the application server
be causing the ora-01555?
I've checked for table/index inconsitenties and found no issues there.
regards,
Ivan