On a contemporary naval ship - all crew will have a designated action station (battle stations sounds cooler, but is not strictly correct) in addition to their regular duty station. Even "non-combative" crewmen, such as cooks and orderlies, will have an action station: as part of a damage control team, or first aid, etc. Even on a civilian merchant ship, crew would have been trained in fire-fighting and damage control roles.
The USS Enterprise (NCC-1701) is a bit different, in that the primary role is non-military (and many of its crew are scientists rather than combative), it carries a large number of civilians, and many systems are at least semi-automated. Starfleet probably use Red Alert to mean a call to action stations (again - which is more dramatic).
Still, there are references in several episodes of designated shelter areas for civilians - presumably they have extra shielding, more structure around them, and rapid access to the lifepods.
As for Starfleet personnel - every serving crewman would have been trained in an "action" role - damage control, medical aid, etc, regardless of their primary role on ship. Even semi-automated systems can (and do) fail in the 24th century.