The reason that new degrees of freedom open up at higher temperatures is because, with the possible exception of translational kinetic energy, degrees of freedom are quantized. Due to quantum mechanics, the molecules can only vibrate/rotate/get excited with certain discrete energies, and there is a lowest energy at which this happens. The particular energy of this "lowest excited state" determines the temperature at which the degree of freedom "turns on," by which we mean that it is accessible to a large number of particles in the ensemble (in reality, you'll nearly always have a few highly excited particles at any temperature simply due to the nature of the statistical distribution of the particles, but in most situations this tiny fraction is irrelevant). As such, the temperature at which new degrees of freedom turn on is highly dependent on the specific material that is being examined.
Water, for example, has rotational energy levels at very low energies (low enough to be excited by microwaves) due to its asymmetry, while its vibrational energy levels are somewhat higher (can only be excited by higher-energy infrared radiation). Incidentally, this is why both infrared radiation and microwaves are perceived as heat - they put energy directly into one of these degrees of freedom, and this energy redistributes to the translational degrees of freedom to give a higher temperature.
Water is a bit of a complicated case, as it's a nonlinear asymmetric molecule, so for more concrete predictions, the numbers to look for are the rotational temperature and the vibrational temperature, which are material-specific, and tend to only be calculated for linear molecules. The rotational temperature is typically much lower than the vibrational temperature, as rotational motion typically has a much lower first excited state than vibrational motion. Some typical values for each are found on Wikipedia (https://en.wikipedia.org/wiki/Rotational_temperature, https://en.wikipedia.org/wiki/Vibrational_temperature). For example, oxygen gas (O$_2$) has a rotational temperature of 2.08 K and a vibrational temperature of 2256 K. This means, at room temperature, the typical energy of the oxygen gas molecules is more than large enough to excite rotational modes, but vibrational modes will be essentially out of reach.
As such, if you were to heat oxygen gas to above 2256 K, then you would see a jump in heat capacity corresponding to the vibrational modes now being accessible places to store energy; likewise, cooling oxygen to below 2 K will cause a decrease in heat capacity, as the rotational modes are no longer easily accessible.