No game is perfect. There's always a part where you get to, and you just sigh. Hoping that you remember the solution as to hasten your progress.

Let me start with a strong example.

The Forsaken Fortress (TLOZ:WW)
Picture this: You've barely come to terms with the controls of the game, Suddenly you lose your main weapon and are forced to do a multi level 3D dungeon as a stealth option. Yes, DT you can put your hand down. I know that Stealth Sections are deal breakers for you.

So with that brilliant example of tarnishing an otherwise grand experience, what would you said are your examples of a brilliant game tarnished by some dumb part/thing?

Zelda: Ocarina of Time and way too many later games: Insta-fail stealth sections.

Zelda: Majora's Mask: The first 3 day cycle, which even speedrunners haven't been able to skiip. (Arbitrary code execution has been found, which makes it possible to skip almost the entire rest of the game, but there's still no way to skip the first 3 day cycle.)

Chrono Trigger: There is one part where you literally have to mash a button in order to proceed.

Dragon Quest 6: A part where you have to follow someone, and if you get seen, you have to start over.

Also, in SaGa 2 my least favorite part is Ki's Body, which is a fairly sizable dungeon area that has no treasure (except for the excavation point added in the remake that has a random stat boost potion (which can be defense)). Even worse, this comes before the point where, in the original version, you can use glitches to skip the rest of the game.

Final Fantasy 3 has one part where you're in a huge world that's mostly water, and you need to find a specific small island in order to progress through the game; the world is big enough that (in the original game) if you cast Sight, there will probably be no visible land on the map that appears. On the other hand, this part does feature ny favorite music track in the game, which sadly goes away after you complete the next dungeon. (With that said, the game isn't *that* great; both FF1 and FF5 are better, as FF3 has poor normal enemy design.)

Final Fantasy 1 has, right before you get the freedom to explore the world with an airship (at which point many interesting gameplay options open up), the dreaded Ice Cave, which is notorious for its difficulty. For example, remake versions feature the dreaded "Enemy Wins First" effect, where a group of Mages ambushes yor party and burns your entire party to death before you even get a turn. On NES, that particular encounter isn't as bad, but you still have to worry about instant death (though at least you don't have them using multi-target instant death often, whereas they do in remakes if they get a second spell of). I wouldn't mind this as much if this were saved for end-game, but having this happen in a dungeon that gates many fun gameplay option isn't nice. To make things even worse, once you get the treasure you have to *walk* out of the dungeon, as you do not yet have the ability to teleport out of the dungeon.

Generic complaint: I often hate the endings of games. They tend to ramp up the challenge and annoying aspects at the end, right when you're ready to quit the game, which drives me nuts.

Recently I played through FEAR and its expansions, and FEAR: Extraction Point has a super annoying section where you stand on a tower and snipe enemies below. FEAR was not built for this kind of ranged combat, and it feels super clunky and no fun. Everything else is great though.

I loved Alien: Isolation, one of the best games of its kind and a perfect "inside the movie" experience. However the early section where the Alien constantly hunts you, with one hit being a game over, no checkpoints and no weapons to fight back with... I hated that crap. Later on you get a flamethrower and it's ten times better.

Not a fan of going back to the swamp over and over again in the middle acts of The Witcher.

The boss battles in Deus Ex: Human Revolution are well known as terrible, with no stealth options, and the rest of the game is a 10 out of 10 for me. The "Director's Cut" adds alternatives but is worse in every other way than the original, so it's not worth it.

Syndicate Wars
Great game, but the second to last mission is bugged to the point that I can't finish it. You are in a space station of some sort, so any explosion will breach the walls, depressurize the station and instafail the mission. The problem is that some of the enemies drop bombs when they die that explode after a few seconds. Now normally this is not a problem since you can just pick up a bomb to deactivate it, but for some reason on this level you can only do it about half of the time. It's a weird bug that creeped in making the port, but it essentially makes the game unbeatable for me.

Bio Menace
That infernal sewer level! Well not only unfortunately - the majority of levels are great fun, but a few, in ep3 especially iirc, are boring and frustrating to play.

Age of Wonders1&2
If you progress far enough in the campaign then your hero essentially becomes too strong. As in, an army killing, city razing demi-god. It applies to, I think, about the latter third or so of the campaigns I played. It's unfortunate because it just sucks the fun right out of the game.

Dreamfall: TLJ is my favourite game in The Longest Journey trilogy, as it has most interesting gameplay, including some minigames. However, that said, combat (yes, another "mini-game") is simply atrocious - it's slow and controls are not responsive, not to mention that if you initiate an attack, you can't stop.

Half life, good game, then goes all mario.
Two worlds 2, hours of struggle building your character all for it to be meaningless.

The gunfights in Gemini Rue.

The last 30-60 minutes in The Samaritan Paradox have a weird change of pace, tone of the story and type of puzzles.

The zeppelin section in Indiana Jones and the Last Crusade. I made a full thread about it in the game-specific subforum.

The "mechanical" puzzles in Grim Fandango.

The Kayran fight in The Witcher 2.

The safari zone in any Pokemon game.

I'd still recommend all of these games for the good parts.

Hacking in BioShock.

I usually call those parts "boss battles". ;P

While I am not sure how good I would consider the game (at least it's not terrible), I have another example, Castlevania 64:

In the fifth stage, which has a lot of going back and forth to get the items you need, there is one part where you need to carry an explosion from one end of the level to the other. While carrying the explosion, if you get hit even once, or if you jump or fall even a little bit, it's instant death.

Even worse, if you are playing on Easy mode, once you finish the level, the game just gives you a message to the effect of "this is the end of Easy mode" and doesn't let you progress further, so if you want to see the rest of the game, you have to start over on normal mode, which means you have to go through this section again. (Neither the game nor the manual gives you any indication that this would be the case.)

This too. They should ramp up the pace and excitement factor, but actually just slow it down and make the game more grindy, tedious and frustrating, prolonging it when it should end.

Another great risk is introducing sections that play totally different than the rest, like forced stealth sections in a fast-paced action game, timers or reaction games in otherwise turn-based games etc. (It's also part of why I dislike boss battles, because they often change the way you have to fight to some sort of puzzle and exercise in patience, as well as lock you into tight spaces, when the rest of the game offers fluent action and freedom.)

far cry 1 final levels (from the helicopter part)... the game became so much more difficult for sponge enemies that just don't die....
Fay Cry 1 is a really awesome game, but the best part is that, when is good, you need to actually learn how to just shot everything in the head and win the level without a hit (because the enemies are better than you and they will shot you in the head, and you will die fast like any men in the game).... in the last part of the game they start to throw at you multiple boss and monster enemies... so many that you just want to give up.
also checkpoints are so rare and so random that it just is a dice roll.

I still love far cry 1, but don't ask me to play it from start to finish... I will just stop before the helicopter boss, because that's the good part.

Bad parts in a barely good game for me is Doom 2016 which i tried for a few days. Half the game is corridor level design and shitty spawn wave combat however when the second part of the game opened up it became a lot more fun with large open areas and well designed fast paced combat that relies on your skill as a player with no bullshit moves from demons like in some games.

Just a shame that half the game is ruined by that crap.

A few years ago I started Star Trek 25th Anniversary (or it could be Judgement Rites) and I stopped playing it because of the space battles.
They're probably fun and all, but at that moment I just felt like playing a point & click adventure.
I'm still going to play (and finish) them though!

In general, any part of a game that introduces an accessibility issue not otherwise present in the game is going to be a bad part of the game for at least some players.