wtorek, 30 grudnia 2014

"ORA-01555: snapshot too old" after failover physical standby

Recently I received error on  test database:

ORA-01555: snapshot too old: rollback segment number 53 with name "_SYSSMU53_526582059$" too small


simple problem with a simple solution :)

but...

This  is a test database and almost nobody use this database. I checked a UNDO utilization and was on very low level.

I changed undo guarantee, undo retention and I added additional datafiles to UNDO tablespace.

Nothing help...

and now the background on how we prepare the test database.

This test database is prepared as I storage snapshot from standby. Before preparing snapshot recovery managed standby had been cancel

recover managed standby database cancel;

and stopped.
So everything should be OK. 

I thought that then problem can be with UNDO and I decided to recreate UNDO tablespace. I create new one and switched database to use new UNDO tablespace. After that I dropped old UNDO tablespace and I executed query once again.

and...

the same error but without indicating the UNDO segment.

ORA-01555: snapshot too old: rollback segment number  with name "" too small

In original query application use two sub-queries, I wanted to find exactly where is the problem.

In execution plan I found that optimizer for one of the sub-query use index. After eliminating this sub-query everything works :)

BINGO

I tried rebuild this index and once again ORA-01555, so I decided to drop index and create index once again. Successfully...

After that original query works perfectly