ORA-01555 caused by SQL statement below (SQL ID: 6pfwn5k98dywk, Query Duration=0 sec, SCN: 0x000000070377b986)

ORA-01555 caused by SQL statement below (SQL ID: 6pfwn5k98dywk, Query Duration=0 sec, SCN: 0x000000070377b986) in the DB alert log

Environment Details 

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

Oracle Real Application Cluster Multitenant

Oracle Solaris 11.3 SPARC

Oracle version 19.12.0.0.0

Problem Description

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

Above error was continuously occurring daily in a specific time period. Basically when there is an error like above in the db alert we must check if any task has been performed during the incident time period.

ORA-01555 is an error which occurs due to insufficient rollback segments or UNDO_RETENTION parameter value [Default Value is 900]. Therefore increasing this value as you want will not solve the issue. You must find why does this error occur?. In this scenario, expdp was executing during the incident time using the parameter value FLASHBACK_SCN which means CONSISTENT=Y.  So the flashback method depends on the UNDO data. Therefore the UNDO must retained until the job completion to be consistent the backup. Database alert log error as below;

PDB_*****(3):Stopped service SYS.KUPC$C_1_20211122050003_0
2021-11-22T05:06:14.682859+05:30
PDB_*****(3):Stopped service SYS.KUPC$S_1_20211122050003_0
2021-11-22T05:13:36.202127+05:30
PDB_*****(3):ORA-01555 caused by SQL statement below (SQL ID: 6pfwn5k98dywk, Query Duration=0 sec, SCN: 0x000000070377b986):
2021-11-22T05:13:36.202321+05:30
2021-11-22T05:06:14.682859+05:30
PDB_*****(3):Stopped service SYS.KUPC$S_1_20211122050003_0
2021-11-22T05:13:36.202127+05:30
PDB_*****(3):ORA-01555 caused by SQL statement below (SQL ID: 6pfwn5k98dywk, Query Duration=0 sec, SCN: 0x000000070377b986):
2021-11-22T05:13:36.202321+05:30

Usually services like SYS.KUPC$C involves with export dump jobs. In this case It is must to find the expdp log for any undo rollback segment errors.

Expdp log error as below which led to alert errors in the database alert log.

ORA-31693: Table data object "<Owner>"."<Object Name>" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_2656415742$" too small

You may query to find the existing value as below.

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

Please note UNDO_RETENTION parameter will not inherit for PDBs when change the value in container database after the version 19.9.0. This has been identified as  a BUG 30577591 – LOCAL UNDO RELATED PARAMETER VALUES AUTOMATICALLY PROPAGATING FROM ROOT TO PDBS.

Before the version 19.8.0 UNDO_RETENTION parameter was inherited from container database. Anyways fixed has been included from 19.9.0 onward for the bug 30577591.

But in this scenario we have applied the UNDO_RETENTION parameter for the particular PDB.

Usually, expdp jobs had been taken around 01 Hour and 45 Minutes for Job completion.

Therefore we changed the parameter value from 900 to 7200 and undo tablespace had sufficient space.

Issue was not reported after the parameter change.