Ah, the nostalgia!
As a kid growing up in the 90's with a ever-so-slightly younger brother, 2-player console games were a huge part of my childhood. We burned *hours* on Toejam & Earl, Golden Axe, Streets of Rage, Micro Machines, Combat Cars and the Sonic games. It was mostly co-op stuff for us (that's not to say our Ryu vs. Ken feud hasn't ruined a few controllers though).
We're still playing co-op today - there are quite a few newer games out there that still do local co-op well. Risk of Rain and Trine (and its sequel) are both great for this.