Yes, Opeth usually play in standard tuning. A couple of songs are in drop D and most of Ghost Reveries is in an open D tuning (DADFAE), but they have never actually tuned down.
I personally tune to C# and drop B, and use a capo when I'm playing a song that's in a higher tuning. A lot of metal bands tune down, and standard D seems to be popular, but there's not one tuning that's "better" than the others. I'd say it depends on what kind of stuff you like to play - for example, if you mostly play Opeth and/or traditional metal, standard tuning should be fine, but modern metal may require going lower.