I pretty sure I first heard Wagner's music on old Bugs Bunny Cartoons. Yet, as a gamer, Wagner didn't come alive for me until I watched the movie Excalibur, which features selections of "Parsifal," "Prelude to Tristan and Isolde," and "Siegfried's Funeral March." The soundtrack also has a version of "O Fortuna" from the Carmina Burana. If you think you haven't heard of Wagner before, you probably have.
Here's the scene from Apocalypse Now that features Wagner's "Ride of the Valkyries" (skip to 1:15 for the music):
It can be played during a battle, where the characters are no doubt going to be victorious, perhaps after a long struggle. Yet there is still some danger to be had.
In my last D&D campaign, I actually had Valkyries ride forth to turn the tide of a massive siege.
You can find the music with or without the opera singing. I recommend the performance by the London Phiharmonic Orchestra.
Next up we've got "Parsifal:"
Perhaps I should let the music speak for itself. It is after all, about the Grail quest. Got a paladin in the group searching for a holy sword? Play this when he finds it.
The neat thing about Wagner's music is that it remains fairly accessible for a decent price--unless, of course, you want to listen to his complete operas. Just do a quick search on amazon and you can find collections for $8-20.
You can get a brief history about Wagner on Wikipedia.
(Yes, the Nazis liked Wagner. No, listening to Wagner beautfiul music does not make you a Nazi. The same goes for listening to Rammstein, Schwarzer Engel, etc. Perhaps I don't need to say this, but perhaps I do.)