Support OpenTelemetry SDK v2

[mydea]

Description

Today, the Node SDK uses & supports the OpenTelemetry SDK in version 1.x.

Since v2 was released, we should look into supporting this as well.

This is, as of now, a breaking change, as anybody using @sentry/node with custom stuff that depends on OTEL v1 will have breakage once we update this.

Because of this, we will likely cut a v10 release soon-ish that only bumps OTEL to v2.

There are a few pieces to this:

1. Widen ranges of peerDependencies in @sentry/opentelemetry to allow both v1 and v2 (if possible - this can already happen on v9!)
2. On v10 branch, bump the used versions of dependencies of @sentry/node to v2 of the OTEL SDKs
   a. We need to make sure that all instrumentations we use (e.g. prisma, remix, ...) are also on v2 first!
3. While we are at this, we may re-visit how we handle/support users with custom OTEL setups. Today, it is pretty hard to make this work because we ship our own versions of everything, which easily conflicts with user versions. In order to solve this, we want to introduce a @sentry/node-core SDK, which does not include any OTEL dependencies (but instead has peerDependency ranges defined). This SDK ships without any instrumentations out of the box. It can be used both by users that want a custom OTEL setup, as well as for scenarios where you only need manual instrumentation (e.g. CLIs or similar). See Implement Node Core SDK #15213

Experimental work for this is being done here: #15518