I can speak for Oakland Airport, as I worked in the control tower there for a number of years. A Yellow Alert is used when an air carrier type aircraft is coming in for a landing with some sort of problem. You must have heard Alameda FD being dispatched this afternoon. They roll to assist the OFD to stand by in case the plane crashes. Now, an actual crash is a Red Alert. On the North Field, where smaller planes fly, you will hear Alert 1 for an inbound emergency and Alert 2 for an actual crash. Most Yellow Alerts are for airliners with flap problems, hydraulic problems, bad gear indication, etc.
Dave
Fremont