Spirit guide is more than worth it, yes. So - on a different character - is dual-cursed.
It does sound like something caster-based is going to be important with 2.5-3 goons in the party already. Heirophant & oracle should work.
The shadow mystery is certainly good as a spellcaster and would let you cover some arcane spells too if that's necessary. Outer rifts is more about buffing yourself into a melee monster, more literally than usual. Heavens is a bit more specialised than I'd like as the sole spellcaster. Time works though it doesn't really come with a good image or concept IMO. Battle is indeed about charging into a fight and laying waste to the enemy.
Since you want to keep up with a bunch of power-gamers some of the more powerful mysteries imho are dragon, time and ju-ju if you want to be a necromancer with an undead horde [which I am pretty certain you don't].
The dual-cursed archetype gives you misfortune which is broken-good. Extra revelations are excellent too.
Dual-cursed's misfortune is an immediate action. Sure it only applies to one roll but that's enormously better than the witch version. It would synergise with shadow magic obviously.
The shadow mastery is for spells like shadow conjuration which have a % effectiveness if the enemy makes their save. Shadow conjuration has 20% effectiveness in that situation, if you'd just got shadow mastery at level 7 then it would be 27% effective instead. It's useful but not great. The first two revelations from the shadow mystery you would get are army of darkness and dark secrets.
Dragon has a really cool flavor to it but is it good if I want to be purely spellcasting ? I'm not much of a close combat player and while the mystery does seems to have some really cool things, it seems to me he would shine better as melee oriented ?
Kind of yes and no, mostly no imho.
I know where you are coming from in avoiding melee. I often play arcane casters and when so doing avoid melee like the plague.2 of the dragon mysteries are useful only in melee, those giving you claws and tail swipe. Form of the Dragon sort of does too,somewhat, but that is so cool and powerful you want it anyway.
I have had the time to try misfortune with the witch it does the body good.
Dual-cursed's misfortune is an immediate action. Sure it only applies to one roll but that's enormously better than the witch version.
avr is right as usual aside from one thing. A witch can cackle,prolonging misfortune indefinitely. An Oracle can't.
If you want to optimize a mythic oracle make sure that whatever mystery you take has a lot of revelations that scale up as you level up. Mythic domain makes all of your revelation count as if you were 4 levels higher, but does not grant early access to them. It also allows you to regain all uses of your revelations once a day. This can be a huge boost in power and should not be lightly dismissed.
Another thing to consider is which mythic spells will you be able to use. Look to see if the spells of your domain have mythic version of them, and if they are any good.
The Heavens mystery is actually pretty good in a mythic campaign. Awesome Display allows you to reduce the HD of those affected by your pattern spells by your CHA modifier. This means spells like color spray, hypnotic pattern and rainbow patters remain useful much longer.
Mythic color spray allows you to add half your tier to what you can affect with it. Between that and awesome display this means that this spell remains useful at nearly any level. Mythic is going to give you a huge CHA bonus because of the extra stats from mythic. Assuming a 30 CHA at 13th level your color spray will knock unconscious a 15 HD creature, and blind and stun a 17 HD creature. If you take spell focus, greater spell focus and mythic spell focus the saving throw will be 25 as a 1st level spell.
