Query on Active DataGuard failing with ORA-12801 and ORA-03254
Hope you’re doing good.
I was working with a client where they have their DR environment running with Active DataGuard, ie, a physical standby database opened in read only mode, meanwhile is applying the transactions which are transfered from Production to DR.
Sometimes they need to perform some huge data extraction and they are doing it from DR to avoid impacting the performance on PROD. So, all CPU and disks reads are done from DR environment.
OK. This is an Oracle Database 19.19, running on RHEL 7, 64-bit.
- 80G SGA
- 24 CPU’s (Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz)
- Storage is a NetApp, DB is using dNFS
DB serves for SAP CRM app.
Query it was pretty simple, but table has 2 billion records, the expected output is about 913k records:
select /*+ parallel(24) */ guid,tr_obj_id,zlic_plate_ctry,zlic_plate_stat, zlic_platetype,zvehicle_class
group by guid,tr_obj_id,zlic_plate_ctry,zlic_plate_stat,zlic_platetype,zvehicle_class
having count(*)> 1;
They were running through SQL*Plus and spooling the contents to a CSV file.
When they were running this query, it was ending with following errors:
ORA-12801: error signaled in parallel query server POOD
ORA-03254: unable to execute the sql in read only database
Interesting error, isn’t?
Usually when a parallel query server dies, we have additional messages which we can have some clue about the error. On this case we only had those two errors described above.
- There is no lag between primary and standby.
- This is not a frequent issue, only happens with some queries that take some time to complete.
- As said, DB is running on 19.19. We do know that is not running in the latest RU, but we cannot say that the RU where we are running is “old”.
OK, let’s check on Metalink (My Oracle Support)…
We found it this note:
Cause is related to “influx SCN”.
There are some bugs related to this issue when using MIRA (Multiple Instance Redo Apply) on RAC configuration where we have more than one instance applying the changes on Standby at same time.
But, this is not the case here, this is a single instance DB, so, it’s using SIRA (Single Instance Redo Apply).
On our case, the workaround mentioned in the note worked fine for us. The workaround is:
alter system set “_readable_standby_sync_timeout”=100 scope=spfile;
After change this parameter you need to restart your Standby database instance.
As I said, after changed this parameter and restarted the database instance, the query worked fine!
Hope this helps!
This site uses Akismet to reduce spam. Learn how your comment data is processed.