I think Oblivion is a pretty poor example, as I and everyone I have seen play it, saves before doing anything dangerous.
Runescape used to really inspire a fear of death for me, as when you died, everything you were carrying but 3 items was dropped, and it used to be pointless to try and get those items back. As an early player, losing your inventory and equipment was many hours of effort...
It shocked me when I tried the WOW trial, and found you lost nothing but your equipment degraded, and you had to run a bit. Death was just annoying.
I have not played mincraft, afraid of how addicted I would be to it.
I think Mount and Blade, with the hardcore saving mode on, does this well. You see, in Mount and Blade you can opt to can the game auto save when you leave, so you cannot undo actions. You can't die, but it you are "knocked out" in a battle, you are taken prisoner, which means you loses days in the game, I think every party member but the heroes, and all your horses and equipment, if I recall. Now, maybe you didn't lose quite everything, I can't recall, but I know I would not take dying as an option pretty much. there was too much at stake, including my reputation and relationships in the game. To one hero, even retreating from a fight was reason enough for him to leave your party.
So, I think really the bast way to make a player care about self preservation, is not to give massive penalties for death merely, but rather not let them actually die. Instead, they have to live with whatever happens, game saves instantly.
What if the player fails a quest? Don't RESTART the quest! Rather, they failed the quest, and the game saved, so they are stuck having failed. Maybe they care redeem themselves, maybe not, but they cannot restart amd pretend they did not fail.
The player has to live with their actions, and if they fall off a cliff, well, maybe they wake up days later with broken legs and there is a game itself to get help and escape.
Or, perhaps you want you player to plan events out, well say they are going someplace without food, and do not bring food. Say they are starving, perhaps they eat a body limb, or become delirious, and essentially lost all that is on them, many stat losses, perhaps they are found, but people who knew them in the game thought they were dead, etc.
In combat, say they lose a fight, and lose an arm. Even if they got a fake arm in the game, that's permentant visually, as well as possibly gameplay wise.
The point is, the player WILL make mistakes, and if they care about the world, they will try to avoid making them.
Another idea, is if your character dies, you would take over another character. In a complex enough game, this could potentially be an offspring, which would give incentive to become married and have a child in a game, and possibly give reason to interact with them, if you wanted your next character to have various skills. With no relations in the game, you may be forced to take over a random character with no relation to the story, a huge penatly. It would make it worth having your character start a normal life before going off to adventure, and communicate with many other characters, so that upon death, there are others to "continue the good fight"
So yeah. I don't think telling the play the game is over because they died is a good idea. Making them live with the failure I feel is much more effective. Then your game matters, as does not making mistakes. Though in this case, mistakes should not be as easy as walking off a cliff, or some other mistake that could be made very easily. The game needs to be smart enough to prevent "stupid deaths". No one wants to have a huge penalty for having bad depth perception, or not timing their jump properly.
In multiplayer? In multiplayer, in a game about killing other players, there IS no good way to punish death, without frustrating the player. At least no way that will make them FEAR death. You need to make death something that the player is not dodging at every turn, no matter what game it is, so that the player does not die constantly, and then they can be invested in NOT dying.
Give the player a chance, otherwise it's just frustrating.
Quote: "That is truly losing something you've worked for. You're truly losing hours of your life."
Yeah.

A Web Comic Graphic Novel!