Well, this topic is kinda failing since nobody is reading what the actual question is. :/

But I did, and the first thing that comes to mind is how in Castlevania IV, during the final segment of the battle against Dracula, the music changes to Simon Belmont's theme. It definitely pumps up the excitement of the fight. I agree, it is really cool to fight a final battle with a dramatic, heroic theme for the playable character rather than a sinister song aimed to capture the spirit of the enemy (not that the latter's songs can't be good). It's also nice when they go full circle and reuse or build off of a theme from the title screen or the like.