Metal Gear Solid 4. All the MGS games have fantastic climaxes, from the bizarreness of MGS2 to the all out action of MGS3, but MGS4 absolutely nails it. The tension and sheer epicness of the Microwave corridor, followed by that incredible fight on top of a battleship that recaps the entire Solid series and the relationship between Liquid, Ocelot and Solid Snake through simple gestures, with the sun glinting down on the ships. After everything they'd been through, everything they'd done and everything they'd achieved. They'd fought through every battlefield with every tool available to them.
And now it comes down to one last encounter, when all that is over and there is nothing left but the personal feelings between each person to settle. Fist against fist
Knights of the Old Republic 1. It's someone clunky nowadays and lacking the immersion. But when I was playing it, I was overawed at the sorts of choices they made you make and the ways your paths with your companions was affected.
In general Bioware is pretty good at climaxes too. All the Mass Effect games have good climaxes (except the fights in ME3 are too grey, too dour and too long which puts you in a really bad emotional state to be receptive to the ending) but ME2 is the stand out. I hate the Collectors and fighting them, but the moments with your party are brilliant.
Lord of the Rings: The Two Towers. The last battle in Helms Deep is incredibly epic and really gives off a huge sense of scale and desperation. It's amazing as Legolas to set huge swathes of enemies on fire with your arrows until the ground itself looks like it's burning, but still the enemies come at you getting harder and harder until you find yourself fighting off multiple trolls as the NPCs on your side get steadily diminished.
inFamous 2. So much of this game is absolutely brilliant and the ending is no exception, it ups the ante in terms of characterisation and story and stakes, it remembers that some of the fun in this game is the way the world around you feels like it's living, with NPCs fighting on yourside or two enemy factions fighting each other in a pitched battle. And then the evil ending, which I've promised myself I will not play through, does not play it soft. You chose to be evil, now follow it through, live the consequences of your actions or let it destroy you.