Okay, a bit more testing has revealed something rather interesting that explains why most people probably wouldn't experience something similar.
It seems that at least Opera Mac 75.0.3969.93 & later versions prefer to be on the current startup volume, for Auto-Update to work. From my limited tests, Opera doesn't have to be in '/Applications' though; it can be located elsewhere on the startup volume.
I've been keeping Opera located on a secondary non-boot internal volume, and thus ran into this issue. Also, again in my limited tests, launching Opera 75 from any of my external volumes led to Auto-Update failure too.
So, there's a fairly straightforward workaround, which is to store Opera on my main boot volume. Although, this change in its expectations might be an unintended regression, perhaps from Opera Mac 74.0.3911.232.
@leocg Good to know that. I just tried it out and I am again into same problem (which was resolved after uninstalling & re-installing Opera). I think this feature might not be stable yet for MacOS Big Sur (This OS does not seem to be straightforward compatible with things in market)