I live in Aberdeen, moved here from London over a decade ago and am raising my kids here.
I think you need to spend a bit of time in a place (any place!) before moving there regardless of what people tell you about it, but IME...
Never experienced anti English sentiment in Aberdeen. Have loads of friends, probably 50% Scots, 25% English, 25% elsewhere in the world. For such a small city it's relatively global due to oil.
True that the city is in decline and it is sad, again see oil.
It is cold and dark in winter but every year I think what we lack in quantity is made up for in quality. We get the most beautiful sunrises and sunsets, the most gorgeous frosty days.
I get red squirrels and deer in my back garden. (Actually the deer are a bit annoying as they eat my flowers 😂)
Our family hasn't had many medical issues but we've never had problems seeing a GP or nurse when needed.
As others have said there are a lot of things going on if you take the time to look for them. Spectra and the Mela are brilliant every year. Have a look at the Aberdeen Performing Arts website which gives you listings across the various venues. Also NEOS, the Art Gallery, Gray's School of Art, NuArt. There's also The Barn in Banchory which has various artistic things going on.
Scenery as has been mentioned is incredible. Skiing, mountain biking, paddleboarding, hiking - it's all on your doorstep.
I'm with you on Dundee, the waterfront around the Tate is beautiful but it's the only place I've ever seen an assault during broad daylight on a main road and (different occasion) police called to a soft play. Aberdeen has troubled areas, of course it does, but there isn't a single neighbourhood I'd be scared to walk through.