I've played a bunch of co-op games with my brother during the last few months. Splinter Cell: Chaos Theory has a separate campaign made for co-op, which is quite long and well designed. UT3 has a great invasion mod that's also a lot of fun. If you go back several years, Descent 3 and Unreal can be fully played in co-op. There are also a number of fanmade singleplayer campaigns for UT with co-op support.
Far Cry has a co-op mod, but we never got that to work properly. There was some strange desync problem in certain levels that kept messing up everything.