"Above and Beyond" Not all the awesome ones were the ones with vocals. The first 40 seconds are "Holy crap! It works! IT WORKS!" with the rest illustrating that eerie calm that sinks in once the endorphin rush has passed.
Leo Leo!, the music from the Boss Battle with Vic Viper. To elaborate: This is the music that plays when you fight Leo Stenbuck, the protagonist of the first game, in his Vic Viper, which is of course patterned after the famous ship from Gradius. Naturally, then, this track's takes cues from both Gradius and the boss music of the first game.
Subverted in Killer7, where possibly the most uptempo and exciting piece of music in the entire game, "Rave On", is heard only during a brief interstitial scene in which a character climbs a flight of stairs — without facing any enemies or obstacles whatsoever. It could possibly be considered a sort of prelude theme to each level's upcoming Sub Boss fight, but it's still funny to have a completely uneventful trip through a stairwell scored by blood-pumping ass-kicking techno.
And it goes for four minutes twenty nine seconds, too!
Max Payne 2 The Fall Of Max Payne has two. First, there's the transformation of this somewhat unremarkable theme tune from the first game into this. Then there's the song "Late Goodbye" by Poets of the Fall, based on a poem by the game's writer, which plays over the closing credits; it went on to become a chart-topping hit in the band's native country.
Alan Wake from the same developer has this piece by the same band. Not only is it an awesome piece of music, but it is heard at what may be the game's Momentof Awesome, fighting a legion of The Taken while standing on a rock stage with exploding pyrotechnics all around, not just used to enhance the mood but to fight the Taken using the light of their explosions.
Among the Dragonball games, here's this Kami's Lookout from Super Dragonball Z.
Godzilla Destroy All Monsters Melee has mostly blah music. However, one track, used in the opening, gets to be on this page simply because it sums up the entire game: giant monsters fighting each other and causing massive destruction.
TimeSplitters. Made by same guy who made the music to Golden Eye 1997. Almost all levels in the games contains music like this. There are so many that should be mentioned here, but from the second game you have the Golden Eye 1997 throwback Siberia, a wild west theme that gives Ennio Morricone a run for his money, a thick and heavy techno theme of Planet X. In multiplayer there many other goodies such as the haunting circus theme, the theme rom streets, which starts slow and then builds up, ufopia that is a mix of techno and 50's theremin driven sci-fi themes. But nothing beats this. While being a throwback to the old 8 bit themes, first it sounds fun, but then you will just fall in love with it! Just go to the main site and pick your choice!
A little piece from the third game called Scotland the Brave here. It just so happens to be THE BEST piece in the game. The only question is... how are Free Radical (or Crytek Nottingham, nowadays) going to top it?
Burn Out 2: Point of Impact had some particularly awesome music, especially when you initiated a boost - most of the time the music is subdued and in the background, and then all the instruments get in your face (or rather, ears) as you suicidally flew down the highway on the wrong side of the road.
Burnout 3, meanwhile, gave us "Orpheus", "Over The Counter Culture", "Everyone Alive", or "Independence Day", and "Lazy Generation".
Klonoa 2: Lunatea's Veil has a relatively catchy soundtrack, but most of the mindblowingly awesome music occurs in the second half of the game, where the tone of the music becomes a good deal darker than the more light-hearted tones near the beginning of the game. Memorable tracks include Cursed Leorina, King of Sorrow, The Sorrow Revives, Hyuponia, and Mirage.
GU's version is, if anything, even more beautiful: Hope of Dawn.
For the final battle against Cubia, we have this song. Rather unorthodox for such an epic encounter, in that it is very slow-paced and haunting, yet manages to be awesome in its own way, and very fitting for the battle.
The "IDOLA" series is used to identify final boss themes throughout the series. "IDOLA" Have The Immortal Feather is used for Olga Flow phase 1, for instance. Each stands as an example of how awesome music is one thing Sega and Sonic Team consistently get right.
Napple Tale features a soundtrack by Yoko Kanno, and said soundtrack gets more exposure than the game. Wild Wind and Folly Fall are probably the 2 numbers with the most visibility, but unsurprisingly it's golden from beginning to end.
Dynasty Warriors - forget the Anachronism Stew aspects of using techno and wall-to-wall guitars to score battles set in ancient China. The soundtrack rocks. Special mention should go to the truly epic feel of "Men of Intelligence" (He Fei castle, Wu side, third installment) and the adrenaline of "Flame and Rapidity" (Chi Bi in the sixth installment). Plus, whenever the tide of battle turns in your favor and the "winning" music kicks in, especially in DW3, it feels ridiculously awesome.
Also memorable is Lu Bu's theme, which often comprises part of the main series' leitmotif and serves as a warning to run when you hear it. There's been several variations over the various years and games, of varying levels of intensity. In lieu of having to pick just one version of the theme to name as most awesome, here is the entire collection of them, all nicely put together into one arrangement for your enjoyment.
Gitaroo Man. After playing through most of the game enjoying the combination of music from many genres and batshit insane visuals (such as a space shark that transforms into a robot with turntables for nipples that sings reggae), there comes the electric guitar version of "The Legendary Theme".
The Arnhem music in Frontline always leads to some emotional moments, especially towards the end of the level when you realise most of your squad of chirpy Cockney paratroopers has been wiped out. But the music that plays during the mansion and train missions is perfect for getting you in that Nazi-shooting mood.
Wild ARMs 3 has more awesome tunes, particularly "FATE Breaker" and "Flying in the Mists of the Storm", though there are several dozen tracks that would easily fit this category. The ending theme, Wings, has cemented its place as the best ending song of any game, ever (only the one used in the US, though; the Japanese version is nowhere near as powerful, and the version in the US game is not available on any soundtrack; it has to be ripped from the game OST.)
The main theme from Fable is really damned epic. Mostly because it was composed by Danny Elfman.
Legacy of Kain Soul Reaver 2: The main menu theme, commonly referred to as Kain Refused The Sacrifice. Reminiscent of Ozar Midrashim while also having several snippets of Ariel's dialogue from the previous game.
The first Wayofthe Samurai had a few noteworthy tracks, particularly Afterglow. The song manages to take a pan flute, piano, some strings and a guitar and utterly capture the feel of what it must have been like to be the last of a dying breed, fighting a losing battle against time and technology.
Followed by Righteous Soul. This song makes even the good ending a heartbreaking and yet beautiful experience.
From the Japanse-only RPG Zwei: Final Battle -Demon Vesper- is a bit unique among Final Battle themes in that while it's not EPIC it opts for a more catchy and somewhat lighthearted tone while still having the feeling that it really is the final battle.
There was a little-known, very ambitious and, sadly, commercially unsuccessful PS2 game called Haven: Call of the King which boasted a "genre-defying" gameplay experience and a COMPLETELY FUCKING AMAZING soundtrack. Unfortunately, as Western games seldom get proper soundtrack releases, there is no CD soundtrack to be had for this game. However, just listen to the Level 1 music; the video comments include links to a download page for the rest of the soundtrack. The links are all "001, 002, 003" but seriously, just pick some. You're guaranteed to get something that sounds good.
Toy Commander, another overlooked game for the Dreamcast, has a pretty fantastic soundtrack. One of the best songs is Feel.