The worst ones are "super-hero killed my evil parent so now I'm going to dedicate my life to trying to murder said super-hero which will also inevitably involve murdering loads of innocent people". Like boo-fucking-hoo; your parent was a cunt and they got killed while being a cunt so no sympathy there. And using the death of a cunt to be all self-righteous when murdering innocent people is double bullshit. It's not only a lazy trope but completely unrelateable and makes for a thoroughly one dimensional antagonist.
I'll have a think about good ones because nothing's coming to mind at the moment...
Two Face is a decent example. Taking a character that was initially setup as a shining light of justice and goodness in a swamp like Gotham then turning them into a villain is an interesting enough setup in itself; but having the original Harvey still there and locked in a constant battle with his worst self adds an ongoing level of complexity that's pretty satisfying.
Though I suppose I'm more talking about the overall character there rather than the actual origin story which is more "man gets half his face burned off". In most cases the actual origin is something equally boring sounding though, the interesting stuff comes from the impact that has on the character.
A race of bald humanoids create life on Earth millions of years ago. The humans developed and eventually go looking for their creators with the help of a gay robot. The gay robot eventually finds the baldies planet and kills them all. With everyone dead he’s a bit bored so he experiments with the baldies bioweapon to create the alien species after experimenting with some wasps t... oh a good one. I’ll have another think.