Prod outage due to invalid SYNONYMS in Oracle? Result of "alter index rebuild online"?

[Carsten Schmitz]

Hi.

This is all based on a preliminary analysis, I am still looking at logs and other things. But our production engine had another major outage last night. It appears that SYNONYMS, an Oracle mechanism, were invalid, and that recompiling all SYNONYMS that were not "VALID" fixed the problem. The affected SYNONYMS were these:

SQL> select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status <> 'VALID';

'ALTER'||OBJECT_TYPE||''||OWNER||'.'||OBJECT_NAME||'COMPILE;'

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

ALTER SYNONYM GFD_IS_UC4WORKER_01.BT COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.EV COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQCP001 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQCP002 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQCP003 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQCP004 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQCP005 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQOWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQPWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQRWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.OT COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.OV COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.OVW COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQCP001 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQCP002 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQCP003 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQCP004 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQCP005 COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQDWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQOWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQPWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQQWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQRWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.SQ_MQWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.UC_VERSI COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQDWP COMPILE;

ALTER SYNONYM GFD_IS_UC4WORKER_01.MQQWP COMPILE;

Our current working hypothesis is that invalid SYNONYMS are the result of using "alter index rebuild online", at least that's what our very capable DBA says, and that this crashed the engine. We did rebuild indexes that way for many years (our index rebuild job was, afaik, made by Automic consultants around version 9) and we did rebuild indexes successfully with 12.3.1 earlier, so I am lost why this would happen now and not, say, a week earlier. But the fact that recompiling the SYNONYMS immediately revived the engine is, at this time, the strongest indicator that the invalid SYNONYMS were the root cause.

Can anyone on Oracle, or anyone from Automic shed more light on this? Also interresting would be to know whether SYNONYMS are a new thing. Did they perhaps come more recently, with ZDU changes?

Thanks!

------------------------------
These contain very good advise on asking questions and describing supposed bugs (no, you do not need to go to StackExchange for Automic questions, but yes, the parts on asking detailed, useful questions ARE usually relevant):

http://www.catb.org/~esr/faqs/smart-questions.html

https://www.chiark.greenend.org.uk/~sgtatham/bugs.html

I will not respond to PM asking for help unless there's an actual reason to keep the discussion off of the public forums.
------------------------------

[Carsten Schmitz]

By the way, that five hour outage, even though most logs stopped at the start of it, has generated a cool one dot eight gigabytes of logs and traces still (likely including the traces the engine always throws each night since going from v10 to v12).

I could likely not watch a gigabyte of video on an eight hour work day.

Scouring that amount of log files, when one does not know what one is looking for exactly, is "exciting". And yes, I know how to use grep and it performs, but I don't know what I am looking for.

:(

------------------------------
These contain very good advise on asking questions and describing supposed bugs (no, you do not need to go to StackExchange for Automic questions, but yes, the parts on asking detailed, useful questions ARE usually relevant):

http://www.catb.org/~esr/faqs/smart-questions.html

https://www.chiark.greenend.org.uk/~sgtatham/bugs.html

I will not respond to PM asking for help unless there's an actual reason to keep the discussion off of the public forums.
------------------------------

[Carsten Schmitz]

Hi.

Sadly, no answers on this one yet. I am going to renew my question one time, since this is still an ongoing issue and Automic Support really doesn't send me anything beyond repeating how to optimize my Oracle DB thus far.

Are there any Oracle experts who share the view that Oracle SYNONYMS are not necessarily recompiled along with an "alter index rebuild online", and that this can potentially create a race condition which might, at least conceivably, cause Automic processes to freeze?

Cheers!

[Will Boyd]

This is an interesting issue that I had not seen before. Why are you rebuilding the index is this related to ILM? Do I need to check that my SYNONYMS are still valid. 

[Carsten Schmitz]

> ​Why are you rebuilding the index is this related to ILM?

I don't think this is specific to ILM. Automic recommends rebuilding indexes either way. This is somewhat hidden though in an "recommendations for Oracle" segment, which is conviniently not part of the installation instructions and Automic does not ship with a prepared script or procedure. So I'd not even be surprised if many clients miss this stuff entirely.

https://docs.automic.com/documentation/webhelp/english/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#AWA/Admin/admin_Oracle.htm#link3

> Do I need to check that my SYNONYMS are still valid. 

Well, only if you are in fact rebuilding indexes, I guess, and even then it seems a broken synonym would possibly crash your engine. So in a way, if they were broken, you'd instantly know ...

[Keld Mollnitz]

Hi,

we are using ILM and we do an online rebuild of all indexes once a week and afaik we have never run into an issue with invalid SYNONYMS...

/Keld.

[Carsten Schmitz]

Final update.

While the Automic source code does contain references to synonyms, these can, according to Automic, be used when using MS SQL Server. Automic maintains there are no synonyms being created or used by Automic when using an Oracle database.

We could ultimately not establish how the synonyms in our Oracle database came into existence.

We have now removed the entirety of the 140 synonyms, and as of yet this had no ill effects on the Automic installation. This means that we can return to "alter index rebuild online" without fearing further complications.

(edit: typo, clarification)

------------------------------
# signature.sh --verbose=[true|false]
# no configurables beyond this point, only signature

Using the slide show widget for posting individual images is wrong. Please use the "insert image" button in the editor.

Did you know? I will NOT respond to PM asking for help unless there's an actual reason to keep the discussion off of the public forums!

"Efficient Solutions Monthly Magazine" says: These contain very good advise on asking good questions. No, you do not need StackExchange for Automic, but asking questions the right way never hurts:

http://www.catb.org/~esr/faqs/smart-questions.html

https://www.chiark.greenend.org.uk/~sgtatham/bugs.html
------------------------------