Amen to the Trench Run. Rogue Squadron II was the game that PERFECTED the cinematic Star Wars dogfight- not as in-depth and technical as the likes of X-Wing or TIE Fighter, just sheer, balls-out, arcadey, "you are really there" thrills. The assault on the 2nd Death Star from the same game, an event so big it had to be split into 2 seperate levels (Battle of Endor and Strike at the Core) was almost as good, although they didn't quite spark the same magic as the flawless trench run conversion. Strike at the Core was particularly insane, especially if you tried it with the Falcon rather than Wedge's X-Wing (one thing's for sure, it gives you a new respect for Lando Calrissian's piloting skills).
As far as other game set pieces? The Garden War from Final Fantasy VIII. Balamb and Galbadia Gardens, both taking to the air and clashing in a huge war sequence. Galbadian bikers launching a coordinated jump to land on Balamb Garden, running through an active FMV warzone where SEED students clash with Galbadian soldiers, the two Gardens ramming into each other, and the desperate race against time to save Rinoa from falling to her death. Thrilling. I don't think any other FF game has equaled that sequence at any stage. Although the bit where your party use the airship to crash Yuna's wedding in FFX by skating down the boarding cables was pretty wild.
Or for the TRULY retro, the first encounter with the Cyberdemon at the end of the 2nd episode of DOOM- the Tower of Babel. An army's worth of weapons and powerups waiting for you at the start. The corpses of Barons of Hell nailed to the doors leading out of the starting area. A single, huge, hexagonal open-air arena with the titular tower in the middle. A few scattered Lost Souls to deal with. And then... KA-SHUNK, KA-SHUNK, KA-SHUNK, KA-SHUNK *ROOOOOOOOOOOAAAAAAR!!!* Hell has come for you, armed with a rocket launcher. The Spider Mastermind just couldn't compare.