Oooh, really interesting question. Unfortunately, the answer is 'I really can't think of one', and that's not just a question of being prudish, I'd answer if I could.

There are plenty of sexy game characters of more or less every stripe, good, bad and awful, but generally the ones that do the best job of it aren't really cast in specifically arousing situations. They're attractive because of a fusion of looks and personality, like a Zoe or an April or a Jade rather than someone overtly sexualised like an Elexis Sinclaire. A lot of them tend to fall into the good-girl/girlfriend archetypes, but I think that's usually more down to the designers doing their best to make them likeable characters than them being the only ones that can work. Femme fatales usually come with too much built in cynicism, and villains... villains usually try way too hard.

In my mind, to actually be arousing, any sexual content has to be based on an emotional core, and in general games do a terrible job of that. The end of the road is typically a badly done sex scene, handed out as a reward - or worse, these days, an Achievement - rather than treating the relationship going physical as part of the characters' emotional journey. A sex scene can be many things, joyous, exciting, boding, unpleasant, tacky.. but there's a reason that a casual opportunistic shag is usually described as 'meaningless'.

I also think it works best when the player isn't the one meant to be experiencing the romantic sensations. This might be short sighted, but right now I think there's more satisfaction and honesty in getting two characters together than trying to 'get' one for yourself. Even in cases where the player is directly in control of one of the characters, it tends to work better when the design makes you want the NPC/personality-driven player character to be happy rather than simply wanting to see the big event. This removes much of the notch-on-the-bedpost problem, as well as instilling more meaning into the big moment.

(I'm also not a fan of outright sex scenes in games, not because I have a problem with showing that kind of content, but because in most forms of media, what's not shown will usually be far sexier than anything that makes it onto the screen. The important bits are usually - although not exclusively - what goes around the physical action, leaving the bit in the middle to your imagination. Games even do that poorly though. Dragon Age is spectacularly bad for it, using the same sequence and godawful music for all the characters, never mind that sex with Morrigan would be an utterly different experience to getting down and dirty with Zevran or Leilana...)

With that in mind, I'd like to switch 'arousing' for 'effective' - not 'I got off to these characters', which is a mental image you don't want in your brain anyway, but 'this worked well'. Dragon Age's Alistair romance is easily the best in recent years, terrible sex scene notwithstanding, but I think Tali's romance arc in Mass Effect 2 is also a great example. It's also a great example of how sexy secrets can be, as shown by all the fans largely uninterested by Yvonne Strahovski's virtual bra, but furious that only Shepard got to see the adorable space refugee's actual face.

Going back a bit further, the scene where Gabriel Knight and Grace finally get together in Gabriel Knight 3: Blood of the Sacred, Damn I Wish This Game Had Been Better is an incredibly crude piece of staging, but both the nature of the big night and the effect on the characters afterwards is incredibly appropriate. Prince of Persia: The Sands of Time doesn't have an actual sex scene in it, but the scene between the Prince and Farah in the baths serves the same purpose, and is a crucial moment in their relationship (in a very interesting way).

There are probably others too of course, but those are the ones that immediately come to mind.