ORA-01555 caused by SQL statement below… on Active Dataguard after upgrade to 188.8.131.52 25 January 2011Posted by David Alejo Marcos in Oracle 11.2, Standby, Tuning.
Tags: Oracle 11.2, Standby, Tuning
Sometimes I wonder why we do upgrade our databases…
after upgrading from 184.108.40.206 to 220.127.116.11, our standby databases (we are using active dataguard) was recording ORA-01555 on the alert log file after being running for some time.
We actively use our standby databases to run some queries for reporting.
We have two standby databases, one local (less than 50km away) and another one on a remote location (250km) from primary.
The first thing we did was to raise a ticket with Oracle and change one of the standby databases from “open read only” to “mounted” (I had experience with problems on active dataguard in the past).
This proved useful in several ways:
1.- We narrowed the problem to active dataguard. The database in mount status was applying log files without issues and not logging any errors. The other standby started to log errors after a few hours of running (seems time depends on the workload the database is under).
2.- Once we stop the database and open it in read only, it will work for some time (not very elegant but still useful).
3.- Oracle Support came with a bug and a fix.
The bug number is 10320455 and the new patch is 10018789 (to be applied on top of 18.104.22.168).
The problem itself seems to be a hang (lock) in the library cache. This lock will cause the workload to accumulate and do not release resources and logging ORA-01555 after some time.
As the patch only updates binaries (546 kb in size) we might apply it to DR and, once we secure some downtime over the weekend, apply it to production. This will help us to minimize the time we impact business by having the standby databases in mounted status.
As always, comments are welcome.