奇怪的ORA-01555

从上个月开始有一个项目一直反馈出现ora-01555快照过旧的问题，经过察看，undo表空间充足，数据库日志里出现的报错都是query duration=0。

查询持续时间为0的ora-01555，太奇怪了，搜索到一篇文档  IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds (Doc ID 1950577.1) 

CAUSE
ORA-1555 with Query Duration 0 or a few seconds is mainly caused by indexes/table mismatch Or primary key index corruption.
Similar error is reported for flashback queries or queries involving dblinks.

1. Find the failing SQL statement and the tables involved.
1.a) Check the error logged in the alert log file. Along with the error message, the failing statement is also reported.
1.b) If no sql query is shown along with ORA-1555 error, then get the SQL ID from the error message (reported in the console / alert log file) and then find the SQL
statement as below:
1.b.i) If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from :
SQL> select SQL_TEXT from v$SQL where SQL_ID='<sql id from the error message>';
1.b.ii)If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from :
SQL> select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='<sql id from the error message>';
2) This step is specific to flashback queries and queries involving dblinks. If your situation doesn't match, please skip this step and go to step 3.
If the flashback query is failing with ORA-1555 error, ensure the UNDO_RETENTION is set for the duration specified in the query. If not, set the UNDO_RETENTION
accordingly and retry the failing query.
If the failing query involve dblinks, ensure the UNDO_RETENTION is set high enough on both source and target databases.
3.) Once the tables in the failing query is identified, execute analyze statement to check for possible inconsistencies. Please note analyze table.. validate structure cascade may
lock table, so run it when you do not access this table for other users. please check oracle documentation for analyze table online options.
SQL>analyze table <table name found in the error message> validate structure cascade
and check if you are getting the same ORA-1555 error.
4) Resolve the inconsistency by recreating the indexes associated with the table.
4.a) Rebuild/Recreate the Indexes associated with the table.
You can either rebuild the indexes:
alter index <index name> rebuild online;
In some cases, rebuilding wont help and may have to recreate the indexes. Get the DDL of the indexes using
DBMS_METADA.GET_DDL
select dbms_metadata.get_ddl('INDEX','<index name>','<schema>') from dual;
Drop the indexes and then recreate using the DDL obtained in the above step.
4.b) If it is Primary Key Index then disable and enable the PK constraint in order to recreate the PK index associated with the primary key constraint and to solve the
primary key index corruption.
E.g :
SQL> alter table <table name> disable primary key;
SQL> alter table <table name> enable primary key;
4.c) If there are many indexes associated with the table, check the execution plan to see the indexes involved in the failing query and recreate them
5) If the issue still persists, then create a new undo table space and switch the Undo to the new tablespace
5.a) Create a new undo tablespace with new name
SQL> create undo tablespace <New Undo Tablespace name> size <new size>;
5.b) Set the new tablespace as the undo tablespace to be used:
SQL> alter system set undo_tablespace=<New Undo Tablespace name>;
5.c) Donot drop the old Undo tablespace immediately. Check the status of the Undo segments in the Undo tablespace to be dropped.
select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
If there are Undo segments with status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE.
select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name=<undo tablespace to
be dropped>;
If all the Undo segments in the tablespace to the dropped is of status OFFLINE, then drop the tablespace.
SQL>Drop tablespace <tablespace_name> including contents and datafiles

按文档的解决方案，分析表、重建索引及主键后还是照旧。刚刚，创建新undo空间并进行了切换。再观察一下。这应该是一个bug。

切换表空间时的日志。

 

后记：

切换了新的undo表空间后问题依旧，出现此报错的sql一共3条，涉及两个表，可以尝试重建表。后来从应用方面解决了，这三条查询的sql不是必须的。