In some cases, it is OK to fail. Some of the better scenes in adventure games is finding unique ways to kill our hero. I doubt they enjoy it, but whatever. Table top games are the same way, if the heroes easily triumph over every thing, that's not really fun. In real life, failure isn't ideal. Though, some endeavors take longer to get the hint. We played a game awhile back where winning was fun, but losing? Losing was just a slow, unbearable grind. I'm not sure how you make losing fun though.
Even in history, we remember only the most fantastic of losers. Waterloo, for example. The Mets (and the Cubs) were famous because they were losers. Teams of losers. America is fascinated by losers almost as much as winners, provided the loser is special enough. We've even, as a society, created a blog designed to find moments of utter failure.
Making failure fun for players is hard. Sometimes, they just roll poorly and all die. That's no fun. The same is true in any fiction, really. The Empire Strikes Back is probably one of the best movie examples I can think of where the heroes lose, and yet it is still fun. So, maybe that's the secret of writing good failure. Getting it so the heroes lose, but at some magic level where it is not a complete wipe.
In Fire Emblem or similar games, losing a character isn't fun. In fact, in most single player games, options that open up to players by them doing poorly are all sub-optimal and not fun. Losing people in the Mass Effect 2 ending is not fun. In fact, any fight you are supposed to lose in an RPG just irks me. There's one in Skies of Arcadia that annoys me. The villain tells you to surrender. You get hero points for surrendering yourself and your friends to what is certain death.
Here's another example of failure that is not fun. To make failure exciting, you have to let the players (or audience) know that, first, the heroes had a legitimate chance to succeed. You did not railroad them. Their failure, like their success, is their own. Second, they need to have a marginal win in there. In The Empire Strikes Back, Luke escapes. It's a minor win. Sure, they lost Han and a hand, but, they've got a plan.
So, even when it looks like your characters or players are about to fail, you can still make the adventure fun and exciting. Like any close match or sport, let them get out played, but let everyone walk off the field happy to have had a good time. That is what they do in sports now, right?
You will be seeing a lot of this in the future. Also, someone was talking about stack overflow at work today. All I could think about is that you can't over fill it, just remember: LIFO. Also, if you were part of the Giant in the Playground Kickstarter, aren't the printable minis awesome?