I own 3D Land, Kart 7, NSMB2, Ocarina of Time, and Kid Icarus and none of them require 3D ever.  It's purely enhancement, never necessary.  I keep it turned off 99% of the time.

I never use it, it gives me a headache tho I did use it mostly in ocarina of time

I'm about halfway through KH and I haven't had to use 3d yet- doesn't look like I will have to either. I only turn it on for cutscenes really

You can play any game with the 3D off and the game is still playable. Like others have said it just enhances the game. They pretty much have to do this considering they have the under 7 years old warning for 3D effect. They make all the games playable without it so they don't alienate anyone that either doesn't like the 3D effect or is too young.

No game "requires" 3D, much how no game requires HD. However, some games are greatly improved by having it. 3D Land and Pilotwings are the best examples, but I keep the 3D on for most of my games because it helps provide a bit more information and makes the worlds a bit more pretty. Based on my experience...

3D is VERY Useful for:
*Super Mario 3D Land (The game is built around it)
*Pilotwings Resort (To the extent where I can't play flying games in 2D anymore)
*Kid Icarus: Uprising (Lots of Action and distances going on in air and on ground)

3D is SOMEWHAT Useful for
*Theatrhythm Final Fantasy (Helps notes stand out from colorful backgrounds)
*Resident Evil: Revelations (How far is that zombie-thing? Should I knife it? Will my >4 meter power boost work?)

3D is MILDLY Useful for
*Street Fighter 4 3D
*Kingdom Hears 3D
*Mario Kart 7
*Ghost Recon: Shadow Wars

3D is best turned off for
*Ocarina 3D (Gyroscope is more useful)

No games actually require it. 3D, much like HD, is purely a cosmetic boost for the 3DS's games. There are definitely many games where the sense of distance is very useful like Pilotwings or Mario 3D Land. For other games like MGS3D or Ocarina of Time, I think the 3D really helps immerse you in the environment.

The 3D effect on the XL is awesome! much better than the regular 3DS, and the better battery life makes it so that using 3D isn't really an issue anymore, in terms of shortening the battery life.

Hardly needed. The sweet spot for the original 3DS is limited in range. There are certain games where a slight movement changes the placement of frames. Games specially that requires much action.

Kid Icarus is best when viewed in 3D, especially the flight levels. In Super Mario 3D Land, 3D is very essential, so kep it on from start to finish. In Ocarina of Time 3D, 3D is not needed; just an enhancment. Same as Resident Evil Revelations, but it immerses you to the scary atmosphere.