Most of the last half of the FF series had pretty amazing openings, I'll highlight I especially liked FFXII, what an awesome way to set off the game.

Emerging from the sewers and discovering the massive open world in front of you is one of the definitive "wow" moments in Oblivion.

I really enjoyed the MGS4 "WTF" tv commercial followed by the PMC ad, followed by the "War has changed." monlogue which seamlessly transitioned into the player's control of Snake. That was a definitive moment for me on the PS3, but I know a lot of people hated it.

God of War III opening was similar in its technique and badassery.