I remember hearing about an indie curtain shooter which aimed to let (for example) some leet kid play with their Casual Parent Gamer (or a parent play with their tiny child-n00b). The idea was that the primary player would be doing ordinary curtain shooter stuff, dodging thousands of bullets at once and generally being dextrous. The secondary player would be a sort of "satellite ghost thing" that rotated around the main ship and didn't need to worry about dodging bullets at all, but was only there to provide extra suppressing fire or healing -- so they were playing a very different game that didn't require the crazy dodging skills, instead focusing on one or two simple tasks, but still contributing to the overall excitement.
I can't remember the name of that game right now :( but I've always thought it was an interesting idea for co-op. I know MMOs and suchlike are somewhat asymmetrical with different characters fulfilling very different roles, but still, you'd consider all the characters in a group to be on fairly equal footing. (Same with games like Trine).
Now let me tell you about my greatest RTS experience ever, which involved three camels. (Stop me if you've heard this one before). I was playing 2v2 Age of Empires: The Rise of Rome; my teammate was very good, I was pretty bad, and our opponents were mediocre. I was obliterated early on, but managed to rescue THREE CAMEL RIDERS! I didn't even have a way to get more villagers to try and bounce back. =) Thus, the rest of the game, which went on for a respectable length of five or so hours, involved me running these camels around, raiding unprotected villagers, doing recon for my Mighty Teammate, and stopping back near his priests for healing. My camels were slightly faster than anything our opponents were fielding, and I don't know if they made a difference to the final victorious outcome, but in my imagination they were glorious camelteers, striking at a bloated and ungainly foe, buckling swashes, et cetera. I traded off with my brother and cousin throughout the five hours, so we could continue to keep the legacy alive.
Thinking on it, I wish to have more such experiences, where two people are working together but on very different scales, with very different tools and levels of attention. Perhaps a team of elite commandos (p1) supported by an army (p2) as in my example; on the wackier end of the spectrum, maybe something like The Sims, where the main Sim of the household is controlled independently as a Third Person not-Shooter by player2 (this would be best if communication were only possible via icons and gestures =P).
For some reason, those FPS games where one player is the "commander" never really seem that good to me, so you can certainly suggest them as asymmetrical co-op but I doubt I'll be excited. ;)
There's also EVE which is probably very different depending on what you fly...other than that, I can't think of many cooperative experiences that are likely to have such a wide variance in scale. HOW ABOUT YOU, INTERNET?