I had a similar experience when playing Half Life for the first time. As a child grown up with Doom, Quake and perhaps Unreal (can’t remember if that came afterwards or not), it was the first one that really caught my imagination. That intro level on the monorail still rates as my best immersive gaming experience I’ve had.
My vote would be for Medal of Honor: Allied Assault. https://youtube.com/watch?v=U3EnVrAquBA
I'm a big fan of the opening in Call of Duty 4: Modern Warfare.
https://www.youtube.com/watch?v=TGzGsb_TPtI
I had forgotten how good it is. The whole game was very good. The next one, World at War was also good, but I didn't enjoy any of the later installments.
With this precondition, Far Cry 2 completely smashed me. The intro scene, in which the avatar arrives at the airport, is picked up by a driver and transferred to the hotel - passing checkpoints on the way and with no way to do anything else but to look around - is one of the strongest episodes in computer game history for me. Nothing is happening in that scene, but I had a full flashback of my experiences in Africa: the disorientation, the helplessness, the subtle tension. I could smell the air and had sweaty hands on the controller. This part hit me much more than the remaining game play.