I am traveling to the UK from India on a student visitor visa. I have a stopover at Frankfurt. Can I sightsee while transiting without a visa or do I need to procure a Schengen visa? Can I get a short stay visa on arrival in Germany?
None of this is possible. It appears to be a description of the UK system but the Schengen area does not work that way.
Generally speaking, Indian citizens need a visa in Germany even if they are merely transiting airside but your UK visa should exempt you of that requirement (see Do I need a visa to transit in the Schengen area?). It will not be possible to leave the airport or to get a Schengen visa on the spot as applying at the border point is only possible for EU citizens' family members and emergency cases.