Texture packs are neat. I think they give a little extra life to the older games (its surprising how low-res they look today and how much more awesome they look with texture packs. This is something completely impossible with original hardware).
3D games are going to have some issues with camera. Was reading an interview with Eiji Aonuma about Ocarina of Time, and he said the lock-on system developed out of a need to get a good camera. It's usually pretty good in the PS2 titles (excepting a few rooms where foreground blocks your view or the camera zooms out too much), horrendous in Judgment b/c it moves around too much, but I didn't play much of the N64.
My biggest beef with the PS2 games is the poor level design. You get these huge castles and yeah, the walls look good, but... It's just a bunch of empty corridors. Where's the furniture? Where's stuff you can knock enemies into for extra damage or break open for hearts and money? Dracula must have had a blowout castle sale. DMC had all these detailed rooms, but it also had a fixed camera. I don't think Castlevania needs a fixed camera as it is inherently a scrolling game, but the levels I find to be really boring as they ultimately amount to just long, empty tunnels.
The other thing is going to have to be with fights, and this plagues nearly all 3D games I've played. It's easier to just avoid monsters by walking around them or by taking a couple points of damage to get through them than it is to spend the 10 seconds or so it would take to fight. You get nothing from lower-level enemies and it takes multiple hits to kill them (versus 2D games where weak enemies get sliced through in one hit). I think it's the lack of 'walking tank' mode, but also tedium of fighting in 3D that gives me problems. Locked doors is just a cheap way of keeping you stuck.
I honestly didn't think CoD was THAT bad, but I won't say it's a good game. LoI is in the same category, but usually rated higher simply because it was the first. The N64 games were notorious, but I don't think they were completely awful. The 2D games are still superior, so for your 3D vampire-killing action fix, you'll have to stick with DMC and God of War. It's really too bad that even though CV is a gold standard for 2D, they just can't get it right in 3D.